刚从server2000转做oracle,有个小问题大家帮帮忙,谢了!下面这个存储过程怎么手工执行,我要查看执行的结果。尝试过的语句格式 exec update_carr_audit('DC1','SH1009130013','SH1009130013',NULL);不成功!!!!用PL/SQL Developer 中的test 也不成功,看不到我想要的结果!!!CREATE OR REPLACE PROCEDURE update_carr_audit(P_CR_CURSOR IN OUT CR_ORACLE_PACKAGE.CR_TYPE,
P_whse IN PKT_HDR.whse%TYPE,
P_pick_wave_nbr_f IN PKT_HDR_INTRNL.pick_wave_nbr%TYPE,
P_pick_wave_nbr_t IN PKT_HDR_INTRNL.pick_wave_nbr%TYPE,
P_pkt_ctrl_nbr IN PKT_HDR_INTRNL.pkt_ctrl_nbr%TYPE) IS n_carrno number;
V_CARR_NUMBER varchar2(30);
CURSOR cur_carr_id IS
select cwcd.whse, cwcd.carrcode
from PKT_HDR_INTRNL phi,
PKT_HDR ph,
cux_wms_carr_detail cwcd,
store_master sm
where phi.pick_wave_nbr >= P_pick_wave_nbr_f
and phi.pick_wave_nbr <= P_pick_wave_nbr_t
and ph.pkt_ctrl_nbr = phi.pkt_ctrl_nbr
and InStr(phi.pkt_ctrl_nbr, nvl(P_pkt_ctrl_nbr, phi.pkt_ctrl_nbr)) <> 0
and ph.whse = P_whse
AND sm.whse = ph.whse
AND sm.store_nbr LIKE substr(ph.DC_CTR_NBR, 1, 3) || '%'
-- AND sm.name like '%总仓%'
and cwcd.whse = SUBSTR(sm.store_nbr, 1, 3)
and cwcd.enabled_flag = 'Y'
and ph.shipto = cwcd.companyid;BEGIN
FOR rec_carr_id IN cur_carr_id LOOP
select MAX(NVL(CARR_NO, 0))
INTO n_carrno
from CUX_WMS_CARR_NUMBER CWCN
where cwcn.whse = rec_carr_id.whse
and cwcn.carrcode = rec_carr_id.carrcode
and cwcn.YEARS = TO_CHAR(SYSDATE, 'YY');
if n_carrno = 0 then
n_carrno := n_carrno + 1;
insert into CUX_WMS_CARR_NUMBER
(WHSE, CARRCODE, YEARs, CARR_NUMBER, CARR_NO)
values
(rec_carr_id.whse,
rec_carr_id.carrcode,
TO_CHAR(SYSDATE, 'YY'),
rec_carr_id.whse || rec_carr_id.carrcode || TO_CHAR(SYSDATE, 'YY') ||
'0000' || n_carrno,
n_carrno);
else
n_carrno := n_carrno + 1;
IF LENGTH(n_carrno) = 1 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '0000' || n_carrno;
ELSIF LENGTH(n_carrno) = 2 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '000' || n_carrno;
ELSIF LENGTH(n_carrno) = 3 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '00' || n_carrno;
ELSIF LENGTH(n_carrno) = 4 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '0' || n_carrno;
ELSIF LENGTH(n_carrno) = 5 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || n_carrno;
ELSE
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || n_carrno;
END IF;
UPDATE CUX_WMS_CARR_NUMBER A
SET CARR_NUMBER = V_CARR_NUMBER, CARR_NO = n_carrno
WHERE A.whse = rec_carr_id.whse
and A.carrcode = rec_carr_id.carrcode
and A.YEARS = TO_CHAR(SYSDATE, 'YY');
end if;
COMMIT;
end loop; OPEN P_CR_CURSOR FOR
SELECT CWCN.CARR_NUMBER,
S.pick_wave_nbr,
S.whse,
S.shipto,
S.shipto_name,
S.shipto_addr_1,
S.ORD_TYPENAME,
S.carrcode,
S.carrname1,
s.carrname,
S.companyname,
S.carr_date,
S.carr_to_date,
S.carr_days,
update_carr_ss_audit(P_whse,
S.pick_wave_nbr,
P_pkt_ctrl_nbr,
CWCN.CARR_NUMBER) countnumber,
update_carr_ds_audit(P_whse,
S.pick_wave_nbr,
P_pkt_ctrl_nbr,
CWCN.CARR_NUMBER) countno,
SUM(S.unit_vol) 立方数,
floor((trunc(SUM(S.pkt_qty) / S.std_pack_qty))) "箱数",
SUM(S.pkt_qty) total_qty
FROM (select phi.pkt_ctrl_nbr, --单号,
phi.pick_wave_nbr, --波次号
PD.PKT_SEQ_NBR,
ph.whse,
ph.shipto,
ph.shipto_name,
ph.shipto_addr_1,
CWCO.ORD_TYPENAME,
cwcd.carrcode,
cwcd.carrname1,
cwcd.carrname,
cwcd.companyname,
to_char(sysdate, 'yyyy-mm-dd') carr_date,
to_char(sysdate + cwcd.carr_days - 1, 'yyyy-mm-dd') carr_to_date,
cwcd.carr_days,
pd.sku_id,
im.unit_vol,
im.std_pack_qty,
pd.pkt_qty -- 发货总件数,
from PKT_HDR_INTRNL phi,
PKT_HDR ph,
PKT_DTL pd,
ITEM_MASTER im,
cux_wms_carr_detail cwcd,
store_master sm,
CUX_WMS_CARR_ORDERTYPE CWCO
where phi.pick_wave_nbr >= P_pick_wave_nbr_f
and phi.pick_wave_nbr <= P_pick_wave_nbr_t
and InStr(phi.pkt_ctrl_nbr,
nvl(P_pkt_ctrl_nbr, phi.pkt_ctrl_nbr)) <> 0
and ph.pkt_ctrl_nbr = phi.pkt_ctrl_nbr
and ph.whse = P_whse
and ph.pkt_ctrl_nbr = pd.pkt_ctrl_nbr
and pd.sku_id = im.sku_id
AND sm.whse = ph.whse
AND sm.store_nbr LIKE substr(ph.DC_CTR_NBR, 1, 3) || '%'
AND sm.name like '%总仓%'
and cwcd.whse = SUBSTR(sm.store_nbr, 1, 3)
and cwcd.enabled_flag = 'Y'
and ph.shipto = cwcd.companyid
AND PH.ORD_TYPE = CWCO.ORD_TYPE
AND IM.SPL_INSTR_CODE_1 = cwco.brand) S,
CUX_WMS_CARR_NUMBER CWCN
WHERE CWCN.WHSE(+) = S.WHSE
AND CWCN.CARRCODE(+) = S.CARRCODE
AND CWCN.YEARS(+) = TO_CHAR(SYSDATE, 'YY')
group by CWCN.CARR_NUMBER,
S.pick_wave_nbr,
S.whse,
S.shipto,
S.shipto_name,
S.shipto_addr_1,
S.ORD_TYPENAME,
S.carrcode,
S.carrname1,
s.carrname,
S.companyname,
S.carr_date,
S.carr_to_date,
S.carr_days,
S.std_pack_qty;END update_carr_audit;
P_whse IN PKT_HDR.whse%TYPE,
P_pick_wave_nbr_f IN PKT_HDR_INTRNL.pick_wave_nbr%TYPE,
P_pick_wave_nbr_t IN PKT_HDR_INTRNL.pick_wave_nbr%TYPE,
P_pkt_ctrl_nbr IN PKT_HDR_INTRNL.pkt_ctrl_nbr%TYPE) IS n_carrno number;
V_CARR_NUMBER varchar2(30);
CURSOR cur_carr_id IS
select cwcd.whse, cwcd.carrcode
from PKT_HDR_INTRNL phi,
PKT_HDR ph,
cux_wms_carr_detail cwcd,
store_master sm
where phi.pick_wave_nbr >= P_pick_wave_nbr_f
and phi.pick_wave_nbr <= P_pick_wave_nbr_t
and ph.pkt_ctrl_nbr = phi.pkt_ctrl_nbr
and InStr(phi.pkt_ctrl_nbr, nvl(P_pkt_ctrl_nbr, phi.pkt_ctrl_nbr)) <> 0
and ph.whse = P_whse
AND sm.whse = ph.whse
AND sm.store_nbr LIKE substr(ph.DC_CTR_NBR, 1, 3) || '%'
-- AND sm.name like '%总仓%'
and cwcd.whse = SUBSTR(sm.store_nbr, 1, 3)
and cwcd.enabled_flag = 'Y'
and ph.shipto = cwcd.companyid;BEGIN
FOR rec_carr_id IN cur_carr_id LOOP
select MAX(NVL(CARR_NO, 0))
INTO n_carrno
from CUX_WMS_CARR_NUMBER CWCN
where cwcn.whse = rec_carr_id.whse
and cwcn.carrcode = rec_carr_id.carrcode
and cwcn.YEARS = TO_CHAR(SYSDATE, 'YY');
if n_carrno = 0 then
n_carrno := n_carrno + 1;
insert into CUX_WMS_CARR_NUMBER
(WHSE, CARRCODE, YEARs, CARR_NUMBER, CARR_NO)
values
(rec_carr_id.whse,
rec_carr_id.carrcode,
TO_CHAR(SYSDATE, 'YY'),
rec_carr_id.whse || rec_carr_id.carrcode || TO_CHAR(SYSDATE, 'YY') ||
'0000' || n_carrno,
n_carrno);
else
n_carrno := n_carrno + 1;
IF LENGTH(n_carrno) = 1 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '0000' || n_carrno;
ELSIF LENGTH(n_carrno) = 2 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '000' || n_carrno;
ELSIF LENGTH(n_carrno) = 3 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '00' || n_carrno;
ELSIF LENGTH(n_carrno) = 4 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || '0' || n_carrno;
ELSIF LENGTH(n_carrno) = 5 THEN
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || n_carrno;
ELSE
V_CARR_NUMBER := rec_carr_id.whse || rec_carr_id.carrcode ||
TO_CHAR(SYSDATE, 'YY') || n_carrno;
END IF;
UPDATE CUX_WMS_CARR_NUMBER A
SET CARR_NUMBER = V_CARR_NUMBER, CARR_NO = n_carrno
WHERE A.whse = rec_carr_id.whse
and A.carrcode = rec_carr_id.carrcode
and A.YEARS = TO_CHAR(SYSDATE, 'YY');
end if;
COMMIT;
end loop; OPEN P_CR_CURSOR FOR
SELECT CWCN.CARR_NUMBER,
S.pick_wave_nbr,
S.whse,
S.shipto,
S.shipto_name,
S.shipto_addr_1,
S.ORD_TYPENAME,
S.carrcode,
S.carrname1,
s.carrname,
S.companyname,
S.carr_date,
S.carr_to_date,
S.carr_days,
update_carr_ss_audit(P_whse,
S.pick_wave_nbr,
P_pkt_ctrl_nbr,
CWCN.CARR_NUMBER) countnumber,
update_carr_ds_audit(P_whse,
S.pick_wave_nbr,
P_pkt_ctrl_nbr,
CWCN.CARR_NUMBER) countno,
SUM(S.unit_vol) 立方数,
floor((trunc(SUM(S.pkt_qty) / S.std_pack_qty))) "箱数",
SUM(S.pkt_qty) total_qty
FROM (select phi.pkt_ctrl_nbr, --单号,
phi.pick_wave_nbr, --波次号
PD.PKT_SEQ_NBR,
ph.whse,
ph.shipto,
ph.shipto_name,
ph.shipto_addr_1,
CWCO.ORD_TYPENAME,
cwcd.carrcode,
cwcd.carrname1,
cwcd.carrname,
cwcd.companyname,
to_char(sysdate, 'yyyy-mm-dd') carr_date,
to_char(sysdate + cwcd.carr_days - 1, 'yyyy-mm-dd') carr_to_date,
cwcd.carr_days,
pd.sku_id,
im.unit_vol,
im.std_pack_qty,
pd.pkt_qty -- 发货总件数,
from PKT_HDR_INTRNL phi,
PKT_HDR ph,
PKT_DTL pd,
ITEM_MASTER im,
cux_wms_carr_detail cwcd,
store_master sm,
CUX_WMS_CARR_ORDERTYPE CWCO
where phi.pick_wave_nbr >= P_pick_wave_nbr_f
and phi.pick_wave_nbr <= P_pick_wave_nbr_t
and InStr(phi.pkt_ctrl_nbr,
nvl(P_pkt_ctrl_nbr, phi.pkt_ctrl_nbr)) <> 0
and ph.pkt_ctrl_nbr = phi.pkt_ctrl_nbr
and ph.whse = P_whse
and ph.pkt_ctrl_nbr = pd.pkt_ctrl_nbr
and pd.sku_id = im.sku_id
AND sm.whse = ph.whse
AND sm.store_nbr LIKE substr(ph.DC_CTR_NBR, 1, 3) || '%'
AND sm.name like '%总仓%'
and cwcd.whse = SUBSTR(sm.store_nbr, 1, 3)
and cwcd.enabled_flag = 'Y'
and ph.shipto = cwcd.companyid
AND PH.ORD_TYPE = CWCO.ORD_TYPE
AND IM.SPL_INSTR_CODE_1 = cwco.brand) S,
CUX_WMS_CARR_NUMBER CWCN
WHERE CWCN.WHSE(+) = S.WHSE
AND CWCN.CARRCODE(+) = S.CARRCODE
AND CWCN.YEARS(+) = TO_CHAR(SYSDATE, 'YY')
group by CWCN.CARR_NUMBER,
S.pick_wave_nbr,
S.whse,
S.shipto,
S.shipto_name,
S.shipto_addr_1,
S.ORD_TYPENAME,
S.carrcode,
S.carrname1,
s.carrname,
S.companyname,
S.carr_date,
S.carr_to_date,
S.carr_days,
S.std_pack_qty;END update_carr_audit;
你需要传入一个CR_ORACLE_PACKAGE.CR_TYPE型的cursor ,然后调用此过程如果你要用PL/SQL Developer进行调试,同样也是要传入一个cursor,这样你过程才能执行update_carr_audit('DC1','SH1009130013','SH1009130013',NULL);
你这个里面,由于你传入的变量P_CR_CURSOR是一个字串‘DC1’,故不能执行你可以这样:
declare
P_CR_CURSOR CR_ORACLE_PACKAGE.CR_TYPE;
begin
open P_CR_CURSOR for select * from emp.......;
exec update_carr_audit(P_CR_CURSOR,'SH1009130013','SH1009130013',NULL);
end;如果你要想调试,那么你可以把上面这个SQL块写在另一个test过程中,然后对test过程进行调试,一步一步跟踪到你update_carr_audit过程中。
begin
update_carr_audit('DC1','SH1009130013','SH1009130013',NULL);
end;
--在oracle comand 的命令行中:
call update_carr_audit('DC1','SH1009130013','SH1009130013',NULL);
declare
P_CR_CURSOR CR_ORACLE_PACKAGE.CR_TYPE;
begin
update_carr_audit(P_CR_CURSOR,'SH1009130013','SH1009130013',NULL);
end;
P_CR_CURSOR CR_ORACLE_PACKAGE.CR_TYPE;
begin
update_carr_audit(P_CR_CURSOR,'SH1009130013','SH1009130013',NULL);
end;
P_CR_CURSOR CR_ORACLE_PACKAGE.CR_TYPE;
begin
update_carr_audit(P_CR_CURSOR,'SH1009130013','SH1009130013',NULL);
end;