oracle存储过程实例（一）

create or replace procedure P_Problem_stock is
p_pk_mt_stockin varchar2(60);
p_pk_mt_stocklist varchar2(60);
p_pk_project varchar2(60);
p_pk_mt_stockchecklist varchar2(60);
p_taxrate NUMBER(20,8);
p_newgatemoney NUMBER(20,8);
p_pk_mt_stocktakelist varchar2(60);
p_nottaxmny NUMBER(20,8);
p_checkprice NUMBER(20,8);
p_taxmny NUMBER(20,8);
p_taxrate_temp NUMBER(20,8);
p_invoicetype NUMBER(20,8);
p_newgateprice NUMBER(20,8);
p_checkmoney_ntx NUMBER(20,8);
p_checkmoney_tx NUMBER(20,8);
p_checkprice_tx NUMBER(20,8);
p_pk_realcost varchar2(60);
p_notcostvalue varchar2(60);
p_leviedmode NUMBER(20,8);
p_isjishui NUMBER(20,8);
p_realcostcount NUMBER(20,8);
p_stocktakecount NUMBER(20,8);
p_mtaxes varchar2(60);

cursor stock is (select pk_mt_stocklist from temp_stock_Problem);
begin
for stocklist in stock loop
	select sl.pk_mt_stockin,sl.pk_mt_stocklist into p_pk_mt_stockin,p_pk_mt_stocklist from bpm_nmt_stocklist sl where sl.pk_mt_stocklist=stocklist.pk_mt_stocklist;
	select s.pk_project,s.taxrate into p_pk_project,p_taxrate from bpm_nmt_stockin s where s.pk_mt_stockin=p_pk_mt_stockin;
	select INVOICETYPE into p_invoicetype from bpm_nmt_stockin where pk_mt_stockin=p_pk_mt_stockin;
	select bbp.leviedmode into p_leviedmode from bpm_bd_project bbp where bbp.pk_project=p_pk_project;
	if (p_leviedmode=1 or p_invoicetype=1)
	then
		select 0 into p_isjishui from dual;
		select 0 into p_taxrate_temp from dual;
	end if;
	if p_isjishui=0
	then
		select round(sl.newgatemoney, 2),
		round(round(sl.newgatemoney, 2) / (1 + p_taxrate_temp / 100), 2),
		round(sl.newgatemoney, 2) -round(round(sl.newgatemoney, 2) / (1 + p_taxrate_temp / 100), 2),
		sl.newgateprice/(1 + p_taxrate_temp / 100),sl.newgateprice
		into p_newgatemoney, p_nottaxmny, p_taxmny,p_newgateprice,p_checkprice
		from bpm_nmt_stocklist sl
		where sl.pk_mt_stocklist = p_pk_mt_stocklist;
	else
		select round(sl.newgatemoney, 2),
		round(round(sl.newgatemoney, 2) / (1 + p_taxrate / 100), 2),
		round(sl.newgatemoney, 2) -round(round(sl.newgatemoney, 2) / (1 + p_taxrate / 100), 2),
		sl.newgateprice/(1 + p_taxrate / 100),sl.newgateprice
		into p_newgatemoney, p_nottaxmny, p_taxmny,p_newgateprice,p_checkprice
		from bpm_nmt_stocklist sl
		where sl.pk_mt_stocklist = p_pk_mt_stocklist;
	end if;

	update bpm_nmt_stocklist sl
	set sl.newgatemoney=p_newgatemoney,sl.nottaxmny=p_nottaxmny,sl.taxmny=p_taxmny,sl.nottaxprice=p_newgateprice
	where sl.pk_mt_stocklist=p_pk_mt_stocklist;

	update bpm_nmt_stockcheckin sc
	set sc.taxrate=p_taxrate
	where sc.pk_mt_stockin=p_pk_mt_stockin;

	update bpm_nmt_stockchecklist scl
	set scl.checkmoney=p_newgatemoney,scl.nottaxmny=p_nottaxmny,scl.taxmny=p_taxmny,scl.nottaxprice=p_newgateprice
	where scl.pk_mt_stocklist=p_pk_mt_stocklist;

	update bpm_nmt_stockout so
	set so.taxrate=p_taxrate
	where so.pk_mt_stockin=p_pk_mt_stockin;

	if p_isjishui=0
	then
		update bpm_nmt_stockoutlist sol
		set 
		sol.nottaxmny=round(round(sol.ntotalbasem,2)/(1+p_taxrate_temp/100),2),
		sol.taxmny=round(sol.ntotalbasem,2)-round(round(sol.ntotalbasem,2)/(1+p_taxrate_temp/100),2),
		sol.nottaxprice=p_newgateprice
		where sol.pk_mt_stocklist=p_pk_mt_stocklist;
	else
		update bpm_nmt_stockoutlist sol
		set
		sol.nottaxmny=round(round(sol.ntotalbasem,2)/(1+p_taxrate/100),2),
		sol.taxmny=round(sol.ntotalbasem,2)-round(round(sol.ntotalbasem,2)/(1+p_taxrate/100),2),
		sol.nottaxprice=p_newgateprice
		where sol.pk_mt_stocklist=p_pk_mt_stocklist;
	end if;

	select scl.pk_mt_stockchecklist into p_pk_mt_stockchecklist from bpm_nmt_stockchecklist scl where scl.pk_mt_stocklist=p_pk_mt_stocklist and dr=0;

	select count(*) into p_stocktakecount from bpm_nmt_stocktakelist stl where stl.pk_mt_stocklist=p_pk_mt_stockchecklist and stl.acceptancetype!=7 and stl.dr=0 and stl.isnew=‘Y’;
	if
		p_stocktakecount=1
	then
		select stl.pk_mt_stockchecklist into p_pk_mt_stocktakelist from bpm_nmt_stocktakelist stl where stl.pk_mt_stocklist=p_pk_mt_stockchecklist and stl.acceptancetype!=7 and stl.dr=0 and stl.isnew=‘Y’;

		if p_pk_mt_stocktakelist is not null 
		then
			if
				p_isjishui=0
			then
				select round(stl.checkmoney/(1+p_taxrate_temp/100),2),stl.checkmoney-round(stl.checkmoney/(1+p_taxrate_temp/100),2),stl.checkprice/(1+p_taxrate_temp/100)
				into p_checkmoney_ntx,p_checkmoney_tx,p_checkprice_tx from bpm_nmt_stocktakelist stl
				where stl.pk_mt_stockchecklist=p_pk_mt_stocktakelist;
				update bpm_nmt_stocktakelist stl
				set stl.taxrate=p_taxrate,stl.nottaxmny=p_checkmoney_ntx,stl.taxmny=p_checkmoney_tx,stl.nottaxprice=p_checkprice_tx
				where stl.pk_mt_stockchecklist=p_pk_mt_stocktakelist;
			else
				select round(stl.checkmoney/(1+p_taxrate/100),2),stl.checkmoney-round(stl.checkmoney/(1+p_taxrate/100),2),stl.checkprice/(1+p_taxrate/100)
				into p_checkmoney_ntx,p_checkmoney_tx,p_checkprice_tx from bpm_nmt_stocktakelist stl
				where stl.pk_mt_stockchecklist=p_pk_mt_stocktakelist;
				update bpm_nmt_stocktakelist stl
				set stl.taxrate=p_taxrate,stl.nottaxmny=p_checkmoney_ntx,stl.taxmny=p_checkmoney_tx,stl.nottaxprice=p_checkprice_tx
				where stl.pk_mt_stockchecklist=p_pk_mt_stocktakelist;
			end if;
			select count(*) into p_realcostcount from bpm_cm_realcost_b rb
			where rb.pk_subvaluedetail=p_pk_mt_stocktakelist and rb.dr=0 and rb.pk_realcost in (select pk_realcost from bpm_cm_realcost r where r.pk_project=p_pk_project and r.costtype=‘01’);
			if 
				p_realcostcount =1
			then
				select rb.pk_realcost into p_pk_realcost from bpm_cm_realcost_b rb
				where rb.pk_subvaluedetail=p_pk_mt_stocktakelist and rb.dr=0 and rb.pk_realcost in (select pk_realcost from bpm_cm_realcost r where r.pk_project=p_pk_project and r.costtype=‘01’);

				update bpm_cm_realcost_b rb
				set rb.notrtotalbasemny=p_checkmoney_ntx,rb.mtaxes=p_checkmoney_tx
				where rb.pk_subvaluedetail=p_pk_mt_stocktakelist and rb.dr=0 and rb.pk_realcost in (select pk_realcost from bpm_cm_realcost r where r.pk_project=p_pk_project and r.costtype=‘01’);

				select p_mtaxes,p_notcostvalue from bpm_cm_realcost_b rb
				where rb.dr=0 and rb.pk_realcost=p_pk_realcost group by rb.pk_realcost;

				update bpm_cm_realcost r
				set r.mtaxes=p_mtaxes,r.notcostvalue=p_notcostvalue/,r.costvalue=p_COSTVALUE/
				where r.pk_realcost=p_pk_realcost;
			end if;
		end if;
	end if ;
end loop;
/* commit;*/
end ;