[SQLD : Ⅳ. SQL 활용] 8-1. 절차형 SQL

* 절차형 SQL 개요

 - 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로

    PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공

 - 절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여

   특정 기능을 수행하는 저장 모듈을 생성 가능

 

* PL/SQL 개요

 - PL/SQL 특징

    PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.

   변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.

    IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

    DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다. 

    PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다. 

    PL/SQL은 응용 프로그램의 성능을 향상시킨다.

    PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

  PL/SQL Block 프로그램을 입력받으면 SQL 문장과 프로그램 문장을 구분하여 처리

  프로그램 문장은 PL/SQL 엔진이 처리하고

  SQL 문장은 Oracle 서버의 SQL Statement Executor가 실행하도록 작업을 분리하여 처리

 

 - PL/SQL 구조

  DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다. 

  BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. 

  EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면

                   그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.

 

 - PL/SQL 기본문법

CREATE [OR REPLACE] Procedure [Procedure_name] 
( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... ) 
IS [AS] ... ... BEGIN ... ... EXCEPTION ... ... END; /

DROP Procedure [Procedure_name];

  CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성

  [OR REPLACE] 절은 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 새로운 내용으로 덮어쓰기 하겠다는 의미

  Argument는 프로시저 호출 시 프로시저로 어떤 값이 들어오거나 처리한 결과값을 리턴시킬 매개 변수를 지정

  [mode] 부분에 지정할 수 있는 매개 변수의 유형은 3가지

      IN은 운영 체제에서 프로시저로 전달될 변수의 MODE

      OUT은 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE

      INOUT은  IN과 OUT 두 가지의 기능을 동시에 수행하는 MODE

 

* T-SQL 개요

 - 근본적으로 SQL Server를 제어하기 위한 언어

 - MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것

 - T-SQL 특징

   변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다. 

   지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만든 변수, 전역변수는 이미 SQL서버에 내장된 값

   데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.

   연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.

   흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.

   주석 기능한줄 주석 : -- 뒤의 내용은 주석범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함

 - T-SQL 구조

  DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다.

  BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다.

                       T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아님

                       블록 단위로 처리하고자 할 때는 반드시 작성해야 한다.

  ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면

                    그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.

 - T-SQL 기본문법

CREATE Procedure [schema_name.]Procedure_name @parameter1 data_type1 [mode],
                                              @parameter2 date_type2 [mode], 
... ... WITH AS ... ... BEGIN ... ... ERROR 처리 ... ... END;

DROP Procedure [schema_name.]Procedure_name;

  CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성

  프로시저의 변경이 필요할 경우 Oracle은 [CREATE OR REPLACE]와 같이 하나의 구문으로 처리 

                                           SQL Server는 CREATE 구문을 ALTER 구문으로 변경

  @parameter는 프로시저 호출 시 프로시저로 어떤 값이 들어오거나 처리한 결과 값을 리턴 시킬 매개 변수를 지정

  [mode] 부분에 지정할 수 있는 매개 변수(@parameter)의 유형은 4가지

    ① VARYING결과 집합이 출력 매개 변수로 사용되도록 지정합니다. CURSOR 매개변수에만 적용된다.

    ② DEFAULT지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리한다.

        즉, 기본 값이 지정되어 있으면 해당 매개 변수 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행

    ③ OUT, OUTPUT프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환한다.

    ④ READONLY자주 사용되지는 않는다.

        프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타낸다.

        매개 변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정해야 한다.

  WITH 부분에 지정할 수 있는 옵션은 3가지가 있다.

    ① RECOMPILE데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 된다.

        데이터베이스 엔진에서 저장 프로시저 안의 개별 쿼리에 대한 계획을 삭제할 때 RECOMPILE 쿼리 힌트를 사용

    ② ENCRYPTIONCREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다.

        변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않는다.

        원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업을 해두어야 한다.

    ③ EXECUTE AS 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다.

 

* Procedure 생성과 활용

  [예제] SCOTT 유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 Procedure를 작성한다.

          SCOTT 유저가 기본적으로 소유한 DEPT 테이블의 구조는 [표 Ⅱ-2-14]와 같다.

Oracle CREATE OR REPLACE Procedure 
p_DEPT_insert -------------① 
( v_DEPTNO in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2) 
IS cnt number := 0; BEGIN SELECT COUNT(*) INTO CNT -------------② 
FROM DEPT WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1; if cnt > 0 then -------------③ 
v_result := '이미 등록된 부서번호이다'; 
else INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------④ 
VALUES (v_DEPTNO, v_dname, v_loc); COMMIT; -------------⑤ 
v_result := '입력 완료!!'; end if; EXCEPTION -------------⑥ 
WHEN OTHERS THEN ROLLBACK;
v_result := 'ERROR 발생'; END; /

SQL Server CREATE Procedure dbo.p_DEPT_insert -------------① 
@v_DEPTNO int, @v_dname varchar(30), @v_loc varchar(30), @v_result varchar(100) 
OUTPUT AS DECLARE @cnt int SET @cnt = 0 BEGIN SELECT @cnt=COUNT(*) -------------② 
FROM DEPT WHERE DEPTNO = @v_DEPTNO IF @cnt > 0 -------------③ 
BEGIN SET @v_result = '이미 등록된 부서번호이다' 
RETURN END ELSE BEGIN BEGIN TRAN INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------④ 
VALUES (@v_DEPTNO, @v_dname, @v_loc) IF @@ERROR<>0 BEGIN ROLLBACK -------------⑥ 
SET @v_result = 'ERROR 발생' RETURN END ELSE BEGIN COMMIT -------------⑤ 
SET @v_result = '입력 완료!!' RETURN END END END

  ① DEPT 테이블에 들어갈 칼럼 값(부서코드, 부서명, 위치)을 입력 받는다.

  ② 입력 받은 부서코드가 존재하는지 확인한다.

  ③ 부서코드가 존재하면 '이미 등록된 부서번호입니다'라는 메시지를 출력 값에 넣는다.

  ④ 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.

  ⑤ 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션을 종료한다.

  ⑥ 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생'라는 메시지를 출력값에 넣는다.

Oracle SQL> SELECT * FROM DEPT; -----------------① 
            DEPTNO DNAME LOC 
            ------- ------- --------- 
            10 ACCOUNTING NEW YORK 
            20 RESEARCH DALLAS 
            30 SALES CHICAGO 
            40 OPERATIONS BOSTON 
SQL> variable rslt varchar2(30); -----------------② 
SQL> EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt); -----------------③ 
PL/SQL 처리가 정상적으로 완료되었다. 

SQL> print rslt; -----------------④ 
RSLT 
-------------------------------- 
이미 등록된 부서번호이다 

SQL> EXECUTE p_DEPT_insert(50,'NewDev','seoul',:rslt); ----------------⑤ 
PL/SQL 처리가 정상적으로 완료되었다. 
SQL> print rslt; ----------------⑥ 
RSLT
-------------------------------- 
입력 완료!!

SQL> SELECT * FROM DEPT; ----------------⑦ 
DEPTNO DNAME LOC 
------ -------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON 
50 NewDev SEOUL 5개의 행이 선택되었다.

  ① DEPT 테이블을 조회하면 총 4개 행의 결과가 출력된다.

  ② Procedure를 실행한 결과 값을 받을 변수를 선언한다. (BIND 변수)

  ③ 존재하는 DEPTNO(10)를 가지고 Procedure를 실행한다.

  ④ DEPTNO가 10인 부서는 이미 존재하기 때문에 변수 rslt를 print해 보면 '이미 등록된 부서번호이다' 라고 출력된다.

  ⑤ 이번에는 새로운 DEPTNO(50)를 가지고 입력한다.

  ⑥ rslt를 출력해 보면 '입력 완료!!' 라고 출력된다.

  ⑦ DEPT 테이블을 조회하여 보면 DEPTNO가 50인 데이터가 정확하게 저장되었음을 확인할 수 있다.

SQL Server SELECT * FROM DEPT; -----------------① 
DEPTNO DNAME LOC 
------- ---------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON DECALRE
@v_result VARCHAR(100) -----------------② 
EXECUTE dbo.p_DEPT_insert 10, 'dev', 'seoul', @v_result=@v_result OUTPUT -----------------③ 
SELECT @v_result AS RSLT -----------------④ 
RSLT 
-------------------------------- 
이미 등록된 부서번호이다 

DECALRE @v_result VARCHAR(100) -----------------⑤ 
EXECUTE dbo.p_DEPT_insert 50, 'dev', 'seoul', @v_result=@v_result OUTPUT -----------------⑥ 
SELECT @v_result AS RSLT -----------------⑦ 
RSLT 
-------------------------------- 
입력 완료! 

SELECT * FROM DEPT;----------------⑧ 
DEPTNO DNAME LOC
------- -------- --------- 
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON 
50 NewDev SEOUL 5개의 행에서 선택되었다.

  ① DEPT 테이블을 조회하면 총 4개 행의 결과가 출력된다.

  ② Procedure를 실행한 결과 값을 받을 변수를 선언한다.

  ③ 존재하는 DEPTNO(10)를 가지고 Procedure를 실행한다.

  ④ DEPTNO가 10인 부서는 이미 존재하기 때문에 변수 rslt를 print해 보면 ‘이미 등록된 부서번호이다’라고 출력된다.

  ⑤ Procedure를 실행한 결과 값을 받을 변수를 선언한다.

  ⑥ 이번에는 새로운 DEPTNO(50)를 가지고 입력한다.

  ⑦ rslt를 출력해 보면 ‘입력 완료!’라고 출력된다.

  ⑧ DEPT 테이블을 조회하여 보면 DEPTNO가 50인 데이터가 정확하게 저장되었음을 확인할 수 있다.