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.
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 then many features have been added to PL/SQL such as XML support, preprocessor, file I/O, object-orientation, new statements such as
GOTO, etc. to make it one of the most highly structured programming languages.
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:
- Block structure, nested block structure
- Conditional and sequential statements: IF, CASE, GOTO, CONTINUE and NULL
- Loop statements: WHILE loop,
- Exception and error handling
- Data types: string, numbers, date & timestamp, Boolean and LOB
- 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.