oracle函数的应用: 查询年到查询日的种类余额

同上篇所言，功能不变。用函数实现查当前年到查询日期的不同种类的余额和。

create or replace function web_Res_NC
( 
T_ST in varchar2 default null,
T_TM in varchar2
)
return t_table2
pipelined is  --这里原本是用了管道了的，但是其实不用应该也可以
L_RESULT obj_table2;
T_ST1 varchar2(20);  --函数里应该没有办法直接把传入的变量当条件，需要声明变量来接收。
--TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标
-- rowrecord ref_cursor_type;
-- v_cSql clob;
begin
        --首先判断一下是否传入这个变量，然后付给变量
       if T_ST is null
      then T_ST1 :=to_date(to_char(trunc(sysdate,'YYYY'),'yyyy-mm-dd'), 'yyyy-mm-dd');
      else
      T_ST1 :=to_date(T_ST,'yyyy-mm-dd');
      end if;
      for aa in (select 
           C_PROD_NO,  --险种编码
           C_SBJT_NO,  --科目类型
           C_DPTACC_NO,  --做账机构
           C_CUR_NO,   --币种
           N_AMT,  --余额
           T_DUE_TM, --凭证世间
           T_CRT_TM     --生成时间
          from (
                      SELECT h.urancecode C_PROD_NO,  --险种编码
                    b.accountcode C_SBJT_NO,  --科目类型
                    f.code C_DPTACC_NO,  --做账机构
                    w.name C_CUR_NO,   --币种
                     (case when b.accountcode in('6031') then sum(b.localcreditamountsum - b.localdebitamountsum)  when b.accountcode in('6541','6542','6421','651101','651105')  then sum(b.localdebitamountsum - b.localcreditamountsum) end ) as N_AMT,
                     to_char(to_date（T_TM,'yyyy-mm-dd'）,'yyyy-mm-dd') T_DUE_TM, --凭证时间   
                    to_char(sysdate , 'yyyy-mm-dd hh24:mi:ss') T_CRT_TM --生成时间
                    from (

                    select * from 
                    (select
                                                     gl_detail.pk_voucher pk_voucher,
                                                   --  gl_detail.accountcode accountcode,
                                                  (case when substr(gl_detail.accountcode,0,4) = '6031' then '6031'
                                                   when substr(gl_detail.accountcode,0,4)  = '6541' then '6541'
                                                   when substr(gl_detail.accountcode,0,4)  = '6542' then '6542'
                                                   when substr(gl_detail.accountcode,0,4)  = '6421' then '6421'
                                                   when substr(gl_detail.accountcode,0,6)  = '651101' then '651101'
                                                   when substr(gl_detail.accountcode,0,6)  = '651105' then '651105'
                                                   end
                                                   )as accountcode,
                                                     gl_detail.assid assid,
                                                     gl_detail.pk_currtype pk_currtype,
                                                     gl_detail.pk_accasoa pk_accasoa,

                                                     gl_detail.prepareddatev prepareddatev,
                                                     sum(gl_detail.debitamount) localdebitamountsum, -- 原币借方
                                                     sum(gl_detail.creditamount) localcreditamountsum --原币贷方

                                                      from gl_detail gl_detail
                                                     where  gl_detail.dr = 0
                                                    -- and gl_detail.accountcode in ('6031%','6541%','6542%','6421%','651101%','651105%')
                                                    and gl_detail.accountcode  like '6031%' or 
                                                    gl_detail.accountcode  like '6541%' or 
                                                    gl_detail.accountcode  like '6542%' or 
                                                    gl_detail.accountcode  like '6421%' or 
                                                    gl_detail.accountcode  like '651101%' or 
                                                    gl_detail.accountcode  like '651105%'

                                                     and gl_detail.adjustperiod <> to_char('12A')
                                                    group by
                                                              gl_detail.pk_voucher,
                                                              gl_detail.accountcode,
                                                              gl_detail.assid,
                                                             gl_detail.pk_currtype,
                                                              gl_detail.pk_accasoa,
                                                              gl_detail.prepareddatev)hh where    to_date(substr(hh.prepareddatev, 1, 10),'yyyy-mm-dd') >= T_ST1
                                                     and  to_date(substr(hh.prepareddatev, 1, 10),'yyyy-mm-dd') <=  to_date(T_TM, 'yyyy-mm-dd')



                                                              ) b,
                         (select s.urancecode urancecode,g.assid assid from ins_bd_urancekind s,(select l.F18  F18, l.assid assid from gl_docfree1 l) g where s.pk_urancekind in g.F18) h,
                         (select p.name name,p.pk_currtype pk_currtype from bd_currtype p) w,
                         (select code,pk_org from org_orgs)f,
                         (select pk_org,pk_voucher from gl_voucher)v
                    where
                          w.pk_currtype = b.pk_currtype
                          and h.assid = b.assid
                          and f.pk_org=v.pk_org
                          and b.pk_voucher = v.pk_voucher
                          group by h.urancecode,b.accountcode,w.name,f.code
                      ) aa
            )--查询语句是根本，先换一个值查出来，然后再用传入的参数进行替换
      LOOP
       L_RESULT := obj_table2(aa.c_prod_no,aa.C_SBJT_NO,aa.c_dptacc_no,aa.c_cur_no,aa.N_AMT,aa.T_DUE_TM,aa.T_CRT_TM); --这一点上是函数先前要设定类型，跟建表差不多，然后用来接收。
       PIPE ROW (L_RESULT); --依次返回行
     END LOOP;
     return;
     --CLOSE rowrecord;
   END;

下边是函数类型的声明：

/*创建类型*/
create or replace type obj_table2 as object
(
 C_PROD_NO  VARCHAR2(200),
 C_SBJT_NO  VARCHAR2(200),
 C_DPTACC_NO VARCHAR2(200),
 C_CUR_NO VARCHAR2(100),
 N_AMT   NUMBER(36,8),
 T_DUE_TM VARCHAR2(200),
 T_CRT_TM  VARCHAR2(200)
)

create or replace type t_table2 as table of obj_table2;

完结：中间还有一个问题是，当用了when case 之后，下边的那个时间限制竟然不管用了，不知道什么情况，然后只能把时间条件摘出来在嵌套一下。

不是dba所以写出来的sql执行效率和代码规范性都比较差，希望之后有所改进。