oracle 如何将序列值分配给变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12092430/
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
How do you assign a sequence value to a variable?
提问by Nick Brunt
I need to assign a sequence value to a variable for use later after the sequence value has been incremented. I've tried this but it gives an error:
我需要将一个序列值分配给一个变量，以便在序列值递增后稍后使用。我试过这个，但它给出了一个错误：
variable imageID number;
select SEQ_IMAGE_ID.CURRVAL into :imageID from dual;
select * from IMAGES where IMAGE_ID = :imageID;
Error starting at line 2 in command:
select SEQ_IMAGE_ID.CURRVAL into :imageID from dual
Error report:
SQL Error: ORA-01006: bind variable does not exist
01006. 00000 - "bind variable does not exist"
I have triple checked that the sequence name is correct, any ideas?
我已经三重检查序列名称是否正确，有什么想法吗？
回答by Alex Poole
You seem to be doing this in SQL*Plus or SQL Developer, from the variabledeclaration. You need to do the assignment in a PL/SQL block, either with an explicit begin/endor with the execcall that hides that:
从variable声明中，您似乎是在 SQL*Plus 或 SQL Developer 中执行此操作。您需要在 PL/SQL 块中进行分配，或者使用显式begin/end或使用exec隐藏的调用：
variable imageID number;
exec select SEQ_IMAGE_ID.CURRVAL into :imageID from dual;
select * from IMAGES where IMAGE_ID = :imageID;
If you're using 11g you don't need to select, you can just assign:
如果您使用的是 11g，则不需要select，您只需分配：
variable imageID number;
exec :image_id := SEQ_IMAGE_ID.CURRVAL;
select * from IMAGES where IMAGE_ID = :imageID;
You could also use a substitution variable:
您还可以使用替换变量：
column tmp_imageid new_value image_id;
select SEQ_IMAGE_ID.CURRVAL as tmp_imageID from dual;
select * from IMAGES where IMAGE_ID = &imageID;
Note the change from :to indicate a bind variable, to &to indicate a substitution variable.
请注意从:表示绑定变量&到表示替换变量的更改。
回答by GolezTrol
In PL/SQL, the variable needs to be declared, something like this:
在 PL/SQL 中，需要声明变量，如下所示：
declare
V_IMAGEID;
begin
select SEQ_IMAGE_ID.CURRVAL into V_IMAGEID from dual;
select * /*into ... */ from IMAGES where IMAGE_ID = V_IMAGEID;
end;
If you're using bind variables, the variable must be bound. The error message indicates this isn't the case. How exactly to bind variables depends on the language/situation. Make sure you use the right direction when binding variables. In the first (dual) query, you will need an out parameter. You may need to specify this.
如果您使用绑定变量，则必须绑定变量。错误消息表明情况并非如此。如何准确绑定变量取决于语言/情况。确保在绑定变量时使用正确的方向。在第一个（双）查询中，您将需要一个 out 参数。您可能需要指定此项。
回答by Alexander Tokarev
just remove ':' before :imageId. If you are in a trigger use :new.imageid
只需删除 :imageId 之前的 ':'。如果您处于触发器中，请使用 :new.imageid
the word variable should be removed as well
单词变量也应该被删除
p.s. I mean anonymous block surely.
ps我的意思是匿名块肯定。
