--建表
create table TEST
(
UUID VARCHAR2(36),
TEST VARCHAR2(20)
)
--创建临时表
CREATE GLOBAL TEMPORARY TABLE TESTaa
(
UUID VARCHAR2(36),
TEST VARCHAR2(20)
)
ON COMMIT DELETE ROWS;
--已有数据表
create table TABLE1
(
UUID VARCHAR2(20),
XUHAO VARCHAR2(20),
PRICE NUMBER(10,2)
)
--创建测试数
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('001', '3', 140);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('002', '3.1', 140);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('003', '3.1.1', 50);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('004', '3.1.1.1', 20);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('005', '3.1.1.2', 30);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('006', '3.1.2', 90);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('007', '3.1.2.1', 40);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('008', '3.1.2.2', 50);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('009', '4', 136);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('010', '4.1', 70);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('011', '4.2', 66);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('012', '5', 90);
COMMIT;
/* Formatted on 2011/10/12 20:15 (Formatter Plus v4.8.8) */
DECLARE
CURSOR cr_prjt_cds
IS SELECT uuid FROM ims.table1 WHERE XUHAO LIKE '3.1.%';
v_uuid ims.table1.uuid%TYPE;
clob_Sql clob;
strSql varchar2(30000);
BEGIN
OPEN cr_prjt_cds;
LOOP
FETCH cr_prjt_cds
INTO v_uuid;
EXIT WHEN cr_prjt_cds%NOTFOUND;
--clob_Sql:='insert into ims.test(uuid,test) values (sys_guid
(),'''||v_uuid||''');';
-- EXECUTE IMMEDIATE clob_Sql;
-- strSql:='insert into ims.TEST(uuid,test) values (sys_guid
(),'''||v_uuid||''')';--一般表
strSql:='insert into ims.TESTaa(uuid,test) values (sys_guid
(),'''||v_uuid||''')';--临时表
EXECUTE IMMEDIATE strSql;
END LOOP;
COMMIT;
CLOSE cr_prjt_cds;
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/775341/viewspace-733224/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/775341/viewspace-733224/