(6주차) Chapter 07 ~ 08

내용 정리

Chapter 07.스토어드 프로시저

07-1. 스토어드 프로시저 사용 방법

1) 스토어드 프로시저 정의와 기본 형식

스토어드 프로시저 : MySQL에서 제공하는 프로그래밍 기능, 쿼리문의 집합으로도 볼 수 있음, 어떠한 동작을 일괄 처리하기 위한 용도로 사용됨.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN

-- 이 부분에 SQL 프로그래밍 코드를 작성

END $$
DELIMITER ;

1)-1. 스토어드 프로시저 호출 : CALL

CALL 스토어드_프로시저_이름();

1)-2. 스토어드 프로시저 삭제 : DROP PROCEDURE

 : CREATE PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만, DROP PROCEDURE에서는 괄호를 붙이지 않아야 함.

DROP PROCEDURE user_proc;

2) 매개변수

2)-1. 입력 매개변수

- 입력 매개변수를 지정하는 형식 : IN 입력_매개변수_이름 데이터_형식

- 입력 매개변수를 실행하는 형식 : CALL 프로시저_이름(전달_값);

2)-2. 출력 매개변수

- 출력 매개변수를 지정하는 형식 : OUT 출력_매개변수_이름 데이터_형식

- 출력 매개변수를 실행하는 형식 : CALL 프로시저_이름(@변수명); SELECT @변수명;

3) 스토어드 프로시저의 활용

3)-1. IF~ELSE문

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
WHERE mem_name = memName;
IF (debutYear >= 2015) THEN
SELECT '신인 가수네요. 화이팅 하세요.' AS '메세지';
ELSE
SELECT '고참 가수네요. 그동안 수고 하셨어요.' AS '메세지';
END IF;
END $$
DELIMITER ;

CALL ifelse_proc('오마이걸');

3)-2. WHILE문

DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
DECLARE hap INT; -- 합계
DECLARE num INT; -- 1부터 100까지 증가
SET hap = 0; -- 합계 초기화
SET num = 1;

WHILE (num<=100) DO
SET hap = hap + num;
SET num = num + 1;
END WHILE;
SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

3)-3. 동적 SQL

 : 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회

 : 다이나믹하게 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);
PREPARE myQuery FROM @sqlQuery;
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL dynamic_proc('member');

 

---

 

07-2. 스토어드 함수와 커서

1) 스토어드 함수

1)-1. 스토어드 함수의 개념과 형식

스토어드 함수 : MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공

(1) RETURNS문으로 반환할 값의 데이터 형식을 지정하고, BEGIN ~ END 안에서 RETURN문으로 하나의 값을 반환해야 함.

(2) 스토어드 함수의 매개변수는 모두 입력 매개변수로 IN을 붙이지 않음.

(3) 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT문 안에서 호출

(4) 스토어드 프로시저 안에서는 SELECT문을 사용할 수 있지만, 스토어드 함수 안에서는 SELECT문을 사용할 수 없음.

(5) 스토어드 프로시저는 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용.

1)-2. 스토어드 함수 생성 권한 허용

먼저 스토어드 함수 생성 권한을 허용해줘야 하며, MySQL에서 한 번만 설정해주면 이후에는 신경쓰지 않아도 됨.

SET GLOBAL log_bin_trust_function_creators=1;​

1)-3. 스토어드 함수의 생성

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 '합계';

 : 필요하다면 함수의 반환 값을 SELECT ~ INTO ~ 로 저장했다가 사용할 수 있음.

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007-@debut2013 AS '2007과 2013의 차이';

1)-4. 스토어드 함수의 내용 확인 : SHOW CREATE FUNCTION

SHOW CREATE FUNCTION 함수_이름;

1)-5. 스토어드 함수의 삭제 : DROP FUNCTION

DROP FUNCTION calcYearFunc;

2) 커서

2)-1. 커서의 개념과 작동 순서

커서 : 테이블에서 한 행씩 처리하기 위한 방식, 대부분 스토어드 프로시저와 함께 사용됨.

커서의 작동 순서

(1) 커서 선언하기

(2) 반복 조건 선언하기

(3) 커서 열기

(4) 데이터 가져오기

(5) 데이터 처리하기

(6) 커서 닫기

2)-2. 커서 실습

USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()

BEGIN
-- 1. 사용할 변수 준비하기
DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝을 파악하기 위한 변수 준비

-- 2. 커서 선언하기
DECLARE memberCursor CURSOR FOR
SELECT mem_number FROM member;

-- 3. 반복 조건 선언하기
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;

-- 4. 커서 열기
OPEN memberCursor;

-- 5. 행 반복하기
cursor_loop : LOOP
FETCH memberCursor INTO memNumber; -- FETCH는 한 행씩 읽어오는 것

IF endOfRow THEN
LEAVE cursor_loop; -- LEAVE는 조건을 만족 시 반복을 빠져나감.
END IF;

SET cnt = cnt + 1;
SET totNumber = totNumber + memNumber;

END LOOP cursor_loop;

-- loop를 빠져나오면 최종 목표였던 회원의 평균 인원수 계산
SELECT (totNumber/cnt) AS '회원의 평균 인원 수';

-- 6. 커서 닫기
CLOSE memberCursor;

END $$
DELIMITER ;

CALL cursor_proc();

---

07-3. 자동 실행되는 트리거

1) 트리거의 개요

 : 테이블에서 DML문 중 INSERT, UPDATE, DELETE의 이벤트가 발생할 때 작동함.

 : 테이블에 미리 부착(attach)되는 프로그램 코드라고 생각하면 됨.

 : 스토어드 프로시저와 비슷하지만, CALL문으로 직접 실행시킬 수는 없고, 오직 테이블에서 이벤트가 발생할 경우에만 자동으로 실행됨.

 : 트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있음.

 : 테이블에 이벤트가 먼저 적용된 후 트리거가 실행됨.

 : 하나의 테이블에 여러 트리거를 부착할 수 있음.

DELIMTER $$
CREATE TRIGGER 트리거_이름
AFTER [INSERT|UPDATE|DELETE]
ON 트리거를_부착할_테이블
FOR EACH ROW

BEGIN
-- (이 부분이 작동함);
END $$
DELIMITER ;

2) 트리거의 예시

 : OLD 테이블은 UPDATE나 DELETE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블임.

 : TRUNCATE TABLE 테이블_이름은 DELETE FROM 테이블_이름과 동일한 효과를 낼 수 있으나 TRUNCATE TABLE로 데이터 삭제 시 DELETE TRIGGER는 작동하지 않음.

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMTER $$
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 ;

---

Chapter 08. SQL과 파이썬 연결

08-1. 파이썬 개발 환경 준비

1) 파이썬 소개

 : 귀도 반 로섬(Guido van Rossum)이라는 프로그래머가 C언어 기반으로 제작했으며, 1991년도에 공식적으로 발표함.

 : 입력한 내용이 바로 결과로 나오는 대화형 모드와 여러 줄을 입력한 후 한꺼번에 실행하는 스크립트 모드가 있음.

 : 인터프리터 언어로 소스 코드를 한 줄 씩 읽어 바로 실행되는 언어이며, 파이썬, 자바스크립트(JS), 펄(Perl) 등도 이에 속함.

 : 컴파일 언어는 소스 코드를 실행 가능한 기계어로 일괄 번역한 후에 번역이 완료된 파일(*.exe, *.class 등의 파일)이 실행되는 언어임.

 : pymysql 라이브러리를 통해 MySQL과 연동하는 데이터베이스 연동 프로그램을 작성할 수 있음.

---

08-2. 파이썬과 MySQL의 연동

1) 데이터 입력 프로그램

(1) MySQL 연결하기

 : 연결자 = pymysql.connect(연결 옵션)

(2) 커서 생성하기

 : 커서이름 = 연결자.cursor()

(3) 테이블 만들기

 : 커서이름.execute("CREATE TABLE 문장")

(4) 데이터 입력하기

 : 커서이름.execute("INSERT 문장")

(5) 입력한 데이터 저장하기

 : 연결자.commit()

(6) MySQL 연결 종료하기

 : 연결자.close()

 

# 전역변수 선언부
conn, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
sql = ""

# 메인 코드
conn = pymysql.connect(host='127.0.0.1', user='root', password='', db='solodb', charset='utf8')
cur = conn.cursor()

while(True):
    data1 = input("사용자 ID ==> ")
    if data1 == "":
        break
    data2 = input("사용자 이름 ==> ")
    data3 = input("사용자 이메일 ==> ")
    data4 = input("사용자 출생연도 ==> ")
    sql = "INSERT INTO userTable VALUES('" + data1 +"', '" + data2 + "', '" + data3 + "', " + data4 + ")"
    cur.execute(sql)

conn.commit()
conn.close()

2) 데이터 조회 프로그램

(1) MySQL 연결하기

 : 연결자 = pymysql.connect(연결 옵션)

(2) 커서 생성하기

 : 커서이름 = 연결자.cursor()

(3) 데이터 조회하기

 : 커서이름.execute("SELECT 문장")

(4) 조회한 데이터 출력하기

 : 커서이름.fetchone()

 : fetchone()은 한 행씩 접근하며, fetchall()는 모든 행에 한꺼번에 접근

 : fetchone() 함수로 조회된 결과가 저장된 row변수에는 튜플 형식으로 각 행 데이터가 저장됨.

(5) MySQL 연결 종료하기

 : 연결자.close()

# 전역변수 선언부
conn, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
row = None

# 메인 코드
conn = pymysql.connect(host = '127.0.0.1', user = 'root', password='', db='solodb', charset='utf8')
cur = conn.cursor()

cur.execute("SELECT * FROM userTable;")

print("사용자 ID   사용자이름           이메일       출생연도")
print("----------------------------------------------")

while(True):
    row = cur.fetchone()
    if row == None:
        break
    data1 = row[0]
    data2 = row[1]
    data3 = row[2]
    data4 = row[3]
    print("%5s %8s %20s %8d"%(data1, data2, data3, data4))

conn.close()

---

08-3. GUI 응용 프로그램

1) GUI

 : 윈도에 그래픽 환경으로 제공되는 화면을 통틀어서 말함.

 : 파이썬의 tkinter 라이브러리를 통해 윈도에 출력되는 GUI 응용 프로그램을 작성할 수 있음.

 : Tk()는 루트 윈도(root window)라고 하며, 기본이 되는 윈도를 반환함.

2) 위젯(widget)

 : 윈도에 나오는 버튼, 텍스트, 라디오 버튼, 이미지 등을 통합해서 지칭하는 용어

 : 옵션에서 다양한 설정을 할 수 있음.

 : 모든 위젯들은 pack()을 사용해야 화면에 나타남.

 : 위젯은 pack() 옵션에서 정렬 방향, 여백 등을 지정할 수 있음.

 : 위젯의 종류에는 라벨, 버튼, 프레임, 엔트리, 리스트 박스 등이 있음.

 : 버튼은 command옵션으로 사용자가 버튼을 눌렀을 때 지정한 작업을 처리해야 함.

2)-1. 위젯의 정렬

 : pack() 함수의 옵션 중에서 가로로 정렬하는 방법으로 side = LEFT 또는 RIGHT 방식이 있음.

 : 세로로 정렬하는 방법으로 side = TOP 또는 BOTTOM 방식이 있음.

2)-2. 위젯 사이에 여백 추가

 : 위젯 사이에 여백을 주려면 pack() 함수의 옵션 중 padx = (픽셀값) 또는 pady = (픽셀값) 방식을 사용함.

import pymysql
from tkinter import *
from tkinter import messagebox

## INSERT function
def insertData():
    conn, cur = None, None
    data1, data2, data3, data4 = "","","",""
    sql = ""
    
    conn = pymysql.connect(host = '127.0.0.1', user = 'root',
                          password = '', 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('성공', '데이터 입력 성공')
    
## SELECT function
def selectData():
    strData1, strData2, strData3, strData4 = [], [], [], []
    
    conn = pymysql.connect(host = '127.0.0.1', user = 'root', password = '', db = 'solodb', charset = 'utf8')
    cur = conn.cursor()
    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()

## GUI use tkinter
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()

 

기본 미션

p. 363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간,  변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기

 

선택 미션

p. 402 GUI 응용 프로그램 만들고 인증하기