Flat 20% & up to 50% off + Free additional Courses. Hurry up!

Overview of PL/SQL


PL/SQL stands for Procedural Language/Structured Query Language. It is a combination of sql with procedural features of programming language. It stored and compiled in the database, runs within the Oracle executable and inherits the security, robustness and portability of the Oracle Database.


1.1 Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

■ Tight Integration with SQL

■ Better Performance

■ Higher Productivity

■ Full Portability

■ Tight Security

■ Access to Pre-defined Packages

■ Support for Object-Oriented Programming

■ Support for Developing Web Applications and Pages


boosts performance

1.2 The Main Features of PL/SQL

 PL/SQL combines the data-manipulating power of SQL with the processing power of procedural languages. You can control program flow with statements like IF and LOOP. As with other procedural programming languages, you can declare variables, define procedures and functions, and trap runtime errors.


  • PL/SQL Block Structure

The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can be nested inside one another. A block groups related declarations and statements. You can place declarations close to where they are used, such as inside a large subprogram. The declarations are local to the block and cease to exist when the block completes, helping to avoid cluttered namespaces for variables and procedures.

block structure

  • PL/SQL Variables and Constants

PL/SQL lets you declare variables and constants, then use them in SQL and procedural statements anywhere an expression can be used. You must declare a constant or variable before referencing it in any other statements.


  • Declaring Variables



part_no NUMBER(6);

part_name VARCHAR2(20);


  • Assigning Values to a Variable

You can assign values to a variable in three ways:-

  1. The first way uses the assignment operator (:=), a colon followed by an equal sign.
  2. The second way to assign values to a variable is by selecting (or fetching) database values into it.
  3. The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.


  • Bind Variables

When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically.


  • Declaring Constants

Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. No further assignments to the constant are allowed.

The following example declares a constant:

credit_limit CONSTANT NUMBER := 5000.00;


  • Processing Queries with PL/SQL

 Example:- Processing Query Results in a LOOP


FOR someone IN (SELECT * FROM employees WHERE employee_id < 120 )


DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||

', Last name = ' || someone.last_name);





1.2.5 Declaring PL/SQL Subprograms

Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has two types of subprograms: procedures and functions. The following is an example of a declaration of a PL/SQL procedure:


in_string VARCHAR2(100) := 'This is my test string.';

out_string VARCHAR2(200);

PROCEDURE double ( original IN VARCHAR2, new_string OUT VARCHAR2 ) AS


new_string := original || original;



1.2.6 Writing Reusable PL/SQL Code

PL/SQL lets you break an application down into manageable, well-defined modules. PL/SQL meets this need with program units, which include blocks, subprograms, and packages. You can reuse program units by loading them into the database as triggers, stored procedures, and stored functions.


1.2.7 Inputting and Outputting Data with PL/SQL

Most PL/SQL input and output is through SQL statements, to store data in database tables or query those tables. All other PL/SQL I/O is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as

SQL*Plus, to read and display the data passed to DBMS_OUTPUT.

SQL*Plus does not display DBMS_OUTPUT data unless you first issue the SQL*Plus command SET SERVEROUTPUT ON as follows:



 1.2.8 PL/SQL Data Abstraction

 Data abstraction lets you work with the essential properties of data without being too involved with details. After you design a data structure, you can focus on designing algorithms that manipulate the data structure.

  • Cursors

A cursor is a name for a specific private SQL area in which information for processing the specific statement is kept. PL/SQL uses both implicit and explicit cursors.


  • Collections

PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection. When declaring collections, you use a TYPE definition.


  • Records

Records are composite data structures whose fields can have different datatypes. You can use records to hold related items and pass them to subprograms with a single parameter. When declaring records, you use a TYPE definition.


  • Object Types

An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. Object types reduce complexity by breaking down a large system into logical entities.


1.2.9 PL/SQL Error Handling

 PL/SQL makes it easy to detect and process error conditions known as exceptions. When an error occurs, an exception is raised: normal execution stops and control transfers to special exception-handling code, which comes at the end of any PL/SQL block. Each different exception is processed by a particular exception handler.


1.3 PL/SQL Architecture

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms.


plsql engine

"0 Responses on Overview of PL/SQL"

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.


Sales Offer

  • To avail this offer, enroll before 24th October 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
DW offer

Sign Up or Login to view the Free Overview of PL/SQL.