What is PL/SQL?

by E-Computer Concepts October 22, 2019 at 1:29 pm

PL/SQL is a sophisticated programing language use to access in Oracle database from various environments. It is integrated with the database server so that the PL/SQL code can be processed quickly and efficiently.

PL/SQL is a procedural language that Oracle developed as an extension to standard SQL in order to provide a way to execute procedural logic on the database. It allows its users to develop complex database applications that require the usage of control structures and procedural elements such as procedures, functions, modules etc.

History of PL/SQL

PL/SQL is contained within Oracle server. The first version of PL/SQL, 1.0 was released with Oracle version 6 in 1991. Oracle 7 contained PL/SQL 2. X. With Oracle 8, the PL/SQL version number was increased to 8 as well. Oracle8i consequently contains PL/SQL version 8.1, Oracle 9i contains PL/SQL version 9.0. Each sulisequent release of the database contains an associated version of PL/SQL.

PL/SQL Block Structure

PL/SQL allows the creation of structured logical blocks of code that describe processes, which have to be applied to data. A single PL/SQL code block consists of a set SQL statements, clubbed together and then passed to the Oracle engine entirely.

A PL/SQL block has a define structure, which can be divided into sections. The sections of PL/SQL block are

  • Declare Section
  • The master Begin and End section that also contains an Exception Section. This exception section is optional

The Declare Section consists of memory variables and other Oracle objects that can be declared and initialized. Once declared, they can be used in SQL statements for data manipulation.

The Begin Section consists a set of SQL and PL/SQL statements which describe processes that have to be applied to table data. Data manipulation, loping and branching constructs are specified in this section.

The Exception Section deals with handling of errors that arise during execution of data manipulation statements defined in PL/SQL code block. Errors can arise due to syntax, logic or/and validation rules violation.

The End Section marks the end of PL/SQL block

PL/SQL and SQL *PLUS

SQL *PLUS is an interactive program that allow you to type in and execute SQL statements. It also enables you to type in and execute PL/SQL code and is one of the most common front end and used to develop and create stored PL/SQL procedures and functions.

PL/ SQL Character Set

Any PL/SQL program is made up of lexical units. A lexical unit is a sequence of characters, in the character set allowed for the PL/SQL language. This character set includes:

  • Uppercase and Lowercase letters A-Z, a-z.
  • Digits 0-9.
  • White space : tabs, spaces and carriage return.
  • Mathematical Symbols : +, -, *, 1 ,<>, =.
  • Punctuation Symbols : ( ), { }, [ ], ?, !, ;, :, ‘ ,@, #, %,$,&,- ,!.

Like SQL, PL/SQL is not case sensitive. Thus upper and lower case letters are equivalent except inside Quoted strings.

Add Comment