可以在存储过程中生成视图,然后查询视图获得结果 或者用游标传出结果集 例如 create or replace procedure proc(cur out sys_refcursor) as begin open cur for select * from a; end;
create function(p_id varchar2) return sys_refcursor as cursor cur is select * from a where a.id=p_id begin open cur; return cur; end
open cur for select * from a; 游标不用关闭啊,不关是不是浪费资源。
cursor cur is select * from a where a.id=p_id begin open cur; return cur; end 游标是不是应该关了呢
楼上代码有问题,应该改成:create or replace function F_Get_Result(p_id varchar2) return sys_refcursor as type cc is ref cursor; cur cc; begin open cur for select * from a where a.id=p_id; return cur; end;
http://xiaohewoai.javaeye.com/ 访问这个
CREATE OR REPLACE PACKAGE MyTypes AS TYPE ref_cursor IS REF CURSOR; END;CREATE TABLE STOCK_PRICES( RIC VARCHAR(6) PRIMARY KEY, PRICE NUMBER(7,2), UPDATED DATE );CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER) RETURN MYTYPES.ref_cursor AS stock_cursor MYTYPES.ref_cursor; BEGIN OPEN stock_cursor FOR SELECT ric,price,updated FROM stock_prices WHERE price < v_price; RETURN stock_cursor; END;在plsql下运行有错误 请高手帮忙 在线等
declare stock_cursor MYTYPES.ref_cursor; begin exec :stock_cursor := sp_get_stocks(20.0) for myrecord in stock_cursor loop dbms_output.put_line(myrecord.ric); end loop; end; 在plsql下运行有错误 请高手帮忙 在线等
或者用游标传出结果集
例如
create or replace procedure proc(cur out sys_refcursor)
as
begin
open cur for select * from a;
end;
return sys_refcursor
as
cursor cur is select * from a where a.id=p_id
begin
open cur;
return cur;
end
begin
open cur;
return cur;
end
游标是不是应该关了呢
楼上代码有问题,应该改成:create or replace function F_Get_Result(p_id varchar2)
return sys_refcursor
as
type cc is ref cursor;
cur cc;
begin
open cur for select * from a where a.id=p_id;
return cur;
end;
CREATE OR REPLACE PACKAGE MyTypes
AS
TYPE ref_cursor IS REF CURSOR;
END;CREATE TABLE STOCK_PRICES(
RIC VARCHAR(6) PRIMARY KEY,
PRICE NUMBER(7,2),
UPDATED DATE );CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
RETURN MYTYPES.ref_cursor
AS
stock_cursor MYTYPES.ref_cursor;
BEGIN
OPEN stock_cursor FOR
SELECT ric,price,updated FROM stock_prices WHERE price < v_price;
RETURN stock_cursor;
END;在plsql下运行有错误 请高手帮忙 在线等
declare
stock_cursor MYTYPES.ref_cursor;
begin
exec :stock_cursor := sp_get_stocks(20.0)
for myrecord in stock_cursor loop
dbms_output.put_line(myrecord.ric);
end loop;
end;
在plsql下运行有错误 请高手帮忙 在线等