Transcript CH4_A
PL/SQL
Declaring
Variables
Writing Executable Statements
Interacting with the Oracle
Server
Writing Control Structures
Working with Composite
Datatypes
Cursors
Handling Exceptions
1
Objectives
After completing this lesson, you should be
able to do the following:
List the benefits of PL/SQL
Recognize the basic PL/SQL block and its
sections
Describe the significance of variables in
PL/SQL
Declare PL/SQL variables
Execute a PL/SQL block
2
About PL/SQL
PL/SQL is an extension to SQL with design
features of programming languages.
Data manipulation and query statements of SQL
are included within procedural units of code.
PL/SQL not case sensitive language
A semicolon ; must end each PL/SQL command
3
Benefits of PL/SQL
Integration
Application
Shared
library
Oracle Server
4
Benefits of PL/SQL
Improve performance
SQL
Application
SQL
SQL
Other DBMSs
SQL
Application
SQL
IF...THEN
SQL
ELSE
SQL
END IF;
SQL
Oracle with
PL/SQL
5
PL/SQL Block Structure
DECLARE – Optional
Variables, cursors, user-defined exceptions
BEGIN – Mandatory
– SQL statements
– PL/SQL statements
EXCEPTION – Optional
Actions to perform when errors occur
END; – Mandatory
DECLARE
BEGIN
EXCEPTION
END;
6
PL/SQL Block Structure
DECLARE
v_variable VARCHAR2(5);
BEGIN
SELECT
column_name
INTO
v_variable
FROM
table_name;
EXCEPTION
WHEN exception_name THEN
...
END;
DECLARE
BEGIN
EXCEPTION
END;
7
Block Types
Procedure
Function
[DECLARE]
PROCEDURE name
IS
BEGIN
--statements
BEGIN
--statements
[EXCEPTION]
[EXCEPTION]
FUNCTION name
RETURN datatype
IS
BEGIN
--statements
RETURN value;
[EXCEPTION]
END;
END;
END;
Anonymous
•These blocks can be entirely separate or nested one within another.
•Anonymous blocks are unnamed blocks.
•Subprograms are named PL/SQL blocks that can take parameters and
can be invoked.
8
Declaring Variables
10
Use of Variables
Use variables for:
Temporary storage of data
Manipulation of stored values
Reusability
Ease of maintenance
11
Handling Variables in PL/SQL
Declare and initialize variables in the
declaration section.
When you declare variable, the variables
default value is always NULL
Assign new values to variables in the
executable section.
Pass values into PL/SQL blocks through
parameters.
View results through output variables.
12
Types of Variables
25-OCT-99
TRUE
“Four score and seven years ago
our fathers brought forth upon
this continent, a new nation,
conceived in LIBERTY, and dedicated
256120.08
to the proposition that all men
are created equal.”
Atlanta
13
Types of Variables
The slide illustrates the following variable datatypes:
TRUE represents a Boolean value.
25-OCT-99 represents a DATE.
The photograph represents a BLOB.
The text of a speech represents a LONG RAW.
256120.08 represents a NUMBER datatype with precision
and scale.
The movie represents a BFILE.
The city name represents a VARCHAR2.
14
Types of Variables
PL/SQL variables:
Scalar
Composite
Reference
LOB (large objects)
Non-PL/SQL variables: Bind and host
variables
15
Types of Variables - scalar
All PL/SQL variables have a datatype, which
specifies a storage format, constraints, and
valid range of values.
Scalar datatypes hold a single value.
The main datatypes are those that
correspond to column types.
PL/SQL also supports Boolean variables.
16
Types of Variables - scalar
17
Declaring PL/SQL Variables
Syntax
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
Examples
Declare
v_hiredate
v_deptno
v_location
c_comm
DATE;
NUMBER(2) NOT NULL := 10;
VARCHAR2(13) := 'Atlanta';
CONSTANT NUMBER := 1400;
18
Declaring PL/SQL Variables
Guidelines
Follow naming conventions.
Initialize variables designated as NOT NULL
and CONSTANT.
Initialize identifiers by using the assignment
operator (:=) or the DEFAULT reserved
word.
Declare at most one identifier per line.
19
Naming Rules
Two variables can have the same name,
provided they are in different blocks.
The variable name (identifier) should not be
the same as the name of table columns used in
the block.
DECLARE
empno NUMBER(4);
BEGIN
SELECT
empno
INTO
empno
FROM
emp
WHERE
ename = 'SMITH';
END;
20
Variable Initialization and
Keywords
Using:
Assignment operator (:=)
DEFAULT keyword
NOT NULL constraint
21
Assigning Values to Variables
Syntax
identifier := expr;
Examples
Set a predefined hiredate for new
employees.
v_hiredate := '31-DEC-98';
Set the employee name to Maduro.
v_ename := 'Maduro';
22
Scalar Datatypes
• Hold a single value
• Have no internal components
25-OCT-99
256120.08
TRUE
Atlanta
23
Base Scalar Datatypes
VARCHAR2 (maximum_length)
NUMBER [(precision, scale)]
DATE
CHAR [(maximum_length)]
LONG
BOOLEAN
BINARY_INTEGER
24
Scalar Variable Declarations
Examples
v_job
v_count
v_total_sal
v_orderdate
c_tax_rate
v_valid
VARCHAR2(9);
BINARY_INTEGER := 0;
NUMBER(9,2) := 0;
DATE := SYSDATE + 7;
CONSTANT NUMBER(3,2) := 8.25;
BOOLEAN NOT NULL := TRUE;
25
Types of Variables – Reference
Directly reference specific database column or
row
Assume data type of associated column or row
%TYPE data declaration syntax:
var_name tablename.fieldname%TYPE;
%ROWTYPE data declaration syntax:
variable_name tablename%ROWTYPE;
26
The %TYPE Attribute
Declare a variable according to:
A database column definition
Another previously declared variable
Prefix %TYPE with:
The database table and column
The previously declared variable name
27
Declaring Variables
with the %TYPE Attribute
Examples
...
v_ename
v_balance
v_min_balance
...
emp.ename%TYPE;
NUMBER(7,2);
v_balance%TYPE := 10;
28
Declaring Boolean Variables
Only the values TRUE, FALSE, and NULL
can be assigned to a Boolean variable.
The variables are connected by the logical
operators AND, OR, and NOT.
The variables always yield TRUE, FALSE, or
NULL.
Arithmetic, character, and date expressions can
be used to return a Boolean value.
29
PL/SQL Record Structure
TRUE
23-DEC-98
PL/SQL table structure
1
2
3
4
SMITH
JONES
NANCY
TIM
VARCHAR2
BINARY_INTEGER
ATLANTA
PL/SQL table structure
1
2
3
4
5000
2345
12
3456
NUMBER
BINARY_INTEGER
30
LOB Datatype Variables
Book
(CLOB)
Photo
(BLOB)
Movie
(BFILE)
NCLOB
31
Displaying PL/SQL Program
Output in SQL*Plus
PL/SQL output buffer
Memory area on database server
Stores program’s output values before they are
displayed to user
Should increase size
SET SERVEROUTPUT ON SIZE buffer_size
Default buffer size
2000 bytes
32
Displaying PL/SQL Program
Output in SQL*Plus (continued)
Display program output
DBMS_OUTPUT.PUT_LINE('display_te
xt');
Display maximum of 255 characters of text data
33
Writing a PL/SQL Program
Write PL/SQL program in Notepad or another
text editor
Copy and paste program commands into
SQL*Plus
Press Enter after last program command
Type front slash ( / )
Then press Enter again
34
DBMS_OUTPUT.PUT_LINE
An Oracle-supplied packaged procedure
An alternative for displaying data from a
PL/SQL block
Must be enabled in SQL*Plus with
SET SERVEROUTPUT ON
35
PL/SQL Program Commands
36
Summary
PL/SQL blocks are composed of the
following sections:
Declarative (optional)
Executable (required)
Exception handling (optional)
A PL/SQL block can be an
anonymous block, procedure, or
function.
DECLARE
BEGIN
EXCEPTION
END;
37
Summary
PL/SQL identifiers:
Are defined in the declarative section
Can be of scalar, composite, reference, or LOB
datatype
Can be based on the structure of another variable
or database object
Can be initialized
38