Prerequisite : PL/SQL Introduction
What do you mean by PL/SQL?
In Oracle, PL/SQL (Procedural Language/SQL) is the procedural language extension to the non-procedural SQL. It combines the data manipulation power of SQL and the procedural power of standard programming languages. PL/SQL was developed by Oracle Corporation within the early ’90s to reinforce the capabilities of SQL. It integrates well with SQL* PLUS and other application development products of Oracle. PL/SQL is the superset of SQL. It provides SQL data manipulation commands and SQL data types. In PL/SQL, a block without any name is called Anonymous Block. PL/ SQL block consists of various functions, library, procedures, trigger, packages etc.
The following points should be remembered while writing a PL/SQL program –
- In PL/SQL the semicolon (;) is placed at the end of an SQL statement or PL/SQL control statement.
- Section keyword DECLARE, BEGIN and EXECUTION are not followed by semicolons.
- END keyword and all other PL/SQL statements require a semicolon to terminate the statements.
Features of PL/SQL :
The various features of PL/SQL are given below –
- PL/SQL runs on various operating systems such as windows, Linux etc.
- PL/SQL have an error-checking facility and displays user-friendly messages when an error occurs in a program.
What do you mean by PL/SQL Architecture?
The PL/SQL runtime system is a technology and not an independent product. This technology is actually like an engine that exhibits PL/SQL blocks, subprograms like functions and procedures. This engine can be installed in an Oracle Server or in application development tools such as Oracle Form Builder, Oracle Reports Builder etc.
PL/SQL can reside in two environments –
- The Oracle Server
- The Oracle tools
These two environments are independent of each other. In either environment, the PL/SQL engine accepts any valid PL/SQL block as input. The PL/SQL engine executes the procedural part of the statements and sends the SQL statement executer in the Oracle Server. A single transfer is required to send the block from the application to the Oracle Server, thus improving performance, especially in a Client-Server network. PL/SQL code can also be stored in the Oracle server as subprograms that can be referenced by any number of applications connected to the database.
Advantages of PL/SQL :
- PL/SQL provides better performance.
- PL/SQL has high Productivity.
- It supports Object-Oriented Programming concepts.
- It has Scalability and Manageability.
- PL/SQL supports various Web Application Development tools.
Disadvantages of PL/SQL :
- PL/SQL requires high memory.
- Lacks of functionality debugging in stored procedures.