开发者

Reading from an write-only(OUT) parameter in pl/sql

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


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜