Preface
이번 장에선 스토어드 함수, 커서, 트리거 등 다양한 스토어드 프로그램에 대해 공부했다.
내용 자체가 많진 않았지만, 직접 코드를 작성하며 이해해야 할 부분이 많아서 그런지 3~4시간이 훌쩍 갔다.
처음 BEFORE 트리거의 설명을 봤을 땐 어떤 상황에서 사용하는 트리거인지 도통 감이 오지 않았는데, 예시를 본 후 직접 코드를 짜니 금방 이해가 됐다.
또, 중첩 트리거를 공부하며 코딩은 차라리 지식이 얕을 때가 훨씬 간단하고 쉬운 것 같다는 생각을 했다.
이런저런 내용을 배우면 배울수록 코드를 짤 때 고려해야 할 부분이 많아지는 것 같다.
그래도 오늘은 오랜만에 코딩 공부를 처음 시작했을 때 처럼 시간 가는 줄 모르고 재미있게 공부했다.
2. 스토어드 함수
- 스토어드 함수: 사용자가 직접 만들어서 사용하는 함수
- 스토어드 함수와 스토어드 프로시저의 차이
1) 스토어드 함수는 in, out 등을 사용할 수 없고, 모두 입력 파라미터로 사용된다.
2) 스토어드 함수는 returns문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 return문으로 하나의 값을 반환해야 한다.
→ 스토어드 프로시저는 별도의 반환하는 구문이 없다.
3) 스토어드 함수는 select 문장 안에서 호출된다.
4) 스토어드 함수 안에서는 집합 결과를 반환하는 select를 사용할 수 없다.
→ select~into~는 집합 결과를 반환하는 것이 아니므로 사용할 수 있다.
5) 스토어드 프로시저는 여러 SQL문이나 숫자 계산 등 다양한 용도로 사용되지만, 스토어드 함수는 어떤 계산을 통해 하나의 값을 반환하는데 주로 사용된다.
※ 스토어드 함수를 사용하기 위해서는 먼저 스토어드 함수 생성 권한을 허용해줘야 한다.
set global log_bin_trust_function_creators = 1;
- 2개 숫자의 합을 계산하는 스토어드 함수
use sqldb;
drop function if exists userFunc;
delimiter $$
create function userFunc(value1 int, value2 int)
returns int
begin
return value1 + value2;
end $$
delimiter ;
select userFunc(100, 200);
- 출생년도를 입력하면 나이가 출력되는 함수
drop function if exists getAgeFunc;
delimiter $$
create function getAgeFunc(bYear int)
returns int
begin
declare age int;
set age = year(curdate()) - bYear;
return age;
end $$
delimiter ;
select getAgeFunc(1979) into @age1979;
select getAgeFunc(1989) into @age1989;
select concat('1989년과 1979년의 나이차 ==> ', (@age1989 - @age1979));
- 스토어드 함수의 내용을 확인하는 방법
show create function getAgeFunc;
- 스토어드 함수의 삭제는 다른 DB 개체와 마찬가지로 drop문을 사용한다.
3. 커서
- MySQL은 스토어드 프로시저 내부에 커서를 사용할 수 있다.
- 커서(cursor): 인덱스를 지정한 변수
- 커서의 처리 순서
1) 커서의 선언: declare cursor
2) 반복 조건 선언: declare continue handler
3) 커서 열기: open
4) 커서에서 데이터 가져오기: fetch
5) 데이터 처리: loop~end loop문으로 반복 구간 지정
6) 커서 닫기: close
- 커서를 활용하여 고객의 평균 키를 구하는 스토어드 프로시저
use sqldb;
drop procedure if exists cursorProc;
delimiter $$
create procedure cursorProc()
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 -- 행의 끝이면 endOfRow 변수에 true를 대입
for not found set endOfRow = true;
open userCursor; -- 커서 열기
cursor_loop: loop
fetch userCursor into userHeight; -- 고객 키 1개를 대입
if endOfRow then -- 더이상 읽을 행이 없으면 loop를 종료
leave cursor_loop;
end if;
set cnt = cnt + 1;
set totalHeight = totalHeight + userHeight;
end loop cursor_loop;
-- 고객 키의 평균을 출력한다.
select concat('고객 키의 평균 ==> ', (totalHeight/cnt));
close userCursor; -- 커서 닫기
end $$
delimiter ;
call cursorProc();
- 고객의 구매 총액에 따라 등급 값을 입력하는 스토어드 프로시저
drop procedure if exists gradeProc;
delimiter $$
create procedure gradeProc()
begin
declare id varchar(10); -- 사용자 아이디를 저장할 변수
declare hap bigint; -- 총 구매액을 저장할 변수
declare userGrade char(5);
declare endOfRow boolean default false;
declare userCursor cursor for
select U.userid, sum(price * amount)
from buyTbl B
right outer join userTbl U
on B.userid = U.userid
group by U.userid, U.username;
declare continue handler
for not found set endOfRow = true;
open userCursor;
grade_loop: loop
fetch userCursor into id, hap; -- 첫 행 값을 대입
if endOfRow then
leave grade_loop;
end if;
case
when (hap >= 1500) then set userGrade = '최우수고객';
when (hap >= 1000) then set userGrade = '우수고객';
when (hap >= 1) then set userGrade = '일반고객';
else set userGrade = '유령고객';
end case;
update userTbl set grade = userGrade where userID = id;
end loop grade_loop;
close userCursor;
end $$
delimiter ;
call gradeProc();
select * from userTbl;
4. 트리거
- 트리거(trigger): 테이블에 무슨 일이 일어나면 자동으로 실행되는 DB 개체
1) MySQL은 View에 트리거를 부착할 수 없다.
2) in, out 매개 변수를 사용할 수 없다.
3) 삭제는 drop trigger를 사용하면 된다.
4) alter trigger문을 사용할 수 없다.
- 트리거 사용을 위한 테이블
create database if not exists testDB;
use testDB;
create table if not exists testTbl (id int, txt varchar(10));
insert into testTbl values(1, '레드벨벳');
insert into testTbl values(2, '잇지');
insert into testTbl values(3, '블랙핑크');
- 테이블에 트리거를 부착한 후 데이터를 삽입, 수정, 삭제하는 과정
drop trigger if exists testTrg;
delimiter $$
create trigger testTrg
after delete -- 삭제 후에 작동하도록 지정
on testTbl -- 트리거를 부착할 테이블
for each row -- 각 행마다 적용시킴
begin
set @msg = '가수 그룹이 삭제됨'; -- 트리거 실행 시 작동되는 코드들
end $$
delimiter ;
set @msg = '';
insert into testTbl values(4, '마마무');
select @msg;
update testTbl set txt = '블핑' where id = 3;
select @msg;
delete from testTbl where id = 4;
select @msg;
select * from testTbl;
- 트리거의 작동 순서(본체 바로 위에 옵션 지정)
1) FOLLOWS 트리거이름: 지정한 트리거 다음에 현재 트리거가 작동
2) PRECEDES 트리거이름: 지정한 트리거가 작동하기 이전에 현재 트리거가 작동
- 트리거의 종류
1) AFTER 트리거: 테이블에 insert, update, delete 등의 작업이 일어난 후에 작동하는 트리거
2) BEFORE 트리거: insert, update, delete가 발생하기 전에 작동하는 트리거
- AFTER 트리거의 사용
1. 변경되기 전의 데이터를 저장할 테이블 생성
use sqlDB;
drop table if exists buytbl;
create table backup_userTbl
( userID char(8) not null primary key,
username varchar(10) not null,
birthYear int not null,
addr char(2) not null,
mobile1 char(3),
monile2 char(8),
height smallint,
mDate date,
modType char(2), -- 변경된 타입. 수정 또는 삭제
modDate date, -- 변경된 날짜
modUser varchar(256) -- 변경한 사용자
);
2. 변경과 삭제가 발생했을 때 작동하는 트리거 생성
drop trigger if exists backUserTbl_UpdateTrg;
delimiter $$
create trigger backUserTbl_UpdateTrg
after update
on userTBL
for each row
begin
insert into backup_userTbl values( old.userID, old.username, old.birthYear,
old.addr, old.mobile1, old.mobile2, old.height, old.mDate,
'수정', curdate(), current_user() );
end $$
delimiter ;
drop trigger if exists backUserTbl_DeleteTrg;
delimiter $$
create trigger backUserTbl_DeleteTrg
after delete
on userTBL
for each row
begin
insert into backup_userTbl values( old.userID, old.username, old.birthYear,
old.addr, old.mobile1, old.mobile2, old.height, old.mDate,
'삭제', curdate(), current_user() );
end $$
delimiter ;
3. 데이터를 변경한 후 결과를 확인하는 과정
update userTbl set addr = '몽고' where userID = 'JKW';
delete from userTbl where height >= 177;
select * from backup_userTbl;
- delete 트리거는 delete문에만 작동하고, truncate table문에는 작동하지 않는다.
- 테이블에 새로운 데이터가 입력되지 못하도록 설정하고, 누군가 수정이나 삭제를 시도할 경우 경고 메시지를 출력하는 트리거
drop trigger if exists userTbl_InsertTrg;
delimiter $$
create trigger userTbl_InsertTrg
after insert
on userTbl
for each row
begin
signal sqlstate '45000'
set message_text = '데이터의 입력을 시도했습니다. 귀하의 정보가 서버에 기록되었습니다.';
end $$
delimiter ;※ signal sqlstate '45000'문은 사용자가 오류를 강제로 발생시키는 함수이다.
→ 사용자가 정의한 오류 메시지를 출력하고, 사용자가 시도한 insert는 롤백이 되어 테이블에 적용되지 않는다.
- 트리거가 생성하는 임시 테이블: 트리거에서 insert, update, delete 작업이 수행되면 임시로 사용되는 테이블
1) NEW 테이블: insert와 update 작업 시에 변경할 새로운 데이터를 잠시 저장하는 테이블
2) OLD 테이블: delete와 update 작업이 수행되면, 삭제 또는 변경되기 전의 예전 값을 저장하는 테이블
→ 트리거의 작동 시에 새로 입력/변경되는 새로운 데이터를 참조하려면 NEW 테이블을, 변경되기 전인 예전 데이터를 참조하려면 OLD 테이블을 참조하면 된다.
- BEFORE 트리거의 사용 예시: 입력될 데이터 값을 미리 확인해서 문제가 있을 경우 다른 값으로 변경
- 잘못 입력한 출생년도 값을 다른 값으로 정하는 BEFORE 트리거
use sqldb;
drop trigger if exists userTbl_BeforeInsertTrg;
delimiter $$
create trigger userTbl_BeforeInsertTrg
before insert
on userTBL
for each row
begin
if new.birthYear < 1900 then
set new.birthYear = 0;
elseif new.birthYear > year(curdate()) then
set new.birthYear = year(curdate());
end if;
end $$
delimiter ;
insert into userTbl values
('AAA', '에이', 1877, '서울', '011', '1112222', 181, '2022-12-25');
select * from usertbl;
- DB에 생성된 트리거를 확인하는 방법
show triggers from 데이터베이스_이름;
- 다중 트리거: 하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것
→ after insert 트리거가 한 테이블에 2개 이상 부착되어 있는 경우
- 중첩 트리거: 트리거가 또 다른 트리거를 작동시키는 것
→ 시스템 성능에 좋지 않은 영향을 미칠 수 있다. (마지막 작업이 실패하면 앞의 작업도 롤백됨)
- 중첩 트리거의 작동
1. 연습용 DB 생성한 후 테이블 생성
drop database if exists triggerDB;
create database if not exists triggerDB;
use triggerDB;
create table orderTbl
(orderNo int auto_increment primary key,
userID varchar(5),
prodName varchar(5),
orderamount int);
create table prodTbl
(prodName varchar(5),
account int); -- 남은 수량
create table deliverTbl
(deliverNo int auto_increment primary key,
prodName varchar(5),
account int); -- 배송할 물건 개수
insert into prodtbl values('사과', 100);
insert into prodtbl values('배', 100);
insert into prodtbl values('귤', 100);
2. 중첩 트리거 생성
-- 물품 테이블에서 개수를 감소시키는 트리거
drop trigger if exists orderTrg;
delimiter $$
create trigger orderTrg
after insert
on orderTbl
for each row
begin
update prodTbl set account = account- new.orderamount
where prodName = new.prodName;
end $$
delimiter ;
-- 배송 테이블에 새 배송 건을 입력하는 트리거
drop trigger if exists prodTrg;
delimiter $$
create trigger prodTrg
after update
on prodTbl
for each row
begin
declare orderAmount int;
-- 주문 개수 = (변경 전의 개수 - 변경 후의 개수)
set orderAmount = old.account - new.account;
insert into deliverTbl(prodName, account)
values(new.prodName, orderAmount);
end $$
delimiter ;
3. 값을 입력한 후 중첩 트리거가 잘 작동했는지 확인하는 과정
insert into orderTbl values(null, 'JOHN', '배', 4);
select * from orderTbl;
select * from prodTbl;
select * from deliverTbl;
'DB & SQL > 이것이 MySQL이다' 카테고리의 다른 글
| MySQL과 공간 데이터 (0) | 2022.06.27 |
|---|---|
| 전체 텍스트 검색과 파티션 (0) | 2022.06.26 |
| 스토어드 프로그램 (1) (0) | 2022.06.23 |
| DB 및 테이블의 정보를 출력하는 명령어 (0) | 2022.05.29 |
| 인덱스 (2) (0) | 2022.05.29 |
댓글