我可以在Oracle中将表作为输出参数作为单个查询来编写存储过程吗? [英] Can I write the stored procedure with table as output parameter as a single query in Oracle?

问题描述

我有一个C#方法，该方法采用一条SQL字符串语句并将数据保存为xml格式.

I have a C# method which takes a SQL string statement and saves the data into xml format.

public XmlDocument GetDBRequestXml(String sql)
{
}

我有一个存储过程，输出参数如表所示.在上述C#方法中，是否有任何方法可以将此存储过程作为可执行的单个SQL语句传递?有人可以帮我吗！！！

I have a stored procedure with output parameter as table. Is there any way to pass this stored procedure as an executable single SQL statement in the above C# method? Can somebody please help me on this!!!

create or replace PACKAGE BODY EMPLOYEE_DETAILS AS

    PROCEDURE GET_EMPLOYEES(
        EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE, 
        EMP_SALARY employees.salary%TYPE,
        TBL_EMPLOYEES OUT TABLE_EMPLOYEES)
    IS
        LC_SELECT SYS_REFCURSOR;
        LR_DETAILS DETAILS;
        TBL_EMPLOYEE EMPLOYEE_DETAILS.TABLE_EMPLOYEES := EMPLOYEE_DETAILS.TABLE_EMPLOYEES();

    BEGIN
        OPEN LC_SELECT FOR 
            SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME 
            FROM EMPLOYEES
            WHERE DEPARTMENT_ID = EMP_DEPT_ID AND 
                  EMPLOYEES.SALARY > EMP_SALARY;

        LOOP 
            FETCH LC_SELECT INTO LR_DETAILS;
            EXIT WHEN LC_SELECT%NOTFOUND;

            IF LR_DETAILS.EMPLOYEE_ID > 114 THEN
                TBL_EMPLOYEE.extend();
                TBL_EMPLOYEE(TBL_EMPLOYEE.count()) := LR_DETAILS;
            END IF;
        END LOOP;
        CLOSE LC_SELECT;
        TBL_EMPLOYEES := TBL_EMPLOYEE;

    END GET_EMPLOYEES;
END EMPLOYEE_DETAILS; 

推荐答案

我今天做得很好.我不知道您是否仍然需要它，但这很容易理解.您可以从存储的proc中输出此内容:

I had a good run at this today. I don't know if you still need it but this is good to understand. You can output this from stored proc:

CREATE OR REPLACE PACKAGE Eidmadm.TestPkg AS
  TYPE stringTbl IS TABLE OF varchar2(250) INDEX BY BINARY_INTEGER;

  PROCEDURE TestProc (p_strings out stringTbl );
END;

另一个表，如下所示，不起作用

The other table, like below, didn't work

TYPE numTbl IS TABLE OF varchar2(100);

用于此目的的c#代码为:

The c# code for this is:

OracleParameter p2 = new OracleParameter(":p_strings", OracleDbType.Varchar2, ParameterDirection.Output);
p2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p2.Size = 100; // allocate enough extra space to retrieve expected result
// assign amount of space for each member of returning array
p2.ArrayBindSize = Enumerable.Repeat(250, 100).ToArray(); 

cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();

// And this is how you retrieve values
OracleString[] oraStrings = (OracleString[])p2.Value;
string[] myP2Values = new string[oraStrings.Length];

for (int i = 0; i < oraNumbers.Length; i++)
    myP2Values[i] = oraStrings[i].Value;

**但是最重要的是:**

**But most important is this: **

When you fill your pl/sql table, it needs to start from something larger than `0`, and preferably from `1`. Because and also - if you have index with skipped numbers, i.e. `2,4,6,8`, all those spaces will be part of returning `oracle array` and there will be `oracle null` in them. You would need to check for `null` in your loop

if !oraStrings[i].IsNull {....} 
else {....}

享受！

这篇关于我可以在Oracle中将表作为输出参数作为单个查询来编写存储过程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！