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)
View Code

 

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

 1 case var
 2   when 条件1 then
 3     if 条件1_1 then
 4       return ****;
 5     end if;
 6     reutrn ****;
 7   when 条件2 then
 8     if 条件2_1 then
 9       return ****;
10     end if;
11     reutrn ****;
12   else
13     return 9999;
14 end case;
Case...When

 

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

1     SELECT Accountno
2       INTO OUTACCOUNTNO
3       FROM (SELECT Accountno,
4                    AccountName,
5                    ROW_NUMBER() OVER(ORDER BY OPTIME DESC) ROWPOS
6               FROM Pub_Agt_Allinpay_Reg_Info
7              WHERE AGTPHONE = InAgtphone
8                AND STATE = '2')
9      WHERE ROWPOS = 1;
排序去重

 

8. 连表更新

1 UPDATE table_1 a
2    SET col_x1 = (SELECT b.col_y1 FROM table_2 b WHERE b.col_n = a.col_m),
3        col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
4 WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m)
写法一
1 UPDATE table_1 a
2    SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
3 WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);
写法二

 

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

 1   insert into PUB_SHOP_INFO
 2     (MOBILE, -- 1
 3      REAL_NAME, -- 2
 4      ID_CARD, -- 3
 5      SHOP_ADDR, -- 4
 6      BELONG_TO)
 7     select t.agtphone, -- 1
 8            t.agtname, -- 2
 9            t.agtidcard, -- 3
10            nvl(t.agtofficeaddr, t.agthomeaddr), --4
11            t.agtbelong -- 5
12       from pub_agt_info_s t;
选定字段复制

 

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

 1 select to_char((trunc(sysdate) +
 2                trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
 3                'hh24:mi') || '-' ||
 4        to_char((trunc(sysdate) +
 5                trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
 6                'hh24:mi') period,
 7        count(*) 订单笔数,
 8        sum(o.order_fee) 订单总面值,
 9        sum(o.success_fee) 成功总面值
10   from bp_platform_order o
11  where o.request_time >= to_date('20150119', 'yyyymmdd')
12    and o.request_time < to_date('20150119', 'yyyymmdd') + 1
13  group by to_char((trunc(sysdate) +
14                   trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
15                   'hh24:mi') || '-' ||
16           to_char((trunc(sysdate) +
17                   trunc((o.request_time - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
18                   'hh24:mi')
19  order by 1;
按时间段进行分组统计

 

 

 

 

posted @ 2014-03-10 11:37  流失的痕迹  阅读(241)  评论(0编辑  收藏  举报