杂

1. 查看所有job

select job,what,failures,broken from user_jobs  

   查看所有存储过程

select * from user_objects where object_type='PROCEDURE'

2. 为统计增加合计列

select o.platform_order_id,sum(o.system_cost),sum(o.platform_cost),sum(o.spreader_cost),sum(o.shop_cost) from 
bp_platform_order o 
where o.order_status=0
and o.business_type='G3' 
group by rollup(o.platform_order_id)

 

3. 将一列数据用 "," 拼接起来

select wm_concat(t.shop_id) from shop_info t where t.xx=11691

 

4. 枚举加权值

if (bitand(v_total_steps, e_apply_step.need_extradata)+0 = 0)

if (bitand(v_finished_steps, e_apply_step.need_extradata)+0=e_apply_step.need_extradata)

 

5. SQL%ROWCOUNT!=1

在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功或者没有操作(如update、insert、delete为0条），SQL%ROWCOUNT的值为0

 

6. case... when

case var
　　when 条件1 then
　　　　if 条件1_1 then
　　　　　　return ****;
　　　　end if;
　　　　reutrn ****;
　　when 条件2 then
　　　　if 条件2_1 then
　　　　　　return ****;
　　　　end if;
　　　　reutrn ****;
　　else
　　　　return 9999;
end case;

 

7. 多条重复的记录，仅取出按需排序的某一条

    SELECT Accountno
      INTO OUTACCOUNTNO
      FROM (SELECT Accountno,
                   AccountName,
                   ROW_NUMBER() OVER(ORDER BY OPTIME DESC) ROWPOS
              FROM Pub_Agt_Allinpay_Reg_Info
             WHERE AGTPHONE = InAgtphone
               AND STATE = '2')
     WHERE ROWPOS = 1;

 

8. 连表更新

UPDATE table_1 a
   SET col_x1 = (SELECT b.col_y1 FROM table_2 b WHERE b.col_n = a.col_m),
       col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m)

UPDATE table_1 a
   SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);

 

9. 从某表复制数据到另一个表

  insert into PUB_SHOP_INFO
    (MOBILE, -- 1
     REAL_NAME, -- 2
     ID_CARD, -- 3
     SHOP_ADDR, -- 4
     BELONG_TO)
    select t.agtphone, -- 1
           t.agtname, -- 2
           t.agtidcard, -- 3
           nvl(t.agtofficeaddr, t.agthomeaddr), --4
           t.agtbelong -- 5
      from pub_agt_info_s t;

 

10. 按某一个时间段进行分组统计

select to_char((trunc(sysdate) +
               trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
               'hh24:mi') || '-' ||
       to_char((trunc(sysdate) +
               trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
               'hh24:mi') period,
       count(*) 订单笔数,
       sum(o.order_fee) 订单总面值,
       sum(o.success_fee) 成功总面值
  from bp_platform_order o
 where o.request_time >= to_date('20150119', 'yyyymmdd')
   and o.request_time < to_date('20150119', 'yyyymmdd') + 1
 group by to_char((trunc(sysdate) +
                  trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
                  'hh24:mi') || '-' ||
          to_char((trunc(sysdate) +
                  trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
                  'hh24:mi')
 order by 1;