Summer Course 08
Oracle Database 10g
PL SQL – Part I
Presented By
Huda Amin Maghawry
TA, Information Systems Department
Overview
What is PL/SQL?
• PL/SQL is an extension to SQL with design features of
programming languages.
• Data manipulation and query statements are included within
procedural units of code.
Benefits of PL/SQL:
• Modularize program development
• A procedural language with control structures
• Handle errors
• Improves performance
2
PL/SQL Boosts Performance
SQL
SQL
Application Other DBMSs
SQL
SQL
SQL
IF...THEN
SQL
Application ELSE Oracle7 with
SQL PL/SQL
END IF;
SQL
3
PL/SQL Block Structure
DECLARE (Optional)
Variables, constants, cursors, user
defined exceptions
BEGIN (Mandatory)
SQL statements
PL/SQL control statements
EXCEPTION (Optional)
Actions to perform when errors occur
END; (Mandatory)
4
Example PL/SQL Block
DECLARE
v_product_id s_product.id%TYPE;
BEGIN
SELECT id
INTO v_product_id
FROM s_product
WHERE id = &p_product_id;
DELETE FROM s_inventory
WHERE product_id = v_product_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
INSERT INTO exception_table (message)
VALUES ('Some error occurred in the
database.');
COMMIT;
END;
5
Block Types
Anonymous Procedure Function
[DECLARE] PROCEDURE name FUNCTION name
IS RETURN datatype
IS
BEGIN BEGIN BEGIN
--statements --statements --statements
RETURN value;
[EXCEPTION] [EXCEPTION] [EXCEPTION]
END; END; END;
6
Developing a Simple
PL/SQL Block
7
Anatomy of a PL/SQL Block
• DECLARE – Optional
– Variables, constants, cursors, user-
defined exceptions
• BEGIN – Mandatory
– SQL statements
– PL/SQL control statements
• EXCEPTION – Optional
– Actions to perform when errors occur
• END; – Mandatory
8
Handling Variables in PL/SQL
• Declare and initialize variables within the declaration
section.
• Assign new values to variables within the executable
section.
• Pass values into PL/SQL blocks through parameters.
• View results through output variables.
9
Declaring Variables and Constants: Syntax
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
Guidelines
• Follow naming conventions.
• Initialize constants and variables designated as NOT
NULL.
• Initialize identifiers by using the assignment operator
(:=) or by the DEFAULT reserved word.
• Declare at most one identifier per line.
10
Declaring Scalar Variables
• Have no internal components.
• Hold a single value.
• Base Types:
– BINARY_INTEGER
– NUMBER [(precision, scale)]
– CHAR [(maximum_length)]
– LONG
– VARCHAR2(maximum_length)
– DATE
– BOOLEAN
11
Scalar Variable
Declarations: Examples
v_gender CHAR(1);
v_count BINARY_INTEGER := 0;
v_total_sal NUMBER(9,2) := 0;
v_order_date DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;
12
Operators in PL/SQL: Examples
• Set the value of a Boolean flag.
v_equal := (v_n1 = v_n2);
• Validate an employee number if it contains a value.
v_valid := (v_emp_id IS NOT NULL);
13
Nested Blocks and Variable Scope
• Statements can be nested wherever an executable
statement is allowed.
• Nested block becomes a statement.
• Exception section can contain nested blocks.
• Scope of an object is the region of the program that can
refer to the object.
• Identifier is visible in the regions in which you can
reference the unqualified identifier.
– A block can look up to the enclosing block.
– A block cannot look down to enclosed blocks.
14
Nested Blocks and
Variable Scope: Example
...
x BINARY_INTEGER;
BEGIN Scope of x
...
DECLARE
y NUMBER;
BEGIN Scope of y
...
END;
...
END;
15
Commenting Code
Comment code by
• Prefixing the comment with two dashes (- -).
• Placing the comment between /* and */.
Example
...
v_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
monthly salary input from the user */
v_sal := v_sal * 12;
END;
16
%TYPE Attribute
17
The %TYPE Attribute
• Declare a variable according to
– Another previously declared variable.
– A database column definition.
• Prefix %TYPE with
– The database table and column.
– The previously declared variable name.
• PL/SQL determines the datatype and size of the
variable.
18
The %TYPE Attribute: Examples
...
v_last_name s_emp.last_name%TYPE;
v_first_name s_emp.first_name%TYPE;
v_balance NUMBER(7,2);
v_minimum_balance v_balance%TYPE := 10;
...
Advantages of using the %TYPE attribute
• The datatype of the underlying database column
may be unknown.
• The datatype of the underlying database column
may change at runtime.
19
%ROWTYPE Attribute
20
The %ROWTYPE Attribute
• Declare a variable according to a collection of columns in
a database table or view.
• Prefix %ROWTYPE with the database table or view.
• Fields in the record take their names and data types from
the columns of the table or view.
• Syntax:
DECLARE
identifier reference%ROWTYPE;
21
Advantages of Using %ROWTYPE
• The number and data types of the underlying
database columns need not be known.
• The number and data types of the underlying
database column may change at run time.
• The attribute is useful when retrieving a row with
the SELECT * statement.
22
DataType Conversion
23
Datatype Conversion
• Convert data to comparable datatypes.
• Mixed datatypes can result in an error and affect
performance.
• Conversion functions:
– TO_CHAR
– TO_DATE
– TO_NUMBER
24
Datatype Conversion: Example
• This statement produces a compile error.
v_comment := USER||': '||SYSDATE;
• To correct the error, the TO_CHAR conversion
function is used.
v_comment := USER||': '||TO_CHAR(SYSDATE);
25
Interacting with the
Database
26
SQL Commands in PL/SQL
• Extract a row of data from the database by using the
SELECT command.
• Make changes to rows in the database by using DML
commands.
• Control a transaction with COMMIT or ROLLBACK
commands.
27
Retrieving Data: Syntax
Retrieve data from the database with SELECT.
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;
• INTO clause is required.
• Exactly one row must be returned.
• Full SELECT syntax is available.
28
Retrieving Data: Example
Retrieve the order date and the ship date for the
specified order.
DECLARE
v_date_ordered s_ord.date_ordered%TYPE;
v_date_shipped s_ord.date_shipped%TYPE;
BEGIN
SELECT date_ordered, date_shipped
INTO v_date_ordered, v_date_shipped
FROM s_ord
WHERE id = v_ord_id;
...
END ship_date;
29
Retrieving Data: Example
Return the sum of the salaries for all employees in the
specified department.
DECLARE
v_sum_salary s_emp.salary%TYPE;
BEGIN
SELECT SUM(salary) --group function
INTO v_sum_salary
FROM s_emp
WHERE dept_id = v_dept_id;
END sum_emp;
30
Retrieving Data: Example
Retrieve all information about the specified department
DECLARE
dept_record s_dept%ROWTYPE;
BEGIN
SELECT *
INTO dept_record --PL/SQL RECORD
FROM s_dept
WHERE id = v_dept_id;
...
END all_dept;
31
SELECT Exceptions
• SELECT statements in PL/SQL must retrieve exactly
one row.
• If zero or more than one row is retrieved, an
exception is raised.
• SELECT exceptions:
– TOO_MANY_ROWS
– NO_DATA_FOUND
32
TOO_MANY_ROWS Exception: Example
Retrieve the order for customer number 208.
PROCEDURE my_order
IS
v_customer_id s_ord.customer_id%TYPE := 208;
v_ord_id s_ord.id%TYPE;
BEGIN
-- Customer 208 has several orders.
SELECT id
INTO v_ord_id
FROM s_ord
WHERE customer_id = v_customer_id;
... --TOO_MANY_ROWS
END my_order;
33
NO_DATA_FOUND Exception: Example
Retrieve the order for customer number 999.
PROCEDURE my_order
IS
v_customer_id s_ord.customer_id%TYPE := 999;
v_ord_id s_ord.id%TYPE;
BEGIN
-- Customer 999 is non-existent.
SELECT id
INTO v_ord_id
FROM s_ord
WHERE customer_id = v_customer_id;
...
-- NO_DATA_FOUND
END my_order;
34
Flow Control in PL/SQL
35
Controlling PL/SQL Flow of Execution
Change the logical flow of statements by using
control structures:
• Conditional control structures (IF statement)
• Loop control structures
– Basic loop
– FOR loop
– WHILE loop
36
The IF Statement: Syntax
• You can perform actions selectively based upon
conditions being met.
IF condition THEN
statements;
[ELSE
statements;]
END IF;
• END IF is two words.
• At most, one ELSE clause is permitted.
37
The IF Statement: Syntax
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
• ELSIF is one word.
38
Simple IF Statements: Example
Set the job title to Sales Representative and the
region number to 35 if the last name is Dumas.
. . .
IF v_last_name = 'Dumas' THEN
v_job := 'Sales Representative';
v_region_id := 35;
END IF;
. . .
39
IF-THEN-ELSE Statements: Example
Set a flag for orders where there are fewer than 5
days between order date and ship date.
. . .
IF v_date_shipped - v_date_ordered 5000 THEN
dbms_output.put_line (‘Category one’);
ELSIF sal > 3000 THEN
dbms_output.put_line (‘Category two’);
ELSE
dbms_output.put_line (‘Category three’);
END IF;
41
Handling Nulls
• When working with nulls, you can avoid some
common mistakes by keeping in mind the following
rules:
– Simple comparisons involving nulls always yield
NULL.
– Applying the logical operator NOT to a null yields
NULL.
– In conditional control statements, if the condition
yields NULL, its associated sequence of statements
is not executed.
42
Building Logical Conditions
• You can handle null values with the IS NULL
operator.
• Any expression containing a null value evaluates
to NULL.
• Concatenated expressions with null values treat
null values as an empty string.
43
LOOP Statements
• Loops repeat a statement or sequence of statements
multiple times.
• Three loop types:
– Basic loop
– FOR loop
– WHILE loop
44
Basic Loop: Syntax
• Iterate through your statements with a basic
loop.
LOOP -- delimiter
statement1; -- statements
. . .
-- EXIT statement
EXIT [WHEN condition];
-- delimiter
END LOOP;
• Without the EXIT statement, the loop would be
infinite.
45
Basic Loop: Example
Insert the first ten new line items for order number 101.
. . .
v_ord_id s_item.ord_id%TYPE := 101;
v_counter NUMBER(2) := 1;
BEGIN
. . .
LOOP
INSERT INTO s_item (ord_id, item_id)
VALUES (v_ord_id, v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 10;
END LOOP;
. . .
46
FOR Loop: Syntax
• Use a FOR loop to shortcut the test for the number of
iterations.
FOR index in [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
• Do not declare the index; it is declared implicitly.
47
FOR Loop: Example
Guidelines
• Reference the index within the loop only; it is
undefined outside the loop.
• Use an expression to reference the existing value of
an index.
• Do not reference the index as the target of an
assignment.
48
WHILE Loop: Syntax
Use the WHILE loop to repeat statements while a
condition is TRUE.
WHILE condition LOOP Condition is
statement1; evaluated at the
beginning of
statement2; each iteration.
. . .
END LOOP;
49
WHILE Loop: Example
Insert the first ten new line items for order number 101.
. . .
v_ord_id s_item.ord_id%TYPE := 101;
v_counter NUMBER(2) := 1;
BEGIN
. . .
WHILE v_counter > LOOP
v_counter :=v_counter+1;
EXIT WHEN v_counter>10;
> LOOP
...
EXIT Outer_loop WHEN total_done = ‘YES’;
--Leave both loops
EXIT WHEN inner_done = ‘YES’;
--Leave inner loop only
...
END LOOP Inner_loop;
...
END LOOP Outer_loop;
END;
52
Subprograms
53
What Are PL/SQL Program Units?
• Named PL/SQL blocks
• Three main categories:
– Procedures to perform actions
– Functions to compute a value
– Packages to bundle logically related procedures and
functions
• Stored in the database or developed as an
application subprogram.
54
Benefits of Subprograms
• Easy maintenance
• Improved data security and integrity
• Improved performance
• Improved code clarity
55
Subprogram Components
HEADER – Mandatory
Subprogram name, type, and
arguments
DECLARATIVE – Optional
Local identifiers
EXECUTABLE – Mandatory
SQL statements
PL/SQL control statements
EXCEPTION – Optional
Actions to perform when errors occur
END; – Mandatory
56
Procedural Parameter Modes
Procedure
IN Argument
Calling
Environment OUT Argument
IN OUT Argument
(DECLARE)
BEGIN
EXCEPTION
END;
57
Procedure or Function?
Procedure Function
Calling Calling
Environment IN Argument Environment IN Argument
OUT Argument
IN OUT Argument
(DECLARE) (DECLARE)
BEGIN BEGIN
EXCEPTION EXCEPTION
END; END;
58
Stored Procedures
What Is a Procedure?
• A procedure:
– Is a type of subprogram that performs an
action
– Can be stored in the database as a schema
object
– Promotes reusability and maintainability
60
Creating a Procedure: Syntax
PROCEDURE name
[(parameter,...)]
IS
pl/sql_block;
where the parameter syntax is
parameter_name [IN | OUT | IN OUT] datatype
[{:= | DEFAULT} expr]
61
Syntax for Creating Stored Procedures
– Use CREATE PROCEDURE followed by the name,
optional parameters, and keyword IS or AS.
– Add the OR REPLACE option to overwrite an existing
procedure.
– Write a PL/SQL block containing local variables,
a BEGIN, and an END (or END procedure_name).
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode] datatype1,
parameter2 [mode] datatype2, ...)]
IS|AS
[local_variable_declarations; …]
BEGIN PL/SQL Block
-- actions;
END [procedure_name];
62
What Are Parameters?
• Parameters:
– Are declared after the subprogram name in the
PL/SQL header
– Pass or communicate data between the caller and the
subprogram
– Are used like local variables but are dependent on
their parameter-passing mode:
– An IN parameter (the default) provides values for
a subprogram to process.
– An OUT parameter returns a value to the caller.
– An IN OUT parameter supplies an input value,
which may be returned (output) as a modified
value.
63
Summary of Parameter Modes
IN OUT IN OUT
Default mode Must be Must be specified
specified
Value is passed into Returned to Passed into
subprogram calling subprogram; returned
environment to calling environment
Formal parameter acts Uninitialized Initialized variable
as a constant variable
Actual parameter can Must be a Must be a variable
be a literal, expression, variable
constant, or initialized
variable
Can be assigned a Cannot be Cannot be assigned
default value assigned a default value
a default value
64
Creating a Procedure: Example
PROCEDURE change_salary
(v_emp_id IN NUMBER,
v_new_salary IN NUMBER)
IS
BEGIN
UPDATE s_emp
SET salary = v_new_salary
WHERE id = v_emp_id;
COMMIT;
END change_salary;
65
Invoking Procedures
You can invoke procedure:
• Using EXECUTE
EXECUTE raise_salary(7900,10)
• Using an anonymous PL/SQL block
BEGIN
raise_salary(7900, 10);
...
END;
66
Removing Procedures
• You can remove a procedure that is stored in the
database.
– Syntax:
DROP PROCEDURE procedure_name
– Example:
DROP PROCEDURE raise_salary;
67
Stored Functions
Overview of Stored Functions
• A function:
– Is a named PL/SQL block that returns a value
– Can be stored in the database as a schema
object for repeated execution
– Is called as part of an expression or is used to
provide a parameter value
– Add a RETURN clause with the datatype in the
declaration of the function.
– Include at least one RETURN statement in the
PL/SQL block.
69
Creating a Function: Syntax
FUNCTION name
[(parameter,...)]
RETURN datatype
IS
pl/sql_block;
• Remember to include at least one RETURN
statement in the PL/SQL block.
70
Creating a Function: Example
Return the tax based on the value.
FUNCTION tax
(v_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (v_value * .07);
END tax;
71
Invoking Functions in SQL Statements:
Example
Enter a SQL statement with a function call at the
Interpreter prompt.
PL/SQL> SELECT total, tax(total)
+> FROM s_ord
+> WHERE id = 100;
TOTAL TAX(TOTAL)
------------- ----------
601100.00 42077
72
Syntax for Creating Stored Functions
• The PL/SQL block must have at least one RETURN
statement.
CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [mode1] datatype1, ...)]
RETURN datatype IS|AS
[local_variable_declarations; …]
BEGIN
-- actions;
PL/SQL Block
RETURN expression;
END [function_name];
73
Stored Function: Example *
• Create the function:
CREATE OR REPLACE FUNCTION get_sal
(id emp.empno%TYPE) RETURN NUMBER IS
salary emp.sal%TYPE := 0;
BEGIN
SELECT sal
INTO salary
FROM emp
WHERE empno = id;
RETURN salary;
END get_sal;
/
74
Ways to Execute Functions
• Using a local variable to obtain the result
DECLARE sal employees.salary%type;
BEGIN
sal := get_sal(100); ...
END;
• Use as a parameter to another subprogram
EXECUTE dbms_output.put_line(get_sal(100))
• Use in a SQL statement (subject to restrictions)
SELECT job_id, get_sal(employee_id) FROM employees;
75
Function in SQL Expressions: Example
CREATE OR REPLACE FUNCTION tax(value IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (value * 0.08);
END tax;
/
SELECT employee_id, last_name, salary, tax(salary)
FROM employees
WHERE department_id = 100;
Function created.
6 rows selected.
76
Removing Functions
• Removing a stored function:
DROP FUNCTION function_name
• Example:
DROP FUNCTION get_sal;
77
Procedures Versus Functions
Procedures Functions
Execute as a PL/SQL Invoke as part of an
statement expression
Do not contain RETURN Must contain a RETURN
clause in the header clause in the header
Can return values (if any) Must return a single value
in output parameters
Can contain a RETURN Must contain at least one
statement without a value RETURN statement with
to exit from procedure value
78
Thanks You