[Oracle] Procedure 예제

CREATE OR REPLACE PROCEDURE USER_PROC (
    V_VALUE_1 IN VARCHAR2,
    V_VALUE_2 IN VARCHAR2,
    V_DEPTNO IN EMP.DEPTNO%TYPE ,
    O_ERRORCODE OUT VARCHAR2,
    O_ERRORMESG OUT VARCHAR2
) 
IS
/*****************************************************************
 Program Name   : 프로시져명
 Description    : 설명
 Author         : 성명
 History        : 날짜
*****************************************************************/
V_ENAME    VARCHAR2(8) := '';

V_USER_NAME VARCHAR2(20) := '';
V_USER_ID VARCHAR2(20) := '';

-- 커서선언
CURSOR CUR_EMP_LIST(P_USER_ID EMP.USER_ID%TYPE)
IS
SELECT *
  FROM EMP
 WHERE USER_ID = P_USER_ID
;

-- 커서 변수선언
V_EMP_LIST EMP%ROWTYPE;

BEGIN
    DBMS_OUTPUT.ENABLE;

    -- 오류코드: -20000 ~ -20999
    IF P_USER_ID IS NULL THEN
        RAISE_APPLICATION_ERROR(-20000, 'P_USER_ID 는 필수입력 항목입니다.');
    END IF;

    SELECT ENAME
      INTO V_ENAME
      FROM EMP
     WHERE ENAME = '1234' 
    ;
    
    DBMS_OUTPUT.PUT_LINE ('사원명은'||V_ENAME||' 입니다') ;

    FOR EMP_LIST IN  (
         SELECT *
          FROM EMP   -- 테이블
         WHERE JOIN_DATE LIKE '202301%'
    ) LOOP

        V_USER_NAME := EMP_LIST.USER_NAME;
        V_USER_ID := EMP_LIST.USER_ID;

    END LOOP;

    -- 커서오픈
	OPEN CUR_EMP_LIST('1234');
    -- LOOP
    LOOP 
    	FETCH CUR_EMP_LIST INTO V_EMP_LIST;
    	EXIT WHEN CUR_EMP_LIST%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE ('사원명은'||V_EMP_LIST.USER_NAME || ' 입니다') ;
        
    END LOOP;
    
    -- 커서닫기
    CLOSE CUR_EMP_LIST;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('해당 사원이 없습니다') ;
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('사원이 두 명 이상입니다') ;
  WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('0 으로 나눌 수 없습니다.') ;
  WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('동일한 데이터가 존재합니다.') ;
  WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('기타오류 SQLCODE :: ' || SQLCODE) ;
      DBMS_OUTPUT.PUT_LINE('기타오류 SQLERRM :: ' || SQLERRM) ;
END;

---

https://hermeslog.tistory.com/287

[Oracle] Function 예제

https://hermeslog.tistory.com/293

[Oracle] PL/SQL 예제

https://hermeslog.tistory.com/291

[Oracle] PL/SQL PREDEFINED EXCEPTION - 미리정의된 예약