如何从Oracle中的clarify/begin/end块返回行?

问题描述

我想从声明/开始/结束块中的选择语句返回行.我可以在T-SQL中做到这一点，但我想知道如何在PL/SQL中做到这一点.

I want to return rows from a select statement within a declare/begin/end block. I can do this in T-SQL but I would like to know how to do it in PL/SQL.

代码看起来类似于以下内容:

The code looks a bit like the following:

declare
     blah number := 42;
begin
     select *
     from x
     where x.value = blah;
end;

推荐答案

一个匿名的PL/SQL块(如您所示)无法返回"任何内容.但是，它可以通过绑定变量与调用方进行交互.

An anonymous PL/SQL block, like the one you've shown, can't "return" anything. It can interact with the caller by means of bind variables, however.

因此，在这种情况下，我将使用的方法是声明一个游标引用，在PL/SQL块中为所需查询打开它，然后让调用应用程序从中获取行.在SQLPlus中，它看起来像:

So the method I would use in this case would be to declare a cursor reference, open it in the PL/SQL block for the desired query, and let the calling application fetch rows from it. In SQLPlus this would look like:

variable rc refcursor

declare
     blah number := 42;
begin
  open :rc for
     select *
     from x
     where x.value = blah;
end;
/

print x

如果将PL/SQL重铸为存储函数，则它可能返回值.在这种情况下，您可能想要创建一个集合类型，将所有行提取到该类型的变量中，然后将其返回:

If you recast your PL/SQL as a stored function then it could return values. In this case what you might want to do is create a collection type, fetch all the rows into a variable of that type, and return it:

CREATE TYPE number_table AS TABLE OF NUMBER;

CREATE FUNCTION get_blah_from_x (blah  INTEGER)
  RETURN number_table
  IS
    values  number_table;
  BEGIN
    SELECT id
      BULK COLLECT INTO values
      FROM x
      WHERE x.value = blah;
    RETURN values;
  END;
/

这篇关于如何从Oracle中的clarify/begin/end块返回行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！