Oracle 嵌套表和列名

发布于 2024-08-14 01:03:55 字数 632 浏览 4 评论 0原文

我想在 Oracle 10g 中执行以下操作（这是一个人为的示例，用于展示概念，而不是真实的代码）

    create table orders (order_id NUMBER);
    insert into table orders values (1);
    insert into table orders values (2);
    insert into table orders values (3);


TYPE NUMBER_ARRAY_T is TABLE of NUMBER;

PROCEDURE VALIDATE_ORDER_IDS(i_orders IN NUMBER_ARRAY_T, o_output OUT SYS_REFCURSOR)    
IS    
BEGIN
    OPEN o_output FOR
        select ??? from TABLE(i_orders) where ??? NOT IN (select order_id from orders);
END VALIDATE_ORDER_IDS;

存储过程将使用包含 (1,2) 的数组进行调用，我们期望返回 3因此

，问题是，是否有办法指定一个列名，其中 ???当使用嵌套表作为表时，上面的 select 语句会起作用吗？

原文

I'd like to do the following in Oracle 10g (this is a contrived example to show the concepts, not real code)

    create table orders (order_id NUMBER);
    insert into table orders values (1);
    insert into table orders values (2);
    insert into table orders values (3);


TYPE NUMBER_ARRAY_T is TABLE of NUMBER;

PROCEDURE VALIDATE_ORDER_IDS(i_orders IN NUMBER_ARRAY_T, o_output OUT SYS_REFCURSOR)    
IS    
BEGIN
    OPEN o_output FOR
        select ??? from TABLE(i_orders) where ??? NOT IN (select order_id from orders);
END VALIDATE_ORDER_IDS;

The stored procedure would be called with an array containing (1,2) and we'd expect to get 3 back as a result

So, the question is, is there anyway to specify a column name where the ??? are when using a nested table as a table, so the above select statement would work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

破晓 2024-08-21 01:03:55

您要查找的关键字是column_value。根据您的设置：

SQL> CREATE OR REPLACE TYPE NUMBER_ARRAY_T is TABLE of NUMBER;
  2  /

Type created
SQL> CREATE OR REPLACE PROCEDURE validate_order_ids(i_orders IN number_array_t,
  2                                                 o_output OUT SYS_REFCURSOR)
  3  IS BEGIN
  4      OPEN o_output FOR
  5          SELECT COLUMN_VALUE
  6            FROM TABLE(i_orders)
  7           WHERE COLUMN_VALUE NOT IN (SELECT order_id FROM orders);
  8  END validate_order_ids;
  9  /

Procedure created

要调用该过程，您只需：

SQL> VARIABLE x REFCURSOR
SQL> exec validate_order_ids(number_array_t(1,5), :x);

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> print x

COLUMN_VALUE
------------
           5

The keyword you're looking for is column_value. With your setting:

SQL> CREATE OR REPLACE TYPE NUMBER_ARRAY_T is TABLE of NUMBER;
  2  /

Type created
SQL> CREATE OR REPLACE PROCEDURE validate_order_ids(i_orders IN number_array_t,
  2                                                 o_output OUT SYS_REFCURSOR)
  3  IS BEGIN
  4      OPEN o_output FOR
  5          SELECT COLUMN_VALUE
  6            FROM TABLE(i_orders)
  7           WHERE COLUMN_VALUE NOT IN (SELECT order_id FROM orders);
  8  END validate_order_ids;
  9  /

Procedure created

To call the procedure you would simply:

SQL> VARIABLE x REFCURSOR
SQL> exec validate_order_ids(number_array_t(1,5), :x);

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> print x

COLUMN_VALUE
------------
           5

回复收藏 0 原文

~没有更多了~