从 pl/sql 中的只写（OUT）参数读取

发布于 2024-09-02 10:07:31 字数 645 浏览 2 评论 0原文

当我尝试写入函数的只读参数（IN）时，Oracle 抱怨错误。但从函数的只写 (OUT) 参数读取时，情况并非如此。 Oracle 默默地允许这样做，不会出现任何错误。这种行为的原因是什么？以下代码执行时不会对“so”变量进行任何赋值：

create or replace function foo(a OUT number) return number
  is
    so number;
  begin
    so := a; --no assignment happens here
    a := 42;
    dbms_output.put_line('HiYA there');
    dbms_output.put_line('VAlue:' || so);
    return 5;
  end;
/

declare 
  somevar number;
  a number := 6;
begin
  dbms_output.put_line('Before a:'|| a);
  somevar := foo(a);
  dbms_output.put_line('After a:' || a);
end;
/

这是我得到的输出：

Before a:6
HiYA there
VAlue:
After a:42

原文

When I tried writing to an read-only parameter(IN) of a function, Oracle complains with an error. But that is not the case when reading from an write-only(OUT) parameter of a function. Oracle silently allows this without any error. What is the reason for this behaviour?.
The following code executes without any assignment happening to "so" variable:

create or replace function foo(a OUT number) return number
  is
    so number;
  begin
    so := a; --no assignment happens here
    a := 42;
    dbms_output.put_line('HiYA there');
    dbms_output.put_line('VAlue:' || so);
    return 5;
  end;
/

declare 
  somevar number;
  a number := 6;
begin
  dbms_output.put_line('Before a:'|| a);
  somevar := foo(a);
  dbms_output.put_line('After a:' || a);
end;
/

Here's the output I got:

Before a:6
HiYA there
VAlue:
After a:42

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甩你一脸翔 2024-09-09 10:07:31

允许从 OUT 参数读取：您可以在过程开始时在 OUT 参数中写入内容，并且您可能希望在返回之前读取它包含的值，这不是一个错误。

这里发生的情况是，由于它是 OUT 参数而不是 IN OUT 参数，因此 a 的值不会传递给函数 foo，因此在开头过程中 OUT 参数 a 包含 NULL 值。您可以通过注释掉 a := 42; 行来检查这一点：

SQL> create or replace function foo(a OUT number) return number
  2    is
  3      so number;
  4    begin
  5      so := a; --no assignment happens here
  6      /*a := 42;*/
  7      dbms_output.put_line('HiYA there');
  8      dbms_output.put_line('VAlue:' || so);
  9      return 5;
 10    end;
 11  /

Function created
SQL> declare
  2    somevar number;
  3    a number := 6;
  4  begin
  5    dbms_output.put_line('Before a:'|| a);
  6    somevar := foo(a);
  7    dbms_output.put_line('After a:' || a);
  8  end;
  9  /

Before a:6
HiYA there
VAlue:
After a:
         ^^ as you can see an OUT parameter is effectively "NULLed" at the
            beginning of a call

Reading from an OUT parameter is allowed: you could have written things in your OUT parameter at the beginning of a procedure and you might want to read the value it contains before returning, this is not a bug.

What happens here is that since it is an OUT parameter and not an IN OUT parameter, the value of a is not passed to the function foo, so at the beginning of the procedure the OUT parameter a contains the NULL value. You can check this by commenting out the line a := 42; :

SQL> create or replace function foo(a OUT number) return number
  2    is
  3      so number;
  4    begin
  5      so := a; --no assignment happens here
  6      /*a := 42;*/
  7      dbms_output.put_line('HiYA there');
  8      dbms_output.put_line('VAlue:' || so);
  9      return 5;
 10    end;
 11  /

Function created
SQL> declare
  2    somevar number;
  3    a number := 6;
  4  begin
  5    dbms_output.put_line('Before a:'|| a);
  6    somevar := foo(a);
  7    dbms_output.put_line('After a:' || a);
  8  end;
  9  /

Before a:6
HiYA there
VAlue:
After a:
         ^^ as you can see an OUT parameter is effectively "NULLed" at the
            beginning of a call

回复收藏 0 原文

~没有更多了~