postgresql函数返回参数 为空 求解决方案

CREATE OR REPLACE FUNCTION "public"."sp_picking"(IN inwhid varchar, IN intote varchar, IN inordernumber varchar, IN shippinglabelno varchar, IN userid varchar, OUT passornot varchar, OUT msg varchar)
RETURNS "pg_catalog"."record" AS $BODY$
DECLARE is_cancelNCHAR(1);
carrierVARCHAR(30);
statusVARCHAR(30);
item_numberVARCHAR(30);
actual_qtyFLOAT;
location_idVARCHAR(50);
fifo_dateVARCHAR(30);
expiration_dateVARCHAR(30);
reserved_forVARCHAR(15);
lot_numberVARCHAR(15);
inspection_codeNCHAR(1);
serial_numberVARCHAR(30);
typeBIGINT;
put_away_locationVARCHAR(50);
stored_attribute_idBIGINT;
shipment_numberVARCHAR(30);
inventory_lossFLOAT;
inventory_profitFLOAT;
picking_methodVARCHAR(30);
pack_line VARCHAR(10);
passornot VARCHAR(30) :='1';
msg VARCHAR(100) :='';
counts BIGINT:=0;
BEGIN
IF shippinglabelno IS NULL OR shippinglabelno = ''
then
passornot:=-3;
msg:=N'没有取到面单号';
RETURN;
end if;
loop
SELECT iscancel ,carriercode ,sp_order_main.status into is_cancel ,carrier ,status
FROM sp_order_main
WHERE whid = inwhid
AND ordernumber = inordernumber;
IF (is_cancel = 'Y')
then
passornot:=-9;
msg:=N'订单取消，不能打包';
RETURN;
end if;
IF (status NOT IN ('STAGED','PICKED'))
then
passornot=-2;
msg:=N'订单状态为 '||status||N' 不能打包!';
RETURN;
END if;
select distinct pickingmethod into picking_method
from sp_order_detail
where whid = inwhid
and ordernumber = inordernumber;
raise notice 'picking_method = %',picking_method;
IF picking_method in ('D0','E0')
then
intote := null;
if not EXISTS (SELECT 1
FROM sp_items_stored
WHERE whid = inwhid
AND shipmentnumber = inordernumber)
then
passornot:=-4;
msg:=N'订单数据错误, intote =' || COALESCE(intote,'') || 'inordernumber = ' || COALESCE(inordernumber,'');
RETURN;
end if;
ELSE
raise notice '到这里= %',picking_method;
if not EXISTS (SELECT 1
FROM sp_items_stored
WHERE whid = inwhid
and huid = intote
AND shipmentnumber = inordernumber)
then
passornot:=-4;
msg:=N'订单数据错误, intote =' || COALESCE(intote,'') || 'inordernumber = ' || COALESCE(inordernumber,'');
RETURN;
end if;
end if;
UPDATE sp_order_main
SET status = 'PACKED'--,@get=1
WHERE whid = inwhid
AND ordernumber = inordernumber
and iscancel<>'Y' and sp_order_main.status IN ('STAGED','PICKED');
GET DIAGNOSTICS counts := ROW_COUNT ;
if counts = 0
then
passornot:=-2;
msg:=N'订单状态为 '||@status||N' 不能打包';
RETURN;
end if;
raise notice 'picking_method = %',picking_method;
IF picking_method NOT in ('D0','E0')
then
raise notice '到这里 = %',picking_method;
INSERT INTO sp_hu_main
(huid,"type",controlnumber,locationid,status
,fifodate,whid,loadid
,reservedfor,userid
,convenyweight,weighttime,isinspected)
SELECT shippinglabelno,sp_hu_main.type,inordernumber,locationid,sp_hu_main.status
,fifodate,inwhid,loadid
,reservedfor,sp_hu_main.userid
,convenyweight,weighttime,isinspected
FROM sp_hu_main
WHERE huid = intote
AND whid = inwhid;
UPDATE sp_items_stored
SET huid = shippinglabelno
WHERE whid = inwhid
AND huid = intote;
DELETE FROM sp_hu_main
WHERE huid = intote
AND whid = inwhid;
ELSE
raise notice '到这里2
= %',picking_method;
select locationid, huid into location_id,intote
from sp_items_stored
where whid = inwhid
AND shipmentnumber = inordernumber LIMIT 1;
INSERT INTO sp_hu_main(whid, huid, locationid, type, status, controlnumber,userid)
VALUES (inwhid, shippinglabelno , location_id, 'SO', 'A', inordernumber,userid);
UPDATE sp_items_stored
SET huid = shippinglabelno
WHERE whid = inwhid
AND shipmentnumber = inordernumber;
IF intote is not null
then
DELETE FROM sp_hu_main
WHERE huid = intote
AND whid = inwhid
and not exists (select 1
from sp_items_stored
where sp_hu_main.whid = sp_items_stored.whid
and sp_hu_main.huid = sp_items_stored.huid);
end if;
end if;
--Update pkd
UPDATE sp_picked_detail
SET status = (CASE WHEN sp_picked_detail.status = 'STAGED'
THEN 'PACKED'
ELSE sp_picked_detail.status END)
,pickedquantity = (CASE WHEN sp_picked_detail.status in ('STAGED','PACKED')
THEN pickedquantity
ELSE 0 END)
WHERE whid = inwhid
AND ordernumber = inordernumber;
--Insert into tbl_shipping_label
INSERT INTO sp_shipping_labels
(whid
,shiplabel
,carriercode
,weight
,status
,ordernumber
,createtime
,remark)
VALUES (inwhid
,shippinglabelno
,carrier
,0
,'PACKED'
,inordernumber
,now()
,NULL);
INSERT INTO sp_wms_log
(tranlogid,trantype,description,starttrandate,endtrandate,employeeid,controlnumber
,whid,tolocationid,tohuid,itemnumber,lotnumber,tranqty
)
SELECT
nextval('sp_log_logid_seq') AS tranlogid,'315',N'包装',now(),now(),userid,inordernumber
,inwhid,location_id,intote,NULL,NULL,0
FROM sp_items_stored
WHERE whid = inwhid
AND huid = shippinglabelno;
IF picking_method in ('D0')
then
UPDATE sp_shipping_labels
SET weight = od.orderweigt
FROM sp_order_main od,sp_shipping_labels spl
WHERE od.whid = spl.whid
AND od.ordernumber = spl.ordernumber
and od.whid = inwhid
and od.ordernumber = inordernumber;
END if;
raise notice '到这里passornot= %',passornot;
EXIT;
end loop;
IF passornot ='1'
then
raise notice '复核完成passornot= %',123;
passornot := 0;
msg := '复核成功';
END if;
raise notice 'passornot= %',passornot;
raise notice 'msg= %',msg;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
;
这是过程


这是运行后的结果
但是为什么我返回的为空呢 ，求大神解决