Oracle(2022.03.02)2-PL/SQL(Procedure language SQL)

PL/SQL(Procedure language SQL)
 -절차적언어의 특징을 포함하는 SQL
 -비교명령, 반복명령, 변수, 상수 등의 개념이 도입된 확장된 sql
 -블록구조(Block)로 구성
 -모듈화 캡슐화 가능
 -익명블록(Anonymous Block), 저장프로시저(Stored Procedure),
  함수(User Defined Function), 패키지(Package), 트리거(Trigger) 제공
 -서버에 실행가능한 상태로 저장되어 실행속도가 빠르고 내부 네트워크의 트래픽 감소
 -표준 문법이 없으며, DBMS에 종속적임

 

1.익명블록(Anonymous Block)
  -PL/SQL의 기본 구조
  -단순 스크립트에서 실행되는 블록(이름이 없기 때문)으로 재실행 될 수없음
  -서버에 저장되지 않음

  (사용형식)

 DECLARE
  선언부 --변수,상수,커서 선언
  BEGIN
  실행부 --비지니스 로직을 처리하기 위한 SQL문
    [EXCEPTION
       예외처리 --예외처리 수행
    ]
  END;

사용예)키보드로 수하나를 입력받아 해당되는 부서에 속한 사원들의 
       직원수,평균급여,부서장이름,부서명을 출력하는 익명블록 작성

 ACCEPT P_DID PROMPT '부서번호 입력(10-110)'  --ACCEPT 매개변수(P로시작) ,PROMPT(입력박스위에 출력하고싶은 글)
       DECLARE
         V_DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE:=TO_NUMBER('&P_DID');  --변수(V),'&P_DID'는 P_DID 입력받은 값 (문자열->숫자로바꿈),V_DID:P_DID를 받아올것이다.
         V_CNT NUMBER:=0; --변수명 타입명 일반형
         V_ASAL NUMBER:=0;
         V_ENAME HR.EMPLOYEES.EMP_NAME%TYPE;
         V_DNAME HR.DEPARTMENTS.DEPARTMENT_NAME%TYPE; --
       BEGIN
         SELECT COUNT(*), AVG(SALARY) INTO V_CNT,V_ASAL  --INTO뒤에 절에 COUNT, AVG차례로 들아가짐
           FROM HR.EMPLOYEES
          WHERE DEPARTMENT_ID =V_DID;
         SELECT A.DEPARTMENT_NAME, B.EMP_NAME INTO V_DNAME,V_ENAME
           FROM HR.DEPARTMENTS A, HR.EMPLOYEES B  --
         WHERE A.DEPARTMENT_ID=V_DID 
           AND A.DEPARTMENT_ID=B.DEPARTMENT_ID --사원테이블도 같아야함
           AND A.MANAGER_ID=B.EMPLOYEE_ID; 
           
        DBMS_OUTPUT.PUT_LINE('부서명 : '||V_DNAME);
        DBMS_OUTPUT.PUT_LINE('부서장이름 : '||V_ENAME);
        DBMS_OUTPUT.PUT_LINE('직원수: '||V_CNT);
        DBMS_OUTPUT.PUT_LINE('급여평균 : '||ROUND(V_ASAL));
        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
       END;

보기탭에서 출력창을 띄울 수 있음

DBMS출력은 지우개로 지워야한다. 안그러면 데이터(결과)는 그위에 계속 축적된다.

---

1)변수와 상수
 .(1)변수
   .프로그램언어의 변수와 같은 기능
   .SCLAR 변수(스칼라): 하나의 데이터만 저장하는 일반적인 변수 **
   .참조변수: 임의 테이블의 행이나 컬럼을 참조하는 타입 **
   .COMPOSITE 변수: PL/SQL의 배열변수로 RECORD TYPE, TABLE TYPE
   .BIND변수: 파라메타로 넘겨지는 값을 전달하기위한 변수로
             IN(밖->모듈안),OUT(모듈안->밖),INOUT(둘다가능) 등의 역할 수행

    (선언형식)

  변수명 데이터타입[(크기)]|참조타입[:=초기값]; -- :=(할당연산자),NUMBER타입은 반드시 초기화해줘야한다.
    -데이터 타입:오라클SQL에서 제공하는 모든 데이터타입
                BINARY INTEGER, PLS_INTEGER : 4BYTE 정수
                BOOLEAN : 논리값(true,false,null)
    -참조타입
     테이블명.컬럼명%TYPE
     테이블명.%ROWTYPE

.(2)상수
   .프로그램이 진행되는 동안 변하지 않는 값을 가진 LITERAL 
   .수식의 Left Value가 될 수 없다.

(선언형식)

  변수명  CONSTANT 데이터타입[(크기)]:=초기값; 
     .상수 선언은 'CONSTANT'를 사용하고 초기값을 반드시 지정
     .상수로 선언된 식별자는 수식의 :=연산자 왼쪽에 사용할 수 없다.

사용예)입력된 반지름을 이용하여 원둘레, 원의 너비를 구하시오.

 DECLARE
    V_RADIOUS NUMBER:=124.5;
    V_PI CONSTANT NUMBER:=3.1415926;
    V_LENGTH NUMBER:=0;
    V_AREA NUMBER:=0;
   BEGIN
      DBMS_OUTPUT.PUT_LINE('반지름: '||V_RADIOUS);
      DBMS_OUTPUT.PUT_LINE('원둘레: '||V_RADIOUS*2*V_PI);
      DBMS_OUTPUT.PUT_LINE('원의너비: '||V_RADIOUS*V_RADIOUS*V_PI);
   END;

(3)커서
  .커서는 SQL문으로 영향 받은 행들의 집합
  .대부분 SELECT문에 의한 결과 집합을 의미함(VIEW와 같은 결과)
  (사용형식)
   CURSOR 커서명[(매개변수list)] IS
    SELECT 문;
   .커서는 선언문->OPEN문->FETCH문->CLOSE 문으로 선언되고 사용됨
   
사용예)마일리지가 많은 5명의 회원이 2005년 4-6월 사이에 회원별 구매집계를 조회하시오.
       Alias는 회원번호, 회원명, 구매금액합계

(마일리지가 많은 5명의 회원을 뽑아내는 커서 )

 DECLARE
      V_MID MEMBER.MEM_ID%TYPE; --멤버테이블의 멤버아이디와 같은 타입이다라고 선언
      V_NAME MEMBER.MEM_NAME%TYPE;
      V_SUM NUMBER:=0;   --출력할 내용, 3가지의 변수 선언)
      CURSOR CUR_MILEAGE IS
        SELECT A.MEM_ID, 
               A.MEM_NAME 
          FROM (SELECT MEM_ID, MEM_NAME, MEM_MILEAGE --마일리지 순으로 정렬된 회원테이블에서 
               FROM MEMBER                           --회원번호, 회원명 조회
               ORDER BY 3 DESC) A                    --ORDER BY 보다 WHERE절이 먼저 실행되므로 서브쿼리로 작성
         WHERE ROWNUM <= 5;
    BEGIN
      OPEN CUR_MILEAGE;
      LOOP
        FETCH CUR_MILEAGE INTO V_MID,V_NAME;
        EXIT WHEN CUR_MILEAGE%NOTFOUND; --더이상 자료가 없으면 참 => EXIT 실행 => LOOP문 벗어남
        SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO V_SUM
          FROM CART A, PROD B
         WHERE A.CART_PROD = B.PROD_ID
           AND A.CART_MEMBER = V_MID
           AND SUBSTR(A.CART_NO,1,6) BETWEEN '200504' AND '200506'; --날짜가 아니라 TO_DATE 쓰면 안됨
          
       DBMS_OUTPUT.PUT_LINE('회원번호 : '||V_MID);
       DBMS_OUTPUT.PUT_LINE('회원명 : '||V_NAME);
       DBMS_OUTPUT.PUT_LINE('구매금액합계 : '||V_SUM);
       DBMS_OUTPUT.PUT_LINE('---------------------------');
       
     END LOOP;
     CLOSE CUR_MILEAGE;
    END;

(커서를 사용하지 않은 경우)  

 SELECT TBLA.CMID AS 회원번호,
         TBLB.ANAME AS 회원명,
         TBLA.V_SUM AS 구매금액합계
    FROM (SELECT A.CART_MEMBER AS CMID,
                 SUM(A.CART_QTY*B.PROD_PRICE) AS V_SUM
            FROM CART A, PROD B
           WHERE A.CART_PROD=B.PROD_ID
             AND SUBSTR(A.CART_NO,1,6) BETWEEN '200504' AND '200506'
           GROUP BY A.CART_MEMBER)TBLA,
          (SELECT A.MEM_ID AS AMID,
                  A.MEM_NAME AS ANAME
             FROM (SELECT MEM_ID,MEM_NAME,MEM_MILEAGE 
                     FROM MEMBER 
                    ORDER BY 3 DESC)A        
            WHERE ROWNUM<=5)TBLB
   WHERE TBLA.CMID=TBLB.AMID;