Oracle存储过程

示例代码：

create or replace procedure p_fmapp_dau(var_tm date, var_plat varchar2) as
       var_dau number;
       var_yesterday_dau number;
       status number;
begin
       --dbms_output.put_line('out:'||var_plat);
       select dau into var_dau from f_fmapp_overall where tm = var_tm and plat = var_plat;
       select dau into var_yesterday_dau from f_fmapp_overall where tm = var_tm-1 and plat = var_plat;
       delete from f_fmapp_trend_dau where tm = var_tm and plat = var_plat;

       if var_dau-var_yesterday_dau >= 0 then
          status := 1;
       else
          status := -1;
       end if;

       insert into f_fmapp_trend_dau values(var_dau, var_plat, var_dau-var_yesterday_dau, var_tm, status);
       --execute immediate 'insert into fmapp_overall_trend values(:1, :2, :3, :4)' using var_dau, var_plat, var_dau-var_yesterday_dau, var_tm;
       commit;
       --dbms_output.put_line('var_dau:'||var_dau);
       --dbms_output.put_line('var_yesterday_dau:'||var_yesterday_dau);
end;

遍历游标操作：

create or replace procedure p_fmapp_chs_remain(var_tm date) as
       
       var_return number;
       var_total number;
       var_date date;
       var_pub varchar2(512);
       var_pub_name varchar2(512);
       var_pubid varchar2(512);
       CURSOR var_cur_today is
              select tm, sum(newcomer), pub_name, pub, pubid from  f_fmapp_pub_remain_new where tm = var_tm group by pubid, tm, pub_name, pub, pubid;
       CURSOR var_cur_2 is
              select tm, sum(newcomer), trunc(sum(newcomer*two/100)), pub_name, pub, pubid from f_fmapp_pub_remain_new where tm = var_tm - 1 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_3 is
              select tm, sum(newcomer), trunc(sum(newcomer*three/100)), pub_name, pub, pubid from f_fmapp_pub_remain_new where tm = var_tm - 3 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_7 is
              select tm, sum(newcomer), trunc(sum(newcomer*seven/100)), pub_name, pub, pubid from f_fmapp_pub_remain_new where tm = var_tm - 7 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_15 is
              select tm, sum(newcomer), trunc(sum(newcomer*fifteen/100)), pub_name, pub, pubid from f_fmapp_pub_remain_new where tm = var_tm - 15 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_30 is
              select tm, sum(newcomer), trunc(sum(newcomer*thirty/100)), pub_name, pub, pubid from f_fmapp_pub_remain_new where tm = var_tm - 30 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_90 is
              select tm, sum(newcomer), trunc(sum(newcomer*ninety/100)), pub_name, pub, pubid from f_fmapp_pub_remain_new where tm = var_tm - 90 group by pubid, tm, pub_name, pub;
begin
       
       delete from f_fm_chs_old@appchs_link where day = var_tm;
       update f_fm_chs_old@appchs_link set one = null where day = var_tm - 1;
       update f_fm_chs_old@appchs_link set three = null where day = var_tm - 3;
       update f_fm_chs_old@appchs_link set seven = null where day = var_tm - 7;
       update f_fm_chs_old@appchs_link set fifteen = null where day = var_tm - 15;
       update f_fm_chs_old@appchs_link set thirty = null where day = var_tm - 30;
       update f_fm_chs_old@appchs_link set ninety = null where day = var_tm - 90;

       open var_cur_today;
       loop
            fetch var_cur_today into var_date, var_total, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_today%NOTFOUND;
            insert into f_fm_chs_old@appchs_link values(var_pubid, var_total, null, null, null, null, var_tm, null /*存量比*/, 0, null /*安装量*/, null, null);
       end loop;
       close var_cur_today;

       open var_cur_2;
       loop
            fetch var_cur_2 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_2%NOTFOUND;
            update f_fm_chs_old@appchs_link set one = var_return where day = var_tm - 1 and chs = var_pubid;
       end loop;
       close var_cur_2;

       open var_cur_3;
       loop
            fetch var_cur_3 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_3%NOTFOUND;
            update f_fm_chs_old@appchs_link set three = var_return where day = var_tm - 3 and chs = var_pubid;
       end loop;
       close var_cur_3;

       open var_cur_7;
       loop
            fetch var_cur_7 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_7%NOTFOUND;
            update f_fm_chs_old@appchs_link set seven = var_return where day = var_tm - 7 and chs = var_pubid;
       end loop;
       close var_cur_7;

       open var_cur_15;
       loop
            fetch var_cur_15 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_15%NOTFOUND;
            update f_fm_chs_old@appchs_link set fifteen = var_return where day = var_tm - 15 and chs = var_pubid;
       end loop;
       close var_cur_15;

       open var_cur_30;
       loop
            fetch var_cur_30 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_30%NOTFOUND;
            update f_fm_chs_old@appchs_link set thirty = var_return where day = var_tm - 30 and chs = var_pubid;
       end loop;
       close var_cur_30;

       open var_cur_90;
       loop
            fetch var_cur_90 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_90%NOTFOUND;
            update f_fm_chs_old@appchs_link set ninety = var_return where day = var_tm - 90 and chs = var_pubid;
       end loop;
       close var_cur_90;      
       commit;
end;

跨数据库的数据需要新建DateLink

create or replace procedure p_fmapp_chs_remain_t(var_tm date) as
       
       var_return number;
       var_total number;
       var_date date;
       var_pub varchar2(512);
       var_pub_name varchar2(512);
       var_pubid varchar2(512);
       CURSOR var_cur_today is
              select tm, sum(newcomer), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm group by pubid, tm, pub_name, pub;
       CURSOR var_cur_2 is
              select tm, sum(newcomer), sum(two), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm - 1 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_3 is
              select tm, sum(newcomer), sum(three), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm - 3 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_7 is
              select tm, sum(newcomer), sum(seven), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm - 7 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_15 is
              select tm, sum(newcomer), sum(fifteen), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm - 15 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_30 is
              select tm, sum(newcomer), sum(thirty), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm - 30 group by pubid, tm, pub_name, pub;
       CURSOR var_cur_90 is
              select tm, sum(newcomer), sum(ninety), pub_name, pub, pubid from f_fmapp_pub_remain_new_appchs where tm = var_tm - 90 group by pubid, tm, pub_name, pub;
begin
       
       delete from f_fm_chs@appchs_link where day = var_tm;
       update f_fm_chs@appchs_link set one = null where day = var_tm - 1;
       update f_fm_chs@appchs_link set three = null where day = var_tm - 3;
       update f_fm_chs@appchs_link set seven = null where day = var_tm - 7;
       update f_fm_chs@appchs_link set fifteen = null where day = var_tm - 15;
       update f_fm_chs@appchs_link set thirty = null where day = var_tm - 30;
       update f_fm_chs@appchs_link set ninety = null where day = var_tm - 90;

       open var_cur_today;
       loop
            fetch var_cur_today into var_date, var_total, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_today%NOTFOUND;
            insert into f_fm_chs@appchs_link values(var_pubid, var_total, null, null, null, null, var_tm, null /*存量比*/, 0, null /*安装量*/, null, null);
       end loop;
       close var_cur_today;

       open var_cur_2;
       loop
            fetch var_cur_2 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_2%NOTFOUND;
            update f_fm_chs@appchs_link set one = var_return where day = var_tm - 1 and chs = var_pubid;
       end loop;
       close var_cur_2;

       open var_cur_3;
       loop
            fetch var_cur_3 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_3%NOTFOUND;
            update f_fm_chs@appchs_link set three = var_return where day = var_tm - 3 and chs = var_pubid;
       end loop;
       close var_cur_3;

       open var_cur_7;
       loop
            fetch var_cur_7 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_7%NOTFOUND;
            update f_fm_chs@appchs_link set seven = var_return where day = var_tm - 7 and chs = var_pubid;
       end loop;
       close var_cur_7;

       open var_cur_15;
       loop
            fetch var_cur_15 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_15%NOTFOUND;
            update f_fm_chs@appchs_link set fifteen = var_return where day = var_tm - 15 and chs = var_pubid;
       end loop;
       close var_cur_15;

       open var_cur_30;
       loop
            fetch var_cur_30 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_30%NOTFOUND;
            update f_fm_chs@appchs_link set thirty = var_return where day = var_tm - 30 and chs = var_pubid;
       end loop;
       close var_cur_30;

       open var_cur_90;
       loop
            fetch var_cur_90 into var_date, var_total, var_return, var_pub_name, var_pub, var_pubid;
            EXIT WHEN var_cur_90%NOTFOUND;
            update f_fm_chs@appchs_link set ninety = var_return where day = var_tm - 90 and chs = var_pubid;
       end loop;
       close var_cur_90;      
       commit;
end;

View Code

select查询出的数据需要保存在临时变量中：

create or replace procedure p_fmapp_openway(var_tm date, var_plat varchar2) as
       var_today_dir number;
       var_yesterday_dir number;
       var_today_push number;
       var_yesterday_push number;
       status number;
begin
       -- dir
       select diruv into var_today_dir from f_fmapp_openway_mos where tm = var_tm and plat = var_plat and mos = 'all';
       select diruv into var_yesterday_dir from f_fmapp_openway_mos where tm = var_tm-1 and plat = var_plat and mos = 'all';
       if var_today_dir-var_yesterday_dir >= 0 then
          status := 1;
       else
          status := -1;
       end if;
       delete from f_fmapp_trend_openway where tm = var_tm and plat = var_plat and type = 'direct';
       insert into f_fmapp_trend_openway values(var_today_dir, var_plat, var_today_dir-var_yesterday_dir, var_tm, status, 'direct');

       -- push
       select puuv into var_today_push from f_fmapp_openway_mos where tm = var_tm and plat = var_plat and mos = 'all';
       select puuv into var_yesterday_push from f_fmapp_openway_mos where tm = var_tm-1 and plat = var_plat and mos = 'all';
       if var_today_push-var_yesterday_push >= 0 then
          status := 1;
       else
          status := -1;
       end if;
       delete from f_fmapp_trend_openway where tm = var_tm and plat = var_plat and type = 'push';
       insert into f_fmapp_trend_openway values(var_today_push, var_plat, var_today_push-var_yesterday_push, var_tm, status, 'push');

       commit;
end

View Code

存储过程的调用：

import db
import time
import datetime
import sys
import cx_Oracle

proc_list = ["p_fmapp_cv",
"p_fmapp_dau",
"p_fmapp_newcomer",
"p_fmapp_newcomer_pub",
"p_fmapp_oldcomer",
"p_fmapp_open",
"p_fmapp_openway",
"p_fmapp_pushnum",
"p_fmapp_pushopen_pv",
"p_fmapp_pushpv_per",
"p_fmapp_pushuv_per",
"p_fmapp_vv"]

pub_p = ["p_fmapp_chs_remain"]

# proc_list = ["p_fmapp_pushuv_per"]

plats = ["android", "ios", "all"]

def call_procedure(num):
    dayStr = time.strftime('%Y-%m-%d', time.localtime(time.time()-86400*num))
    today = datetime.datetime.strptime(dayStr, "%Y-%m-%d").date()
    con, cur = db.open_app()
    # l_cur = cur.var(cx_Oracle.CURSOR)
    for proc in proc_list:
        for plat in plats:
            cur.callproc(proc, (today, plat))
    # for proc in pub_p:
    #     cur.callproc(proc, (today,))

def call_pub_remain(num):
    dayStr = time.strftime('%Y-%m-%d', time.localtime(time.time()-86400*num))
    today = datetime.datetime.strptime(dayStr, "%Y-%m-%d").date()
    con, cur = db.open_app()
    print "call_pub_remain"
    # l_cur = cur.var(cx_Oracle.CURSOR)
    for proc in pub_p:
        cur.callproc(proc, (today,))

def update_fmchs(num):
    dayStr = time.strftime('%Y-%m-%d', time.localtime(time.time()-86400*num))
    today = datetime.datetime.strptime(dayStr, "%Y-%m-%d").date()
    con, cur = db.open_app()
    sql = "select * from appchs.d_fm_chs"
    cur.execute(sql)
    dic = {}
    print "update_fmchs"
    for items in cur.fetchall():
        id = items[0]
        de_rule = items[4]
        if not de_rule:
            continue
        try:
            per = int(de_rule.split(";")[0].split(",")[-1])/float(100)
        except:
            import traceback
            print traceback.print_exc()
            continue
        dic.setdefault(id, per)
    for key in dic:
        id = key
        per = dic[id]
        sql = "update appchs.f_fm_chs set de_per = %.4f, install_de = install*(1-%.4f) where chs = '%s' and day = to_date('%s', 'yyyy-mm-dd')" % (per, per, id, dayStr)
        cur.execute(sql)
    con.commit()
    con.close()

if __name__ == "__main__":
    if "test" in sys.argv:
        call_pub_remain(1)
        update_fmchs(1)
    if 'daily' in sys.argv:
        call_procedure(1)
        call_pub_remain(1)
        update_fmchs(1)
    if "store" in sys.argv:
        for i in range(1, 9):
            call_pub_remain(9-i)
            update_fmchs(9-i)

View Code

posted on 2015-04-01 14:05 闪电战阅读(121) 评论(0) 收藏举报