先建立了一张临时表,然后想建个游标循环查询这张临时表,该怎么做?CREATE GLOBAL TEMPORARY TABLE t (
ID NUMBER NOT NULL,
RP_ID number,
RP_CODE varchar2(50),
CUS_ID number,
CP_ID number,
PROD_ID number
)
ON COMMIT delete ROWS ;
DECLARE
CURSOR lv_Cursor IS SELECT RP_ID,RP_CODE,CUS_ID,CP_ID,PROD_ID From t Order by RP_ID 现在的问题是:提示找不到t
ID NUMBER NOT NULL,
RP_ID number,
RP_CODE varchar2(50),
CUS_ID number,
CP_ID number,
PROD_ID number
)
ON COMMIT delete ROWS ;
DECLARE
CURSOR lv_Cursor IS SELECT RP_ID,RP_CODE,CUS_ID,CP_ID,PROD_ID From t Order by RP_ID 现在的问题是:提示找不到t
cursor mycur(var1 number, var2 varchar2........) is select *from linshitable;open mycur;fetch mycur into mvar1, mvar2.........;insert into targettable(var.......) values(mvar1, mvar2,......);close mycur;这样行吗?
for rs in (select * from t)
loop
rs.col ....
end loop就可以了, 这就是游标了.你要的动态游标是什么?
strsql := 'DECLARE
CURSOR lv_Cursor IS SELECT RP_ID,RP_CODE,CUS_ID,CP_ID,PROD_ID From t Order by RP_ID ',但这样好像不行。
在包中声明游标
CREATE OR REPLACE PACKAGE pck_query
AS
TYPE type_cur IS REF CURSOR;
Procedure test
;
END pck_query;
/
CREATE OR REPLACE PACKAGE BODY pck_query
AS
Procedure test
as
v_cur type_cur;
Begin
---建立临时表
commit;Open v_cur For
......
Close v_cur;
end;end ;
IF 条件判断 THEN
vSQl:=SQL;
EXECUTE IMMEDIATE vSQl;
END IF ;
END LOOP;