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?
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
精彩评论