存储过程

最新推荐文章于 2025-01-15 10:00:00 发布

发愁昵称

最新推荐文章于 2025-01-15 10:00:00 发布

阅读量104

点赞数

文章标签： SQL

本文链接：https://blog.youkuaiyun.com/u012727243/article/details/83705811

版权

CREATE OR REPLACE procedure proc_mail_box_count
IS

table_name varchar2(64);
table_num varchar2(64);
i number;
BEGIN

i:=0;
while i<100 loop

if i<10 then
table_num:='0'||i;
else
table_num:=''||i;
end if;

table_name:='DXBG_MAIL_BOX_'||table_num;

proc_mail_box_count_sub(table_name,table_num);

i:=i+1;

end loop;

END;
/

CREATE OR REPLACE procedure proc_mail_box_count_sub(v_table in varchar2, v_box in varchar2)
IS
v_curr_size integer;

v_has number;

v_sql varchar2(64);

BEGIN

v_sql:='select count(*) from '||v_table;

execute immediate v_sql into v_curr_size;

select count(*) into v_has from DXBG_MAIL_BOX_COUNT where BOXID=v_box;

if(v_has =0) then
insert into DXBG_MAIL_BOX_COUNT (BOXID,BOX_CURR_SIZE)
values (v_box,v_curr_size);
elsif(v_has =1) then
update DXBG_MAIL_BOX_COUNT set BOX_CURR_SIZE=v_curr_size where BOXID=v_box;
else
delete from DXBG_MAIL_BOX_COUNT where BOXID=v_box;
insert into DXBG_MAIL_BOX_COUNT (BOXID,BOX_CURR_SIZE)
values (v_box,v_curr_size);
end if;

END;
/

表DXBG_MAIL_BOX_COUNT 有两个字段BOXID,BOX_CURR_SIZE
表DXBG_MAIL_BOX_00至DXBG_MAIL_BOX_99，表的字段不是很关心