Summary: in this tutorial, we will introduce you to PL/SQL language and 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 the other. To overcome this limitation, you often have to use other programming languages such as C/C++, Perl, PHP, and Java, 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 and GOTO 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 & time stamp, 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 the Oracle database in compiled form. This allows applications or users to share the same functionality stored in the Oracle database.
PL/SQL also allows you to define triggers that can be invoked automatically in 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.
