17,082
社区成员
发帖
与我相关
我的任务
分享存储过程
需求：新增表: DP_ ZMMJYHWJJ01（货物交接单），复制调配系统表：ZMMJYHWJJ01即可，主要为存储物流凭证电子化所涉及的交接单信息。
每天将表ZMMJYHWJJ01中实际交货日期（ZSJJHQ）为空的数据导入表DP_ ZMMJYHWJJ01作为未操作数据供物流凭证电子化使用。
如ZMMJYHWJJ01中数据实际交货日期（ZSJJHQ）不为空，而DP_ZMMJYHWJJ01-ZSJJHQ为空、DP_ZMMJYHWJJ01-RESERVESTR8为空，则将表DP_ZMMJYHWJJ01中此笔数据删除，因为已在ERP系统完成交接单过账操作。
存储过程：
CREATE OR REPLACE PROCEDURE ZMMJYHWJJ01_TO_DPZMMJYHWJJ01
AS
TYPE ZMMJYHWJJ01_TO_DPZMMJYHWJJ01 IS REF CURSOR;//定义一个引用游标类型
BENGIN
OPEN SELECT_ZMMJYHWJJ01 FOR
SELECT * FROM ZMMJYHWJJ01 WHERE ZSJJHQ is null;//ZSJJHQ 数据库字段表示实际交货日期
c_row SELECT_ZMMJYHWJJ01%rowtype;
FOR c_row in SELECT_ZMMJYHWJJ01
LOOP
INSERT INTO DP_ZMMJYHWJJ01(ZFHTZDH,EBELN,EBELP,GYSCH,MBLNR,ZEILE,GJAHR,BUKRS,WERKS,ZSJJHQ,c_rowJSL,MEINS,CHARG,REMARK,ZDJZT,UPGSTATUS,MATNR,ERNAM,ERNAMTXT,ZFHFQZ,ZFHFSJ,ZSHFQZ,ZFLAG,FLATXT,ZSHFSJ,LGORT,LGOBE,ZFQR,ZSPR)
VALUES (c_row.ZFHTZDH,c_row.EBELN,c_row.EBELP,c_row.GYSCH,c_row.MBLNR,c_row.ZEILE,c_row.GJAHR,c_row.BUKRS,c_row.WERKS,c_row.ZSJJHQ,c_row.c_rowJSL,c_row.MEINS,c_row.CHARG,c_row.REMARK,c_row.ZDJZT,c_row.UPGSTATUS,c_row.MATNR,c_row.ERNAM,c_row.ERNAMTXT,c_row.ZFHFQZ,c_row.ZFHFSJ,c_row.ZSHFQZ,c_row.ZFLAG,c_row.FLATXT,c_row.ZSHFSJ,c_row.LGORT,c_row.LGOBE,c_row.ZFQR,c_row.ZSPR);
EXIT WHEN SELECT_ZMMJYHWJJ01%notfound;
END LOOP;
CLOSE SELECT_ZMMJYHWJJ01;
OPRN SELECT_ZMMJYHWJJ02 FOR
SELECT * FROM ZMMJYHWJJ01 Z
LEFT JOIN DP_ZMMJYHWJJ01 DP ON DP.ZFHTZDH=Z.ZFHTZDH AND DP.ZSJJHQ IS NULL AND DP.UPGSTATUS IS NULL // ZFHTZDH主键ID字段，UPGSTATUS 操作标识
WHERE Z.ZSJJHQ IS NOT NULL ;
d_row SELECT_ZMMJYHWJJ02%rowtype;
FOR d_row IN SELECT_ZMMJYHWJJ02
LOOP
DELETE FROM DP_ZMMJYHWJJ01 WHERE d_row.ZFHTZDH=DP_ZMMJYHWJJ01.ZFHTZDH;
exit when SELECT_ZMMJYHWJJ02%notfound;
END LOOP;
CLOSE SELECT_ZMMJYHWJJ02;
END;
每天将表ZMMJYHWJJ01中实际交货日期（ZSJJHQ）为空的数据导入表DP_ ZMMJYHWJJ01作为未操作数据供物流凭证电子化使用。
如ZMMJYHWJJ01中数据实际交货日期（ZSJJHQ）不为空，而DP_ZMMJYHWJJ01-ZSJJHQ为空、DP_ZMMJYHWJJ01-RESERVESTR8为空，则将表DP_ZMMJYHWJJ01中此笔数据删除，因为已在ERP系统完成交接单过账操作。
存储过程：
CREATE OR REPLACE PROCEDURE ZMMJYHWJJ01_TO_DPZMMJYHWJJ01
AS
TYPE ZMMJYHWJJ01_TO_DPZMMJYHWJJ01 IS REF CURSOR;//定义一个引用游标类型
BENGIN
OPEN SELECT_ZMMJYHWJJ01 FOR
SELECT * FROM ZMMJYHWJJ01 WHERE ZSJJHQ is null;//ZSJJHQ 数据库字段表示实际交货日期
c_row SELECT_ZMMJYHWJJ01%rowtype;
FOR c_row in SELECT_ZMMJYHWJJ01
LOOP
INSERT INTO DP_ZMMJYHWJJ01(ZFHTZDH,EBELN,EBELP,GYSCH,MBLNR,ZEILE,GJAHR,BUKRS,WERKS,ZSJJHQ,c_rowJSL,MEINS,CHARG,REMARK,ZDJZT,UPGSTATUS,MATNR,ERNAM,ERNAMTXT,ZFHFQZ,ZFHFSJ,ZSHFQZ,ZFLAG,FLATXT,ZSHFSJ,LGORT,LGOBE,ZFQR,ZSPR)
VALUES (c_row.ZFHTZDH,c_row.EBELN,c_row.EBELP,c_row.GYSCH,c_row.MBLNR,c_row.ZEILE,c_row.GJAHR,c_row.BUKRS,c_row.WERKS,c_row.ZSJJHQ,c_row.c_rowJSL,c_row.MEINS,c_row.CHARG,c_row.REMARK,c_row.ZDJZT,c_row.UPGSTATUS,c_row.MATNR,c_row.ERNAM,c_row.ERNAMTXT,c_row.ZFHFQZ,c_row.ZFHFSJ,c_row.ZSHFQZ,c_row.ZFLAG,c_row.FLATXT,c_row.ZSHFSJ,c_row.LGORT,c_row.LGOBE,c_row.ZFQR,c_row.ZSPR);
EXIT WHEN SELECT_ZMMJYHWJJ01%notfound;
END LOOP;
CLOSE SELECT_ZMMJYHWJJ01;
OPRN SELECT_ZMMJYHWJJ02 FOR
SELECT * FROM ZMMJYHWJJ01 Z
LEFT JOIN DP_ZMMJYHWJJ01 DP ON DP.ZFHTZDH=Z.ZFHTZDH AND DP.ZSJJHQ IS NULL AND DP.UPGSTATUS IS NULL // ZFHTZDH主键ID字段，UPGSTATUS 操作标识
WHERE Z.ZSJJHQ IS NOT NULL ;
d_row SELECT_ZMMJYHWJJ02%rowtype;
FOR d_row IN SELECT_ZMMJYHWJJ02
LOOP
DELETE FROM DP_ZMMJYHWJJ01 WHERE d_row.ZFHTZDH=DP_ZMMJYHWJJ01.ZFHTZDH;
exit when SELECT_ZMMJYHWJJ02%notfound;
END LOOP;
CLOSE SELECT_ZMMJYHWJJ02;
END;
...全文
请发表友善的回复…
发表回复
