What is PL/SQL

Summary:

 
  In this tutorial, we introduce you to PL/SQL language. We will discuss PL/SQL history, the language elements and the advantages that PL/SQL brings to the Oracle development.
 
 PL/SQL is a Procedural Language (PL) that extends the Structured Query Language (SQL). If you have been programming Pascal or Ada, you will find much familiar syntax in PL/SQL.
 
 It is difficult to write applications using SQL only because each SQL statement runs independently and has little or no effect on each other. To overcome this limitation, you often have to use other programming languages such as C/C++, Perl, PHP, Java, etc., by using standard database interfaces. Oracle supports this approach when you want to develop applications that interact with Oracle databases.
 
 In addition, Oracle also encourages you to use PL/SQL to work with Oracle databases because PL/SQL brings many advantages.
 

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 GOTO, etc. to make it one of the most highly structured programming languages.
 

PL/SQL Advantages

PL/SQL is a highly structured language

 PL/SQL provides a very expressive syntax that makes it easy for anyone who wants to learn PL/SQL. If you are programming in other languages, you can get familiar with PL/SQL very quickly and understand the intent of the code without difficulty.
 
PL/SQL language features include the following elements:

• Variables
• Block structure, nested block structure
• Conditional and sequential statements: IF, CASE, GOTO, CONTINUE and NULL
• Loop statements: WHILE loop, FOR loop
• Exception and error handling
• Data types: string, numbers, date & timestamp, Boolean and LOB
• Record
• Collection
• Cursor
• Procedures, functions, packages
• Object-orientation features
• Dynamic SQL and dynamic PL/SQL

PL/SQL is a portable and standard language for Oracle development

 

Once you develop a PL/SQL program in an Oracle Database, you can move it to the other Oracle Databases without changes, with the assumption that the versions of Oracle database are compatible.
 

PL/SQL is an embedded language

 
PL/SQL programs such as functions and procedures are stored in Oracle database in compiled form. This allows applications or users to share the same functionality stored in Oracle database.
 
PL/SQL also allows you to define triggers that can be invoked automatically to response to particular events in associated tables.
 

PL/SQL is a high-performance language inside Oracle Databases

 
Oracle adds many enhancements to the PL/SQL to make it more efficient to interact with Oracle databases.