[혼공S] 6주차_CH7&8

Ch7.스토어드 프로시저

7.1 스토어드 프로시저 사용방법

• 스토어드 프로시저 : SQL에 프로그래밍 기능을 추가하여 일반프로그래밍 언어와 비슷한 효과를 줌. 쿼리문의 집합으로, 어떤 동작을 일관적으로 처리하고 간단한 호출로 사용가능.

• 스토어드 프로시저의 생성

  USE market_db;
  DROP PROCEDURE IF EXISTS user_proc;
  DELIMITER $$ -- 스토어드 프로시저의 시작
  CREATE PROCEDURE user_proc()
  BEGIN
  SELECT * FROM member;
  END $$ -- 스토어드 프로시저의 끝
  DELIMITER; -- 세미콜론이 붙으면 SQL 끝
  CALL user_proc(); -- 스토어드 프로시저 호출. 즉, SELECT 실행

tip : 스토어드 프로시저임을 알 수 있도록 이름은 '이름_proc'으로 지을 것!

• 스토어드 프로시저 삭제

  DROP PROCEDURE 프로시저이름;

• 스토어드 프로시저 실습
  (1) 입력 매개변수의 활용

  USE market_db;
  DROP PROCEDURE IF EXISTS user_proc1;
  DELIMITER $$ -- 스토어드 프로시저 시작
  CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
  BEGIN
  SELECT * FROM member WHERE mem_name = userName;
  END $$
  DELIMITER;
  CALL user_proc1('에이핑크'); -- 이름이 에이핑크인 멤버의 대해 조회

- CREATE PROCEDURE 프로시저이름(IN 입력매개변수이름 데이터형식)

(2) 출력 매개변수의 활용

DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(
IN txtValue CHAR(10),
OUT outValue INT)
BEGIN
INSERT INTO noTable VALUES(NULL, txtValue); -- 첫 번째 컬럼인 id에는 NULL이 전달되지만, AUTO_INCREMENT 설정에 의해 MySQL이 자동으로 새로운 번호를 할당
SELECT MAX(id) INTO outValue FROM noTable; -- 방금 추가된 행의 id가 가장 큰 값이므로, 그 값을 출력 매개변수에 저장
END $$
DELIMITER;

- 이때, noTable이라는 테이블이 없음에도 실행가능했음! -> 스토어드 프로시저를 만들 때에는 아직 존재하지 않는 테이블을 사용할 수 있음. 물론, CALL할땐 테이블이 있어야함. 그래서 테이블 먼저 만들고 CALL!!!

CREATE TABLE IF NOT EXISTS noTable(
id INT AUTO_INCREMENT PRIMARY KEY,
txt CHAR(10)
);
CALL user_proc3("테스트1", @myValue); -- 테이블에 데이터를 추가하고, 그 행의 자동 증가된 id 값을 @myValue에 할당
SELECT CONCAT('입력된 ID값 ==>', @myValue);

- 출력 매개변수 위치에는 @변수명 형태로 변수를 전달해줘야함!!

(3) SQL 프로그래밍의 활용
✔️ 가수 그룹의 데뷔 연도가 2015년 이전이면 '고참가수', 2015년 이후이면 '신인가수'를 출력하는 스토어드 프로시저

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
IN memName VARCHAR(10)
)
BEGIN
DECLARE debutYear INT; -- 데뷔년수 변수 선언
SELECT YEAR(debut_date) into debutYear FROM member -- debut_date에서 연도만 추출해서 debutYear열에 넣어줌.
WHERE mem_name = memName;
IF (debutYear >= 2015) THEN
SELECT '신인 가수' AS '메시지'; -- 쿼리 결과로서 "메시지"라는 열에 "신인 가수"라는 값이 표시되도록 함.
ELSE
SELECT '고참 가수' AS '메시지';
END IF; -- 이게 IF조건문의 끝을 알려주는 코드
END $$ -- 스토어드 프로시저 끝
DELIMITER; -- 다시 ;을 구분자로 사용하도록 하는 코드
CALL ifelse_proc('오마이걸');

✔️ 1부터 100까지의 합계 계산

DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$ -- 구분자를 $$ 로
CREATE PROCEDURE while_proc()
BEGIN
DECLARE hap INT;
DECLARE num INT;
SET hap = 0;
SET num = 1;
WHILE (num <= 100) DO -- WHILE문 시작을 나타내는 DO
SET hap = hap + num;
SET num = num + 1;
END WHILE;
SELECT hap AS '1~100 합계'; -- 프로시저가 계산한 합계를 결과로 출력
END $$
DELIMITER ;
CALL while_proc();

✔️ 테이블 이름을 매개변수로 받아서 해당테이블조회(동적SQL -> PREPARE, EXECUTE문 사용)

DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
IN tableName VARCHAR(20)
) -- 테이블이름을 입력 매개변수로
BEGIN
SET @sqlQuery = CONCAT('SELECT * FROM ', tableName); -- sql구문이 만들어짐.
PREPARE myQuery FROM @sqlQuery; -- @sqlQuery을 기반으로 실행할 쿼리를 준비
EXECUTE myQuery; -- 준비된 쿼리를 실제로 실행
DEALLOCATE PREPARE myQuery; -- myQuery라는 쿼리가 메모리에서 제거됨.
END $$
DELIMITER ;
CALL dynamic_proc('member');

(4) 스토어드 프로시저의 삭제

DROP PROCEDURE 프로시저이름;

7.2 스토어드 함수와 커서

• 스토어드 함수 : MySQL에서 제공하는 내장 함수 외에 사용자가 직접 만들어서 사용하는 함수. 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용

• 스토어드 함수의 사용

⭐ 스토어드 함수 생성 권한 허용부터 꼭!

SET GLOBAL log_bin_trust_function_creators = 1;

✔️ 숫자 2개의 합계를 계산하는 스토어드 함수

USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
RETURNS INT -- 반환값의 데이터형식
BEGIN
RETURN number1 + number2; -- 반환값
END $$
DELIMITER;
SELECT sumFunc(100, 200) AS '합계';

✔️ 데뷔연도를 입력하면, 활동기간이 얼마나 되었는지 출력해주는 함수

DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
RETURNS INT
BEGIN
DECLARE runYear INT;
SET runYear = YEAR(CURDATE()) - dYear; -- 현재날짜의 연도 빼기 dYear
RETURN runYear;
END $$
DELIMITER;
SELECT calcYearFunc(2010) AS '활동 햇수';
SELECT calcYearFunc(2007) INTO @debut2007; -- 함수의 결과를 @debut2007이라는 출력 매개변수에 저장
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007 - @debut2013 AS '2007과 2013차이'; -- 출력 매개변수끼리 연산해서 결과값출력

✔️ 스토어드 함수의 내용 확인

SHOW CREATE FUNCTION 함수이름;

✔️ 스토어드 함수 삭제

DROP FUNCTION 함수이름;

• 커서로 한 행씩 처리하기

- 커서는 모든 행을 한 행씩 처리할 때 사용.

- 커서의 단계별 실습 : 회원의 평균 인원수 구하기

(0) 스토어드 프로시저부터 준비

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
이후에는 변수

(1) 사용할 변수 준비

DECLARE memberNumber INT;
DECLARE cnt INT DEFAULT 0; -- cnt는 읽은 행의 수. 초기값 0
DECLARE totNumber INT DEFAULT 0; -- 전체 인원의 합계. 초기값 0
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝을 파악하기 위한 변수

(2) 커서 선언하기

DECLARE memberCuror CURSOR FOR
SELECT mem_number FROM member;

(3) 반복 조건 선언하기

DECLARE CONTINUE HANDLER -- 반복을 수행하겠다는 코드
FOR NOT FOUND SET endOfRow = TRUE; -- 더이상 읽을 행이 없을 땐 endOfRow = TRUE로 만들겠다는 의미

(4) 커서 열기

OPEN memberCuror;

(5) 행 반복하기

반복할 부분의 이름: LOOP
여기 들어갈 내용 반복
END LOOP 반복할 부분의 이름
IF endOfRow THEN
LEAVE 반복할 부분 이름; -- endOfRow가 TRUE가 되면 반복하는 부분을 빠져나감.
END IF;

예를 들어, 한 행씩 읽어오려면

cursor_loop: LOOP
FETCH memberCuror INTO memNumber; -- FETCH는 한 행씩 읽어오는 것. memberCuror로부터 한 행의 데이터를 읽어서, 그 행의 값을 memNumber 변수에 저장
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt +1;
SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;

(6) 반복을 빠져나와서 회원의 평균 인우너수 계산

SELECT (totNumber/cnt) AS '회원의 평균 인원 수'; -- 총인원수를 행의 수로 나눠줌.

(7) 커서 닫기

CLOSE memberCuror;

(8) 스토어드 프로시저로 결과 확인

CALL cursor_proc();

-> 커서는 스토어드 프로시저 안에 코드를 작성하므로 스토어드 프로시저로 커서 작동가능.

7.3 자동 실행되는 (AFTER)트리거

• 트리거 : 테이블에 DML(Data Manipulation Language)문(INSERT, UPDATE,DELETE)작업이 발생하면 자동으로 실행되는 코드.

💥주의 : IN, OUT 매개변수 사용불가
ex) DELETE작업이 일어날 경우 해당 데이터가 삭제되기 전에 다른 곳에 자동으로 저장해주는 기능 -> 입력/수정/삭제되는 정보를 백업하는 용도.

• 트리거 활용
  ✔️ 고객 테이블에 입력된 회원 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터를 기록하는 트리거

(1) 회원 테이블의 4개의 열을 가수 테이블로 복사

USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

(2) 변경되기 전의 데이터를 저장할 백업 테이블을 생성

CREATE TABLE backup_singer
(mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
mem_number INT NOT NULL,
addr CHAR(2) NOT NULL,
modType CHAR(2), -- 변경된 타입, '수정' 또는 '삭제'
modDate DATE,
modUser VARCHAR(30) -- 변경한 사용자
);

(3) 변경(UPDATE)이 발생했을 때 작동하는 singer_updateTrg 생성

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg
AFTER UPDATE -- 변경 후에 작동하도록 지정
ON singer -- 트리거를 부착할 테이블
FOR EACH ROW
BEGIN
INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER()); -- 오늘날짜와 현재 사용자도 포함시킴.
END $$
DELIMITER ;

- OLD테이블은 UPDATE나 DELETE가 수행될때, 변경되기 전의 데이터가 잠깐 저장되는 곳으로 OLD테이블에 UPDATE문이 작동되면 업데이트되기 전의 데이터가 백업테이블에 입력됨.

(4) 삭제(DELETE)가 발생했을 때 작동하는 singer_deleteTrg 생성

DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg
AFTER DELETE -- 삭제 후에 작동하도록
ON singer -- 트리거를 부착할 테이블
FOR EACH ROW
BEGIN
INSERT INTO backup_singer VALUES(OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;

(5) 한 건의 데이터를 업데이트하고, 여러 건을 삭제한 후 확인

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;
SELECT * FROM backup_singer;

(6) 테이블의 모든 행 데이터를 삭제 후 확인

TRUNCATE TABLE singer;
SELECT * FROM backup_singer;

🤔🤔안전 업데이트 모드를 비활성화하라며 SET SQL_SAFE_UPDATES = 0; 이걸 실행하라는데 맞는지 모르겠어서 더 이상의 코드는 실행하지 않았다...ㅠㅠ

Ch8. SQL과 파이썬 연결

8.1 파이썬 개발 환경 준비

- 외부라이브러리는 파이썬에서 제공하지 않는 기능을 외부 개발자가 만들어서 제공하는 추가 기능.
ex) MySQL과 연결시켜주는 PyMySQL

8.2 파이썬과 MySQL의 연동

(1) MySQL에 데이터베이스 생성

DROP DATABASE IF EXISTS soloDB;
CREATE DATABASE soloDB;

(2) 파이썬에서 pymysql임포트한 후 데이터베이스와 연동

-> 여기서 host에는 로컬컴퓨터라면 127.0.0.1을 적어주면 되는 것 같다.

(3) 완전한 데이터 입력 프로그래밍 후 데이터 조회

- 계속해서 반복하여 데이터를 입력할 수 있고, data1에서 아무것도 입력하지 않으면 while문을 빠져나갈 수 있음.

- 읽기전용이므로 변경은 불가.
- fetchone()함수로 조회된 결과에 한 행씩 접근하여 while문 수행

8.3 GUI 응용 프로그램

• GUI(Graphical User Interface) : 윈도에 그래픽 환경으로 제공되는 화면으로, tkinter라는 라이브러리를 이용하여 GUI응용 프로그램 작성 가능.

✔️ 기본 윈도의 구성

from tkinter import *
root = Tk()
이 부분에 코딩 추가해서 화면에 버튼 등 만들어줌.
root.mainloop()

✔️ 윈도에 제목 지정 및 크기 조절

from tkinter import *
root = TK()
root.title("혼공 GUI 연습")
root.geometry("400x200")
root.mainloop()

✔️ 라벨 : 문자를 표현할 수 있는 위젯

from tkinter import *
root = TK()
root.geometry("300x100")
label1 = Label(root, text = '혼공 SQl은')
label2 = Label(root, text = '쉽습니다.', font = ("궁서체",30), bg = "blue", fg = "yellow") # fg는 글자색, bg는 배경색
label1.pack() # pack으로 해당 라벨을 화면에 표시
label2.pack()
root.mainloop()

✔️ 버튼 : 마우스로 클릭하면 지정한 작업이 실행되도록 사용되는 위젯

from tkinter import *
from tkinter import messagebox
def clickButton():
messagebox.showinfo('버튼 클릭', '버튼을 눌렀습니다.')
root = Tk()
root.geometry("200x200")
button1 = Button(root, text = '여기를 클릭하세요', fg = 'red', bg = 'yellow', command = clickButton)
button1.pack(expand = 1) # 화면 중앙에 오도록
root.mainloop()

✔️ 위젯의 정렬 : 가로로 정렬

from tkinter import *
root = Tk()
button1 = Button(root, text = "혼공1")
button2 = Button(root, text = "혼공2")
button3 = Button(root, text = "혼공3")
button1.pack(side=LEFT)
button2.pack(side=LEFT)
button3.pack(side=LEFT)
root.mainloop()

- side = LEFT : 왼쪽부터 채워라
- side = TOP : 수직으로 정렬

✔️ 위젯 사이에 여백 추가

from tkinter import *
root = Tk()
button1 = Button(root, text = "혼공1")
button2 = Button(root, text = "혼공2")
button3 = Button(root, text = "혼공3")
button1.pack(side=TOP, fill=X, padx=10, pady = 10)
button2.pack(side=TOP, fill=X, padx=10, pady = 10)
button3.pack(side=TOP, fill=X, padx=10, pady = 10)
root.mainloop()

✔️ 프레임, 엔트리, 리스트 박스

from tkinter import *
root = Tk()
root.geometry("200x250")
upFrame = Frame(root) # 윗 프레임
upFrame.pack()
downFrame = Frame(root) # 아래 프레임
downFrame.pack()
editBox = Entry(upFrame, width = 10) # 입력을 위한 entry
editBox.pack(padx = 20, pady = 20)
listbox = Listbox(downFrame, bg = 'yellow') # 리스트 박스는 아래에 나타나도록
listbox.pack()
listbox.insert(END, '하나')
listbox.insert(END, '둘')
listbox.insert(END, '셋')
root.mainloop()

✔️ 데이터의 입력과 수정이 가능하고, 초기화 버튼을 클릭하면 테이블이 삭제되고 다시 생성되도록 하는 GUI

from tkinter import *
from tkinter import messagebox
## 메인 코드부
def insertData():
con, cur, = None, None
data1, data2, data3, data4 = "","","",""
sql = ""

conn = pymysql.connect(host = '127.0.0.1', user='root', password = '0000', db = 'soloDB', charset = 'utf8')
cur = conn.cursor()

data1 = edt1.get(); data2 = edt2.get()
data3 = edt3.get(); data4 = edt4.get()
sql = "INSERT INTO userTable Values('" + data1 + "', '" + data2 + "', '" + data3 + "', " +data4+")"
cur.execute(sql)

conn.commit()
conn.close()
messagebox.showinfo('성공', '데이터 입력 성공')
def selectData() :
    strData1, strData2, strData3, strData4 = [], [], [], []

    conn = pymysql.connect(host = '127.0.0.1', user='root', password = '0000', db = 'soloDB', charset = 'utf8')
    cur = conn.cursur()
    cur.execute("SELECT * FROM userTable")

    strData1.append("사용자 ID"); strData2.append("사용자 이름")
    strData3.append("사용자 이메일"); strData4.append("사용자 출생연도")
    strData1.append("----------"); strData2.append("----------")
    strData3.append("----------"); strData4.append("----------")

    while (True):
        row = cur.fetchone()
        if row == None:
            break;
        strData1.append(row[0]); strData2.append(row[1])
        strData3.append(row[2]); strData4.append(row[3])
    listData1.delete(0,listData1.size() - 1)
    listData2.delete(0,listData2.size() - 1)
    listData3.delete(0,listData3.size() - 1)
    listData4.delete(0,listData4.size() - 1)
for item1, item2, item3, item4 in zip(strData1, strData2, strData3, strData4):
listData1.insert(END,item1); listData2.insert(END,item2)
listData3.insert(END,item3); listData4.insert(END,item4)
conn.close()
\## 메인 코드부
root = Tk()
root.geometry("600x300")
root.title("완전한 GUI 응용 프로그램")
edtFrame = Frame(root);
edtFrame.pack()
listFrame = Frame(root)
listFrame.pack(side = BOTTOM, fill = BOTH, expand = 1)
edt1 = Entry(edtFrame, width = 10); edt1.pack(side = LEFT, padx = 10, pady = 10)
edt2 = Entry(edtFrame, width = 10); edt2.pack(side = LEFT, padx = 10, pady = 10)
edt3 = Entry(edtFrame, width = 10); edt3.pack(side = LEFT, padx = 10, pady = 10)
edt4 = Entry(edtFrame, width = 10); edt4.pack(side = LEFT, padx = 10, pady = 10)
btnInsert = Button(edtFrame, text = "입력", command = insertData)
btnInsert.pack(side=LEFT, padx = 10, pady = 10)
btnSelect = Button(edtFrame, text ="조회", command = selectData)
btnSelect.pack(side=LEFT,padx = 10, pady = 10)
listData1 = Listbox(listFrame, bg = 'yellow');
listData1.pack(side = LEFT,fill=BOTH, expand = 1)
listData2 = Listbox(listFrame, bg = 'yellow');
listData2.pack(side = LEFT,fill=BOTH, expand = 1)
listData3 = Listbox(listFrame, bg = 'yellow');
listData3.pack(side = LEFT,fill=BOTH, expand = 1)
listData4 = Listbox(listFrame, bg = 'yellow');
listData4.pack(side = LEFT,fill=BOTH, expand = 1)
root.mainloop()