比方说有一个表(commod),里面有几百条数据,其中有一列(code),code是主键,里面是6位数字。现在有一个过程(sp_sh_code)。我每执行一次这个过程,那么commod里面的数据就少一条。不过我需要手工执行。exec sp_sh_code('123456').我现在要写一个过程,执行这个写的过程以后,sp_sh_code这个过程会把code里面的数据全部执行。也就是说不需要我在手工一条条的执行sp_sh_code这个过程了。请问应该怎么写?
解决方案 »
- oracle存储过程请教
- 错误代码:variant conversion error for variable:V3是什么原因,复制粘贴UPDATE表
- 急!!!在线等,在cursor中使用db link出现ORA-02020: too many database links in use异常
- oracle触发器的问题
- 如何用SQL 语句实现如下. 横纵转制的结果(超难)
- 建表问题.在线等.谢
- 今天发现原来录入的数据全都没有了,提示ntos-2011:无法显示带有LOB,RAW,LONG RAW和OBJECT类型列的表的内容
- 怎样去掉表格中的NULL值?
- oracle for linux谁能帮忙???
- 请各位高手帮一下忙
- 存储过程可不可以用字段做参数。。。
- 有難度的SQL語句
truncate table your_table;
即可。
as
v_rows number:=0;
begin
execute immediate 'declare
tmp number;
begin
select count(1) into tmp from A where code=:code;
:v_rows:=tmp;
end;'
using in code,in out v_rows;
if v_rows=0 then
return;
end if;
for i in 1..v_rows
loop
execute immediate 'begin sp_sh_code(:code); end;' using code;
end loop;
commit;
exception
when others then
rollback;
raise;
end;
as
v_rows number:=0;
begin
execute immediate 'declare
tmp number;
begin
select count(1) into tmp from commod where code=:code;
:v_rows:=tmp;
end;'
using in code,in out v_rows;
if v_rows=0 then
return;
end if;
for i in 1..v_rows
loop
execute immediate 'begin sp_sh_code(:code); end;' using code;
end loop;
commit;
exception
when others then
rollback;
raise;
end;
create or replace p_execall
is
begin
for i in (select code from commod)
loop
sp_sh_code(i.code);
end loop;
end;
create table commod(code varchar2(20));--插入测试数据
declare
i number default 1;
begin
loop
insert into commod
select to_char(i) from dual
union
select to_char(i+1) from dual;
exit when i>20;
i:=i+1;
end loop;
commit;
exception
when others then
rollback;
raise;
end;--创建存储过程(删除一行)
create or replace procedure sp_sh_code(code varchar2)
as
begin
execute immediate 'delete from commod where code=:code and rownum=1'
using in code;
commit;
exception
when others then
rollback;
raise;
end;--创建自动全部删除的存储过程
create or replace procedure pp(code varchar2)
as
v_rows number:=0;
begin
execute immediate 'declare
tmp number;
begin
select count(1) into tmp from commod where code=:code;
:v_rows:=tmp;
end;'
using in code,in out v_rows;
if v_rows=0 then
return;
end if;
for i in 1..v_rows
loop
execute immediate 'begin sp_sh_code(:code); end;' using code;
end loop;
commit;
exception
when others then
rollback;
raise;
end;--select count(1) from commod where code='2'
--测试
begin
pp('3');
end;
create or replace p_execall(pcode varchar2)
is
begin
for i in (select code from commod where code = pcode)
loop
sp_sh_code(i.code);
end loop;
end;
执行一次,得手动执行
exec sp_rk_sh_cont('6300200012');
比方说现在有6300200012--6300201012这么多数据。有一千条数据,我一条条执行,肯定是不可能的,我的意思就是写一个过程,自动执行sp_rk_sh_cont这个过程。我在开始说的,其实只是个举例。我想要的过程就是一个可以自动执行sp_rk_sh_cont这个过程的过程。下面就是sp_rk_sh_cont过程的代码,其实这个代码跟我想要的没有关系,担心大家弄不懂我到底需要是,,还是写出来了。
create or replace procedure
sp_rk_sh_cont(v_seq in char)
as
v_seqno varchar2(11);
v_djbh varchar2(11);
v_htbh varchar2(15);
v_shmc varchar2(15);
v_ddseq varchar2(11);
v_ddbh varchar2(11); v_djlb char(1);
v_dhbz char(1);
lret number;
v_dd char(1);
ln_hdfk number;
ln_zq number;
ln_zqfk number;
ln_zhsje number;
ln_zbhsje number;
ln_dhsl number;
ln_sssl number;
ln_hsjj number;
ln_cont_seq number;
lddhsje number; lc_fk_style char(1);
lc_fk_type char(1);
lv_hdfkdh varchar2(11);
v_gys char(5);
v_jyfs char(1); cursor cur_com(vseq char) is
select *
from str_rk_com
where seqno=vseq
for update;
com_r cur_com%rowtype;
cursor cur_ddcom(vddseq char) is
select *
from str_dd_com
where seqno=vddseq
for update;
com_rdd cur_ddcom%rowtype;
r_rkdoc str_rk_doc%rowtype;
ll_count number;
begin
select djbh,djlb,shmc,gys,jyfs,nvl(ltrim(rtrim(htbh)),'#'),nvl(ltrim(rtrim(ddbh)),'#')
into v_djbh,v_djlb,v_shmc,v_gys,v_jyfs,v_htbh,v_ddbh
from str_rk_doc where seqno=v_seq and shbz='Y';
begin
select seqno,dhbz into v_ddseq,v_dhbz from str_dd_doc where djbh=v_ddbh;
exception when others then
v_ddseq:='#'; v_ddbh:='#';
end;
if v_htbh='#' or v_htbh is null then
begin
select htbh into v_htbh from cont_main
where gys=v_gys and jyfs=v_jyfs and status='1' and rownum=1;
exception when others then
v_htbh:='#';
end;
end if;
if v_djlb='1' or v_djlb='3'then
ln_zhsje:=0;
ln_zbhsje:=0;
if v_dhbz = '1' then
open cur_ddcom(v_ddseq);
loop
fetch cur_ddcom into com_rdd;
exit when cur_ddcom%notfound or cur_ddcom%notfound is null;
if com_r.splb='1' then
sp_com_bh_ztsl(com_rdd.com_code,v_shmc,nvl(com_rdd.sssl,0),'DD',-1);
end if;
begin
select nvl(hsjjje,0),nvl(sssl,0)+nvl(zpsl,0)
into lddhsje,ln_dhsl
from str_rk_com
where seqno=v_seq and com_code=com_rdd.com_code and nvl(memo,'#')=nvl(com_rdd.memo,'#');
exception when others then
lddhsje := 0;
ln_dhsl := 0;
end;
update cont_main
set yzxje = nvl(yzxje,0) + lddhsje - nvl(com_rdd.hsjjje,0)
where htbh=v_htbh ;
if com_rdd.splb='1' then
update str_dd_com
set sssl=ln_dhsl
where current of cur_ddcom;
else
update str_dd_com
set ssjhl=lddhsje
where current of cur_ddcom;
end if;
end loop;
close cur_ddcom;
else
open cur_com(v_seq);
loop
fetch cur_com into com_r;
exit when cur_com%notfound or cur_com%notfound is null;
ln_zhsje := ln_zhsje + round(com_r.hsjjje,2);
ln_zbhsje := ln_zbhsje + round(com_r.bhsjjje,4);
begin
select nvl(dhsl,0),nvl(sssl,0),nvl(hsjj,0),nvl(hsjjje,0)
into ln_dhsl,ln_sssl,ln_hsjj,lddhsje
from str_dd_com
where seqno=v_ddseq and com_code=com_r.com_code and nvl(memo,'#')=nvl(com_r.memo,'#');
exception when others then
ln_dhsl:=0; ln_sssl:=0;
end;
if ln_sssl=0 and v_dhbz<>'0' then
if v_dhbz='2' and com_r.lsje<0.1 then
null;
else
if com_r.splb='1' then
sp_com_bh_ztsl(com_r.com_code,v_shmc,ln_dhsl,'DD',-1);
end if;
update cont_main
set yzxje = nvl(yzxje,0) - lddhsje + nvl(com_r.hsjjje,0)
where htbh=v_htbh ;
end if;
elsif v_dhbz='0' then
update cont_main
set yzxje = nvl(yzxje,0) + nvl(com_r.hsjjje,0)
where htbh=v_htbh ;
end if;
if com_r.splb='1' then
update str_dd_com
set sssl=sssl+nvl(com_r.sssl,0)+nvl(com_r.zpsl,0),ssjj=com_r.hsjj
where seqno=v_ddseq and com_code=com_r.com_code and nvl(memo,'#')=nvl(com_r.memo,'#');
else
update str_dd_com
set ssjhl=ssjhl+com_r.hsjjje,ssjj=com_r.hsjj
where seqno=v_ddseq and com_code=com_r.com_code and nvl(memo,'#')=nvl(com_r.memo,'#');
end if;
end loop;
close cur_com;
end if;
elsif v_djlb='A' then
return;
else
raise_application_error(-20534,'错误:无效的单据类别'||v_djbh);
end if;
commit;
exception
when no_data_found then
sp_ht_error('a','sp_rk_sh_cont',v_djbh,'-20534','错误:找不到指定的单据,或单据未审核!'||v_djbh);
raise;
when others then
sp_ht_error('a','sp_rk_sh_cont',v_djbh,sqlcode,sqlerrm);
raise;
end sp_rk_sh_cont;