DB - 23. Functions and Procedures , Trigger

1. Declare SQL Functions

- 집계함수를 배웠었다.

- 언어 자체가 제공하는 built in function이다.

- 이 처럼 사용자가 직접 원하는 logic을 구현해서 사용할 수 있다.

- 아래 예시는 학과 이름을 주면 해당 학과의 instructor 수를 반환하는 함수를 정의한다.

- query문을 통해 반환된 값을 d_count에 저장하여 반환한다.

- 이렇게 함수를 정의하여 나중에 사용자가 사용할 수 있다.

- 원래 같으면 department와 instructor 테이블을 둘다 봐야 알수 있지만 이미 dept_count가 instructor에서 값을 가져오기 때문에 아래 구문에서 instructor테이블까지 명시할 필요가 없어졌다.

2. Table Functions

- 함수의 반환값이 integer등의 값이 아니라 table인 경우를 말한다.

- 아래 예시는 과 이름을 입력으로 받아 해당 과의 교수이름들을 반환한다.

- usage를 보면 instructor_of('Music')의 반환값이 table이라고 명시해주고 있다.

3. Procedures

- 앞서 살펴본 함수 예시를 procedure로 변환해보자.

- input parameter를 받아서 계산한 결과를 output parameter에 저장한다.

- procedure를 호출할때는 함수와 달리 call을 사용한다.

- 변수를 선언하고 procedure의 결과를 그 변수에 저장한다.

 

*사용자가 정의한 함수나 프로시저는 db에 저장되어 호출된다.

4. trigger

- db에 대해 수정이 일어나면 자동으로 실행되는 sql구문을 말한다.

- 사용자로부터 db의 내용이 update되면 이후 필요한 추가 작업들을 trigger로 설정해둘 수 있다.

- 예를들어 학생이 수강하는 과목이 추가되고 학기말에 성적이 부여가 되면 trigger가 작동해서 학생의 tot_cred에 이 변화가 반영되도록 할 수 있다.

- 트리거의 이벤트는 update, insert, delete가 가능하다.

- 트리거를 활용하면 능동적인 dbms를 구현할 수 있다.

- credits_earned라는 이름의 트리거

- takes 테이블의 grade 컬럼이 업데이트 되면 호출된다.

- when절의 조건을 만족해야 begin...end사이의 동작이 수행된다.

- new row는 변경 후의 grade column의 값이고 old row 는 변경 전의 grade column의 값이다.

- 꼭 이벤트 발생 후에만 트리거를 호출할 수 있는 건 아니다. 이벤트 전에도 호출할 수 있다.

- 수정 구문이 실행되면 실행 직전에 트리거가 호출되어 ''공간을 null로 채워넣는 작업을 수행한다.