存储过程中不能单独使用SELECT * FROM 能使用INSERT INTO TABLE SELECT * FROM TABLE_NAME 或者SELECT ID INTO V_ID FROM TABLE_NAME来赋值我记得是在做DDL语句的时候 要加上EXECUTE IMMEDIATE 'SQL';否则会报错
for v_RS in (select T.DAY_ID V_DATE from TABLE_NAME where DAY_ID>='20090501' and day_id<='20090531' GROUP BY DAY_ID order by day_id) loop INSERT INTO TABLE_TEMP SELECT V_RS.V_DATE FROM DUAL; COMMIT; end loop; --V_RS.V_DATE就是那个结果集
1。 通过into,如果是单条记录,直接select name, age, ... into 某个定义的变量1,变量2,... from a; 如果多条结构的记录集,需要用到cursor,通过对cursor的loop,来把游标into到变量里。2。 通过execute immediate 作为动态sql执行。
在pl/sql中 要不用游标显示查询结果,9i提供了一个 "管道化表函数"可以试试
如果一句SELECT能实现,就不需要用procedure。
SELECT * FROM TABLE_NAME
或者SELECT ID INTO V_ID FROM TABLE_NAME来赋值我记得是在做DDL语句的时候 要加上EXECUTE IMMEDIATE 'SQL';否则会报错
from TABLE_NAME where DAY_ID>='20090501'
and day_id<='20090531'
GROUP BY DAY_ID
order by day_id)
loop
INSERT INTO TABLE_TEMP SELECT V_RS.V_DATE FROM DUAL;
COMMIT;
end loop;
--V_RS.V_DATE就是那个结果集
create procedure p (my_cur out sys_refcursor)
as
begin
open my_cur for
select * from emp;
end;
http://www.oradev.com/ref_cursor.jsp