[혼공s] 6주차 : 여러 프로그래밍 기능과 pymysql

 

 

 

 

1. 스토어드 프로시저 (Stored Procedure)

스토어드 프로시저란 데이터베이스에서 사용되는 SQL 코드의 집합으로, 특정 작업을 수행하기 위해 미리 정의된 SQL 명령문들을 말한다. 쉽게 말해, SQL문에 프로그래밍 기능을 추가하는 것과 같다.

 

 

 

1-1. 스토어드 프로시저의 기본 형식

스토어드 프로시저를 생성할 땐 이를 스토어드 프로시저임을 알려주기 위해 구분자를 사용해주어야 한다. 우리가 일반적으로 생각하는 구분잔는 ';'인데, 스토어드 프로시저는 $$ (혹은 ##, %%, &&, //)를 사용한다. 이유는 단순한데, 여러 SQL문장을 담고 있는 스토어드 프로시저를 일반 SQL문장과 구분하기 위함이다. 스토어드 프로시저의 기본 형식은 다음과 같다.

DELIMITER $$
CREATE PROCEDURE proc_name(IN|OUT argument)
BEGIN

    sql_command;
    sql_command;
   
END $$
DELIMITER ;

 

스토어드 프로시저를 생성하는 방법은 테이블을 생성하는 것처럼 'CREATE' 구문을 사용해주면 된다. 그러나 이때, 스토어드 프로시저는 매개변수가 필요할 경우 괄호 안에 입력 혹은 출력 변수를 명시해주어야 한다. 'BEGIN'과 'END' 사이에는 우리가 담고자 하는 SQL 명령문을 작성해주면 되며, 이후 구분자로 스토어드 프로시저가 끝났음을 명시해주면 된다.

 

 

 

1-2. 입출력 매개변수

스토어드 프로시저에는 입출력 매개변수를 설정할 수 있다. 먼저 입력 매개변수에 대해 알아보자.

 

 

 

입력 매개변수

먼저 입력 매개변수란, 스토어드 프로시저를 호출할 때 외부에서 전달되는 값으로, 스토어드 프로시저가 작업을 수행할 때 필요한 데이터를 제공하는 역할을 한다. 매개변수를 명시해줄 땐 프로시저의 이름 뒤 괄호 안에 위치해주면 된다.

그렇다면 입력 매개변수의 형식을 알아보자.

IN in_arg_name data_type

 

또한 입력 매개 변수를 사용하기 위해선 스토어드 프로시저를 호출 시에 전달 값을 넣어주면 된다.

CALL proc_name(value);

 

 

 

출력 매개변수

출력 매개변수는 스토어드 프로시저에서 처리된 결과를 반환하는 데 사용되는 매개변수로, 스토어드 프로시저 내에서 계산된 결과를 반환하는 그릇이라 생각하면 쉽다. 출력 매개변수의 형식은 다음과 같다.

OUT out_arg_name data_type

 

출력 매개변수를 호출하는 방법은 다음과 같다.

CALL proc_name(@variable_name);
select @variable_name;

 

일반적으로 스토어드 프로시저에서 계산된 결과 값이 출력 매개변수에 저장되어 나오기 때문에, SELECT문으로 변수에 저장된 값을 확인하면 된다.

 

 

 

스토어드 프로시저에는 IF, WHILE문, 동적 SQL 등을 활용하여 다양하면서도 편리하게 사용할 수 있으므로, 상황에 알맞게 잘 사용하도록 하자.

 

 

 

 

2. 스토어드 함수 (Stored Function)

스토어드 함수는 이름에서 볼 수 있듯 스토어드 프로시저와 역할이 유사하다. 그러나 다른 점은 스토어드 함수는 무조건 REUTNS 예약어를 통해 값을 반환해야 한다는 것이다. 

우리는 앞선 챕터들에서 SUM(), CAST(), CONCAT() 등의 내장 함수를 사용해왔다. 그러나 기본으로 제공되는 내장 함수 이외에도 우리가 필요로 하는 함수들은 굉장히 많다. 이를 위해 우리는 사용자 정의 함수인 스토어드 함수를 사용할 수 있는 것이다.

 

스토어드 함수의 기본 형식은 다음과 같다.

DELIMITER $$
CREATE FUNCTION func_name (argument)
	RETURNS return_type
BEGIN

    sql_command;
    sql_command;
    
END $$
DELIMITER ;
SELECT func_name();

 

스토어드 함수도 스토어드 프로시저와 동일하게 CREATE문을 사용하여 생성할 수 있다. 그러나 프로시저와 다른 점은, 함수는 매개변수로 입력 매개변수만을 받는다는 점이다. 해당 이유로, 앞에 IN은 생략하여 작성한다. 또한 RETURN 예약어를 통해 해당 함수가 반환할 결과 값에 대한 반환 타입을 지정해준다. 마지막으로 프로시저는 호출 시 CALL을 이용하지만 함수는 SELECT를 사용하며, 프로시저 내부에는 SELECT문을 사용할 수 있지만 함수 내부에는 SELECT문을 사용할 수 없다.

 

 

 

 

3. 커서 (Cursor)

커서는 테이블에서 한 행씩 순차적으로 처리할 수 있도록 도와주는 데이터베이스 객체이다.  커서는 대량의 데이터나 복잡한 데이터를 처리할 때 유용하며, 특히나 루프를 통해 행 단위로 데이터를 처리할 때 많이 사용된다.

 

커서는 다음의 순서로 작동한다.

1. 커서 선언하기
2. 반복 조건 선언하기
3. 커서 열기
4. 데이터 가져오기
5. 데이터 처리하기
6. 커서 닫기

커서는 처음 행부터 마지막 행까지 다 읽기 위하여 4번, 5번을 반복한다.

 

 

 

3-1. 커서 선언하기

커서를 선언하는 방법은 다음과 같다.

DECLARE cursor_name CURSOR FOR 
    SELECT column_name FROM table_name;

 

먼저 사용할 커서를 선언해주어야 하는데, 일반 변수 선언과는 다르게 해당 변수가 커서로 사용될 것임을 명시해주어야 한다. 또한 해당 커서를 어떠한 테이블에서 어떠한 열을 읽는데 사용하는 커서인지를 작성해준다.

 

 

 

3-2. 반복 조건 선언하기

마지막 행까지 읽고 나면, 해당 커서는 테이블을 읽는 것을 멈추어야 한다. 이를 위한 반복 조건을 우리는 설정해주어야 한다.

DECLARE CONTINUE HANDLER
   FOR NOT FOUND SET endOfRow = TRUE;

 

먼저, DECLARE CONTINUE HANDLER는 반복 조건을 준비하는 예약어이다. 또한 FOR NOT FOUND SET 예약어는 테이블의 마지막 행에 도착했을 때 다음의 SQL문을 수행하도록 한다.

(위의 예시에서 endOfRow는 행의 마지막에 도착했는지 확인하는 변수이며, 기본값은 FALSE로 설정해두었다.)

 

 

 

3-3. 커서 열기

커서를 열 때엔 OPEN을 이용하여 열어주면 된다.

OPEN cursor_name;

 

 

 

3-4. 행 반복하기

위의 모든 조건을 설정하였다면, 이제 반복 조건을 설정해주어야 한다. 커서가 돌아야 할 반복 조건은 다음과 같이 선언한다.

cursor_loop: LOOP
    FETCH cursor_name into value_name;
    
    IF ... THEN
        LEAVE cursor_loop
    END IF;
    
END LOOP cursor_loop;

 

먼저, 커서가 돌아야할 반복문의 처음과 끝을 'cursor_loop: LOOP ... END LOOP cursor_loop'로 나타내어 준다. 다음으로 내부에 IF문을 작성하여 마지막 행에 다다랐을 때 해당 루프문을 빠져나갈 수 있는 조건을 추가해준다. if문 외에 추가적으로 진행해야 하는 연산이 있다면, loop문 안에 작성해주면 된다.

 

 

 

3-5. 커서 닫기

마지마으로, 커서를 다 사용하면 CLOSE로 커서를 닫아주면 된다.

CLOSE cursor_name;

 

 

 

 

앞서 설명한 내용을 스토어드 프로시저와 함께 사용한다면 유용하게 사용할 수 있다.

 

 

 

 

4. 트리거

트리거는 말 그대로 특정 행동이 발현되면 자동으로 실행되는 프로그래밍 기능이다. 특정 행동 뒤 같이 진행되어야 하는 행동이 있는데, 사람이 직접 하면 까먹고 놓칠 수 있는 부분을 트리거로 자동으로 설정해놓으면 이에 대한 실수를 방지할 수 있다. (이를 데이터에 무결성이라고도 한다.)

트리거는 특정 이벤트가 발생할 시에만 작동하며, 우리가 임의로 트리거만 작동시킬 수는 없다. 즉, 트리거를 작동시키기 위한 사전 조건도 무조건 설정해주어야 한다.

 

트리거의 기본 형식은 다음과 같다.

DELIMITER $$
CREATE TRIGGER trigger_name
    [BEFORE | AFTER] [INSERT | UPDATE | DELETE]
    ON table_name
    FOR EACH ROW
BEGIN
    
    sql_command;
    sql_command;
   
END $$
DELIMITER ;

 

먼저, [BEFORE | AFTER]는 트리거를 특정 이벤트 전에 실행할지, 이벤트 후에 실행할지 지정하는 예약어이다. 그 뒤에는 트리거가 반응할 이벤트의 유형을 지정해주면 된다.

FOR EACH ROW는 하나의 행에 대해 하나의 트리거를 실행시킨다는 의미이다. 트리거를 생성할 땐 해당 문장을 꼭 넣어주어야 한다.

'BEGIN ... END'는 트리거가 실질적으로 수행할 명령들을 모아놓은 곳이다. 이 부분은 상황에 알맞게 작성해주면 된다.

 

 

 

이러한 트리거를 잘 사용하면 데이터의 무결성을 지키면서, 특정 행동을 자동화시킬 수 있다.

 

 

 

 

 

5. 6주차 숙제

5-1. 기본 숙제

백업 테이블 생성

업데이트에 대한 트리거 생성

 

 

5-2. 추가 숙제

코드

import pymysql
from tkinter import *
from tkinter import messagebox

def insertData() :
    conn, cur = None, None
    data1, data2, data3, data4 = "", "", "", ""
    sql = ""

    conn = pymysql.connect(host = "127.0.0.1", user = 'root', password = 'Chosun12@@', 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 = 'Chosun12@@', db = 'soloDB', charset = 'utf8')
    cur = conn.cursor()
    cur.execute("select * from userTable")

    strData1.append("사용자 ID")
    strData1.append("----------")
    strData2.append("사용자 이름")
    strData2.append("----------")
    strData3.append("사용자 이메일")
    strData3.append("----------")
    strData4.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()

 

 

실행 화면