PL/SQL Tutorial

PL/SQL tutorial provides basic and advanced concepts of SQL. Our PL/SQL tutorial is designed for beginners
and professionals.

PL/SQL is a block structured language that can have multiple blocks in it.

Our PL/SQL tutorial includes all topics of PL/SQL language such as conditional statements, loops, arrays,
string, exceptions, collections, records, triggers, functions, procedures, cursors etc. There are also given
PL/SQL interview questions and quizzes to help you better understand the PL/SQL language.

SQL stands for Structured Query Language i.e. used to perform operations on the records stored in database
such as inserting records, updating records, deleting records, creating, modifying and dropping tables, views etc.

54.4M
1K
Difference between JDK, JRE, and JVM

What is PL/SQL?
PL/SQL is a block-structured language. The programs of PL/SQL are logical blocks that can contain any
number of nested sub-blocks. Pl/SQL stands for "Procedural Language extension of SQL" that is used in
Oracle. PL/SQL is integrated with Oracle database (since version 7). The functionalities of PL/SQL usually
extended after each release of Oracle database. Although PL/SQL is closely integrated with SQL language, yet
it adds some programming constraints that are not available in SQL.

PL/SQL Functionalities
PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and
variables, procedures and functions, types and variable of those types and triggers. It can support Array and
handle exceptions (runtime errors). After the implementation of version 8 of Oracle database have included
features associated with object orientation. You can create PL/SQL units like procedures, functions, packages,
types and triggers, etc. which are stored in the database for reuse by applications.
With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process
the data.

The PL/SQL is known for its combination of data manipulating power of SQL with data processing power of
procedural languages. It inherits the robustness, security, and portability of the Oracle Database.

PL/SQL is not case sensitive so you are free to use lower case letters or upper case letters except within string
and character literals. A line of PL/SQL text contains groups of characters known as lexical units. It can be
classified as follows:

o Delimeters
o Identifiers
o Literals
o Comments

PL/SQL Index

PL/SQL Tutorial

o PL/SQL Tutorial
o PL/SQL Variables
o PL/SQL Constant

Control Statements

o PL/SQL IF
o PL/SQL Case
o PL/SQL Loop
o PL/SQL Exit Loop
o PL/SQL While Loop
o PL/SQL For Loop
o PL/SQL Continue
o PL/SQL GOTO

PL/SQL Procedure

o PL/SQL Procedure
 PL/SQL Function

o PL/SQL Function

PL/SQL Cursor

o PL/SQL Cursor

PL/SQL Exception

o PL/SQL Exception

PL/SQL Trigger

o PL/SQL Trigger

Interview Questions

o PL/SQL Interview Questions

o SQL Interview Questions

Quiz

o SQL QUIZ

Prerequisite
Before learning PL/SQL, you must have the basic knowledge of SQL and programming language like C.

Audience
Our PL/SQL tutorial is designed to help beginners and professionals.

Problem
We assure that you will not find any problem in this PL/SQL tutorial. But if there is any mistake, please
post the problem in contact form.

PL/SQL Variables
 A variable is a meaningful name which facilitates a programmer to store data temporarily during the
execution of code. It helps you to manipulate data in PL/SQL programs. It is nothing except a name
given to a storage area. Each variable in the PL/SQL has a specific data type which defines the size and
layout of the variable's memory.

A variable should not exceed 30 characters. Its letter optionally followed by more letters, dollar signs,
numerals, underscore etc.

1. It needs to declare the variable first in the declaration section of a PL/SQL block before using it.
2. By default, variable names are not case sensitive. A reserved PL/SQL keyword cannot be used
as a variable name.

How to declare variable in PL/SQL

You must declare the PL/SQL variable in the declaration section or in a package as a global variable.
After the declaration, PL/SQL allocates memory for the variable's value and the storage location is
identified by the variable name.

Syntax for declaring variable:

54.4M

1K

Difference between JDK, JRE, and JVM

Following is the syntax for declaring variable:

1. variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]  

Here, variable_name is a valid identifier in PL/SQL and datatype must be valid PL/SQL data type. A
data type with size, scale or precision limit is called a constrained declaration. The constrained
declaration needs less memory than unconstrained declaration.

Example:

Radius Number := 5;

Date_of_birth date;

Declaration Restrictions:
In PL/SQL while declaring the variable some restrictions hold.

o Forward references are not allowed i.e. you must declare a constant or variable before referencing it in
another statement even if it is a declarative statement.
val number := Total - 200;
 Total number := 1000;
The first declaration is illegal because the TOTAL variable must be declared before using it in an
assignment expression.
o Variables belonging to the same datatype cannot be declared in the same statement.
N1, N2, N3 Number;
It is an illegal declaration.

Naming rules for PL/SQL variables

The variable in PL/SQL must follow some naming rules like other programming languages.

o The variable_name should not exceed 30 characters.

o Variable name should not be the same as the table table's column of that block.
o The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive so it could be
either lowercase or uppercase. For example: v_data and V_DATA refer to the same variables.
o You should make your variable easy to read and understand, after the first character, it may be any
number, underscore (_) or dollar sign ($).
o NOT NULL is an optional specification on the variable.

Initializing Variables in PL/SQL

Evertime you declare a variable, PL/SQL defines a default value NULL to it. If you want to initialize a
variable with other value than NULL value, you can do so during the declaration, by using any one of
the following methods.

o The DEFAULT keyword

o The assignment operator

1. counter binary_integer := 0;  
2. greetings varchar2(20) DEFAULT 'Hello JavaTpoint';    

You can also specify NOT NULL constraint to avoid NULL value. If you specify the NOT NULL
constraint, you must assign an initial value for that variable.

You must have a good programming skill to initialize variable properly otherwise, sometimes program
would produce unexpected result.

Example of initilizing variable

Let's take a simple example to explain it well:
DECLARE  
   a integer := 30;  
   b integer := 40;  
   c integer;  
   f real;  
BEGIN  
   c := a + b;  
   dbms_output.put_line('Value of c: ' || c);  
   f := 100.0/3.0;  
   dbms_output.put_line('Value of f: ' || f);  
END;  

After the execution, this will produce the following result:

Value of c: 70
Value of f: 33.333333333333333333

PL/SQL procedure successfully completed.

Variable Scope in PL/SQL:

PL/SQL allows nesting of blocks. A program block can contain another inner block. If you declare a
variable within an inner block, it is not accessible to an outer block. There are two types of variable
scope:

o Local Variable: Local variables are the inner block variables which are not accessible to outer blocks.
o Global Variable: Global variables are declared in outermost block.

Example of Local and Global variables

Let's take an example to show the usage of Local and Global variables in its simple form:

1. DECLARE  
2.  -- Global variables   
3.    num1 number := 95;   
4.    num2 number := 85;   
5. BEGIN   
6.    dbms_output.put_line('Outer Variable num1: ' || num1);  
7.    dbms_output.put_line('Outer Variable num2: ' || num2);  
8.    DECLARE   
9.       -- Local variables  
10.       num1 number := 195;   
11.       num2 number := 185;   
12.    BEGIN   
13.       dbms_output.put_line('Inner Variable num1: ' || num1);  
14.       dbms_output.put_line('Inner Variable num2: ' || num2);  
15.    END;   
16. END;  
17. /  

After the execution, this will produce the following result:

Outer Variable num1: 95

Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.

Variable Attributes:
When you declare a PL/SQL variable to hold the column values, it must be of correct data types and
precision, otherwise error will occur on execution. Rather than hard coding the data type and
precision of a variable. PL/SQL provides the facility to declare a variable without having to specify a
particular data type using %TYPE and %ROWTYPE attributes. These two attributes allow us to specify
a variable and have that variable data type be defined by a table/view column or a PL/SQL package
variable.

A % sign servers as the attribute indicator. This method of declaring variables has an advantage as the
user is not concerned with writing and maintaining code.

Following are the types of Variable Attributes in PL/SQL.

o %TYPE:

The %TYPE attribute is used to declare variables according to the already declared variable or
database column. It is used when you are declaring an individual variable, not a record. The data type
and precision of the variable declared using %TYPE attribute is the same as that of the column that is
referred from a given table. This is particularly useful when declaring variables that will hold database
values. When using the %TYPE keyword, the name of the columns and the table to which the variable
will correspond must be known to the user. These are then prefixed with the variable name. If some
previously declared variable is referred then prefix that variable name to the %TYPE attribute.

The syntax for declaring a variable with %TYPE is:

1. <var_name> <tab_name>.<column_name>%TYPE;  

Where <column_name> is the column defined in the <tab_name>.

 Consider a declaration.

SALARY EMP.SAL % TYPE;

This declaration will declare a variable SALARY that has the same data type as column SAL of the EMP
table.

Example:

1. DECLARE    
2. SALARY EMP.SAL % TYPE;  
3. ECODE EMP.empno % TYPE;   
4. BEGIN     
5. Ecode :=&Ecode;  
6. Select SAL into SALARY from EMP where EMPNO = ECODE;  
7. dbms_output.put_line('Salary of ' || ECODE || 'is = || salary');    
8. END;  

After the execution, this will produce the following result:

Enter value for ecode: 7499

Salary of 7499 is = 1600
PL/SQL procedure successfully completed.

o %ROWTYPE:

The %ROWTYPE attribute is used to declare a record type that represents a row in a table. The record
can store an entire row or some specific data selected from the table. A column in a row and
corresponding fields in a record have the same name and data types.

The syntax for declaring a variable with %ROWTYPE is:

1. <var_name> <tab_name>.ROW%TYPE;  

Where <variable_name> is the variable defined in the <tab_name>.

Consider a declaration.

EMPLOYEE EMP. % ROW TYPE;

This declaration will declare a record named EMPLOYEE having fields with the same name and data
types as that of columns in the EMP table. You can access the elements of EMPLOYEE record as

EMPLOYEE.SAL := 10000;

EMPLOYEE.ENAME := ‘KIRAN’;
 Example:

1. DECLARE    
2. EMPLOYEE EMP. % ROW TYPE;  
3. BEGIN     
4. EMPLOYEE.EMPNO := 2092;  
5. 5   EMPLOYEE.ENAME := 'Sanju';  
6. Insert into EMP where (EMPNO, ENAME) Values (employee.empno, employee.ename);  
7. dbms_output.put_line('Row Inserted');    
8. END;  

After the execution, this will produce the following result:

Row Inserted
PL/SQL procedure successfully completed.

Advantages:
o If you don’t know the data type at the time of declaration. The data type assigned to the associated
variables will be determined dynamically at run time.
o If the data type of the variable you are referencing changes the %TYPE or %ROWTYPE variable changes
at run time without having to rewrite variable declarations. For example: if the ENAME column of an
EMP table is changed from a VARCHAR2(10) to VRACHAR2(15) then you don’t need to modify the
PL/SQL code.

PL/SQL Constants
A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It is a
user-defined literal value. It can be declared and used instead of actual values.

Let's take an example to explain it well:

Suppose, you have to write a program which will increase the salary of the employees upto 30%, you
can declare a constant and use it throughout the program. Next time if you want to increase the
salary again you can change the value of constant than the actual value throughout the program.

Syntax to declare a constant:

53.2M
 1.1K
OOPs Concepts in Java

1. constant_name CONSTANT datatype := VALUE;  

o Constant_name:it is the name of constant just like variable name. The constant word is a
reserved word and its value does not change.
o VALUE: it is a value which is assigned to a constant when it is declared. It can not be assigned
later.

Example of PL/SQL constant

Let's take an example to explain it well:

1. DECLARE  
2.    -- constant declaration  
3.    pi constant number := 3.141592654;  
4.    -- other declarations  
5.    radius number(5,2);   
6.    dia number(5,2);   
7.    circumference number(7, 2);  
8.    area number (10, 2);  
9. BEGIN   
10.    -- processing  
11.    radius := 9.5;   
12.    dia := radius * 2;   
13.    circumference := 2.0 * pi * radius;  
14.    area := pi * radius * radius;  
15.    -- output  
16.    dbms_output.put_line('Radius: ' || radius);  
17.    dbms_output.put_line('Diameter: ' || dia);  
18.    dbms_output.put_line('Circumference: ' || circumference);  
19.    dbms_output.put_line('Area: ' || area);  
20. END;  
21. /  

After the execution of the above code at SQL prompt, it will produce the following result:.

1. Radius: 9.5  
2. Diameter: 19  
3. Circumference: 59.69  
4. Area: 283.53  
5.   
6. Pl/SQL procedure successfully completed.  

PL/SQL Literals
Literals are the explicit numeric, character, string or boolean values which are not represented by an
identifier. For example: TRUE, NULL, etc. are all literals of type boolean. PL/SQL literals are case-
sensitive. There are following kinds of literals in PL/SQL:

o Numeric Literals
o Character Literals
o String Literals
o BOOLEAN Literals
o Date and Time Literals

Example of these different types of Literals:

Literals Examples

Numeric 75125, 3568, 33.3333333 etc.

Character 'A' '%' '9' ' ' 'z' '('

String Hello JavaTpoint!

Boolean TRUE, FALSE, NULL etc.

Date and Time '26-11-2002' , '2012-10-29 12:01:01'