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 EXCEPTION are not followed by semicolons.
- END keyword and all other PL/SQL statements require a semicolon to terminate the statements.
- Before you start creating code, define the goals and capabilities of your PL/SQL software.
- Select names for constants, variables, and other identifiers that are both meaningful and descriptive.
- To keep track of the modifications in your PL/SQL code, use version control tools (like Git).
- Keep the use of universal variables to a minimum.
- Observe the least privilege principle.
- Use the COMMIT and ROLLBACK instructions to provide proper transaction management.
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.
- It offers logging and debugging capabilities, including the capacity to use exception messages.
- SQL can be executed dynamically.
- When certain data events, such as INSERT, UPDATE, or DELETE actions on a table, occur, triggers are specialized forms of stored processes that are automatically invoked.
- Multi-row queries are handled using cursors.
- The declaration of variables and constants to store data values is supported.
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 Architecture
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.