在 pl/sql 块中声明变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8469301/
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
declare variables in a pl/sql block
提问by mnky9800n
I am trying to follow this guidefor creating pl/sqlblocks and I am getting an ORA-00922:missing or invalid option on the SET orderNumberSEQ.... What am I doing wrong?
我正在尝试按照本指南创建pl/sql块，但在SET orderNumberSEQ.... 我究竟做错了什么？
declare
orderNumberSEQ number(5);
userid varchar(20);
begin
insert into bs_orders (userid, ono, timepurchased)
values('lilith', orderNum_seq.NEXTVAL,(SELECT current_timestamp FROM dual));
SET orderNumberSEQ := orderNum_seq.CURRVAL;
SELECT userid FROM bs_orders
where ono = orderNumberSEQ;
end;
/
回答by zerkms
You don't need to use SET. Just
您不需要使用SET. 只是
SELECT orderNum_seq.CURRVAL INTO orderNumberSEQ FROM DUAL;
will do the trick. Or if you use oracle11:
会做的伎俩。或者，如果您使用oracle11：
orderNumberSEQ := orderNum_seq.CURRVAL;
回答by mulander
There are several problems with your initial approach. While the selected answer correctly provides a way to determine the current value of the sequence is does not address these problems:
您最初的方法有几个问题。虽然选择的答案正确提供了一种确定序列当前值的方法，但并没有解决这些问题：
- The value of the sequence might have changed between the call to NEXTVAL and CURRVAL. This will lead to a hard to detect bug and there is a possibility that you will get a value used by a different session. Use the returning clause in the insert statement to retrieve the actual inserted value.
- Your variable names are the same as your column names. This will lead to hard to detect bugs in queries embedded inside PL/SQL blocks. Make sure your variables are named differently - you can prefix them from the type name like v_userid instead of userid.
SELECT statement inside an Oracle PL/SQL block requires an INTO clause. Example:
SELECT userid INTO v_userid FROM bs_orders WHERE ono = orderNumberSEQ;
The sub-query for current_timestamp is redundant. You can use plain CURRENT_TIMESTAMP in place of the sub-query to achieve the same result.
Instead of manually providing the column type. Anchor it to the exact table type using %type notation.
v_userid bs_orders.userid%type;
- 序列的值可能在调用 NEXTVAL 和 CURRVAL 之间发生了变化。这将导致难以检测的错误，并且您可能会获得不同会话使用的值。在插入语句中使用返回子句来检索实际插入的值。
- 您的变量名称与列名称相同。这将导致难以检测嵌入在 PL/SQL 块中的查询中的错误。确保您的变量以不同的方式命名 - 您可以使用 v_userid 之类的类型名称作为前缀，而不是 userid。
Oracle PL/SQL 块内的 SELECT 语句需要 INTO 子句。例子：
SELECT userid INTO v_userid FROM bs_orders WHERE ono = orderNumberSEQ;
current_timestamp 的子查询是多余的。您可以使用普通的 CURRENT_TIMESTAMP 代替子查询来获得相同的结果。
而不是手动提供列类型。使用 %type 表示法将其锚定到确切的表类型。
v_userid bs_orders.userid%type;
The following code addresses all 5 issues.
以下代码解决了所有 5 个问题。
DECLARE
v_userid bs_orders.userid%type; -- anchoring the type
BEGIN
INSERT INTO bs_orders(userid , ono , timepurchased)
VALUES('lilith', orderNum_seq.NEXTVAL, CURRENT_TIMESTAMP)
RETURNING userid INTO v_userid; -- instead of currval and an additional select
-- do whatever you want with v_userid here
END;
/
