728x90
반응형
Stored Procedure(저장 프로시저)
- MySQL에서 제공되는 프로그래밍 기능
- 쿼리문의 집합으로 어떠한 동작 혹은 업무를 일괄 처리하기 위한 용도로 사용.
- 쿼리 모듈화
- 필요할 때 마다 호출만 하면 훨씬 편리하게 MySQL 운영.
- CALL 프로시저_이름() 으로 호출.
- 기본 형식
스토어드 프로시저 생성 예
- DELIMITER $$
create procedure userProc()
begin
select * from usertbl;
end $$
DELIMITER ;
call userProc(); - 수정과 삭제
- 수정 : ALTER PROCEDURE
- 삭제 : DROP PROCEDURE
- 매개 변수의 사용
- 입력 매개 변수를 지정하는 형식 -> IN 입력_매개변수_이름 데이터_형식
- 입력 매개 변수가 있는 스토어드 프로시저 실행방법 -> CALL 프로시저_이름(전달 값);
- 출력 매개 변수 지정 방법 -> OUT 출력_매개변수_이름 데이터 형식
- 출력 매개 변수에 값 대입하기 위해 주로 SELECT...INTO문 사용.
- 출력 매개 변수가 있는 스토어드 프로시저 실행 방법 -> CALL 프로시저_이름(@변수명); SELECT @변수명;
- 예시
DELIMITER $$
create procedure userProc1(IN userName varchar(10))
begin
select * from usertbl where name = userName;
end $$
DELIMITER ;
call userProc1('조용필');
저장된 프로시저의 이름과 내용 확인 : information_schema 데이터베이스 routines 테이블 조회
- select * from information_schema.routines where routine_schema = 'bookstore' and routine_type = 'PROCEDURE';
저장된 프로시저의 파라미터의 내용 확인 : information_schema의 parameters 테이블 조회
- select * from information_schema.parameters where specific_name = 'userProc3';
MySQL에서 저장 프로시저의 정의를 확인하는 데 사용되는 명령어
- show create procedure bookstore.userProc3;
프로시저에 내가 원하는 테이블 이름을 전달하여 조회해보기(동적 쿼리 만들기)
DELIMITER $$
create procedure nameTableProc(in tblName varchar(30))
begin
set @sqlQuery = concat('select * from ', tblName);
prepare myQuery from @sqlQuery;
execute myQuery;
deallocate prepare myQuery;
end $$
DELIMITER ;
drop procedure nameTableProc;
call nameTableProc('book');
call nameTableProc('buytbl');
call nameTableProc('orders');
추가 예시
- 점수를 전달 받아 90점 이상이면 A, 80점 이상이면 B, 70점 이상이면 C, 60점 이상이면 D, 나머지는 F로 처리하는 프로시저를 작성하라.(프로시저명 : GradeProc() / 최종출력 : 당신의 학점은 ====> A 입니다.)
DELIMITER $$
create procedure GradeProc1(in jumsu int)
begin
declare point int;
declare credit char(1);
set point = jumsu;
case
when point >= 90 then
set credit = 'A';
when point >= 80 then
set credit = 'B';
when point >= 70 then
set credit = 'C';
when point >= 60 then
set credit = 'D';
else
set credit = 'F';
end case;
select concat('당신의 학점은 ===>', credit, '입니다.');
end $$
DELIMITER ;
call GradeProc1(85);
DELIMITER $$
create procedure GradeProc2(in jumsu int, out outComment varchar(50))
begin
declare point int;
declare credit char(1);
set point = jumsu;
case
when point >= 90 then
set credit = 'A';
when point >= 80 then
set credit = 'B';
when point >= 70 then
set credit = 'C';
when point >= 60 then
set credit = 'D';
else
set credit = 'F';
end case;
select concat('당신의 학점은 ===>', credit, '입니다.') into outComment;
end $$
DELIMITER ;
call GradeProc2(85, @getComment);
select @getComment;
커서(Cursor)
- 스토어드 프로시저 내부에 사용
- 일반 프로그래밍 언어의 파일 처리와 방법이 비슷함
- 행의 집합을 다루기 편리한 기능 제공
- 테이블에서 여러 개의 행을 쿼리한 후, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식
- 예시
delimiter $$
create procedure customerHeightAVGProc()
begin
declare userheight int; -- 고객 키
declare cnt int default 0; -- 고객의 인원 수
declare totalheight int default 0; -- 고객의 키 합계
declare endOfRow boolean default false; -- 행의 끝 여부(기본은 false로 둔다.)
declare userCursor cursor for
select height from usertbl;
declare continue handler for not found set endOfRow = true; -- 행의 끝이면 endrow 변수에 true 대입.
open userCursor;
c_loop : loop -- 그냥 loop만 해도 됨. C_LOOP는 그냥 따로 라벨링 한겨.
fetch userCursor into userheight;
if endOfRow then
leave c_loop;
end if;
set cnt = cnt + 1;
set totalheight = totalheight + userHeight;
end loop c_loop;
select concat('고객의 키 평균 =>', (totalheight/cnt));
close userCursor;
end $$
delimiter ;
call customerHeightAVGProc;
트리거(Trigger)
- 사전적 의미로 '방아쇠' (탕탕후루후루~)
- 방아쇠 당기면 '자동'으로 총알이 나가듯이 테이블에 무슨 일이 일어나면 '자동'으로 실행.
- 제약 조건과 더불어 데이터 무결성을 위해 MySQL에서 사용할 수 있는 기능.
- 테이블에 DML문(insert, update, delete 등) 이벤트가 발생될 때 작동
- 테이블에 부착되는 프로그램 코드
- 직접 실행 불가
- 테이블에 이벤트가 일어나야 자동 실행
- in, out 매개 변수를 사용할 수 없음.
- MySQL은 View에 트리거 부착 불가.
트리거의 종류
- AFTER 트리거
- 테이블에 insert, update, delete 등의 작업이 일어났을 때 작동.
- 이름이 뜻하는 것처럼 해당 작업 후에 작동.
- BEFORE 트리거
- Before 트리거는 이벤트가 발생하기 전에 작동.
- insert, update, delete 세가지 이벤트로 작동.
728x90
반응형