oracle一次统计脚本 

 1 DECLARE
 2 x varchar2(7);num varchar2(35);num2 varchar2(35);num3 varchar2(35);num4 varchar2(35); 
 3 num5 number;num6 number;num7 number;num8 number;  
 4 BEGIN
 5 
 6 ---------------------------------------Electricity Meter---------------------------------------------------------
 7 --Electricity Meter quantity
 8 SELECT COUNT(1) into num5 FROM DA_BJ;
 9 dbms_output.put_line('Electricity Meter quantity:'||num5);
10 
11 --Purchased Electricity Meter quantity
12 SELECT COUNT(DISTINCT D.BJJH) into num6 FROM DA_BJ D, ORDER_MASTER M WHERE D.BJJH = M.METERNO;
13 dbms_output.put_line('Purchased Electricity Meter quantity:'||num6);
14 
15 dbms_output.put_line('');
16 
17 --title
18 dbms_output.put_line(Lpad('Date',7,' ') || '|' || Lpad('Ele Meter purchase token Num',35,' ') || '|' || Lpad('Ele Meter management token Num',35,' '));  
19 dbms_output.put_line('--------------------------------------------------------------------------------');
20 
21 --loop begin
22 x:='2015-12';
23 WHILE x < to_char(sysdate, 'yyyy-mm')
24   LOOP
25   x:=to_char(add_months(to_date(x||'-01', 'yyyy-mm-dd'),1), 'yyyy-mm');
26 
27   --Number of Electricity Meter purchase token
28   SELECT Lpad(to_char(COUNT(t.token)),35,' ') into num FROM ORDER_TOKEN T, ORDER_MASTER M
29   WHERE T.ORDERSID = M.ORDERSID 
30   AND M.ORDER_TYPE IN ('01','23') 
31   AND M.ORDER_TYPE NOT IN ('15','16')
32   AND M.RES_TYPE = '03'
33   and to_char(M.op_time, 'yyyy-mm') = x;
34   --Number of Electricity Meter management token
35   SELECT Lpad(to_char(COUNT(t.token)),35,' ') into num2 FROM ORDER_TOKEN T, ORDER_MASTER M
36   WHERE T.ORDERSID = M.ORDERSID AND 
37   M.ORDER_TYPE NOT IN ('01','23','15','16')
38   AND M.RES_TYPE = '03'
39   and to_char(M.op_time, 'yyyy-mm') = x;
40 
41   dbms_output.put_line(Lpad(x,7,' ') || '|' || num || '|' || num2 || '|');
42   dbms_output.put_line('--------------------------------------------------------------------------------');
43 END LOOP;
44 --loop END
45 
46 
47 dbms_output.put_line('');
48 
49 ---------------------------------------Water Meter---------------------------------------------------------
50 --Water Meter quantity
51 SELECT COUNT(1) into num7 FROM DA_SB;
52 dbms_output.put_line('Water Meter quantity:'||num7);
53 
54 --Purchased Water Meter quantity
55 SELECT COUNT(DISTINCT D.SBJH) into num8 FROM DA_SB D, ORDER_MASTER M WHERE D.SBJH = M.METERNO;
56 dbms_output.put_line('Purchased Water Meter quantity:'||num8);
57 
58 dbms_output.put_line('');
59 
60 --title
61 dbms_output.put_line(Lpad('Date',7,' ') || '|' || Lpad('Water Meter purchase token Num',35,' ') || '|' || Lpad('Water Meter management token Num',35,' ') || '|');  
62 dbms_output.put_line('-------------------------------------------------------------------------------');
63 
64 --loop begin
65 x:='2015-12';
66 WHILE x < to_char(sysdate, 'yyyy-mm')
67   LOOP
68   x:=to_char(add_months(to_date(x||'-01', 'yyyy-mm-dd'),1), 'yyyy-mm');
69 
70   --Number of Water Meter purchase token
71   SELECT Lpad(to_char(COUNT(t.token)),35,' ') into num3 FROM ORDER_TOKEN T, ORDER_MASTER M
72   WHERE T.ORDERSID = M.ORDERSID 
73   AND M.ORDER_TYPE IN ('01','23') 
74   AND M.ORDER_TYPE NOT IN ('15','16')
75   AND M.RES_TYPE = '02'
76   and to_char(M.op_time, 'yyyy-mm') = x;
77   
78   --Number of Water Meter management token
79   SELECT Lpad(to_char(COUNT(t.token)),35,' ') into num4 FROM ORDER_TOKEN T, ORDER_MASTER M
80   WHERE T.ORDERSID = M.ORDERSID AND 
81   M.ORDER_TYPE NOT IN ('01','23','15','16')
82   AND M.RES_TYPE = '02'
83   and to_char(M.op_time, 'yyyy-mm') = x;
84 
85   dbms_output.put_line(Lpad(x,7,' ') || '|' || num3 || '|' || num4 || '|');
86   dbms_output.put_line('--------------------------------------------------------------------------------');
87 END LOOP;
88 --loop END
89 
90 END;
91 /

 

posted @ 2017-07-27 14:19  tvxqpurpleline  阅读(275)  评论(0)    收藏  举报