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

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

 

表结构：

--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)
);

-- 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)
);

 

 

使用SEQUENCE生成新的主键:

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查询主表:

 

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查询子表:

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('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,'''||T_PAGE_LABEL.LABEL_ID||''','''||T_PAGE_LABEL.PAGE_ID||''','''||T_PAGE_LABEL.LABEL_NAME||''','''||T_PAGE_LABEL.TOOL_TIP_TEXT||''','''||T_PAGE_LABEL.CHANGE_REASON||''',''-'',''-'');');
    END LOOP;
    CLOSE C_PL;

END PAGE_LABEL_EXPORT;

 

 

CREATE OR REPLACE PROCEDURE PAGE_LABEL_EXPORT
AS
CURSOR C_P IS SELECT * FROM PAGE/* WHERE PAGE_ID = 'pg_create_user'*/ 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('/*');
  DBMS_OUTPUT.PUT_LINE(' * Page : ' || T_PAGE.PAGE_ID);
  DBMS_OUTPUT.PUT_LINE(' */');
  DBMS_OUTPUT.PUT_LINE('INSERT INTO PAGE (PAGE_ID,PAGE_NAME,FUNCTION_ID) values ('''||T_PAGE.PAGE_ID||''','''||T_PAGE.PAGE_NAME||''','''||T_PAGE.FUNCTION_ID||''');');

    --PAGE_LABEL
    DBMS_OUTPUT.PUT_LINE('/* PAGE_LABEL 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('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,'''||T_PAGE_LABEL.LABEL_ID||''','''||T_PAGE_LABEL.PAGE_ID||''','''||T_PAGE_LABEL.LABEL_NAME||''','''||T_PAGE_LABEL.TOOL_TIP_TEXT||''','''||T_PAGE_LABEL.CHANGE_REASON||''',''-'',''-'');');
      
          --LABEL_MESSAGE_TEXT
          DBMS_OUTPUT.PUT_LINE('/* LABEL_MESSAGE_TEXT Begin*/');
          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('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,'''||T_MESSAGE.MESSAGE_TEXT||''','''||T_MESSAGE.CHANGE_REASON||''',''-'',''-'');');
          END LOOP;
          CLOSE C_LMT;
          DBMS_OUTPUT.PUT_LINE('/* LABEL_MESSAGE_TEXT End*/');
    END LOOP;
    CLOSE C_PL;
    DBMS_OUTPUT.PUT_LINE('/* PAGE_LABEL End*/');
  END LOOP;
  CLOSE C_P;
END PAGE_LABEL_EXPORT;