25.plsql프로시저

-- EMP테이블 복사
CREATE TABLE EMP_DUP
AS
    SELECT * FROM EMP;
-- EMP테이블 조회
SELECT * FROM EMP_DUP;

-- EMP_DUP테이블의 데이터를 모두 삭제 하는 프로시저
CREATE OR REPLACE PROCEDURE PROC_DEL_EMP_DUP
IS
BEGIN
    DELETE FROM EMP_DUP;
    COMMIT;
END;
/
-- 프로시져 실행
EXEC PROC_DEL_EMP_DUP;


/*
    <PROCEDURE>
        PL/SQL 문을 저장하는 객체이다.
        필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 
        실행 결과를 얻을 수 있다.
        특정 로직을 처리하기만 하고 결과값을 반환하지 않는다.
        
        [표현법]
            CREATE PROCEDURE 프로시저명
            (
                매개변수 1 [IN/OUT] 테이터타입 [:=DEFAULT 값],
                매개변수 2 [IN/OUT] 테이터타입 [:=DEFAULT 값],
                ...
            )
            IS [AS]
                선언부
            BEGIN
                실행부
            EXCEPTION
                예외처리부
            END [프로시저명];
            /
            
        [실행방법]
            EXECUTE(EXEC) 프로시저명[(매개값1, 매개값2, ...)];
*/
-- 매개변수가 있는 프로시저 
-- 저장프로시저의 경우 생성후 
-- 호출문장을 이용하여 프로시저를 실행 할수 있습니다.
-- 익명의 프로시저의 경우 저장되지 않음
CREATE OR REPLACE PROCEDURE PROC_DEL_EMP_ID
(
    P_EMP_ID    EMP.EMP_ID%TYPE
)
IS
    -- 변수선언
    -- 변수이름     변수타입
    RES     NUMBER;
BEGIN
    -- 로직작성
    DELETE 
    FROM EMP
    WHERE   EMP_ID = P_EMP_ID;
    
    RES := SQL%ROWCOUNT;
    
    INSERT INTO PROC_RES 
        VALUES ('PROC_DEL_EMP_ID'
                , RES||'건 삭제되었습니다.'
                , SYSDATE);
                
    -- 순서가 중요!!!
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(RES||'건 삭제되었습니다.');
    
END;
/

--310, 304, 302, 301
SELECT * FROM EMP ORDER BY EMP_ID DESC;

-- 프로시저 실행
EXEC PROC_DEL_EMP_ID('220');
EXEC PROC_DEL_EMP_ID('&사번');

SELECT * FROM PROC_RES;

-- 테이블 생성
CREATE TABLE PROC_RES(
    PROC_NAME   VARCHAR2(100)
    , RES       VARCHAR2(1000)
    , REG_DATE  DATE
);

-- 프로시저를 관리하는 데이터 딕셔너리
SELECT * FROM USER_SOURCE;

-- 사원정보를 받아서 사원테이블에 입력하는 프로시저를 생성!!

-- 프로시저 이름 : PROC_INS_EMP
-- 파라메터 : 사번, 이름, 주민번호, 직급코드

CREATE OR REPLACE PROCEDURE PROC_INS_EMP
(
    -- 매개변수 매개변수타입
    P_EMP_ID        EMP.EMP_ID%TYPE
    , P_EMP_NAME    EMP.EMP_NAME%TYPE
    , P_EMP_NO      EMP.EMP_NO%TYPE
    , P_JOB_CODE    EMP.JOB_CODE%TYPE
)
IS
    RES VARCHAR2(100);
BEGIN
    INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_NO, JOB_CODE)
            VALUES  (P_EMP_ID, P_EMP_NAME, P_EMP_NO, P_JOB_CODE);
    RES := SQL%ROWCOUNT || '건 삽입되었습니다.';
    INSERT INTO PROC_RES 
        VALUES('PROC_INS_EMP', RES, SYSDATE);
END;
/

EXEC PROC_INS_EMP('&사번','&이름','&주민번호','&직급코드');
EXEC PROC_INS_EMP('300','공유','111111-1111111','J1');

SELECT * FROM EMP ORDER BY 1 DESC;

/*
    2) IN/OUT 매개변수가 있는 프로시저
      IN 매개변수 : 프로시저 내부에서 사용될 변수
      OUT 매개변수 : 프로시저 호출부(외부)에서 사용될 값을 담아줄 변수
*/

-- 사번을 입력 받아서 사원명, 급여, 보너스를 OUT 매개변수에 담아 봅시다
SELECT  EMP_NAME, SALARY, BONUS
FROM    EMP
WHERE   EMP_ID=200;

CREATE OR REPLACE PROCEDURE PROC_SEL_EMP_ID
(
    P_EMP_ID        IN  EMP.EMP_ID%TYPE
    , P_EMP_NAME    IN OUT EMP.EMP_NAME%TYPE
    , P_SALARY      OUT EMP.SALARY%TYPE
    , P_BONUS       OUT EMP.BONUS%TYPE
)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('P_EMP_ID : '||P_EMP_ID);
    DBMS_OUTPUT.PUT_LINE('P_EMP_NAME : ' || P_EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('P_SALARY : ' || P_SALARY);

    SELECT  EMP_NAME, SALARY, BONUS
    INTO    P_EMP_NAME, P_SALARY, P_BONUS
    FROM    EMP
    WHERE   EMP_ID=P_EMP_ID;
END;
/
SELECT EMP_NAME FROM EMP;

SET SERVEROUTPUT ON;
SET AUTOPRINT OFF;

-- 바인드변수 선언(VARIABLE, VAR)
--VAR V_EMP_NAME  EMP.EMP_NAME%TYPE;
-- VAR  변수이름   타입;
VAR V_EMP_NAME  VARCHAR2(30);
VAR V_SALARY    NUMBER;
VAR V_BONUS     NUMBER;

-- 바인드 변수에 값을 설정
-- 값 설정
-- EXEC :변수명 := '값';
EXEC :V_EMP_NAME := '나잘난';
PRINT V_EMP_NAME;

-- 프로시저 실행
-- 바인드 변수는 :변수명 형태로 참조 가능
EXEC PROC_SEL_EMP_ID('200', :V_EMP_NAME, :V_SALARY, :V_BONUS);
-- 실행결과 바인드변수 출력

PRINT V_EMP_NAME;
--PRINT V_EMP_SALARY;
--PRINT V_BONUS

SELECT * FROM JOB;
-- 파라메터로 전달받은 직급코드의 직급명을 변경
-- IN P_JOB_CODE 
-- IN P_JOB_NAME
-- OUT VARCHAR2(100) (SQL%ROWCNT)
-- 프로시져의 이름 : PROC_UDT_TITLE
CREATE OR REPLACE PROCEDURE PROC_UDT_TITLE
(
    -- 매개변수 정의
    P_JOB_CODE      IN  JOB.JOB_CODE%TYPE
    , P_JOB_NAME    IN  JOB.JOB_NAME%TYPE
    -- OUT : 외부로 부터 값을 받아오지 않아요!
    --       프로시저 외부로 값을 저장
    , P_RES         OUT VARCHAR2
)
IS
    -- 변수선언
BEGIN
    DBMS_OUTPUT.PUT_LINE('P_JOB_CODE : ' || P_JOB_CODE);
    DBMS_OUTPUT.PUT_LINE('P_JOB_NAME : ' || P_JOB_NAME);
    DBMS_OUTPUT.PUT_LINE('P_RES : ' || P_RES);
    
    -- 실행로직
    UPDATE JOB
    SET JOB_NAME = P_JOB_NAME
    WHERE JOB_CODE = P_JOB_CODE;
    
    P_RES := SQL%ROWCOUNT || '건 처리되었습니다.';
    
    DBMS_OUTPUT.PUT_LINE('P_JOB_CODE : ' || P_JOB_CODE);
    DBMS_OUTPUT.PUT_LINE('P_JOB_NAME : ' || P_JOB_NAME);
    DBMS_OUTPUT.PUT_LINE('P_RES : ' || P_RES);
END;
/

SELECT * FROM JOB;

-- 1. 프로시저 생성
-- 2. 변수선언 (OUT 파라메터의 값을 확인하기 위해)
VAR P_RES VARCHAR2(100);
-- 3. 프로시저 실행
--    프로시저  실행할때 :변수명
EXEC PROC_UDT_TITLE('J1','대표이사',:P_RES);
-- 4. 변수값 출력 (OUT 파라메터로 부터 저장된 값을 출력)
PRINT P_RES;