存储过程导出关联表的INSERT SQL
为了方便有关联的3张表的数据迁移，导出数据成INSERT SQL语句，顺便温习一下PROCEDURE的用法表结构：
--PAGECREATE 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_LABELCREATE 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_TEXTCREATE 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_EXPORTASCURSOR C_P IS SELECT * FROM PAGE ORDER BY PAGE_ID;T_PAGE PAGE%ROWTYPE;BEGINOPEN 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_EXPORTASC_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_EXPORTASCURSOR 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;BEGINOPEN C_P;LOOPFETCH 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;
页:
[1]