Oracle SQL PLS-00049:绑定变量错误 [英] Oracle SQL PLS-00049: bad bind variable

问题描述

我遇到此错误，这似乎与列拼写有关.但是我百分之九十九确定我的拼写正确无误，但是我看不到任何原因导致我犯错误...

I'm getting this error which seems to be an issue with column spelling. However I am 99% percent sure I have spelled everything correct, but I can't see any reason to be getting the error I do...

以下是来源:

CREATE OR REPLACE TRIGGER update_qoh_trigger
    AFTER INSERT ON sales
    FOR EACH ROW
DECLARE
    v_qoh products.qoh%TYPE;
    v_new_qoh products.qoh%TYPE;
BEGIN
    SELECT qoh INTO v_qoh
    FROM products
    WHERE id = :new.product_id;

    v_new_qoh := v_qoh - new.quantity; // ERROR HERE

    UPDATE products
    SET qoh = :v_new_qoh
    WHERE id = :new.product_id;
END;
/
sho err

然后给出一个:

12/12 PLS-00049: bad bind variable 'V_NEW_QOH' 

我尝试用以下组合替换第12行:

I have tried replacing line 12 with the following combinations:

• v_new_qoh := :v_qoh - :new.quantity;
• :v_new_qoh := :v_qoh - :new.quantity;
• :v_new_qoh = :v_qoh - :new.quantity;
• :v_new_qoh := v_qoh - :new.quantity;
• :v_new_qoh := :v_qoh - new.quantity;
• v_new_qoh := v_qoh - :new.quantity;

• v_new_qoh := :v_qoh - :new.quantity;
• :v_new_qoh := :v_qoh - :new.quantity;
• :v_new_qoh = :v_qoh - :new.quantity;
• :v_new_qoh := v_qoh - :new.quantity;
• :v_new_qoh := :v_qoh - new.quantity;
• v_new_qoh := v_qoh - :new.quantity;

但是它仍然给我错误.

products表看起来像这样:

The products table looks like this:

CREATE TABLE products (
    id NUMBER,
    name VARCHAR2,
    price NUMBER,
    qoh NUMBER(2)
);

CREATE TABLE sales (
    id NUMBER(10) AUTO_INCREMENT,
    customer_id NUBMER(3),
    product_id NUMBER(3),
    quantity NUMBER(2),
    price NUMBER(5,2),
    sale_date DATE,
    despatch_id NUMBER(10)
);

预先感谢您的帮助.

推荐答案

将更新更改为:

UPDATE products
SET qoh = v_new_qoh
WHERE id = :new.product_id;

即v_new_qoh前面没有冒号.

i.e. no colon in front of v_new_qoh.

行号(12)表示PL/SQL块的行号. 该代码块以单词DECLARE开头，因此第12行以以下代码开头:

The line number (12) refers to the line number of the PL/SQL block. The block begins with the word DECLARE, so the 12th line is the one starting with:

SET qoh = :v_new_qoh

这篇关于Oracle SQL PLS-00049:绑定变量错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！