create or replace PROCEDURE 总部有货门店无货品种明细 ( V_SUBBH IN NVARCHAR2 --v_lsdl IN NVARCHAR2 ) AS v_code VARCHAR2(20); v_name VARCHAR2(20); begin EXECUTE IMMEDIATE 'TRUNCATE TABLE lyl_总部有货门店无货品种明细'; DECLARE CURSOR SUBPHK_CURSOR IS SELECT code,name from organization where nvl(ISFORBIDDEN,0)=0 and code<>'03010757' and code like '%V_SUBBH%' order by code; BEGIN OPEN SUBPHK_CURSOR; LOOP FETCH SUBPHK_CURSOR INTO v_code,v_name; --总部有货门店无货品种信息 insert into lyl_总部有货门店无货品种明细 (门店编号,门店名称,货号,商品名称) Select v_code,v_name,a.hh 货号,a.pm 商品名称 From yw A left join KCLB lb on lb.id=a.lb left join (SELECT HH,subbh FROM SUBPHK WHERE SL<>0 AND SUBBH=v_code group by HH,subbh having SUM(sl)>0) sub on sub.hh=a.hh where nvl(a.jksl,0)>0 and sub.subbh is null order by a.lb,a.hh; END LOOP; CLOSE SUBPHK_CURSOR; END; COMMIT; RETURN; END;
(
V_SUBBH IN NVARCHAR2
--v_lsdl IN NVARCHAR2
) AS
v_code VARCHAR2(20);
v_name VARCHAR2(20);
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE lyl_总部有货门店无货品种明细';
DECLARE CURSOR SUBPHK_CURSOR IS SELECT code,name from organization where nvl(ISFORBIDDEN,0)=0 and code<>'03010757' and code like '%V_SUBBH%' order by code;
BEGIN
OPEN SUBPHK_CURSOR;
LOOP
FETCH SUBPHK_CURSOR INTO v_code,v_name;
--总部有货门店无货品种信息
insert into lyl_总部有货门店无货品种明细 (门店编号,门店名称,货号,商品名称)
Select v_code,v_name,a.hh 货号,a.pm 商品名称
From yw A
left join KCLB lb on lb.id=a.lb
left join (SELECT HH,subbh FROM SUBPHK WHERE SL<>0 AND SUBBH=v_code group by HH,subbh having SUM(sl)>0) sub on sub.hh=a.hh
where nvl(a.jksl,0)>0 and sub.subbh is null
order by a.lb,a.hh;
END LOOP;
CLOSE SUBPHK_CURSOR;
END;
COMMIT;
RETURN;
END;