[应用]试用oracle的function

近日写了一些vew,用到了自定义funciton,都是多表连接查询,也没有优化,留个记录以备后查;

sql 代码

 

1. -- create view report_service  
2. create or replace view report_service as  
3.   select UPPER(trim(to_char(c.aidcategoryid, '0x')) ||  
4.              trim(to_char(t.aidserviceid, '0xxx'))) ||  
5.        RPAD(t.servicename, 40) || RPAD(b.id, 2) ||  
6.        RPAD(decode(y.id, 1, 3, 2, 2, 3, 1) || l.userlevelid, 5) || '1' ||  
7.        RPAD(' ', 12) Service  
8.   from co2servicelist  t,  
9.        co2categorylist c,  
10.        co2group        g,  
11.        co2brand        b,  
12.        co2grouplevel   l,  
13.        co2grouptype    y  
14.  where t.categorylistid = c.id  
15.    and c.groupid = g.id  
16.    and g.id = b.groupid  
17.    and g.id = l.groupid  
18.    and g.grouptypeid = y.id;  
19.   
20.   
21. --create view report_molog  
22. create or replace view report_molog as  
23. select Rpad(t.msisdn, 14) || trim(decode(a.id, 1, 0, 3, 2, 4, 1))  ||  
24.        Rpad(nvl(d.servicelistname, ' '), 40) ||  
25.        trim(Rpad(to_char(t.creationdate, 'YYYY-MM-DD HH:MI:SS'), 19)) molog  
26.   from co2transactionlog t, co2transactionlogdetail d, co2actiontype a  
27.  where t.id = d.transactionlogid  
28.    and d.actiontypeid = a.id  
29.    and t.originid = 1  
30.    and a.id in (1, 3, 4);  
31.   
32.   
33. create or replace view report_mtlog as  
34.  select Rpad(t.msisdn, 14) || trim(decode(a.id, 1, 0, 3, 2, 4, 1))  || Rpad(nvl(d.servicelistname, ' '), 40) ||  
35.        trim(Rpad(to_char(t.creationdate, 'YYYY-MM-DD HH:MI:SS'), 19)) ||  
36.        decode(t.erroroccured, 'N', '0', 'Y', '1') mtlog  
37.   from co2transactionlog       t,  
38.        co2transactionlogdetail d,  
39.        co2actiontype           a,  
40.        co2origin               b  
41.  where t.id = d.transactionlogid  
42.    and d.actiontypeid = a.id  
43.    and t.directionid = 2  
44.    and b.id = t.originid  
45.    and a.id in (1, 3, 4)  ;  
46.   
47. -- create view for user  
48. create or replace view REPORT_USER as  
49.   select RPAD(t.msisdn, 14) || RPAD(b.id, 2) ||  
50.        RPAD(decode(p.id, 1, 3, 2, 2, 3, 1) || l.userlevelid, 5) ||  
51.        RPAD(t.imsi, 16) || RPAD(c.cardproviderid, 2) ||  
52.        RPAD(c.protocolversionid, 3) || Rpad(c.cardbatchid, 2) ||  
53.        RPAD(nvl(query_user_menu(c.id),' '), 500) userinfo  
54.   from co2user       t,  
55.        co2card       c,  
56.        co2grouplevel l,  
57.        co2group      g,  
58.        co2grouptype  p,  
59.        co2brand      b  
60.  where t.cardid = c.id  
61.    and c.grouplevelid = l.id  
62.    and l.groupid = g.id  
63.    and g.id = b.groupid  
64.    and g.grouptypeid = p.id;  
65.     
66. --create function for concat AID string  
67. create or replace function query_user_menu(id in varchar2) return varchar2 is  
68.   Result varchar2(500);  
69.   c_id   varchar2(500);  
70.   a_id   number;  
71.   cursor region_cur is  
72.     select t.servicelistid, t.profilestate, t.isopen  
73.       from co2currentserviceprofile t  
74.      where t.cardid = id;  
75. begin  
76.   
77.   for my_cur in region_cur loop  
78.     select t.aidserviceid, UPPER(trim(to_char(c.aidcategoryid, '0x')))  
79.       into a_id, c_id  
80.       from co2servicelist t, co2categorylist c  
81.      where my_cur.servicelistid = t.id  
82.        and t.categorylistid = c.id;  
83.     if c_id is not null and a_id is not null then  
84.       if my_cur.profilestate = 'Y' then  
85.         a_id := a_id + 32768;  
86.       end if;  
87.       if my_cur.isopen = 'Y' then  
88.         a_id := a_id + 1024;  
89.       end if;  
90.       a_id   := a_id + 16384;  
91.       Result := CONCAT(Result, c_id);  
92.       Result := concat(Result, UPPER(trim(to_char(a_id, '0xxx'))));  
93.       Result := concat(Result, '|');  
94.     end if;  
95.   end loop;  
96.   
97.   return(Result);  
98. end query_user_menu;  
99. /  
100.