HomeOracle Page 4 - Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes
What are OUT types of parameters? - Oracle
This is part 15 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at several examples that covered the use of sub-programs. In this article we will work with PL/SQL TABLE types in between sub-programs. We will also discuss IN, OUT and IN OUT types of parameters in this article.
This is a bit different (of course quite opposite) from the case we just finished discussing. The IN parameter could never be modified (but only accessed). The OUT parameter could never be accessed (but modified and indirectly returned back). Let us consider the following example:
procedure doSquare(a IN number, z OUT number) is begin
z := a * a;
end; begin doSquare(10,b); dbms_output.put_line('Square : ' || b); end;
Even though the above is a very simple program, it gives us a lot to discuss. First of all, you should observe that ‘b’ never gets initialized within the program. It was just declared as being of type number and we are directly displaying it. And we received a value of 100. So, before displaying the value of ‘b’, we are calling a procedure named ‘doSquare’, which performs all of the magic.
The statement ‘doSquare(10,b)’ calls the procedure ‘doSquare’ by sending 10 into the parameter ‘a’. Just like 10, we are passing ‘b’ to ‘z’ (but as being of type OUT parameter). Since ‘z’ is an OUT type of parameter, it will not expect any value from ‘b’. Instead, it in turn reflects back its own value (value of ‘z’) to ‘b’. That means the parameter ‘z’ returns back its own value directly into ‘b’. It is another way of returning a value from a procedure (but indirectly, because we cannot use a RETURN statement with a procedure). So, within the procedure ‘doSquare’, whenever ‘z’ gets modified, the variable ‘b’ also gets modified with the same value, and thus ‘z’ is called an OUT type of parameter.