[혼공학습단 12기] 혼자 공부하는 SQL - 6주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 1주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 2주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 3주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 4주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 5주차
- 스토어드 프로시저
SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 내도록 한다.
MySQL에서 제공하는 프로그래밍 기능으로, 쿼리문의 집합이다.
자주 사용하는 일반적인 쿼리를 반복하는 것보다 스토어드 프로시저로 묶어놓고 필요할 때마다 간단히 호출하면 훨씬 편리하게 MySQL을 운영할 수 있다.
* 스토어드 프로시저도 데이터 베이스의 개체 중 한가지로, 테이블 처럼 각 db 내부에 저장된다.
delimiter $$
create procedure 스토어드_프로시저_이름 (in 또는 out 매개변수)
begin
-- sql 프로그래밍 코드 --
end $$
delimiter ;* delimiter은 구분자라는 의미로, 스토어드 프로시저 내부의 많은 sql문의 끝에 세미콜론;을 사용한다. 세미콜론이 sql문의 끝인지 스토어드 프로시저의 끝인지 모호해질 수 있으므로, 구분자를 $$로 바꾼다.
세미콜론은 sql의 끝으로만 표시하고 $$은 스토어드 프로시저의 끝으로 사용한다.
마지막 행에서 delimiter을 세미콜론으로 바꿔주면 원래대로 MySQL의 구분자가 세미콜론으로 돌아온다.
create procedure로 프로시저를 만들고, 실행(호출)하기 위해 다음과 같이 입력한다.
call 스토어드_프로시저_이름();
- 스토어드 프로시저 실습
스토어드 프로시저를 실행 시 입력 매개변수를 지정할 수 있는데,
쉽게는 자판기를 사용할 때 동전을 넣고 버튼을 누르는 것과 같다.
in 입력_매개변수_이름 데이터_형식
입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 괄호안에 값을 전달해야 한다.
call 프로시저_이름(전달_값);
스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수 있는데
쉽게는 커피 자판기에서 미리 준비되어 있는 컵이라고 할 수 있다.
out 출력_매개변수_이름 데이터_형식
출력 매개변수에 값을 대입하기 위해서는 주로 select ~ into 문을 사용하는데, 다음과 같이 출력 매개 변수가 있는 스토어드 프로시저를 실행한다.
call 프로시저 이름(@변수명);
select @변수명;
입력매개변수를 활용하기 위해 2개의 입력 매개변수가 있는 스토어드 프로시저를 생성한다.
drop procedure if exists user_proc2;
delimiter $$
create procedure user_proc2( -------
in usernumber int,
in userheight int ) ------
begin
select * from member ----- 인원을 6으로, 평균키를 165로 전달받고 대입된다.
where mem_number > usernumber and height > userheight; -----
end $$
delimiter ;
call user_proc2(6, 165); ----- 그룹 조회
다음과 같이, 출력 매개변수가 있는 스토어드 프로시저를 생성한다.
(notable 이 없는 상태에서 만들어도 오류없이 만들어진다.)
스토어드 프로시저를 만드는 시점에서는 존재하지 않는 테이블을 사용해도 되지만, call로 실행하는 시점에는 사용한 테이블이 있어야 한다.
drop procedure if exists user_proc3;
delimiter $$
create procedure user_proc3(
in txtvalue char(10),
out outvalue int ) ------- 출력 매개변수인 outvalue를 지정
begin
insert into notable values(null, txtvalue);
select max(id) into outvalue from notable; -------- into outvalue 구문을 통해, outvalue에 id 열의 최대값을 저장한다.
end $$
delimiter ;---notable 테이블 생성----
create table if not exists notable(
id int auto_increment primary key,
txt char(10)
);call user_proc3('테스트1', @myvalue);
select concat('입력된 id값 ==>', @myvalue);
위와 같이 스토어드 프로시저를 호출하면, 호출한 횟수만큼 값이 계속 2,3,4... 로 증가한다.
- 동적 sql 활용
테이블은 고정된 것이 아니라, 테이블 이름을 매개변수로 전달 받아서 해당 테이블을 조회한다.
drop procedure if exists dynamic_proc;
delimiter $$
create procedure dynamic_proc(
in tablename varchar(20)
)
begin
set @sqlquery = concat('select * from ', tablename); ---- 넘겨받은 테이블 이름을 @sqlquery 변수에 select 문으로 문자열을 생성하는데, 결굴 select * from member을 생성
prepare myquery from @sqlquery; ------ select 문자열을 준비하고 실행
execute myquery;
deallocate prepare myquery; ----- 사용한 myquery 해제
end $$
delimiter ;
call dynamic_proc ('member');
- 스토어드 함수
스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공한다.
스토어드 프로세저와 모양은 비슷하지만, 세부적으로는 용도가 다르며, returns 예약어를 통해서 하나의 값을 반환해야 하는 특징을 가진다.
커서는 스토어드 프로시저 안에서 한 행씩 처리 할 때 사용하는 프로그래밍 방식이다.
delimiter $$
create function 스토어드_함수_이름(매개변수)
returns 반환형식
begin
이 부분에 프로그래밍 코딩
return 반환값;
end $$
delimiter ;
select 스토어드_함수_이름();
스토어드 함수의 매개변수는 모두 입력 매개변수이고, in을 붙이지 않는다.
스토어드 프로시저는 call로 호출하지만, 스토어드 함수는 select 문안에서 호출된다.
스토어드 프로시저 안에서는 select 문을 사용할 수 있지만, 스토어드 함수 안에서는 select를 사용할 수 없다.
스토어드 프로시저는 여러 sql문이나 숫자계산 등 다양한 용도로 사용하지만, 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용한다.
set global log_bin_trust_function_creaters = 1;
sql을 통해 스토어드 함수 생성 권한을 허용해주어야 하는데, mysql에서 한번 허용 시 이후 다시 사용하지 않아도 된다.
사용하지 않으면 아래와 같은 오류가 발생한다.
Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
데뷔연도를 입력하면 활동기간을 출력해주는 함수를 다음과 같이 스토어드 함수를 사용하여 생성하였다.
drop function if exists calcyearfunc;
delimiter $$
create function calcyearfunc(dyear int) ----- 데뷔연도를 매개변수로 받았다.
returns int
begin
declare runyear int; ----- 활동기간(연도)
set runyear = year(curdate()) - dyear; ----- 계산 진행 (현재 연도 - 데뷔 연도 = 활동한 햇수)
return runyear; ----- 계산된 결과 반환
end $$
delimiter ;select calcyearfunc(2010) as '활동 햇수';
* 각 함수의 반환값을 select ~ into ~ 로 저장했다가 사용할 수 있다.
select mem_id, mem_name, calcyearfunc(year(debut_date)) as '활동 햇수'
from member;
- 커서
커서를 사용해서, 테이블에서 한행씩 처리할 수도 있다.
커서 선언 -> 반복조건 선언 -> 커서 열기 -> 데이터 가져오기 -> 데이터 처리하기 -> 커서 닫기
4,5번을 반복한다.
커서는 대부분 스토어드프로시저와 함께 사용된다.
use market_db;
drop procedure if exists cursor_proc;
delimiter $$
create procedure cursor_proc()
begin
declare memnumber int; ---- 1) 사용할 변수 준비 (회원의 인원, 읽은 행의 수, 전체 인원의 합계)
declare cnt int default 0;
declare totnumber int default 0;
declare endofrow boolean default false; ---- 행의 끝을 파악하기 위한 변수(처음에는 행의 끝이 아니므로 false로 초기화)
declare membercursor cursor for ---- 2)커서 선언 (커서 = select문)
select mem_number from member; ---- 회원 테이블을 조회하는 구문이 커서이고, 커서 이름은 membercursor
declare continue handler ----- 3) 반복 조건 선언 (행이 끝나면 어떻게 설정해야 더이상 반복하지 않을지 생각): declare continue handler는 반복조건을 준비하는 예약어
for not found set endofrow = true; ---- 행의 끝에 다다르면 앞에서 선언한 endofrow 변수가 true가 된다. for not found는 더 이상 행이 없을 때 이어진 문장을 수행한다. 행이 끝나면 true를 대입
open membercursor; ----- 4) 커서 열기
cursor_loop: loop ---- 5) 행 반복: 커서의 끝까지 한행씩 접근해서 반복
fetch membercursor into memnumber; --- fetch는 한행씩 읽어오기로, 2번에서 선언 시 인원수 행을 조회했으므로 memnumber 변수에는 각 회원의 인원수가 한번에 하나씩 저장된다.
if endofrow then
leave cursor_loop; --- leave는 반복할 이름을 빠져나가는데, 행의 끝에 다다르면 3번에 의해 endofrow가 true로 변경되고 반복하는 부분을 빠져나간다.
end if;
set cnt = cnt+1; --- 읽은 행의 수(cnt)를 하나씩 증가
set totnumber = totnumber + memnumber; ---- 인원수를 totnumber에 누적
end loop cursor_loop; ---- cursor_loop: loop 부터 사이에 있는 문장을 반복
select (totnumber/cnt) as '회원의 평균 인원 수'; ---- 반복을 빠져나오면 최종적으로 회원의 평균 인원수 계산, 여기서는 누적된 총 인원수를 읽은 행의 수로 나눈다.
close membercursor; --- 커서 닫기
end $$
delimiter ;
스토어드 프로시저를 실행하면, 회원의 평균 회원수 값이 나온다.
- 자동 실행 되는 트리거
트리거: 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지
insert, update, delete 문이 작동할 때 자동으로 실행되는 프로그래밍 기능으로, 트리거 활용 시 데이터가 삭제될 때 해당 데이터를 다른 곳에 자동으로 백업할 수 있다.
트리거는 스토어드 프로시저와 문법이 비슷하나, call문으로 직접 실행할 수 없고
테이블에 dml 문의 이벤트가 발생될 때만 자동으로 실행된다. in, out 매개변수도 사용불가하다.
트리거 사용 전에 간단히 테이블을 생성한다.
use market_db;
create table if not exists trigger_table (id int, txt varchar(10));
insert into trigger_table values(1, '레드벨벳');
insert into trigger_table values(2, '잇지');
insert into trigger_table values(3, '블랙핑크');
간단한 트리거를 테이블에 부착한다.
drop trigger if exists mytrigger;
delimiter $$
create trigger mytrigger
after delete
on trigger_table
for each row
begin
set @msg = '가수 그룹이 삭제됨' ; --트리거 실행시 작동되는 코드
end $$
delimiter ;
delete문이 발생된 이후에 작동되는데 for each row는 각 행마다 적용시킨다는 의미로 트리거에는 항상 사용한다.
begin 이후가 트리거에서 실제로 작동할 부분이다.
set @msg = '';
delete from trigger_table where id = 4;
select @msg;
트리거문을 작동 시키기 위해 delete문을 테이블에 적용시키는데, 다음과 같은 오류가 발생한다.
| 0 | 128 | 23:09:22 | delete from trigger_table where id = 4 | Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. | 0.000 sec |
set sql_safe_updates=0;
위의 안전모드 해제를 통해 오류를 해결하면 아래와 같이 트리거문이 발동된 결과가 출력된다.
- p. 363: market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거
트리거는 테이블에 입력/수정/삭제 되는 정보를 백업하는 용도로 활용가능하다.
예를 들면, 데이터를 변경한 사용자와 시간등을 기록하는 경우에 해당한다.
먼저, 간단한 가수 테이블을 생성한다.(복사)
use market_db;
create table singer (select mem_id, mem_name, mem_number, addr from member);
* create table ~ (select ~) 문 : 테이블을 복사하여 새로운 테이블을 생성
* 기본 키 정의는 복사되지 않음.
아래와 같이 백업 테이블을 생성한다.
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) -- 변경한 사용자
);
아래와 같이, 변경과 삭제가 발생할 때 작동하는 트리거를 singer table에 부착한다.
변경이 발생했을 때 작동하는 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 테이블: mysql에서 내부적으로 제공되는 테이블
old 테이블은 변경 되기 전 데이터가 잠시 저장되는 임시 테이블이다.
old 테이블에 update 문이 작동되면 begin 다음의 2행으로 인해서
업데이트 되기 전의 데이터가 백업 테이블(backup_singer)에 입력되고 원래 데이터가 보존된다.
curdate()는 현재 날짜를, current_user()은 현재 작업중인 사용자를 알려준다.
* select문을 제외한 dml 문을 사용한 작업이 수행되면 임시로 사용되는 시스템 테이블이 2개 있는데, new와 old이다. 알아서 생성되므로 사용자는 신경쓰지 않아도 된다. insert (새값) 형태로 테이블에 새 값이 바로 들어가는데, 새 값은 테이블에 들어가기 전에 new 테이블에 잠시 들어가 있는다.
삭제가 발생했을 때 작동하는 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 ;
* 하나의 테이블에 여러개의 트리거를 부착할 수 있다.
아래와 같이 데이터를 변경한다.
update singer set addr = '영국' where mem_id = 'blk';
delete from singer where mem_number >= 7;
만약에 truncate table singer;과 같이, truncate 문을 통해서 삭제를 하면 트리거가 작동하지 않는다.
- 파이썬 개발 환경 (파이썬과 MySQL 연동)
파이썬에서 mysql을 인식하게 하려면 pymysql이라는 외부 라이브러리를 설치해야한다.
다음과 같은 단계를 통해서 쇼핑몰 db를 구축하고 사용한다.
my sql 연결 - 커서 생성 - 테이블 생성 - 데이터 입력 - 입력한 데이터 저장 - mysql 연결 종료
연동 프로그램을 위한 쇼핑몰을 생성하기 위해 다음과 같이 혼공 쇼핑몰 db를 생성한다.
drop database if exists solodb;
create database solodb;
이후, 파이썬에서 mysql을 사용하기 위해서는 pymysql을 임포트하고, pymysql.connect()를 사용하여 데이터베이스와 연동해야 한다. 여기서는 conn이라는 변수를 db와 연결된 연결자로 사용한다.
pymysql.connect(host = 서버ip주소, user=사용자, password=암호, db=데이터베이스, charset=문자세트)
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='0000', db='solodb', charset='utf8')
아래와 같이 연결자에 커서를 생성한다. cur 변수를 사용한다.
cur = conn.cursor()
이후, 테이블을 생성한다.
테이블을 생성하는 sql문을 커서이름. execute()함수의 매개변수로 넘겨주면, sql문이 데이터베이스에 실행된다.
cur.execute("create table usertable (id char(4), username char(15),
email char(20), birthyear int)")
데이터는 필요한 만큼 입력한다. 데이터 입력도 sql문을 사용하므로 커서이름.execute() 함수를 사용한다.
cur.execute("insert into usertable values( 'hong', '홍홍홍', 'hong@naver.com', 1980)")
cur.execute("insert into usertable values( 'kong', '공공공', 'kong@naver.com', 1990)")
현재는 임시로 저장된 상태로 이를 확실하게 저장한다. 이것을 커밋이라고 부른다.
conn.commit()
데이터베이스를 모두 사용 후, 연결한 데이터베이스를 닫는다.
conn.close()
- 완전한 데이터 입력 프로그램 & 조회 프로그램
--- 데이터 입력 프로그램 ---
--- 사용자가 반복해서 데이터를 입력 ---
import pymysql
# 전역 변수 선언부
conn, 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()
while (True): ---- 무한 반복(data 1~4 입력 받기)
data1 = input('사용자 ID ==> ')
if data1 == "":
break;
data2 = input('사용자 이름 ==> ')
data3 = input('사용자 이메일 ==> ')
data4 = input('사용자 출생연도 ==> ') ---- 반복
sql = "INSERT INTO userTable VALUES('" + data1 + "', '" + data2 + "', '" + data3 + "', " + data4 + ")" --- insert 문으로 입력한 데이터를 sql 변수에 문자열로 만든다.
cur.execute(sql) --- 생성한 문자열을 실행해서 데이터 입력
conn.commit() --- 입력한 데이터 저장
conn.close() --- 연결된 데이터베이스 닫기
데이터 4는 정수 이므로 작은 따옴표로 묶지 않는다.
--- 데이터 조회 프로그램 ---
import pymysql
# 전역 변수 선언부
conn, cur = None, None
data1, data2, data3, data4 = "", "", "", ""
row = "" --- 행을 하나씩 읽어오기 위한 row 변수 설정
# 메인 코드
conn = pymysql.connect(host='127.0.0.1', user='root', password='0000', db='soloDB', charset='utf8')
cur = conn.cursor()
cur.execute("SELECT * FROM userTable") ---- select 문으로 테이블을 조회하고, 조회한 결과는 cur 변수에 저장된다.
print('사용자ID 사용자이름 이메일 출생연도')
print('-------------------------------------------')
while (True):
row = cur.fetchone() --- fetchone() 함수로 결과를 한 행씩 추출하는데, while 문안에 있으므로 무한반복(조회한 결과의 모든 행을 추출)
if row==None: --- 조회결과가 없으면 none값 반환 후 while문 종료
break
data1 = row[0]
data2 = row[1]
data3 = row[2]
data4 = row[3]
print("%5s %15s %20s %d"%(data1, data2, data3, data4)) --- fetchone() 함수로 조회한 결과가 row 변수에는 튜플 형식으로 각 행 데이터가 저장되는데, data1,2,3,4와 같이 각 형식에 맞추어 한 줄에 출력
conn.close()
* 튜플은 리스트와 비슷한 구조이나, 읽기 전용으로 변경 불가하다. 리스트는 [ ] 로 구성되고, 튜플은 () 로 구성된다. 그 외에는 리스트와 사용법이 비슷하다.
- p. 402 GUI 응용 프로그램
import pymysql
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.conmmit()
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.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()
# 메인 코드부
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()