Oracle sql和过程的总结

1、  递归

start with ...connect by 的用法， start with 后面所跟的就是就是递归的种子。

递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；

connect by prior 后面所放的字段是有关系的，它指明了查询的方向。没有start with 也是可以的

在实际中又发现了没有具体某一个数据作为开始节点，整个表都想作为开始节点，结果很悲剧，出来的并不是自己想要的结果。

总结了下，这个递归语句只能根据具体的某一个start with 具体值   返回的是具有关联关系的记录。

针对这种需求，可以这样实现，写一个过程入参为“start with 后面跟的具体值”    出参就是递归后的结果。示例如下：

create or replace function sp_get_child_team(p_vc_team_no IN HsCrmType.VC20%Type)
  RETURN type_constraintpdt  /*CREATE OR REPLACE TYPE type_constraintpdt IS TABLE OF NUMBER (10);*/
  PIPELINED as
  var_vc_team_no   HsCrmType.VC20%Type;
  v_vc_team_no     HsCrmType.VC20%Type;
  v_cursor         HsCrmType.t_cursor;

begin
  v_vc_team_no := nvl(p_vc_team_no,' ');

  open v_cursor for
  select b.l_serial_no from brokerteam b
    connect by prior b.l_serial_no = b.vc_up_team_no
        start with b.l_serial_no = v_vc_team_no;   --需要获取子团队的team

  loop
    fetch v_cursor
      into var_vc_team_no;
    exit when v_cursor%NOTFOUND;
    PIPE ROW(var_vc_team_no);
  end loop;
  close v_cursor;

end sp_get_child_team;

然后就可以直接这样了：

select b.l_serial_no as vc_team_no,t.column_value from
                                  brokerteam b, table(sp_get_child_team(b.l_serial_no)) t

这样就可以得到brokerteam表的所有递归记录了。

2、  Oracle分析函数

2.1、sum(en_business_balance) over(partition by vc_operator_no,vc_customer_no order by l_oc_date range unbounded preceding)en_business_balance

该分析函数的算法是根据l_oc_date排序，按照vc_operator_no,vc_customer_no 分组，依次增加，比如第一天的business是1，第二天的也是1，用此分析函数的话第一天是1，第二天等于第一天加第二天的business，也就是2，很实用，这里主要是算一段时间内的交易总额什么时候超过了标准，所以需要依次算出来当前时间加前段时间的business。

2.2、RANK() OVER(PARTITION BY ''1'' ORDER BY sum(rn.en_stock_asset)DESC) as eb_sort

该分析函数的算法是根据某一分组，按照sum（en_stock_asset）进行排序，得出来排名。

比如结果如下：

sum               sort

          10                    1

          10                    1

           9                     3

           8                     4

3、季度、周

 季度：select to_char(to_date('20110101','yyyyMMdd'),'yyyyq') from dual;

  周   ：select to_char(to_date('20110101','yyyyMMdd'),'yyyyMMw') from dual;

/* oracle中的周不是按照周一到周日，而是单纯的按照7天，比如9月1号是周四，那2011091（9月第一周）就是包括20110901到20110907也就是下周三。月末不够7天也算是一星期，从1号算作每月第一周的开始*/