使用带参数的Oracle函数从Oracle返回一个记录集到.net

本文关键字：Oracle 一个 记录 net 返回 参数 函数 | 更新日期: 2023-09-27 18:14:48

我目前在oracle数据库中有以下函数，该函数返回由管道分隔的连接字符串。它是一个遗留的应用程序，正在更新为使用。net 3.5。现有应用程序将返回的结果集连接到VARCHAR2数据类型中。我想做的是返回整个结果集回到我的。net客户端。MS SQL相当于我想要完成的是一个简单的"SELECT * FROM TBL WHERE id = @id"，我不习惯Oracle使用的一些概念。它看起来不像是OOP和SQL查询的混合体。我已经读了很多例子，但似乎找不到我想要的。你能帮帮我吗?

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;
/

使用带参数的Oracle函数从Oracle返回一个记录集到.net

那么，您可以将函数转换为过程，并使用SYS_REFCURSOR类型的OUT参数。在Oracle和。net中，你可以将游标返回，并将其作为阅读器进行迭代。

Oracle程序示例:

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;
示例。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;
      }
    }
  }
}

确保在使用完游标后关闭它(上面通过using (reader)语句完成)。

因此，在您的情况下，您可能会创建一个过程，在函数中输出原始游标，然后像上面列出的那样在. net中迭代游标。请注意，Oracle端使用的列名很重要，它将与。net中使用的列名相匹配。

到目前为止，我已经编译好了。

CREATE OR REPLACE PROCEDURE DOCSADMIN.GET_DOCS_SP  ( IN_APID IN NUMBER,   out_DATA OUT SYS_REFCURSOR ) 
AS 
BEGIN   
OPEN out_DATA FOR   
SELECT DOCNUMBER, DOCNAME, CUSTOM_STATUS, DOCUMENTTYPES.DESCRIPTION, LAST_EDIT_TIME
        FROM PROFILE, DOCUMENTTYPES, FORMS WHERE APID = IN_APID AND PROFILE.FORM = FORMS.SYSTEM_ID AND 
        DOCUMENTTYPE = DOCUMENTTYPES.SYSTEM_ID AND FORM_NAME = 'PROFILE' ORDER BY DOCNUMBER;
END GET_DOCS_SP;

/

但是，我遇到了另一种情况，希望你能帮忙。如果我想使用OPENQUERY从sql数据库调用以下内容，我该如何做?返回连接字符串的旧版本如下所示:

SELECT * FROM OPENQUERY (TESTSERVER, 'SELECT DOCSADMIN.GET_DOCS_SP (26) AS DOCINFO FROM DUAL')

我只是删除作为DOCINFO FROM DUAL子句吗?

谢谢

相关文章：