项目中用到的一个Oracle存储过程实例

--存储过程说明:一个在项目中使用的例子,作用:
根据时间字符串删除本次比对结果中重复的结果数据,遍历源数据分别统计每条源数据对应的结果数和未查看结果数将数据更新到源表中,最后统计当前实例的总的结果数和未查看结果数,如果有新结果则报警并将统计出的数据更新到实例表中

create or replace procedure p_countCompareInstanceResult(
COMPARE_ID in number, INSTANCE_ID in number,COMPARE_TIME_STR in varchar2) is
pkField varchar2(100);--目标字段中的主键字段名
resultTable varchar2(100);--结果表
sourceTable varchar2(100);--源表
currentSourceId number(22);--当前源数据ID
currentResultNumber number(22);--当前源数据对应的结果数
currentNotCheckNumber number(22);--当前源数据对应的未查看结果数
resultNumber_before number(22);--实例执行前的结果数
resultNumber_after number(22);--实例执行后的结果数
notCheckNumber number(22);--实例未查看结果数
v_sql varchar2(4000);--SQL字符串
type c_sourcedata is ref cursor;
c_sd c_sourcedata;--游标:遍历源数据,统计源数据对应的结果条数并更新源表的RESULT$_NUMBER字段
begin
resultTable:='R_'||COMPARE_ID;--结果表
sourceTable:='S_'||COMPARE_ID;--源表
select t.field_name into pkField from t_compare_field t where t.is_unique='1' and t.compare_id=COMPARE_ID and rownum=1;--得到主键字段名
v_sql:='delete from '||resultTable||--删除重复结果数据
' r1 where r1.COMPARE$_TIME=to_date('''||
COMPARE_TIME_STR||''',''yyyy-mm-dd hh24:mi:ss'') and r1.'||pkField||
' in (select r2.'||pkField||' from '||resultTable||
' r2 where r2.COMPARE$_TIME<>to_date('''||COMPARE_TIME_STR||
''',''yyyy-mm-dd hh24:mi:ss'')and r2.INSTANCE$_ID='||INSTANCE_ID||
')and r1.INSTANCE$_ID='||INSTANCE_ID;
execute immediate v_sql;
commit;
open c_sd for --遍历实例对应的源数据,重新统计源数据对应的结果条数并更新源表的字段RESULT$_NUMBER
'select SOURCE$_ID from '||sourceTable||
' s where s.INSTANCE$_ID='||INSTANCE_ID;
loop
fetch c_sd into currentSourceId;
v_sql:='select count(RESULT$_ID) from '||
resultTable||' r where r.SOURCE$_ID='||currentSourceId||
' and r.INSTANCE$_ID='||INSTANCE_ID;
execute immediate v_sql into currentResultNumber;
v_sql:='select count(RESULT$_ID) from '||
resultTable||' r where r.SOURCE$_ID='||
currentSourceId||' and r.CHECK$_STATUS=''0'' and r.INSTANCE$_ID='||INSTANCE_ID;
execute immediate v_sql into currentNotCheckNumber;
v_sql:='update '||sourceTable||
' s set s.RESULT$_NUMBER='||currentResultNumber||
',s.NOT_CHECK$_NUMBER='||currentNotCheckNumber||
' where s.SOURCE$_ID='||currentSourceId||' and s.INSTANCE$_ID='||INSTANCE_ID;
execute immediate v_sql;
commit;
exit when c_sd%notfound;
end loop;
close c_sd;
select RESULT_NUMBER into resultNumber_before from T_COMPARE_INSTANCE t where t.ID=INSTANCE_ID;
--取得未更新实例时的结果数(即执行前的结果数)
v_sql:='select count(RESULT$_ID) from '||
resultTable||' r where r.INSTANCE$_ID='||INSTANCE_ID; --取得实例执行后的结果数
execute immediate v_sql into resultNumber_after;
v_sql:='select count(RESULT$_ID) from '||
resultTable||' r where r.INSTANCE$_ID='||
INSTANCE_ID||' and r.CHECK$_STATUS=''0'''; --取得实例执行后未查看的结果数
execute immediate v_sql into notCheckNumber;
if (resultNumber_after<>resultNumber_before) then--如果有新结果则更新结果数和未查看结果数,并将warnFlag修改为1(报警)
update t_compare_instance t set t.result_number=resultNumber_after,t.not_check_number=notCheckNumber,t.warnflag='1' where t.id=INSTANCE_ID;
commit;
end if;
exception
when others then
dbms_output.put_line('exception ! ! !');
rollback;
end p_countCompareInstanceResult;