DB/SQL

PL/SQL

lavender1122 2024. 1. 4. 11:22
  • 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;