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