你的位置:首页 > ASP.net教程

[ASP.net教程]C#中使用Oracle存储过程返回结果集


问题:

在MSSQLServer中定义的存储过程可以直接返回一个数据集,如:

create procedure sp_getAllEmployeesasSELECT * FROM [NORTHWND].[dbo].[Employees]

在Oracle数据库中这样定义是错误的,怎么解决?

 

办法:

Oracle中可以使用游标(Cursor)对数据集进行操作,但在存储过程输出参数中直接使用Cursor错误,此时的Cursor应该是一个定义游标的关键字并非类型,所以先要定义一个包,在包中声明全局的自定义游标类型,如:

复制代码
/**创建一个名为pkg_products的包**/create or replace package pkg_products is--定义一个公有的游标类型cursor_pdt--ref 可以在程序间传递结果集--一个程序里打开游标变量,在另外的程序里处理数据type cursor_pdt is ref cursor;--声明一个存储过程 ,游标类型参数为输出类型procedure proc_GetAllProducts(cur_set out cursor_pdt);end pkg_products;
复制代码

这个包(package)有点类似OO中的接口,C++中的文件头,实现这个包时叫包体(package body),且名称要相同,如:

复制代码
/**创建一个包体**/create or replace package body pkg_products is --实现包中没有实现的存储过程 procedure proc_GetAllProducts(cur_set out cursor_pdt) as  begin    --打开游标,由于定义游标时使用ref处理游标可以推迟到客户端   open cur_set for select id, name, producttype, price,picture, isout, mark, adddate from products;   end;end;
复制代码

定义成功,且编译通过,就可以先在Oracle中测试,如:

复制代码
/**使用过程测试定义的存储过程**/declare--定义游标类型的变量cur_set pkg_products.cursor_pdt;--定义行类型pdtrow products%rowtype;begin --执行存储过程 pkg_products.proc_GetAllProducts(cur_set); --遍历游标中的数据    LOOP     --取当前行数据存入pdtrow      FETCH cur_set INTO pdtrow;      --如果未获取数据就结束循环      EXIT WHEN cur_set%NOTFOUND;      --输出获取到的数据      DBMS_OUTPUT.PUT_LINE (pdtrow.id||','||pdtrow.name);     END LOOP;     CLOSE cur_set; end;
复制代码

如果运行上面的PL/SQL过程脚本能取到结果,说明定义的包与包体的实现就成功了,可以使用.NET,Java或其它程序访问定义好的存储过程,如使用C#访问存储过程的脚本如下:

复制代码
 1       //定义连接对象 2       OracleConnection conn = new OracleConnection("Data Source=orcl;Persist Security Info=True;User ID=t52;Unicode=True;Password=t52"); 3       //注意:包名.存储过程名的形式 4       OracleCommand cmd = new OracleCommand("pkg_products.proc_GetAllProducts", conn); 5       //设置命令类型为存储过程 6       cmd.CommandType = CommandType.StoredProcedure; 7       //定义参数,注意参数名必须与存储过程定义时一致,且类型为OracleType.Cursor 8       OracleParameter cur_set = 9         new OracleParameter("cur_set", OracleType.Cursor);10       //设置参数为输出类型11       cur_set.Direction = ParameterDirection.Output;12       //添加参数13       cmd.Parameters.Add(cur_set);14       //打开连接15       conn.Open();16       //执行并返回OracleDataReader,注意使用后释放17       return cmd.ExecuteReader(CommandBehavior.CloseConnection);
复制代码

注意项目要添加引用程序集:System.Data.OracleClient,代码中也要using;另外如果使用sql语句在C#中作为字符串出现时尽量不要换行,也不要以分号结尾。上面访问数据库的代码没有封装,返回DataSet,DataTable也可以,示例代码如下: