oracle 动态存储过程

1. 定时 

begin

sys.dbms_scheduler.create_job(job_name => 'job_pro_his_delete_cp',

job_type => 'STORED_PROCEDURE',

job_action => 'pro_his_delete_cp',

start_date => sysdate,

repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',

end_date => to_date(null),

enabled => true,

auto_drop => false,

comments => '历史经验删除by stationCode,每天1点');

end;

 

2. -----存储过程

-- DROP PROCEDURE if EXISTS pro_his_delete;/

CREATE OR REPLACE PROCEDURE pro_his_delete_cp AS

V_DELETE_HIS_COUNT VARCHAR2(10); --删除历史经验条数

V_DELETE_SEG_COUNT VARCHAR2(10); --删除分词历史经验条数

   V_STATIONCODE varchar2(10);

V_HISTABLENAME varchar2(50); --表名

V_SEGTABLENAME varchar2(50); --表名

    v_his_cnt_sql varchar2(500);

    v_his_del_sql varchar2(500);

    v_seg_cnt_sql varchar2(500);

    v_seg_del_sql varchar2(500);

    v_pro_udt_sql varchar2(500);

    

BEGIN

---循环

FOR HIS_DELETE IN (SELECT t.STATIONCODE,t.HISTABLENAME,t.SEGTABLENAME

FROM gisdata.T_PRO_HIS_DELETE t

WHERE t.ISDELETE = 'N'

                      )

LOOP

V_STATIONCODE := HIS_DELETE.STATIONCODE;

     V_HISTABLENAME := HIS_DELETE.HISTABLENAME;

     V_SEGTABLENAME := HIS_DELETE.SEGTABLENAME;

    

     v_his_cnt_sql :=' SELECT COUNT(*) FROM ' || V_HISTABLENAME|| ' WHERE STATIONCODE = ''' || V_STATIONCODE || '''';

execute immediate v_his_cnt_sql into V_DELETE_HIS_COUNT;

dbms_output.put_line(V_DELETE_HIS_COUNT);

 

     v_his_del_sql := 'DELETE FROM ' || V_HISTABLENAME || ' WHERE STATIONCODE = ''' || V_STATIONCODE ||'''';

     dbms_output.put_line(v_his_del_sql);

     execute immediate v_his_del_sql;

    

     v_seg_cnt_sql :=' SELECT COUNT(*) FROM ' || V_SEGTABLENAME|| ' WHERE BIZCODE = ''' || V_STATIONCODE || '''';

execute immediate v_seg_cnt_sql into V_DELETE_SEG_COUNT;

dbms_output.put_line(V_DELETE_SEG_COUNT);

    

v_seg_del_sql := 'DELETE FROM ' || V_SEGTABLENAME || ' WHERE BIZCODE = ''' || V_STATIONCODE ||'''';

     dbms_output.put_line(v_seg_del_sql);

     execute immediate v_seg_del_sql;

 

--   v_pro_udt_sql := 'UPDATE T_PRO_HIS_DELETE t SET t.MODIFYTIME = SYSDATE, t.ISDELETE = ''Y'', t.DELETEHISCOUNT = '''

--                      || V_DELETE_HIS_COUNT || ''', t.DELETESEGCOUNT = ''' || V_DELETE_SEG_COUNT ||''' WHERE t.STATIONCODE = '''

--                      || V_STATIONCODE ''' AND t.ISDELETE = ''N''';

v_pro_udt_sql := 'UPDATE T_PRO_HIS_DELETE t SET t.MODIFYTIME = SYSDATE, t.ISDELETE = ''Y'', t.DELETEHISCOUNT = '''

                      || V_DELETE_HIS_COUNT || ''', t.DELETESEGCOUNT = ''' || V_DELETE_SEG_COUNT ||''' WHERE t.ISDELETE = ''N'' and t.STATIONCODE = '''

                      || V_STATIONCODE || '''';

    dbms_output.put_line(v_pro_udt_sql);

    execute immediate v_pro_udt_sql;

COMMIT;

END LOOP;

 

--异常处理

EXCEPTION

WHEN others THEN

dbms_output.put_line(SQLCODE || '::'||SUBSTR(SQLERRM, 1, 200));

rollback;

END;

/

3.调用存储过程

-- 执行

begin

pro_his_delete_cp();

end;

-- 或 

call pro_his_delete_cp();

4.手动 调用job，不需等定时

 begin 
-- Call the procedure 
sys.dbms_scheduler.run_job(job_name => 'job_pro_his_delete_cp', 
            use_current_session => 'false'); 
end;