Functions (Cont.)

In PL/SQL, parameters are not copies but the actual argument variables used in the function call, and the compiler rejects code that tries to modify them. 

 SQL> CREATE FUNCTION  increase( x INT )  RETURN INT IS
   2  BEGIN
   3    x := x + 1;    -- change x, a parameter variable
   4                   -- **ERROR — WON’T COMPILE**
   5    RETURN  x;     -- We should have written “return x+1”
   6  END;
   7  /
 Warning: Function created with compilation errors.

It would not be possible to use increase(x in out int) in arbitrary SQL statement, because SQL expressions are not supposed to change data. Since parameter variables must be read-only, we need to declare local variables to hold immediate and final results. 

 SQL> CREATE TYPE  name_type  AS OBJECT (
   2    lname  VARCHAR(32),
   3    fname  VARCHAR(32),
   4    mi     CHAR(1) );
 Type created.

 SQL> CREATE TYPE  person_type  AS OBJECT (
   2    ssno   INT,
   3    pname  name_type,
   4    age    INT );
 Type created.

 SQL> CREATE TABLE  people_tab  OF  person_type;
 Table created.

 SQL> INSERT INTO  people_tab  VALUES (
   2    person_type( 123456789, name_type( 'Door', 'Kids', 'N' ), 36 ) );
 1 row created.




      My little brother wanted to know what happens after we die.    
      I explained that we get dumped in a hole under a pile of dirt, and then worms eat our bodies.    
      I probably should have told him the truth ―    
      that most people go to hell and burn in a lake of fire for all eternity ―    
      but I didn’t want to upset him.