存储过程导出关联表的INSERTSQL

存储过程导出关联表的INSERT SQL

 

为了方便有关联的3张表的数据迁移，导出数据成INSERT SQL语句，顺便温习一下PROCEDURE
的用法

 

表结构：

Sql代码  

--PAGE  

CREATE TABLE PAGE(  

  PAGE_ID VARCHAR2(30) NOT NULL,  

  PAGE_NAME VARCHAR2(100) NOT NULL,  

  FUNCTION_ID VARCHAR2(30) NOT NULL,  

  CONSTRAINT PAGE_PK    

    PRIMARY KEY (PAGE_ID)  

);    www.2cto.com  

  

-- PAGE_LABEL  

CREATE TABLE PAGE_LABEL(  

  PAGE_LABEL_ID NUMBER(20,0) NOT NULL,    

  LABEL_ID VARCHAR2(30)  NOT NULL,  

  PAGE_ID VARCHAR2(30) NOT NULL,  

  LABEL_NAME VARCHAR2(100) NOT NULL,  

  TOOL_TIP_TEXT VARCHAR2(255),  

  CHANGE_REASON VARCHAR2(255) NOT NULL,    

  CREATED_BY_USERID VARCHAR2(9) NOT NULL,  

  UPDATED_BY_USERID VARCHAR2(9) NOT NULL,  

  CREATED_AT timestamp(6) default sysdate NOT NULL,  

  UPDATED_AT timestamp(6) default sysdate NOT NULL,  

  LAST_ACCESS_JOB_EXEC_ID NUMBER(20,0),  

    

  CONSTRAINT PAGE_LABEL_PK  

    PRIMARY KEY (PAGE_LABEL_ID),  

  CONSTRAINT PAGE_LABEL_FK  

    FOREIGN KEY (PAGE_ID)   

     REFERENCES PAGE (PAGE_ID)  

);  

  

-- LABEL_MESSAGE_TEXT  

CREATE TABLE LABEL_MESSAGE_TEXT(  

  MESSAGE_TEXT_ID NUMBER(20,0) NOT NULL,  

  PAGE_LABEL_ID NUMBER(20,0) NOT NULL,  

  ERROR_MESSAGE_ID NUMBER(20,0) NOT NULL,    

  MESSAGE_TEXT VARCHAR2(255),  

  CHANGE_REASON VARCHAR2(255) NOT NULL,    

  CREATED_BY_USERID VARCHAR2(9) NOT NULL,  

  UPDATED_BY_USERID VARCHAR2(9) NOT NULL,  

  CREATED_AT timestamp(6) default sysdate NOT NULL,  

  UPDATED_AT timestamp(6) default sysdate NOT NULL,  

  LAST_ACCESS_JOB_EXEC_ID NUMBER(20,0),  

    

  CONSTRAINT LABEL_MESSAGE_TEXT_PK  

    PRIMARY KEY (MESSAGE_TEXT_ID),  

  CONSTRAINT LABEL_MESSAGE_TEXT_FK1   

    FOREIGN KEY (PAGE_LABEL_ID)   

     REFERENCES PAGE_LABEL (PAGE_LABEL_ID)  

);  

   www.2cto.com  

使用SEQUENCE生成新的主键:

Sql代码  

CREATE SEQUENCE SEQ_YW_PAGE_LABEL INCREMENT BY 1 START WITH 1 NOMAXVALUE
NOCYCLE NOCACHE;  

CREATE SEQUENCE SEQ_YW_MESSAGE_TEXT_ID INCREMENT BY 1 START WITH 1
NOMAXVALUE NOCYCLE CACHE 10;  

 

使用显示CURSOR查询主表:

 

Sql代码  

CREATE OR REPLACE PROCEDURE PAGE_LABEL_EXPORT  

AS  

CURSOR C_P IS SELECT * FROM PAGE ORDER BY PAGE_ID;  

T_PAGE PAGE%ROWTYPE;  

BEGIN  

  OPEN C_P;  

  LOOP  

    FETCH C_P INTO T_PAGE;  

    EXIT WHEN C_P%NOTFOUND;  

    DBMS_OUTPUT.PUT_LINE(T_PAGE.PAGE_NAME);  

    -- ...  

  END LOOP;  

  CLOSE C_P;  

END PAGE_LABEL_EXPORT;  

 

主表CURSOR的LOOP中，使用隐式CURSOR查询子表:

Sql代码  

CREATE OR REPLACE PROCEDURE PAGE_LABEL_EXPORT  

AS  

C_PL SYS_REFCURSOR; T_PAGE_LABEL PAGE_LABEL%ROWTYPE;  

BEGIN  

    OPEN C_PL FOR SELECT * FROM PAGE_LABEL WHERE PAGE_ID=T_PAGE.PAGE_ID
ORDER BY PAGE_ID,LABEL_ID;  

    LOOP  

      FETCH C_PL INTO T_PAGE_LABEL;  

      EXIT WHEN C_PL%NOTFOUND;  

      DBMS_OUTPUT.PUT_LINE(&＃39;INSERT INTO PAGE_LABEL (PAGE_LABEL_ID,LABEL_ID,PAGE_ID,LABEL_NAME,TOOL_TIP_TEXT,CHANGE_REASON,
CREATED_BY_USERID,UPDATED_BY_USERID) values (SEQ_YW_PAGE_LABEL.NEXTVAL,&＃39;&＃39;&＃39;||T_PAGE_LABEL.LABEL_ID||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE_LABEL.
PAGE_ID||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE_LABEL.LABEL_NAME||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE_LABEL.TOOL_TIP_TEXT||&＃39;&＃39;&＃39;,
&＃39;&＃39;&＃39;||T_PAGE_LABEL.CHANGE_REASON||&＃39;&＃39;&＃39;,&＃39;&＃39;-&＃39;&＃39;,&＃39;&＃39;-&＃39;&＃39;);&＃39;);  

    END LOOP;  

    CLOSE C_PL;  

  

END PAGE_LABEL_EXPORT;  

   www.2cto.com  

Sql代码  

CREATE OR REPLACE PROCEDURE PAGE_LABEL_EXPORT  

AS  

CURSOR C_P IS SELECT * FROM PAGE/* WHERE PAGE_ID = &＃39;pg_create_user&＃39;*/ ORDER
BY PAGE_ID; T_PAGE PAGE%ROWTYPE;  

C_PL SYS_REFCURSOR; T_PAGE_LABEL PAGE_LABEL%ROWTYPE;  

C_LMT SYS_REFCURSOR; T_MESSAGE LABEL_MESSAGE_TEXT%ROWTYPE;  

BEGIN  

  OPEN C_P;  

  LOOP  

  FETCH C_P INTO T_PAGE;  

  EXIT WHEN C_P%NOTFOUND;  

  DBMS_OUTPUT.PUT_LINE(&＃39;/*&＃39;);  

  DBMS_OUTPUT.PUT_LINE(&＃39; * Page : &＃39; || T_PAGE.PAGE_ID);  

  DBMS_OUTPUT.PUT_LINE(&＃39; */&＃39;);  

  DBMS_OUTPUT.PUT_LINE(&＃39;INSERT INTO PAGE (PAGE_ID,PAGE_NAME,FUNCTION_ID) values (&＃39;&＃39;&＃39;||T_PAGE.PAGE_ID||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE.PAGE_NAME||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE.FUNCTION_ID||&＃39;&＃39;&＃39;);&＃39;);  

    www.2cto.com  

    --PAGE_LABEL  

    DBMS_OUTPUT.PUT_LINE(&＃39;/* PAGE_LABEL Begin*/&＃39;);  

    OPEN C_PL FOR SELECT * FROM PAGE_LABEL WHERE PAGE_ID=T_PAGE.PAGE_ID
ORDER BY PAGE_ID,LABEL_ID;  

    LOOP  

      FETCH C_PL INTO T_PAGE_LABEL;  

      EXIT WHEN C_PL%NOTFOUND;  

      DBMS_OUTPUT.PUT_LINE(&＃39;INSERT INTO PAGE_LABEL (PAGE_LABEL_ID,LABEL_ID,PAGE_ID,LABEL_NAME,TOOL_TIP_TEXT,CHANGE_REASON,
CREATED_BY_USERID,UPDATED_BY_USERID) values (SEQ_YW_PAGE_LABEL.NEXTVAL,&＃39;&＃39;&＃39;||T_PAGE_LABEL.LABEL_ID||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE_LABEL.
PAGE_ID||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE_LABEL.LABEL_NAME||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_PAGE_LABEL.TOOL_TIP_TEXT||&＃39;&＃39;&＃39;,
&＃39;&＃39;&＃39;||T_PAGE_LABEL.CHANGE_REASON||&＃39;&＃39;&＃39;,&＃39;&＃39;-&＃39;&＃39;,&＃39;&＃39;-&＃39;&＃39;);&＃39;);  

          www.2cto.com  

          --LABEL_MESSAGE_TEXT  

          DBMS_OUTPUT.PUT_LINE(&＃39;/* LABEL_MESSAGE_TEXT Begin*/&＃39;);  

          OPEN C_LMT FOR SELECT * FROM LABEL_MESSAGE_TEXT WHERE PAGE_LABEL_ID=T_PAGE_LABEL.PAGE_LABEL_ID ORDER BY PAGE_LABEL_ID,
ERROR_MESSAGE_ID;  

          LOOP  

            FETCH C_LMT INTO T_MESSAGE;  

            EXIT WHEN C_LMT%NOTFOUND;  

            DBMS_OUTPUT.PUT_LINE(&＃39;INSERT INTO LABEL_MESSAGE_TEXT (MESSAGE_TEXT_ID,PAGE_LABEL_ID,ERROR_MESSAGE_ID,MESSAGE_TEXT,
CHANGE_REASON,CREATED_BY_USERID,UPDATED_BY_USERID) values (SEQ_YW_MESSAGE_TEXT_ID.NEXTVAL,SEQ_YW_PAGE_LABEL.CURRVAL,
T_MESSAGE.ERROR_MESSAGE_ID,&＃39;&＃39;&＃39;||T_MESSAGE.MESSAGE_TEXT||&＃39;&＃39;&＃39;,&＃39;&＃39;&＃39;||T_MESSAGE.
CHANGE_REASON||&＃39;&＃39;&＃39;,&＃39;&＃39;-&＃39;&＃39;,&＃39;&＃39;-&＃39;&＃39;);&＃39;);  

          END LOOP;  

          CLOSE C_LMT;  

          DBMS_OUTPUT.PUT_LINE(&＃39;/* LABEL_MESSAGE_TEXT End*/&＃39;);  

    END LOOP;  

    CLOSE C_PL;  

    DBMS_OUTPUT.PUT_LINE(&＃39;/* PAGE_LABEL End*/&＃39;);  

  END LOOP;  

  CLOSE C_P;  

END PAGE_LABEL_EXPORT;