Chapter 1:PL/SQL Tutorial

PL/SQL Introduction

In this PL/SQL tutorial, you will learn PL/SQL data types, conditions, looping, type of cursor, how to handling PL/SQL exceptions, how to define PL/SQL functions, procedures, packages, and PL/SQL Trigger. Let’s start the PL/SQL tutorial.

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

What is PL/SQL

PL/SQL stands for a procedural language extension of the structured query language (SQL).PL/SQL (Procedural language/structured query language) is a program to executes in Oracle database.

PL/SQL is a block structured language. The basic structure block of PL/SQL built using DECLARE, BEGIN, EXCEPTION (optional), END keywords. These keyword blocks defined as a declaration block, executable block, exception handling block, and last end block (for end of the structure).

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.

With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data.

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.

PL/SQL history

Oracle introduced PL/SQL (version 1.0) in its Oracle Database product version 6.0 as the scripting language in SQL*Plus and programming language in SQL*Forms 3.

Since version 7, Oracle did a major upgrade for PL/SQL (version 2.0) that provides more features such as procedures, functions, packages, records, collections, and some package extensions.

Since then many features have been added to PL/SQL such as XML support, preprocessor, file I/O, object-orientation, new statements such as CONTINUE and GOTO to make it one of the most highly structured programming languages.

PL/SQL Advantages

  • Reduces network traffic :This one is great advantages of PL/SQL. Because PL/SQL nature is entire block of SQL statements execute into oracle engine all at once so it’s main benefit is reducing the network traffic.
  • Procedural language support: PL/SQL is a development tools not only for data manipulation futures but also provide the conditional checking, looping or branching operations same as like other programming language.
  • Error handling :PL/SQL is dealing with error handling, It’s permits the smart way handling the errors and giving user friendly error messages, when the errors are encountered.
  • Declare variable: PL/SQL gives you control to declare variables and access them within the block. The declared variables can be used at the time of query processing.
  • Portable application :Applications are written in PL/SQL are portable in any Operating system. PL/SQL applications are independence program to run any computer.