Oracle 存储过程返回带有字段名称的记录集 [英] Oracle Stored Procedure Return Recordset with Field Names

问题描述

我是 Oracle 和存储过程的新手.我只是想知道是否可以像在 SQL Server 中一样将带有字段名称的记录集返回给外部程序.我阅读了一些文档，但我不确定我是否在正确的轨道上.当我使用 Sys_Refcursor 时，我只能返回一个 Field 而不是我想要的那么多.

I am new to Oracle and Stored Procedures. I just would like to know if its possible, like in SQL Server, to return a recordset with Field Names to an extern program. I read some documentations but I'm not sure if I'm on the right track. When I use Sys_Refcursor I can only return one Field and not as many as I would like to.

我需要返回多个字段名称并且我有一个输入参数.

I need to return multiple Field Names and I have one input parameter.

在程序的文档中，我有一个 SQL Server 示例，我希望我的 Oracle 存储过程也有相同的示例:

In the documentation of the program, i have an example for SQL Server and I would like to have the same for my Oracle Stored Procedure:

Use
Go
Set Ansi_Nulls ON
Go
Alter Procedure
   @InputLocation Varchar(255)
As
Begin
   Set Nocount On;
   select FirstName as '@FirstName', Company as '@Company' from dbo.company where Location = @InputLocation
End

有什么建议我可以这样做吗?如果您需要一些其他信息，请告诉我.谢谢.

Are there any suggestions how I can do that? If you need some additional informations just let me know. Thanks.

/

我的示例代码(没有使用第一步中的输入参数，只是为了生成输出看是否有效):

My sample Code (without using the Input Parameter in the first step, just for generating Output to see if it works):

create or replace 
PROCEDURE TEST_PROZEDUR1 (
  Input_Location IN Varchar2, 
  First_Name OUT SYS_Refcursor, 
  Company OUT Sys_Refcursor) IS 
BEGIN
  open First_Name For Select FirstName from dbo.company;
  open Company For Select Company from dbo.company;
END TEST_PROZEDUR1;

推荐答案

PL/SQL 和 TSQL 使用的编程模型是不同的.在 TSQL 中，您可能会返回记录集，而在 PL/SQL 中，您将返回一个游标.游标只是指向已打开并可读取的 SQL 语句的指针.它不限于返回单个列.粗略地说，与上面的 TSQL 过程等效的 PL/SQL 将类似于:

The programming models used for PL/SQL and TSQL are different. Where you might return a recordset in TSQL, in PL/SQL you would return a cursor. A cursor is just a pointer to an SQL statement which is opened and can be read. It is not limited to returning a single column. Roughly, the PL/SQL equivalent of your TSQL procedure above would be something like:

CREATE OR REPLACE FUNCTION GET_INPUT_LOCATION(pinInput_location IN VARCHAR2(255))
  RETURN SYS_REFCURSOR
IS
  cCursor  SYS_REFCURSOR;
BEGIN
  OPEN cCursor FOR
    SELECT FIRSTNAME,
           COMPANY
      FROM COMPANY
      WHERE LOCATION = pinInput_location;

  RETURN cCursor;
END GET_INPUT_LOCATION;

然后调用者会调用这个函数:

The caller would then invoke this function as:

DECLARE
  cCursor       SYS_REFCURSOR;
  strFirstname  COMPANY.FIRSTNAME%TYPE;
  strCompany    COMPANY.COMPANY%TYPE;
BEGIN
  cCursor := GET_INPUT_LOCATION('SOMEWHERE OVER THE RAINBOW, INC.');

  FETCH cCursor
    INTO strFirstname,
         strCompany;

  CLOSE cCursor;
END;

但是，我可能不会这样编码.如果 COMPANY.LOCATION 是唯一的，那么返回一个游标会遇到很多麻烦，调用者在完成它时需要记住关闭它，而他们可能会忘记这样做.相反，我只是使用输出参数返回 FIRSTNAME 和 COMPANY 字段；例如

However, I probably wouldn't code it this way. If COMPANY.LOCATION is unique then you're going to a lot of trouble to return a cursor which the caller will need to remember to close when they're done with it, which they may forget to do. Instead, I'd just return the FIRSTNAME and COMPANY fields using output parameters; e.g.

CREATE OR REPLACE PROCEDURE GET_INPUT_LOCATION
  (pinInput_location IN  VARCHAR2(255),
   poutFirst_name    OUT COMPANY.FIRSTNAME%TYPE,
   poutCompany       OUT COMPANY.COMPANY%TYPE)
IS
  cCursor  SYS_REFCURSOR;
BEGIN
  SELECT FIRSTNAME,
         COMPANY
    INTO poutFirst_name,
         poutCompany
    FROM COMPANY
    WHERE LOCATION = pinInput_location;
END GET_INPUT_LOCATION;

分享和享受.

这篇关于Oracle 存储过程返回带有字段名称的记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！