PL/SQL

• SQL 중간에 분기처리 또는 조회 결과를 변수로  사용하거나 예외처리를 하는 기능은 제공되지 않는다. 
  이러한 SQL의 단점을 개선해서 나온것이 바로 PL/SQL(Procedural Language extension to SQL)이다.

PL/SQL 정의

• SQL을 확장한 절차적 언어.
• 여러 SQL을 하나의 블록(Block)으로 구성하여 SQL 제어
  • Commit, Rollback, 예외처리 등
• 조건문(IF),반복문(LOOP)을 사용하여 대용량 테이터 처리 할때, 데이터 분할하여 처리할 수 있음
• 동적 SQL를 통하여 문자열로 작성된 SQL 실행할 수 있으며, DML, DDL, DCL 사용 할수 있음

기본구조

DECLARE, BEGIN, EXCEPTION, END 한 블록(Block)을 이룸

DECLARE -- 변수 정의영역
BEGIN -- 처리 로직 시작
EXCEPTION -- 예외처리
END -- 처리 로직 종료
;

• DECLARE : 변수 정의 영역, 생략가능
• BEGIN : 작업영역, BEGIN과 END 반드시 한쌍으로 작성
• EXCEPTION : 예외처리 영역으로 BEGIN과 END 사이에 작성, 생략가능
• END : 작업 종료

한개의 블록(Block)은 BEGIN과 END 사이 여러번 사용

-- Block 1
DECLARE

BEGIN
	--Block2
    DECLARE
    
	BEGIN
    	--Block 2-1
        DECLARE
        
        BEGIN
        
        EXCEPTION
        
        END
        ;
    EXCEPTION
    
    END
    ;
    --Block 3
    DECLARE
    
    BEGIN
    
    EXCEPTION
    
    END
    ;
EXCEPTION

END
;

DEPTNO가 10인 부서의 부서번호, 부서명, 지역을 조회하는 PL/SQL

--실행 결과를 출력하도록 설정
SET SERVEROUTPUT ON

--스크립트 경과 시간을 출력하도록 설정
SET TIMING ON

DECLARE-- 변수 정의 영역
	V_STRD_DT       VARCHAR2(8);
    
    V_STRD_DEPTNO   NUMBER;
    
    V_DEPTNO        NUMBER;
    V_DNAME         VARCHAR2(50);
    V_LOC           VARCHAR2(50);
    
    V_RESULT_MSG    VARCHAR2(500) DEFAULT 'SUCCESS';
    
BEGIN -- 작업영역
	-- 기준일자 - 내장함수 사용.
	V_STRD_DT := TO_CHAR(SYSDATE, 'YYYYMMDD');
    -- 조회 부서번호 변수 설정
    V_STRD_DEPTNO := 10;
    
    BEGIN
    --WHGHL -INTO절로 조회된 데이터 저장
    	SELECT T1.DEPTNO
             , T1.DNAME
             , T1.LOC
          INTO V_DEPTNO
             , V_DNAME
             , V_LOC
          FROM SCOTT.DEPT T1
         WHERE T1.DEPTNO = V_STRD_DEPTNO
        ;
    END
    ;
    --조회 결과 변수 설정
    V_RESULT_MSG := 'RESULT > DEPTNO='||V_DEPTNO||', DNAME='||V_DNAME||', LOC='||V_LOC;
    
    --조회 결과 출력
    DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
    
EXCEPTION --예외 처리
    WHEN OTHERS THEN
        V_RESULT_MSG := 'SQLCODE['||SQLCODE||'], MESSAGE =>'||SQLERRM;
        
        DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
 
END
;

 

상세구조

DECLARE : 변수를 정의하는 영역

DECLARE     
  V_STRD_DT       VARCHAR2(8);
    
    V_STRD_DEPTNO   NUMBER;
    
    V_DEPTNO        NUMBER;
    V_DNAME         VARCHAR2(50);
    V_LOC           VARCHAR2(50);
    
    V_RESULT_MSG    VARCHAR2(500)    DEFAULT 'SUCCESS';

• IDENTIFIER[CONSTANT] DATATYPE [NOT NULL] [DEFAULT값];
• CONSTANT
  
  • 상수 의미, 초기값이 있어야 하며 값 변경 불가능
• DATATYPE 
  • 컬럼의 기본타입
  • EXCEPTION, CURSOR, %ROWTYPE 등 확장된 타입 있음
• NOT NULL
  • 무조건 값 존재
• DEFAULT
  • : = '초기값' 으로 사용

BEGIN : 작업 영역

BEGIN
    /**
     * DEPTNO가 10인 부서의 부서번호, 부서명, 지역을 조회.
     */
    
    --기준일자 - 내장함수 사용.
    V_STRD_DT := TO_CHAR(SYSDATE, 'YYYYMMDD');
    
    --조회 부서번호 변수 설정
    V_STRD_DEPTNO := 10;
    
    BEGIN
        --조회 - INTO절로 조회된 데이터 저장.
        SELECT T1.DEPTNO
             , T1.DNAME
             , T1.LOC
          INTO V_DEPTNO
             , V_DNAME
             , V_LOC
          FROM SCOTT.DEPT T1
         WHERE T1.DEPTNO = V_STRD_DEPTNO
        ;
    END
    ;
 
    --조회 결과 변수 설정
    V_RESULT_MSG := 'RESULT > DEPTNO='||V_DEPTNO||', DNAME='||V_DNAME||', LOC='||V_LOC;
    
    --조회 결과 출력
    DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );

• 변수에 대입할 때 := 사용
• TO_CHAR, TO_DATE 와 같은 내장 함수 사용 가능
• INTO절 사용하여 SELECT절에서 조회한 결과를 변수 대입
  • 반드시 1개의 행만 조회
  • 조회된 컬럼의 순서에 따라 변수 값 저장
  • 데이터 타입DL  %ROWTYPE인 경우 하나의 변수에 모든 컬럼 값 지정

EXCEPTION과 END : 예외처리와 작업 종료

EXCEPTION
    WHEN OTHERS THEN
        V_RESULT_MSG := 'SQLCODE['||SQLCODE||'], MESSAGE =>'||SQLERRM;
        
        DBMS_OUTPUT.PUT_LINE( V_RESULT_MSG );
 
END
;

• 상위 조건에는 사용자 정의 예외(USER DEFINED EXCEPTION) 처리
• WHEN OTHERS THEN 구문은 상위 조건에 부합하는것이 없을 경우 처리 의미
• ELSE  같음
• SQLCODE와 SQLERRM 은 내장변수로 오류코드와 메세지 담고 있음
• EXCEPTION 처리

종류	내  용
NO_DATA_FOUND	선택된 행이 없는 경우 (SELECT.. INTO)
TOO_MANY_ROWS	여러 행이 리턴 되는 경우 (SELECT.. INTO)
DUP_VAL_ON_INDEX	UNIQUE인덱스가 걸린 컬럼에 중복 데이터를 입력할 때
VALUE_ERROR	값을 할당하거나 변환할 때 오류가 나는 경우
INVALID_NUMBER	숫자로 변환이 되지 않는 경우(문자형 데이터)
NOT_LOGGED_ON	DB에 접속하지 않은 채 실행하는 경우
LOGIN_DENIED	잘못된 사용자나 비밀번호로 로그인을 시도할 때
ZERO_DIVIDE	0으로 나누려고 할 때
INVALID_CURSOR	허용되지 않은(열리지 않은) 커서에 접근하는 경우

DECLARE 
   v_name varchar2(20);
BEGIN
   SELECT  lprod_nm  INTO v_name FROM lprod WHERE lprod_gu >= 'P201'; 
   DBMS_OUTPUT.PUT_LINE ('분류명 =' || v_name);

EXCEPTION 
        WHEN  NO_DATA_FOUND  THEN 
            DBMS_OUTPUT.PUT_LINE ( '해당 정보가 없습니다.');
        WHEN  TOO_MANY_ROWS  THEN 
            DBMS_OUTPUT.PUT_LINE (' 한개 이상의 값이 나왔습니다. ');
        WHEN  OTHERS  THEN  
            DBMS_OUTPUT.PUT_LINE ( '기타 에러 :' || SQLERRM  ); 
END;