PL/SQL 프로시저/패키지

 

▶ PL/SQL (Procedural Language extenstion to SQL)

- SQL을 확장한 절차적 언어(Procedural Language)이다.

(. 관계형 데이터베이스 시스템(RDBMS) 에서 사용하는 표 준 언어인 SQL 은 절차형 언어가 아님.

 SQL의  DDL/DML/DCL 을 통해 데이터베이스를 정의 조작 제어 하는 것이 가능하지만 SQL은 결국 데이터의 처리 집합 만을 기술할 뿐  어떠한 절차를 걸쳐서 데이터 집합을 얻어야 하는지에대한 절차적 과정을 제공하지는 않습니다.  일반적인 SQL 로직을 작성하면 이 문장의 실행순서는 옵티마이저에 의해 이루어지는데 , 이는 여러가지 데이터베이스 통계를 기초로 실행 게획을 처리하기 때문에 데이터를 일반적으로 빠르게 다루어 주지만 결국 개발자 입장에서는 융통성 있는 데이터 처리는 불가능 합니다.

 이러한 단점을 극복하기 위한 대안으로 대부분의 상용 DBMS 는 절자척으로 작성이 가능한 PL/SQL을 제공합니다.  PL/SQ은 절차적 언어의 특성을 가지고 있기 때문에 원하는 결과, 제약조건, 처리 절차를 개발자가 작성해야 합니다.

)

 

- 관계형 데이터 베이스에서 사용되는 Oracle의 표준데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다.

 - 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보낸다.

- PL/SQL 프로그램의 종류는 크게 Procedure,Function,Trigger로 나뉘어 진다.

- 오라클에서 지원하는 프로그래밍 언어의 특성을 수용하여 SQL에서는 사용할 수 없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완하였다.

 

▶ 기본 PL/SQL 구조 

영역	설명	옵션
DECLARE(선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분 DECLARE로 시작 => 변수/상수/커서 등을 선언	옵션
BEGIN(실행부)	절자척 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 실제 처리할 로직을 기술할 수 있는 부분. BEGIN으로 시작	필수
EXCEPTION(예외처리부)	PL/SQL문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외 사항이라고함 이러한 예외사항이 발생했을때 이를 해결하기 위한 문장을 기술 하는 부분	옵션
END(실행문종료)		필수

 

 

연습! 

 

콘솔에 찍히는걸 활성화 

/
SET SERVEROUTPUT ON;
/

 

 

 

** PLSQL에서 SELECT를 쓰면 반드시 INTO를 써야함 

 

/
CREATE OR REPLACE FUNCTION FN_MEMNAME
    RETURN VARCHAR2
IS
    R_NM VARCHAR2(100);
BEGIN
    SELECT EMP_NM INTO R_NM
    FROM EMP
    WHERE EMP_NUM= 'EMP001';
    
    DBMS_OUTPUT.PUT_LINE('R_NM : ' || R_NM);
    
    RETURN R_NM;
END;
/

 

실행

SELECT FN_MEMNAME FROM SYS.DUAL;

 

 

 

--매개변수(바인드)가 있는 프로시저 

EMP_NUM으로 EMP_NM을 찾기

--자바의 매개변수 = PL/SQL에서 IN바인드 변수
CREATE OR REPLACE FUNCTION FN_MEMNAME(P_EMP_NUM IN VARCHAR2)
    RETURN VARCHAR2
IS
    R_NM VARCHAR2(100);
BEGIN
    SELECT EMP_NM INTO R_NM
    FROM EMP
    WHERE EMP_NUM= P_EMP_NUM;
    
    DBMS_OUTPUT.PUT_LINE('R_NM : ' || R_NM);
    
    RETURN R_NM;
END;

실행

SELECT FN_MEMNAME('EMP001') FROM SYS.DUAL;

 

 

 

 

--PACKAGE

--선언부
CREATE OR REPLACE PACKAGE PKG_GET_NM
IS
    FUNCTION FN_MEMNAME(P_EMP_NUM IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION FN_GET_CUS_NM(P_CAR_NUM IN VARCHAR2) RETURN VARCHAR2;
END;
/
--본문
CREATE OR REPLACE PACKAGE BODY PKG_GET_NM
IS
    --첫번재 함수 BODY
    FUNCTION FN_MEMNAME(P_EMP_NUM IN VARCHAR2) RETURN VARCHAR2
    IS
        --변수 : SCALAR변수, BIND변수, COMPOSITE변수, REFERENCE변수
        R_NM VARCHAR2(100);
        --R_PAY EMP.EMP_PAY%TYPE; --NUMBER
    BEGIN
        SELECT EMP_NM INTO R_NM
        FROM   EMP
        WHERE  EMP_NUM = P_EMP_NUM;
        
        RETURN R_NM;
    END FN_MEMNAME;
    
    --두번재 함수 BODY
    FUNCTION FN_GET_CUS_NM(P_CAR_NUM IN VARCHAR2) RETURN VARCHAR2
    IS
        --REFERENCE변수
        R_CUS_NM CUS.CUS_NM%TYPE;   --VARCHAR2(30)
    BEGIN
        SELECT B.CUS_NM INTO R_CUS_NM
        FROM   CAR A, CUS B
        WHERE  A.CUS_NUM = B.CUS_NUM
        AND    A.CAR_NUM = '12가1234';
        
        RETURN R_CUS_NM;
    END FN_GET_CUS_NM;
END PKG_GET_NM;
/