因为不熟悉Oracle
CREATE procedure [dbo].[UP_ETDepotFind]
@Deptid int
as
Set Nocount On
Declare @inmount Numeric(18,2),
@inFare Money ,
@ETcode varchar(10),
@ETname varchar(50),
@spec varchar(50),
@unitid int,
@classid int,
@countryid int,
@pvcode varchar(10),
@pdcode varchar(10),
@price money,
@amount numeric(10,2),
@pjmoney money,
@wbcode varchar(20),
@pycode varchar(20),
@jobnumber varchar(20) Create table #StoreData(
ETFTCode varchar(10) default '',
ETFTName varchar(50) default '',
ClassID int default 0,
Unitid int default 0,
Spec varchar(50) default '',
PDCode varchar(10) default '',
PVCode varchar(10) default '',
PYCode varchar(20) default '',
WBCode varchar(20) default '',
pjprice Money default(0),
JobNumber varchar(10) default '',
CountryID int default 0,
DepotNum Numeric(18,2) default(0), --库存总数量
DepotFare Money default(0), --库存总金额
BackNum Numeric(18,2) default(0), --回收数量
BackFare Money default(0), --回收金额
)
insert into #StoreData(ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,
pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,
backnum,backfare)
select ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,price,jobnumber,
countryid,0,0.00,0,0.00
from MEMEquipmentFt
where kind=0 and useflag = 1 and depotid = @deptid--声明库存光标
declare cur_in cursor for
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=@Deptid and tag = '入' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid
open cur_in fetch next from cur_in into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney while @@fetch_status=0
begin
if exists(select * from #StoreData where ETFTCode=@ETcode and
pjprice=@price )
begin
update #StoreData
set Depotnum=isnull(depotnum,0)+isnull(@amount,0),
Depotfare=isnull(depotfare,0.00)+isnull(@pjmoney,0.00)
where ETFTCode=@ETcode and
pjprice=@price
if @@error<>0
return(10)--修改数据失败
end
else
begin
insert into #StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(@etcode,@etname,@classid,@unitid,@spec
,@pvcode,@pdcode,@pycode,@wbcode,@price,@jobnumber,@countryid,
isnull(@amount,0),isnull(@pjmoney,0.00),0,0.00)
if @@error<>0
return(10)--修改数据失败
end
fetch next from cur_in into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
end close cur_in deallocate cur_in --结束库存光标 --声明出库光标
declare cur_out cursor for
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=@Deptid and tag = '出' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid
open cur_out fetch next from cur_out into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney while @@fetch_status=0
begin
if exists(select * from #StoreData where ETFTCode=@ETcode and
Pjprice=@price )
begin
update #StoreData
set Depotnum=isnull(depotnum,0)-isnull(@amount,0),
Depotfare=isnull(depotfare,0.00)-isnull(@pjmoney,0.00)
where ETFTCode=@ETcode and
pjprice=@price
if @@error<>0
return(10)--修改数据失败
end
else
begin
insert into #StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(@etcode,@etname,@classid,@unitid,@spec
,@pvcode,@pdcode,@pycode,@wbcode,@price,@jobnumber,@countryid,
-isnull(@amount,0),-isnull(@pjmoney,0.00),0,0.00)
if @@error<>0
return(10)--修改数据失败
end
fetch next from cur_out into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
end close cur_out deallocate cur_out --声明回收光标
declare cur_back cursor for
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=@Deptid and tag = '回' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid
open cur_back fetch next from cur_back into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney while @@fetch_status=0
begin
if exists(select * from #StoreData where ETFTCode=@ETcode and
pjprice=@price )
begin
update #StoreData
set backnum=isnull(backnum,0)+isnull(@amount,0),
backfare=isnull(backfare,0.00)+isnull(@pjmoney,0.00)
where ETFTCode=@ETcode and
pjprice=@price
if @@error<>0
return(10)--修改数据失败
end
else
begin
insert into #StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(@etcode,@etname,@classid,@unitid,@spec
,@pvcode,@pdcode,@pycode,@wbcode,@price,@jobnumber,@countryid,
0,0.00,isnull(@amount,0),isnull(@pjmoney,0.00))
if @@error<>0
return(10)--修改数据失败
end
fetch next from cur_back into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
end close cur_back deallocate cur_back
Select identity(int,1,1)as ETID,* into #storedatatemp
from #StoreData
where DepotNum>=0
order by ETFTname
select * from #storedatatemp
drop table #storedatatemp
drop table #StoreData
CREATE procedure [dbo].[UP_ETDepotFind]
@Deptid int
as
Set Nocount On
Declare @inmount Numeric(18,2),
@inFare Money ,
@ETcode varchar(10),
@ETname varchar(50),
@spec varchar(50),
@unitid int,
@classid int,
@countryid int,
@pvcode varchar(10),
@pdcode varchar(10),
@price money,
@amount numeric(10,2),
@pjmoney money,
@wbcode varchar(20),
@pycode varchar(20),
@jobnumber varchar(20) Create table #StoreData(
ETFTCode varchar(10) default '',
ETFTName varchar(50) default '',
ClassID int default 0,
Unitid int default 0,
Spec varchar(50) default '',
PDCode varchar(10) default '',
PVCode varchar(10) default '',
PYCode varchar(20) default '',
WBCode varchar(20) default '',
pjprice Money default(0),
JobNumber varchar(10) default '',
CountryID int default 0,
DepotNum Numeric(18,2) default(0), --库存总数量
DepotFare Money default(0), --库存总金额
BackNum Numeric(18,2) default(0), --回收数量
BackFare Money default(0), --回收金额
)
insert into #StoreData(ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,
pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,
backnum,backfare)
select ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,price,jobnumber,
countryid,0,0.00,0,0.00
from MEMEquipmentFt
where kind=0 and useflag = 1 and depotid = @deptid--声明库存光标
declare cur_in cursor for
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=@Deptid and tag = '入' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid
open cur_in fetch next from cur_in into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney while @@fetch_status=0
begin
if exists(select * from #StoreData where ETFTCode=@ETcode and
pjprice=@price )
begin
update #StoreData
set Depotnum=isnull(depotnum,0)+isnull(@amount,0),
Depotfare=isnull(depotfare,0.00)+isnull(@pjmoney,0.00)
where ETFTCode=@ETcode and
pjprice=@price
if @@error<>0
return(10)--修改数据失败
end
else
begin
insert into #StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(@etcode,@etname,@classid,@unitid,@spec
,@pvcode,@pdcode,@pycode,@wbcode,@price,@jobnumber,@countryid,
isnull(@amount,0),isnull(@pjmoney,0.00),0,0.00)
if @@error<>0
return(10)--修改数据失败
end
fetch next from cur_in into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
end close cur_in deallocate cur_in --结束库存光标 --声明出库光标
declare cur_out cursor for
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=@Deptid and tag = '出' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid
open cur_out fetch next from cur_out into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney while @@fetch_status=0
begin
if exists(select * from #StoreData where ETFTCode=@ETcode and
Pjprice=@price )
begin
update #StoreData
set Depotnum=isnull(depotnum,0)-isnull(@amount,0),
Depotfare=isnull(depotfare,0.00)-isnull(@pjmoney,0.00)
where ETFTCode=@ETcode and
pjprice=@price
if @@error<>0
return(10)--修改数据失败
end
else
begin
insert into #StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(@etcode,@etname,@classid,@unitid,@spec
,@pvcode,@pdcode,@pycode,@wbcode,@price,@jobnumber,@countryid,
-isnull(@amount,0),-isnull(@pjmoney,0.00),0,0.00)
if @@error<>0
return(10)--修改数据失败
end
fetch next from cur_out into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
end close cur_out deallocate cur_out --声明回收光标
declare cur_back cursor for
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=@Deptid and tag = '回' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid
open cur_back fetch next from cur_back into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney while @@fetch_status=0
begin
if exists(select * from #StoreData where ETFTCode=@ETcode and
pjprice=@price )
begin
update #StoreData
set backnum=isnull(backnum,0)+isnull(@amount,0),
backfare=isnull(backfare,0.00)+isnull(@pjmoney,0.00)
where ETFTCode=@ETcode and
pjprice=@price
if @@error<>0
return(10)--修改数据失败
end
else
begin
insert into #StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(@etcode,@etname,@classid,@unitid,@spec
,@pvcode,@pdcode,@pycode,@wbcode,@price,@jobnumber,@countryid,
0,0.00,isnull(@amount,0),isnull(@pjmoney,0.00))
if @@error<>0
return(10)--修改数据失败
end
fetch next from cur_back into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
end close cur_back deallocate cur_back
Select identity(int,1,1)as ETID,* into #storedatatemp
from #StoreData
where DepotNum>=0
order by ETFTname
select * from #storedatatemp
drop table #storedatatemp
drop table #StoreData
Create global temporary table tmp_StoreData(
ETFTCode varchar2(10) default '',
ETFTName varchar2(50) default '',
ClassID int default 0,
Unitid int default 0,
Spec varcha2(50) default '',
PDCode varchar2(10) default '',
PVCode varchar2(10) default '',
PYCode varchar2(20) default '',
WBCode varchar2(20) default '',
pjprice Money default(0),
JobNumber varchar2(10) default '',
CountryID int default 0,
DepotNum number(18,2) default(0), --库存总数量
DepotFare number(18,2) default(0), --库存总金额
BackNum number(18,2) default(0), --回收数量
BackFare number(18,2) default(0), --回收金额
) on commit preserve rows
--你的就是利用临时表 返回结果集
CREATE procedure UP_ETDepotFind(v_Deptid number,cur out sys_refcursor)
as
v_inmount number(18,2);
v_inFare number(18,2);
v_ETcode varchar2(10);
v_ETname varchar2(50);
v_spec varchar2(50);
v_unitid number;
v_classid number;
v_countryid number;
v_pvcode varchar2(10);
v_pdcode varchar2(10);
v_price number(18,2);
v_amount number(10,2);
v_pjmoney number(18,2);
v_wbcode varchar2(20);
v_pycode varchar2(20);
v_jobnumber varchar2(20);
cnt1 number;
cnt2 number;
cnt3 number;--声明库存光标
cursor cur_in(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '入' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
--声明出库光标
cursor cur_out(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '出' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
--声明回收光标
cursor cur_back(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '回' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
begin
insert into tmp_StoreData(ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,
pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,
backnum,backfare)
select ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,price,jobnumber,
countryid,0,0.00,0,0.00
from MEMEquipmentFt
where kind=0 and useflag = 1 and depotid =v_deptid;--库存光标
open cur_in(v_Deptid); --fetch next from cur_in into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
--@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
while cur_in%found loop
---if exists(select * from #StoreData where ETFTCode=@ETcode and
--pjprice=@price )
select count(*) into cnt from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price;
if cnt1>0 then
update tmp_StoreData
set Depotnum=nvl(depotnum,0)+nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)+nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price;
--if @@error<>0
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,nvl(v_amount,0),nvl(v_pjmoney,0.00),0,0.00)
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop;
close cur_in;--结束库存光标 --出库光标
open cur_out(v_Deptid); --fetch next from cur_out into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
--@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney; --while @@fetch_status=0
while cur_out%found loop
select count(*) into num2 from tmp_StoreData where ETFTCode=v_ETcode and Pjprice=v_price ;
if num2>0 then
update tmp_StoreData
set Depotnum=nvl(depotnum,0)-nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)-nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price ;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
elseinsert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,-nvl(v_amount,0),-nvl(v_pjmoney,0.00),0,0.00);
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop; close cur_out; --回收光标 open cur_back(v_Deptid); fetch next from cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney; while cur_back%found loopselect count(*) into num3 from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price ;
if num3>0 then
update tmp_StoreData
set backnum=nvl(backnum,0)+nvl(v_amount,0),backfare=nvl(backfare,0.00)+nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price ;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v-pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,
0,0.00,nvl(v_amount,0),nvl(v_pjmoney,0.00));
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch next from cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop; close cur_back;
---游标返回结果集
open cur for select rownum as ETID,a.* from tmp_StoreData a where DepotNum>=0 order by ETFTname;
end;
---临时表的建表语句改下Create global temporary table tmp_StoreData(
ETFTCode varchar2(10) default '',
ETFTName varchar2(50) default '',
ClassID int default 0,
Unitid int default 0,
Spec varchar2(50) default '',
PDCode varchar2(10) default '',
PVCode varchar2(10) default '',
PYCode varchar2(20) default '',
WBCode varchar2(20) default '',
pjprice number(18,2) default 0,
JobNumber varchar2(10) default '',
CountryID int default 0,
DepotNum number(18,2) default 0,
DepotFare number(18,2) default 0,
BackNum number(18,2) default 0,
BackFare number(18,2) default 0
) on commit preserve rows---上班有事情 所以没看 现在你看看
--改下CREATE procedure UP_ETDepotFind(v_Deptid number,cur out sys_refcursor)
as
v_inmount number(18,2);
v_inFare number(18,2);
v_ETcode varchar2(10);
v_ETname varchar2(50);
v_spec varchar2(50);
v_unitid number;
v_classid number;
v_countryid number;
v_pvcode varchar2(10);
v_pdcode varchar2(10);
v_price number(18,2);
v_amount number(10,2);
v_pjmoney number(18,2);
v_wbcode varchar2(20);
v_pycode varchar2(20);
v_jobnumber varchar2(20);
cnt1 number;
cnt2 number;
cnt3 number;--声明库存光标
cursor cur_in(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '入' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
--声明出库光标
cursor cur_out(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '出' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
--声明回收光标
cursor cur_back(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '回' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
begin
insert into tmp_StoreData(ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,
pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,
backnum,backfare)
select ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,price,jobnumber,
countryid,0,0.00,0,0.00
from MEMEquipmentFt
where kind=0 and useflag = 1 and depotid =v_deptid;--库存光标
open cur_in(v_Deptid); --fetch next from cur_in into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
--@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
while cur_in%found loop
---if exists(select * from #StoreData where ETFTCode=@ETcode and
--pjprice=@price )
select count(*) into cnt from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price;
if cnt1>0 then
update tmp_StoreData
set Depotnum=nvl(depotnum,0)+nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)+nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price;
--if @@error<>0
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,nvl(v_amount,0),nvl(v_pjmoney,0.00),0,0.00)
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop;
close cur_in;--结束库存光标 --出库光标
open cur_out(v_Deptid); --fetch next from cur_out into @ETcode,@ETname,@classid,@unitid,@spec,@pdcode,@pvcode,
--@pycode,@wbcode,@price,@jobnumber,@countryid ,@amount,@pjmoney
fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney; --while @@fetch_status=0
while cur_out%found loop
select count(*) into num2 from tmp_StoreData where ETFTCode=v_ETcode and Pjprice=v_price ;
if num2>0 then
update tmp_StoreData
set Depotnum=nvl(depotnum,0)-nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)-nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price ;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
elseinsert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,-nvl(v_amount,0),-nvl(v_pjmoney,0.00),0,0.00);
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop; close cur_out; --回收光标 open cur_back(v_Deptid); fetch cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney; while cur_back%found loopselect count(*) into num3 from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price ;
if num3>0 then
update tmp_StoreData
set backnum=nvl(backnum,0)+nvl(v_amount,0),backfare=nvl(backfare,0.00)+nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price ;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,
pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,
depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v-pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,
0,0.00,nvl(v_amount,0),nvl(v_pjmoney,0.00));
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop; close cur_back;
---游标返回结果集
open cur for select rownum as ETID,a.* from #StoreData a where DepotNum>=0 order by ETFTname;
end;
depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,nvl(v_amount,0),nvl(v_pjmoney,0.00),0,0.00)
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败---Error(82,164): PL/SQL: ORA-00933: SQL 命令未正确结束end if;
end if;
end if;
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,
v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop;
close cur_in;
--Error(86,5): PLS-00103: 出现符号 "IF"在需要下列之一时： loop 符号 "loop在 "IF" 继续之前已插入。
Error(90,3): PLS-00103: 出现符号 "CLOSE"
已写入 file afiedt.buf 1 CREATE procedure UP_ETDepotFind(v_Deptid number,cur out sys_refcursor)
2 as
3 v_inmount number(18,2);
4 v_inFare number(18,2);
5 v_ETcode varchar2(10);
6 v_ETname varchar2(50);
7 v_spec varchar2(50);
8 v_unitid number;
9 v_classid number;
10 v_countryid number;
11 v_pvcode varchar2(10);
12 v_pdcode varchar2(10);
13 v_price number(18,2);
14 v_amount number(10,2);
15 v_pjmoney number(18,2);
16 v_wbcode varchar2(20);
17 v_pycode varchar2(20);
18 v_jobnumber varchar2(20);
19 cnt1 number;
20 cnt2 number;
21 cnt3 number;
22 cursor cur_in(c_deptid number) is select b.devcode ETFTCode,b.name ETFTName,b.classid,b.uniti
23 b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney)
24 from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
25 where depotid=c_deptid and tag = '入' and checker is not null
26 group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
27 b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
28 cursor cur_out(c_deptid number) is
29 select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
30 b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney)
31 from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
32 where depotid=c_deptid and tag = '出' and checker is not null
33 group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
34 b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
35 cursor cur_back(c_deptid number) is
36 select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
37 b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney)
38 from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
39 where depotid=c_deptid and tag = '回' and checker is not null
40 group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,b.pycode,b.wbcode,b.pjp
41 begin
42 insert into tmp_StoreData(ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,p
43 select ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,price,jobnumber,coun
44 from MEMEquipmentFt
45 where kind=0 and useflag = 1 and depotid =v_deptid;
46 open cur_in(v_Deptid);
47 fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wb
48 while cur_in%found loop
49 select count(*) into cnt1 from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price;
50 if cnt1>0 then
51 update tmp_StoreData
52 set Depotnum=nvl(depotnum,0)+nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)+nvl(v_pjmoney,0.00)
53 where ETFTCode=v_ETcode and pjprice=v_price;
54 if sql%rowcount=0 then
55 dbms_output.put_line('修改数据未成功');--修改数据失败
56 end if;
57 else
58 insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjp
59 values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,
60 if sql%rowcount=0 then
61 dbms_output.put_line('插入数据未成功');--修改数据失败
62 end if;
63 end if;
64 fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbco
65 end loop;
66 close cur_in;
67 open cur_out(v_Deptid);
68 fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbc
69 while cur_out%found loop
70 select count(*) into cnt2 from tmp_StoreData where ETFTCode=v_ETcode and Pjprice=v_price ;
71 if cnt2>0 then
72 update tmp_StoreData
73 set Depotnum=nvl(depotnum,0)-nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)-nvl(v_pjmoney,0.00)
74 where ETFTCode=v_ETcode and pjprice=v_price ;
75 if sql%rowcount=0 then
76 dbms_output.put_line('修改数据未成功');--修改数据失败
77 end if;
78 else
79 insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjp
80 values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,
81 if sql%rowcount=0 then
82 dbms_output.put_line('插入数据未成功');--修改数据失败
83 end if;
84 end if;
85 fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbc
86 end loop;
87 close cur_out;
88 open cur_back(v_Deptid);
89 fetch cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_w
90 while cur_back%found loop
91 select count(*) into cnt3 from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price ;
92 if cnt3>0 then
93 update tmp_StoreData set backnum=nvl(backnum,0)+nvl(v_amount,0),backfare=nvl(backfare,0.00)+nvl
94 where ETFTCode=v_ETcode and pjprice=v_price ;
95 if sql%rowcount=0 then
96 dbms_output.put_line('修改数据未成功');--修改数据失败
97 end if;
98 else
99 insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjp
100 values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,
101 if sql%rowcount=0 then
102 dbms_output.put_line('插入数据未成功');--修改数据失败
103 end if;
104 end if;
105 fetch cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_w
106 end loop;
107 close cur_back;
108 open cur for select rownum as ETID,a.* from tmp_StoreData a where DepotNum>=0 order by ETFTname
109* end;
SQL> /过程已创建。SQL>
as
v_inmount number(18,2);
v_inFare number(18,2);
v_ETcode varchar2(10);
v_ETname varchar2(50);
v_spec varchar2(50);
v_unitid number;
v_classid number;
v_countryid number;
v_pvcode varchar2(10);
v_pdcode varchar2(10);
v_price number(18,2);
v_amount number(10,2);
v_pjmoney number(18,2);
v_wbcode varchar2(20);
v_pycode varchar2(20);
v_jobnumber varchar2(20);
cnt1 number;
cnt2 number;
cnt3 number;
cursor cur_in(c_deptid number) is select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '入' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
cursor cur_out(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '出' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
cursor cur_back(c_deptid number) is
select b.devcode ETFTCode,b.name ETFTName,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,
b.pycode,b.wbcode,b.pjprice price,b.jobnumber,b.countryid,sum(b.amount) innum,sum(b.pjmoney) infare
from MEMInOutMaster a inner join MEMInOutDetail b on a.iomid = b.imid
where depotid=c_deptid and tag = '回' and checker is not null
group by b.devcode,b.name,b.classid,b.unitid,b.spec,b.pdcode,b.pvcode,b.pycode,b.wbcode,b.pjprice,b.jobnumber,b.countryid;
begin
insert into tmp_StoreData(ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,backnum,backfare)
select ETFTCode,ETFTName,classid,unitid,spec,pdcode,pvcode,pycode,wbcode,price,jobnumber,countryid,0,0.00,0,0.00
from MEMEquipmentFt
where kind=0 and useflag = 1 and depotid =v_deptid;
open cur_in(v_Deptid);
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
while cur_in%found loop
select count(*) into cnt1 from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price;
if cnt1>0 then
update tmp_StoreData
set Depotnum=nvl(depotnum,0)+nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)+nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,nvl(v_amount,0),nvl(v_pjmoney,0.00),0,0.00);
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_in into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop;
close cur_in;
open cur_out(v_Deptid);
fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
while cur_out%found loop
select count(*) into cnt2 from tmp_StoreData where ETFTCode=v_ETcode and Pjprice=v_price ;
if cnt2>0 then
update tmp_StoreData
set Depotnum=nvl(depotnum,0)-nvl(v_amount,0),Depotfare=nvl(depotfare,0.00)-nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price ;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,-nvl(v_amount,0),-nvl(v_pjmoney,0.00),0,0.00);
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_out into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop;
close cur_out;
open cur_back(v_Deptid);
fetch cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
while cur_back%found loop
select count(*) into cnt3 from tmp_StoreData where ETFTCode=v_ETcode and pjprice=v_price ;
if cnt3>0 then
update tmp_StoreData set backnum=nvl(backnum,0)+nvl(v_amount,0),backfare=nvl(backfare,0.00)+nvl(v_pjmoney,0.00)
where ETFTCode=v_ETcode and pjprice=v_price ;
if sql%rowcount=0 then
dbms_output.put_line('修改数据未成功');--修改数据失败
end if;
else
insert into tmp_StoreData(ETFTcode,ETFTname,classid,unitid,spec,pvcode,pdcode,pycode,wbcode,pjprice,jobnumber,countryid,depotnum,depotfare,backnum,backfare)
values(v_etcode,v_etname,v_classid,v_unitid,v_spec,v_pvcode,v_pdcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid,0,0.00,nvl(v_amount,0),nvl(v_pjmoney,0.00));
if sql%rowcount=0 then
dbms_output.put_line('插入数据未成功');--修改数据失败
end if;
end if;
fetch cur_back into v_ETcode,v_ETname,v_classid,v_unitid,v_spec,v_pdcode,v_pvcode,v_pycode,v_wbcode,v_price,v_jobnumber,v_countryid ,v_amount,v_pjmoney;
end loop;
close cur_back;
open cur for select rownum as ETID,a.* from tmp_StoreData a where DepotNum>=0 order by ETFTname;
end;
/
exec UP_ETDepotFind('85',:cur)
print cur我 调式没点问题
或者你写个匿名快调试下
SQL> set serveroutput on size 100000
SQL> declare
2 cur sys_refcursor;
3 v_id number;
4 v_ETFTCODE VARCHAR2(10);
5 v_ETFTNAME VARCHAR2(50);
6 v_CLASSID NUMBER(38);
7 v_UNITID NUMBER(38);
8 v_SPEC VARCHAR2(50);
9 v_PDCODE VARCHAR2(10);
10 v_PVCODE VARCHAR2(10);
11 v_PYCODE VARCHAR2(20);
12 v_WBCODE VARCHAR2(20);
13 v_PJPRICE NUMBER(18,2);
14 v_JOBNUMBER VARCHAR2(10);
15 v_COUNTRYID NUMBER(38);
16 v_DEPOTNUM NUMBER(18,2);
17 v_DEPOTFARE NUMBER(18,2);
18 v_BACKNUM NUMBER(18,2);
19 v_BACKFARE NUMBER(18,2);
20 begin
21 UP_ETDepotFind(85,cur);
22 loop
23 fetch cur into v_id,v_ETFTCODE,v_ETFTNAME,v_CLASSID,v_UNITID,v_SPEC,v_PDCODE,v_PVCODE,v_PYCODE,v_WBCODE,v_PJPRICE,v_JOBNUMBER,v_COUNTRYID,v_DEPOTNUM,v_DEPOTFARE,v_BACKNUM,v_BACKFARE;
24 exit when cur%notfound ;
25 dbms_output.put_line(v_id||' '||v_ETFTCODE||' '||v_ETFTNAME||' '||v_CLASSID||' '||v_UNITID||' '||v_SPEC||' '||v_PDCODE||' '||v_PVCODE||' '||v_PYCODE||' '||v_WBCODE||' '||v_PJPRICE||' '||v_JOBNUMBER||' '||v_COUNTRYID||' '||v_DEPOTNUM||' '||v_DEPOTFARE||' '||v_BACKNUM||' '||v_BACKFARE);
26 end loop;
27 close cur;
28 end;
29 /declare
cur sys_refcursor;
v_id number;
v_ETFTCODE VARCHAR2(10);
v_ETFTNAME VARCHAR2(50);
v_CLASSID NUMBER(38);
v_UNITID NUMBER(38);
v_SPEC VARCHAR2(50);
v_PDCODE VARCHAR2(10);
v_PVCODE VARCHAR2(10);
v_PYCODE VARCHAR2(20);
v_WBCODE VARCHAR2(20);
v_PJPRICE NUMBER(18,2);
v_JOBNUMBER VARCHAR2(10);
v_COUNTRYID NUMBER(38);
v_DEPOTNUM NUMBER(18,2);
v_DEPOTFARE NUMBER(18,2);
v_BACKNUM NUMBER(18,2);
v_BACKFARE NUMBER(18,2);
begin
UP_ETDepotFind(85,cur);
loop
fetch cur into v_id,v_ETFTCODE,v_ETFTNAME,v_CLASSID,v_UNITID,v_SPEC,v_PDCODE,v_PVCODE,v_PYCODE,v_WBCODE,v_PJPRICE,v_JOBNUMBER,v_COUNTRYID,v_DEPOTNUM,v_DEPOTFARE,v_BACKNUM,v_BACKFARE;
exit when cur%notfound ;
dbms_output.put_line(v_id||' '||v_ETFTCODE||' '||v_ETFTNAME||' '||v_CLASSID||' '||v_UNITID||' '||v_SPEC||' '||v_PDCODE||' '||v_PVCODE||' '||v_PYCODE||' '||v_WBCODE||' '||v_PJPRICE||' '||v_JOBNUMBER||' '||v_COUNTRYID||' '||v_DEPOTNUM||' '||v_DEPOTFARE||' '||v_BACKNUM||' '||v_BACKFARE);
end loop;
close cur;
end;
--结果 ---回复有长度限制 就贴一部分237 0516 24小时动态心电监护仪 38 3 24XSDTXDJH 24IJFDNJJR 150000 1998-10-29 32 0 0 0 0
266 0543 B超 38 3 0038 0012 BC BF 49860 2010-12-10 0 0 0 0
151 0282 B超 38 3 BC BF 40000 0 0 0 0
74 0359 C波段紫外线消毒机 38 3 CBDZWXXDJ CIWHQXIGS 10800 2003-08-07 0 0 0 0
69 0354 NSK根管手机 38 3 NSKGGSJ NSKSTRS 3100 2004-03-28 0 0 0 0
177 0459 UPS 38 3 UPS UPS 5500 2006-5-12 32 0 0 0 0
235 0514 UPS 38 3 UPS UPS 9800 2002-12-19 32 0 0 0 0
234 0513 UPS 38 3 UPS UPS 1500 1999-12-07 32 0 0 0 0
5 0290 X光机 38 3 XGJ XIS 1300 1995-12-12 0 0 0 0
213 0494 X光机 38 3 XGJ XIS 130000 1995-12-12 32 0 0 0 0
135 0420 X光机 38 3 XGJ XIS 59800 2003-04-18 0 0 0 0
212 0493 X光胶片观察灯 38 1 XGJPGCD XIETCPO 1092 2001-10-22 32 0 0 0 0
187 0469 半自动分析仪 38 3 BZDFXY UTFWSW 56000 2000-09-27 32 0 0 0 0
180 0462 半自动血凝仪 38 3 BZDXNY UTFTUW 60000 2001-12-12 32 0 0 0 0
103 0388 膀胱尿道镜 38 3 BGNDJ EENUQ 110000 2003-09-26 0 0 0 0
230 0510 背负式喷雾器 38 3 BFSPWQ UQAKFK 7600 2003-9-10 32 0 0 0 0
3 0288 背负式喷雾器 38 3 BFSPWQ UQAKFK 7128.82 2003-9-10 0 0 0 0
240 0518 病理夹车 38 3 0004 BLJC UGGL 1860 32 0 0 0 0
10 0295 病理设备一套 38 15 BLSBYT UGYTGD 285000 2008-3-31 0 0 0 0
129 0414 病历夹车 38 3 BLJC UDGL 1800 2005-05-30 0 0 0 0
46 0331 病历夹车 38 1 BLJC UDGL 1800 2005-05-30 0 0 0 0
147 0432 不锈钢Ⅰ护理车 38 3 BXG1HLC IQQ1RGL 1220 2005-10-24 0 0 0 0
42 0327 不锈钢Ⅰ型护理车 38 3 BXG1XHLC IQQ1GRGL 1220 2005-10-24 0 0 0 0
126 0411 不锈钢病人推车 38 1 BXGBRTC IQQUWRL 1600 2005-05-30 0 0 0 0
26 0311 不锈钢病人推车 38 3 BXGBRTC IQQUWRL 1600 2005-05-30 0 0 0 0
150 0435 不锈钢病人推车 38 3 BXGBRTC IQQUWRL 1600 2005-05-30 0 0 0 0
43 0328 不锈钢病人推车 38 1 BXGBRTC IQQUWRL 1600 2005-05-30 0 0 0 0
108 0393 不锈钢担架 38 1 BXGDJ IQQRL 3700 1999-02-12 0 0 0 0
100 0385 不锈钢单臂托盘升降台 38 1 BXGDBTPSJT IQQUNRTTBC 1189 2001-04-27 0 0 0 0
82 0367 不锈钢多用牵开器 38 3 BXGDYQKQ IQQQEDGK 627.9 2002-06-15 0 0 0 0
40 0325 不锈钢换药车 38 3 BXGHYC IQQRAL 1200 2006-06-20 0 0 0 0
190 0471 不锈钢急救车 38 3 BXGJJC IQQQFL 1480 2006-07-12 32 0 0 0 0
51 0336 不锈钢急救床 38 1 BXGJJC IQQQFY 1480 2006-07-12 0 0 0 0
101 0386 不锈钢麻醉车 38 3 BXGMZC IQQYSL 1252 2001-04-27 0 0 0 0
72 0357 不锈钢咬骨钳 38 3 BXGYGQ IQQKMQ 523.25 2002-06-15 0 0 0 0
102 0387 不锈钢药品柜 38 1 BXGYPG IQQAKS 1035 2001-06-14 0 0 0 0
127 0412 不锈钢治疗车 38 3 BXGZLC IQQIUL 780 2005-05-30 0 0 0 0
97 0382 不锈钢治疗车 38 3 BXGZLC IQQIUL 1284 1999-03-05 0 0 0 0
36 0321 不锈钢治疗车 38 3 BXGZLC IQQIUL 780 2005-05-30 0 0 0 0
27 0312 不锈钢治疗车 38 3 BXGZLC IQQIUL 780 2005-05-30 0 0 0 0
17 0302 不锈钢治疗车 38 3 BXGZLC IQQIUL 780 2005-05-30 0 0 0 0
160 0442 不锈钢治疗车 38 3 BXGZLC IQQIUL 1900 2001-04-04 0 0 0 0
75 0360 不锈钢椎板咬骨钳 38 1 BXGZBYGQ IQQSSKMQ 568 2002-06-15 0 0 0 0
152 0283 彩色B超（惠普影像之星） 38 3 CSBCHPYXZX EQBFGUJWPJ 1165000 2000-3-19 32 0 0 0 0
274 0551 测试的字典 57 2 0002 0002 CSDZD IYRPM 23 123 32 0 0 0 0
23 0308 铲式单架 38 1 CSDJ QAUL 2000 2010-03-22 0 0 0 0
66 0351 超声波洁牙机 38 3 CSBJYJ FFIIAS 2800 2004-03-28 0 0 0 0
57 0342 超声波洁牙机 38 3 CSBJYJ FFIIAS 8850 2004-03-28 0 0 0 0
62 0347 超声波清洗机 38 3 CSBQXJ FFIIIS 2850 2006-08-07 0 0 0 0
202 0483 超声多普勒胎音仪 38 3 CSDPLTYY FFQUAEUW 2080 2006-4-19 32 0 0 0 0
153 0284 超声影像工作站 38 3 CSYXGZZ FFJWAWU 40000 050111 0 0 0 0
232 0511 超声诊断设备 38 3 CSZDSB FFYOYT 39000 2003-5-27 32 0 0 0 0
68 0353 齿科手机 38 3 CKSJ HTRS 2100 2004-03-28 0 0 0 0
162 0444 除颤监护仪 38 3 CCJHY BYJRW 97000 2002-11-10 0 0 0 0
219 0500 除湿机 38 3 CSJ BIS 4000 1998-7-16 32 0 0 0 0
217 0498 除湿机 38 3 CSJ BIS 3050 1999-05-29 32 0 0 0 0
236 0515 除湿机 38 3 CSJ BIS 3300 1995-7-11 32 0 0 0 0
15 0300 除湿机 38 3 CSJ BIS 3050 1998-12-12 0 0 0 0
13 0298 除湿机 38 3 CSJ BIS 3050 1999-05-29 0 0 0 0
124 0409 除湿机 38 3 CSJ BIS 2800 2006-08-08 0 0 0 0
84 0369 穿刺器 38 1 CCQ PGK 2800 2005-08-15 0 0 0 0
77 0362 穿刺鞘 38 1 CCQ PGA 2000 2002-12-12 0 0 0 0
76 0361 穿刺鞘 38 1 CCQ PGA 7000 2002-12-12 0 0 0 0
253 0531 传片箱 38 1 0037 0009 CPX WTT 4200 2010-11-01 32 0 0 0 0
161 0443 床单位臭氧消毒机 38 3 CDWCYXDJ YUWTRIGS 23000 2003-08-04 0 0 0 0
7 0292 床旁X光机 38 3 CPXGJ YUXIS 90610.18 2000-1-10 0 0 0 0
215 0496 床旁X光机 38 3 CPXGJ YUXIS 205000 2000-01-10 32 0 0 0 0
55 0340 打磨抛光机 38 3 DMPGJ RYRIS 1380 1999-10-10 0 0 0 0
2 0287 大功率喷雾器 38 3 DGLPWQ DAYKFK 23450.02 2006-8-4 0 0 0 0
170 0452 大功率喷雾器 38 3 DGLPWQ DAYKFK 25000 32 0 0 0 0
229 0509 大功率喷雾器 38 1 DGLPWQ DAYKFK 25000 2003-8-4 32 0 0 0 0
206 0487 担架 38 1 DJ RL 0 0 0 0 0
1 0286 单导热线阵自动心电图机 38 3 DDRXZZDXDT UNRXBTFNJL 5575.87 2003-5-27 0 0 0 0
137 0422 单导热线阵自动心电图机 38 3 DDRXZZDXDT UNRXBTFNJL 6580 2003-05-27 0 0 0 0
107 0392 胆道外科刀包 38 1 DDWKDB EUQTVQ 5300 1998-11-09 0 0 0 0
254 0532 德力洗片机 38 3 0010 DLXPJ TLITS 19000 2010-10-30 32 0 0 0 0
98 0383 低噪音吸引器 38 3 DZYXYQ WKUKXK 801.5 1994-04-25 0 0 0 0
194 0475 低周波治疗仪 38 3 DZBZLY WMIIUW 35000 2001-06-26 32 0 0 0 0
44 0329 低周波治疗仪 38 3 DZBZLY WMIIUW 35000 2001-06-26 0 0 0 0
50 0335 电动按摩床 38 3 DDAMC JFRYY 30500 1999-10-29 0 0 0 0
196 0477 电动产床 38 3 DDCC JFUY 106000 2000-3-25 32 0 0 0 0
200 0481 电动流产吸引器 38 3 DDLCXYQ JFIUKXK 1360 2005-8-15 32 0 0 0 0
49 0334 电动牵引床 38 3 DDQYC JFDXY 77000 1999-12-26 0 0 0 0
112 0397 电动手术床 38 3 DDSSC JFRSY 160000 1999-12-12 0 0 0 0
249 0527 电动手术床 38 3 0005 DDSSC JFRSY 39000 32 0 0 0 0
159 0441 电动吸痰车 38 3 DDXTC JFKUL 984 1995-10-16 0 0 0 0
252 0530 电动吸痰器 38 3 DDXTQ JFKUK 780 2010年7月 32 0 0 0 0
30 0315 电动吸引器 38 3 DDXYQ JFKXK 1298 1994-11-10 0 0 0 0
14 0299 电动吸引器 38 3 DDXYQ JFKXK 1298 2000-03-29 0 0 0 0
183 0465 电解质分析仪 38 3 DJZFXY JQRWSW 45000 2004-9-28 32 0 0 0 0
178 0460 电解质分析仪 38 3 DJZFXY JQRWSW 29800 2006-3-28 32 0 0 0 0
188 0470 电热干燥箱 38 3 DRGZX JRFOT 2200 1997-10-15 32 0 0 0 0
11 0296 电热鼓风干燥箱 38 3 DRGFGZX JRFMFOT 4000 2006-3-28 0 0 0 0
175 0457 电子胃镜 38 3 DZWJ JBLQ 187000 1999-08-25 32 0 0 0 0
199 0480 电子阴道镜 38 3 DZYDJ JBBUQ 155000 2001-6-18 32 0 0 0 0
145 0430 动态血压监测仪 38 3 DTXYJCY FDTDJIW 41600 2006-04-24 0 0 0 0
247 0525 多参数监护仪 38 3 0003 DCSJHY QCOJRW 18600 32 0 0 0 0
270 0547 多参数监护仪 38 3 0041 0012 DCSJHY QCOJRW 17500 2010-05-10 32 0 0 0 0
39 0324 多功能电离子治疗机 38 3 DGNDLZZLJ QACJYBIUS 3360 2006-07-27 0 0 0 0
192 0473 多功能电离子治疗机 38 3 DGNDLZZLJ QACJYBIUS 3360 2006-07-27 32 0 0 0 0
25 0310 多功能动态杀菌机 38 3 DGNDTSJJ QACFDQAS 3300 2006-04-19 0 0 0 0
73 0358 多功能动态杀菌机 38 3 DGNDTSJJ QACFDQAS 5500 2001-04-05 0 0 0 0
156 0438 多功能动态杀菌机 38 3 DGNDTSJJ QACFDQAS 3638 2005-05-30 0 0 0 0
114 0399 多功能骨钻 38 1 DGNGZ QACMQ 3000 2001-12-12 0 0 0 0
95 0380 多功能监护仪 38 3 DGNJHY QACJRW 50500 2001-02-01 0 0 0 0
143 0428 多功能麻醉机 38 3 DGNMZJ QACYSS 35000 1995-09-11 0 0 0 0
58 0343 二孔高速手机 38 3 EKGSSJ FBYGRS 1180 2006-08-07 0 0 0 0
6 0291 放射遥控器 38 3 FSYKQ YTERK 2590.22 2001-6-21 0 0 0 0
214 0495 放射遥控器 38 3 FSYKQ YTERK 37000 2001-6-21 32 0 0 0 0
264 0542 肺功能仪 38 3 0011 FGNY EACW 44800 2010-12-10 0 0 0 0
118 0403 芬兰麻醉机 38 3 FLMZJ AUYSS 354000 2002-10-13 0 0 0 0
109 0394 腹腔镜 38 15 FQJ EEQ 350000 2001-04-15 0 0 0 0
86 0371 腹腔镜镜头 38 7 FQJJT EEQQU 39700 2006-01-23 0 0 0 0
88 0373 腹腔镜摄像系统 38 15 FQJSXXT EEQRWTX 54922 2006-07-27 0 0 0 0
110 0395 高频电刀 38 1 GPDD YHJV 47865 1999-07-28 0 0 0 0
65 0350 高压消毒锅 38 3 GYXDG YDIGQ 900 1999-11-11 0 0 0 0
172 0454 骨科牵引床 38 3 GKQYC MTDXY 2522 1997-08-13 32 0 0 0 0
106 0391 骨科手术刀包 38 3 GKSSDB MTRSVQ 6280 1998-11-09 0 0 0 0
224 0505 光固化灯 38 3 GGHD ILWO 3950 2002-12-12 32 0 0 0 0
56 0341 光固化机 38 3 GGHJ ILWS 2162 2000-12-12 0 0 0 0
67 0352 光固化机 38 3 GGHJ ILWS 3100 2004-03-28 0 0 0 0
138 0423 毫米波治疗仪 38 3 HMBZLY YOIIUW 14000 2000-08-08 0 0 0 0
272 0549 喉镜 38 3 0003 HJ KQ 600 2010-12-29 32 0 0 0 0
263 0541 喉镜 38 15 0003 HJ KQ 650 32 0 0 0 0
33 0318 呼吸机 38 3 HXJ KKS 23000 2005-09-19 0 0 0 0
113 0398 呼吸急救机 38 3 HXJJJ KKQFS 31200 1999-12-12 0 0 0 0
91 0376 幻灯机 38 3 HDJ XOS 3200 2000-06-06 0 0 0 0
22 0307 急救车 38 1 JJC QFL 885 1995-09-23 0 0 0 0
158 0440 急救车 38 3 JJC QFL 885 1995-08-24 0 0 0 0
256 0534 急救药品推车 38 3 0004 JJYPTC QFAKRL 1860 20101019 32 0 0 0 0
226 0507 钾钠氯分析仪 38 3 JNLFXY QQRWSW 48000 1996-09-15 32 0 0 0 0
141 0426 钾钠氯分析仪 38 3 JNLFXY QQRWSW 48000 1996-09-15 0 0 0 0
228 0508 煎药机 38 3 JYJ UAS 23600 2006-04=24 32 0 0 0 0
238 0516 经颅多普勒 38 3 JLDPL XHQUA 170000 1998-9-16 32 0 0 0 0
171 0453 净水消毒器 38 3 JSXDQ UIIGK 25000 0 0 0 0
231 0510 净水消毒器 38 3 JSXDQ UIIGK 25000 1996-11-11 32 0 0 0 0
168 0450 空气净化消毒器 38 3 YKX2-800 KQJHXDQ PRUWIGK 6500 32 0 0 0 0
16 0301 空气消毒内规镜储存柜 38 1 KQXDNGJCCG PRIGMFQWDS 20000 2005-06-07 0 0 0 0
41 0326 冷光单孔手术灯 38 3 LGDKSSD UIUBRSO 720 2006-06-20 0 0 0 0
193 0474 冷光单孔手术灯 38 1 LGDKSSD UIUBRSO 720 2006-06-20 32 0 0 0 0
105 0390 冷光源 38 3 LGY UII 15000 2002-12-20 0 0 0 0
85 0370 冷光源喉镜 38 3 LGYHJ UIIKQ 6800 2002-05-05 0 0 0 0
184 0466 离心机 38 3 LXJ YNS 7900 2003-05-27 32 0 0 0 0
123 0408 立式压力蒸汽灭菌器 38 3 LSYLZQMJQ UADLAIGAK 10000 2003-08-07 0 0 0 0
223 0504 立式压力蒸汽灭菌器 38 3 LSYLZQMJQ UADLAIGAK 10000 2003-8-7 32 0 0 0 0
54 0339 连体式牙科综合治疗机 38 3 LTSYKZHZLJ LWAATXWIUS 40000 2000-12-24 0 0 0 0
268 0545 裂隙灯显微镜 38 3 0039 0012 LXDXWJ GBOJTQ 32000 2010-12-20 32 0 0 0 0
122 0407 裂隙灯显微镜 38 3 LXDXWJ GBOJTQ 26000 2006-03-28 0 0 0 0
28 0313 轮椅 38 1 LY LS 872 2005-05-30 0 0 0 0
45 0330 轮椅 38 1 LY LS 865 2005-05-30 0 0 0 0
154 0436 轮椅 54 1 LY LS 865 2005-05-30 0 0 0 0
128 0413 轮椅 38 1 LY LS 865 2005-05-30 0 0 0 0
90 0375 麻醉机 38 3 MZJ YSS 100000.2 2006-12-29 0 0 0 0
89 0374 麻醉机 38 3 MZJ YSS 102000 2006-04-15 0 0 0 0
83 0368 麻醉咽喉镜 38 3 MZYHJ YSKKQ 736 2002-06-15 0 0 0 0
245 0523 脉冲针灸治疗仪 38 3 0003 MCZJZLY EUQQIUW 900 32 0 0 0 0
61 0346 灭菌封口机 38 3 MJFKJ GAFKS 1584 2006-08-07 0 0 0 0
PL/SQL procedure successfully completed