[이것이 MySQL이다] 10장 정리

쿼리

delimiter $$
create procedure userProc1(in userName varChar(10))
begin
	select * from usertbl where name = userName;
end $$
delimiter ;

call userProc1('조관우');

핵심 포인트

• 스토어드 프로그램은 MySQL에서 제공하는 프로그래밍 기능이다.

• 스토어드 프로그램은 매개변수를 사용 가능하며, 호출은 CALL문을 사용한다.

쿼리

set global log_bin_trust_function_creators = 1;
delimiter $$
create function userFunc(value1 int, value2 int) returns int
begin
	return value1 + value2;
end $$
delimiter ;

select userFunc(100,200);

핵심 포인트

• 스토어드 함수는 반환하는 값이 반드시 있다. 또한 파라미터로 in, out을 사용할 수 없으며 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.

• 스토어드 함수는 returns 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 return 문으로 하나의 값을 반환해야 한다. 스토어드 프로시저는 별도의 반환하는 구문이 없으며, 필요하다면 out 파라미터를 통해 여러 개의 값을 반환할 수 있다.

• 스토어드 프로시저는 call로 호출하지만, 스토어드 함수는 select 문장 안에서 호출된다.

• 스토어드 프로시저 안에는 select 문을 사용할 수 있지만, 스토어드 함수 안에서는 집합 결과를 반환하는 select를 사용할 수 없다.

• 스토어드 프로시저는 여러 sql문이나 숫자 계산 등의 다양한 용도로 사용되지만, 스토어드 람수는 어떤 계한을 통해서 하나의 값을 반환하는데 주로 사용된다.

쿼리

delimiter $$
create procedure cursorProc()
begin
	declare userHeight int;
    declare count int default 0;
    declare totalHeight int default 0;
    
    declare endOfRow boolean default false;
    declare userCursor cursor for select height from usertbl;
    declare continue handler for not found set endOfRow = true;
    
    open userCursor;
    
    cursor_loop : loop
		fetch userCursor into userHeight;
        if endOfrow then 
					leave cursor_loop;
        end if;
        set count = count+1;
        set totalHeight = totalHeight + userHeight;
		end loop cursor_loop;
    
    select totalHeight / count;
    
    close userCursor;
end $$
delimiter ;

call cursorProc();
select avg(height) from usertbl;	-- 값이 같다.

핵심 포인트

• 커서는 일반 프로그래밍 언어의 파일 처리와 방법이 비슷하다.

• 커서는 테이블에서 여러 개의 행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식이다.

쿼리

create table backup_usertbl(
	userID char(8) not null primary key,
    name varchar(10) not null,
    birthYear int not null,
    addr char(2) not null,
    mobile1 char(3),
    mobile2 char(8),
    height smallint,
    mDate date,
    modType char(2),
    modDate date,
    modUser varChar(256)
);

delimiter //
create trigger backuptbl_updatetrg
	after update
    on usertbl
    for each row
begin
	insert into backup_usertbl values(old.userid, old.name, old.birthYear, old.addr, old.mobile1,
		old.mobile2, old.height, old.mDate, '수정', curdate(), current_user());
end //
delimiter ;

delimiter //
create trigger backuptbl_deletetrg
	after delete
    on usertbl
    for each row
begin
	insert into backup_usertbl values(old.userid, old.name, old.birthYear, old.addr, old.mobile1,
		old.mobile2, old.height, old.mDate, '삭제', curdate(), current_user());
end //
delimiter ;

update usertbl set addr = '몽고' where userid='JKW';
select * from usertbl;
select * from backup_usertbl;

delete from usertbl where height>=177;
select * from usertbl;
select * from backup_usertbl;

truncate table usertbl;
select * from usertbl;
select * from backup_usertbl;

핵심 포인트

• 트리거는 제약 조건과 더불어 데이터 무결성을 위해서 MySQL에서 사용할 수 있는 기능이다. 테이블에 관련되어 DML문의 이벤트가 발생할 때 작동하는 데이터베이스 개체 중 하나다.

• 트리거는 스토어드 프로시저와 거의 비슷한 문법으로 내용을 작성할 수 있다. 그리고 트리거가 부착된 테이블에 이벤트가 발생하면 자동으로 부착된 트리거가 실행된다.

• 트리거는 스토어드 프로시저와 작동이 비슷하지만 직접 실행시킬 수는 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다.

결론!

• 해당 내용을 학습하면서 스토어드 프로시저의 개요와 사용법, 스토어드 함수의 개요와 사용법, 커서의 개요와 사용법, 트리거의 개요와 사용법에 대해 익힐 수 있었다.