Oracle自定义函数实现动态参数复制表（使用了自定义type以及pipelined）

╰半橙微兮° 2022-05-10 10:06 393阅读 0赞

**（作者：陈玓玏）**  
之前试了一下，想用自定义函数及游标实现动态传入参数，确实可以，但是输出结果总是不能成表格。

查了一圈，Oracle自定义函数好像是不能直接在SQL语句中写create as select和insert into这些功能的，但是后来的版本中提供了一个叫pipelined 的功能，但是需要自定义类。下面把我跑通了的过程写出来。

--返回多行值且是表格的形式，但是在执行时需要用create as或insert into才能真正写入表格
        --type必须按照生成顺序从后往前删除，否则会报错，所以最后使用到type的表或类型要最先删除
        drop table risk_temp.cdl_tb1;
        drop function fun1;
        drop type table_type1;
        drop type row_type1;
        
        --先创建表结构
        create table risk_temp.cdl_tb1(col1 number, col2 varchar2(10)); 
        --下面这些往创建的表里插入的数据的代码可以不要的
        insert into risk_temp.cdl_tb1(col1, col2) values(100,'aaa'); 
        insert into risk_temp.cdl_tb1(col1, col2) values(200,'bbb'); 
        insert into risk_temp.cdl_tb1(col1, col2) values(200,'ccc'); 
        select * from risk_temp.cdl_tb1;
    
        --创建类型及表类型
        create type row_type1 as object(col1 number, col2 varchar2(10)); 
        create type table_type1 as table of row_type1;
        
        --创建函数以表格形式逐行输出，表格形式就是之前定义的表类型
        create or replace function fun1(name VARCHAR2) return table_type1 pipelined as 
        tempvar row_type1; 
        begin 
        for myrow in (select col1,col2 from tableName where id=name) loop 
        tempvar := row_type1(myrow.col1, myrow.col2); 
        pipe row (tempvar); 
        end loop; 
        return; 
        end;
        
        --执行。如果这里不insert into或者create as，那么之前创建的表不会有任何结果，只是显示的结果是表形式的，但实际并没有插    入到表中
        insert into risk_temp.cdl_tb1 select * from table(fun1(name=>1445903));

关于pipelined和type的具体的更高级的用法，以后再探索吧~