http://weejw.tistory.com/85
저장 프로시저란
-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
//
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'
이때 마지막 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
https://dev.mysql.com/doc/refman/5.7/en/cursors.html
출처: http://weejw.tistory.com/85 [위지원의 블로그]