[Oracle SQL] PL/SQL-익명블록

고동이의 IT·2021년 10월 28일
0

Oracle SQL

목록 보기
27/31

> # PL/SQL (Procedual Language SQL)

  • 표준 SQL에 기능을 확장한 문법제공(분기,반복, 변수등)

  • 미리 컴파일되어 저장되기 때문에 처리속도와 효율성 증가

  • 모듈화 및 캡슐화 기능 제공

  • 표준 문법이 없음

  • 각 DBMS에 종속적

  • 익명블록(Anonymous block), Procedure, Function, Trigger, Package 등 제공됨

    1. 익명블록

  • 단순 스크립트에서 실행되는 블록(이름이 없음)

  • 저장불가

  • pl/sql의 기본구조

(사용형식)
DECLARE
선언부; --변수, 상수, 커서 선언
BEGIN
실행부;-- 문제해결을 위한 비즈니스 로직 처리 SQL문
[EXCEPTION
예외처리부;]
END;

사용예) 키보드로 부서번호를 입력 받아 해당부서의 주소, 인원수, 평균급여를 출력하는 익명블록을 작성하시오

   ACCEPT P_DEPT_NO PROMPT '부서코드 입력(10-110) : '
   DECLARE 
     V_DEPT_NO HR.DEPT.DEPARTMENT_ID%TYPE := TO_NUMBER('&P_DEPT_NO'); --% 참조타입: 해당되어지는 컬럼의 데이터타입과 똑같은 변수선언가능
     V_CNT NUMBER:=0; --NUMBER 타입 초기화하지 않고 사용하면 결과안나오기 때문에 초기화
     V_ASAL NUMBER:=0;
     V_ADDR VARCHAR2(200);
   BEGIN --BEGIN절에서만 SELECT-INTO-FROM절 사용
    SELECT  A.STREET_ADDRESS||A.CITY||' '||A.STATE_PROVINCE
      INTO V_ADDR
      FROM HR.LOCATIONS A, HR.DEPT B
      WHERE A.LOCATION_ID=B.LOCATION_ID
        AND B.DEPARTMENT_ID=V_DEPT_NO;
        
        
     SELECT COUNT(EMPLOYEE_ID),
            ROUND(AVG(SALARY))
       INTO V_CNT, V_ASAL
       FROM HR.EMP
      WHERE DEPARTMENT_ID=V_DEPT_NO;
      
      --DBMS_OUTPUT.PUT_LINE 자바에서의 SOP랑 동일
      DBMS_OUTPUT.PUT_LINE('부서번호 : '||V_DEPT_NO);
      DBMS_OUTPUT.PUT_LINE('주소 : '||V_ADDR);
      DBMS_OUTPUT.PUT_LINE('인원수 : '||V_CNT);
      DBMS_OUTPUT.PUT_LINE('평균급여 : '||V_ASAL);
      
   END;
   

1) 변수와 상수

  • SCALR 변수, REFERENCES 변수제공
    (선언형식)
    변수(상수)명 [CONSTANT] 데이터타입|참조타입 [:=초기값]; --반드시 초기값을 지정
  • 데이터 타입
    . 표준 오라클에서 사용하는 데이터 타입
    . PLS_INTEGER, BINARY_INTEGER : 4 BYTE 정수(-2147483648~2147483647)
    . BOOLEAN 타입(true, false, null)
  • 참조타입
    . 열참조타입: 테이블명.컬럼명%TYPE
    . 행참조타입: 테이블명%ROWTYPE --자바의 ArrayList와 같음

사용예) 임의의 부서코드를 발생시켜 해당 부서에 속한 사원 중 가장 많은 급여를 받는 사원하나의
사원번호, 사원명, 입사일, 직책코드, 급여를 출력하는 익명블록을 작성하시오

          DECLARE
           V_EMP_ID HR.EMP.EMPLOYEE_ID%TYPE;
           V_ENAME HR.EMP.EMP_NAME%TYPE;
           V_JOB HR.EMP.JOB_ID%TYPE;
           V_SAL HR.EMP.SALARY%TYPE;
           V_DEPT_ID HR.DEPT.DEPARTMENT_ID%TYPE;
          BEGIN
            V_DEPT_ID:=TRUNC(DBMS_RANDOM.VALUE(10,110),-1); --정수형 난수 발생.
            SELECT A.EMPLOYEE_ID, A.EMP_NAME, A.JOB_ID, A.SALARY
              INTO V_EMP_ID, V_ENAME, V_JOB, V_SAL
            FROM(SELECT EMPLOYEE_ID, EMP_NAME, JOB_ID, SALARY
                   FROM HR.EMP
                  WHERE DEPARTMENT_ID = V_DEPT_ID
                  ORDER BY 4 DESC) A
           WHERE ROWNUM=1;
                
                DBMS_OUTPUT.PUT_LINE('사원번호 :' ||V_EMP_ID);
                DBMS_OUTPUT.PUT_LINE('사원명 :' ||V_ENAME);
                DBMS_OUTPUT.PUT_LINE('직책 :' ||V_JOB);
                DBMS_OUTPUT.PUT_LINE('부서코드 :' ||V_DEPT_ID);
                DBMS_OUTPUT.PUT_LINE('급여 :' ||V_SAL);
          END;
          

사용예) 2005년 7월 29일 'h001' 회원이 'P202000012' 제품을 2개구입 했을 경우 이 정보를 CART 테이블에 저장하시오.
--COUNT로 구매여부 조회후 1이면 UPDATE 0이면 INSERT

 DECLARE
 V_CNT NUMBER:=0; --V_CNT는 행의 수를 체크해서 보관할 변수. 거래가 없으면 초기값인 0의값을 가짐
 V_CART_NO CART.CART_NO%TYPE;
 BEGIN --젤먼저 검색할것은 2005년7월29일에 거래가 있었나 확인
 SELECT COUNT(*) INTO V_CNT
   FROM CART
  WHERE CART_NO LIKE '20050729%';
  
  IF V_CNT=0 THEN
     V_CART_NO:='2005072900001'; -- 거래가 없다면 CART_NO에 '2005072900001' 부여
     
  ELSE -- 거래가 있다면
     SELECT CART_NO INTO V_CART_NO --V_CART_NO=1
       FROM CART
      WHERE CART_MEMBER='h001' -- 'h001'회원이 'P202000012' 상품을 구매한 이력이 있는지. 있으면 UPDATE 없으면 INSERT
        AND SUBSTR(CART_NO,1,8) = '20050729'
        AND CART_PROD='P202000012';
 END IF;
 
       IF V_CART_NO IS NULL THEN 
          INSERT INTO CART
          SELECT 'h001', MAX(CART_NO)+1, 'P202000012',2 --카트번호가 증가되어야함. 카트번호중 젤 큰번호를 찾음
            FROM CART
           WHERE CART_NO LIKE '20050729%';
        ELSIF V_CNT !=0 THEN --20050729에 판매흔적있고 P202000012를 구매하였다면 = 1번이아니면 수량만UPDATE
           UPDATE CART
              SET CART_QTY:=CART_QTY+2
            WHERE CART_MEMBER='h001'
              AND SUBSTR(CART_NO,1,8) = '20050729'
              AND CART_PROD='P202000012';
    ELSIF AND V_CNT=0 THEN --20050729에 판매흔적은 있지만 P202000012를 구매한적이 없다면 INSERT
        INSERT INTO CART
          VALUES('h001', V_CART_NO, 'P202000012', 2);
        END IF; 
      END;
      
profile
삐약..뺙뺙

0개의 댓글