17,082
社区成员
发帖
与我相关
我的任务
分享PLSQL在查询分析器里可以正常插入数据，可放在存储过程中为什么总是返回插入行数为0啊
INSERT INTO T027_SHIPMENT_REQ_IF
(
-- データ作成日時
T027_CREATE_YMD,
-- 最終更新日時
T027_UPDATE_YMD,
-- 最終処理区分
T027_TRAN_KBN,
-- 更新担当者コード
T027_TANTO_CD,
-- 更新端末識別コード
T027_TERM_CD,
-- 倉庫コード
T027_WAREH_CD,
-- 寄託者コード
T027_OWNER_CD,
-- 出荷ID
T027_SHIPMENT_ID,
-- 取込ID
T027_GET_ID,
-- 出荷区分
T027_SHIPMENT_KBN,
-- 取引先コード
T027_CUSTOM_CD,
-- 納入先コード
T027_SHIP_TO_CD,
-- 配送区分
T027_DELI_KBN,
-- 方面コード
T027_DIRECTION_CD,
-- コースコード
T027_COURSE_CD,
-- 配送業者コード
T027_DELI_CD,
-- 受注日
T027_ACCEPT_YMD,
-- 出荷指示日
T027_SHIPMENTSIJI_YMD,
-- 出荷予定日
T027_SHIPMENT_PLAN_YMD,
-- 納品指定日
T027_DELI_RESV_YMD,
-- 納品日
T027_DELI_PLAN_YMD,
-- 明細行数
T027_MEISAI_NUM,
-- 個口集約可否FLG
T027_COLLECT_KAHI_FLG,
-- 分納可否FLG
T027_BUNNO_KAHI_FLG,
-- 入力方法
T027_INPUT_KBN,
-- 出荷依頼NO
T027_SHIPMENT_NO,
-- 送信管理NO
T027_SEND_NO,
-- 納入先名称１
T027_SHIP_TO_NM1,
-- 納入先名称２
T027_SHIP_TO_NM2,
-- 納入先部門名
T027_SHIP_TO_SNM,
-- 納入先担当者名
T027_SHIP_TO_TANTO_NM,
-- 納入先郵便番号
T027_SHIP_TO_POST_CD,
-- 納入先住所１
T027_SHIP_TO_ADDRESS1,
-- 納入先住所２
T027_SHIP_TO_ADDRESS2,
-- 納入先住所３
T027_SHIP_TO_ADDRESS3,
-- 納入先電話番号
T027_SHIP_TO_TEL,
-- 納入先連絡先電話番号
T027_SHIP_TO_CONTACT_TEL,
-- 納品金額合計
T027_SUM_DELI_MONEY,
-- 消費税
T027_CONSUMPTION_TAX,
-- 取込対象FLG
T027_GET_FLG,
-- 備考
T027_MEMO,
-- 伝票種類
T027_SLIP_KBN)
SELECT
TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
'1',
'System',
NULL,
p_wareh_cd, --倉庫コード
p_owner_cd, --寄託者コード
p_shipment_ID, --出荷ID
p_get_id, -- 取入ID
'01', --出荷区分
T501_AEON_ORDER.T501_CUSTOM_CD2, --取引先コード
T501_AEON_ORDER.T501_SHOP_CD, --納入先コード
M029_DESTINATION.M029_DELI_KBN, -- 配送区分
M029_DESTINATION.M029_DIRECTION_CD, -- 方面コード
M029_DESTINATION.M029_COURSE_CD, -- コースコード
M029_DESTINATION.M029_DELI_CD, -- 配送業者コード
subStr(p_last_modified_time, 0, 8), -- 取入File Create Time 受注日
v_process_date, -- getProcessData 出荷指示日
v_process_date, -- getProcessData 出荷予定日
' ', -- 納品指定日
T501_AEON_ORDER.T501_DELI_PLAN_YMD, -- 納品日
tempInt, -- 明細行数
M028_CUSTOM.M028_COLLECT_KAHI_FLG, -- 個口集約可否FLG
M028_CUSTOM.M028_BUNNO_KBN, -- 分納可否FLG
'4', -- 入力方法
p_shipment_NO, -- p_shipment_NO出荷依頼NO
' ', -- 送信管理NO
M029_DESTINATION.M029_SHIP_TO_NM1, -- 納入先名称１
M029_DESTINATION.M029_SHIP_TO_NM2, -- 納入先名称２
M029_DESTINATION.M029_SHIP_TO_SNM, -- 納入先部門名
M029_DESTINATION.M029_TANTO_NM, -- 納入先担当者名
M029_DESTINATION.M029_POST_CD, -- 納入先郵便番号
M029_DESTINATION.M029_ADDRESS1, -- 納入先住所１
M029_DESTINATION.M029_ADDRESS2, -- 納入先住所２
M029_DESTINATION.M029_ADDRESS3, -- 納入先住所３
M029_DESTINATION.M029_TEL, -- 納入先電話番号
M029_DESTINATION.M029_CONTACT_TEL, -- 納入先連絡先電話番号
0, -- 納品金額合計
0, -- 消費税
'1', -- 取込対象FLG
' ', -- 備考
' ' -- 伝票種類
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
LEFT JOIN M028_CUSTOM ON T501_AEON_ORDER.T501_WAREH_CD =
M028_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M028_OWNER_CD
AND M028_CUSTOM_CD =
RPAD(T501_AEON_ORDER.T501_CUSTOM_CD2,
12,
' ')
WHERE ROWNUM = 1
AND T501_WAREH_CD = p_wareh_cd
AND T501_OWNER_CD = p_owner_cd
AND RTRIM(T501_CUSTOM_CD2) = p_custom_cd2
AND RTRIM(T501_SHOP_CD) = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_ymd
--AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
---------------------------------------
AND T501_AEON_ORDER.T501_SLIP_NO NOT IN
(SELECT T030_SHIPMENT_REQ_IMP_ERR.T030_Send_No
FROM T030_SHIPMENT_REQ_IMP_ERR
WHERE T030_SHIPMENT_REQ_IMP_ERR.T030_GET_ID = p_get_id)
AND T501_AEON_ORDER.T501_SLIP_NO IN
(SELECT column_value FROM TABLE(CAST(p_slip_NO_array AS T_AEON_SLIP_NO)))
---------------------------------------
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO;
dbms_output.put_line('INSERT INTO T027 inserte');
dbms_output.put_line('INSERT INTO T027 inserted'||sql%rowcount);
...全文
请发表友善的回复…
发表回复
xiaoxiao1984 2008-09-13
- 打赏
- 举报
把插入语句替换成查询语句，看看查询的结果真的有值存在不？
declare
v_no number;
begin
SELECT count(1) into v_no
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
LEFT JOIN M028_CUSTOM ON T501_AEON_ORDER.T501_WAREH_CD =
M028_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M028_OWNER_CD
AND M028_CUSTOM_CD =
RPAD(T501_AEON_ORDER.T501_CUSTOM_CD2,
12,
' ')
WHERE ROWNUM = 1
AND T501_WAREH_CD = p_wareh_cd
AND T501_OWNER_CD = p_owner_cd
AND RTRIM(T501_CUSTOM_CD2) = p_custom_cd2
AND RTRIM(T501_SHOP_CD) = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_ymd
--AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
---------------------------------------
AND T501_AEON_ORDER.T501_SLIP_NO NOT IN
(SELECT T030_SHIPMENT_REQ_IMP_ERR.T030_Send_No
FROM T030_SHIPMENT_REQ_IMP_ERR
WHERE T030_SHIPMENT_REQ_IMP_ERR.T030_GET_ID = p_get_id)
AND T501_AEON_ORDER.T501_SLIP_NO IN
(SELECT column_value FROM TABLE(CAST(p_slip_NO_array AS T_AEON_SLIP_NO)))
---------------------------------------
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO;
dbms_output.put_line( 'The result size of the query is ' || v_no );
end;
