오라클 프로시저 예제 (Oracle Procedure, 프로시져)

오라클 프로시져 예제 입니다.

원본 테이블을 읽어와서 커서를 생성하고 LOOP를 돌아 백업 테이블에 입력하는 기본 프로시져입니다.

커서관련 정보는 여기서 확인하세요. http://annehouse.tistory.com/102

프로시져 삭제는 DROP PROCEDURE 프로시져명; 입니다.

SET SERVEROUTPUT ON;  (DBMS_OUTPUT.put_line(); 으로 로그 출력시 프로시저 실행전 앞에 명령어를 실행해야 출력 된다.)

---

[프로시저를 실행하기 위해 기초데이터 생성]

--원본 테이블
CREATE TABLE BOOKS (
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR(20) NOT NULL,
  WRITER VARCHAR(20) NOT NULL,
  PRICE NUMBER(10,2) DEFAULT 0,
  GENRE VARCHAR(20) NOT NULL,
  PUBLISHER VARCHAR(20) NOT NULL,
  CNT NUMBER(10,0) DEFAULT 0
);

--백업 테이블
CREATE TABLE BOOKS_BAK (
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR(20) NOT NULL,
  WRITER VARCHAR(20) NOT NULL,
  PRICE NUMBER(10,2) DEFAULT 0,
  GENRE VARCHAR(20) NOT NULL,
  PUBLISHER VARCHAR(20) NOT NULL,
  CNT NUMBER(10,0) DEFAULT 0
);

--시퀀스 생성
CREATE SEQUENCE BOOKS_SEQID INCREMENT BY 1 START WITH 1;

--데이터 입력
INSERT INTO BOOKS(
  ID,
  NAME,
  WRITER,
  PRICE,
  GENRE,
  PUBLISHER
) VALUES (
  BOOKS_SEQID.NEXTVAL,
  '홍길동전',
  '허균',
  300,
  '소설',
  '허균 출판사'
);
 INSERT INTO BOOKS(
  ID,
  NAME,
  WRITER,
  PRICE,
  GENRE,
  PUBLISHER
) VALUES (
  BOOKS_SEQID.NEXTVAL,
  '레미제라블',
  '빅토르 위고',
  900,
  '소설',
  '빅토르 출판사'
);
COMMIT;
 

---

[프로시저 생성]

--프로시져 생성 시작
CREATE OR REPLACE PROCEDURE PRO_BACKUP_BOOK(
  BOOK_ID IN BOOKS.ID%TYPE --BOOKS 테이블의 ID와 같은 컬럼 타입 (외부에서 받는 값)
)
IS
  --커서 선언
  CURSOR CSOR IS SELECT ID, NAME, WRITER, PRICE, GENRE, PUBLISHER, CNT FROM BOOKS WHERE ID = BOOK_ID;
  --사용할 변수 선언
  B_ID BOOKS.ID%TYPE; --테이블.컬럼%TYPE (해당 테이블의 컬럼 타입과 동일하게 선언하겠다는 표현)
  B_NAME BOOKS.NAME%TYPE;
  B_WRITER BOOKS.WRITER%TYPE;
  B_PRICE BOOKS.PRICE%TYPE;
  B_GENRE BOOKS.GENRE%TYPE;
  B_PUBLISHER BOOKS.PUBLISHER%TYPE;
  B_CNT BOOKS.CNT%TYPE;  
 
BEGIN
  
  OPEN CSOR; --커서 열기
    LOOP --루프 시작
      FETCH CSOR INTO B_ID, B_NAME, B_WRITER, B_PRICE, B_GENRE, B_PUBLISHER, B_CNT; --커서에서 가져와 위에 선언한 변수에 삽입 (갯수 일치)
      EXIT WHEN CSOR%NOTFOUND; --LOOP를 빠져나오는 조건
        INSERT INTO BOOKS_BAK( --백업 테이블에 입력
          ID,
          NAME,
          WRITER,
          PRICE,
          GENRE,
          PUBLISHER,
          CNT
        ) VALUES (
          B_ID,
          B_NAME,
          B_WRITER,
          B_PRICE,
          B_GENRE,
          B_PUBLISHER,
          B_CNT
        );
      COMMIT; --커밋
    END LOOP; --루프 종료
  CLOSE CSOR; --커서 닫기
 
END;
/
--프로시져 생성 종료

---

[프로시져 실행]

--프로시져 실행
EXECUTE PRO_BACKUP_BOOK(1);