[Oracle] PL/SQL - STORED PROCEDURE

PL/SQL

- Procedure Language SQL
- 표준 SQL에는 변수(상수), 분기(반복) 명령이 없음
- 표준 SQL에 절차적 언어의 특징을 추가한 SQL
- 익명블록(Anonymous Block), 저장프로시져(Stored Procedure), 함수(User Defined Function),
  트리거(Trigger), 패키지(Package) 제공
☞ 함수는 반환값이 있지만  프로시져는 반환값이 없다.
☞ 반환값이 없으면 SELECT문에 쓸 수 없다.

 

익명블록(Anonymous Block)
PL/SQL의 기본 구조 선언부(DECLARE)와 실행부(BEGIN ~ END)로 구성
저장이 안됨 ☞ 재사용불가

 

 (사용형식)
DECLARE
선언부 - 변수, 상수, 커서 선언
BEGIN
처리할 명령문을 절차적으로 기술
SQL문, 반복문,비교문, 입출력문 등
 
 
 [EXCEPTION
   예외처리문
 ]
 
 END;
 . 할당연산자 : ':='
 -- '='의 기능은 같다는 의미로만 사용됨 (할당 연산자는 ':='으로 사용) -- 파스칼 문법,,,
 . 실행영역에 사용되는 SQL문 중 SELECT문의 형식
 SELECT 컬럼list
      INTO 변수list
    FROM 테이블명
 [WHERE 조건절]

 

• ☞ 서브쿼리에 있는 SELECT절은 바뀌지 않고 메인쿼리에 있는 SELECT문만 바뀜
  - '컬럼'의 갯수, 순서, 데이터타입과 '변수'의 갯수, 순서, 데이터타입은 일치해야 함
• P_YEAR   ☞ 변수 설정,
• &P_YEAR ☞ 변수 참조
• ACCEPT ~ PROMPT ☞ 입력
• DBMS_OUTPUT.PUT_LINE ☞ print

---

변수

• 계산의 중간값을 일시적으로 저장
• 개발언어의 변수와 동일 기능 제공
• 종류 : SCLAR 변수, REFERENCE 변수, BIND 변수, COMPOSITE 변수

변수 선언 형식

• 변수(상수) [CONSTANT] 데이터타입 [:=초기값]
• 데이터타입
  . SQL에서 사용하는 모든 자료형 사용
  . BINARY_INTEGER, PLS_INTEGER, BOOLEAN 사용 가능
  . 참조형 데이터타입 사용 형식
      - 컬럼참조형 : 테이블명.컬럼명%TYPE
      - 행참조형 : 테이블명%ROWTYPE
• 숫자형 변수는 사용 전 반드시 초기화시켜야 함
  -- 초기화 하지 않으면 오류 발생
  -- 마련된 기억장소에 값을 전달시키는 과정 -> Binding 
  -- 자바에서 매개변수와 비슷
• COMPOSITE 변수 ☞ 배열형 변수
  -- 자바에서 왼쪽값엔 상수 못쓰임 (변수만 사용가능) (ex. a = 1 (a는 기억장소,변수)(상수는 literal 값이 불변))
  -- [CONSTANT]가 오면 상수가 됨 ☞ 변수 선언시 사용 x
  -- 상수는 [:=초기값] 생략불가
  -- BINARY_INTEGER, PLS_INTEGER, 4byte 빠르긴한데 값이 작아서 잘 안씀
  -- Oracle BOOLEAN ☞ true, false, null

 

---

예제

사용예) 키보드로 년도를 입력 받아 윤년과 평년을 구별하는 익명 블록을 작성하시오
  ACCEPT P_YEAR PROMPT '년도입력 : '
  DECLARE
    V_YEAR NUMBER:=0;  
  BEGIN
    V_YEAR:=TO_NUMBER('&P_YEAR');
    IF(MOD(V_YEAR,4)=0 AND MOD(V_YEAR,100)!=0) OR MOD(V_YEAR,400)=0 THEN
      DBMS_OUTPUT.PUT_LINE(V_YEAR||'년도는 윤년입니다.');
    ELSE
      DBMS_OUTPUT.PUT_LINE(V_YEAR||'년도는 평년입니다.');    
    END IF;
    
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('에러발생 : '||SQLERRM);     
  END;

 

사용예) 여성 회원 중 마일리지가 가장 많은 회원의 회원번호, 회원명, 직업, 마일리지를 조회하는
        익명 블록을 작성하시오        
  DECLARE
    V_MID MEMBER.MEM_ID%TYPE; -- 컬럼 참조형 (MEMBER테이블의 MEM_ID컬럼과 같은 컬럼으로 지정)
    V_NAME MEMBER.MEM_NAME%TYPE;
    V_JOB MEMBER.MEM_JOB%TYPE;
    V_MILE NUMBER:=0; -- 값 초기화
  BEGIN
    -- 여성 회원 중 마일리지가 가장 많은 회원
    SELECT MAX(MEM_MILEAGE) INTO V_MILE
      FROM MEMBER
     WHERE SUBSTR(MEM_REGNO2,1,1) IN('2','4');
     -- MEM_ID를 쓰면 24명 회원의 마일리지 최대값이 모두 나옴
     -- 여러개의 자료를 출력하고 싶음 -> INTO 다음으로 나오는 변수때문에 (한순간에 하나밖에 저장 못함) 커서를 이용해서 처리
           
    SELECT MEM_ID, MEM_NAME, MEM_JOB INTO V_MID, V_NAME, V_JOB
      FROM MEMBER
     WHERE MEM_MILEAGE=V_MILE
       AND SUBSTR(MEM_REGNO2,1,1) IN ('2','4')
       AND ROWNUM=1;
    -- 여러개의 자료를 출력하고 싶음 -> INTO 다음으로 나오는 변수때문에 (한순간에 하나밖에 저장 못함) 커서를 이용해서 처리
     DBMS_OUTPUT.PUT_LINE(V_MID||' '||V_NAME||'  '||V_JOB||'  '||V_MILE);     
  END;
  
사용예) 년월을 6자리 문자열로 받아 해당 기간동안 매출총액을 조회하시오
    SELECT TO_CHAR(SUM(A.CART_QTY*B.PROD_PRICE),'9,999,999,999') AS 매출액
      FROM CART A, PROD B
     WHERE SUBSTR(A.CART_NO,1,6) LIKE ('2020%')
       AND A.CART_PROD=B.PROD_ID;
-- 일반(내부) 변수는 V_, 바인딩(입력) 변수는 P_

  ACCEPT P_YEAR PROMPT '년도와 월을 6자리로 입력 : '
  DECLARE
   V_PERIOD CHAR(7):= '&P_YEAR'||'%';
   V_SUM NUMBER:=0;   
  BEGIN
        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_NO LIKE V_PERIOD;
       
    DBMS_OUTPUT.PUT_LINE('&P_YEAR'||'기간 매출액 합계 : '||V_SUM);  
  END;

 

 

---

STORED PROCEDURE

STORED PROCEDURE

• 저장 프로시져(프로시져)로 반환 값이 없는 모듈
• 미리 컴파일되어 서버에 저장되고 필요시 호출하여 실행
• 내부 네트워크의 트래픽을 감소시켜 수행 효율 증대 -- 컴퓨터 입력장치, 주기억장치 사이의 내부 네트워크 -- 함수와 다른 점 반환값이 없음 -> DML 명령, SELECT문에 사용 불가

 

 (사용형식)
CREATE [OR REPLACE] PROCEDURE 프로시져명
[(매개변수 [MODE] [데이터타입],
:   )]
IS|AS -- DECLARE와 같은 역할
선언부
BEGIN
실행부
END;
. 'MODE' : 매개변수의 종류 IN(입력전용), OUT(출력전용), INOUT(입출력용) 생략하면 IN으로 간주
-- 프로시져 안으로 입력 IN 프로시져 밖으로 내보낼때 OUT, 컴파일러에게 부담이 되는 INOUT는 되도록 사용X
 
 
 -- 반환값 : 변수에 의해서 반환되어지는 값 (변수를 통해서 받아야함 : 변수에 저장)
 -- => 변수를 사용할 수 있는 익명블록,프로시져,트리거,..(블록 구조)에서 사용
 -- 매개변수 -> 변수를 통해서 밖으로 전달 (변수 이름을 통해서 반환되어지는 경우)
 
 . '데이터타입' : 크기를 지정하지 않음
 -- 데이터 타입은 기술하되 크기는 기술하면 안된다.
 -- 매개변수가 없으면 괄호 다 생략

 

(실행)
EXECUTE|EXEC 프로시져명[(매개변수list)];
OR
프로시져명[(매개변수list)]; -- 익명블록 또는 프로시져, 함수 내부에서 실행 시 execute|exec 생략

 

사용예) 기간(시작년월과 종료년월)을 입력 받아 해당 기간에 상품별 매입집계를 출력하시오

CREATE OR REPLACE PROCEDURE PROC_BUYPROD_SUM(
P_START IN VARCHAR2,
P_END IN VARCHAR2)
IS
V_PID PROD.PROD_ID%TYPE;     -- 상품코드 => REC.BUY_PROD를 사용해서 안 씀..
V_PNAME PROD.PROD_NAME%TYPE; -- 상품명
V_QTY NUMBER:=0;             -- 수량집계
V_AMT NUMBER:=0;             -- 금액집계
V_SDATE DATE := TO_DATE(P_START||'01');
V_EDATE DATE := LAST_DAY(TO_DATE(P_END||'01'));

CURSOR CUR_BUYPROD_SUM IS
    SELECT DISTINCT BUY_PROD
      FROM BUYPROD
     WHERE BUY_DATE BETWEEN V_SDATE  AND V_EDATE;    -- 상품코드만 중복없이 나오도록 집계

BEGIN
	DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
	FOR REC IN CUR_BUYPROD_SUM LOOP
	SELECT SUM(A.BUY_QTY), SUM(A.BUY_QTY*B.PROD_COST) INTO V_QTY, V_AMT
	  FROM BUYPROD A, PROD B
	 WHERE A.BUY_PROD= B.PROD_ID
		 AND A.BUY_PROD=REC.BUY_PROD
		 AND A.BUY_DATE BETWEEN V_SDATE AND V_EDATE;
	SELECT PROD_NAME INTO V_PNAME
	  FROM PROD
	 WHERE PROD_ID=REC.BUY_PROD;
-- REC.BUY_PROD -> 커서로 된 BUY_PROD 읽어오는 방법
-- BUYPROD A, PROD B 테이블을 두 개 쓴 이유 : 실무에 나가면 예시처럼 매입테이블에 매입단가가 포함되어있는 경우가 거의 없음!
-- 보통은 상품 테이블 내에 들어있다.
   DBMS_OUTPUT.PUT_LINE(REC.BUY_PROD||'  '||RPAD(V_PNAME,20)||'  '||TO_CHAR(V_QTY,'99,999')||'  '||TO_CHAR(V_AMT,'999,999,999'));
   DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
	END LOOP;
END;

EXECUTE PROC_BUYPROD_SUM('202002','202005');