Oracle函数将行返回到C# [英] Oracle function return rows into c#
问题描述
我想在oracle中创建一个过程或函数,该函数将多行不同类型的返回给我的应用程序.我在互联网上找到了一些示例,但我无法使它正常工作...
I want to create a procedure or function in oracle that returns more than one row of different types to my application. I found some examples on the internet but i can't get it to work...
示例: 我在oracle中创建了一个自定义类型:
Example: I created a custom type in oracle:
create type proc_selectall is object
(Mjera varchar2(50),
Status number(10,0),
Naziv varchar2(50),
Stat number(10,0));
然后:
create type ret_selectall is table of proc_selectall;
然后创建函数:
create or replace function fsp_SelectAll return ret_selectall
is
l_ret_selectall ret_selectall := ret_selectall();
n integer := 0;
BEGIN
for r in(SELECT JEDINICAMJERE.Mjera,
JEDINICAMJERE.Status,
KATEGORIJE.Naziv,
KATEGORIJE.Status as Stat
FROM JEDINICAMJERE, KATEGORIJE)
loop
l_ret_selectall.extend;
n := n + 1;
l_ret_selectall(n) := proc_selectall(r.Mjera, r.Status, r.Naziv, r.Stat);
end loop;
return l_ret_selectall;
END;
当我输入以下行时:
select * from table (fsp_SelectAll)
我得到了不错的预期输出:
I get a nice and expected output:
MJERA | STATUS | NAZIV | STAT
tr 0 name1 1
fd 1 name2 1
ds 1 name3 0
.....
稍后,我尝试在我的应用中获取这些值. 我尝试过这样的事情:
Later i tried to get those values in my app. I tried something like this:
//cmd.Parameters.Add("proc_selectall ", OracleDbType.Object);
//cmd.Parameters.Add("ret_selectall ", OracleDbType.Array);
//cmd.Parameters["ret_selectall "].Direction = ParameterDirection.ReturnValue;
//if (connection.State == ConnectionState.Closed)
// connection.Open();
//cmd.ExecuteNonQuery();
稍后……
//OracleCommand objCmd = new OracleCommand("select * from table fsp_SelectAll", connection);
//objCmd.CommandType = CommandType.StoredProcedure;
//objCmd.ExecuteNonQuery();
//int size=objCmd.ExecuteReader().FieldCount;
我也尝试过
//adapter = new OracleDataAdapter(cmd);...
但是关于类型,参数总是存在一些错误... (连接到oracle工作.我执行了Insert,Update,Delete程序,它们工作正常)
But there are always some errors about type, parameters... (Connection to oracle work. I executed Insert, Update, Delete procedures and they work fine)
我也尝试创建此过程:(这是我的第一次尝试)
Also i tried to create this procedure: (this was my first try)
CREATE OR REPLACE PROCEDURE osp_SelectAll (Mjera OUT VARCHAR2,
Status OUT NUMBER, Naziv OUT VARCHAR2,Stat OUT NUMBER) AS
BEGIN
SELECT JEDINICAMJERE.Mjera,
JEDINICAMJERE.Status,
KATEGORIJE.Naziv,
KATEGORIJE.Status as Stat INTO Mjera, Status, Naziv, Stat
FROM JEDINICAMJERE, KATEGORIJE;
END osp_SelectAll;
但是它不返回任何一行...我放弃了.
But it doesn't return any row or rows... and i give up.
请帮助我,我需要解决这个问题.
Please help me, i need to solve this.
推荐答案
返回用户定义的对象并非易事,这需要大量的额外工作.
Returning a user defined object is not so easy, it requires a lot of additional effort.
尝试一下:
create or replace function fsp_SelectAll return SYS_REFCURSOR is
res SYS_REFCURSOR;
BEGIN
OPEN res FOR
SELECT JEDINICAMJERE.Mjera,
JEDINICAMJERE.Status,
KATEGORIJE.Naziv,
KATEGORIJE.Status as Stat
FROM JEDINICAMJERE, KATEGORIJE;
return res;
END;
然后
OracleCommand objCmd = new OracleCommand("fsp_SelectAll", connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("res", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
var da = new OracleDataAdapter(objCmd);
var dt = new DataTable();
da.Fill(dt);
或者,如果您愿意,也可以使用ExecuteReader()
:
Or you can use ExecuteReader()
, if you prefer:
OracleDataReader dr = objCmd.ExecuteReader();
while ( dr.Read() ) {
...
}
dr.Close();
注意,我从未使用过.CommandType = CommandType.StoredProcedure;
.如果失败,请使用以下语法:
Note, I never worked with .CommandType = CommandType.StoredProcedure;
. If it fails use this syntax:
OracleCommand objCmd = new OracleCommand("BEGIN :res := fsp_SelectAll; END;", connection);
objCmd.CommandType = CommandType.Text;
这篇关于Oracle函数将行返回到C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!