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

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

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;

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

www.2cto.com

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