Enhanced Guide to Oracle8i

Chapter 4:
Introduction to PL/SQL

1
What is PL/SQL?
 Procedural programming language
 Uses detailed instructions
 Processes statements sequentially
 Combines SQL commands with
procedural instructions
 Used to perform sequential
processing using an Oracle
database
2
PL/SQL Variables
 Variable names must follow the Oracle
naming standard
 Can use reserved words (BEGIN, NUMBER) and
table names for variable names, but is not a good
practice
 Make variable names descriptive
 Use lower-case letters, and separate words
with underscores
 Example: current_s_id

3
Declaring PL/SQL Variables
 PL/SQL is a strongly-typed language
 All variables must be declared prior to use
 Syntax for declaring a variable:
variable_name data_type_declaration;
 Example:
current_s_id NUMBER(6);

4
PL/SQL Data Types
 Scalar
 References a single value
 Composite
 References a data structure
 Reference
 References a specific database item
 LOB
 References a large binary object

5
Scalar Data Types
 Database scalar data types:
 VARCHAR2
 CHAR
 DATE
 LONG
 NUMBER
 Non-database scalar data types:
 Integers: BINARY_INTEGER, INTEGER, INT, SMALLINT
 Decimal numbers: DEC, DECIMAL, DOUBLE,
PRECISION, NUMERIC, REAL
 BOOLEAN

6
Composite Data Types
 Reference multiple data elements, such
as a record
 Types:
 RECORD
 TABLE
 VARRAY
 Tabular structure that can expand or contract
as needed

7
Reference Data Types
 Reference a database item
 Assume data type of item
 %TYPE: assumes data type of field
 %ROWTYPE: assumes data type of entire
row

8
 PL/SQL Program Structure
DECLARE Variable
Variable
Variable declarations Declarations
Declarations

BEGIN
Body
Program statements Body

EXCEPTION
Error-handling statements Exception
Exception
Section
Section
END;

9
PL/SQL Program Lines
 May span multiple text editor lines

 Each line ends with a semicolon

 Text is not case sensitive

10
Comment Statements
 Block of comments are delimited with /*
*/

/* <comment that spans more than one

line of code> */

 Single comment line starts with 2

hyphens
-- comment on a single line
11
 Arithmetic Operators
Example Result

** Exponentiation 2 ** 3 8
* Multiplication 2*3 6
/ Division 9/2 4.5
+ Addition 3+2 5
- Subtraction 3–2 1
- Negation -5 Negative 5

12
Assignment Statements
 Assignment operator: :=
 Variable being assigned to a new value
is on left side of assignment operator
 New value is on right side of operator

student_name := ‘John Miller’;

student_name := current_student;

13
Displaying PL/SQL Output
in SQL*Plus
 Normally PL/SQL is used with other Oracle
utilities such as forms or reports
 You will learn to use PL/SQL in SQL*Plus
 Command to activate memory buffer in
SQL*Plus to enable output from PL/SQL
programs:
SQL> SET SERVEROUTPUT ON SIZE buffer_size;

SQL> SET SERVEROUTPUT ON SIZE 4000;

14
 Displaying PL/SQL Program
Output in SQL*Plus
 Command to output data from a PL/SQL
program in SQL*Plus:
DBMS_OUTPUT.PUT_LINE(‘output string’);

DBMS_OUTPUT.PUT_LINE(‘Current Output:’);

15
 Executing a PL/SQL Program
in SQL*Plus
 Copy program code from Notepad to
SQL*Plus
 Type / to execute

16
 PL/SQL Data Type Conversion
Functions
 TO_DATE: character string to DATE
TO_DATE(‘07/14/01’, ‘MM/DD/YY’);
 TO_NUMBER: character string to NUMBER
TO_NUMBER(‘2’);
 TO_CHAR: NUMBER or DATE to character string
TO_CHAR(2);
TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH:MI’);

17
 Character String Functions
 Concatenating strings: joining 2 or more
character strings into a single string
 Concatenation operator: ||
s_first_name := ‘Sarah’
s_last_name := ‘Miller’
s_full_name := s_first_name || ‘ ’
|| s_last_name

18
 PL/SQL Character String
Functions
 RTRIM: removes blank trailing spaces
cust_address := RTRIM(cust_address);
 LENGTH: returns string length (number of
characters)
address_length := LENGTH(cust_address);
 UPPER, LOWER: changes characters to all upper
or lower case
s_name := UPPER(s_name);
s_name := LOWER(s_name);

19
 PL/SQL Character String
Functions
 INSTR: searches a string and looks for a
matching substring and returns its starting
position
starting_position :=
INSTR(string_being_searched,
search_string>);

blank_position :=
INSTR(‘Sarah Miller’, ‘ ’);

20
 PL/SQL Character String
Functions
 SUBSTR: extracts a specific number of
characters from a string, starting at a given
point
extracted_string :=
SUBSTR(string_being_searched,
starting_point,
number_of_characters_to_extract);

s_first_name := SUBSTR(‘Sarah Miller’, 1,5);

21
Debugging PL/SQL Programs
 Syntax error
 Does not follow language guidelines
 Causes a PLS- compile error
 Examples: misspelling a reserved word,
using a function incorrectly
 Logic error
 Program compiles correctly, but does not
give correct output

22
Locating and Correcting
Syntax Errors
 Isolate the line that is causing the error
 This may be before or after the line that is
flagged by the compiler
 Comment out lines as necessary until
program runs
 One error may cause several cascading
errors, so re-run program after fixing
each error

23
Locating and Fixing Logic
Errors
1. Identify the output variable(s) that have the
error.
2. Identify the inputs and calculations that
contribute to the error.
3. Display the values of the inputs using
DBMS_OUTPUT commands.
4. Take a break and look at it again later.
5. Ask a fellow student for help.
6. Ask your instructor for help.

24
NULL Values in Assignment
Statements
 Until a value is assigned to a variable,
the variable’s value is NULL
 Performing an arithmetic value on a
NULL value always results in a NULL
value

 Advice: Always initialize variable values

25
PL/SQL
IF/THEN
Selection Structures
 IF/END IF:
IF condition THEN
program statements
END IF;

 IF/ELSE/END IF:
IF condition THEN
program statements
ELSE
alternate program statements
END IF;

26
PL/SQL Selection Structures
 IF/ELSIF:
IF condition1 THEN
program statements;
ELSIF condition2 THEN
alternate program statements;
ELSIF condition3 THEN
alternate program statements;
. . .
ELSE
alternate program statements;
END IF;

27
PL/SQL Comparison Operators
Operator Description Example
= Equal Count = 5
<>, != Not Equal Count <> 5
> Greater Than Count > 5
< Less Than Count < 5
>= Greater Than or Equal Count >= 5
To
<= Less Than or Equal To Count <= 5

28
Evaluating NULL Conditions in
IF/THEN Structures
 If a condition evaluates as NULL, then it
is FALSE
 How can a condition evaluate as NULL?
 It uses a BOOLEAN variable that has not
been initialized
 It uses any other variable that has not
been initialized

29
Using SQL Commands in
PL/SQL Programs
SQL Command Purpose Examples Can be
Category used in
PL/SQL
Data Definition Change CREATE, No
Language (DDL) database ALTER,
structure GRANT,
REVOKE
Data View or change SELECT, Yes
Manipulation data INSERT,
Language (DML) UPDATE,
DELETE
Transaction Create logical COMMIT, Yes
Control transactions ROLLBACK

30
PL/SQL Loops
 Loop: repeats one or more program
statements multiple times until an exit
condition is reached
 Pretest loop: exit condition is tested
before program statements are executed
 Posttest loop: exit condition is tested after
program statements are executed

31
LOOP … EXIT Loop
LOOP … EXIT
LOOP
program statements
IF condition THEN Pretest
Pretest
EXIT; OR
OR
END IF; Posttest
Posttest
more program statements
END LOOP;

32
LOOP … EXIT WHEN Loop
LOOP
program statements Posttest
Posttest
EXIT WHEN condition;
END LOOP;

33
WHILE Loop
WHILE condition
LOOP Pretest
Pretest
program statements
END LOOP;

WHILE … LOOP

34
Numeric FOR Loop
FOR counter_variable
IN start_value .. end_value
LOOP
Preset
Preset
program statements
number
numberof of
END LOOP;
iterations
iterations

35
Cursors
 Pointer to a server memory location
 Contains information about a SQL
command in a PL/SQL program
 Called the command’s context area

36
Cursors
Database Server Memory
Cursor

Number of Parsed
context rows command
area processed statement

CID CALLID CNAME CCREDIT

active set 1 MIS 101 Intro. to Info. Systems 3
2 MIS 301 Systems Analysis 3
3 MIS 441 Database Management 3
4 CS 155 Programming in C++ 3
5 MIS 451 Client/Server Systems 3

37
Types of Cursors
 Implicit
 Explicit

38
Implicit Cursors
 Created automatically every time you use an
INSERT, UPDATE, DELETE, or SELECT
command
 Doesn’t need to be declared
 Can be used to assign the output of a SELECT
command to one or more PL/SQL variables
 Can only be used if query returns one and
only one record

39
Implicit Cursor Syntax
SELECT field1, field2, …
INTO variable1, variable2, …
FROM tablename
WHERE
search_condition_that_will_
return_a_single_record;

40
Explicit Cursors
 Must be declared in program DECLARE
section
 Can be used to assign the output of a
SELECT command to one or more PL/SQL
variables
 Can be used if query returns multiple
records or no records

41
Using an Explicit Cursor
 Declare the cursor
 Open the cursor
 Fetch the cursor result into PL/SQL
program variables
 Close the cursor

42
Declaring an Explicit Cursor
DECLARE
CURSOR cursor_name IS
SELECT_statement;

43
Opening an Explicit Cursor
OPEN cursor_name;

44
Fetching Explicit Cursor
Records
FETCH cursor_name INTO
variable_name(s);

45
Closing an Explicit Cursor
CLOSE cursor_name;

46
Processing an Explicit Cursor
 LOOP ..EXIT WHEN approach:
OPEN cursor_name;
LOOP
FETCH cursor_name INTO
variable_name(s);
EXIT WHEN cursor_name%NOTFOUND:
END LOOP;
CLOSE cursor_name;

47
Processing an Explicit Cursor
 Cursor FOR Loop approach:

FOR variable_name(s) in cursor_name LOOP

additional processing statements;
END LOOP;

48
 Using Reference Data Types in
Explicit Cursor Processing
 Declaring a ROWTYPE reference variable:

DECLARE
reference_variable_name cursor_name%ROWTYPE;

 Referencing a ROWTYPE reference variable:

reference_variable_name.database_field_name

49
Explicit Cursor Attributes
Attribute Return Value
%NOTFOUND TRUE when no rows left
to fetch; FALSE when
rows left to fetch
%FOUND TRUE when rows left to
fetch; FALSE when no
rows left to fetch
%ROWCOUNT Number of rows a cursor
has fetched so far
%ISOPEN TRUE if cursor is open
and FALSE is cursor is
closed
50
PL/SQL Tables
 Data structure that Key Value
contains multiple data
items that are the same
data type 1 Shadow
 Each table item has a key
and a value
 Key values do not need to 2 Dusty
be sequential
 Used to create a lookup
table that is stored in 3 Sassy
memory to improve
processing speed

51
PL/SQL Table of Records
 PL/SQL table that can store multiple
values that are referenced by a key
 Usually used to store database records
that need to be processed by a PL/SQL
program
 Improves performance by limiting
number of database retrievals

52
PL/SQL Exception Handling
 All error handling statements are placed
in the EXCEPTION program block
 Exception handler: program command
that provides information about an
error, and suggest correction actions

53
Predefined Exceptions
 Common errors that have been given
predefined names that appear instead of
error numbers
Error Code Exception Name Description
ORA-00001 DUP_VAL_ON_INDEX Unique constraint violated
ORA-01001 INVALID_CURSOR Illegal cursor operation
ORA-01403 NO_DATA_FOUND Query returns no records
ORA-01422 TOO_MANY_ROWS Query returns more rows than
expected
ORA-01476 ZERO_DIVIDE Division by zero
ORA-01722 INVALID_NUMBER Invalid numeric conversion
ORA-06502 VALUE_ERROR Error in arithmetic or numeric
function operation

54
Exception Handler Syntax
For Predefined Exceptions

WHEN exception1_name THEN

exception handling statements;
WHEN exception2_name THEN
exception handling statements;
…
WHEN OTHERS THEN
exception handling statements;

55
Undefined Exceptions
 Less-common errors that have not been
given predefined names
 ORA- error code appears
 Exception handler tests for ORA- error
code and provides alternate error
message

56
User-Defined Exceptions
 Errors that will not cause a run-time
error, but will violate business rules
 Programmer creates a custom error
message

57
Nested PL/SQL Program
Blocks
 An inner DECLARE
variable declarations Outer
program BEGIN
program statements
block

block can DECLARE

variable declarations
be nested BEGIN
program statements Inner
within an EXCEPTION
error handling statements
block
outer END;

program EXCEPTION
error handling statements

block END;

58
Exception Handling in Nest
Program Blocks
 If an exception is raised and handled in
an inner block, program execution
resumes in the outer block

59
Exception Handling in Nested
Program Blocks
DECLARE
variable declarations
BEGIN
program statements
DECLARE
exception_A
BEGIN
RAISE exception_A Exception is raised and
EXCEPTION handled in inner block
exception_A error handler
END;
additional program statements Program execution
EXCEPTION resumes here
error handling statements
END;

60
Exception Handling in Nested
Program Blocks
 Exceptions raised in inner blocks can be
handled by exception handlers in outer
blocks

61