常用的sql语句（存储过程语法）

1.存储过程语法

①package 

create or replace package PKG_RPT_WAREHOUSE is

-- Author :
-- Created : 2018/9/28 17:17:39
-- Purpose : 仓库统计报表

--生成每日库存统计
PROCEDURE GEN_WAREHOUSE_STORAGE;

end PKG_RPT_WAREHOUSE;

 ②package body 

create or replace package body PKG_RPT_WAREHOUSE is

--生成库存每日报表

PROCEDURE GEN_WAREHOUSE_STORAGE AS
BEGIN（

CREATE OR REPLACE PROCEDURE INVENTORY_FREEZING(v_own_uuid in varchar2) as
begin（带参数）

）
--0.删除当日无效数据
delete from RPT_WAREHOUSE_STORAGE t
where trunc(t.storage_date) = trunc(sysdate);

--1 insert into语句
insert into rpt_warehouse_storage_detail
(storage_date, area_code, pallet_code, pallet_type, warehouse_name)
select trunc(sysdate),
a.area_code,
t.pallet_code,
decode(instr(t.pallet_code, 'ES'), 1, 'SMALL', 'BIG'),
a.tj_area_name
from wm_inventory_detail_area t
left join wm_pallet w
on w.pallet_uuid = t.pallet_uuid
left join wm_warehouse_area a
on a.wwa_uuid = t.wwa_uuid
left join wm_in_label i
on i.ilb_uuid = t.ilb_uuid
and i.is_active = 'Y'
and i.inventory_qty > 0
where exists (select 1
from wm_in_label ww
where ww.ilb_uuid = t.ilb_uuid
and ww.is_active = 'Y'
and ww.inventory_qty > 0)
and t.is_active = 'Y'
and a.is_active = 'Y'
and a.tj_area_name = '康捷空仓库'
group by t.pallet_code, a.area_code, a.tj_area_name;
 
--2.修改语句
merge into (select *
from RPT_WAREHOUSE_STORAGE t
where t.warehouse_name = '康捷空仓库'
and t.storage_date = trunc(sysdate)) a
using (select sum(small_in) small_in,
sum(big_in) big_in,
'康捷空仓库' warehouse_name
from (select decode(t.pallet_type, 'SMALL', t.inventory_qty, 0) small_in,
decode(t.pallet_type, 'BIG', t.inventory_qty, 0) big_in
from RPT_EI_WAREHOUSE_DTL t
where trunc(t.count_date) = trunc(sysdate)
and not exists
(select 1
from RPT_EI_WAREHOUSE_DTL oi
where oi.il_unique_box_code = t.il_unique_box_code
and trunc(t.count_date - 1) =
trunc(oi.count_date))
when matched then
update set a.BIG_IN = b.BIG_IN, a.SMALL_IN = b.SMALL_IN;

END GEN_WAREHOUSE_STORAGE;

end PKG_RPT_WAREHOUSE;

 

转载于:https://www.cnblogs.com/cxks-xu/p/10448952.html