Introduction to PL/SQL
Sen Zhang
1
Fundamentals
This lecture serves as an
introduction to PL/SQL –
a powerful programming language
that works hand in hand with SQL.
2
Objectives
• Learn the fundamentals of the PL/SQL programming
language
• How to write and execute PL/SQL programs in
SQL*Plus
• Understand PL/SQL data type conversion functions
• Manipulate character strings in PL/SQL programs
• Learn how to debug PL/SQL programs
3
SQL vs. PL/SQL
• As we have learned, one benefit of SQL is that it is declarative,
allowing us to easily create Oracle database tables and write
queries to insert, update, delete, and view records without
specifying too much detailed data manipulation steps.
• There is no free lunch though. The weakness for SQL is also
due to its declarative language feature: it has no support for
procedural constructs such as flow control, or loop mechanisms,
etc.
• While a single SQL command usually cannot satisfy Non-trivial
business logics; complex business logics demand more
powerful programming scheme like procedural language.
4
Procedural vs. declarative
• Procedural programming requires that the programmer tells how to
process data using detailed sequential or flow control instructions to.
– Bad: Procedural languages result in many lines of code.
– Good: Have more control on detail
• Declarative programming requires the programmer to tell what data
is needed.
– Good: The Declarative languages usually result in one statement of
the desired result.
– Bad: Have less control on data.
5
• SQL is not a procedural language but a declarative language.
• Using SQL, we carefully phrase what we want and then let the
DBMS get it for us.
• You write a single SQL declaration and hand it to the DBMS.
The DBMS then executes internal code, which is hidden from
us.
6
Why SQL is declarative not procedural?
• SQL came into existence as a relational database query
language.
• SQL was designed, evolved, optimized for structured data, i.e.
table, manipulation, saving people from considering tedious step
by step instructions.
• Being declarative is exactly what we want when it comes to data
manipulation.
• Not optimized nor designed for logics.
• This is a problem!!!
7
An example.
• Let us look at the a simplified business logic (business rule)
involved in creating a new user account, which is useful in any
user restricted database application.
– Assuming we have a user table to store account infor
– Three inputs: username, password, and password confirmation
– Verify two passwords, if not equal, report failure reason
– Otherwise, verify whether username is new or not.
• If username exists, the new account cannot be
added to the user table; report failure reason.
• If username does not exist, go ahead to insert
the new record to table; report success status.
8
Solution
• Here, at least, variables, conditional logic and branch action are
involved, what will happen is conditioned by user input and the
current database status.
• We, as programmers, can take the responsibility to coordinate
the logic, can manually type several sql statements to get the
job done.
9
End user vs. programmer
• But the terminal user is not necessarily a programmer.
• Most database users, reasonably assumed to have no
programming knowledge at all, don’t use SQL commands or
SQLPLUS to interact with a database. Instead, users use GUI
frontend interface to deal with backend database.
• The terminal user Is God! They just want to type into text
boxes, and point and click buttons and …. Wait output to show
up.
• In run time, the end users provide input data and interpret
output, but the logic needs to be implemented in the design time
in the program which should have been done by programmers.
10
• This means we have to implement this logic in a program, using
some kind of procedural programming language, not just SQL.
• Since database application is so important, other procedural
programming languages have been designed to be able to
contain SQL commands and interact with an Oracle database.
• We can write applications with SQL statements embedded
within a host language such as C++, Java, vb or vbscript etc..
• The SQL statements provide the database interface, while the
host language provides the remaining support needed for the
application to execute.
11
• We can take a lot at VB script solution in an asp page using
ODBC and ADODB
• Java can also be used to talk to Oracle through JDBC.
12
PL/SQL
• PL/SQL is the Oracle solution to this need,
– PL/SQL stands for Procedural Language/SQL.
– PL/SQL extends SQL by adding constructs found in procedural
languages, resulting in a structural language that is more powerful
than SQL.
– A procedural programming language that pure oracle applications
uses to manipulate database data.
– A complement to SQL.
– An extension to SQL, allowing us do things we cannot do in SQL
alone.
13
Good things combined
• A PL/SQL program combines good things from both sides, combining
SQL quries with procedural commands for tasks such as
– manipulating variable values,
– Evaluating IF/THEN decision control structures,
– Creating loop structures that repeat instructions multiple times until the loop
reaches an exit condition.
– Build in functions
– User defined functions
– Encapsulations using packages
14
• A Full-featured procedural programming language
• An interpreted language, which means that a program called the
PL/SQL interpreter checks each program command for syntax
errors, translates each command into machine language, and
then executes each program command, one command at a
time.
• PL/SQL commands are not case-sensitive, except for character
strings, which you must enclose in single quotation marks.
• The PL/SQL interpreter ignores blank spaces and line breaks.
• A semicolon (;) marks the end of each PL/SQL command.
• The programming style is a mixture of conventional statements
(if, while, etc.) and SQL statements.
15
What are the benefits
PL/SQL brings to us?
• Using PL/SQL, you can use various regular procedural
programming language constructs to implement complex
business logics.
• You can also develop stored procedures, functions, and triggers
easily and in a modular fashion. We are used to stored data, but
not stored commands. Yes! Here, we will be able to make
procedures stored in the database.
• The stored components are stored directly in the database,
which makes the program available to all database users if the
access privileges has been properly granted.
• This stored feature also makes it easier to manage database
applications, providing a unified API interface to different front
end applications and different front end languages.
16
Stored?
• It means surviving the sessions or connections.
• Stored on server permenently.
17
How to edit PL/SQL programs
– Type in any text editor, submitted to Oracle server through
SQL*Plus
18
Comments
– Not executed by interpreter
– C style comments (/* ... */) may be used.
• Enclosed between /* and */
– On one line beginning with --
19
PL/SQL is a programming language or
procedural language
• Since we are talking about a procedural language, we are interested in various
language constructs which constitute a procedural language.
– Reserved word
– Data type
– Variable
– Statement
– Arithmetic operation
– Logical operation
– Control constructs
• Loop
• Branch
– block
– Built in functions
– How to define user defined functions
– How to call user defined functions
– …
20
• PL/SQL is not case sensitive.
21
Reserved word
• Each of the reserved words has a special syntactic meaning to
PL/SQL.
• So, you should not use them to name program objects such as
constants, variables, or cursors.
• Also, some of these words (marked by an asterisk) are reserved
by SQL.
• So, you should not use them to name database objects such as
columns, tables, or indexes.
• http://thinkunix.net/unix/db/oracle/docs-
7.3/DOC/server/doc/PLS23/ape.htm
22
Variables and Data Types
• Information is transmitted between a PL/SQL program and the
database through variables.
• Variables need to be declared!
23
Data types
• PL/SQL is a strongly typed language, which means that you
must write a command that explicitly declares each variable and
specifies its data type before you use the variable.
• With a strongly typed language, you can assign values to
variables and compare variable values only for variables with
the same data type or compatible data type.
24
• Every PL/SQL variable has a specific type associated with it.
• There are four kinds of data types
– Scalar data type
• One of the types used by SQL for database columns
• A generic type used in PL/SQL such as NUMBER
– Composite data type
– Reference data type
• Declared to be the same as the type of some database column
– LOB data types for huge binary data used by images and sounds
25
Scalar Data Types
• Represent a single value
Data type Description Sample Declaration
Varchar2 Variable-length Lastname
character string varchar2(30)
char Fixed-length Gender char(1)
character string
Number Floating-point, fixed- Price number(5,2)
point, or integer
number
date Date and time Todays_date Date;
….
These data types are directly from data types used by SQL database
field specification.
26
Bridging role reflected in data types
• Some other data types used PL/SQL are more general purpose
programming language oriented, not corresponding to database
data types.
– INTEGER
– BOOLEAN
– DECIMAL
– …
• Note that PL/SQL allows BOOLEAN variables, even though
Oracle does not support BOOLEAN as a type for database
columns.
27
Composite data types
• Composite data types
– RECORD: contains multiple scalar values, similar to a table
record
– TABLE: tabular structure with multiple columns and rows
– VARRAY: variable-sized array
28
Reference data types
– In many cases, a PL/SQL variable will be used to manipulate
data stored in a existing table. In this case, it is essential that
the variable have the same type (compatible is also ok in
some situation) as the relation column.
– Directly reference a specific database field or record and
assume the data type of the associated field or record
• %TYPE: same data type as a database
field
• %ROWTYPE: same data type as a
database record
29
Data Types in PL/SQL can be tricky!
• If there is any type mismatch, variable assignments and
comparisons may not work the way you expect.
• To be safe, instead of hard coding the type of a variable,
• you should use the %TYPE operator.
• For example
– DECLARE myBeer Beers.name%TYPE;
– gives PL/SQL variable myBeer whatever type was declared for the
name column in relation Beers
30
Variables
• Variables
– Used to store numbers, character strings, dates,
and other data values
– Avoid using keywords, table names and column
names as variable names
– Must be declared with data type before use:
variable_name data_type_declaration;
• Userid varchar2(10);
– Default value is always NULL when declared
without being initialized.
• The initial value of any variable, regardless of its
31
type, is NULL.
Variable names are any valid PL/SQL
identifiers.
• Read book page 300 for what a valid identifier is.
32
Remarks
• Data types in a procedure definition specification cannot have
size specifications.
– For instance, you can specify that a parameter is a NUMBER
datatype, but not a NUMBER(10,2)
33
Assignment Statements
We can assign values to variables, using the ":=" operator. Like any
other programming languages you might have used before, the
assignment can occur either immediately after the type of the
variable is declared, or anywhere in the executable portion of the
program.
• Assigns a value to a variable
• variable_name := value;
• Value can be a literal:
– s_first_name := ‘Steven';
• Value can be another variable:
– first_name := s_first_name;
34
• How to link variables between SQL command and PL
command?
• We will solve this problem in sample code.
35
Some simple statements
• Return
• Goto
• Exit, break a loop
36
Arithmetic Operators in PL/SQL
Operator Description
** Exponentiation
* Multiplication
/ Division
+ and - Addition and subtraction
- negation
37
Most SQL Relational Operators
can be used for PL/SQL
Query Relational Operators
Operator Meaning Data Type Pattern
= equal to all =X
> greater than all >X
= greater than or equal to all >=X
or != not equal to all X
* wildcard Character *X, X*, *X*
? single-character wildcard Character ?X, X?, ?X?, X??
38
Logical Operators
• and, or, not
39
Expressions
• Simple arithmetic expressions
• Simple relational expressions
• Simple logical expressions
• Nested and compound expressions
40
Built-in functions
• You can also you built-in functions to perform common tasks
such as manipulating numbers or character strings.
41
PL/SQL Data Conversion Functions
42
Manipulating Character
Strings with PL/SQL
• To concatenate two strings in PL/SQL, you use the
double bar (||) operator:
– new_string := string1 || string2;
• To remove blank leading spaces use the LTRIM
function:
– string := LTRIM(string_variable_name);
• To remove blank trailing spaces use the RTRIM
function:
– string := RTRIM(string_variable_name);
• To find the number of characters in a character string
use the LENGTH function:
– string_length := LENGTH(string_variable_name);
43
Manipulating Character
Strings with PL/SQL
• To change case, use UPPER, LOWER, INITCAP
• INSTR function searches a string for a specific
substring:
– start_position := INSTR(original_string, substring);
• SUBSTR function extracts a specific number of
characters from a character string, starting at a given
point:
– extracted_string := SUBSTR(string_variable, starting_point,
number_of_characters);
44
The basic unit in PL/SQL is a block.
• All PL/SQL programs are made up of blocks, which can be
nested within each other. Typically, each block performs a
logical action in he program.
• A block has the following structure:
• DECLARE
• /* Declarative section: variables, types, and local subprograms. */
• BEGIN
• /* Executable section: procedural and SQL statements go here. */
• /* This is the only section of the block that is required. */
• EXCEPTION
• /* Exception handling section: error handling statements go here. */
• END;
45
• A PL/SQL block contains 1 or more PL/SQL statements. Such a
block must at least have the two keywords begin and end:
begin
PL contaminated SQL statements
The executable section also contains constructs such as
assignments, branches, loops, procedure calls, and ….
end;
I call them PL contaminated SQL statements, because they are not
pure SQL, they contains variables of PL part.
Sometimes, not PL contaminated for trivial operations.
http://www.adp-gmbh.ch/ora/plsql/block.html
46
• The only SQL statements allowed in a PL/SQL program are
SELECT, INSERT, UPDATE, DELETE and several other data
manipulation statements plus some transaction control.
– For most cases, they have to be PL contaminated though!
• Data definition statements like CREATE, DROP, or ALTER are
not allowed.
47
A PL/SQL block
• The header section
• Declaration section, optional
• Execution section
• Optional exception section
48
• The major nuance is that the form of the SELECT statement is
different from its SQL form. After the SELECT clause, we must
have an INTO clause listing variables, one for each attribute in
the SELECT clause, into which the components of the retrieved
tuple must be placed.
• Notice we said "tuple" rather than "tuples", since the SELECT
statement in PL/SQL only works if the result of the query
contains a single tuple.
• If the query returns more than one tuple, you need to use a
cursor,!!!! as described in the future lectures.
49
PL/SQL Program Blocks
50
The scope of A PL/SQL block
for local variables
• A PL/SQL block establishes a scope for all locally-declared
variables.
• Outside of the block, those variables do not exist.
51
Executing a PL/SQL
Program in SQL*Plus
The built-in function SYSDATE returns a DATE value
containing the current date and time on your system.
52
How to test out the PL/SQL program?
• Create program in text editor
• Paste into SQL*Plus window
• Press Enter, type / then enter to execute.
– The forward slash (/) tells SQL*PLUS to go ahead
and process the commands in the program.
53
Debugging PL/SQL Programs
• Syntax error:
– Command does not follow the guidelines of the
programming language
– Generates compiler or interpreter error messages
• Logic error:
– Program runs but results in an incorrect result
– Caused by mistakes at semantic level in
programing
54
Finding and Fixing Syntax Errors
• Interpreter flags the line number and
character location of syntax errors
• If error message appears and the flagged line
appears correct, the error usually occurs on
program lines preceding the flagged line
• Comment out program lines to look for hidden
errors
• One error (such as missing semicolon) may
cause more – fix one error at a time
55
Finding and Fixing Logic Errors
• Locate logic errors by viewing variable values
during program execution
• There is no SQL*Plus debugger
• Use DBMS_OUTPUT statements to print
variable values
56
Objectives
• Create PL/SQL decision control structures
• Use SQL queries in PL/SQL programs
• Create loops in PL/SQL programs
• Create PL/SQL tables and tables of records
• Use cursors to retrieve database data into
PL/SQL programs
• Use the exception section to handle errors in
PL/SQL programs
57
PL/SQL Control Structures
• PL/SQL allows you to branch and create
loops and function calls in the way that you
have been doing in C++/Java.
• If statement
• Loops: three different iteration constructs.
– Loop
– While
– For
– .. 58
PL/SQL Decision Control Structures
• One way branch!
– We also say the commands are conditioned.
• Use IF/THEN structure to execute code if condition is true.
– IF condition THEN
commands that execute if condition is TRUE;
END IF;
• If condition evaluates to NULL it is considered false
59
Two way decision
• Use IF/THEN/ELSE to execute code if condition is
true or false
– IF condition THEN
commands that execute if condition is TRUE;
ELSE
commands that execute if condition is FALSE;
END IF;
• Can be nested – be sure to end nested statements
60
PL/SQL Decision Control Structures
• Multi-way branches
• Use IF/ELSIF to evaluate many conditions:
– IF condition1 THEN
commands that execute if condition1 is TRUE;
ELSIF condition2 THEN
commands that execute if condition2 is TRUE;
ELSIF condition3 THEN
commands that execute if condition3 is TRUE;
...
ELSE
commands that execute if none of the
conditions are TRUE;
END IF; 61
IF/ELSIF Example
62
Complex Conditions
• Created with logical operators AND, OR and NOT
• AND is evaluated before OR
• Use () to set precedence
63
MIX SQL and PL/SQL
• DDL commands may not be used in PL/SQL
(usually we do not do that anyway.)
64
Using SQL Queries in PL/SQL Programs
(This is where SQL and PL/SQL
communicate.)
• Insert, delete and update statements (we also
call them action queries, because these
commands perform an action that changes
the data values in the database.) can be used
as in SQL*Plus
• Usually use variables in action queries
65
Using SQL Queries in PL/SQL Programs
• Select command can be used, but how to use them usually
depending on what will be returned from select command.
– Single row, then use into assign the retrieved values to variables.
Then you can manipulate the values in program commands.
– Multiple row, you want to use cursor.
66
Using an Implicit Cursor
(Implicit Cursor will be discussed!)
• Executing a SELECT query creates an implicit cursor
• To retrieve it into a variable use INTO:
– SELECT field1, field2, ...
INTO variable1, variable2, ...
FROM table1, table2, ...
WHERE join_ conditions
AND search_condition_to_retrieve_1_record;
• Can only be used with queries that return exactly one
record
67
• The select statement that finds the total number of employee is
a simple select statement with added keyword INTO. The INTO
part of the statement is required in order to put the values
returned by the select statement into the corresponding PL/SQL
variables.
• If it returns more than one record, obviously, there will
something wrong.
• To address this issue, we usually need to use explicit cursor.
68
Loops
• Program structure that executes a series of program
statements, and periodically evaluates an exit
condition to determine if the loop should repeat or
exit
• Pretest loop: evaluates the exit condition before any
program commands execute
• Posttest loop: executes one or more program
commands before the loop evaluates the exit
condition for the first time
• PL/SQL has 5 loop structures
69
The LOOP...EXIT Loop
LOOP
[program statements]
IF condition THEN
EXIT;
END IF;
[additional program statements]
END LOOP
70
The LOOP...EXIT WHEN Loop
LOOP
program statements
EXIT WHEN condition;
END LOOP;
71
The WHILE...LOOP
WHILE condition LOOP
program statements
END LOOP;
72
The Numeric FOR Loop
FOR counter_variable IN start_value .. end_value
LOOP
program statements
END LOOP;
73
Cursors
• Pointer to a memory location that the DBMS
uses to process a SQL query
• Use to retrieve and manipulate database data
74
• When you execute a SQL statement from PL/SQL, the Oracle
RDBMS assigns a private work area for that statement. This
work area contains information about the SQL statement and
the set of data returned or affected by that statement. The
PL/SQL cursor is a mechanism by which you can name that
work area and manipulate the information within it.
75
Implicit Cursor
76
Using an Implicit Cursor
• Executing a SELECT query creates an implicit cursor
• To retrieve it into a variable use INTO:
– SELECT field1, field2, ...
INTO variable1, variable2, ...
FROM table1, table2, ...
WHERE join_ conditions
AND search_condition_to_retrieve_1_record;
• Can only be used with queries that return exactly one
record
77
• The select statement that finds the total number of employee is
a simple select statement with added keyword INTO. The INTO
part of the statement is required in order to put the values
returned by the select statement into the corresponding PL/SQL
variables.
• If it returns more than one record, obviously, there will
something wrong.
78
Explicit Cursor
• Use for queries that return multiple records or
no records
• Must be explicitly declared and used
79
Cursor
• In its simplest form, you can think of a cursor as a pointer into a relation in the
database or dynamically generated from other relations.
• For example, the following cursor declaration associates the entire employee
table with the cursor named employee_cur:
– Step 1: cursor declaration
CURSOR employee_cur
IS
SELECT * FROM employee;
– Step 2: Once you have declared the cursor, you can open it:
OPEN employee_cur;
– Step 3: And then you can fetch data from it row by row, usually inside a loop
FETCH employee_cur INTO employee_rec;
In this case, each record fetched from this cursor represents an
entire record in the employee table.
– Step 4: finally, You can close the cursor:
CLOSE employee_cur;
80
Using an Explicit Cursor
• Declare the cursor
– CURSOR cursor_name IS select_query;
• Open the cursor
– OPEN cursor_name;
• Fetch the data rows
– LOOP
FETCH cursor_name INTO variable_name(s);
EXIT WHEN cursor_name%NOTFOUND;
• Close the cursor
– CLOSE cursor_name;
81
Explicit Cursor with %ROWTYPE
82
Cursor FOR Loop
• Automatically opens the cursor, fetches the records,
then closes the cursor
• FOR variable_name(s) IN cursor_name LOOP
processing commands
END LOOP;
• Cursor variables cannot be used outside loop
83
Using Cursor FOR Loop
84
Handling Runtime Errors
in PL/SQL Programs
• Runtime errors cause exceptions
• Exception handlers exist to deal with different error
situations
• Exceptions cause program control to fall to exception
section where exception is handled
85
Predefined Exceptions
86
Undefined Exceptions
• Less common errors
• Do not have predefined names
• Must declare your own name for the
exception code in the declaration section
– DECLARE
e_exception_name EXCEPTION;
PRAGMA
EXCEPTION_INIT(e_exception_name,
-Oracle_error_code);
87
User-Defined Exceptions
• Not a real Oracle error
• Use to enforce business rules
88
Summary
• PL/SQL is a programming language for working with
an Oracle database
• Scalar, composite and reference variables can be
used
• The IF/THEN/ELSE decision control structure allows
branching logic
• Five loop constructs allow repeating code
• Cursors are returned from queries and can be
explicitly iterated over
• Exception handling is performed in the exception
section. User defined exceptions help to enforce
business logic
89
Summary
• PL/SQL is a programming language for working with
an Oracle database
• Scalar, composite and reference variables can be
used
• The IF/THEN/ELSE decision control structure allows
branching logic
• Five loop constructs allow repeating code
90
What can you do using PL/SQL
• Something procedural instead of declarative.
• Stored procedure
• Stored function
• Trigger
• Cursor manipulation
• Exception control
• ….
•
91
Stored procedure
• Procedure, a function that does not return output through return value.
– Prototype, signature, and definition of the procedure,
• Procedure header includes name(a parameter list)
– A Parameter list includes a list of parameters
» A parameter list includes parameter name, in or out
mode, and parameter data type.
– Name
• Procedure body
• Stored
– Precompiled ?
– modular
92