Transcript 01365p_chapter_06

P
L
/
S
Q
L
Oracle10g Developer:
PL/SQL Programming
Chapter 6
Functions
Chapter Objectives
P
L
/
S
Q
L
• After completing this lesson, you should be
able to understand:
–
–
–
–
Functions
Creating a stored function in SQL*Plus
Using OUT parameters in functions
Including multiple RETURN statements in a
function
– Using a RETURN statement in a procedure
– Using constraints of actual and formal
parameters
Oracle10g Developer: PL/SQL Programming
2
Chapter Objectives (continued)
P
L
/
S
Q
L
• After completing this lesson, you should be
able to understand (continued):
• Understanding and controlling the passing of
parameter values
• Working with function purity levels
• Referencing the data dictionary for program
units
• Deleting program units
Oracle10g Developer: PL/SQL Programming
3
Brewbean’s Challenge
P
L
/
S
Q
L
•Need program module to check a user login
Oracle10g Developer: PL/SQL Programming
4
Brewbean’s Challenge (continued)
P
L
/
S
Q
L
•Need program module to calculate shipping cost based on the number
of items in the basket
Oracle10g Developer: PL/SQL Programming
5
Introduction to Functions
P
L
/
S
Q
L
• A function is similar to a procedure in that it
can accomplish a task and retrieve/return
values
• A function is part of an expression, not an
entire statement such as a procedure
• Can be used in both PL/SQL and SQL
statements
• Same as Oracle-supplied functions (ROUND,
TO_CHAR)
• Contains a RETURN statement
Oracle10g Developer: PL/SQL Programming
6
Example of Oracle-Supplied
Function
P
L
/
S
Q
L
SELECT idProduct, price, ROUND(price, 0)
FROM bb_product
WHERE idProduct < 4;
Oracle10g Developer: PL/SQL Programming
7
Function Create Statement
P
L
/
S
Q
L
Oracle10g Developer: PL/SQL Programming
8
Function Example
P
L
/
S
Q
L
•Shipping cost
Oracle10g Developer: PL/SQL Programming
9
Invoking a Function from a Block
P
L
/
S
Q
L
•An assignment statement is used – a function RETURNS a value!
Oracle10g Developer: PL/SQL Programming
10
Attempt to Invoke Stand-alone
P
L
/
S
Q
L
Oracle10g Developer: PL/SQL Programming
11
Use Function in SQL
P
L
/
S
Q
L
Oracle10g Developer: PL/SQL Programming
12
Brewbean’s Member Display
P
L
/
S
Q
L
CREATE OR REPLACE FUNCTION memfmt1_sf
(p_id IN NUMBER,
p_first IN VARCHAR2,
p_last IN VARCHAR2)
RETURN VARCHAR2
IS
lv_mem_txt VARCHAR2(35);
BEGIN
lv_mem_txt := 'Member '||p_id||' - '||p_first||'
'||p_last;
RETURN lv_mem_txt;
END;
Oracle10g Developer: PL/SQL Programming
13
Using OUT Mode in a Function
P
L
/
S
Q
L
• OUT parameters are not typically used in
functions, as:
– Mixing OUT and RETURN values can lead
to confusion
– It prohibits the function from being used in
SQL
Oracle10g Developer: PL/SQL Programming
14
Multiple RETURN Statements
P
L
/
S
Q
L
Note: Only one RETURN statement can execute
Oracle10g Developer: PL/SQL Programming
15
RETURN Statement in a Procedure
P
L
/
S
Q
L
• Different purpose than a RETURN
statement in a function
• Used to change flow of execution
• Stops processing in that block and
moves to the next statement after the
procedure call
• Stand-alone statement with no
arguments
Oracle10g Developer: PL/SQL Programming
16
Parameter Constraints
P
L
/
S
Q
L
• Formal parameters – included in a program
unit
• Actual parameters – arguments used in a
program unit call
• Argument for an OUT parameter must be a
variable to hold the value returned
• Actual parameters determine the size of the
formal parameters
Oracle10g Developer: PL/SQL Programming
17
Passing Parameter Values
P
L
/
S
Q
L
•
Two techniques used to pass values
between actual and formal parameters:
1. Passed by Reference – create pointer to
value in the actual parameter
2. Passed by Value – copies value from
actual to formal parameter
•
•
Pass by value is the default
Use a compiler hint to use pass by reference
Oracle10g Developer: PL/SQL Programming
18
Pass by Reference
P
L
/
S
Q
L
Compiler Hint
Oracle10g Developer: PL/SQL Programming
19
Purity Levels
P
L
/
S
Q
L
• Restrictions on functions used in SQL
– Functions cannot modify any tables in Oracle8
and prior versions
• Beginning with Oracle8i, the function cannot
modify a table used in the SQL statement that
calls the function; however, it may alter other
tables if called from a non-select statement
– If used in a remote or parallel operation, no
reading or writing of packaged variables allowed
– If used in a SELECT, VALUES, or SET clause,
the function can write values to packaged
variables; otherwise, it is not allowed
Oracle10g Developer: PL/SQL Programming
20
Purity Levels (continued)
P
L
/
S
Q
L
• Restrictions on functions used in SQL
(continued)
– Functions cannot be used in a check constraint
or as a default value of a table column
– If the function calls other subprograms, the
subprograms cannot break these rules
– Must be a stored database object (or in a
stored package)
– Can use only IN parameters
Oracle10g Developer: PL/SQL Programming
21
Purity Levels (continued)
P
L
/
S
Q
L
• Restrictions on functions used in SQL
(continued)
– Formal parameter data types must use database
data types (no PL/SQL data types such as
BOOLEAN are permitted)
– Return data types must be a database data type
– Must not issue transaction control statements to
end the current transaction prior to execution
– Cannot issue ALTER SESSION or ALTER
SYSTEM commands
Oracle10g Developer: PL/SQL Programming
22
Purity Levels (continued)
P
L
/
S
Q
L
Level Acronym
Level Name
Level Description
WNDS
Writes No
Database State
Function does not modify any
database tables (No DML)
RNDS
Reads No
Database State
Function does not read any tables
(No select)
WNPS
Writes No
Package State
Function does not modify any
packaged variables (packaged
variables are variables declared in
a package specification; they are
discussed in detail in Chapter 6)
RNPS
Reads No
Package State
Function does not read any
packaged variables
Oracle10g Developer: PL/SQL Programming
23
Data Dictionary Information
P
L
/
S
Q
L
• DESCRIBE identifies parameters and return
value data type
Oracle10g Developer: PL/SQL Programming
24
Data Dictionary Information
(continued)
P
L
/
S
Q
L
• View source code using USER_SOURCE
Oracle10g Developer: PL/SQL Programming
25
Delete Functions
P
L
/
S
Q
L
DROP FUNCTION function_name;
Oracle10g Developer: PL/SQL Programming
26
Summary
P
L
/
S
Q
L
• Functions can be used in PL/SQL and SQL
statements
• A function is part of an expression
• Functions include parameters and must return
a value
• OUT parameter rarely used
• Pass parameter values by value or reference
Oracle10g Developer: PL/SQL Programming
27
Summary (continued)
P
L
/
S
Q
L
• Actual versus formal parameters
– Formal parameters – included in a program
unit
– Actual parameters – arguments used in a
program unit call
• Purity levels
• USER_SOURCE view
Oracle10g Developer: PL/SQL Programming
28