--------单表插入 树形目录结构数据 （存储过程处理方式） create or replace procedure ADD_MT_BUYTYPE(
PARA_CODE MT_BUYTYPE.BUY_TYPECODE%type, //上级目录id；
V_NAME Mt_Buytype.Buy_Typename%type, //本级目录名称
V_MEMO Mt_Buytype.EXPLAN%type, //说明
tag out number) is //输出参数tag 类型为number。out 表示输出
max_code varchar(30); //定义返回值(生成类型编码的后三位)
begin //截取同级序列的后三位，加1排序.
if PARA_CODE is null then
begin
select lpad(max(substr(BUY_TYPECODE,1,3))+1,3,'0') into max_code from MT_BUYTYPE where active_flag='1';
if max_code is null then
max_code:='001';
end if;
end;
else
select lpad(max(substr(BUY_TYPECODE,length(BUY_TYPECODE)-2,3))+1,3,'0') into max_code from MT_BUYTYPE where
BUY_TYPECODE like PARA_CODE ||'%' and active_flag='1';
end if;
insert into MT_BUYTYPE(BUY_TYPECODE,Buy_Typename,PRE_CODE,GRADE,GRADE_END,ACTIVE_FLAG,EXPLAN)
values(PARA_CODE||max_code,V_NAME,PARA_CODE,length(PARA_CODE||max_code)/3,1,1,V_MEMO);
update MT_BUYTYPE set GRADE_END=0 where BUY_TYPECODE=PARA_CODE; //改变上级目录状态为0,不是末层.
tag:=0; //增加正常为0
exception //异常
when others then
tag:=-1; //增加失败 -1
rollback; //事务回滚
end ADD_MT_BUYTYPE;
--函数：
-- SUBSTR(string,start,count) ： 取子字符串,从start开始,取count个
-- LPAD ：在列的左边粘贴字符
-- max ：求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
-- || ：并置 的意思.
--说明：也就是通过函数获取树目录的上级类型编码，然后通过if语句获取层级类型编码的后三位，获取的数据返回到定义的返回值；
-- 然后通过sql语句(上级)拼成本级的类型编码;传入目录名称，说明，其他值默认输入；然后进行异常(exception)处理，事务处理。
-- when others then 表示它所在的begin.....end之间的代码所有执行的错误都可以；所有得错误都转到这个语句下面来执行
------end
------ 存储过程 begin
--- 功能实现:(查询出一张表的数据 插入另外一张表中)
create or replace procedure "P_BASE_FORWARD" is
mt_code mt_base_forward.mt_code%type; --材料ID
mt_ccode mt_base_forward.mt_ccode%type; --所属类别编码
mt_depotcode mt_base_forward.mt_depotcode%type; --- 库房编码
forward_how mt_base_forward.forward_how%type; --结转库存
forward_avgprice mt_base_forward.forward_avgprice%type; --结转平均单价
--功能主要是材料基础结转
--定义一个游标
cursor c_mt_base is select mt_code,mt_ccode,mt_depotcode,new_how,new_avgprice from mt_base where active_flag=1 order by mt_ccode;
begin
--打开游标
open c_mt_base;
fetch c_mt_base into mt_code,mt_ccode,mt_depotcode,forward_how,forward_avgprice;
while c_mt_base%found loop
insert into mt_base_forward(AUTO_ID,MT_CODE,MT_CCODE,MT_DEPOTCODE,FORWARD_HOW,FORWARD_AVGPRICE,FORWARD_DATE,ACTIVE_FLAG)
values(SEQ_MT_BASE_FORWARD.NEXTVAL,mt_code,mt_ccode,mt_depotcode,forward_how,forward_avgprice,sysdate,1);
fetch c_mt_base into mt_code,mt_ccode,mt_depotcode,forward_how,forward_avgprice;
end loop;
close c_mt_base;
commit;
end P_BASE_FORWARD;
------end
-----主键的生成机制 返回值 begin
declare
j number:=2;
i number:=1; ---定义序列的环境变量
v_sql varchar2(5000):='select sque_1.nextval from dual'; ---定义生成的序列的语句
begin
execute immediate v_sql into i; --- 立即执行 （execute immediate） v_sql 返回序列值
j:=j+i;
dbms_output.put_line(j);
end;
------end
-----主从表插入数据 的存储过程 执行 begin
create or replace procedure ADD_WT_CHECKSYB (
CHECK_PDPC in wt_check.pdpc%type,
tag out number -- 成功 tag:0 失败 tag:-1
)
is
spbh wt_checksyb_py.spbh%type; ---定义环境变量
ypmc wt_checksyb_py.ypmc%type;
ypgg wt_checksyb_py.ypgg%type;
jldw1 wt_checksyb_py.jldw1%type;
pjdj wt_checksyb_py.pjdj%type;
kczl wt_checksyb_py.kczl%type;
zmje wt_checksyb_py.zmje%type;
yksl wt_checksyb_py.yksl%type;
ykje wt_checksyb_py.ykje%type;
xulie number;
v_sql varchar2(5000):='select SEQ_WT_CHECKSYB.nextval from dual';
cursor c_checksyb_py is ---盈亏表
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.yksl ,B.ykje
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Yksl<>0 and a.pdpc=CHECK_PDPC;
begin
---生成盘点损益表主表
execute immediate v_sql into xulie; ---生成序列
dbms_output.put_line('序列==='||xulie);
insert into wt_checksyb (pdsybid,ypflbh,ypflmc,pdpc,pdsj,pdr,pddw ) ---- INSERT INTO SELECT语句 （增加 查询的语句（子查询） 的一种处理方式）
select xulie,ypflbh,ypflmc,pdpc,pdsj,pdr,pddw from wt_check where pdpc=CHECK_PDPC;
---生成盘点损益表--盘盈明细表
open c_checksyb_py; ---游标的使用 打开游标
fetch c_checksyb_py into spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje; ----提取游标中的数据
while c_checksyb_py%FOUND ---判断条件
Loop ----循环开始
insert into wt_checksyb_py(pymxbid,pdsybid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje )
values(SEQ_WT_CHECKSYB_PY.NEXTVAL,xulie,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje );
fetch c_checksyb_py into spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje; ---提取下一行数据
End loop; ----循环结束
close c_checksyb_py; ----关闭游标
commit; ----事务提交
tag:=0;
exception
when others then
tag:=-1;
rollback; ----异常的话 事务回滚
end ADD_WT_CHECKSYB ;
-----end
------相关存储过程
-----生成盘盈主从表
create or replace procedure ADD_WT_CHECKSYB_PY (
CHECK_PDPC in wt_check.pdpc%type,
tag out number -- 成功 tag:0 失败 tag:-1
)
is
spbh wt_checksyb_py_main.spbh%type;
ypmc wt_checksyb_py_main.ypmc%type;
ypgg wt_checksyb_py_main.ypgg%type;
jldw1 wt_checksyb_py_main.jldw1%type;
pjdj wt_checksyb_py_main.pjdj%type;
kczl wt_checksyb_py_main.kczl%type;
zmje wt_checksyb_py_main.zmje%type;
yksl wt_checksyb_py_main.yksl%type;
ykje wt_checksyb_py_main.ykje%type;
pyxl number;
v_sql varchar2(5000):='select SEQ_WT_CHECKSYB_PY.NEXTVAL from dual';
cursor c_checksyb_py is ---盘盈表主表
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.yksl ,B.ykje
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Yksl>0 and a.pdpc=CHECK_PDPC;
begin
---生成盘盈主表
execute immediate v_sql into pyxl;
insert into wt_checksyb_py (pyzbid,ypflbh,ypflmc,pdpc,syblxbh,syblxmc,pdsj,pddw,pdr)
select pyxl,ypflbh,ypflmc,pdpc,'1','盘盈',pdsj,pddw,pdr from wt_check where pdpc=CHECK_PDPC;
---生成盘盈明细表
open c_checksyb_py;
fetch c_checksyb_py into spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje;
while c_checksyb_py%FOUND
Loop
insert into wt_checksyb_py_main(pymxbid,pyzbid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje )
values(SEQ_WT_CHECKSYB_PY_MAIN.NEXTVAL,pyxl,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje );
fetch c_checksyb_py into spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje;
End loop;
close c_checksyb_py;
commit;
tag:=0;
exception
when others then
tag:=-1;
rollback;
end ADD_WT_CHECKSYB_PY ;
------生成盘亏主从表
create or replace procedure ADD_MT_CHECKSYB_PK1(
CHECK_PDPC in wt_check.pdpc%type,
tag out number ----生成盘亏表 成功 tag:0 失败 tag:-1
) is
spbh1 wt_checksyb_pk1_main.spbh%type;
ypmc1 wt_checksyb_pk1_main.ypmc%type;
ypgg1 wt_checksyb_pk1_main.ypgg%type;
jldw11 wt_checksyb_pk1_main.jldw1%type;
pjdj1 wt_checksyb_pk1_main.pjdj%type;
kczl1 wt_checksyb_pk1_main.kczl%type;
zmje1 wt_checksyb_pk1_main.zmje%type;
yksl1 wt_checksyb_pk1_main.yksl%type;
ykje1 wt_checksyb_pk1_main.ykje%type;
pkxl number;
v_sql2 varchar2(5000):='select SEQ_WT_CHECKSYB_PK1.nextval from dual';
cursor c_checksyb_pk is
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.yksl ,B.ykje
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Yksl<0 and a.pdpc=CHECK_PDPC;
begin
----生成盘亏主表
execute immediate v_sql2 into pkxl;
dbms_output.put_line('盘亏序列=='||pkxl);
insert into WT_CHECKSYB_PK1(pkzbid,ypflbh,ypflmc,pdpc,syblxbh,syblxmc,pdsj,pddw,pdr )
select pkxl,ypflbh,ypflmc,pdpc,'2','盘亏',pdsj,pddw,pdr from wt_check where pdpc=CHECK_PDPC;
----生成盘亏明细表
open c_checksyb_pk;
fetch c_checksyb_pk into spbh1,ypmc1,ypgg1,jldw11,pjdj1,kczl1,zmje1,yksl1,ykje1;
while c_checksyb_pk%FOUND
LOOP
insert into wt_checksyb_pk1_main(pkmxbid,pkzbid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje)
values(SEQ_WT_CHECKSYB_PK1_MAIN.NEXTVAL,pkxl,spbh1,ypmc1,ypgg1,jldw11,pjdj1,kczl1,zmje1,yksl1,ykje1);
fetch c_checksyb_pk into spbh1,ypmc1,ypgg1,jldw11,pjdj1,kczl1,zmje1,yksl1,ykje1;
end loop;
close c_checksyb_pk;
----异常处理
commit;
tag:=0;
exception
when others then
tag:=-1;
rollback;
end ADD_MT_CHECKSYB_PK1;
----生成损耗主从表
create or replace procedure ADD_WT_CHECKSYB_SH(
CHECK_PDPC in wt_check.pdpc%type,
tag out number --------生成盘点 损耗表 成功 tag:0 失败 tag:-1
) is
spbh2 wt_checksyb_sh_main.spbh%type;
ypmc2 wt_checksyb_sh_main.ypmc%type;
ypgg2 wt_checksyb_sh_main.ypgg%type;
jldw12 wt_checksyb_sh_main.jldw1%type;
pjdj2 wt_checksyb_sh_main.pjdj%type;
kczl2 wt_checksyb_sh_main.kczl%type;
zmje2 wt_checksyb_sh_main.zmje%type;
shsl2 wt_checksyb_sh_main.shsl%type;
shje2 wt_checksyb_sh_main.shje%type;
shyy2 wt_checksyb_sh_main.ykyy%type;
shxl number;
v_sql3 varchar2(5000):='select SEQ_WT_CHECKSYB_SH.NEXTVAL from dual';
cursor c_checksyb_sh is
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.shsl ,B.Shje,B.Shyy
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Shsl>0 and a.pdpc=CHECK_PDPC;
begin
----生成损耗主表
execute immediate v_sql3 into shxl;
dbms_output.put_line('损耗序列=='||shxl);
insert into wt_checksyb_sh(shzbid,ypflbh,ypflmc,pdpc,syblxbh,syblxmc,pdsj,pddw,pdr )
select shxl,ypflbh,ypflmc,pdpc,'3','损耗',pdsj,pddw,pdr from wt_check where pdpc=CHECK_PDPC;
----生成损耗明细表
open c_checksyb_sh;
fetch c_checksyb_sh into spbh2,ypmc2,ypgg2,jldw12,pjdj2,kczl2,zmje2,shsl2,shje2,shyy2;
while c_checksyb_sh%FOUND
loop
insert into wt_checksyb_sh_main(shmxbid,shzbid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,shsl,shje,ykyy)
values(SEQ_WT_CHECKSYB_SH_MAIN.NEXTVAL,shxl,spbh2,ypmc2,ypgg2,jldw12,pjdj2,kczl2,zmje2,shsl2,shje2,shyy2 );
fetch c_checksyb_sh into spbh2,ypmc2,ypgg2,jldw12,pjdj2,kczl2,zmje2,shsl2,shje2,shyy2;
end loop;
close c_checksyb_sh;
----异常处理
commit;
tag:=0;
exception
when others then
tag:=-1;
rollback;
end ADD_WT_CHECKSYB_SH;
-----生成报表存储过程（盘盈,盘亏,损耗报表）
create or replace procedure CREAT_CHECK_REPORTFORMS(
CHECK_PDPC in wt_check.pdpc%type,
tag out number ----生成盘亏表 成功 tag:0 失败 tag:-1
) is
spbh wt_checksyb_py_main.spbh%type;
ypmc wt_checksyb_py_main.ypmc%type;
ypgg wt_checksyb_py_main.ypgg%type;
jldw1 wt_checksyb_py_main.jldw1%type;
pjdj wt_checksyb_py_main.pjdj%type;
kczl wt_checksyb_py_main.kczl%type;
zmje wt_checksyb_py_main.zmje%type;
yksl wt_checksyb_py_main.yksl%type;
ykje wt_checksyb_py_main.ykje%type;
spbh1 wt_checksyb_pk1_main.spbh%type;
ypmc1 wt_checksyb_pk1_main.ypmc%type;
ypgg1 wt_checksyb_pk1_main.ypgg%type;
jldw11 wt_checksyb_pk1_main.jldw1%type;
pjdj1 wt_checksyb_pk1_main.pjdj%type;
kczl1 wt_checksyb_pk1_main.kczl%type;
zmje1 wt_checksyb_pk1_main.zmje%type;
yksl1 wt_checksyb_pk1_main.yksl%type;
ykje1 wt_checksyb_pk1_main.ykje%type;
spbh2 wt_checksyb_sh_main.spbh%type;
ypmc2 wt_checksyb_sh_main.ypmc%type;
ypgg2 wt_checksyb_sh_main.ypgg%type;
jldw12 wt_checksyb_sh_main.jldw1%type;
pjdj2 wt_checksyb_sh_main.pjdj%type;
kczl2 wt_checksyb_sh_main.kczl%type;
zmje2 wt_checksyb_sh_main.zmje%type;
shsl2 wt_checksyb_sh_main.shsl%type;
shje2 wt_checksyb_sh_main.shje%type;
shyy2 wt_checksyb_sh_main.ykyy%type;
pyxl number;
v_sql varchar2(5000):='select SEQ_WT_CHECKSYB_PY.NEXTVAL from dual';
cursor c_checksyb_py is ---盘盈表主表
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.yksl ,B.ykje
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Yksl>0 and a.pdpc=CHECK_PDPC;
pkxl number;
v_sql2 varchar2(5000):='select SEQ_WT_CHECKSYB_PK1.nextval from dual';
cursor c_checksyb_pk is
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.yksl ,B.ykje
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Yksl<0 and a.pdpc=CHECK_PDPC;
shxl number;
v_sql3 varchar2(5000):='select SEQ_WT_CHECKSYB_SH.NEXTVAL from dual';
cursor c_checksyb_sh is
select B.spbh ,B.ypmc ,B.ypgg ,B.jldw1 ,B.pjdj ,B.kczl ,B.zmje ,B.shsl ,B.Shje,B.Shyy
from wt_check A, wt_check_main B where A.PDBID=B.PDBID and B.Shsl>0 and a.pdpc=CHECK_PDPC;
begin
---生成盘盈表
execute immediate v_sql into pyxl;
insert into wt_checksyb_py (pyzbid,ypflbh,ypflmc,pdpc,syblxbh,syblxmc,pdsj,pddw,pdr)
select pyxl,ypflbh,ypflmc,pdpc,'1','盘盈',pdsj,pddw,pdr from wt_check where pdpc=CHECK_PDPC;
open c_checksyb_py;
fetch c_checksyb_py into spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje;
while c_checksyb_py%FOUND
Loop
insert into wt_checksyb_py_main(pymxbid,pyzbid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje )
values(SEQ_WT_CHECKSYB_PY_MAIN.NEXTVAL,pyxl,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje );
fetch c_checksyb_py into spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje;
End loop;
close c_checksyb_py;
----生成盘亏表
execute immediate v_sql2 into pkxl;
insert into WT_CHECKSYB_PK1(pkzbid,ypflbh,ypflmc,pdpc,syblxbh,syblxmc,pdsj,pddw,pdr )
select pkxl,ypflbh,ypflmc,pdpc,'2','盘亏',pdsj,pddw,pdr from wt_check where pdpc=CHECK_PDPC;
open c_checksyb_pk;
fetch c_checksyb_pk into spbh1,ypmc1,ypgg1,jldw11,pjdj1,kczl1,zmje1,yksl1,ykje1;
while c_checksyb_pk%FOUND
LOOP
insert into wt_checksyb_pk1_main(pkmxbid,pkzbid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,yksl,ykje)
values(SEQ_WT_CHECKSYB_PK1_MAIN.NEXTVAL,pkxl,spbh1,ypmc1,ypgg1,jldw11,pjdj1,kczl1,zmje1,yksl1,ykje1);
fetch c_checksyb_pk into spbh1,ypmc1,ypgg1,jldw11,pjdj1,kczl1,zmje1,yksl1,ykje1;
end loop;
close c_checksyb_pk;
----生成损耗表
execute immediate v_sql3 into shxl;
insert into wt_checksyb_sh(shzbid,ypflbh,ypflmc,pdpc,syblxbh,syblxmc,pdsj,pddw,pdr )
select shxl,ypflbh,ypflmc,pdpc,'3','损耗',pdsj,pddw,pdr from wt_check where pdpc=CHECK_PDPC;
open c_checksyb_sh;
fetch c_checksyb_sh into spbh2,ypmc2,ypgg2,jldw12,pjdj2,kczl2,zmje2,shsl2,shje2,shyy2;
while c_checksyb_sh%FOUND
loop
insert into wt_checksyb_sh_main(shmxbid,shzbid,spbh,ypmc,ypgg,jldw1,pjdj,kczl,zmje,shsl,shje,ykyy)
values(SEQ_WT_CHECKSYB_SH_MAIN.NEXTVAL,shxl,spbh2,ypmc2,ypgg2,jldw12,pjdj2,kczl2,zmje2,shsl2,shje2,shyy2);
fetch c_checksyb_sh into spbh2,ypmc2,ypgg2,jldw12,pjdj2,kczl2,zmje2,shsl2,shje2,shyy2;
end loop;
close c_checksyb_sh;
commit;
tag:=0;
exception
when others then
tag:=-1;
rollback;
end CREAT_CHECK_REPORTFORMS;
--生成库存调整表
create or replace procedure CREAT_STOCKS(
wt_pdpc wt_stock_change.pdpc%type ,
tag out number
) is
spbh_w wt_stock_changexx.spbh%type;
ypmc_w wt_stock_changexx.ypmc%type;
ypgg_w wt_stock_changexx.ypgg%type;
jldw_w wt_stock_changexx.jldw1%type;
pjdj_w wt_stock_changexx.pjdj%type;
kczl_w wt_stock_changexx.kczl%type;
zmje_w wt_stock_changexx.zmje%type;
sjsl_w wt_stock_changexx.sjsl%type;
sjje_w wt_stock_changexx.sjje%type;
yksl_w wt_stock_changexx.yksl %type;
ykje_w wt_stock_changexx.ykje%type;
shsl_w wt_stock_changexx.shsl%type;
shje_w wt_stock_changexx.shje%type;
tzsl_w wt_stock_changexx.tzsl%type;
tzje_w wt_stock_changexx.tzje%type;
tzb_ykjehj wt_stock_change.ykjehj%type;
tzb_shjehj wt_stock_change.shjehj%type;
tzb_tzjehj wt_stock_change.tzjehj%type;
kctzxl number;
v_sql1 varchar2(5000):='select SEQ_WT_STOCK_CHANGE.nextval from dual';
cursor change_c is
select A.Spbh,A.ypmc,A.ypgg,A.Jldw1,A.Pjdj,A.kczl,A.Zmje,A.Sjsl,A.Sjje,nvl(B.Ykslsp,0)+nvl(C.Ykslsp,0) As yksl,
nvl(B.Ykjesp,0)+nvl(C.Ykjesp,0) as ykje,nvl(D.Shslsp,0) as shsl,nvl(D.Shjesp,0) as shje,
nvl(B.Ykslsp,0)+nvl(C.Ykslsp,0)-nvl(D.Shslsp,0) As tzsl,nvl(B.Ykjesp,0)-nvl(C.Ykjesp,0)+nvl(D.Shjesp,0) as tzje
from wt_check_main A left join wt_checksyb_py_main B on A.spbh=B.Spbh
left join wt_checksyb_pk1_main C on A.spbh=C.Spbh
left join wt_checksyb_sh_main D on A.Spbh=D.Spbh
where A.PDBID=(select E.Pdbid from wt_check E where E.Pdpc=wt_pdpc);
begin
---生成库存调整表
execute immediate v_sql1 into kctzxl;
select sum(ykje),sum(shje), sum(tzje) into tzb_ykjehj,tzb_shjehj,tzb_tzjehj from
(select A.Spbh,A.ypmc,A.ypgg,A.Jldw1,A.Pjdj,A.kczl,A.Zmje,A.Sjsl,A.Sjje,nvl(B.Ykslsp,0)+nvl(C.Ykslsp,0) As yksl,
nvl(B.Ykjesp,0)+nvl(C.Ykjesp,0) as ykje,nvl(D.Shslsp,0) as shsl,nvl(D.Shjesp,0) as shje,
nvl(B.Ykslsp,0)+nvl(C.Ykslsp,0)-nvl(D.Shslsp,0) As tzsl,nvl(B.Ykjesp,0)+nvl(C.Ykjesp,0)-nvl(D.Shjesp,0) as tzje
from wt_check_main A left join wt_checksyb_py_main B on A.spbh=B.Spbh
left join wt_checksyb_pk1_main C on A.spbh=C.Spbh
left join wt_checksyb_sh_main D on A.Spbh=D.Spbh
where A.PDBID=(select E.Pdbid from wt_check E where E.Pdpc='20090909')) order by ykje, shje, tzje;
insert into wt_stock_change (Kctzid,Ypflbh,Ypflmc,Pddw,Pdpc,Pdsj,Pdr,Active_Flag,ykjehj,shjehj,tzjehj)
select kctzxl, A.Ypflbh,A.Ypflmc,A.Pddw,A.Pdpc,A.Pdsj,A.Pdr,0,tzb_ykjehj,tzb_shjehj,tzb_tzjehj from wt_check A where A.Pdpc=wt_pdpc;
open change_c;
fetch change_c into spbh_w,ypmc_w,ypgg_w,jldw_w,pjdj_w,kczl_w,zmje_w,sjsl_w,sjje_w ,yksl_w,ykje_w,shsl_w,shje_w,tzsl_w,tzje_w;
while change_c%FOUND
Loop
insert into wt_stock_changexx (KCTZMXID,kctzid,spbh,Ypmc,ypgg,Jldw1,Pjdj,Kczl,Zmje,Sjsl,Sjje, Yksl, Ykje, Shsl, Shje, Tzsl, Tzje,Tzyy)
values (SEQ_WT_STOCK_CHANGEXX.NEXTVAL,kctzxl,spbh_w,ypmc_w,ypgg_w,jldw_w,pjdj_w,kczl_w,zmje_w,sjsl_w,sjje_w,yksl_w,ykje_w,shsl_w,shje_w,tzsl_w,tzje_w,'www');
fetch change_c into spbh_w,ypmc_w,ypgg_w,jldw_w,pjdj_w,kczl_w,zmje_w,sjsl_w,sjje_w,yksl_w,ykje_w,shsl_w,shje_w,tzsl_w,tzje_w;
End loop;
close change_c;
update wt_checksyb_py A set A.Active_Flag=1 where A.Pdpc=wt_pdpc;
update wt_checksyb_pk1 A set A.Active_Flag=1 where A.pdpc=wt_pdpc;
update wt_checksyb_sh A set A.Active_Flag=1 where A.Pdpc=wt_pdpc;
commit;
tag:=0;
exception
when others then
tag:=-1;
rollback;
end CREAT_STOCKS;
------*******进行库存调整操作
create or replace procedure EDIT_STOCK_CHANGE(
CHECK_PDPC in wt_stock_change.pdpc%type,
tag out number
) is
spbh1 wt_bgypb.spbh%type;
tzsl1 wt_bgypb.kczl%type;
cursor c_stock_change is
select B.Spbh,B.Tzsl from wt_stock_change A, wt_stock_changexx B
where A.KCTZID=B.KCTZID and A.Pdpc=CHECK_PDPC;
begin
-----库存调整
open c_stock_change;
fetch c_stock_change into spbh1,tzsl1;
while c_stock_change%FOUND
loop
update wt_bgypb A set A.kczl=A.kczl+nvl(tzsl1,0) where A.SPBH=to_char(spbh1);
fetch c_stock_change into spbh1,tzsl1;
end loop;
close c_stock_change;
update wt_stock_change M set M.Active_Flag=1 where M.PDPC=CHECK_PDPC;
commit;
tag:=0;
exception
when others then
tag:=-1;
rollback;
end EDIT_STOCK_CHANGE;
----注意：变量定义不能跟数据库字段名一样 ，否则sql语句判断会出现混乱，无法识别到底是 变量 ，还是数据库字段
------***end***
-----函数生成流水号
create or replace function CREAT_PDBLSH(
Name in varchar )
return varchar
is
yearMonth varchar(10);
yearCount varchar(10);
monthCount varchar(10);
pdpclsh varchar(10);
begin
---生成盘点序列号 生成规则: 200908(年月)-10(年度次数)-01(月次数)
select to_char(sysdate,'yyyyMM') into yearMonth from dual;
select lpad(count(*)+1,2,'0') into yearCount
from wt_check A where to_char(A.pdsj,'yyyy') in (select to_char(sysdate,'yyyy') from dual);
select lpad(count(*)+1,2,'0') into monthCount
from wt_check A where to_char(A.pdsj,'yyyyMM')in (select to_char(sysdate,'yyyyMM') from dual) ;
pdpclsh:=yearMonth||yearCount||monthCount;
return(pdpclsh);
end CREAT_PDBLSH;
----**********注：存储过程和函数的调用
----使用‘存储过程’生成报表
public int creatReportForms(String pdpc) throws Exception {
// TODO Auto-generated method stub
int tag=-1;
CallableStatement pre= null;
Session session=this.getSession();
try {
pre= session.connection().prepareCall("{call CREAT_CHECK_REPORTFORMS(?,?)}");
pre.setString(1,pdpc);
pre.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);
pre.execute();
tag=pre.getInt(2);
} catch (RuntimeException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("tag=========="+tag);
return tag;
}
-----函数的使用
-----获取流水号
public String getPdLsh() throws Exception {
// TODO Auto-generated method stub
String lsh="0";
CallableStatement pre=null;
Session session=this.getSession();
try {
pre=session.connection().prepareCall("{ ?= call CREAT_PDBLSH }");
pre.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR);
pre.execute();
lsh=pre.getString(1);
} catch (RuntimeException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return lsh;
}
-----***********end
----触发器的使用例子
create or replace trigger dml_log
before
delete or insert or update
on emp2
for each row
begin
if inserting then
insert into logs
values(log_id_squ.nextval,'emp2','insert',:new.empno,sysdate,user);
elsif deleting then
insert into logs
values(log_id_squ.nextval,'emp2','delete',:new.empno,sysdate,user);
else
insert into logs
values(log_id_squ.nextval,'emp2','update',:new.empno,sysdate,user);
end if;
end;
Oracle总结
扫一扫
260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
