SQLPlus를 사용하여 오라클 전체 Schema 추출

set pagesize 0
set long 90000
set termout off
set feedback off
set echo off

— schema.sql 파일로 DUMP
spool schema.sql

— 임시 테이블 (BIN$…) 과 “PACKAGE BODY”를 제외한 전체 객체 가져오기
SELECT
  dbms_metadata.GET_DDL(u.object_type,u.object_name)
FROM
  user_objects u
WHERE
      object_id IS NOT NULL
  AND object_type != ‘PACKAGE BODY’
  AND object_name NOT LIKE ‘BIN$%==$0′
ORDER BY
  object_type ASC;

— “PACKAGE BODY’ 객체 가져오기
SELECT
  dbms_metadata.GET_DDL(‘PACKAGE’,u.object_name)
FROM
  user_objects u
WHERE
      object_id IS NOT NULL
  AND object_type = ‘PACKAGE BODY’;

spool off

[SP] 기존 정보가 있으면 UPDATE, 없으면 INSERT

CREATE PROCEDURE [dbo].[sp_InfoUpdate]
(
  @FIELD_1   varchar(10),
  @FIELD_2   varchar(10),
  @FIELD_3   varchar(10)
)
AS
BEGIN
 SET NOCOUNT ON
  SET XACT_ABORT ON

  DECLARE @error int, @rowcount int

  BEGIN TRAN
    — 전달된 인자로 UPDATE 를 시도한다
    UPDATE [dbo].[tbl_TABLENAME]
       SET FIELD_2 = @FIELD_2,
           FIELD_3 = @FIELD_3
     WHERE FIELD_1 = @FIELD_1

    SELECT @error=@@ERROR, @rowcount=@@ROWCOUNT

    — 기존에 있는 정보가 UPDATE 되면, 종료
    IF @error <> 0 GOTO Done
    — UPDATE 된 내용이 없으면, 신규 등록
    IF @rowcount = 0
    BEGIN
      INSERT INTO [dbo].[tbl_TABLENAME] (FIELD_1, FIELD_2, FIELD_3)
      VALUES (@FIELD_1, @FIELD_2, @FIELD_3)

      SELECT @error = @@ERROR
      IF @error <> 0 GOTO Done
    END

    COMMIT

  Done:

    IF @error <> 0 ROLLBACK

  RETURN @error
END