C#中使用Oracle存儲過程返回結果集

2019-11-14 16:52:56

字體：大中小

來源：轉載

供稿：網友

問題：

在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);