■예 5_2_1  ||  프로시저 생성 예제
CREATE OR REPLACE PROCEDURE salary_inc (v_sabun  IN  VARCHAR2)
IS
BEGIN
UPDATE INSA
SET SALARY=SALARY*1.03
WHERE SABUN=v_sabun;
COMMIT;
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('register is failed!');
    ROLLBACK;
END;
/

■예 5_2_2  ||  프로시저 생성 예제
CREATE OR REPLACE PROCEDURE in_sa(v_NAME  IN  VARCHAR2,  v_RGL  IN VARCHAR2)
IS
v_SABUN INSA.SABUN %TYPE;
BEGIN
INSERT INTO INSA(SABUN, NAME, JOIN_GBN_CODE, JOIN_DAY)
VALUES((____SELECT /*+ INDEX_DESC(INSA INSA_PK) */
                     DECODE(SUBSTR(SABUN,1,8)
                    ,TO_CHAR(SYSDATE,'YYYYMMDD'),SABUN+1
                    ,TO_CHAR(SYSDATE,'YYYYMMDD')||'01')
        FROM INSA
        WHERE SABUN < TO_CHAR(SYSDATE,'YYYYMMDD')||'99'
          AND ROWNUM=1), v_NAME, v_RGL, TO_CHAR(SYSDATE,'YYYYMMDD'));

COMMIT;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('FAIL!');
  ROLLBACK;
END;
/

■예 5_2_3  ||  프로시저 실행
EXEC  in_sa ('김민기', 'RGL')

SELECT  SABUN,  NAME,  JOIN_GBN_CODE,  JOIN_DAY  FROM  INSA
ORDER  BY  SABUN  DESC;

■예 5_2_4  ||  OUT 변수를 이용하는 프로시저 생성
CREATE OR REPLACE PROCEDURE workPm
(v_SABUN  IN  INSA.SABUN %TYPE,
 v_name OUT INSA.ENG_NAME%TYPE,
 v_cdname OUT CMM_CODE_DETAIL.CODE_NAME%TYPE,
 v_m OUT INSA.JOIN_DAY%TYPE)
IS
BEGIN
SELECT T1.ENG_NAME, T2.CODE_NAME,
       ROUND(MONTHS_BETWEEN(SYSDATE, TO_DATE(JOIN_DAY,'YYYYMMDD')))
AS MON_WORK
  INTO v_name, v_cdname, v_m      
  FROM INSA T1, CMM_CODE_DETAIL T2
  WHERE T1.POS_GBN_CODE = T2.CODE_NO
  AND T1.SABUN=v_SABUN;
 
DBMS_OUTPUT.PUT_LINE(v_name||'--'||v_cdname||'--'||v_m);

EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('조회하려는 사번이 존재하지 않음!');

END workPm;
/

■예 5_2_5  ||  익명 블록 PL/SQL 생성
DECLARE
v_name INSA.ENG_NAME%TYPE;
v_cdname CMM_CODE_DETAIL.CODE_NAME%TYPE;
v_m INSA.JOIN_DAY%TYPE;
BEGIN

workPm('2012010119', v_name, v_cdname, v_m);
DBMS_OUTPUT.PUT_LINE('이름 :'||v_name||' 직급 :'||v_cdname||' 근무월수 :'||v_m||'개월');

END;
/

■예 5_2_6  ||  프로시저 확인
SELECT * FROM USER_PROCEDURES;

■예 5_2_7  ||  생성된 PL/SQL 객체 내용 조회
SELECT * FROM____ USER_SOURCE;