PL/SQL Tutorial

Pl/SQL(Procedural Language extension of SQL) is a block structure language.

PL/SQL Tutorial

Learn the concepts of PL/SQL Programming with this easy and complete PL/SQL Tutorial. This tutorial discusses fundamental, functions, procedures, features with examples and simple programs. Beginners, freshers, BE, BTech, MCA, college students will find it useful to develop notes, for exam preparation, solve lab questions, assignments and viva questions.

Who is this PL/SQL Tutorial designed for?

This tutorial is specially designed for beginners who want to learn and improve their PL/SQL Programming skills

What do I need to know to begin with?

To learn PL/SQL, one of the important requirements is to have a basic knowledge of databases and a programming language. Ability to execute these will be beneficial.

PL/SQL Databases syllabus covered in this tutorial

This tutorial covers:
PL/SQL Variables, Constants, Control statements, Procedure, Functions, Cursor, Exception, Triggers.

By the end of this tutorial, you will be quite confident about your skills on working with PL/SQL. So, let's begin!

Introduction

  • Pl/SQL stands for "Procedural Language extension of SQL" used in Oracle. PL/SQL is integrated with Oracle database (since version 7).
  • It is a block structure language.
  • The programs of PL/SQL are logical blocks that can contain any number of nested sub-blocks.
  • The functionalities of PL/SQL are usually extended after each release of Oracle database.
  • Although PL/SQL is closely integrated with SQL language, it adds some programming constraints that are not available in SQL.
  • PL/SQL includes procedural language elements like conditions and loops.
  • It allows declaration of the constants and variables, procedures and functions, triggers etc.

Advantages of PL/SQL

  • PL/SQL is development tool that supports SQL data manipulation and conditional checking, branching and looping.
  • It provides facility to deal with errors, as required and displays user-friendly messages when error occurs.
  • Allows declaration and use of variables in blocks of code. These variables are used to store intermediate results of a query for later processing either in SQL or PL/SQL.
  • Using PL/SQL, all sorts of calculations can be done quickly and efficiently without using oracle engine which improves transaction performance.
  • Applications written in PL/SQL are portable to any computer hardware and operating system, where Oracle is operational.
    For example: PL/SQL code blocks written for a DOS version of Oracle will run on its Linux/ Unix version without any modifications.

Disadvantages of SQL

  • SQL does not provide the programming techniques of condition checking, looping and branching which is very important for data testing before its permanent storage. Pl/SQL does this.
  • SQL statements are passed to the Oracle Engine one at a time. While executing SQL statement, each time a call is made to the engine's resources. This increase traffic in the network that decreases the speed of data processing especially in a multi-user environment.
  • SQL has no facility of error handling.
▲