返回从Oracle记录集使用Oracle函数，它的参数到.NET [英] Returning a recordset from Oracle to .Net using an Oracle function that takes arguments
问题描述
我目前在它返回管道分隔一个连接字符串，Oracle数据库以下功能可按。这是正在被更新到.NET 3.5使用传统的应用程序。该exiisting应用地连接了返回的结果集为一个VARCHAR2数据类型。我想要做的就是返回整个结果集返回给我的.NET客户端。在MS SQL相当于什么，我试图完成一个简单的SELECT * FROM TBL WHERE ID = @id我不会用的一些概念，Oracle使用。 I't看起来像电子混合OOP和SQL查询。我读过这个多的例子，但似乎无法找到我在寻找什么。能否请你帮忙吗？
CREATE OR REPLACE FUNCTION DOCSADMIN.GET_DOCS（
RECID数量） - 记录ID
RETURN VARCHAR2 - 连接字符串，通过配管
IS
RETVAL VARCHAR2（5000）：='';
文档字符串VARCHAR2（5000）：='';
VARCHAR2可采用DocName（5000）：='';
DOCNUMBER数;
STATUS VARCHAR2（5000）：='';
DOCTYPE VARCHAR2（5000）：='';
EDITDATE日期：='';
/ ******************* *****************************
名称：GET_DOCS
目的：从个人资料表拉动相关文档
************************************************** ************ /
CURSOR GETDOCINFO的是选择DOCNUMBER，可采用DocName，CUSTOM_STATUS，DOCUMENTTYPES.DESCRIPTION，LAST_EDIT_TIME
从概要，DOCUMENTTYPES，表格WHERE NAD_APID = IN_APID和PROFILE.FORM = FORMS.SYSTEM_ID和
DOCUMENTTYPE = DOCUMENTTYPES.SYSTEM_ID和FORM_NAME ='DOCS_PROFILE'ORDER BY DOCNUMBER;
开始
OPEN GETDOCINFO;
--get第一条记录
FETCH GETDOCINFO INTO DOCNUMBER，可采用DocName，地位，DOCTYPE，EDITDATE;
--LOOP通过所有相关的文档和抓取信息
WHILE GETDOCINFO％FOUND LOOP
开始
文档字符串：= DOCNUMBER || |〜|' || ||可采用DocName |〜|' ||状态|| |〜|' || DOCTYPE || |〜|' || WS_EDITDATE;
RETVAL：= RETVAL ||文档字符串|| '| ^ |';
GOTO STARTOVER;
结束;
＆LT;＆LT; STARTOVER＆GT;＆GT;
FETCH GETDOCINFO INTO DOCNUMBER，可采用DocName，地位，DOCTYPE，EDITDATE;
END LOOP;
关闭GETDOCINFO;
返回RETVAL;
例外
WHEN NO_DATA_FOUND THEN
空值;
WHEN OTHERS THEN
- 考虑记录
该错误，然后重新加注
提高;
END GET_DOCS;
/
那么，你可以转换功能的步骤，并拥有的 SYS_REFCURSOR 键入OUT参数。随着甲骨文和.Net可以传回一个游标遍历，作为一个读者。
样品Oracle过程：
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.TEST_PROCEDURE（
OUT_DATA OUT SYS_REFCURSOR;
） 如
开始
OPEN FOR OUT_DATA
SELECT COL1，
COL2
从TEST_SCHEMA.TEST_TABLE;
END test_procedure;
示例.NET结尾：
用（为OracleConnection连接=新的OracleConnection（CONNSTRING））
使用（的OracleCommand命令= connection.CreateCommand（））{
command.CommandType = CommandType.StoredProcedure;
command.CommandText =TEST_SCHEMA.TEST_PROCEDURE;
command.Parameters.Add（OUT_DATA，OracleType.Cursor）
.Direction = ParameterDirection.Output;
connection.Open（）;
command.ExecuteNonQuery（）;
OracleDataReader读卡器=
command.Parameters [OUT_DATA]值作为OracleDataReader。
如果（读者！= NULL）{
使用（阅读器）{
而（reader.Read（））{
字符串COL1 =读卡器[COL1]作为串;
字符串COL2 =读卡器[COL2]作为串;
}
}
}
}
请务必关闭游标你使用它完成后（如上所完成的使用（阅读器）语句）。
所以，你的情况，你也许可以创建一个输出原始光标定位到函数的过程，然后就遍历光标在.net上面列出。刚一说明，从Oracle侧列名很重要，将与你在.NET中使用了什么。
I currently have the following funciton in an oracle database that returns a concatenated string seperated by pipes. It's a legacy application that is being updated to use .net 3.5. The exiisting application concatenates the returned result set into a VARCHAR2 data type. What I want to do is return the entire result set back to my .net client. The MS SQL equivalent of what I'm trying to accomplish is a simple "SELECT * FROM TBL WHERE id = @id" I'm not use to some of the concepts Oracle uses. I't seems like e blend of OOP and SQL querying. I've read multiple examples on this but can't seem to find exactly what I'm looking for. Can you please help?
CREATE OR REPLACE FUNCTION DOCSADMIN.GET_DOCS (
RECID IN NUMBER) -- RECORD ID
RETURN VARCHAR2 -- CONCATENATED STRING WITH PIPES
IS
RETVAL VARCHAR2(5000) :='';
DOCSTRING VARCHAR2(5000) :='';
DOCNAME VARCHAR2(5000) :='';
DOCNUMBER NUMBER;
STATUS VARCHAR2(5000) :='';
DOCTYPE VARCHAR2(5000) :='';
EDITDATE DATE :='';
/******************************************************************************
NAME: GET_DOCS
PURPOSE: Pulls associated docs from profile table
******************************************************************************/
CURSOR GETDOCINFO IS SELECT DOCNUMBER, DOCNAME, CUSTOM_STATUS, DOCUMENTTYPES.DESCRIPTION, LAST_EDIT_TIME
FROM PROFILE, DOCUMENTTYPES, FORMS WHERE NAD_APID = IN_APID AND PROFILE.FORM = FORMS.SYSTEM_ID AND
DOCUMENTTYPE = DOCUMENTTYPES.SYSTEM_ID AND FORM_NAME = 'DOCS_PROFILE' ORDER BY DOCNUMBER;
BEGIN
OPEN GETDOCINFO;
--GET THE FIRST RECORD
FETCH GETDOCINFO INTO DOCNUMBER, DOCNAME, STATUS, DOCTYPE, EDITDATE;
--LOOP THROUGH ALL ASSOCIATED DOCS AND GRAB INFO
WHILE GETDOCINFO%FOUND LOOP
BEGIN
DOCSTRING := DOCNUMBER || '|~|' || DOCNAME || '|~|' || STATUS || '|~|' || DOCTYPE || '|~|' || WS_EDITDATE;
RETVAL := RETVAL || DOCSTRING || '|^|';
GOTO STARTOVER;
END;
<<STARTOVER>>
FETCH GETDOCINFO INTO DOCNUMBER, DOCNAME, STATUS, DOCTYPE, EDITDATE;
END LOOP;
CLOSE GETDOCINFO;
RETURN RETVAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging
the error and then re-raise
RAISE;
END GET_DOCS;
/
Well, you could convert the function in to a procedure and have an OUT parameter of the SYS_REFCURSOR type. With Oracle and .Net you can pass back a cursor and iterate through that as a reader.
Sample Oracle procedure:
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.TEST_PROCEDURE (
out_DATA OUT SYS_REFCURSOR;
) AS
BEGIN
OPEN out_DATA FOR
SELECT col1,
col2
FROM TEST_SCHEMA.TEST_TABLE;
END test_procedure;
Sample .Net end:
using (OracleConnection connection = new OracleConnection("connstring"))
using (OracleCommand command = connection.CreateCommand()) {
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TEST_SCHEMA.TEST_PROCEDURE";
command.Parameters.Add("out_DATA", OracleType.Cursor)
.Direction = ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
OracleDataReader reader =
command.Parameters["out_DATA"].Value as OracleDataReader;
if (reader != null) {
using (reader) {
while(reader.Read()) {
string col1 = reader["col1"] as string;
string col2 = reader["col2"] as string;
}
}
}
}
Be sure to close the cursor after you're done using it (accomplished above by the using (reader) statement).
So in your case, you could probably create a procedure that outputs the original cursor in your function, then just iterate over the cursor in .Net as listed above. Just a note, the column names from the Oracle side are important and will match what you're using in .Net.
这篇关于返回从Oracle记录集使用Oracle函数，它的参数到.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
