절차형 SQL?

: 일반적인 개발 언어처럼 SQL문들을 순서대로 실행할 수 있게하는 SQL

• 단일 SQL문으로는 처리하기 어려운 작업을 수행함
• 자주 사용하는 SQL문들을 저장하여 필요시에 재활용할 수 있으므로, 반복적인 작업에 유리함
• 종류 : Stored Procedure, Stored Function, Trigger
• 오류 및 경고 메시지가 상세히 출력되지 않으므로 특정 명령어를 사용하여 확인 : SHOW [ERROS|WARNINGS];

문법

• 변수 저장 : [값] INTO [변수명]
• 변수 선언 : DECLARE [변수명] [변수 타입];
• OR REPLACE란?
  : 동일한 절차형 SQL 이름이 이미 존재하는 경우, 기존의 절차형 SQL를 덮어씀
• DELIMITER란?

  : 절차형 SQL은 내부에서 세미콜론(;)을 사용하므로 절차형 SQL 구문을 구분하기 어려움. 따라서, 다른 문자($$ 나 //)를 사용하여 절차형 SQL 구문을 구분.

  
  

---

Stored Procedure

: SQL문들의 집합으로 프로그래밍 언어의 함수와 비슷함

• MySQL 서버단에 저장됨
• 장점
  • 인자를 받거나(in 변수로), 레코드들의 집합을 리턴(out 변수가 없을 경우만)할 수 있음
  • 분기문(if, case), loop 사용 가능
• 단점
  • 디버깅이 힘듦
  • 서버단의 부하 증가
• 사용하는 곳
  - 시스템의 일일 마감 작업
  • 배치 작업

문법

생성

DELIMITER $$ -- $$ or // 자주 사용
CREATE {OR REPLACE} PROCEDURE [프로시저명](파라미터)
{IS [지역변수명] [자료형(크기)];, ...}
BEGIN
	[control | sql | exception | transaction]
END $$
DELIMITER;

• 구성 요소
  • 선언 부
    • 파라미터
      • 종류
        • in : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
        • out : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
        • inout : in & out
      • 매개변수명 : 호출 프로그램으로부터 전달 받은 값을 저장할 변수의 이름을 지정
      • 자료형
  • Body
    • 적어도 하나의 sql 문이 존재해야 함
    • 들어갈 수 있는 내용
      1. control : 조건문 또는 반복문
      2. sql : DML , DCL
      3. exception
         • DBMS 내부 문제로 종료할 때
         • 데이터를 찾지 못했을 때
         • unique 옵션을 가지는 속성에 중복 데이터를 삽입할 때
         • 0으로 나눌 때
      4. transaction

실행

[EXECUTE|EXEC|CALL] [프로시저명(인자)];

• out 파라미터가 없을 경우, select 결과를 보여줌

제거

DROP PROCEDURE {IF EXISTS} [프로시저명];

예제

• 사원번호를 입력받아 해당 사원의 ‘지급방식’을 “s”로 변경하는 프로시저 생성

  DELIMITER //
  CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
  BEGIN
  	UPDATE 급여 SET 지급방식='s' WHERE 사원번호=i_사원번호;
  	exception
  		WHEN program_error THEN
  			ROLLBACK;
  	COMMIT;
  END //
  DELIMITER ;

• 특정 channel에서 들어온 세션의 수 세기

  ```sql
  DELIMITER //
  CREATE PROCEDURE return_session_count(IN channelName varchar(64), INOUT
  totalRecord int)
  BEGIN
   SELECT COUNT(1) INTO totalRecord FROM session_details
   WHERE channel = channelName;
  END //
  DELIMITER ;
  
  SET @facebook_count = 0;
  CALL return_session_count('Facebook', @facebook_count);
  SELECT @facebook_count;
  ```

  
  

---

Stored Function (사용자 정의 함수)

: Stored Procedure와 사용 방법이 동일

• 한 가지 다른 점 : 단일 값(Scalar) 반환
• SQL문 안에서 함수처럼 사용
• 예제) SUM(), AVG()

문법

생성

DELIMITER $$ -- $$ or // 자주 사용
CREATE {or REPLACE} FUNCTION[사용자 정의 함수명](파라미터)
RETURNS [리턴자료형]
[DETERMINISTIC | NON DETERMINISTIC]
{IS [지역변수명] [자료형(크기)];, ...}
BEGIN
	[control | sql | exception]
	RETURN ([반환 값 | 변수]);
END$$

• 구성 요소
  • 파라미터
    • 종류 : in만 존재
    • 매개변수명 : 호출 프로그램으로부터 전달 받은 값을 저장할 변수의 이름을 지정
    • 자료형
  • Body
    • 적어도 하나의 sql 문이 존재해야 함
    • 들어갈 수 있는 내용
      1. control : 조건문 또는 반복문
      2. sql : select 문
      3. exception
  • Return : 호출 프로그램에 반환할 값이나 변수를 정의

💡 DETERMINISTIC VS NON DITERMINISTIC
- DETERMINISTIC : 입력 동일 -> 결과 무조건 동일
- NON DITERMINISTIC : 입력 동일 -> 결과 비동일 가능 (ex> random 값)

실행

SELECT [사용자 정의 함수명] FROM [테이블명];
INSERT INTO [테이블명](속성명) VALUES (사용자 정의 함수명);
DELETE FROM [테이블명] WHERE [속성명] = [사용자 정의 함수명];
UPDATE [테이블명] SET [속성명] = [사용자 정의 함수명];

제거

DROP FUNCTION [사용자 정의 함수명];

예제

• 성별코드를 입력받아 성별을 리턴하는 함수

  DELIMITER $$
  CREATE FUNCTION GET_S_성별(i_성별코드 IN INT)
  RETURN VARCHAR(2)
  DETERMINISTIC
  BEGIN
  	IF i_성별코드 = 1 THEN
  		RETURN '남자';
  	ELSE
  		RETURN '여자';
  	END IF;
  END$$

• 채널을 입력받아 채널의 타입을 리턴하는 함수

  ```sql
  DELIMITER $$
  CREATE FUNCTION Channel_Type(channel varchar(32))
  RETURNS VARCHAR(20)
  DETERMINISTIC
  BEGIN
   DECLARE channel_type VARCHAR(20);
  
   IF channel in ('Facebook', 'Instagram', 'Tiktok') THEN
  	 SET channel_type = 'Social Network';
   ELSEIF channel in ('Google', 'Naver') THEN
  	 SET channel_type = 'Search Engine';
   ELSE
  	 SET channel_type = channel;
   END IF;
  
   RETURN (channel_type);
  END$$
  ```

  
  

---

Trigger

: INSERT/DELETE/UPDATE 실행 전 후에 관련 작업이 자동으로 실행되게 하는 절차형 SQL

• 목적
  • 데이터 변경 및 무결성 유지
  • 로그 메시지 출력

문법

생성

CREATE {OR REPLACE} TRIGGER [트리거명] -- 트리거명 작명법 : 트리거 시기_동작_이름 
{트리거 시기} {동작} on [테이블명]
{FOR EACH ROW} -- 모든 레코드마다 트리거가 작동되면 명시
{when (조건식)}
BEGIN
	[control | sql | exception];
END;

• 구성 요소
  • 트리거 시기
    • before : 테이블이 변경되기 전
    • after : 테이블이 변경된 후
  • 동작 : 트리거가 실행되게 하는 작업의 종류
    • insert
    • delete
    • update
  • Body
    • 적어도 하나의 sql 문이 존재해야 함
    • 들어갈 수 있는 내용
      1. control : 조건문 또는 반복문
      2. sql : DML
      3. exception
• 트리거가 적용될 테이블을 별칭으로 접근
  • new : 추가되거나 수정에 참여할 튜플들의 집합 (테이블)
    • insert, update
  • old : 수정되거나 삭제 전 대상이 되는 튜플들의 집합 (테이블)
    • delete, update

제거

DROP TRIGGER {IF EXISTS} [트리거명];

예제

• 중요 테이블을 감사(audit)하기 위해서, 레코드에 변경이 생길 때마다 변경전의 레코드를 저장하는 트리거

  CREATE TRIGGER before_update_name_gender
  BEFORE UPDATE ON name_gender
  FOR EACH ROW
  	INSERT INTO name_gender_audit
  	SET name = OLD.name,
  	gender = OLD.gender,
  	modified = NOW();