[Oracle] PL/SQL

 PL/SQL 

 - Procedure Language - SQL
 - 구조적 언어의 SQL의 제한적 성질( 분기문, 반복븜, 변수의 부재)
 - block 구조로 여러 ISQL문을 한번에 실행 가능
 - 모듈화/캡슐화가 가능
 - 서버에 저장디어 빠른 실행과 반복사용 가능
 - 표준 문법이 없음( 각 DBMS에 종속적)
 - 익명블록(Anonymous Block), User Defined Function, Stored PRocedure, Trigger, Package 등 제공

 

1. 익명 블록

   - 이름이 없는 블록
   - PL/SQL의 기본 구조 제공
   - 선언부/실행부로 구성
   
 (구성요소)

  DECLARE
    선언부 : 변수/상수/커서 선언
  BEGIN 
    실행부 : 처리항 비지니스 로직을 구현한 SQL 문
      :
    [EXCEPTION
        예외처리;
    ]
 END;

 
 사용예) 202SUS 5월 가장 많이 구매한 고객의; 회운번호, 이름 주소 구매액을 출력하는 익명 블록
     

	DECLARE
            L_MID MEMBER.MEM_ID%TYPE; --회원번호
            L_MNAME VARCHAR2(100);  --회원명
            L_ADDR VARCHAR2(255);    --주소
            L_AMT NUMBER:=0;    --구매금액
        BEGIN
            --2020 5월 가장 많이 구매한 고객
            SELECT TA.CID, TB.MEM_NAME, TB.MEM_ADD1 || ' ' || MEM_ADD2, TA.CSUM
              INTO L_MID,L_MNAME,L_ADDR,L_AMT
              FROM (SELECT A.CART_MEMBER AS CID,
                           SUM(A.CART_QTY*B.PROD_PRICE) AS CSUM
                      FROM CART A, PROD B
                     WHERE A.CART_PROD=B.PROD_ID
                       AND A.CART_NO LIKE'202005%'
                     GROUP BY A.CART_MEMBER
                     ORDER BY 2 DESC) TA, MEMBER TB
             WHERE TA.CID = TB.MEM_ID
               AND ROWNUM=1; --ROWNUM은 GROUP/ORDER 보다 더 빨리 실행되어져서 서브쿼리로 넣어 줌
               
          DBMS_OUTPUT.PUT_LINE('회원번호 : ' || L_MID);
          DBMS_OUTPUT.PUT_LINE('회원명 : ' || L_MNAME);
          DBMS_OUTPUT.PUT_LINE('회원주소 : ' || L_ADDR);
          DBMS_OUTPUT.PUT_LINE('구매금액 : ' || L_AMT);
          
        EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('오류발생 : ' || SQLERRM);
        END;
        
-- := 하나의 연산자에 할당연산자 오른쪽에 있는걸 왼쪽에 있는걸로 넣으세요

    

    
    

 1. 변수와 상수

  (사용형식)
   식별자 [CONSTANT] 데이터타입 [:=초기값]
    . CONSTANT : 식별자를 상수로 선언할때 사용
    . 데이터타입 : 오라클에서 사용하는 모든 타입 사용 가능
    . := 초기값 을 생략하면 '식별자'에는 NULL값을 초기화 됨
    
 **오라클 변수의 종류와 선언 형식
  (1) SCLAR 변수
    . 일반적인 변수로 하나의 값만 저장하는 변수
    . 선언은 위의 사용형식에 따란 선언
    . EX) L_AMT NUMBER(5) :=0;
 
  (2) REFERENCE 변수
    . 해당 테이블의 행이나 컬럼타입을 참조라는 변수
    . (사용형식)
    . 식별자 [CONSTANT] 테이블명.컬럼명 %TYPE [:=초기값] -- COLNUM참조
    . 식별자 [CONSTANT] 테이블명%ROWTYPE [:=초기값]   -- ROW(행) 참조
    
    . EX)

	L_MILEAGE MKEMBER.MEM_MILEAGE%TYPE 
        --  M_MILEAGE변수의 타입이 MEMBER테이블의 MEM)MILEAGE컬럼의 타입과 크기가 같게 선언
        L_CART_REC CART%ROWTYPE; 
        -- L_CART_REC 변수는 CART 테이블 한 행과 같은 타입으로 선언됨. 
        -- 따라서 L_CART_REC로 CART테이블의 각 행을 참조 시킬수 있는 값의 형태는
        -- CART_MEMBER에 저장된 자료는 L_CART_REC.CART_MEMBER로 
           CART_NO L_CART_REC.CART_NO로
           CART_PROD는 L_CART_REC.CART_NO로 각각 참조 가능함

           
  (3) BIND 변수
    . 프로시저와 함수에서 사요하여 매개변수의 전달을 담당하는 변수
    . EX) 

CREATE OR REPLACR FUNCTION IN_CREATE_CAET_NO (
            P_DATE IN DATE,
            P_MEM_ID IN MEMBER.MEM_ID%TYPE)
                :
        END;
        
        CREATE OR REPLACE PROCEDURE proc_sales_report(
            P_PERIOD IN VARCHAR2,
            P_RESULT OUT VARCHAR2)
                :
            END;
       BOOLEAN : T/F/N 값을 담음

            
   
   

 2) 분기문 

   - IF문이 제공됨

(사용형식-1)
    IF 조건 THEN
        명령문1;
    [ELSE
        명령문2;]
    END IF;
    
(사용형식-2)
     IF 조건 THEN
        명령문1;
     [ELSIF 조건 THEN   -- ELSE IF 쓸때 ELSIF E 가 빠짐
        명령문2;]
          :
     END IF;
     
(사용형식-3)
     IF 조건 THEN
        IF 조건 THEN
            명령문1;
        ELSE 명령문2;
        END IF;
    [ELSE 명령문3;]
    END IF;

         
 사용예) 회원들의 마일리지를 조회하여 평균값보다 큰 마일리지를 보유한 회원은 '우수회원 입니다'라믐 메시지를, 
        평균보다 적으면 '분발하세요'라는 메시지를 출력하시오
        출력은                 평균 마일리지 :
        --------------------------------------------
            회원번호    회원명   보유말일리지    비고
        --------------------------------------------
        
        
        --------------------------------------------
        
        처리인원 :  명

        DECLARE
          L_AMILE NUMBER:=0;    -- 평균마일리지
          L_MID MEMBER.MEM_ID%TYPE;     -- 회원번호
          L_MNAME MEMBER.MEM_NAME%TYPE; -- 회원명
          L_MILE NUMBER:=0;     -- 보유마일리지
          L_REMAEKS VARCHAR2(100); -- 결과 메시지
        
          CURSOR cur_member01 IS 
           SELECT MEM_ID, MEM_NAME, MEM_MILEAGE
             FROM MEMBER;
             
        BEGIN
         OPEN cur_mrmber01;
         
         SELECT AVG(MEM_MILEAGE) INTO L_AMILE
           FROM MEMBER;
           
        DBMS_OUTPUT.PUT_LINE(LPAD('평균마일리지 :',42,' ') || L_AMILE);
        DBMS_OUTPUT.PUT_LINE(' ------------------------------------');
        DBMS_OUTPUT.PUT_LINE('회원번호   회원명    마일리지   비고');
        DBMS_OUTPUT.PUT_LINE(' ------------------------------------');

         LOOP
          FETCH cur_mrmber01 INTO L_MID, L_MNAME, L_MILE;
          EXIT WHEN cur_member01%NOTFOIND;
          IF L_MILE>LAMLIE THEN
             L_REMARKS := '우수회원 입니다';
          ELSE
            L_REMARKS := '분발하세요'
          END IF;
          DBMS_OUTPUT.PUT_LINE(LPAD(L_MID,7,' '));
          DBMS_OUTPUT.PUT('     ');
          DBMS_OUTPUT.PUT(PRAD(L_MID));
          DBMS_OUTPUT.PUT(LPAD(L_MNAME,8,' '));
          DBMS_OUTPUT.PUT(TO_CHAR(L_MILE,'999,999'));
          DBMS_OUTPUT.PUT('     ');
          DBMS_OUTPUT.PUT_LINE(L_REMARKS);
          DBMS_OUTPUT.PUT_LINE('     ');
         END LOOP;
         
        DBMS_OUTPUT.PUT_LINE('처리인원 : '||cur_member01.ROWCOUNT || '명')
          CLOSE cur_member01;
            
        
        END;

 

 사용예) 2020년 7월 28일이라고 가저하고 cart_no를 생성하는 함수를 작성하시오

	CREATE OR REPLACE FUNCTION fn_create_cartno(
            P_DATE IN DATE)
            RETURN VARCHAR2
        IS 
            L_FLAG NUMBER(2):=0;
            L_TMP_CARTNO CHAR(20);
        BEGIN
            SELECT COUNT(*) INTO L_FLAG
              FROM CART
             WHERE TO_DATE(SUBSTR(CART_NO,1,8))=P_DATE;
             
            IF L_FLAG=0 THEN
               L_TMP_CARTNO:=TO_CHAT(P_DATE,'YYYYMMDD') || TRIM('00001') ;
            ELSE
               SELECT TO_CHAR(TO_NUMBER(A.CART_NO)+1) INTO L_TMP_CARTNO
                 FROM (SELECT CART_NO
                         FROM CART
                        WHERE TO_DATE(SUBSTR(CART_NO,1,8))=TO_CHAR(P_DATE,'YYYYMMDD')
                        ORDER BY 1 DESC) A
                  AND ROWNUM=1;
            END IF;
            RETURN L_TMP_CARTNO;
        END;

 

  CREATE OR REPLACE FUNCTION fn_create_cartno(
        P_DATE IN DATE)
        RETURN VARCHAR2
      IS 
        L_FLAG NUMBER(2) := 0;
        L_TMP_CARTNO VARCHAR2(20);
      BEGIN
        SELECT COUNT(*) INTO L_FLAG
          FROM CART
        WHERE SUBSTR(CART_NO, 1, 8)=TO_CHAR(P_DATE,'YYYYMMDD');
        
        IF L_FLAG = 0 THEN 
           L_TMP_CARTNO :=TO_CHAR(P_DATE, 'YYYYMMDD') || TRIM('00001');
        ELSE
           SELECT TO_CHAR(TO_NUMBER(A.CART_NO)+1) INTO L_TMP_CARTNO
                 FROM (SELECT CART_NO
                       FROM CART
                       WHERE SUBSTR(CART_NO, 1, 8)=TO_CHAR(P_DATE, 'YYYYMMDD')
                       ORDER BY 1 DESC) A
            WHERE ROWNUM=1;
      END IF;
      RETURN L_TMP_CARTNO;
  END;
  
실행)
    INSERT INTO CART VALUES('m001', fn_create_cartno(SYSDATE),'P302000005',5);

          

  3) 반복문

  - 주로 커서 처리를 위한 목적으로 사용
  - LOOP, WHILE, FOR문이 제공 됨
  
  1) LOOP 문
    . 반복문의 기본 구조를 제공
    . 무한루프 제공

 (사용형식)
    LOOP
         반복 수행 명령문
           :
        [EXIT WHEN 조건;]
    END LOOP;
    . 'EXIT WHEN 조건' : 조건이 참이면 반복을 벗아님 (END LOOP 다음 명령 수행)

    
** 커서
    - 커서는 오라클 SQL명령으로 영향 받은 결과 집합(넓은 의미의 커서)
    - 협의의 커서는 SELECT문의 결과 집합
    - 개발자가 결과 집합내부의 자료를 접근할 수 있는 방법을 제공
    - 커서 속성
    --------------------------------------------
        커서속성              내용
    --------------------------------------------
     SQL%ISOPEN     커서가 OPEN 된 상태이면 TRUE 반환
     SQL%NOTFOUND   결과 집합에 자료가 없음면 TRUE
     SQL%FOUND      결과 집합에 자료가 있면 TRUE
     SQL%ROWCOUNT   커서내의 행의 갯수
    --------------------------------------------

    가) 익명커서
        . 일반적인 SELECT문의 결과로 이름을 부여하지 않은 커서
        . 결과가 출력될때 OPEN 되엇다가 출력이 종료되면 즉시 CLOSE 됨
        . 개발자가 커서 내부에 접근할 수 없음 (SQL%ISOPEN 값이 늘 FALSE)
    나) 명시적 커서
        . 이름을 부여한 커서
        .개발자가 커서 내부에 접근할 수 이씀
      (사용형식)
        CURSOR 커서명 [(변수명list)] IS
        . '변수list' : 커서 OPEN 문에서 전달되는 값을 보관하는 바인딩 변수로 크기를 선언하지 않음
        . 커서의 사용 절차
            커서선언(선언부) → 커서 OPEN → 커서 FETCH → 커서 종료(CLOSE)
            
        . 커서 OPEN
          - 사용할 커서를 OPEN
          - (기술형식)
          OPEN 커서명 [(변수lsit)}
          
        . 커서 FETCH
          - 커서 집합의 자료를 행단위로 읽어올때 사용
          - (기술 형식)
          FETCH 커서명 INTO 변수 list;
            . 커서문의 SELECT정의 컬럼 값을 INTO 다음 변수에 저장
            . 보통 반복문 안에 기술

 

        . 커서 종료(CLOSE)
          - OPEN 된 커서를 닫음
          - CLOSE 되지 않은 커서는 재 OPEN
          
            
            
  사용예) 키보드로 10-110 사이의 부서번호를 입력 받아 해당 부서에 근무하는 사원 정보를 출력하시오
         (커서 사용) 사원번호, 사워명, 입사일, 급여

 ACCEPT P_DEPT PROMPT '부서번호 입력(10~110) : '
DECLARE
    L_ID HR.EMPLOYEES.EMPLOYEE_ID%TYPE;    -- 사원번호
    L_NAME VARCHAR2(200);   -- 사원명
    L_HDATE DATE;   -- 입사일
    L_SAL NUMBER := 0; -- 급여
    
    CURSOR cur_emp_dept(DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE) IS
    SELECT EMPLOYEE_ID, EMP_NAME, HIRE_DATE, SALARY
      FROM HR.EMPLOYEES
     WHERE DEPARTMENT_ID = DID;
     
BEGIN
    OPEN cur_emp_dept(TO_NUMBER('&P_DEPT'));
                
    DBMS_OUTPUT.PUT_LINE(' 부서 번호 : ' || '&P_DEPT');
    
    LOOP
        FETCH cur_emp_dept INTO L_ID, L_NAME, L_HDATE, L_SAL;
        EXIT WHEN cur_emp_dept%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('---------------------------');
        DBMS_OUTPUT.PUT_LINE(' 사원번호 : ' || L_ID);
        DBMS_OUTPUT.PUT_LINE(' 사원명 : ' || L_NAME);
        DBMS_OUTPUT.PUT_LINE(' 입사일 : ' || L_HDATE);
        DBMS_OUTPUT.PUT_LINE(' 급여 : ' || L_SAL);
        DBMS_OUTPUT.PUT_LINE('---------------------------');

    END LOOP;
    DBMS_OUTPUT.PUT_LINE('사원수 : ' || cur_emp_dept%ROWCOUNT);
    CLOSE cur_emp_dept; -- CLOSE 문에 세미콜론 추가

END;

    2) WHILE 문
    . JAVA의 WHILE과 비슷한 구조 및 기능 제공

 (사용형식)
    WHILE 조건 LOOP
         반복 수행 명령문
           :
    END LOOP;
      - '조건'이 참이면 반복수행하고 '조건'이 거짓이면 END LOOP 이후의 명령 수행

        
           
  사용예) 거주지가 '충남'인 회원들을 출력하시오. 회원번호 회원명 주소
  

DECLARE
    L_MID MEMBER.MEM_ID%TYPE;
    L_MNAME MEMBER.MEM_NAME%TYPE;
    L_ADDR VARCHAR2(300);
    
    CURSOR cur_member IS
        SELECT MEM_ID, MEM_NAME, MEM_ADD1 || ' ' || MEM_ADD2
          FROM MEMBER
         WHERE MEM_ADD1 LIKE '충남%';
BEGIN
    OPEN cur_member;
    FETCH cur_member INTO L_MID, L_MNAME, L_ADDR;
    WHILE cur_member%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(L_MID || '  ' || L_MNAME || '  ' || L_ADDR);
        FETCH cur_member INTO L_MID, L_MNAME, L_ADDR;
    END LOOP;
    CLOSE cur_member;
END;

    3) FOR 문
    . JAVA의 FOR과 비슷한 기능 제공

 (사용형식)
    FOR 제어변수 IN [REVERSE] 초기값 .. 초종값 LOOP
        반복처리문;
    END LOOP;
    
    (커서 처리용 FOR문)
    FOR 레코드명 IN 커서명 | 커서용 SELECT 문 LOOP
     반복처리문
        :
    END LOOP;

     **  FOR 문으로 커서를 사용하는 경우 OPEN/FETCH/CLOSE 문이 생략 됨
         선언부에서 선언된 커서를 사용하거나(커서명) 도는 커서 구성에
         사용되는 SELECT 문을 직접 기술 할 수 업다
         
     ** FOR문을 사용할 경우 커서내의 컬럼 참조는
        레코드명, 컬럼명을 참조함

  사용예) 거주기가 '충남'인 회원들을 출력하시오 회원번호 회원명, 주소

        DECLARE
            CURSOR cur_for_cursor IS
                SELECT MEM_ID, MEM_NAME, MEM_ADD1|| '  ' || MEM_ADD2 AS ADDR
                  FROM MEMBER
                 WHERE MEM_ADD1 LIKE '충남%';
        BEGIN
            FOR MEM_REC IN (SELECT MEM_ID, MEM_NAME,MEM_ADD1|| '  ' || MEM_ADD2 AS ADDR
                              FROM MEMBER
                             WHERE MEM_ADD1 LIKE '충남%') LOOP
                DBMS_OUTPUT.PUT_LINE(MEM_REC.MEM_ID|| '  ' || MEM_REC,MEM_NAME||'  '||
                                     MEM_REC.ADDR);
            END LOOP;
        END;
        
        DECLARE
            CURSOR cur_for_cursor IS
                SELECT MEM_ID, MEM_NAME, MEM_ADD1 || '  ' || MEM_ADD2 AS ADDR
                  FROM MEMBER
                 WHERE MEM_ADD1 LIKE '충남%';
        BEGIN
            FOR MEM_REC IN cur_for_cursor LOOP
            DBMS_OUTPUT.PUT_LINE(MEM_REC.MEM_ID || '  ' || MEM_REC.MEM_NAME || '  ' || MEM_REC.ADDR);
            END LOOP;
        END;

            
            
  사용예) 키보드로 10-110 사이의 부서번호를 입력 받아 해당 부서에 근무하는 사원 정보를 출력하시오
         (커서 사용) 사원번호, 사워명, 입사일, 급여

ACCEPT P_DEPT PROMPT '부서번호 입력(10~110) : '
DECLARE
    L_ID HR.EMPLOYEES.EMPLOYEE_ID%TYPE;    -- 사원번호
    L_NAME VARCHAR2(200);   -- 사원명
    L_HDATE DATE;   -- 입사일
    L_SAL NUMBER := 0; -- 급여
    
    CURSOR cur_emp_dept(DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE) IS
    SELECT EMPLOYEE_ID, EMP_NAME, HIRE_DATE, SALARY
      FROM HR.EMPLOYEES
     WHERE DEPARTMENT_ID = DID;
     
BEGIN
    OPEN cur_emp_dept(TO_NUMBER('&P_DEPT'));
                
    DBMS_OUTPUT.PUT_LINE(' 부서 번호 : ' || '&P_DEPT');
    
    LOOP
        FETCH cur_emp_dept INTO L_ID, L_NAME, L_HDATE, L_SAL;
        EXIT WHEN cur_emp_dept%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('---------------------------');
        DBMS_OUTPUT.PUT_LINE(' 사원번호 : ' || L_ID);
        DBMS_OUTPUT.PUT_LINE(' 사원명 : ' || L_NAME);
        DBMS_OUTPUT.PUT_LINE(' 입사일 : ' || L_HDATE);
        DBMS_OUTPUT.PUT_LINE(' 급여 : ' || L_SAL);
        DBMS_OUTPUT.PUT_LINE('---------------------------');

    END LOOP;
    DBMS_OUTPUT.PUT_LINE('사원수 : ' || cur_emp_dept%ROWCOUNT);
    CLOSE cur_emp_dept; -- CLOSE 문에 세미콜론 추가

END;