--------单表插入 树形目录结构数据 （存储过程处理方式） 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总结
相关推荐
PLSQL Developer是一款强大的Oracle数据库开发工具，专为PL/SQL编程语言设计，它提供了一整套集成环境，便于开发、调试、管理以及执行PL/SQL代码。标题中的"PLSQL_Developer8.0绿色版64位"指的是该版本是针对64位...
PLSQL Developer是一款强大的Oracle数据库管理工具，主要针对PL/SQL语言进行开发、调试和管理。在32位操作系统环境下，用户可能需要下载适用于该系统的PLSQL Developer版本。本指南将详细介绍如何下载并安装32位...
PLSQL Developer 14用户指南 PLSQL Developer 14用户指南是Oracle公司出品的一款专业database开发工具，旨在帮助用户快速掌握PL/SQL语言编程。该指南详细介绍了PL/SQL Developer 14的安装、配置、编程和调试等方面...
**PlSql中的CnPlugin插件详解** PL/SQL Developer（简称PLSQL）是一款由Allround Automations公司开发的专业Oracle数据库管理与开发工具，它为数据库管理员和开发人员提供了强大的功能，包括编写、调试、执行SQL和...
PLSQL Developer是一款强大的Oracle数据库开发工具，主要用于编写、调试、执行和管理PL/SQL程序。在中文环境下，对于不熟悉英文界面的用户来说，使用英文版可能会带来不便。"plsql11汉化包"正是为了解决这个问题，...
PLSQL Developer是一款强大的Oracle数据库开发工具，主要用于编写、调试、执行和管理PL/SQL程序单元。这个"PLSQL14_32位"压缩包文件很可能是PLSQL Developer的第14版本，专为32位操作系统设计。如果你的计算机上已经...
PL/SQL Developer是一个集成开发环境，专门面向Oracle数据库存储程序单元的开发。... 该安装包包含了解决PLSQL过期的文件，将PLSQL安装后将解决过期的文件放到安装包中，即可解决PLSQL过期的问题。即可永久使用。
PLSQL程序优化和性能分析方法 PLSQL程序优化是指在编写PLSQL程序时，为了提高程序的执行效率和性能所采取的一系列措施和技术。这些措施和技术可以帮助开发者编写高效、可靠、可维护的PLSQL程序，从而提高系统的整体...
PLSQL Developer是一款强大的Oracle数据库开发工具，主要用于编写、调试、执行和管理PL/SQL代码。这个8.0版本的汉化包是专为那些需要中文界面的用户设计的，使得国内用户在使用PLSQL Developer时能更加方便地理解和...
PLSQL（Procedural Language/Structured Query Language）是Oracle数据库中的一个强大工具，它结合了SQL的查询功能和过程性编程语言的特点，使得数据库管理、数据处理和应用程序开发更为便捷。"PLSQL Developer"是一...
PLSQL Developer是一款强大的Oracle数据库开发工具，主要用于编写、调试、执行和管理PL/SQL代码。在8.0版本中，为了方便中文用户使用，官方或社区提供了汉化包，使得界面和帮助文档能够显示为中文，提高中国用户的...
PLSQL Developer是一款强大的Oracle数据库管理工具，尤其适合于开发和管理员工进行数据库操作。这款工具以其易用性和全面的功能而闻名，它允许用户编写、测试和调试PL/SQL代码，管理表、视图、存储过程等数据库对象...
plsql developer9 0破解版 PLSQL Developer 9 0 0 1601破解版本使用了8 0的汉化包 可以正常使用 无需注册 默认为英文 点击菜单tools preferences appearance language 选择简体中文即可切换到简体中文界面 如果...
PLSQL Developer是一款强大的Oracle数据库开发工具，专为64位操作系统设计。这款工具以其直观的用户界面、高效的代码编辑器和全面的调试功能而受到广大数据库管理员和开发者的青睐。在"PLSQLDeveloper_解压版(64位...
PLSQL Developer是一款强大的Oracle数据库开发工具，由Allround Automations公司开发。在本文中，我们将深入探讨PLSQL Developer 12的汉化版及其与Oracle数据库的交互，以及如何使用注册Key激活软件。 PLSQL ...
在Oracle数据库环境中，PL/SQL Developer（简称PLSQL）是一种常用的工具，用于编写、调试和管理PL/SQL代码。当我们需要处理大量数据导入任务时，PLSQL提供了多种方法来实现批量导入，这些方法高效且灵活。本文将详细...
PLSQL Developer是一款强大的Oracle数据库开发工具，主要用于编写、调试、测试和管理PL/SQL代码。这个版本是PLSQL Developer的11.0.5更新，特别指出为绿色版，意味着它是一个便携式版本，无需安装即可直接使用，且已...
PLSQL Developer是一款强大的Oracle数据库管理工具，主要用于编写、调试、执行和管理PL/SQL程序。在给定的压缩包文件“PLSQL 绿色版_免安装ORACLE.zip”中，用户可以找到一种无需正式安装Oracle客户端即可使用PLSQL ...
PLSQL Developer是一款强大的Oracle数据库开发工具，主要用于编写、调试、执行和管理PL/SQL代码。在许多情况下，用户可能需要PLSQL的免安装版本，这可能是出于便携性、节省系统资源或者避免系统冲突的考虑。标题...
PLSQL Developer是一款强大的Oracle数据库开发工具，专为64位操作系统设计。这款工具以其直观的用户界面、高效的代码编辑和调试功能，深受数据库管理员和开发人员喜爱。在64位系统上使用PLSQL Developer，可以充分...