[database 고급] 프로시저 (2023.08.09 ~ 10)

프로시저

 

SQL을 절차적으로 프로그래밍하여 사용하는 기술

SQL은 대표적인 비절차형 언어

 

SET SERVEROUTPUT ON;

SELECT * FROM TB_CUST;

CREATE OR REPLACE PROCEDURE P_SOLD_OUT_YN_DEL
AS

v_cnt NUMBER := 0;

BEGIN

    DBMS_OUTPUT.PUT_LINE(v_cnt);
    SELECT COUNT(*) INTO v_cnt FROM TB_CUST ;
    DBMS_OUTPUT.PUT_LINE(v_cnt);

    EXCEPTION
        WHEN OTHERS THEN 
            NULL ;

END ;
/

EXEC P_SOLD_OUT_YN_DEL;

 

 

변수 선언

 

 

CREATE OR REPLACE PROCEDURE P_SOLD_OUT_YN_DEL
AS
    v_cust_id TB_CUST.CUST_ID%TYPE ;
    v_cust TB_CUST%ROWTYPE;

BEGIN

    SELECT CUST_ID INTO v_cust_id FROM TB_CUST WHERE CUST_NAME = '고객001';
    DBMS_OUTPUT.PUT_LINE('해당 고객의 아이디는 ' || v_cust_id || '입니다.');
    
    SELECT * INTO v_cust FROM TB_CUST WHERE CUST_ID = 'C0001';
    DBMS_OUTPUT.PUT_LINE('해당 고객의 아이디는 ' || v_cust.CUST_ID || '입니다.');
    DBMS_OUTPUT.PUT_LINE('해당 고객의 생일은 ' || v_cust.BIRTH_DY || '입니다.');

END ;

 

 

IF문

 

CREATE OR REPLACE PROCEDURE P_SOLD_OUT_YN_DEL
AS
    v_sold_yn VARCHAR2(5);

BEGIN

    SELECT SOLD_OUT_YN INTO v_sold_yn FROM TB_PRD WHERE PRD_ID = 'P0001';
    IF v_sold_yn = 'Y'
    THEN DBMS_OUTPUT.PUT_LINE('P0001 상품은 품절입니다.');
    ELSIF v_sold_yn = 'N'
    THEN DBMS_OUTPUT.PUT_LINE('P0001 상품은 판매중입니다.');
    END IF;
    
    v_sold_yn := null;
    SELECT SOLD_OUT_YN INTO v_sold_yn FROM TB_PRD WHERE PRD_ID = 'P0015';
    IF v_sold_yn = 'Y'
    THEN DBMS_OUTPUT.PUT_LINE('P0015 상품은 품절입니다.');
    ELSE DBMS_OUTPUT.PUT_LINE('P0015 상품은 판매중입니다.');
    END IF;
    
END ;
/

 

 

반복문

 

 

CREATE OR REPLACE PROCEDURE P_SOLD_OUT_YN_DEL
AS
    v_prd_id TB_PRD.PRD_ID%TYPE;
    v_soldout_yn TB_PRD.SOLD_OUT_YN%TYPE;

CURSOR c_prd_cursor IS SELECT PRD_ID, SOLD_OUT_YN FROM TB_PRD;

BEGIN

    OPEN c_prd_cursor ;
    
    LOOP
        FETCH c_prd_cursor INTO v_prd_id, v_soldout_yn ;

    IF v_soldout_yn = 'Y' THEN
        DBMS_OUTPUT.PUT_LINE(v_prd_id || '는 품절입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_prd_id || '는 판매중입니다.');
     END IF;
    
    EXIT WHEN c_prd_cursor%NOTFOUND;
  END LOOP;
  
  CLOSE c_prd_cursor;
    
END ;
/

 

 

예외처리 

 

프로시저에서는 DDL문을 바로 쓸 수 없음 (commit의 개념이 모호해짐 )

→ execute immediate 사용 

 

 

백업 프로시저

 

 

WHERE 사용하여 조건을 지정할수도 있음