Oracle SQL PLS-00049：绑定变量错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7244714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL PLS-00049: bad bind variable
提问by hamstar
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...
我收到此错误，这似乎是列拼写的问题。但是，我 99% 确定我拼写正确，但我看不出有任何理由让我犯错误......
Here's the source:
这是来源：
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
And that gives a:
这给出了一个：
12/12 PLS-00049: bad bind variable 'V_NEW_QOH'
I have tried replacing line 12 with the following combinations:
我尝试用以下组合替换第 12 行：
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;
But it still gives me the error.
但它仍然给我错误。
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)
);
Thanks in advance for your help.
在此先感谢您的帮助。
回答by Tony Andrews
Change the update to:
将更新更改为：
UPDATE products
SET qoh = v_new_qoh
WHERE id = :new.product_id;
i.e. no colon in front of v_new_qoh.
即 v_new_qoh 前面没有冒号。
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:
行号 (12) 指的是 PL/SQL 块的行号。该块以单词 DECLARE 开头，因此第 12 行是以下开头的行：
SET qoh = :v_new_qoh
回答by Florin Ghita
and new.quantityshould be :new.quantity
并且new.quantity应该是:new.quantity
回答by Mangesh Modiraj
for inserting same value of sample_insp_id in to Sir_no:-
用于在 Sir_no 中插入相同的 sample_insp_id 值：-
update TRIGGER "PT"."BI_PROJECT_PART_SAMPLE_INSP"
before insert on "PROJECT_PART_SAMPLE_INSP"
for each row
begin
if :NEW."SAMPLE_INSP_ID" is null then
select "PROJECT_PART_SAMPLE_INSP_SEQ".nextval into :NEW."SAMPLE_INSP_ID",:NEW."SIR_NO"from dual;
end if;
end;
回答by Mangesh Modiraj
Create or replace trigger
创建或替换触发器
"BI_PROJECT_PART_SAMPLE_INSP"
before insert on
在插入之前
"PROJECT_PART_SAMPLE_INSP"
for each row
每行
begin
if :NEW."SAMPLE_INSP_ID" is null then
select "PROJECT_PART_SAMPLE_INSP_SEQ".nextval into :NEW."SAMPLE_INSP_ID" from dual;
:NEW."SIR_NO":= :NEW."SAMPLE_INSP_ID";
end if;
end;
