oracle 数据库存储过程及任务调用
create or replacePROCEDURE PROCEDURE_STATISTIC_RESULT AS result varchar2(100);--执行结果 v_count integer := 0; v_rownum integer := 0; cursor sdyz_cursor is select w.shengdaoyuzhong,count(w.movie_id) as moviecount from v_programlayer_sdyz w , movie b where w.movie_id = b.movie_id group by w.shengdaoyuzhong; cursor zmyz_cursor is select w.zimuyuzhong,count(w.movie_id) as moviecount from v_programlayer_zmyz w , movie b where w.movie_id = b.movie_id group by w.zimuyuzhong;BEGIN /**--------------------------声道语种统计----------------------------**/ select count (1) into v_count from T_RESULT_STATISTIC t where t.resulttype = 'shengdaoyuzhong'; IF v_count > 0 THEN delete from T_RESULT_STATISTIC t where t.resulttype = 'shengdaoyuzhong'; END IF ; FOR sdyz IN sdyz_cursor LOOP insert into T_RESULT_STATISTIC (id,createdate,moviecount,yuzhong,resulttype) values (sys_guid(),sysdate,sdyz.moviecount,sdyz.shengdaoyuzhong,'shengdaoyuzhong'); v_rownum := v_rownum+1; if mod(v_rownum,500) = 0 then commit; end if; END LOOP ; /**-----------------------字幕语种统计-----------------------**/ select count(1) into v_count from T_RESULT_STATISTIC t where t.resulttype = 'zimuyuzhong'; IF v_count > 0 THEN delete from T_RESULT_STATISTIC t where t.resulttype = 'zimuyuzhong'; END IF ; v_rownum := 0; FOR zmyz IN zmyz_cursor LOOP insert into T_RESULT_STATISTIC (id,createdate,moviecount,yuzhong,resulttype) values (sys_guid(),sysdate,zmyz.moviecount,zmyz.zimuyuzhong,'zimuyuzhong'); v_rownum := v_rownum+1; if mod(v_rownum,500) = 0 then commit; end if; END LOOP ; if v_rownum >0 then commit; end if; result := 'success'; dbms_output.put_line(result); exception when others then result := 'error'; dbms_output.put_line(result);END PROCEDURE_STATISTIC_RESULT;
??
?
?
?
?
