오라클에서 기본적인 프로시저 사용 예시 - insert, delete, update

--TABLE 만들기
--시퀀스 생성
CREATE SEQUENCE tt1_seq start with 1
/
--테이블 생성
CREATE TABLE tt1
(
 temp_id   NUMBER not null primary key,
 data1   VARCHAR2(50),
 data2   VARCHAR2(50),
 create_time TIMESTAMP
)
/
--트리거 생성
CREATE OR REPLACE TRIGGER tt1_ti
   before insert on tt1
   for each row
   declare
    l_temp_id number;
   begin
    select tt1_seq.nextval
      into l_temp_id
      from dual;
   :new.temp_id := l_temp_id;
   end;
/


-- 프로시저 패키지 생성
-- 프로시저 단독으로 사용가능 하지만 원활한 관리를 위해 패키지구성을 통해 활용한다.
CREATE OR REPLACE PACKAGE DATA_PROCESSES IS
 PROCEDURE insert_tt1(l_vdata1 IN VARCHAR2,
    l_vdata2  IN VARCHAR2);
 PROCEDURE delete_tt1(l_nid  IN NUMBER);
 PROCEDURE update_tt1(l_nid  IN NUMBER, l_vdata1 IN VARCHAR2,  l_vdata2  IN VARCHAR2);
END DATA_PROCESSES;
/
-- 프로시저 바디 생성
CREATE OR REPLACE PACKAGE BODY DATA_PROCESSES AS
    -- 삽입 프로시저 생성
     PROCEDURE insert_tt1(l_vdata1 IN VARCHAR2, l_vdata2  IN VARCHAR2)    
     IS  l_stmt_str VARCHAR2(200);    
     BEGIN
        SAVEPOINT start_transaction;  ---- 세이브 포인트 지정
        l_stmt_str := 'INSERT INTO tt1 (data1,  data2, create_time) ' || 'VALUES(:l_vdata1, :l_vdata2, SYSDATE)';
        EXECUTE IMMEDIATE l_stmt_str   USING l_vdata1, l_vdata2;
    
     EXCEPTION
        WHEN OTHERS THEN
        ROLLBACK TO start_transaction; ---- 애러 발생시 애러구문 출력이후 롤백 시켜준다.
        dbms_output.put_line('An error occurred while creating query:' ||    SQLERRM ||'.  Please try again later.');
     END;
    
    -- 삭제 프로시저 생성
     PROCEDURE delete_tt1(l_nid  IN NUMBER)
     IS  l_stmt_str VARCHAR2(200);
     BEGIN
        SAVEPOINT start_transaction; ---- 세이브 포인트 지정
        l_stmt_str := 'DELETE FROM tt1 WHERE temp_id = :1';
        EXECUTE IMMEDIATE l_stmt_str USING l_nid;
     EXCEPTION
        WHEN OTHERS THEN
        ROLLBACK TO start_transaction; ---- 애러 발생시 애러구문 출력이후 롤백 시켜준다.
        dbms_output.put_line('An error occurred while creating query: '|| SQLERRM ||'.  Please try again later.');
     END;
    
    -- 업데이트 프로시저 생성
    /*
        변수 입력받는것과 USING 구문만 효력을 발생한다. sql문에서는 using문에 입력된 순서대로 변수값을 매칭 시킨다.
        l_stmt_str :=에서 :1 :2 :3 이나 :3 :1 :2나 같은 결과를 초래한다.
    */
    PROCEDURE update_tt1(l_nid  IN NUMBER, l_vdata1 IN VARCHAR2, l_vdata2  IN VARCHAR2)
    IS    l_stmt_str VARCHAR2(200);
    BEGIN
        SAVEPOINT start_transaction;  ---- 세이브 포인트 지정
        l_stmt_str := 'UPDATE tt1 SET data1=:3, data2=:1 WHERE temp_id = :2';
    EXECUTE IMMEDIATE l_stmt_str USING  l_vdata1, l_vdata2, l_nid ;
    
    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK TO start_transaction; ---- 애러 발생시 애러구문 출력이후 롤백 시켜준다.
        dbms_output.put_line('An error occurred while creating query: '|| SQLERRM || '.  Please try again later.');
    END;
END DATA_PROCESSES;


[TEST]
exec DATA_PROCESSES.insert_tt1('data_clm1','data_clm2');
exec DATA_PROCESSES.insert_tt1('data_clm3','data_clm4');
select * from tt1;
exec DATA_PROCESSES.delete_tt1(5);
select * from tt1;
exec DATA_PROCESSES.update_tt1(4,'data1111','data2222');
select * from tt1;

[추천유틸]
원할한 테스트를 위해서 DB관련 유틸을 사용해야 하는데... 개인적으로는 간단편하게 사용할 수 있는
Benthic Software에서 만든 Golden(디비 컨트롤유틸)과 PLEdit와 (프로시져 생성 전용) 추천한다.
음.. 쉐어웨어 인데.. google에서 검색하면 암흑버전 뭐 손쉽게 얻을 수 있고 -_-;;
무엇보다 토드, 오렌지 ... 과 비교하여 볼때 경량형 프로램인지라 좋은거 같음.