XML 자료를 오라클 DB에 입력하기 예제

PROCEDURE SP_DORM_ENTER_EXCEL_SAVE (
      P_XML_DATA    IN   CLOB,
      P_CAMPUS      IN   VARCHAR2,
      P_CREATE_ID   IN   VARCHAR2
   )
   IS
      V_YEAR_TERM   NUMBER;
      V_TERM        NUMBER;
      V_CNT         NUMBER;
    /******************************************************************************
      NAME:       SP_DORM_ENTER_EXCEL_SAVE
      PURPOSE:    XML 자료 일괄 저장

      REVISIONS:
      VER        DATE        AUTHOR           DESCRIPTION
      ---------  ----------  ---------------  ------------------------------------
      1.0        2010-11-13          1. CREATED THIS PROCEDURE.

      NOTES:

      AUTOMATICALLY AVAILABLE AUTO REPLACE KEYWORDS:
         OBJECT NAME:     SP_DORM_ENTER_EXCEL_SAVE
         SYSDATE:         2010-11-13
         DATE AND TIME:   2010-11-13, 오전 12:05:19, AND 2010-11-13 오전 12:05:19
         USERNAME:         (김일국)
         TABLE NAME:       (T_DORM_ENTER_SUBMIT)

   ******************************************************************************/
   BEGIN
      SELECT COUNT (*)
        INTO V_CNT
        FROM DORM.T_DORM_ENTER_SUBMIT_TEMP;

      IF V_CNT > 0
      THEN
         RAISE_APPLICATION_ERROR
                               (-20000,
                                '기존 엑셀 자료를 삭제후 입력 하셔야 합니다.'
                               );
      ELSE
         INSERT INTO DORM.T_DORM_ENTER_SUBMIT_TEMP
                     (RESISTRATION_NO, CAMPUS, STNO, PERSONAL_ID, NAMEKOR,
                      ZIPCODE, ADDRESS1, ADDRESS2, PAGER, BGTEL, REGIST_TYPE,
                      GROUP_TYPE, CREATE_DT, CREATE_ID, SEX, MEAL_OPTION,
                      BANK_CD, BANK_ACCNO, DEPOSITOR, YEARLY_TRACK)
            WITH T AS
                 (SELECT XMLTYPE.CREATEXML
                                           --(CAST (P_XML_DATA AS VARCHAR2 (4000))
                         (                  TO_CLOB (P_XML_DATA)) XML
                    FROM DUAL)
            SELECT ROWNUM AS RESISTRATION_NO, P_CAMPUS AS CAMPUS,
                   EXTRACTVALUE (VALUE (P), '/FileTable/STNO') AS STNO,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/PERSONAL_ID'
                                ) AS PERSONAL_ID,
                   EXTRACTVALUE (VALUE (P), '/FileTable/NAMEKOR') AS NAMEKOR,
                   EXTRACTVALUE (VALUE (P), '/FileTable/ZIPCODE') AS ZIPCODE,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/ADDRESS1') AS ADDRESS1,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/ADDRESS2') AS ADDRESS2,
                   EXTRACTVALUE (VALUE (P), '/FileTable/PAGER') AS PAGER,
                   EXTRACTVALUE (VALUE (P), '/FileTable/BGTEL') AS BGTEL,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/REGIST_TYPE'
                                ) AS REGIST_TYPE,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/GROUP_TYPE'
                                ) AS GROUP_TYPE,
                   SYSDATE AS CREATE_DT, P_CREATE_ID AS CREATE_ID,
                   EXTRACTVALUE (VALUE (P), '/FileTable/SEX') AS SEX,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/MEAL_OPTION'
                                ) AS MEAL_OPTION,
                   EXTRACTVALUE (VALUE (P), '/FileTable/BANK_CD') AS BANK_CD,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/BANK_ACCNO'
                                ) AS BANK_ACCNO,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/DEPOSITOR'
                                ) AS DEPOSITOR,
                   EXTRACTVALUE (VALUE (P),
                                 '/FileTable/YEARLY_TRACK'
                                ) AS YEARLY_TRACK
              FROM T,
                   TABLE (XMLSEQUENCE (EXTRACT (XML, '/NewDataSet/FileTable'))) P;

   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         ROLLBACK;
         RAISE;
   END SP_DORM_ENTER_EXCEL_SAVE;