前言
从ORACLE 18C开始,ORACLE添加了PTF功能,目前在关系型数据库中,只有ORACLE支持此功能,简单来说,这个功能就是传入一个表作为参数,返回一个查询结果,而返回的这个表可以进行各种各样的定义或者修改,比如让返回的结果中自动屏蔽某些字段、自动对所有字段进行TRIM、自动对所有字段进行NVL、自动对所有number字段保留4位小数、自动添加一些字段、自动将所有date类型的字段修改为VARCHAR2类型,而且值得一提的是,一旦创建一个这样的函数,那么这个函数对所有的例子都通用,下面举几个例子来说明这个函数怎么使用。
1.将要查询的表中的所有date类型格式化成VARCHAR2类型输出,其他字段保持不变
众所周知,oracle中的date类型是个让人又爱又恨的类型,计算日期超方便,既可以表示年月日又可以表示年月日时分秒,麻烦的是,查询date类型默认输出的值是根据会话环境变量自动格式化的,经常会出现 “23-APR-21”或者“23-四月-21”这样的内容,在与其他系统进行数据交换时,经常会忘记使用"to_char"函数对日期进行格式化,导致其他系统接收失败,而且一个查询sql中date类型的字段多了,也要写好多个“to_char”,那么,有没有一种方式可以通用的,将所有要查询的数据中的date类型自动的进行to_char呢?
--先创建一个包说明
CREATE OR REPLACE PACKAGE ptf_date_to_char AS
FUNCTION describe(tab IN OUT dbms_tf.table_t)RETURN dbms_tf.describe_t;
PROCEDURE FETCH_ROWS;
END ptf_date_to_char;
/
--然后创建一个包体
CREATE OR REPLACE PACKAGE BODY ptf_date_to_char AS
FUNCTION describe(tab IN OUT dbms_tf.table_t) RETURN dbms_tf.describe_t AS
new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
FOR i IN 1 .. tab.column.count LOOP --对所有字段循环
IF tab.column(i).description.type IN (dbms_tf.type_date) THEN --如果字段是日期类型
tab.column(i).pass_through := FALSE;
tab.column(i).for_read := TRUE; --是否多态表的列
NEW_COLS(i) := DBMS_TF.COLUMN_METADATA_T(name => tab.column(i).description.name, --字段名不变
type => DBMS_TF.TYPE_VARCHAR2 --字段类型改成varchar2
);
END IF;
END LOOP;
RETURN DBMS_TF.describe_t(new_columns => new_cols,
row_replication => true);
END;
PROCEDURE FETCH_ROWS AS
inp_rs DBMS_TF.row_set_t;
out_rs DBMS_TF.row_set_t;
rows PLS_INTEGER;
BEGIN
--只有上面进入了NEW_COLS的字段才会执行下面这段,其他字段保持不变
--inp_rs 表示原始数据
--out_rs 表示要输出的数据,默认情况下为空
DBMS_TF.get_row_set(inp_rs, rows); --获取原始数据
FOR c IN 1 .. inp_rs.count() LOOP --对列进行循环
FOR r IN 1 .. rows LOOP --对行进行循环
if inp_rs(c).tab_date(r) = trunc(inp_rs(c).tab_date(r)) then
--这里做了个特殊处理,兼容年月日和年月日时分秒两种
out_rs(c).tab_varchar2(r) := to_char((inp_rs(c).tab_date(r)),
'yyyy-mm-dd');
else
out_rs(c).tab_varchar2(r) := to_char((inp_rs(c).tab_date(r)),
'yyyy-mm-dd hh24:mi:ss');
end if;
END LOOP;
END LOOP;
DBMS_TF.put_row_set(out_rs, replication_factor => 1);
END;
END ptf_date_to_char;
/
--再创建个供直接调用的函数
CREATE OR REPLACE FUNCTION ptf_date_to_char_F(tab TABLE)
RETURN TABLE pipelined row polymorphic USING ptf_date_to_char;
/
以上准备工作就做好了,让我们来测试一下
select * from ptf_date_to_char_F(hr.job_history);
EMPLOYEE_ID | JOB_ID | DEPARTMENT_ID | START_DATE | END_DATE |
---|---|---|---|---|
102 | IT_PROG | 60 | 2001-01-13 | 2006-07-24 |
101 | AC_ACCOUNT | 110 | 1997-09-21 | 2001-10-27 |
101 | AC_MGR | 110 | 2001-10-28 | 2005-03-15 |
201 | MK_REP | 20 | 2004-02-17 | 2007-12-19 |
114 | ST_CLERK | 50 | 2006-03-24 | 2007-12-31 |
122 | ST_CLERK | 50 | 2007-01-01 | 2007-12-31 |
200 | AD_ASST | 90 | 1995-09-17 | 2001-06-17 |
176 | SA_REP | 80 | 2006-03-24 | 2006-12-31 |
176 | SA_MAN | 80 | 2007-01-01 | 2007-12-31 |
200 | AC_ACCOUNT | 90 | 2002-07-01 | 2006-12-31 |
这样好像看不出字段类型,没关系,我们用这个sql创建一个表就知道了
create table temp_test as
select * from ptf_date_to_char_F(hr.job_history);
select TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH,COLUMN_ID
from DBA_TAB_COLS WHERE TABLE_NAME = 'TEMP_TEST';
TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_LENGTH | COLUMN_ID |
---|---|---|---|---|
TEMP_TEST | EMPLOYEE_ID | NUMBER | 22 | 1 |
TEMP_TEST | JOB_ID | VARCHAR2 | 10 | 2 |
TEMP_TEST | DEPARTMENT_ID | NUMBER | 22 | 3 |
TEMP_TEST | START_DATE | VARCHAR2 | 4000 | 4 |
TEMP_TEST | END_DATE | VARCHAR2 | 4000 | 5 |
对比下原表
select TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH,COLUMN_ID
from DBA_TAB_COLS WHERE TABLE_NAME = 'JOB_HISTORY';
TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_LENGTH | COLUMN_ID |
---|---|---|---|---|
JOB_HISTORY | EMPLOYEE_ID | NUMBER | 22 | 1 |
JOB_HISTORY | START_DATE | DATE | 7 | 2 |
JOB_HISTORY | END_DATE | DATE | 7 | 3 |
JOB_HISTORY | JOB_ID | VARCHAR2 | 10 | 4 |
JOB_HISTORY | DEPARTMENT_ID | NUMBER | 22 | 5 |
我们可以发现字段类型的确由DATE类型变成了VARCHAR2类型,说明我们方案是可行的,以后只要使用这个函数,任何查询sql或者表都可以自动进行此类转换了。这里要注意的一点,修改后的字段,顺序都排到最后面去了,而且varchar2类型,默认是给的最长的4000,这个可以通过如下方式进行定义
NEW_COLS(i) := DBMS_TF.COLUMN_METADATA_T(name => tab.column(i).description.name, --字段名不变
type => DBMS_TF.TYPE_VARCHAR2 --字段类型改成varchar2,
max_len =>20
);
同理,number类型的有效位数和小数点精确度也可以通过precision,scale这两个参数来定义。
另外,上述创建了两个数据库对象,这对某些“代码洁癖”的开发者是很不爽的,其实这个函数和包可以合并成一个包,把这个函数单独放到包说明中即可,如下
CREATE OR REPLACE PACKAGE ptf_date_to_char AS
FUNCTION input(tab TABLE)
RETURN TABLE pipelined row polymorphic USING ptf_date_to_char;
FUNCTION describe(tab IN OUT dbms_tf.table_t)RETURN dbms_tf.describe_t;
PROCEDURE FETCH_ROWS;
END ptf_date_to_char;
/
CREATE OR REPLACE PACKAGE BODY ptf_date_to_char AS
FUNCTION describe(tab IN OUT dbms_tf.table_t) RETURN dbms_tf.describe_t AS
new_cols DBMS_TF.COLUMNS_NEW_T;
BEGIN
FOR i IN 1 .. tab.column.count LOOP --对所有字段循环
IF tab.column(i).description.type IN (dbms_tf.type_date) THEN --如果字段是日期类型
tab.column(i).pass_through := FALSE;
tab.column(i).for_read := TRUE; --是否多态表的列
NEW_COLS(i) := DBMS_TF.COLUMN_METADATA_T(name => tab.column(i).description.name, --字段名不变
type => DBMS_TF.TYPE_VARCHAR2 --字段类型改成varchar2
);
END IF;
END LOOP;
RETURN DBMS_TF.describe_t(new_columns => new_cols,
row_replication => true);
END;
PROCEDURE FETCH_ROWS AS
inp_rs DBMS_TF.row_set_t;
out_rs DBMS_TF.row_set_t;
rows PLS_INTEGER;
BEGIN
--只有上面进入了NEW_COLS的字段才会执行下面这段,其他字段保持不变
--inp_rs 表示原始数据
--out_rs 表示要输出的数据,默认情况下为空
DBMS_TF.get_row_set(inp_rs, rows); --获取原始数据
FOR c IN 1 .. inp_rs.count() LOOP --对列进行循环
FOR r IN 1 .. rows LOOP --对行进行循环
if inp_rs(c).tab_date(r) = trunc(inp_rs(c).tab_date(r)) then --这里做了个特殊处理,兼容年月日和年月日时分秒两种
out_rs(c).tab_varchar2(r) := to_char((inp_rs(c).tab_date(r)),
'yyyy-mm-dd');
else
out_rs(c).tab_varchar2(r) := to_char((inp_rs(c).tab_date(r)),
'yyyy-mm-dd hh24:mi:ss');
end if;
END LOOP;
END LOOP;
DBMS_TF.put_row_set(out_rs, replication_factor => 1);
END;
END ptf_date_to_char;
/
select * from ptf_date_to_char.input(hr.job_history);
我们稍微分析一下这个功能结构,
了解这个处理顺序后,我们就可以发挥想象来做一些其他PTF功能了,比如对所有字段进行nvl处理或者对所有字段进行trim处理等。
2.自动屏蔽指定字段
假设我们有一张表,有100多个字段(别问字段为什么这么多,siebel系统里就有一个),我们想要得到一个查询结果,这个查询结果中显示98个字段,有2个指定的字段不要显示,我们要如何写这个sql查询?
98个字段名都要列出来,想想都疯了吧,虽然我们可以用listagg加dba_tab_cols视图来拼接字符串,或者用pl/sql developer工具的自动完成来输入,但是,这个98个字段要放在程序里,也太长了,阅读和修改极为不便。这个时候我们就想要有一个函数,我们只要指定表名和不要的列名,就能查询出结果
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION my_ptf(tab IN TABLE,
col IN COLUMNS)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
FUNCTION describe (tab IN OUT DBMS_TF.table_t,
col IN dbms_tf.columns_t)
RETURN DBMS_TF.describe_t;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t,
col IN dbms_tf.columns_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
-- Loop through all the table columns.
FOR i IN 1 .. tab.column.count() LOOP
-- Loop through all the columns listed in the second parameter.
FOR j IN 1 .. col.count() LOOP
-- Set pass_through to true for any columns not in the exclude list.
tab.column(i).pass_through := (tab.column(i).description.name != col(j));
-- Exit inner loop if you find a column that shouldn't be included.
EXIT WHEN NOT tab.column(i).pass_through;
END LOOP;
END LOOP;
RETURN NULL;
END;
END poly_pkg;
/
SELECT * FROM poly_pkg.my_ptf(emp, COLUMNS(hiredate, sal, mgr, comm));
此代码来自 Tim Hall的文章 https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
这个查询表示,从emp表中输出列名不是hiredate/sal/mgr/comm这四个的所有字段。另外,这个sql后面是可以加where条件的,和普通的用法完全一样。
而且我们可以看到,由于不需要对每行的数据进行处理,这个包中没有使用FETCH_ROWS过程。
3.自动增加字段
有时候,我们想要基于原表的某些字段或者所有字段,生成几个新的字段,比如针对所有日期时间字段新增一个0时区日期时间字段,针对所有文本字段生成对应unicode值的字段,或者是根据所有字段生成一个json串,这些在做跨系统交互时,能带来极大的帮助,以下以新增一个包含所有字段的json字段为例
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION my_ptf(tab IN TABLE)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t;
PROCEDURE fetch_rows;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
-- Make sure the for_read flag for each real column
-- is set, or get_row_set has no values.
FOR i IN 1 .. tab.column.count LOOP
CONTINUE WHEN NOT DBMS_TF.supported_type(tab.column(i).description.TYPE);
tab.column(i).for_read := TRUE;
END LOOP;
-- Add the new JSON_DOC column.
RETURN DBMS_TF.describe_t(
new_columns => DBMS_TF.columns_new_t(1 => DBMS_TF.column_metadata_t(name =>'JSON_DOC'))
);
END;
PROCEDURE fetch_rows AS
l_row_set DBMS_TF.row_set_t;
l_new_col DBMS_TF.tab_varchar2_t;
l_row_count PLS_INTEGER;
BEGIN
DBMS_TF.get_row_set(l_row_set, row_count => l_row_count);
--DBMS_TF.trace(l_row_set);
-- Populate the new column with a JSON doc of the associated row.
FOR row_num IN 1 .. l_row_count LOOP
l_new_col(row_num) := DBMS_TF.row_to_char(l_row_set, row_num);
END LOOP;
-- Associate the new values with the new column.
DBMS_TF.put_col(1, l_new_col);
END;
END poly_pkg;
/
SELECT * FROM poly_pkg.my_ptf(dept);
此代码来自 Tim Hall的文章 https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
DEPTNO | DNAME | LOC | JSON_DOC |
---|---|---|---|
10 | ACCOUNTING | NEW YORK | {"DEPTNO":10, "DNAME":"ACCOUNTING", "LOC":"NEW YORK"} |
20 | RESEARCH | DALLAS | {"DEPTNO":20, "DNAME":"RESEARCH", "LOC":"DALLAS"} |
30 | SALES | CHICAGO | {"DEPTNO":30, "DNAME":"SALES", "LOC":"CHICAGO"} |
40 | OPERATIONS | BOSTON | {"DEPTNO":40, "DNAME":"OPERATIONS", "LOC":"BOSTON"} |
总结
有了以上几个案例,应该基本可以说明这个功能怎么使用了,而且我之前有个程序的缺陷(【AIO】使用ORACLE数据库存储过程把任意SQL生成HTML网页表格),无法自动对所有空值进行处理,也能通过此方法解决。
可惜的是,在百度上搜索 oracle +ptf 和 ORACLE+Polymorphic Table Functions ,几乎找不到一篇中国人写的文档,而且就算是在英文网站上,相关说明文档也不到一手之数。
甚至搜索“DBMS_TF”这个包名,都只有一份oracle的英文官方文档和ORACLE官方的在线sql演示页
希望我这篇文章能给各位带来一些新的启发。
fin
参考文章:
https://livesql.oracle.com/apex/livesql/file/content_GC53R14C0CVHJHUT3CUY7SN0F.html
https://oracle-base.com/articles/18c/polymorphic-table-functions-18c