Oracle中带日期参数的存储过程创建报错：PLS-00049

错误原因 #

原存储过程中，静态SQL子查询里误用了动态SQL的绑定变量语法：a.INVC_POST_DATE=:FROM_DATE。静态SQL直接引用存储过程参数时不需要加冒号，冒号仅用于动态SQL的绑定变量。

额外语法问题修正 #

除绑定变量错误外，代码中还有其他需调整的语法问题：

• 字符串常量作为列别名时缺失AS关键字，比如'0'SALE_TIME会导致语法错误，需改为'0' AS SALE_TIME；
• 表名前后不一致：代码中混用EXP_TMP_SALES与JAC_EXP_TMP_SALES、EXPPORT_SALES_LOG与JAC_EXPPORT_SALES_LOG，需统一为实际存在的表名；
• 确保存储过程结尾的END语句语法合规。

修正后的完整存储过程代码 #

CREATE OR REPLACE PROCEDURE EXPORT_SALES_BP (FROM_DATE DATE)
AS
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE JAC_EXP_TMP_SALES';   
    INSERT INTO JAC_EXP_TMP_SALES 
    SELECT SID, STORE_CODE, BP_STORE_CODE, SALES_DATE, INVC_POST_DATE, '0' AS SALE_TIME, 
           RECEIPT_NO, UPC, QTY, GROSS_AMT, POS, '0' AS STORE_CODE_STOCK_MOV 
    FROM (
        SELECT A.SID, ST.STORE_CODE, st.UDF1_STRING BP_STORE_CODE, 
               TO_CHAR(a.INVC_POST_DATE, 'YYYY-MM-DD') Sales_date, a.INVC_POST_DATE,
               '' AS SALE_TIME, a.doc_no Receipt_NO, I.UPC,
               SUM(DECODE(b.item_type, 2, b.qty*-1, b.qty)) Qty, 
               ROUND(SUM(DECODE(b.item_type, 2, b.qty*1, b.qty)*(b.price)), 2) Gross_Amt,
               '1' AS POS, '' AS STORE_CODE_STOCK_MOV
        FROM rps.document a 
        INNER JOIN rps.document_item b ON a.sid = b.doc_sid
        INNER JOIN rps.subsidiary s ON a.sbs_no = s.sbs_no AND b.sbs_no = s.sbs_no 
        INNER JOIN rps.invn_sbs_item i ON b.invn_sbs_item_sid = i.sid AND s.sid = i.sbs_sid
        INNER JOIN rps.store st ON st.STORE_CODE = a.STORE_CODE
        WHERE a.receipt_type IN (0, 1) 
          AND b.item_type IN (1, 2) 
          AND a.status = 4 
          AND S.SBS_NO = 2 
          AND a.store_code NOT IN ('JC04')
          AND a.INVC_POST_DATE = FROM_DATE  -- 移除冒号，直接引用参数
        GROUP BY ST.STORE_CODE, A.SID, TO_CHAR(a.INVC_POST_DATE, 'YYYY-MM-DD'), 
                 a.INVC_POST_DATE, I.UPC, a.doc_no, b.price, b.tax_amt, ST.UDF1_STRING
        ORDER BY st.UDF1_STRING, TO_CHAR(a.INVC_POST_DATE, 'YYYY-MM-DD'), a.doc_no
    ) 
    WHERE Qty <> 0
    ORDER BY BP_STORE_CODE, SALES_DATE, RECEIPT_NO;

    INSERT INTO JAC_EXPPORT_SALES_LOG 
    SELECT SID, STORE_CODE, BP_STORE_CODE, INVC_POST_DATE, RECEIPT_NO, 
           SUM(QTY) QTY, SUM(GROSS_AMT) GROSS_AMOUNT, SYSDATE EXPORT_DATE 
    FROM JAC_EXP_TMP_SALES 
    WHERE SID NOT IN (SELECT SID FROM JAC_EXPPORT_SALES_LOG)
    GROUP BY SID, STORE_CODE, BP_STORE_CODE, INVC_POST_DATE, RECEIPT_NO;

    SELECT BP_STORE_CODE, SALES_DATE, '' AS SALE_TIME, RECEIPT_NO, UPC, QTY, 
           GROSS_AMT, POS, '' AS STORE_CODE_STOCK_MOV 
    FROM JAC_EXP_TMP_SALES 
    ORDER BY BP_STORE_CODE, SALES_DATE, RECEIPT_NO;
END EXPORT_SALES_BP;
/

内容的提问来源于stack exchange，提问作者ANSAK