Functions (Cont.)
Question: Write a function that adds one to a person_type object’s age.
SQL> CREATE FUNCTION inc_age( x person_type ) RETURN person_type IS
2 nper person_type := person_type( x.ssno, x.pname, x.age );
3 -- clone x to local object nper
4 BEGIN
5 nper.age := x.age + 1;
6 -- change age in LOCAL VARIABLE, not parameter
7 RETURN nper; <
8 END;
9 /
Function created.
SQL> SELECT inc_age( value( p ) ) FROM people_tab p;
INC_AGE(VALUE(P))(SSNO, PNAME(LNAME, FNAME, MI), AGE)
-------------------------------------------------------------
PERSON_T(123456789, NAME_T('Door', 'Kids', 'N'), 37)
SQL> UPDATE people_tab p SET p = inc_age( value( p ) )
2 WHERE p.age < 40;
|
|
- Can’t use
x in the object constructor on the right: person_type(x).
- Need to use the function
VALUE to evaluate row object in expression.
- The
SELECT statement simply lists the updated values but does not actually update the database.
Discovering Errors
PL/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic message such as “procedure created with compilation errors.”
If you don’t see what is wrong immediately, try issuing the command
show ERRORS PROCEDURE <procedure_name>;
Alternatively, you can type,
SHO ERR (short for
SHOW ERRORS) to see the most recent compilation error.
Note that the location of the error given as part of the error message is not always accurate!