关于C#中使用Oracle存储过程返回结果集的问题_C#

关于C#中使用Oracle存储过程返回结果集的问题

2022-12-07 11:33笔记 C#

Oracle中可以使用游标（Cursor）对数据集进行操作，但在存储过程输出参数中直接使用Cursor错误，下面小编给大家带来了C#中使用Oracle存储过程返回结果集的问题，感兴趣的朋友一起看看吧

问题：

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

				?

									create procedure sp_getAllEmployees

									as

									SELECT * 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#访问存储过程的脚本如下：

				?

									//定义连接对象

									            OracleConnection conn = new OracleConnection("Data Source=orcl;Persist Security Info=True;User ID=t52;Unicode=True;Password=t52");

									            //注意：包名.存储过程名的形式

									            OracleCommand cmd = new OracleCommand("pkg_products.proc_GetAllProducts", conn);

									            //设置命令类型为存储过程

									            cmd.CommandType = CommandType.StoredProcedure;

									            //定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor

									            OracleParameter cur_set =

									                new OracleParameter("cur_set", OracleType.Cursor);

									            //设置参数为输出类型

									            cur_set.Direction = ParameterDirection.Output;

									            //添加参数

									            cmd.Parameters.Add(cur_set);

									            //打开连接

									            conn.Open();

									            //执行并返回OracleDataReader，注意使用后释放

									            return cmd.ExecuteReader(CommandBehavior.CloseConnection);