스토어드 프로시저(STORED PROCEDURE)
SQL에서 함수를 생성하고 호출하기 위해서 필요한것이다.
쿼리문을 집합형태로 만든것과 비슷하며, 어떤 동작을 일괄 처리하기 위한 용도로도 사용한다.
DELIMITER //
CREATE PROCEDURE 이름 (IN 또는 OUT 매개변수)
BEGIN
프로시저 내용
END
//
DELIMITER ;
위와 같은 형태로 생성하며, 사용할때는 CALL 이름(); 으로 사용한다.
또한 삭제할때는 DROP PROCEDURE 이름; 으로 삭제한다.
생성할 때 이름 뒤에 들어가는 IN과 OUT은 각각 파라미터와 출력이며
이름(IN 매개변수이름 데이터형식) 으로 입력 매개변수를
이름(OUT 매개변수이름 데이터형식) 으로 출력 매개변수를 지정할수 있다.
DELIMITER //
CREATE PROCEDURE uesr_proc(IN userName VARCHAR(10))
BEGIN
SELECT * FROM member WHERE mem_name = 'userName';
END
//
DELIMITER ;
이런식으로 만들어서
CALL user_proc('홍길동');
파라미터를 가지고 호출한다.
이러면 홍길동이란 이름을 가진 사람을 멤버변수에서 찾아서 조회하게 된다.
DELIMITER //
CREATE PROCEDURE uesr_proc2(IN txtValue VARCHAR(10), OUT outValue INT)
BEGIN
INSERT INTO TEST VALUES(NULL, txtValue);
SELECT MAX(id) INTO outValue FROM TEST;
END
//
DELIMITER ;
CALL user_proc2('테스트', @myValue);
SELECT @myvalue;
위의 코드를 매개변수를 가지고 호출하면 출력으로 호출한 숫자를 반납하게 된다.
스토어드 함수
기본 제공되는 함수 외에 직접 함수를 만드는 기능이다.
내장 함수와 용도가 다르며 RETURNS를 이용해 하나의 값을 반환해야한다.
DELIMITER //
CREATE FUNCTION 이름(매개변수)
RETURNS 반환형식
BEGIN
함수 내용
RETURN 반환값;
END
//
DELIMITER ;
SELECT 이름();
프로시저는 call로 호출하지만 함수는 select 문에 의해 호출된다.
mysql에서 스토어드 함수를 만드려면 반드시 1번은 설정 코드를 입력해야한다.
SET GLOBAL log_bin_trust_function_creators = 1;
한번만 하면 되는 설정이라 이후에는 생략해도 된다.
DELIMITER //
CREATE FUNCTION sumFunc(num1 INT, num2 INT)
RETURNS INT
BEGIN
RETURN num1 + num2;
END
//
DELIMITER ;
SELECT sumFunc(100, 200);
간단한 계산함수를 자바에서 처럼 만들수 있다.
커서 함수
delimiter //
create procedure cursor_proc()
begin
declare memNumber int;
declare cnt int default 0;
declare totNumber int default 0;
declare endOfRow boolean default false;
declare memberCursor cursor for
select mem_number from member;
declare continue handler
for not found set endOfRow = true;
open memberCursor;
cursor_loop: loop
fetch memberCursor into memNumber;
if endOfRow then
leave cursor_loop;
end if;
set cnt = cnt + 1;
set totNumber = totNumber + memNumber;
end loop cursor_loop;
select (totNumber / cnt) as '회원의 평균 인원 수';
close memberCursor;
end
//
delimiter ;
call cursor_proc();
이 함수는 자바에서 ITERATOR와 ENUMERATOR와 비슷하게 BOF에서 부터 EOF까지 커서를 옮겨가며 값을 읽는다.
자바에서 이터레이터로 처리하던 작업을 sql에서도 할수있게끔 해주는 함수이다.
트리거(TRIGGER)
명령어가 작동할때 자동으로 작동되어 실수를 방지하게 해주는 기능이다.
예를들어 DML을 이용해서 테이블을 업데이트 하거나 삭제하게 되면 백업 해두지 않았을 때 데이터가 손실되어 문제가 생겼을때 대처할수 없게 되는데 트리거를 이용하면 자동으로 백업되게 설정할수 있다.
트리거의 작동 조건은 INSERT, UPDATE, DELETE가 작동할 때 이다.
create table singer(select mem_id, mem_name, mem_number, addr from member);
여기 멤버의 정보를 담고 있는 singer 테이블이 있다.
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)
);
그리고 이건 백업을 하기 위해서 칼럼 이름과 데이터타입을 같게하고, 내용물은 없는 빈 테이블이다.
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 ;
그리고 업데이트시에 자동으로 백업되는 트리거 함수를 만들어 두면 singer 테이블에 업데이트가 발동될때 자동으로 트리거 함수에 의해서 singer에 있던 모든 데이터가 백업 테이블에 저장되게된다.
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 ;
delete시에도 백업되는 함수를 만들수도 있다.