Oracle 过程出错，对象无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17122321/
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
Error in Oracle procedure, object is invalid
提问by user967225
Where is the bug? Compilation ends with errors and I have no idea where I'm going wrong.
错误在哪里？编译以错误结束，我不知道我哪里出错了。
create or replace
PROCEDURE make_payoff(user_id_argument number)
is
begin
payoff_amount:= 0;
CURSOR Clicks IS
SELECT c.cpc FROM click as c JOIN widget w ON w.id = c.widget_id JOIN website web ON web.id = w.website_id WHERE web.user_id = user_id_argument AND c.payoff_id IS NULL;
BEGIN
FOR Click IN Clicks
LOOP
payoff_amount:= payoff_amount + Click.cpc;
END LOOP;
INSERT INTO payoff (user_id, amount) VALUES (user_id_argument, payoff_amount);
COMMIT;
end;
I'm getting:
我越来越：
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object S10306.MAKE_PAYOFF is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EDIT: I've fixed Cursor name but error is the same
编辑：我已经修复了光标名称，但错误是一样的
回答by Ben
This is the error you get when you try to usethe procedure. Not the error you get when you compileit. You need to find the error you get when you compile the procedure, probably using show errorsand attempt to solve that problem.
这是您在尝试使用该过程时遇到的错误。不是编译时遇到的错误。您需要找到编译过程时遇到的错误，可能使用show errors并尝试解决该问题。
Your problem is that for click in clickshould be for click in clicks... Not the extra s, so you are looping through the cursor.
您的问题是for click in click应该是for click in clicks... 不是 extra s，所以您正在遍历光标。
Additionally, in your cursor you've written FROM click as c, which is not valid in Oracle. This should be FROM click c
此外，您在光标中写入了FROM click as c，这在 Oracle 中无效。这应该是FROM click c
And two BEGINs... remove the first one.
还有两个 BEGIN... 删除第一个。
Alex Poole has notedthat you've also not declared the type of the variable payoff_amount. You should declare this as a number:
Alex Poole 已经注意到您还没有声明变量 payoff_amount 的类型。您应该将其声明为一个数字：
payoff_amount number := 0;
However, there is no need to do this, no need to loop, no need to use a procedure at all. This is possible with a single SQL statement:
但是，不需要这样做，不需要循环，根本不需要使用过程。这可以通过单个 SQL 语句实现：
insert into payoff (user_id, amount)
select 'user_id_argument', sum(c.cpc)
from click c
join widget w
on w.id = c.widget_id
join website web
on web.id = w.website_id
where web.user_id = user_id_argument
and c.payoff_id IS NULL;
回答by David Jashi
You missed s.
你错过了s。
FOR Click IN Clicks
LOOP
payoff_amount:= payoff_amount + Click.cpc;
END LOOP;
Anyway, don't name variables and cursors so close to database fields. Add some prefix, for example, to differentiate easily.
无论如何，不要将变量和游标命名得太靠近数据库字段。例如，添加一些前缀以方便区分。
