1、生成一TEST表 create table test( a number,b number); insert into test values(111,111); insert into test values(11,11); insert into test values(1,1); insert into test values(2,2); insert into test values(22,22); insert into test values(3,3); insert into test values(33,33); select * from test; 2、生成一个包 CREATE OR REPLACE PACKAGE TypeDefine AS TYPE Cursor_Test IS REF CURSOR RETURN Test%ROWTYPE; END; / 3、生成一个存储过程; CREATE OR REPLACE PROCEDURE sp_test (ia test.A%type,resultData out TypeDefine.Cursor_Test) AS BEGIN OPEN resultData FOR SELECT a,b from test; where a=ia; END; / 4、在ORACLE中执行测试: VARIABLE v REFCURSOR; EXECUTE sp_test(:v); PRINT :v;5、在DELPHI显示结果集: 需用控件StoredProc1、DataSource1、DBGrid1 StoredProc1=SP_TEST,参数RESULTDATA=ftCursor,设StoredProc1=TRUE 可从DBGrid1看到所返加的结果集
你需要写到一个包中: create or replace package pag_cs_power as type c_Type is ref cursor;
FUNCTION FUN_CS_GETDICTLIST( v_DictIndex in varchar2) return c_Type;end pag_cs_power;存储过程代码:FUNCTION FUN_CS_GETDICTLIST( v_DictIndex in varchar2) return c_Type as c_cursor c_Type; begin open c_cursor for select DICTID,DICTNAME FROM SYS_DICT WHERE DICTINDEX = v_DictIndex; return c_cursor; end FUN_CS_GETDICTLIST;
create table test( a number,b number);
insert into test values(111,111);
insert into test values(11,11);
insert into test values(1,1);
insert into test values(2,2);
insert into test values(22,22);
insert into test values(3,3);
insert into test values(33,33);
select * from test;
2、生成一个包
CREATE OR REPLACE PACKAGE TypeDefine
AS
TYPE Cursor_Test IS REF CURSOR RETURN Test%ROWTYPE;
END;
/
3、生成一个存储过程;
CREATE OR REPLACE PROCEDURE sp_test
(ia test.A%type,resultData out TypeDefine.Cursor_Test)
AS
BEGIN
OPEN resultData FOR
SELECT a,b
from test;
where a=ia;
END;
/
4、在ORACLE中执行测试:
VARIABLE v REFCURSOR;
EXECUTE sp_test(:v);
PRINT :v;5、在DELPHI显示结果集:
需用控件StoredProc1、DataSource1、DBGrid1
StoredProc1=SP_TEST,参数RESULTDATA=ftCursor,设StoredProc1=TRUE
可从DBGrid1看到所返加的结果集
create or replace package pag_cs_power as type c_Type is ref cursor;
FUNCTION FUN_CS_GETDICTLIST(
v_DictIndex in varchar2) return c_Type;end pag_cs_power;存储过程代码:FUNCTION FUN_CS_GETDICTLIST(
v_DictIndex in varchar2) return c_Type
as
c_cursor c_Type;
begin
open c_cursor for
select DICTID,DICTNAME FROM SYS_DICT WHERE DICTINDEX = v_DictIndex;
return c_cursor;
end FUN_CS_GETDICTLIST;