Transcript Presentation I

PL/SQL
PL/SQL is Oracle's Procedural Language extension to SQL.
SQL enables us to create, organize, retrieve and maintain data stored in
database it does not provide the features which a typical programming
language offers, such as control-of-flow construct ( sequence, selection
and iteration construct ), or the facility to declare and use variables
Though SQL is the natural language of the DBA, it suffers from various
inherent disadvantages, when used as a conventional programming
language.
Although SQL is a very powerful tool, it's set of disadvantages prevent it
from being a fully structured programming language. For a fully
structured programming language, Oracle provides PL/SQL.
Advantages of PL/SQL for the Developer and
the Database Administrator :
PL/SQL is development tool that not only supports SQL data
manipulation but also provides facilities of conditional checking,
branching and looping.
2.
PL/SQL sends an entire block of statements to the Oracle engine at
one time. The communication between the program block and the
Oracle engine reduces considerably. This in turn reduces network
traffic.
3.
PL/SQL also permits dealing with errors as required, and facilitates
displaying user-friendly messages, when errors are encountered.
4. PL/SQL allows declaration and use of variables in blocks of code.
These variables can be used to store intermediate results of a
query for later processing, or calculate values and insert them into
an Oracle table later.
5. Via PL/SQL, all sorts of calculations can be done quickly and
efficiently without the use of the Oracle engine.
1.
Benefits of PL/SQL
 Integration
Application
Shared
library
Oracle Server
Benefits of PL/SQL
SQL
Application
SQL
SQL
Other DBMSs
SQL
Application
SQL
IF...THEN
SQL
ELSE
SQL
END IF;
SQL
Oracle with
PL/SQL
Benefits of PL/SQL



Improved maintenance
Improved data security and integrity
Improved performance
PL/SQL Program Constructs
Anonymous
block
DECLARE
Application
trigger
BEGIN
EXCEPTION
Database
trigger
Stored
procedure/
function
Application
procedure/
function
END;
Package
PL/SQL Anonymous Block
Structure
 DECLARE
(optional)
Declare PL/SQL objects to be used
within this block
 BEGIN
(mandatory)
Define the executable statements
 EXCEPTION
(optional)
Define the actions that take place if
an error arises
 END;
(mandatory)
Subprogram block structure
 Header
 IS|AS
Declaration section
 BEGIN
Executable section
 EXCEPTION
(optional)
Exception section
 END;
PL/SQL The character set:
The basic character set includes the following:
 uppercase alphabets { A - Z } .
 lowercase alphabets {a-z }
 numerals { 0 - 9 }
`
symbols: () + - */ <> = !; : .‘ @ % ,“ # $ ^& _ \ { } ? [ ]
Words used in a PL/SQL block are called Lexical Units. Blank spaces can be freely
insert between lexical units in a PL/SQL block. The spaces have no effect on the
PL/SQL block.
PL/SQL Data Types
Both PL/ SQL and Oracle have their foundations in SQL. Most PL/SQL data types are
native to Oracle's data dictionary. hence, there is a very easy integration of PL/SQL code
with the Oracle Engine.
The default data types that can be declared in PL%SQL are
numeric data},
boolean
number (for storing
char (for storing character data}, date (for storing date and time data},
(for storing TRUE, FALSE or NULL), number,
can have NULL values.
char and date data types
The %TYPE attribute provides for further integration. PL/SQL can use the %TYPE
attribute to declare variables based on definitions of columns in a table.
Use of Variables
 Use




variables for:
Temporary storage of data
Manipulation of stored values
Reusability
Ease of maintenance
Handling Variables in PL/SQL




Declare and initialize variables in the
declaration section.
Assign new values to variables in the
executable section.
Pass values into PL/SQL blocks through
parameters.
View results through output variables.
Variables:
`
Variables in PL/SQL blocks are named variables. A variable name must
begin with a character and can be followed by a maximum of 29 other
characters.
Reserved words cannot be used as variable names unless enclosed
within double quotes. Variables must be separated from each other by at
least one space or by a punctuation mark.
Constants:
Declaring a constant is similar to declaring a variable except that you
have to add the keyword 'constant' and immediately assign a value to it.
Thereafter, no further assignments to the constant are possible, while
the constant is within the scope of the PL/SQL block.
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;
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';
Variable Initialization and
Keywords
 Using:



Assignment operator (:=)
DEFAULT keyword
NOT NULL constraint
Scalar Datatypes
• Hold a single value
• Have no internal components
25-OCT-99
256120.08
TRUE
Atlanta
Base Scalar Datatypes









VARCHAR2 (maximum_length)
NUMBER [(precision, scale)]
DATE
CHAR [(maximum_length)]
LONG
LONG RAW
BOOLEAN
BINARY_INTEGER
PLS_INTEGER
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;
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
Declaring Variables
with the %TYPE Attribute
 Examples
v_ename
v_balance
v_min_balance
emp.ename%TYPE;
NUMBER(7,2);
v_balance%TYPE := 10;
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.
Composite Datatypes


PL/SQL TABLES
PL/SQL RECORDS
LOB Datatype Variables
Recipe
(CLOB)
Photo
(BLOB)
Movie
(BFILE)
NCLOB
Bind Variables
O/S
Bind Variable
Server
Referencing Non-PL/SQL
Variables
 Store
the annual salary into a
SQL*Plus host variable.
:g_monthly_sal := v_sal / 12;


Reference non-PL/SQL variables as
host variables.
Prefix the references with a colon (:).
Displaying user Messages on the Screen:
Programming tools require a method through which message can be
displayed to the user on the VDU screen.
DBMS_OUTPUT is a package that includes a number of procedure and
functions that accumulate information in a buffer so that it can be retrieved
later. These functions can also be used to display messages to the user.
PUT_LINE: Put a piece of information in the package buffer followed by an
end-of-line marker, It can also be used to display message to the user. Put
line expects a single parameter of character data type. if used to display a
message, it is the message ‘string’.
To display messages to the user the SERVEROUTPUT should be set to ON.
SERVEROUTPUT is a SQL*PLUS environment parameter that displays the
information passed as a parameter to the PUT_LINE function.
Syntax
SET SERVEROUTPUT (ON/OFF]
Comments:
A comment can have two forms'
The comment line begins with a double hyphen
treated as a comment.
(--).
The entire line will be
The comment line begins with a slash followed by an asterisk
(/*)
till the
occurrence of an asterisk followed by a slash (*/). All lines within are treated as comments.
This form of specifying comments can be used to span across multiple lines. This technique
can also be used to enclose a section of a PL/SQL block that temporarily needs to be isolated
and ignored.
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;