时间戳

首页 > 解决方案 > 有没有办法从游标中将 plsql 表添加为 SELECT 子句中的列？

oracle - 有没有办法从游标中将 plsql 表添加为 SELECT 子句中的列？

问题描述

我正在编写一个返回 SYS_REFCURSOR CURSOR 的 plsql 过程。

  PROCEDURE PRC_USER_APPS(o_total_rows    OUT NUMBER,
                          o_result_status OUT VARCHAR2,
                          o_cursor        OUT SYS_REFCURSOR);

我坚持当前的分配，因为我需要添加一个 plsql 表或从函数返回的嵌套表作为 SELECT 游标中的列。可以编写这样的代码吗？

OPEN o_cursor FOR
  SELECT id_application, usr.id_user, name_user, fnc_user_phone(usr.id_user)
  FROM   users usr,
         users_applications uap
  WHERE  usr.id_user = uap.id_user;

结果：

99909, 001, 'José de San Martín', {'mobilephone1', 549351999999, 'mobilephone2', 54935188888}

新年快乐！！！

标签： oracleplsqlcursorsys-refcursor

解决方案

您需要了解“plsql 表”或“嵌套表”的含义。

Oracle 文档将这种东西称为“集合”。您必须定义一个集合类型，然后创建该类型的集合。

有 SQL 集合类型和 PL/SQL 集合类型。

  • SQL 集合类型来自 CREATE TYPE ... AS TABLE 语句
  • PL/SQL 集合类型在 PL/SQL 代码中使用 TYPE ... IS TABLE 语句定义。
  • SQL 语句（如 SELECT）只了解 SQL 集合类型！
  • PL/SQL 代码可以使用 SQL 集合类型。
  • SQL 集合可以是 VARRAY 或嵌套表。

在您的情况下，您根本不能在 SELECT 语句中使用“plsql 表”，但如果它基于 SQL 类型，则可以使用“嵌套表”。

create or replace type t_phone as object(
  label varchar2(20),
  nbr integer
)
/
create or replace type tt_phone as table of t_phone
/
select d.*, tt_phone(t_phone('home',12345678), t_phone('work',23456789))
from dept d;

由于这些 SQL 类型，您将无法查看输出。此外，获得您的引用光标的人也必须了解您的类型。这并不明显。

您可以通过使用 CURSOR 而不是嵌套表使事情变得更容易：

select d.*, cursor(select empno, ename from emp where deptno = d.deptno) emps
from dept d;

DEPTNO DNAME        LOC       EMPS
    10 ACCOUNTING   NEW YORK  {<EMPNO=7782,ENAME=CLARK>,<EMPNO=7839,ENAME=KING>,<EMPNO=7934,ENAME=MILLER>,}
    20 RESEARCH     DALLAS    {<EMPNO=7369,ENAME=SMITH>,<EMPNO=7566,ENAME=JONES>,<EMPNO=7788,ENAME=SCOTT>,<EMPNO=7876,ENAME=ADAMS>,<EMPNO=7902,ENAME=FORD>,}
    30 SALES        CHICAGO   {<EMPNO=7499,ENAME=ALLEN>,<EMPNO=7521,ENAME=WARD>,<EMPNO=7654,ENAME=MARTIN>,<EMPNO=7698,ENAME=BLAKE>,<EMPNO=7844,ENAME=TURNER>,<EMPNO=7900,ENAME=JAMES>,}
    40 OPERATIONS   BOSTON    {}

推荐阅读