오라클 저장프로시저 생성

/*
    파라미터 타입
        IN    : SQL로 값 전달
        OUT   : program으로 값 전달
        INOUT : SQL로 값 전달후 program으로 값 전달
*/


-- 테이블 생성
CREATE TABLE TEST_TB
(
    COLUMN_01 INTEGER primary key,
    COLUMN_02 NUMBER(5) NOT NULL,
    COLUMN_03 VARCHAR2(10),
    COLUMN_04 VARCHAR2(10),
    COLUMN_05 VARCHAR2(10)
);
-- DROP TABLE TEST_TB;

---- 시퀀스 설정 ----------------------------------------------------------------------------------
/*  
    자동증가값 설정하기 (INCREMENT BY 1  = 1씩증가, START WITH 1 = 1부터 시작을 의미)
    오라클에서는 시퀀스 설정과 테이블 매칭이 붙어있지 않고 별도로 설정되어 있음..
*/

CREATE SEQUENCE TEST_TB_SEQID INCREMENT BY 1 START WITH 1 ;
--DROP SEQUENCE TEST_TB_SEQID;

---- 커서 문법 ------------------------------------------------------------------------------------
--    DECLARE
--        변수선언  테이블명.컬럼명%TYPE;
--        변수선언  VARCHAR2(200);
--        ...
--    CURSOR 커서명 IS
--        가져올 데이터(SELECT);
--    BEGIN
--        OPEN 커서명;                          -- 커서를 오픈
--        LOOP                                  -- 루프를 돌면서 한 행씩
--            FETCH 커서명 INTO 변수명, ...;    -- 컬럼값을 변수에 담는다(순서 중요)
--            EXIT WHEN 컬럼명%NOTFOUND;        -- 해당로우를 발견할수 없다면 루프를 빠져나감
--        END LOOP
--        CLOSE 커서명;                         -- 커서를 닫음
--    END;

---- 커서 예제 ------------------------------------------------------------------------------------
-- SET serveroutput on          --출력을 위해
DECLARE 
    COLUMN_01 VARCHAR2(30);
    COLUMN_02 VARCHAR2(30);
    COLUMN_03 VARCHAR2(30);
    COLUMN_04 VARCHAR2(30);
    COLUMN_05 VARCHAR2(30);
    
CURSOR cSor IS
        SELECT * FROM TEST_TB;
BEGIN
    OPEN cSor;
    LOOP
        FETCH cSor INTO COLUMN_01, COLUMN_02, COLUMN_03, COLUMN_04, COLUMN_05;
            EXIT WHEN cSor%NOTFOUND;
            
            DBMS_OUTPUT.PUT_LINE(COLUMN_01 || chr(9) || COLUMN_02); -- 데이터를 확인하기위해 출력
    END LOOP;
    CLOSE cSor;
END;

---- 프로시저 문법 --------------------------------------------------------------------------------
--    CREATE OR REPLACE PROCEDURE 프로시저명
--    (
--        변수선언 IN VARCHAR2(200) ,
--        변수선언 OUT VARCHAR2(200) ,         -- 변수 사용시
--        ...
--    )
--    IS
--    CURSOR 커서명 IS
--        가져올 데이터(SELECT);
--        
--        변수선언                             -- 변수 사용시
--
--    BEGIN
--        OPEN 커서명;                          -- 커서를 오픈
--        LOOP                                  -- 루프를 돌면서 한 행씩
--            FETCH 커서명 INTO 변수명, ...;    -- 컬럼값을 변수에 담는다(순서 중요)
--            EXIT WHEN 컬럼명%NOTFOUND;        -- 해당로우를 발견할수 없다면 루프를 빠져나감
--        END LOOP
--        CLOSE 커서명;                         -- 커서를 닫음
--    END;

---- SELECT 프로시저 생성 -------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SELECT_PROC_01

IS 
    CURSOR cSor IS
        SELECT COLUMN_01, COLUMN_02 FROM TEST_TB;  -- 커서

AAA VARCHAR2(30);                       -- 변수사용
BBB VARCHAR2(30);                       -- 변수사용
    
BEGIN
    OPEN cSor;
    LOOP
        FETCH cSor INTO AAA, BBB;       -- 커서의 컬럼갯수와, 변수의 갯수가 일치해야 한다
            EXIT WHEN cSor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(AAA || chr(9) || BBB); 
    END LOOP;
    
    CLOSE cSor;
END
;
/

-- 프로시저 실행 (다른창에서 실행해야 함)
SET serveroutput on            --출력을 위해
 EXECUTE SELECT_PROC_01();     



---- Insert 프로시저 생성 -------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE INSERT_PROC_01
(
    Column02    IN  NUMBER,
    Column03    IN  VARCHAR2,
    Column04    IN  VARCHAR2,
    Column05    IN  VARCHAR2
)
IS
        
BEGIN

    INSERT INTO TEST_TB
    (
        COLUMN_01 ,
        COLUMN_02 ,
        COLUMN_03 ,
        COLUMN_04 ,
        COLUMN_05 
    )
    VALUES
    (
        TEST_TB_SEQID.NEXTVAL,  -- 자동증가
        Column02 ,
        Column03 ,
        Column04 ,
        Column05
    );
END;
/

-- DROP PROCEDURE INSERT_PROC_01;
-- SELECT * FROM TEST_TB;

-- 프로시저 실행 (다른창에서 실행해야 함)
SET serveroutput on                         --출력을 위해
EXECUTE INSERT_PROC_01 (12,'13','14','15');
EXECUTE INSERT_PROC_01 (22,'23','24','25');


-- Update 프로시저 생성 ---------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE UPDATE_PROC_01
(
    Column01    IN INTEGER,
    Column02    IN NUMBER,
    Column03    IN VARCHAR2,
    Column04    IN VARCHAR2,
    Column05    IN VARCHAR2
)
IS

BEGIN

    UPDATE TEST_TB
    SET COLUMN_02 = Column02,
        COLUMN_03 = Column03,
        COLUMN_04 = Column04,
        COLUMN_05 = Column05
    WHERE 
        COLUMN_01 = Column01;

END;
/

-- DROP PROCEDURE UPDATE_PROC_01;
-- SELECT * FROM TEST_TB;

-- 실행
SET serveroutput on            --출력을 위해
EXECUTE UPDATE_PROC_01 (1, 102, '103', '104', '105');

-- Delete 프로시저 생성 ---------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE DELETE_PROC_01
(
    Column01    IN INTEGER
)
IS

BEGIN

    DELETE FROM 
        TEST_TB
    WHERE 
        COLUMN_01 = Column01;

END;
/


-- DROP PROCEDURE DELETE_PROC_01;
-- SELECT * FROM TEST_TB;

-- 실행
SET serveroutput on            --출력을 위해
EXECUTE DELETE_PROC_01 (1);