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 /
