📔설명
스토어드 프로그램 장단점, 문법, 보안 옵션 및 주의사항 등을 알아보자
🌺스토어드 프로그램 장단점
스토어드 프로그램 : 스토어드 루틴이라고도 하며, 스토어드 프로시저와 스토어드 함수, 트리거와 이벤트 등 모두 아우르는 명칭
뷰 : 스토어드 쿼리라고 불림
1. 스토어드 프로그램 장점
- 데이터베이스 보안 향상
- 기능의 추상화
- 네트워크 소요 시간 절감
- 절차적 기능 구현
- 개발 업무의 구분
2. 스토어드 프로그램 단점
- 낮은 처리 성능
- 애플리케이션 코드의 조각화
🌻스토어드 프로그램 문법
헤더 부분 : 보안이나 작동 방식과 관련된 옵션 명시, 이름, 입출력 값
본문 부분 : 실행 내용 작성
1. 스토어드 프로시저
서로 데이터를 주고받아야 하는 여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행
=> 독립적으로 호출
=> SELECT나 UPDATE 같은 SQL 문장에서 참조 불가능
스토어드 프로시저 생성 및 삭제
create procedure 명령으로 생성
create procedure sp_sum(in param1 integer, in param2 integer, out param3 integer)
begin
set param3=param1+param2;
end;;- 스토어드 프로시저는
기본 반환값이없음으로, 내부에서return 명령 사용 X - 파라미터 타입 :
IN타입,OUT타입,INOUT타입
MySQL 서버가 create 명령의 끝을 정확히 판별할 수 있게 별도의 문자열 구분자를 설정해야 함
DELIMITER : 명령의 끝을 알려주는 종료 문자를 변경하는 명령어
delimiter ;;
#이후 위 프로시저 실행
#스토어드 프로그램 생성 끝나면 다시 기본 종료 문자로 복구
delimiter ;ALTER PROCEDURE 명령으로 프로시저 변경
#보안 및 작동 방식 특성 변경 시에만 사용 가능
alter procedure sp_sum SQL SECURITY DEFINER;DROP PROCEDURE 명령으로 프로시저 삭제
#프로시저 내용 수정시에도 삭제 후 재생성 해야함
drop procedure sp_sum;스토어드 프로시저 실행
SELECT 쿼리에 사용 못하며, 반드시 CALL 명령어로 실행
#파라미터 제공 중 OUT 이나 INOUT 타입은 무조건 세션 변수 필요함
set @result:=0;
select @result;
call sp_sum(1,2,@result);
select @result;
스토어드 프로시저의 커서 반환
명시적으로 커서를 파라미터로 전달받거나 반환할 수 없음
=> 커서 오픈하지 않거나 SELECT 쿼리의 결과 셋을 페치(Fetch) 하지 않으면 결과 셋은 바로 클라이언트로 전송
=> 스토어드 프로시저 내에서 단순히 SELECT 쿼리만 사용하면 결과를 화면상에서 확인 가능
=> 디버깅 용도로 사용
delimiter ;;
create procedure sp_selectEmployees(in in_empno integer)
begin
select * from employees where emp_no=in_empno;
end;;
delimiter ;call sp_selectEmployees(10001);
스토어드 프로시저 딕셔너리
information_schema 데이터베이스의 routines 뷰를 통해 스토어드 프로시저 정보 조회
select routine_schema, routine_name, routine_type
from information_schema.routines
where routine_schema='employees';
2. 스토어드 함수
하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용
#사원을 2명씩 가져오는 기능
select dept_no, sf_getRecentEmp(dept_no) #최근 2명의 사원번호만 select하고 문자열로 결합
from dept_emp
group by dept_no;SQL 문장의 일부로 사용 가능
스토어드 함수 생성 및 삭제
CREATE FUNCTION 명령으로 생성
=> 모든 입력 파라미터는 읽기 전용이라 형식 지정X
=> returns를 이용해 반환되는 값의 타입 명시
# SET GLOBAL log_bin_trust_function_creators = 1;
create function sf_sum(param1 integer, param2 integer)
returns integer
begin
declare param3 integer default 0;
set param3=param1+param2;
return param3;
end;;함수 본문에서 사용 못하는 것들
프리페어 스테이트먼트사용 불가ROLLBACK/COMMIT을 유발하는 SQL 문장 사용 불가재귀 호출사용 불가- 스토어드 함수 내에
프로시저 호출 불가 결과 셋반환하는 SQL 문장 사용 불가
ALTER FUNCTION 명령으로 변경
#특성만 변경 가능
alter function sf_sum sql security definer;DROP FUNCTION 명령으로 삭제
drop function sf_sum;스토어드 함수 실행
SELECT 문장을 이용해 실행
select sf_sum(1,2) as sum;
3. 트리거
테이블의 레코드가 저장되거나 변경될 때 미리 정의해둔 작업을 자동으로 실행해주는 프로그램
트리거 생성
CREATE TRIGGER 명령으로 실행
#employees 테이블의 레코드가 삭제되기 전에 실행
create trigger on_delete before delete on employees
for each row
begin
delete from salaries where emp_no=OLD.emp_no;
end ;;
#OLD : 변경되기 전 레코드
#NEW : 변경될 레코드트리거 본문 블록에서 사용 못하는 작업
외래키관계에 의해 자동으로 변경되는 경우 호출XROLLBACK/COMMIT을 유발하는 SQL 문장 사용 불가return 사용 불가, 트리거 종료시leave 명령사용레코드 기반의 복제(ROW)에서는트리거를 기동X,문장 기반의 복제(Statement)에서는트리거 기동mysql과information_schema, performance_schema에 존재하는 테이블은 트리거 생성 불가
트리거 실행
트리거가 생성된 테이블에 직접 레코드를 INSERT, UPDATE, DELETE 수행해서 작동 확인
트리거 딕셔너리
information_schema의 triggers 뷰를 통해 조회
4. 이벤트
주어진 특정한 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능
=> MySQL 서버 설정 파일에서 event_scheduler를 ON이나 1로 활성화해야 사용 가능
show global variables like 'event_scheduler';
information_schema 데이터베이스의 events 뷰에서 확인 가능
이벤트 생성
일회성 이벤트와 반복성 이벤트로 나눌 수 있음
#일회성 이벤트 : on schedule at 명시
create event onetime_job
on schedule at current_timestamp+interval 1 hour
do
insert into daily_rank_log values(now(),'Done');
# 현재로부터 한시간 뒤에 한번 실행됨#반복성 이벤트
create event daily_ranking
on schedule every 1 day starts '2020-09-07 01:00:00' ends '2021-01-01 00:00:00'
do
insert into daily_rank_log values(now(),'Done');ON COMPLETION PRESERVE 옵션과 함께 이벤트 등록시 이벤트 실행 완료돼도 삭제 안됨
이벤트 실행 및 결과 확인
트리거 처럼 특정한 사건이 발생해야 실행되기 때문에 직접 실행해봐야 함
delimiter ;;
create table daily_rank_log(
exec_dttm datetime,
exec_msg varchar(50)
);;
create event daily_ranking
on schedule at current_timestamp+interval 1 minute
on completion preserve
do begin
insert into daily_rank_log values(now(),'Done');
end ;;select * from test.daily_rank_log;
이벤트 딕셔너리
information_schema의 events 뷰를 통해 메타 정보와 마지막 실행 이력을 보여줌
select * from information_schema.events \G
5. 스토어드 프로그램 본문(Body) 작성
BEGIN ... END 블록과 트랜잭션
본문(Body)은 BEGIN으로 시작해서 END로 끝나며, 하나의 BEGIN ... END 블록은 또 다른 여러 개의 BEGIN ... END 블록을 중첩해서 포함 가능
트랜잭션 시작을 위해선 start transaction 명령으로 시작해야 함
=> 스토어드 프로시저나 이벤트의 본문에서만 사용 가능
=> 스토어드 함수나 트리거는 프로시저 외부에서 트랜잭션 완료만 가능
변수
스토어드 프로그램에서 사용하는 변수는 로컬 변수라고 표현
=> DECLARE 명령으로 정의
=> 타입이 함께 명시
=> SET 또는 SELECT ... INTO ... 문장으로 값 할당
=> 사용자 변수보다 빠름
#로컬 변수 정의
declare v_name varchar(50) default 'Matt';
declare v_email varchar(50) default 'matt@email.com';
#로컬 변수에 값 할당
set v_name='Kim', v_email='kim@email.com';입력 파라미터, 로컬 변수, 테이블 칼럼명이 모두 같은 이름일 때 우선순위
1. 로컬 변수
2. 입력 파라미터
3. 테이블의 칼럼
제어문
begin ... end 블록 내부에서만 사용 가능
IF ... ELSEIF ... ELSE ... END IF
#큰 값 반환 함수
create function sf_greatest(p_value1 int, p_value2 int)
returns int
begin
if p_value1 is null then
return p_value2;
elseif p_value2 is null then
return p_value1;
elseif p_value1>=p_value2 then
return p_value1;
else
return p_value2;
end if;
end;;CASE WHEN ... THEN ... ELSE ... END CASE
case
when 비교조건식 1 then 처리내용1
when 비교조건식 2 then 처리내용2
else 처리내용3
end case;반복 루프
LOOP, REPEAT, WHILE 구문 사용 가능
=> LOOP : 반복 조건 명시X, 루프 벗어나기 위해 LEAVE 명령 사용
=> REPEAT : 본문 처리 후 반복 조건 체크
=> WHILE : REPEAT과 반대로 실행
#LOOP
create function sf_fact1(p_max int)
returns int
begin
declare v_fact int default 1;
fact_loop:LOOP #LOOP의 이름(레이블)
set v_fact=v_fact*p_max;
set p_max=p_max-1;
if p_max<=1 then
leave fact_loop;
end if;
end loop;
return v_fact;
end;;#REPEAT
create function sf_fact2(p_max int)
returns int
begin
declare v_fact int default 1;
repeat
set v_fact=v_fact*p_max;
set p_max=p_max-1;
until p_max<=1 end repeat; #해당 내용이 TRUE인 경우 루프 벗어남
return v_fact;
end;;#WHILE
create function sf_fact3(p_max int)
returns int
begin
declare v_fact int default 1;
while p_max>1 do #조건이 TRUE 인 동안 실행
set v_fact=v_fact*p_max;
set p_max=p_max-1;
end while;
return v_fact;
end;;핸들러와 컨디션을 이용한 에러 핸들링
핸들러 : 이미 정의된 컨디션 또는 사용자가 정의한 컨디션을 어떻게 처리할지 정의하는 기능
컨디션 : SQL 문장의 처리 상태에 대해 별명을 붙이는 것
SQLSTATE와 에러 번호(Error No)
ERROR ERROR-NO (SQL-STATE) : ERROR-MESSAGE 형태
ERROR-NO: 에러 식별 번호SQL-STATE: ANSI 표준을 준수하는상태를 의미하는 코드
=>앞 두글자가00-정상 처리,01-경고,02-not found,그외-DBMS 별 에러케이스ERROR-MESSAGE: 에러 메시지
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
핸들러를 SQLSTATE로 정의하는 것이 좋음
핸들러
#예외 핸들링
declare handler_type handler
for condition_value [, condition_value] ... handler_statementshandler_type
continue:handler_statements실행 후스토어드 프로그램의마지막 실행 지점으로 돌아가서나머지 코드처리exit:handler_statements처리 후 해당 핸들러가 정의된begin .. end벗어남
컨디션 값(Condition value)
SQLSTATE: 해당 이벤트의SQLSTATE 값이일치할 때 실행SQLWARNING:SQLSTATE 값이01로 시작하는 이벤트NOT FOUND:SQLSTATE 값이02로 시작하는 이벤트SQLEXCEPTION:경고,not found,00외의모든 케이스에러 코드 값:에러 번호 값을 비교해서 실행사용자 정의 컨디션
handler_statements
- 이벤트 발생 시 이벤트에 대한
처리 코드
declare continue handler for sqlexception set error_flag=1;컨디션
핸들러에서 본 sqlwarning이나 sqlexception 같은 것이 MySQL이 미리 정의해 둔 컨디션
declare condition_name condition for condition_valuecondition_value
- MySQL의
에러 번호를 입력하면 되지만,여러 개 동시에 명시X SQLSTATE입력시SQLSTATE 키워드입력 후값입력
declare dup_key condition for 1062;시그널을 이용한 예외 발생
시그널(SIGNAL) : 사용자가 직접 예외나 에러를 발생시키는 것
스토어드 프로그램의 BEGIN ... END 블록에서 SIGNAL 사용
create function sf_div(p_dividend int, p_divisor int)
returns int
begin
declare null_divisor condition for sqlstate '45000';
if p_divisor is null then
signal null_divisor
...~~
end;;SIGNAL 명령은 SQLSTATE와 연결돼야 함
핸들러 코드에서 SIGNAL 사용
declare exit handler for sqlexception
begin
signal sqlstate '45000'
set message_text='Can not remove user info', mysql_errorno=9999;
end;커서
JDBC의 결과 셋(ResultSet)과 비슷하나 제약적
전진읽기만 가능커서의 칼럼을 바로업데이트 하는 것이불가능
커서 구분
-
센서티브 커서: 일치하는 레코드에 대한 정보를 실제 레코드의포인터만으로 유지
=> 칼럼의 데이터변경및삭제가능
=> 커서 오픈 빠름 -
인센서티브 커서: 일치하는 레코드를 별도의임시 테이블로복사해서 가지고 있는 형태
=> 커서를 통해변경및삭제불가능
SELECT 문장으로 커서 정의하고, 커서를 오픈(OPEN)하면 실제로 쿼리가 MySQL에서 실행되고 결과를 가져오며, 오픈된 커서는 페치(FETCH) 명령으로 레코드 단위로 읽어서 사용, CLOSE 명령으로 커서 닫음
declare v_emp_list cursor for #커서 정의
select emp_no, from_date
from dept_emp
where dept_no=p_dept_no;
open v_emp_list; #커서 오픈
repeat
fetch v_emp_list into v_emp_no, v_from_date; #커서로부터 레코드를 한 개씩 읽음
...
until v_no_more_date end repeat;
close v_emp_list; #커서 닫음declare 정의 순서
1. 로컬 변수와 condition
2. cursor
3. handler
🌼스토어드 프로그램 보안 옵션
스토어드 프로그램의 생성 및 변경 권한은 create routine과 alter routine, execute로 분리
트리거나 이벤트는 trigger과 event 권한으로 분리
1. DEFINER와 SQL SECURITY 옵션
DEFINER : 기본 옵션, 해당 스토어드 프로그램의 소유권과 같은 의미
SQL SECURITY : 누구의 권한으로 실행할지 결정
=> INVOKER : 스토어드 프로그램을 호출한 사용자
=> DEFINER : 스토어드 프로그램을 생성한 사용자
관리자 계정을 definer로 설정하고, sql security를 definer로 설정하면, 스토어드 프로그램을 호출하는 사용자는 관리자 계정의 권한으로 실행하게 됨
2. DETERMINISTIC과 NOT DETERMINISTIC 옵션
DETERMINISTIC : 스토어드 프로그램의 입력이 같다면 시점이나 상황에 관계없이 결과가 항상 같다
NOT DETERMINISTIC : 입력이 같아도 시점에 따라 결과가 달라질 수 있음
create function sf_getdate1()
returns datetime
not deterministic
begin
return now();
end ;;explain select * from dept_emp where from_date>sf_getdate1();;
create function sf_getdate2()
returns datetime
deterministic
begin
return now();
end ;;explain select * from dept_emp where from_date>sf_getdate2();
deterministic으로 정의된 함수는 쿼리를 실행하기 전 딱 한번만 스토어드 함수 호출 후, 결괏값을 상수화해서 쿼리 실행
not deterministic으로 정의된 함수는 where절이 비교를 수행하는 레코드마다 매번 값이 재호출 됨
=> 인덱스 레인지 스캔 불가능
🌷스토어드 프로그램 참고 및 주의사항
1. 한글 처리
소스 코드에 한글 문자열 값을 사용해야 한다면, MySQL 서버에 어떤 문자 집합으로 접속돼 있는지가 중요
show variables like 'character%';
character_set_connection과 character_set_client 세션 변수가 스토어드 프로그램을 생성하는데 관여
2. 스토어드 프로그램과 세션 변수
로컬 변수 : declare로 정의
사용자 변수 : @로 시작
주로 로컬 변수를 사용하는 것이 좋음
3. 스토어드 프로시저와 재귀 호출
max_sp_recursion_depth를 통해 재귀 호출을 최대 몇 번까지 허용할지 설정
4. 중첩된 커서 사용
중첩된 루프 안에서 두 개의 커서를 동시에 열어서 사용할 때도 있음
declare v_dept_list cursor for select dept_no from departments;
declare v_emp_list cursor for select emp_no
from dept_emp where dept_no=v_dept_no limit 1;스토어드 프로시저 코드의 처리 중 발생한 에러나 예외는 항상 가장 가까운 블록에 정의된 핸들러가 사용됨
=> 서로 다른 begin .. end 블록으로 구분해서 작성
