create or replace procedure test
as
t_ID backup.ID%type;
t_GID backup.GID%type;
t_NUM backup.NUM%type;
BEGIN
CURSOR stored_to_b IS
SELECT ID,GID,NUM
FROM stored;
OPEN stored_to_b;
WHILE stored_to_b%FOUND LOOP
FETCH stored_to_b INTO t_ID,t_GID, t_NUM;
INSERT INTO backup (ID,G,NUM)
VALUES (t_ID,t_GID,t_NUM); END LOOP;
END ;
as
t_ID backup.ID%type;
t_GID backup.GID%type;
t_NUM backup.NUM%type;
BEGIN
CURSOR stored_to_b IS
SELECT ID,GID,NUM
FROM stored;
OPEN stored_to_b;
WHILE stored_to_b%FOUND LOOP
FETCH stored_to_b INTO t_ID,t_GID, t_NUM;
INSERT INTO backup (ID,G,NUM)
VALUES (t_ID,t_GID,t_NUM); END LOOP;
END ;
create or replace procedure test
as
t_ID backup.ID%type;
t_GID backup.GID%type;
t_NUM backup.NUM%type;
BEGIN
CURSOR stored_to_b IS
SELECT ID,GID,NUM
FROM stored;
OPEN stored_to_b;
WHILE stored_to_b%FOUND LOOP
FETCH stored_to_b INTO t_ID,t_GID, t_NUM;
INSERT INTO backup (ID,G,NUM)
VALUES (t_ID,t_GID,t_NUM); END LOOP;
close stored_to_b;
END ;
BEGIN
INSERT INTO backup
(ID, G, NUM)
SELECT a.ID, a.GID, a.NUM
FROM stored a, GOODS b
WHERE a.ID = b.ID
AND b.VALID = '有效';
END;
BEGIN
--如果需要把以前的数据清空的话:
execute immediate 'truncate table backup';
--如果不清空的话 最好加一个时间字段以便于按时间查询
INSERT INTO backup
(ID, G, NUM)
SELECT a.ID, a.GID, a.NUM
FROM stored a, GOODS b
WHERE a.ID = b.ID
AND b.VALID = '有效';
END;