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.

view source print? 1 SQL> CREATE FUNCTION  increase( x INT )  RETURN INT IS 2   2  BEGIN 3   3    x := x + 1;    -- change x, a parameter variable 4   4                   -- **ERROR — WON’T COMPILE** 5   5    RETURN  x;     -- We should have written “return x+1” 6   6  END; 7   7  / 8 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.

view source print? 01 SQL> CREATE TYPE  name_type  AS OBJECT ( 02   2    lname  VARCHAR(32), 03   3    fname  VARCHAR(32), 04   4    mi     CHAR(1) ); 05 Type created. 06   07 SQL> CREATE TYPE  person_type  AS OBJECT ( 08   2    ssno   INT, 09   3    pname  name_type, 10   4    age    INT ); 11 Type created. 12   13 SQL> CREATE TABLE  people_tab  OF  person_type; 14 Table created. 15   16 SQL> INSERT INTO  people_tab  VALUES ( 17   2    person_type( 123456789, name_type( 'Door', 'Kids', 'N' ), 36 ) ); 18 1 row created.