9.3 Procedural SQL

개요

절차적 SQL(Procedural SQL)은 SQL 언어를 확장하여 절차(Procedures), 함수(Functions), 조건문(Conditionals), 및 루프(Loops)를 포함한다. 절차적 SQL은 데이터베이스 애플리케이션을 위해 설계되었으며, Java, Python, C와 같은 범용 언어의 모든 기능을 제공하지는 않는다.

 

절차적 SQL은 원칙적으로 완전한 프로그램을 작성하는 데 사용할 수 있다. 그러나 대부분의 경우, 데이터베이스와 상호작용하고 제한된 작업을 수행하는 프로시저를 생성하는 데 사용된다. 이러한 프로시저는 데이터베이스에서 컴파일되고 저장되므로 저장 프로시저(Stored Procedures)라고 한다.

 

저장 프로시저는 명령줄에서 호출하거나, 다른 언어로 작성된 호스트 프로그램에서 호출할 수 있다. 호스트 프로그램이 저장 프로시저를 호출하는 코드는 임베디드 SQL로 작성하거나 API에 내장될 수 있다.

 

SQL/Persistent Stored Modules (SQL/PSM)은 SQL 표준을 확장한 절차적 SQL 표준이다. SQL/PSM은 많은 관계형 데이터베이스에 구현되어 있으나, 각각의 구현은 상당히 다르며 이름도 다양하다. 많은 이름에는 'PL'(Procedural Language)을 포함한다.

 

MySQL은 SQL/PSM 표준을 비교적 잘 준수한다. MySQL의 절차적 SQL은 SQL 언어의 일부로 간주되며 특별한 이름이 없다. 이 섹션은 MySQL에서의 구현을 설명한다.

Table 9.3.1: Procedural SQL.

Database	Programming language
Oracle Database	PL/SQL
SQL Server	Transact-SQL
DB 2	SQL PL
PostgreSQL	PL/pgSQL
MySQL	SQL

 

---

저장 프로시져

저장 프로시저는 CREATE PROCEDURE 문으로 선언되며, 프로시저 이름, 선택적인 매개변수 선언, 그리고 프로시저 본문을 포함한다.

1. 매개변수 선언(Parameter Declaration)
   • 매개변수 선언은 선택적인 IN, OUT, 또는 INOUT 키워드, ParameterName, 그리고 Type으로 구성된다.
   • 데이터 타입(Type)은 데이터베이스에서 지원하는 모든 데이터 타입이 될 수 있다.
   • IN: 입력 전용 매개변수
   • OUT: 출력 전용 매개변수
   • INOUT: 입력 및 출력 모두 가능한 매개변수
   • 키워드가 생략되면, 해당 매개변수는 기본적으로 입력 전용으로 제한된다.
2. 프로시저 본문(Procedure Body)
   • 프로시저 본문은 SELECT, DELETE와 같은 단순 문장이거나, 복합 문장(Compound Statement)일 수 있다.
   • 복합 문장은 아래에서 설명하며, 복잡한 데이터베이스 작업을 코딩하기 위해 저장 프로시저에서 자주 사용된다.

9.3.1: CREATE PROCEDURE statement

CREATE PROCEDURE ProcedureName ( <parameter-declaration>, <parameter-declaration>, ... )
Body;

<parameter-declaration>:
[ IN | OUT | INOUT ] ParameterName Type

저장 프로시저는 CALL 문으로 실행된다. CALL 문에는 프로시저 이름과 매개변수 목록이 포함된다.

CALL 문에서 사용되는 매개변수의 타입은 해당 CREATE PROCEDURE 문에서 선언된 매개변수와 호환되어야 한다.

9.3.2: CALL Statement

CALL ProcedureName( ParameterName, ParameterName, ... )

저장 프로시저는 다음에서 호출할 수 있다:

1. 다른 저장 프로시저
   • 호출 인수는 10, 'alpha'와 같은 문자 그대로 또는 아래에 설명된 저장 프로시저 변수일 수 있다.
2. MySQL 커맨드라인 클라이언트(MySQL Command-Line Client)
   • 호출 인수는 문자 그대로 또는 사용자 정의 변수(User-Defined Variables)일 수 있다.
   • 사용자 정의 변수는 SQL 변수로, 반드시 @ 문자로 시작해야 한다.
3. C, C++, Java, Python 및 기타 프로그래밍 언어
   • 다른 프로그래밍 언어에서 호출될 때는 매개변수 구문이 언어와 API에 따라 크게 다르다.

9.3.2: Stored procedure call from the command line

1. FlightCount 저장 프로시저는 입력 매개변수 airline과 출력 매개변수 quantity를 가진다.  
2. 저장 프로시저 본문은 단일 SELECT문으로 구성된다.  
3. INTO절은 COUNT(*) 값을 quantity 매개변수에 할당한다.  
4. 저장 프로시저는 커맨드라인에서 호출된다. airline에는 'British Airways'가 할당되고, @result에는 결과로 얻어진 quantity 값이 할당된다.  
5. 사용자 정의 변수 @result의 값은 SELECT 문으로 출력된다. Flight 테이블에는 British Airways 항공편이 41개 있다.

---

  Compound statements 복합문

복합문은 BEGIN과 END 키워드 사이에 있는 여러 문의 집합이다. 이러한 문에는 변수 선언, 할당문, 조건문(IF), 반복문(WHILE), 및 기타 일반적인 프로그래밍 구문이 포함될 수 있다.

1. DECLARE Statement
DECLARE 문은 저장 프로시저 내에서 사용할 변수를 생성한다.  
   - 변수는 이름, 데이터 타입, 선택적인 기본값을 가진다.  
   - 변수의 유효 범위(Scope)는 해당 DECLARE 문부터 다음 END 키워드까지이다.  
   - 변수 이름은 테이블 열 이름과 동일하지 않아야 한다.  

Figure 9.3.3: DECLARE statement

DECLARE VariableName Type [ DEFAULT Value ];

 

2. SET statement

SET 문은 변수에 표현식을 할당한다

   -  변수는 SET 문 이전에 선언되어야 한다.

   -  표현식은 유효한 SQL 표현식이면 사용할 수 있다.

Figure 9.3.4: SET statement

SET VariableName = ;

 

3. IF statement
IF 문은 다른 프로그래밍 언어의 조건문과 유사하다.  
- IF 문은 논리적 표현식과 문장 목록을 포함한다.  
- 표현식이 TRUE 이면 목록의 문장들이 실행된다.  
- 표현식이 FALSE 이면 문장들이 무시된다.  

ELSEIF 및 ELSE 절
- ELSEIF 및 ELSE 절은 선택적으로 사용 가능하다.  
- IF 절의 표현식이 FALSE 일 때 실행된다.  
- 다른 프로그래밍 언어의 조건문 동작과 동일하다.

Figure 9.3.5: IF statement

IF expression THEN statement list
[ ELSEIF expression THEN statement list ]
[ ELSE StatementList ]
END IF;

 

 

 

4. WHILE statement
WHILE 문은 표현식과 문장 목록을 포함한다.  
- 표현식이 TRUE인 동안, 문장 목록이 반복적으로 실행된다.  
- 표현식이 FALSE가 되면, WHILE 문이 종료되고, END WHILE 뒤의 문장이 실행된다.  

Figure 9.3.6: WHILE statement

WHILE expression DO
   statement list
END WHILE;

복합문 내에서는 다음과 같은 일반적인 프로그래밍 문장도 지원된다:  CASE, REPEAT, RETURN

복합문도 커서를 지원한다.

1. AddFlights() 저장 프로시저는 Flight 테이블에 새로운 항공편을 삽입한다.  
2. 본문은 복합 문장으로 구성된다.  
3. DECLARE 문은 두 개의 변수를 생성하고 초기화한다. departTime은 startTime 매개변수로 초기화되며, flightNum은 540으로 초기화된다.  
4. WHILE 루프는 departTime이 endTime보다 작거나 같은 동안 반복된다.  
5. 루프를 통과할 때마다 새로운 항공편이 삽입된다.  
   - flightNum은 100씩 증가한다.  
   - departTime이 정오 이전이면 30분을 추가한다.  
   - departTime이 정오 이후거나 같으면 1시간을 추가한다.  

---

CREATE PROCEDURE AwardBonus()
BEGIN

   DECLARE bonusAmount INT;
   DECLARE ratingCount INT;
   ___A___ rating INT DEFAULT 10;

   ___B___ rating > 0 DO

   SELECT COUNT(*)
   ___C___ ratingCount
   FROM Employee
   WHERE PerformanceRating = rating;
 
   ___D___ ratingCount > 80
   SET bonusAmount = 1000 * rating;
   ELSE
   SET bonusAmount = 100 * rating;
   END IF;

   UPDATE Employee
   SET EmployeeBonus = bonusAmount
   WHERE PerformanceRating = rating;

   ___E___ rating = rating - 1;

   END WHILE;

END;

A, B, C, D, E ?

A: DECLARE

B: WHILE

C: INTO

D: IF

E: SET

---

Cursors

커서(Cursor)는 결과 테이블의 개별 행을 식별하는 특별한 변수이다. 커서는 여러 행을 반환하는 쿼리를 처리하는 데 필요하다. 절차적 SQL의 커서 구문은 임베디드 SQL과 유사하다:

- DECLARE CursorName CURSOR FOR Statement
  CursorName이라는 이름의 커서를 생성하고, 이를 쿼리 Statement와 연관시킨다. Statement에는 INTO 절이 포함될 수 없다.
- DECLARE CONTINUE HANDLER FOR NOT FOUND Statement
  커서가 마지막 행을 지나 더 이상 데이터를 가져올 수 없을 때 실행될 문을 지정한다. 데이터는 루프 내에서 읽으므로, 이 문장은 보통 루프를 종료해야 함을 나타내는 변수를 설정한다.
- OPEN CursorName
  CursorName과 연관된 쿼리를 실행하고, 커서를 결과 테이블의 첫 번째 행 앞에 위치시킨다. 이름이 다른 여러 커서를 동시에 열 수 있다.
- FETCH FROM CursorName INTO variable [, variable, ... ]
  커서를 결과 테이블의 다음 행으로 이동시키고, 선택된 값을 변수에 복사한다. INTO 절의 변수 개수는 SELECT 절의 열 개수와 일치해야 한다.
- CLOSE CursorName
  커서와 연관된 결과 테이블을 해제한다.

아래는 모든 항공편의 출발 시간을 변경하기 위해 커서를 사용하는 저장 프로시저이다.

1. flightCursor라는 커서는 모든 항공편 데이터를 검색한다.  
2. finishedReading 변수는 초기값으로 FALSE를 가진다. 핸들러는 커서가 마지막 행을 지나가면 finishedReading 값을 TRUE로 설정한다.  
3. OPEN은 flightCursor와 연관된 쿼리를 실행하고, 커서를 결과 테이블의 첫 번째 행 이전으로 설정한다.  
4. FETCH는 커서를 첫 번째 행으로 이동시키고, 열 값을 프로시저 변수에 할당한다.  
5. WHILE 루프는 커서가 마지막 행을 지나갈 때까지 반복된다.  
6. IF 문은 선택된 항공편의 출발 시간을 변경한다.  
7. FETCH는 커서를 다음 행으로 이동시킨다.  
8. CLOSE는 커서 리소스를 더 이상 필요하지 않을 때 해제한다.

---

Stored functions

저장 함수는 단일 값을 반환하는 저장 프로시저와 유사하다. 저장 함수는 CREATE FUNCTION 문으로 선언된다. CREATE FUNCTION 구문은 CREATE PROCEDURE 구문과 유사하지만 다음과 같은 차이가 있다:

1. 매개변수
   - 함수의 매개변수는 입력 전용이며, IN, OUT, INOUT 키워드를 사용하지 않는다.

2. 반환 값
   - 함수는 단일 값을 반환한다. 반환 값은 저장 함수 본문에서 RETURN 키워드 뒤에 위치한다.  
   - 반환 값의 데이터 타입은 함수 선언부에서 RETURNS 키워드 뒤에 위치한다.

3. 호출 방식
   - 함수는 CALL 문이 아니라 SQL 표현식 내에서 호출된다.

4. 필수 키워드
   - CREATE FUNCTION 문에는 최소 하나의 다음 키워드가 포함되어야 한다:
     - NO SQL: 함수에 SQL 문이 포함되지 않음.
     - READS SQL DATA: 함수에 테이블 데이터를 읽지만 쓰지 않는 SQL 문이 포함됨.
     - DETERMINISTIC: 함수에 테이블 데이터를 쓰는 SQL 문이 포함됨.

Figure 9.3.7: CREATE FUNCTION statement

CREATE FUNCTION FunctionName( <parameter-declaration>, <parameter-declaration>, ... )
   RETURNS Type
   [ NO SQL | READS SQL DATA | DETERMINISTIC ]
Body;

<paramenter-declaration>:
ParameterName Type

저장 함수는 COUNT(), SUM(), AVG()와 같은 내장 SQL 함수처럼 동작한다. 유일한 차이점은 저장 함수는 프로그래머에 의해 정의되고, 내장 함수는 데이터베이스에 의해 정의된다는 것이다.

저장 함수는 저장 프로시저에 비해 제한적이다. 저장 함수는 단일 값만 반환할 수 있지만, 저장 프로시저는 여러 OUT 매개변수를 통해 여러 값을 반환할 수 있다. 그러나 저장 함수는 내장 함수처럼 SQL 표현식 내에서 계산된 값이 필요한 경우 유용하다.

1. 저장 함수는 직원의 세금을 계산하고 정수를 반환한다. 함수는 SELECT 문을 사용하여 SQL 데이터를 읽는다.  
2. 직원의 급여가 조회되어 income 변수에 할당된다.  
3. 세금은 $25,000을 초과하는 소득의 10%로 계산되어 반환된다.  
4. 저장 함수는 내장 함수처럼 SQL 표현식 내에서 호출된다.  
5. Lisa Ellison의 급여는 $40,000이다. 세금은 ($40,000 - $25,000) * 10% = $1,500으로 계산되어 결과 테이블에 표시된다.  

 - RETURN은 저장 함수에 반환 값을 할당하며 본문 내에 나타난다. RETURNS는 반환 값의 데이터 타입을 지정하며 CREATE FUNCTION 절과 본문 사이에 나타난다.

 - 저장함수는 입력 변수만 가진다.

 - 저장 함수는 SQL 문 내의 표현식에 나타날 수 있다. SQL 문이 커맨드라인에서 실행될 때, 포함된 저장 함수도 함께 실행된다.

 - <parameter-declaration>은 저장 프로시저에서는 IN, OUT, INOUT 매개변수를 포함할 수 있지만, 저장 함수에서는 포함할 수 없다.

---

Triggers

트리거는 저장 프로시저나 저장 함수와 비슷하지만, 두 가지 주요 차이가 있다:

1. 매개변수와 반환 값 없음
   - 트리거는 매개변수나 반환 값을 가지지 않는다.  
   - 트리거는 테이블을 읽고 쓰지만, 호출 프로그램과 직접적으로 통신하지 않는다.

2. 명시적 호출 없음
   - 트리거는 CALL 문이나 표현식 내에서 명시적으로 호출되지 않는다.  
   - 대신 특정 테이블에 연관되어 있으며, 테이블이 변경될 때마다 실행된다.

- 트리거는 데이터가 업데이트, 삽입, 삭제될 때 비즈니스 규칙을 자동으로 적용하기 위해 사용된다.

CREATE TRIGGER 문은 TriggerName과 네 가지 필수 키워드를 포함한다:

1. ON TableName
   - 트리거와 연관된 테이블을 식별한다.

2. INSERT, UPDATE, DELETE
   - 해당 SQL 작업이 테이블에 적용될 때 트리거가 실행된다.

3. BEFORE 또는 AFTER
   - 트리거가 삽입, 업데이트, 삭제 작업 전 또는 후에 실행될지를 결정한다.

4. FOR EACH ROW
   - 삽입, 업데이트, 삭제 작업으로 영향을 받는 각 행에 대해 반복적으로 트리거가 실행됨을 나타낸다.  

MySQL의 제한
- MySQL 트리거는 SQL 표준의 모든 기능을 지원하지 않는다.  
  Ex: SQL 표준에는 FOR EACH ROW의 대안으로 FOR EACH STATEMENT 키워드가 포함되어 있다.  
  - FOR EACH STATEMENT는 영향을 받은 각 행마다가 아니라 한 번만 실행됨을 나타낸다.  

트리거 본문
- 저장 프로시저와 마찬가지로, 트리거 본문은 단순 문장 또는 복합 문장일 수 있다.  
- 본문 내에서 OLD와 NEW 키워드는 트리거와 연관된 테이블의 이름을 나타낸다.  
  - OLD: 업데이트 또는 삭제 작업 이전의 테이블 값.  
  - NEW: 삽입 또는 업데이트 작업 이후의 테이블 값.  

Figure 9.3.8: CREATE TRIGGER statement

CREATE TRIGGER TriggerName
[ BEFORE | AFTER ]  [ INSERT | UPDATE | DELETE ]
ON TableName 
FOR EACH ROW
Body;

1. ExamGrade 트리거는 Exam 테이블에 삽입 작업이 실행되기 전에 실행된다.  
2. 하나의 삽입 문은 여러 행을 생성할 수 있다. 트리거는 각 새로운 행마다 한 번씩 실행된다.  
3. 트리거는 자동으로 시험 점수를 결정하고, 새로운 테이블 행의 Grade 열을 업데이트한다.  
4. 새로운 행을 삽입하면, 해당 행이 삽입되기 전에 ExamGrade 트리거가 실행된다.  
5. Grade 열은 ExamGrade 트리거에 의해 설정된 값을 포함한다.  

 

- UPDATE는 트리거를 활성화하지 않는다.
- 삽입된 고유키가 고유하지 않으면 트리거는 INSERT가 시도되기 전에 성공적으로 실행된다.
INSERT는 기본 키의 고유성을 위반하기 때문에 실패한다.

---

CREATE TABLE Seat	(
	RoomName VARCHAR(40),
    SeatNumber	INT,
    PRIMARY KEY (RoomName, SeatNumber)
);

/*
Fill in the keywords to create a stored procedure that adds entrie to the Seat table
*/

___(A)___ PROCEDURE AddSeats(___(B)___ roomName VARCHAR(40), IN howMany INT)
___(C)___
	DECLARE n INT ___(D)___ 1;
    
    WHILE n <= howMany ___(E)___
    	INSERT INTO Seat VALUES (roomName, n);
        ___(F)___ n = n + 1;
    END WHILE;
END;

A: CREATE

B: IN

C: BEGIN

D: DEFAULT

E: DO

F: SET

/*
The following procedure uses a cursor to add a seat with number 0 to every room that does not have any seats. Fill in the missing keywords.CREATE PROCEDURE MarkEmpty()
*/
BEGIN
   DECLARE nam VARCHAR(40);
   DECLARE num INT;
   DECLARE finished BOOL DEFAULT FALSE;

   DECLARE roomCursor CURSOR ___(A)___
      SELECT Name FROM Room;
   DECLARE ___(B)___ HANDLER FOR NOT FOUND SET finished = TRUE;
   OPEN roomCursor;
   FETCH ___(C)___ roomCursor ___(D)___ nam;
 
   WHILE NOT finished DO
      SELECT COUNT(*) INTO num FROM Seat WHERE RoomName = nam;
      IF num = 0 THEN
         INSERT INTO Seat VALUES (nam, 0);
      END ___(E)___;
      FETCH FROM roomCursor INTO nam;
   END WHILE;
   ___(F)___ roomCursor;
END;
CREATE PROCEDURE MarkEmpty()
BEGIN

A: FOR

B: CONTINUE

C: FROM

D: INTO

E: IF

F: CLOSE