存储过程导出关联表的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;
NOCYCLE NOCACHE;
CREATE SEQUENCE SEQ_YW_MESSAGE_TEXT_ID INCREMENT BY 1 START WITH 1
NOMAXVALUE NOCYCLE CACHE 10;
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('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||''',''-'',''-'');');
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代码
CREATE OR REPLACE PROCEDURE PAGE_LABEL_EXPO