oracle自定义函数 for in loop示例

r囧r小猫 2022-03-21 07:54 707阅读 0赞

1、新建type，就是返回结果集有什么，这里就写什么（相当于表的字段）

CREATE OR REPLACE TYPE "TYPE_NQ_FORM_STATISTICS" as object
    (
      recordid   varchar2(500),
      form_name varchar2(200),
      sortone varchar2(100),
      sorttwo varchar2(100),
      formids varchar2(500),
      formidm  varchar2(500),
      reason  varchar2(500),
      check_date  varchar2(100),
      check_userid  varchar2(100),
      check_ward   varchar2(100),
      ward_name  varchar2(100),
      dept       varchar2(100)
    )

2、新建table，（相当于把上部分的查询的字段保存到这张表中）

CREATE OR REPLACE TYPE "TYPE_NQ_FORM_TABLE" is table of TYPE_NQ_FORM_STATISTICS

3、创建自定义函数

CREATE OR REPLACE FUNCTION "MINI_FUN_NQ_FORM_STATISTICS"--我写得是无参函数，所以这里没有传参
      return TYPE_NQ_FORM_TABLE --返回结果集table
      pipelined is    
      recordid varchar2(200);    --定义变量
      formids  varchar2(200);
      formidm  varchar2(200);
      rwodata  TYPE_NQ_FORM_STATISTICS; --查询的结果集
    begin
      for item in (select t.record_id, m.form_id    --循环1（item ）
                     from T_NQ_FORM_RECORD_COLUMN t
                    inner join T_NQ_FORM_COLUMN m
                       on t.column_id = m.column_id
                      and t.column_value = '1') loop --for in lop循环，查询一条循环一次
        recordid := item.record_id; --给变量赋值
        formids  := item.form_id;
      
        for itemo in (select o.column_content --循环2（itemo ）
                        from T_NQ_FORM_COLUMN o
                       where o.form_id = formids --这里是根据循环1的每一个formids来循环
                         and o.td_index = 1) loop
          for itemt in (select p.*,               --循环3（itemt ）
                               (select t.ward_name
                                  from T_HRM_WARD t
                                 where t.ward_id = p.checked_ward_id) as ward_name
                          from T_NQ_FORM_RECORD p
                         where p.record_id = recordid--这里是根据循环1的每一个recordid来循环
                           and p.state = 2) loop
            formidm := itemt.form_id;
            for itemf in (select t.form_name, t.sortone, t.sorttwo  --循环4（itemf ）
                            from T_NQ_FORM t
                           where t.form_id = formidm--这里是根据循环3的每一个formidm来循环
                             and t.del_flag = 0) loop
            
              rwodata := TYPE_NQ_FORM_STATISTICS(itemt.record_id,
                                                 itemf.form_name,
                                                 itemf.sortone,
                                                 itemf.sorttwo,
                                                 item.form_id,
                                                 itemt.form_id,
                                                 itemo.column_content,
                                                 itemt.check_date,
                                                 itemt.check_user_id,
                                                 itemt.checked_ward_id,
                                                 itemt.ward_name,
                                                 itemt.dept_id);--查询想返回的所有结果
    
              pipe row(rwodata);--添加结果集
            end loop; --结束循环，我这里用了4个循环
          end loop;
        end loop;
      end loop;
    end MINI_FUN_NQ_FORM_STATISTICS;

4、调用函数

select * from   table(MINI_FUN_NQ_FORM_STATISTICS)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTIxNDMx_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0NTIxNDMx_size_16_color_FFFFFF_t_70]: /images/20220321/8c9a297ed5714a59ae4788606b541d7b.png