Oracle 存储过程 - 我可以为声明块中的变量赋值吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18513125/
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 stored procedure - can I assign value to the variable in declaration block?
提问by kyooryu
I am writing a stored procedure in oracle and I would like to assign a value to variable right from the start:
我正在 oracle 中编写一个存储过程，我想从一开始就为变量赋值：
CREATE PROCEDURE proc
(
param1
, param2
)
AS
variable1 INT AS SELECT MAX(value) FROM table WHERE field = param1;
BEGIN
...
Is it allowed or should I do it in executable block?
它是允许的还是应该在可执行块中执行？
回答by Ed Gibbs
Oracle assign query values into variables like this...
Oracle 将查询值分配给这样的变量......
SELECT MAX(value) INTO variable1 FROM table WHERE field = param1;
... and because it does, you can't initialize the variable to a query value in the declaration block. It has to be in the execution and/or exception blocks:
...并且因为它确实如此，您无法将变量初始化为声明块中的查询值。它必须在执行和/或异常块中：
CREATE PROCEDURE proc
(
param1; -- don't forget the data type here!
, param2; -- or here!
)
AS
variable1 INT;
BEGIN
SELECT MAX(value) INTO variable1 FROM table WHERE field = param1;
...
回答by Frank Schmitt
You could use a local function instead of the variable:
您可以使用局部函数而不是变量：
create or replace procedure proc(param1 in varchar2) as
function max_val return number is
l_result number;
begin
select max(value)
into l_result
from t1
where field = param1;
return l_result;
end;
begin
dbms_output.put_line('max val for ' || param1 || ': ' || max_val);
end;
Unfortunately, you cannot also declare a local variable and assign the value of max_val to it, since declarations for functions are allowed only after declarations for variables.
不幸的是，您也不能声明局部变量并将 max_val 的值分配给它，因为函数声明仅在变量声明之后才允许。
