如何在Oracle SQL Developer中将存储过程的输出参数选择为表格

Question

我有一个过程

create or replace PROCEDURE proc
(
  p1 IN varchar2,
  p2 IN varchar2,
  p3 OUT varchar2
) AS
BEGIN
 p3:= p1 || ' ' || p2
END proc

我称其为

Declare
  P3 varchar(50);
Begin
 proc('foo', 'bar', P3)
END;

我可以使用以下方式打印出 P3 的值：

Dbms_Output.Put_line('P3: ' || P3)

但我需要将结果作为表格以便我可以从中选择。

我能想到的最好方法是：

Declare
  P3 varchar(50);
Begin
 proc('foo', 'bar', P3)
END;
SELECT &&P3 from dual;

但是这会给我一个错误

Error report -
ORA-06550: line 5, column 12:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 5, column 5:
PL/SQL: SQL statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilatiopn error.
*Action:

有没有一种方法可以将参数值作为列/行值进行选择？

我需要在单个SQL查询中完成所有操作，因为我需要通过链接服务器从另一台服务器执行它。

我没有权限在数据库上创建任何其他存储过程、函数或表。

- Tomas

Answer 1

对我而言，最直接的选择是创建一个函数 - 它应该“返回一个值”：

create or replace function proc (p1 IN varchar2, p2 IN varchar2)
  return varchar2
AS
BEGIN
  return p1 || ' ' || p2;
END proc;

那么你可以这样调用它：

select proc('a', 'b') from dual;

---

如果你说“没有权限创建...函数” - 那么，如果你被授予权限创建一个存储过程(PROCEDURE)，那么你也被授予权限创建一个函数(FUNCTION)。这是相同的权限。

---

如果你只能使用已经创建好的存储过程，则：

SQL> create or replace PROCEDURE proc
  2  (
  3    p1 IN varchar2,
  4    p2 IN varchar2,
  5    p3 OUT varchar2
  6  ) AS
  7  BEGIN
  8    p3:= p1 || ' ' || p2;
  9  END ;
 10  /

Procedure created.

SQL> var result varchar2(20)
SQL>
SQL> exec proc('a', 'b', :result);

PL/SQL procedure successfully completed.

SQL> print :result

RESULT
--------------------------------
a b
    
SQL> select :result from dual;

:RESULT
--------------------------------
a b

SQL>

- Littlefoot

---

就像我说的那样，我没有创建或修改任何存储过程或函数的权限。该函数是由服务器所有者创建的，而我所拥有的只是具有选择权限的登录凭据。 - Tomas

啊哈，所以你只能使用已经创建好的内容。好的，我在答案中添加了一些代码，请看看。 - Littlefoot

如果我添加select :result from dual;，它会要求我输入result的绑定变量，然后只选择我输入的值而不是过程返回的值。 - Tomas

在你使用的工具中禁用该功能（针对此特定任务）。稍后重新启用它。 - Littlefoot

在SQL*Plus中，您的解决方案完美地运行。感谢您为这个问题所花费的所有帮助和时间。我对Oracle完全是新手。 - Tomas

Answer 2

如果您使用的是Oracle 12c或更高版本，则可以使用该版本中添加的WITH FUNCTION功能。使用此功能，您可以将过程包装到本地定义函数的PL/SQL块中，并在SELECT中使用此函数。不需要CREATE PROCEDURE即可使其工作。

create procedure proc (
  p1 in int,
  p2 in int,
  pout out int
)
as
begin
  pout := p1 + p2;
end;
/

✓

with function f(
  p1 in int,
  p2 in int
) return int
as
  pout int;
begin
  proc(p1, p2, pout);
  return (pout);
end;

select f(1,2)
from dual

| F(1,2) |
| -----: |
|      3 |

db<>fiddle 这里

- astentx

---

确实，那正是我所需要的。谢谢。 - Tomas