【SQL】工作中的存储过程
程序员文章站
2022-05-08 18:42:32
...
工作中做了几个存储过程。
建立了几个零时表：
对应的procedure。
建立了几个零时表：
-- Create table
create table D_20190129
(
billno VARCHAR2(20),
transtime DATE,
sap_status VARCHAR2(10),
sap_note VARCHAR2(100),
sap_result VARCHAR2(100)
)
tablespace H2DB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table D_20190130
(
billno VARCHAR2(20),
transtime DATE,
sap_status VARCHAR2(10),
sap_note VARCHAR2(100),
sap_result VARCHAR2(100)
)
tablespace H2DB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table D_20190131
(
memcardno VARCHAR2(40) not null,
busno NUMBER(10) not null,
saleamount NUMBER(14,4) not null,
realamount NUMBER(14,4) not null,
puramount NUMBER(14,4) not null,
integral NUMBER(14,4) not null,
integrala NUMBER(14,4),
cardholder VARCHAR2(40),
mobile VARCHAR2(20),
createtime DATE not null,
lasttime DATE,
integralsum NUMBER(16,4),
cutmonth VARCHAR2(8)
)
tablespace H2DB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table D_20190132
(
busno VARCHAR2(10),
busname VARCHAR2(40),
warecode VARCHAR2(20),
warename VARCHAR2(80),
wareqty NUMBER,
awaitqty NUMBER,
makeno VARCHAR2(20),
stallname VARCHAR2(10),
applyno VARCHAR2(20),
objbusno VARCHAR2(20),
dj_execdate DATE,
pro_execdate DATE,
apptype VARCHAR2(10),
note VARCHAR2(20)
)
tablespace H2DB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
对应的procedure。
create or replace procedure hydee_sap_check_qh
as
cout integer := 0;
cout1 integer := 0;
cout2 integer := 0;
cout3 integer := 0;
status varchar2(1);
note varchar2(100);
begin
--首先查询t_20190129表中的数据是否已经被执行，即其请货单已经存在配送单情况
for t in (select * from d_20190129) loop
select count(1) into cout from t_distapply_h where billcode='APP' and compid=2 and status=1 and distno is null and applyno=t.billno;
if cout=0
then
delete from d_20190129 where billno=t.billno;
commit;
else
DBMS_OUTPUT.put_line('ok');
end if;
end loop;
-- 查询2019年1月1日到当前时间2天内的请货单没有配送单信息
FOR x IN (select billno,transtime from T_SAP_QUEUE_R where tableobj='T_SAP_PO_CRE_R'and status=2 and billno in (select applyno from t_distapply_h where billcode='APP' and compid=2 and status=1 and execdate>to_date('2019-01-01','yyyy-mm-dd') and execdate<trunc(sysdate-2) and distno is null) order by transtime) LOOP
--如果此单信息已经存在t_20190129表中则不进行处理，如有增加则插入
SELECT count(1) into cout1 FROM t_distapply_h t_distapply_h WHERE billcode = 'APP' AND compid=2 and t_distapply_h.notes='从'||x.billno||'复制得来';
if cout1=0
then select count(1) into cout2 from d_20190129 where billno=x.billno;
if cout2=0
then insert into d_20190129(billno,transtime) values(x.billno,x.transtime);
commit;
else
DBMS_OUTPUT.put_line('ok');
end if;
--查询sap下发中间表状态及信息并插入t_20190129表
SELECT count(1) into cout3 FROM t_sap_queue a left join T_SAP_BILL_IN b on b.pkid=a.pkid WHERE b.storebillno=x.billno;
if cout3>0
then select a.status,a.notes into status,note FROM t_sap_queue a left join T_SAP_BILL_IN b on b.pkid=a.pkid WHERE b.storebillno=x.billno;
update d_20190129 set sap_status=status,sap_note=note where billno=x.billno;
commit;
else
DBMS_OUTPUT.put_line('ok');
end if;
else
DBMS_OUTPUT.put_line('ok');
end if;
END LOOP;
end;
create or replace procedure hydee_sap_check_djdb
as
cout integer := 0;
cout1 integer := 0;
cout2 integer := 0;
status varchar2(1);
note varchar2(100);
begin
--首先查询t_20190130表中的数据是否已经被执行，即其请货单已经存在配送单情况
for t in (select * from d_20190130) loop
-- select count(1) into cout from t_distapply_h where billcode='APP' and compid=2 and status=1 and distno is null and applyno=t.billno;
SELECT count(1) into cout FROM t_dist_h WHERE billcode = 'DSSM' AND compid=2 and status=0 and checkbit2=1 and checkbit3=0 and distno=t.billno and lasttime>to_date('2019-01-01','yyyy-mm-dd');
if cout=0
then
delete from d_20190130 where billno=t.billno;
commit;
else
DBMS_OUTPUT.put_line('ok');
end if;
end loop;
-- 查询及今年内店间调拨单sap没有执行的数据
for x in ( SELECT distno,lasttime FROM t_dist_h WHERE billcode = 'DSSM' AND compid=2 and status=0 and checkbit2=1 and checkbit3=0 and lasttime>to_date('2019-01-01','yyyy-mm-dd')) loop
--如果此单信息已经存在t_20190130表中则不进行处理，如有增加则插入
select count(1) into cout1 from d_20190130 where billno=x.distno;
if cout1=0
then insert into d_20190130(billno,transtime) values(x.distno,x.lasttime);
commit;
else
DBMS_OUTPUT.put_line('订单已存在');
end if;
--查询sap下发中间表状态及信息并插入t_20190130表
SELECT count(1) into cout2 FROM t_sap_queue a,T_SAP_BILL_IN b where a.tableobj='T_SAP_BILL_IN' and a.pkid=b.pkid and b.refbillno=x.distno;
--select * from t_sap_queue a,T_SAP_BILL_IN b where a.tableobj='T_SAP_BILL_IN' and a.pkid=b.pkid and b.refbillno='店间调拨单号';
if cout2>0
then select distinct a.status,a.notes into status,note FROM t_sap_queue a,T_SAP_BILL_IN b where a.tableobj='T_SAP_BILL_IN' and a.pkid=b.pkid and b.refbillno=x.distno;
update d_20190130 set sap_status=status,sap_note=note where billno=x.distno;
commit;
else
DBMS_OUTPUT.put_line('ok');
end if;
end loop;
end;
create or replace procedure hydee_memcard_month_cut as
begin
for u in (SELECT memcardno,
busno,
saleamount,
realamount,
puramount,
integral,
integrala,
cardholder,
mobile,
createtime,
lasttime,
integralsum,
to_char(add_months(trunc(sysdate), -1), 'yyyymm') as cutmonth
FROM t_memcard_reg)
loop
insert into d_20190131
(memcardno,
busno,
saleamount,
realamount,
puramount,
integral,
integrala,
cardholder,
mobile,
createtime,
lasttime,
integralsum,
cutmonth)
values
(u.memcardno,
u.busno,
u.saleamount,
u.realamount,
u.puramount,
u.integral,
u.integrala,
u.cardholder,
u.mobile,
u.createtime,
u.lasttime,
u.integralsum,
u.cutmonth);
commit;
end loop;
end;
create or replace procedure hydee_sap_check_dck as
tc_count integer := 0;
bsby_count integer := 0;
djdb_count integer := 0;
applyno varchar(20);--退仓单号
objbusno varchar(10);
dj_execdate date;
abnormityno varchar(20);--报损报溢单号
distno varchar(20);--店间调拨单单号
begin
--清空记录表
EXECUTE IMMEDIATE 'truncate table d_20190132';
--查询所有门店待出库商品及相关信息
for c1 in (SELECT d.compid,d.busno,sz.zonename,vw.warecode,vw.warename,d.wareqty,d.awaitqty,i.makeno,ts.stallname stallname FROM t_store_d d JOIN t_ware vw
ON d.wareid = vw.wareid AND d.compid = vw.compid LEFT JOIN t_stall ts ON d.stallno = ts.stallno AND d.compid = ts.compid AND d.busno = ts.busno
LEFT JOIN s_zone sz ON ts.zoneno = sz.zoneno AND ts.compid = sz.compid LEFT JOIN t_store_i i ON d.compid = i.compid AND d.wareid = i.wareid
AND d.batid = i.batid WHERE d.compid = 2 and awaitqty > 0 and length(d.busno) = 6) loop
--首先查询退仓单是否存在
SELECT count(1) into tc_count FROM t_distapply_d JOIN t_distapply_h ON t_distapply_d.applyno = t_distapply_h.applyno LEFT JOIN v_ware_base
ON t_distapply_d.wareid = v_ware_base.wareid AND t_distapply_h.compid = v_ware_base.compid WHERE t_distapply_h.billcode = 'RAP'
AND t_distapply_h.compid = 2 and t_distapply_h.retdistno is null and (t_distapply_h.srcbusno = c1.busno and v_ware_base.warecode = c1.warecode and t_distapply_h.status = 1) and applyqty=c1.awaitqty and makeno=c1.makeno;
if tc_count=1
then
SELECT t_distapply_d.applyno ,t_distapply_h.objbusno, t_distapply_h.lasttime into applyno, objbusno,dj_execdate FROM t_distapply_d JOIN t_distapply_h ON t_distapply_d.applyno = t_distapply_h.applyno
LEFT JOIN v_ware_base ON t_distapply_d.wareid = v_ware_base.wareid AND t_distapply_h.compid = v_ware_base.compid WHERE t_distapply_h.billcode = 'RAP'
AND t_distapply_h.compid = 2 and t_distapply_h.retdistno is null and (t_distapply_h.srcbusno = c1.busno and v_ware_base.warecode = c1.warecode and t_distapply_h.status = 1) and applyqty=c1.awaitqty and makeno=c1.makeno;
insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,applyno,objbusno,apptype,dj_execdate,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,applyno
,objbusno,'退仓单',dj_execdate,sysdate);
commit;
elsif tc_count>1
then
insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,apptype,note,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,'退仓单','需要手工执行',sysdate);
commit;
else
DBMS_OUTPUT.put_line('其他问题');
end if;
---其次查询报损报溢单是否存在
SELECT count(1) into bsby_count FROM t_abnormity_d t_abnormity_d JOIN t_abnormity_h t_abnormity_h ON t_abnormity_d.abnormityno = t_abnormity_h.abnormityno
and t_abnormity_h.billcode = 'ABN' LEFT JOIN v_ware_base v_ware_base ON t_abnormity_d.wareid = v_ware_base.wareid AND t_abnormity_h.compid = v_ware_base.compid
left join s_busi s_busi on t_abnormity_h.busno = s_busi.busno and t_abnormity_h.compid = s_busi.compid WHERE t_abnormity_h.compid = 2
and (v_ware_base.warecode = c1.warecode and makeno=c1.makeno and wareqtyb=c1.awaitqty and s_busi.busno=c1.busno);
if bsby_count=1
then
SELECT t_abnormity_d.abnormityno,t_abnormity_h.lasttime into abnormityno,dj_execdate FROM t_abnormity_d t_abnormity_d
JOIN t_abnormity_h t_abnormity_h ON t_abnormity_d.abnormityno = t_abnormity_h.abnormityno and t_abnormity_h.billcode = 'ABN' LEFT JOIN v_ware_base v_ware_base
ON t_abnormity_d.wareid = v_ware_base.wareid AND t_abnormity_h.compid = v_ware_base.compid left join s_busi s_busi
on t_abnormity_h.busno = s_busi.busno and t_abnormity_h.compid = s_busi.compid WHERE t_abnormity_h.compid = 2
and (v_ware_base.warecode = c1.warecode and makeno=c1.makeno and wareqtyb=c1.awaitqty and s_busi.busno=c1.busno);
insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,applyno,dj_execdate,apptype,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,abnormityno,dj_execdate,'报损报溢单',sysdate);
commit;
elsif bsby_count>1
then
insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,apptype,note,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,'报损报溢单','需要手工执行',sysdate);
commit;
else
DBMS_OUTPUT.put_line('其他问题');
end if;
---最后查店间调拨单是否存在
SELECT count(1) into djdb_count FROM t_dist_d t_dist_d JOIN t_dist_h t_dist_h ON t_dist_d.distno = t_dist_h.distno and t_dist_h.billcode='DSSM'
LEFT JOIN v_ware_base t_ware ON t_dist_d.wareid = t_ware.wareid AND t_dist_h.compid = t_ware.compid WHERE
t_dist_h.compid=2 and ( t_dist_h.srcbusno =c1.busno and t_ware.warecode = c1.warecode and t_dist_h.status =0 and t_dist_d.makeno=c1.makeno and t_dist_d.wareqty=c1.awaitqty);
if djdb_count=1
then
SELECT t_dist_h.distno, t_dist_h.objbusno, t_dist_h.lasttime into distno,objbusno,dj_execdate FROM t_dist_d t_dist_d JOIN t_dist_h t_dist_h ON t_dist_d.distno = t_dist_h.distno and t_dist_h.billcode='DSSM'
LEFT JOIN v_ware_base t_ware ON t_dist_d.wareid = t_ware.wareid AND t_dist_h.compid = t_ware.compid WHERE
t_dist_h.compid=2 and ( t_dist_h.srcbusno =c1.busno and t_ware.warecode = c1.warecode and t_dist_h.status =0 and t_dist_d.makeno=c1.makeno and t_dist_d.wareqty=c1.awaitqty);
insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,applyno,objbusno,dj_execdate,apptype,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,distno,objbusno,dj_execdate,'店间调拨单',sysdate);
commit;
elsif djdb_count>1
then
insert into d_20190132(busno,busname,warecode,makeno,warename,wareqty,awaitqty,stallname,apptype,note,pro_execdate) values(c1.busno,c1.zonename,c1.warecode,c1.makeno,c1.warename,c1.wareqty,c1.awaitqty,c1.stallname,'店间调拨单','需要手工执行',sysdate);
commit;
else
DBMS_OUTPUT.put_line('其他问题');
end if;
end loop;
end;