TABLE
功能描述
TABLE函数将一个集合类型的数据转换成表格形式，方便进行查询和分析。支持的入参形式包括数组和setof，其中setof又分为return setof（PG风格）以及pipelined函数（Oracle风格）。
Vastbase实现了TABLE函数的以下增强功能：
TABLE函数入参支持变量，并且函数返回结果支持COLUMN_VALUE列。
COLUMN_VALUE：在 Oracle 中关键字 COLUMN_VALUE 也是数据库为没有列名或属性名的内部嵌套表的标量值生成的名称。在这种情况下，COLUMN_VALUE 不是伪列，而是实际的列名。
TABLE函数的入参可以为嵌套表类型。
TABLE函数的入参可以被类型转换函数CAST修饰。
由于无法对数组类型或setof类型进行直接转换，因此本特性适用的场景为参数向其自身类型的转换。即形如
table(cast(var as A))的使用场景中，变量var必须是A类型，否则将会报错。
注意事项
TABLE函数的增强功能仅在数据库兼容模式为Oracle时支持（即数据库初始化时指定DBCOMPATIBILITY='A'）。
语法格式
table(arg);
参数说明
arg
数据集，可以是数组类型或setof。
示例
示例1： 通过TABLE函数将查询的结果集显示为表的形式。
1、创建测试表并插入数据。
CREATE TABLE testtable(id int);
INSERT INTO testtable VALUES(123),(1234);
2、使用表函数查询结果。
SELECT * FROM table(testtable);
返回结果为：
id
------
123
1234
(2 rows)
3、清理测试表。
DROP TABLE testtable;
示例2： TABLE函数入参使用变量。
1、创建自定义类型。
CREATE TYPE nationalstring IS TABLE OF varchar(2000);
2、创建函数。
CREATE OR REPLACE FUNCTION teststr return nationalstring is
na nationalstring;
lcn integer(5);
begin
na(1) := 'abc100';
na(2) := 'abc200';
na(3) := 'abc300';
select count(*) into lcn from table(na);
dbms_output.put_line('写入行数'||lcn);
return na;
end;
/
3、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。
set serveroutput on;
4、调用函数。
SELECT * FROM table(teststr());
返回结果为：
写入行数3
column_value
--------------
abc100
abc200
abc300
(3 rows)
5、清理环境。
DROP TYPE nationalstring;
DROP FUNCTION teststr;
示例3： table入参使用嵌套表。
1、创建自定义数据类型。
CREATE OR REPLACE TYPE t_test as object(
id integer,
rq date,
mc varchar2(60)
);
2、使用步骤1定义好的t_test数据类型创建自定义类型。
CREATE OR REPLACE TYPE t_test_table AS TABLE OF t_test;
3、创建函数。
CREATE OR REPLACE FUNCTION f_test_array(n in number default null) return t_test_table
as
v_test t_test_table;
begin
for i in 1 .. nvl(n,100) loop
v_test(i) := (i,sysdate,'mc'||i);
end loop;
return v_test;
end;
/
4、调用函数。
SELECT * FROM table(f_test_array(10));
返回结果为：
id | rq | mc
----+---------------------+------
1 | 2022-11-04 16:11:56 | mc1
2 | 2022-11-04 16:11:56 | mc2
3 | 2022-11-04 16:11:56 | mc3
4 | 2022-11-04 16:11:56 | mc4
5 | 2022-11-04 16:11:56 | mc5
6 | 2022-11-04 16:11:56 | mc6
7 | 2022-11-04 16:11:56 | mc7
8 | 2022-11-04 16:11:56 | mc8
9 | 2022-11-04 16:11:56 | mc9
10 | 2022-11-04 16:11:56 | mc10
(10 rows)
5、清理环境。
DROP TYPE t_test CASCADE;
DROP TYPE t_test_table;
DROP FUNCTION f_test_array;
示例4： 在with子句中调用table函数，支持column_value列。
1、创建包和包体。
CREATE OR REPLACE PACKAGE package_test AS
TYPE test_Type_List is table of clob INDEX BY varchar2(20);
function func_Pack_para(tmp1_in nvarchar2,tmp2_in nvarchar2) return test_Type_List;
end package_test;
/
CREATE OR REPLACE PACKAGE BODY package_test IS
function func_Pack_para(tmp1_in nvarchar2, tmp2_in nvarchar2) return test_Type_List IS
re_l test_Type_List := test_Type_List();
i int;
begin
re_l('Aa一') := tmp1_in;
re_l('Bb二') := tmp2_in;
re_l('CC') := '333###ccc';
re_l('Bb二') := '22###ccc@_--';
return re_l;
end;
end package_test;
/
2、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。
set serveroutput on;
3、调用函数使用column_value列。
declare
type c_test is ref cursor;
v_cursor2 c_test;
proc_v package_test.test_Type_List:=package_test.func_Pack_para('testTEST测试ちゅうごく~!@#End','222testTEST测试ちゅうごく~!@#End');
v_row2 varchar(400);
begin
open v_cursor2 for
with temp as (select column_value from table(proc_v))
select * from temp;
loop
fetch v_cursor2 into v_row2;
exit when v_cursor2%notfound;
DBMS_OUTPUT.PUT_LINE ('V:'||v_row2);
end loop;
end;
/
返回结果为：
V:testTEST测试ちゅうごく~!@#End
V:22###ccc@_--
V:333###ccc
ANONYMOUS BLOCK EXECUTE
4、清理环境。
DROP PACKAGE package_test;
示例5： 在存储过程中使用TABLE函数，TABLE函数的入参被CAST修饰。
1、创建存储过程。
create or replace procedure proc_test() as
tb1_1219798 NUMBER;
tb2_1219798 TIMESTAMP;
begin
SELECT * INTO tb1_1219798 FROM table(CAST('123' AS NUMBER));
SELECT * INTO tb2_1219798 FROM table(CAST('2022-01-01 12:34:56' AS TIMESTAMP));
RAISE NOTICE 'result %',tb1_1219798;
RAISE NOTICE 'result %',tb2_1219798;
end;
/
2、调用存储过程。
call proc_test();
调用成功，返回结果如下：
NOTICE: result 123
NOTICE: result 2022-01-01 12:34:56
proc_test
-----------
(1 row)
3、清理测试环境。
DROP PROCEDURE proc_test();
