Chapter 1

Introduction to PL/SQL

SQL Star International Ltd 2007

Objectives
At the end of this session, you will be able to :

Understand the need for PL/SQL Learn the structure of a PL/SQL Block Create PL/SQL Blocks Declare variables Write Nested Blocks

2
SQL Star International Ltd 2007

Need for PL/SQL

The need for a Procedural language in SQL arose :

To centralize automated business needs To handle errors more efficiently

3
SQL Star International Ltd 2007

Benefits of PL/SQL
Benefits of using PL/SQL are :

Integration Performance enhancements of applications Modularized program development Portability Variable declaration Usage of control structures in programming Error handling
4

SQL Star International Ltd 2007

PL/SQL Engine

PL/SQL Engine

PL/SQL Block

Procedural statements statements

SQL statements

Procedural

PL/SQL Statement Executer

SQL Statement Executer

5
SQL Star International Ltd 2007

PL/SQL Block Structure

A basic PL/SQL block has three sections :

Declarative Section Executable Section Exception Handling Section

6
SQL Star International Ltd 2007

PL/SQL Block Structure

(contd.)

DECLARE (declarations) BEGIN (statements) EXCEPTION (error handling) END

7
SQL Star International Ltd 2007

PL/SQL Block Structure

(contd.)

Declarative section: All variables and constants declaration. Keyword: DECLARE Executable Section: Actual logic of the program. Keywords: BEGIN Exception section: Code for handling errors. Keyword: EXCEPTION END keyword: Ends the executable section of PL/SQL block

8
SQL Star International Ltd 2007

Operators in PL/SQL
The operators supported in PL/SQL are :

Arithmetic Logical Relational or Comparison Exponential (**)

9
SQL Star International Ltd 2007

SQL Function in PL/SQL

The SQL functions that can be used in PL/SQL are :

Single Row number and Character functions Datatype Conversion functions Date Functions Timestamp functions GREATEST and LEAST functions

10
SQL Star International Ltd 2007

Variables
Named memory locations to store data temporarily. Declared in the declarative section and assigned values in the executable section. Must be declared before its usage in the program.

11
SQL Star International Ltd 2007

Types of Variables
Variables are of two types :

PL/SQL variables Non-PL/SQL variables

12
SQL Star International Ltd 2007

PL/SQL Variables

Have an associated datatype that specifies a storage format, constraint and a valid range of values. Syntax: identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Example:

DECLARE BookID CHAR(13) NOT NULL; BookName CHAR(100); noofcopies NUMBER(5) := 15;
13
SQL Star International Ltd 2007

PL/SQL variables

(contd.)

Datatypes used to declare PL/SQL variables are :

Scalar Datatypes Composite Datatypes Reference Datatypes LOB Datatypes

14
SQL Star International Ltd 2007

PL/SQL variables

(contd.)

Scalar Datatypes used :

CHAR Scalar Datatypes introduced VARCHAR2 in Oracle11g LONG LONG RAW BINARY_FLOAT NUMBER BINARY_INTEGER BINARY_DOUBLE PLS_INTEGER BOOLEAN DATE TIMESTAMP TIMESTAMP WITH TIMEZONE TIMESTAMP WITH LOCAL TIMEZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
15
SQL Star International Ltd 2007

Non-PL/SQL variables
Non-PL/SQL variables include :

Substitution variables: Used to pass values at runtime. Host variables (Bind variables): Used to pass runtime values from the PL/SQL block back to iSQL*Plus environment.

16
SQL Star International Ltd 2007

Non-PL/SQL variables
Example: DECLARE

(contd.)

Substitution variables :

bookprice new_price BEGIN

NUMBER(5) := &bprice; book.bookprice%TYPE;

new_price := bookprice-10; DBMS_OUPUT.PUT_LINE('new price of the book is '|| new_price); END;

%TYPE attribute is used to declare a variable to be of the same data type as that of a previously declared variable or database column.
17
SQL Star International Ltd 2007

Non-PL/SQL variables
Host variables :
Example

(contd.)

VARIABLE new_price NUMBER DECLARE bookprice NUMBER(5) := &bprice BEGIN :new_price := bookprice-10; END; / PRINT new_price

18
SQL Star International Ltd 2007

Nested Blocks
Nesting of blocks :

Allowed wherever an executable statement exists Allowed even in Exception section of a program Breaks the program into Modules thereby, making maintenance easy

19
SQL Star International Ltd 2007

Variable Scope
Variable scope is the ability to refer to a variable within the program. Variables declared in the outermost block can always be referred within their enclosed blocks.

Example:
DECLARE a NUMBER; BEGIN .... DECLARE c NUMBER; BEGIN c := a; END; END;
20
SQL Star International Ltd 2007

Scope of c

Scope of a

Summary
In this session, we have discussed :

Need for Procedural language with SQL Benefits of using PL/SQL PL/SQL Block Types and Constructs PL/SQL Block Structure Operators and SQL Functions in PL/SQL Data Conversion functions Nested Blocks

21
SQL Star International Ltd 2007