Insert SQL statement used to export the joined table in the Stored Procedure

The insert SQL statement used to export the joined table in the stored PROCEDURE. To facilitate the data migration of the three associated tables, export the data into an insert SQL statement. By the way, let's take a look at PROCEDURE.
Usage TABLE Structure: SQL code -- 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 partition (MESSAGE_TEXT_ID NUMBER (20, 0) not null, PAGE_LABEL_ID NUMBER (20, 0) not null, invalid NUMBER (255) not null, MESSAGE_TEXT VARCHAR2 (255), CHANGE_REASON VARCHAR2 () 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 primary key (MESSAGE_TEXT_ID ), CONSTRAINT defaults foreign key (PAGE_LABEL_ID) REFERENCES PAGE_LABEL (PAGE_LABEL_ID); www.2cto.com use SEQUENCE to generate a new primary KEY: SQL code CREATE SEQUENCE SEQ_YW_PAGE_LABEL INCREMENT BY 1 START WITH 1
Nocycle nocache; create sequence SEQ_YW_MESSAGE_TEXT_ID increment by 1 start with 1
Nomaxvalue nocycle cache 10; Use the show CURSOR to query the master table: SQL code 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; in the LOOP of the master table CURSOR, use the implicit CURSOR to query the subtable: SQL code CREATE OR REPLACE PROCEDURE PAGE_LABEL_EXPORT AS C_PL SYS_REFCURSOR; T_PAGE_LABEL 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; values ('insert INTO PAGE_LABEL (values, 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; www.2cto.com SQL code 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 success; T_MESSAGE success % 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); values ('*/'); values ('insert into page (PAGE_ID, PAGE_NAME, FUNCTION_ID) values (''' | T_PAGE.PAGE_ID | ''', ''' | T_PAGE.PAGE_NAME | ''', ''' | T_PAGE.FUNCTION_ID | '''); '); www.2cto.com -- 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; values ('insert INTO PAGE_LABEL (values, 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 | ''',''-'',''-'');'); www.2cto.com -- LABEL_MESSAGE_TEXT values ('/* LABEL_MESSAGE_TEXT Begin */'); OPEN C_LMT for select * FROM LABEL_MESSAGE_TEXT WHERE PAGE_LABEL_ID = Limit order by PAGE_LABEL_ID,
ERROR_MESSAGE_ID; loop fetch C_LMT INTO T_MESSAGE; exit when C_LMT % NOTFOUND; values ('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; values ('/* PAGE_LABEL End */'); end loop; CLOSE C_P; END PAGE_LABEL_EXPORT;