PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural
features of programming languages.
It was developed by Oracle Corporation in the early 90’s to
enhance the capabilities of SQL.
PL/SQL's general syntax resembles that of Ada or Pascal.
The PL/SQL Engine:
Oracle uses a PL/SQL engine to processes the PL/SQL
statements.
A PL/SQL code can be stored in the client system (client-
side) or in the database (server-side).
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.
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 PL/SQL Block consists of three sections:
The Declaration section (optional).
The Execution section (compulsory).
The Exception (or Error) Handling section (optional).
A block has the following structure:
DECLARE
/* Declarative section: variables, types, and local
subprograms( FUNCTION / PROCEDURE declarations ) */
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;
Declaration Section:
The Declaration section of a PL/SQL Block starts with the
reserved keyword DECLARE.
This section is optional and is used to declare any
placeholders like variables, constants, records and cursors,
which are used to manipulate data in the execution section.
Placeholders may be any of Variables, Constants and
Records, which stores data temporarily. Cursors are also
declared in this section.
DECLARE
/* Declarative section: variables, types, and local variables
subprograms. */
In PL/SQL, a variable is a meaningful name of a temporary
storage location that supports a particular data type in
program.
In order to use a variable, you need to declare it in declaration
section of PL/SQL block.
PL/SQL Variable Naming Convention Like other programming
languages, a variable in PL/SQL must follow the naming rules
A variable in PL/SQL must follow the naming rules
The variable name must be less than 31 characters. Try to make it as
meaningful as possible within 31 characters.
The starting of a variable must be an ASCII letter.
PL/SQL is not case-sensitive.(It can be either lowercase or uppercase.)
Space not allowed
Reserved word not allowed
Duplicate not allowed
A variable name can contain numbers, underscore, and dollar sign
characters followed by the first character.
Special symbols not allowed
Again, do not make your variables hard to read and understand to make it
easier to maintain in the future.
It is recommended that you should follow the naming conventions in the
table 1.1 below to name variables to make it obvious in PL/SQL programs:
PrefixData Type
V_ VARCHAR2
N_ NUMBER
T_ TABLE
R_ ROW
D_ DATE
B_ BOOLEAN
1 2
Declare Declare
id NUMBER; N_id NUMBER;
Name VARCHAR(20); V_Name VARCHAR(20);
Employees
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
DECLARE EMPLOYEE_ID NUMBER
v_first_name VARCHAR2(20); HIRE_DATE DATE
v_last_name VARCHAR2(20);
n_employee_id NUMBER;
d_hire_date DATE;
BEGIN
NULL;
END;
Employees
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
DECLARE EMPLOYEE_ID NUMBER
HIRE_DATE DATE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
NULL;
END;
To assign a value or a variable to a variable in PL/SQL, you use
the assignment operator ( := ) which is a colon( : ) followed by
an equal sign( = ).
See the code listing below to have a better understanding:
DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
v_first_name := 'Mary‘; v_last_name := 'Dose';
n_employee_id := 123;
d_hire_date := TO_DATE('31-12-2004','dd-mm-yyyy')
END;
Date variables
variable_name :=
TO_DATE('01-01-2005 14:20:23','DD-MM-YYYY hh24:mi:ss');
TO_DATE('31-12-2004','dd-mm-yyyy')
TO_DATE('14:20:23', 'hh24:mi:ss')
When you declare a variable, its value is uninitialized and
hence is NULL.
You can initialize variable a value by assigning it a value in
declaration section.
DECLARE
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE :=200;
BEGIN
NULL;
DECLARE
END; a NUMBER := 3;
BEGIN
a := a + 1;
END;
You can use INTO of SQL SELECT statement to assign a value to
a variable.
In this way, the INTO clause move the values from the SELECT
query’s column list into corresponding PL/SQL variables.
DECLARE
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
n_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
d_hire_date EMPLOYEES.HIRE_DATE%TYPE;
BEGIN
SELECT employee_id, first_name, last_name, hire_date
INTO n_employee_id, v_first_name, v_last_name, d_hire_date
FROM employees WHERE employee_id = 200;
DBMS_OUTPUT.PUT_LINE(n_employee_id);
DBMS_OUTPUT.PUT_LINE(v_first_name);
DBMS_OUTPUT.PUT_LINE(v_last_name);
DBMS_OUTPUT.PUT_LINE(d_hire_date);
END;
You declare constants the same way as variables, except for
the addition of the keyword CONSTANT and the mandatory
assignment of a value.
Constants do not take attributes other than the value.
An example follows:
DECLARE
interest CONSTANT REAL(5,2) := 759.32;
Information is transmitted between a PL/SQL program and
the database through variables.
Every variable has a specific type associated with it. That
type can be one of the types used by SQL for database
columns.
o A generic type used in PL/SQL such as NUMBER, CHAR, DATE, …etc
o Declared to be the same as the type of some database column
Declar
Std_id number(2);
Std_name Students.sname%TYPE;
Std_record Students%ROWTYPE;
Begin
select sid into std_id from students where sname=‘ALI’;
select sname into std_name from students where
sid=12345;
select * into std_record from students where sid=12345;
End;
Introducing PL/SQL block structure and anonymous block
BEGIN
/* Executable section: procedural and SQL statements go here. */
/* This is the only section of the block that is required. */
End;
PL/SQL program units organize the code into blocks.
A block without name is known as anonymous block.
The anonymous block is the simplest unit in PL/SQL.
It is called anonymous block because it is not saved in the
database.
The anonymous blocks are only one-time use and useful in
certain situations such as creating test units.
The following illustrates anonymous block syntax:
DECLARE
Declaration statements;
BEGIN
Execution statements;
[EXCEPTION]
EXCEPTION handling statements;
END;
/
Let’s take a look at the simplest block that does nothing.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
DMBS_OUTPUT.PUT_LINE('Hello PL/SQL');
END;
The first command does not belong to the anonymous block.
It is telling SQL*Plus to echo the database’s output to the
screen after executing PL/SQL procedure.
In the above examples, you just uses the execution part to
execute code. You will learn how to declare variables and
handling exceptions in the next tutorials.
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;