[DB]SQL응용 2 (트리거, 사용자 정의 함수)

1. 내장함수
2. 저장 프로시저
3. 트리거
4. 사용자 정의 함수
5. 트랜젝션

트리거

데이터 변경 등 명세된 이벤트 발생시 감지하여 자동 실행되는 사용자 정의 프로시저  INSERT, UPDATE, DELETE 명령문의 실행 직전·후 자동으로 호출되어 실행  보통 무결성 제약 조건을 유지하거나 업무 규칙 등을 적용하기 위해 사용

트리거를 생성하는 CREATE TRIGGER 명령문의 형식

create trigger 트리거_이름
[before|alter][insert|update|delete] on 테이블이름 for each row
begin
//sql명령문;
end

생성

delimiter //
create trigger afterInsertStu
after insert on 학생 for each row
begin
if (new성별 = '남')then
update 남녀학생총수 set 인원수 = 인원수 +1 where 성별 = '남';
elseif (new.성별 = '여')then
update 남녀학생총수 set 인원수 = 인원수 + 1 where 성별 = '여';
end if ;

end ;
//
delimiter ;

실행

insert into 학생
values ('s0008','최동석','경기 수원',2,26,'남','010-8888-6666','컴퓨터');

select*from 학생;
select*from 남녀학생총수;

DROP TRIGGER 명령문의 형식

drop trigger 트리거이름;

DROP TRIGGER 적용 예

show triggers;

예제) 'afterInsertStu'삭제하시오 

drop trigger afterInsertStu

사용자 정의 함수

•  
• SELECT문이나 프로시저 안에서 호출되어 특정 기능을 수행하고 결과 값을 반환하는 용 도로 사용

•  

사용자 정의 함수
사용자가 직접 정의한 함수로 DBMS 안에 독립된 데이터베이스 객체로 저장
SELECT문이나 프로시저 안에서 호출되어 특정 기능을 수행하고 결과 값을 반환하는 용 도로 사용	스칼라 함수는 하나의 값 또는 NULL을 반환 테이블 함수는 각 행이 하나 이상의 열로 구성된 테이블을 반환

사용자 정의 함수를 생성하는 CREATE FUNCTION 명령문의 형식

create function 함수명(매개변수 매개변수_자료형)
returns 반환값_자료형
begin 
-- sql명령문 ; 
return 반환값;
end

사용자 함수 적용
'수강' 테이블에서 전체 학생의 '학번', '과목번호', '평가학점' 과 한글로 변환된 '평가등급'을 검색하기.
DROP FUNCTION 명령문의 형식
DROP FUNCTION 적용
	사용자 정의 함수 'Fn_Grade()'를 삭제

 

저장 프로시저, 트리거, 사용자 정의 함수의 비교

저장 프로시저

• 여러 응용 프로그램 사이에 공유함으로써 처리의 일관성 향상과 보안 강화
• 전송되는 SQL 명령문의 양을 줄이고 미리 컴파일된 코드를 호출함으로써 반복적인 처리 시성능 향상

트리거

• 복잡한 데이터 무결성을 강화, 다양한 데이터 처리 업무 규칙을 구현
• 성능이 다소 저하될 수 있음

사용자 정의 함수

• 특정 값뿐만 아니라 테이블도 반환할 수 있어 제한된 SQL 명령문의 기능을 확장시키고 명령문 작성의 편의성을 향상함