引用地址：http://www.alixixi.com/program/a/2008050727634.shtml

 

本例在VS2005+Oracle 92010 + WindowsXp Sp2测试通过

1、创建一个游标变量，为返回值使用

create or replace package types as

  type cursorType is ref cursor;

end;

2、创建函数（或者存储过程）

create or replace function testpro return types.cursorType is

lc types.cursorType;

begin

  open lc for select * from test;

  return lc;

end testpro;

3、编写C#程序（注意：要先应用System.Data.OracleClient）

            OracleConnection conn = new OracleConnection("YourConnectString");

            OracleCommand cmd = new OracleCommand("testpro", conn);

            cmd.CommandType = CommandType.StoredProcedure;

 

            OracleParameter op = new OracleParameter("c", OracleType.Cursor);

            op.Direction = ParameterDirection.ReturnValue; 

            cmd.Parameters.Add(op);

 

            DataSet ds = new DataSet();

            OracleDataAdapter da = new OracleDataAdapter(cmd);

 

            da.Fill(ds,"test");

 

            this.dataGridView1.DataSource = ds.Tables["test"];

PS：使用储过程方法类似。

 

1、创建一个游标变量，为返回值使用

CREATE OR REPLACE PACKAGE types_mei1

AS

TYPE myrctype1 IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype1);

END types_mei1 ;

 

2、创建函数（或者存储过程）

create or replace function testpro_mei1(IV IN NUMBER) return types_mei1.myrctype1 is

lc types_mei1.myrctype1;

begin

open lc for select * from classes where ID=IV;

return lc;

end testpro_mei1;

 

=====================================下面是对通过单个ID查询的数据

 

create or replace package types_mei as

type cursorType is ref cursor;

end;

=============================================

CREATE OR REPLACE PACKAGE types_mei

AS

TYPE myrctype IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);

END types_mei ;

 

create or replace function testpro_mei(IV IN NUMBER) return types_mei.myrctype is

lc types_mei.myrctype;

begin

open lc for select * from test where ID=IV;

return lc;

end testpro;

=================================================

CREATE OR REPLACE PACKAGE types_mei1

AS

TYPE myrctype1 IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype1);

END types_mei1 ;

 

create or replace function testpro_mei1(IV IN NUMBER) return types_mei1.myrctype1 is

lc types_mei1.myrctype1;

begin

open lc for select * from classes where ID=IV;

return lc;

end testpro_mei1;

===================================================

 

 

===================================

存储过程：

插入数据：

CREATE OR REPLACE Procedure p_insert_t_cls --存储过程名称

(

p_stuid in CLASSES.ID%type,

p_stuname in varchar

)

as

BEGIN

insert into classes

values

(p_stuid,p_stuname);

commit;

end;

===============================================

删除 ：（带返回参数）

create or replace procedure proc_delete

(

isid in number , P_ROWS OUT NUMBER

)

is

begin

delete classes where id=isid;

If SQL%Found Then

DBMS_OUTPUT.PUT_LINE('删除成功！');

P_ROWS := 1;

Else

DBMS_OUTPUT.PUT_LINE('删除失败！');

P_ROWS := 0;

End If;

commit;

end

;

删除 ： （不带返回参数）

create or replace procedure p_delete_t_cls1(

cla_id in Number

)

is

begin

DELETE FROM classes WHERE id = cla_id;

commit;

end p_delete_t_cls1;

删除 ： （不带返回参数）指定ID删除

create or replace procedure p_delete_t_cls is

begin

DELETE FROM classes WHERE id = 7;

commit;

end p_delete_t_cls;

====================================================

修改数据：（不带返回参数）

create or replace procedure p_update_t_cls1(

p_stuid in Number,

p_stuname in Nvarchar2

)

is

begin

update classes x set x.classname = p_stuname where x.id = p_stuid;

commit;

end p_update_t_cls1;

修改数据： ：（带返回参数）

create or replace procedure proc_update(

p_stuid in Number,

p_stuname in Nvarchar2,

P_ROW out number

)

is

begin

update classes set classname = p_stuname where id = p_stuid;

If SQL%Found Then

DBMS_OUTPUT.PUT_LINE('更新成功！');

P_ROW := 1;

Else

DBMS_OUTPUT.PUT_LINE('更新失败！');

P_ROW := 0;

End If;

commit;

end proc_update;

修改数据： ： （不带返回参数）指定ID修改

create or replace procedure p_update_t_cls

is

begin

update classes x set x.classname = '44' where x.id = 3;

commit;

end p_update_t_cls;

====================================================

查询所有数据：（带返回参数 游标）

create or replace package types1 as

type cursorType1 is ref cursor;

end;

create or replace function testpro1 return types1.cursorType1 is

lc1 types1.cursorType1;

begin

open lc1 for select id,classname from classes;

return lc1;

end testpro1;

 

传递ID查询数据：（带返回参数 游标）传递ID查询数据

CREATE OR REPLACE PACKAGE pkg_test1

AS

TYPE myrctype IS REF CURSOR;

PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);

END pkg_test1 ;

create or replace function testpro(IV IN NUMBER) return types.cursorType is

lc types.cursorType;

begin

open lc for select * from classes where ID=IV;

return lc;

end testpro;

====================================================