 |
|
Oracle Tips by Burleson |
PL/SQL
Functions
A function is a PL/SQL named block that returns a
value. It is commonly used to convert or assign values. Whereas a
procedure is executed, a function is called, as in the example below:
Begin
…
get_area(10,20,n_area);
n_area := calc_area(10,20);
…
End;
In the code fragment above, the area is calculated
by a procedure named get_area and a function names calc_area.
The procedure was passed three values and it copied the calculated
area into the n_area variable when the procedure exited. The
next line uses a function that is passed two values, calculates the
area, and returns that value, which is assigned to the n_area
variable. Notice that the function is used directly in the assignment
operation. A function is defined in the format below.
create or
replace function <Name>
(<variable list>) return <datatype>
as (or is)
local
variable declaration
begin
code section
exceptions
end;
This is similar to the procedure definition except
that it uses the return definition. A function returns a datatype,
not a variable.
SQL> create
or replace function calc_area
2 (n_length in number,
3 n_width in number)
4 return number
5 as
6 begin
7 return n_length*n_width;
8 end;/
Function created.
Above the function calc_area is defined so
that it returns a number. In the function body there must be a RETURN
statement defining what is returned. In the example above, the
RETURN statement is in line 4.
A function name can be very descriptive with up to
32 characters and the function is always created in the schema of the
user that creates the function. As with the procedure, a function can
declare any number of values in the declaration section, limited only
by the usability of the function. However, unlike a procedure, a
function can not be passed variables in mode OUT or INOUT. A function
can only return a datatype. If a function is defined with an OUT or
INOUT variable, the function will compile but will raise an exception
when executed.
ORA-06572:
Function <name> has out arguments
An example used earlier in the book converted a
temperature in Fahrenheit to Celsius. This is a perfect example of a
function, which takes a value and returns a number.
SQL> create
or replace function f2c
2 (n_faren IN number)
3 return number
4 as
5 n_cel number := 0;
6 begin
7 n_cel := (5/9)*(n_faren -32);
8 return n_cel;
9 end;
10 /
Function created.
The function f2c takes a number in mode IN,
calculates the values in Celsius and returns the Celsius value. If
the function does not compile, SQL*Plus will
display the errors with the “show errors” command.
SQL> create
or replace function broken
2 (n_faren IN number)
3 as
4 n_cel number := 0;
5 begin
6 n_cel := (5/9)*(n_faren -32);
7 return n_cel;
8 end;
9 /
Warning:
Function created with compilation errors.
SQL> show
errors
Errors for FUNCTION BROKEN:
LINE/COL
ERROR
-------- ------------------------------------------------3/1
PLS-00103: Encountered the symbol "AS" when expecting one of the
following: return
5/1
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the
following: end function package
pragma
private procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
…
The function broken will not compile.
Reading PL/SQL errors
is a little different than reading
errors in other programming languages. In PL/SQL, we focus with the
top error. This is because most of the following errors will be
caused by the first error. In this example, the first error states
that the compiler found the AS key word when it expected something
else, namely the RETURN clause. A look at
the code shows that the function definition does not define a return
datatype.
As with procedures, to find the function in the
database, we query the user_objects view, as shown below.
SQL> set
pages 999
SQL> column object_name format a30
SQL> select
2 object_name,
3 object_type,
4 status
5 from user_objects
;
OBJECT_NAME
OBJECT_TYPE STATUS
------------------------------ ------------------
-------AUTHOR TABLE VALID
BOOK TABLE VALID
BOOK_AUTHOR TABLE VALID
BROKEN FUNCTION INVALID
CALC_AREA FUNCTION VALID
EMP TABLE VALID
F2C FUNCTION VALID
GET_AREA PROCEDURE VALID
GET_AREA2 PROCEDURE VALID
JOB TABLE VALID
NUM_CHECK PROCEDURE VALID
PUBLISHER TABLE VALID
SALES TABLE VALID
STORE TABLE VALID
14 rows
selected.
To remove the function broken from the
database, we can drop it with the “drop function”
command.
SQL> drop
function broken;
Function dropped.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|
