mysql 프로시저,함수,트리거,커서

프로시저

저장 프로시저란 

-Mysql 5.0이상 버전에서 사용할 수 있다.

-여러개의 sql문을 하나의 sql문처럼 정리하여 "call 프로시저이름"으로 실행할 수 있게 한 것이 저장 프로시저

-중요한 데이터가 축적된 디비에서 검증되지 않은 프로시저 실행은 위험하다

프로시저의 기본 모양

create procedure [프로시저이름](매개변수)

begin

sql문

end

delimiter로 구분 문자를 변경하기

sql문은 ;로 끝나는데 기본 구분자가 ;로 정해져있기때문에 구분문자를 변경하지 않으면

프로시저내에서 한가지의 sql문이 동작한뒤 실행되어버린다.

ex. delimiter // : //로 구분자를 변경한다.

예제 실행해보기기

delimiter //

create procedure test()

begin

select * from recom_log;

end

//

delimiter ;

call test로 프로시저 실행해서 결과확인

'1', '010-9940-5057', '3.1542870000', '4.1552478000', '2017-08-15 16:19:55', 's1'

'2', '010-9940-5057', '3.1548870000', '4.1552478000', '2017-08-15 16:19:55', 's3'

'3', '010-9940-5057', '3.1548870000', '4.1552478000', '2017-08-15 16:19:55', 's7'

'4', '010-9940-5057', '3.1548870000', '4.1552478000', '2017-08-15 16:19:55', 's8'

'5', '010-9940-5057', '3.1548870000', '4.1552478000', '2017-08-15 16:19:55', 's10'

함수실행하는것과 같이 매개변수를 사용할 수 있다.

매개변수 사용하기

delimiter //

create procedure test(n int)

begin

select * from recom_log;

end

//

delimiter ;

call test(3)으로 프로시저 실행해서 결과확인

'1', '010-9940-5057', '3.1542870000', '4.1552478000', '2017-08-15 16:19:55', 's1'

'2', '010-9940-5057', '3.1548870000', '4.1552478000', '2017-08-15 16:19:55', 's3'

커서

커서란

-프로시저에서 select into 하면 하나의 값만 나오게 처리해야하기때문에 커서로 하나이상의 결과를 return 하기 위해서 사용

커서의 기본모양

-declare 커서이름 cursor for select * from table; (단 모든 변수가 생성된후 선언해야함!)

커서의 사용방법은

-커서는 3단계로 이루어짐

-open : 커서를 사용하기전에 열어줌

-fetch : 커서가 다음 row로 이동하게함

-close : 커서를 닫아줌

예제 실행하기

delimiter //

create procedure test()

begin

declare v_no int default 0;

declare cname cursor for select no from recom_log;

open cname;

recom_loop:Loop

fetch cname into v_no;

insert into test2 values(v_no);

end loop recom_loop;

close cname;

end

결과

'1'

'2'

'3'

'4'

'5'

[test2 테이블에]

이때 마지막 row가 닿았을때 데이터가 없기때문에 오류가 발생할 수 있는데 이때를 위해서 아래와 같이 예외처리를 해준다.

delimiter //

create procedure test()

begin

declare v_no int default 0;

DECLARE done INT DEFAULT FALSE;

declare cname cursor for select no from recom_log;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

open cname;

recom_loop:Loop

fetch cname into v_no;

if done then

leave recom_loop;

end if;

insert into test2 values(v_no);

end loop recom_loop;

close cname;

end;

함수

함수란

-프로시저와 비슷하지만 결과값을 리턴한다.

함수의 기본모양

create function 함수이름(인자 인자타입) returns 반환값의 타입

begin 

sql문

return 반환값 식

end

예제 실행해보기

delimiter //

create function test() returns int

begin

return 1+1;

end

//

delimiter ;

select test() 로 결과확인

'2'

변수선언해서 사용해보기 ( 프로시저도 변수선언해서 사용할 수 있다 )

delimiter //

create function test() returns int

begin

declare result int;

select no from test into result;

return result;

end

//

delimiter ;

select test()로 결과확인

'1'

트리거

트리거란

-mysql 5.0이상의 버전에서만 사용할 수 있음

-테이블에 대해 어떠한 처리를 실행하면 설정해둔 명령이 자동으로 실행되는 구조

 트리거는

-insert,update,delete 등의 명령이 실행되기 전과 후에 호출되어 실행한다 ( 사용자 지정 )

-처리되기전 값은 old.컬럼이름

-처리된   후 값은 new.컬럼이름 으로 얻을 수 있음

하지만 insert실행전 delete실행후 값은 얻을 수 없음

트리거의 기본모양

create trigger test (before/after) 

(insert/update/delete) on table_name for each row

begin

처리할 내용

end

예제 실행하기

delimiter //

create trigger test after update

on test for each row

begin

insert into test2(no) values(old.no);

end

delimiter ;

결과

test2 table

'1'이 들어가있음!

**프로시저,함수,트리거 모두 drop으로 삭제할 수 있으며 show로 해당 내용을 볼 수 있다.

출처url

http://recoveryman.tistory.com/186

http://javapia.blogspot.kr/2010/07/mysql-stored-procedure%EC%BB%A4%EC%84%9C%EC%9D%98-%EC%82%AC%EC%9A%A9.html

https://dev.mysql.com/doc/refman/5.7/en/cursors.html