COLUMN_VALUE Pseudocolumn

在使用Oracle Table()函数时遇到问题，查找资料得知Pseudocolumn

资料地址为：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns004.htm

 

 

COLUMN_VALUE Pseudocolumn

 

When you refer to an XMLTable construct without the COLUMNS clause, or when you use the TABLE function to refer to a scalar nested table type, the database returns a virtual table with a single column. This name of this pseudocolumn is COLUMN_VALUE.

In the context of XMLTable, the value returned is of datatype XMLType. For example, the following two statements are equivalent, and the output for both shows COLUMN_VALUEas the name of the column being returned:

SELECT * FROM XMLTABLE('<a>123</a>');

COLUMN_VALUE
---------------------------------------
<a>123</a>

SELECT COLUMN_VALUE FROM (XMLTable('<a>123</a>'));

COLUMN_VALUE
----------------------------------------
<a>123</a>

In the context of a TABLE function, the value returned is the datatype of the collection element. The following statements create the two levels of nested tables illustrated in"Multi-level Collection Example" to show the uses of COLUMN_VALUE in this context:

CREATE TYPE phone AS TABLE OF NUMBER;   
/
CREATE TYPE phone_list AS TABLE OF phone;
/

The next statement uses COLUMN_VALUE to select from the phone type:

SELECT t.COLUMN_VALUE from table(phone(1,2,3)) t;

COLUMN_VALUE
------------
          1
          2
          3

In a nested type, you can use the COLUMN_VALUE pseudocolumn in both the select list and the TABLE function:

SELECT t.COLUMN_VALUE FROM 
   TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t;
COLUMN_VALUE
------------
           1
           2
           3

The keyword COLUMN_VALUE is also the name that Oracle Database generates for the scalar value of an inner nested table without a column or attribute name, as shown in the example that follows. In this context, COLUMN_VALUE is not a pseudocolumn, but an actual column name.

CREATE TABLE my_customers (
   cust_id       NUMBER,
   name          VARCHAR2(25),
   phone_numbers phone_list,
   credit_limit  NUMBER)
   NESTED TABLE phone_numbers STORE AS outer_ntab
   (NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);

See Also:

• XMLTABLE for information on that function

• table_collection_expression ::= for information on the TABLE function

• ALTER TABLE examples in "Nested Tables: Examples"

---

在Oracle的存储过程中，有可能会对输入的数据参数作处理。这时使用Type + Table()函数的方式就很方便。

例如存在Type类型 type_ac_number

create or replace type type_ac_number as table of number

 在某存储过程中作为输入参数类型

CREATE OR REPLACE Procedure PRC_MERGE_PLAN
( ai_arr_plan_id In type_ac_number,
   ac_opr_type In varchar2,
... ...
ai_code Out Integer,
ac_desc Out varchar2) as
 Begin
.... ...
.... ...

Update b_pool_task a
set a.plan_outage_dt_st = 
    (select b.plan_outage_dt_st
     from b_pool_plan b
     where b.plan_id = ac_plan_id)
where exist(select 1
                    from Table(Cast(ai_arr_plan_id As type_ac_number))
                    where column_value = a.plan_id)
And a.plan_id <> ac_plan_id

 

这里就用到了column_value。