PL/SQL — Comprehensive Introduction
What is PL/SQL?
PL/SQL stands for “Procedural Language extensions to the Structured Query Language” .SQL (Structured Query Language) is a language that we are using to manipulate databases. SQL is mainly used to Query and update data in relational database management systems (RDBMS). PL/SQL adds more functionalities to the traditional SQL. PL/SQL is used and only executable in an oracle database and it’s highly structured and readable language.
We cannot develop a PL/SQL program that runs on a system that does not have an Oracle Database because it’s embedded language to Oracle Databases.
PL/SQL architecture
PL/SQL engine is used to compile the PL/SQL code into byte-code and execute the executable code.
Once you submit a PL/SQL block to the Oracle DB server, the PL/SQL engine collaborates with the SQL engine to compile and execute the code.
PL/SQL engine — Runs the procedural elements
SQL engine — Processes the SQL statements
SQL*Plus
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has three interfaces
1. Command-line User interface
2. A windows Graphical User Interface
3. iSQL*Plus web-based User interface
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database.
Main functions of SQL*Plus
· Format, perform calculations on, store, and print from query results
· Examine table and object definitions
· Develop and run batch scripts
· Perform database administration
PL/SQL Block
This is a block-structured language and code is organized into blocks. PL/SQL Block has three main parts,
1.Declaration (Optional)
2. Executable (Mandatory)
3. Exception-handling (Optional)
Named Blocks in PL/SQL
Cursors
A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors,
1. Implicit cursors — Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE and DELETE, it automatically creates an implicit cursor. Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses.
2. Explicit cursors — A explicit cursor is a SELECT statement declared explicitly in the declaration section of the current block or package specification. For an explicit cursor, we have control over its execution cycle from OPEN, FETCH and CLOSE.
Procedures
A PL/SQL procedure is a reusable unit that encapsulates the specific business logic of the application. A PL/SQL procedure is a named block stored as a schema object in the Oracle Database
CREATE [OR REPLACE] PROCEDURE procedure_name
(parameter_list) IS
[declaration statements]
Begin
[execution statements]
Exception
[exception handler]
End;
/
Functions
Similar to a procedure, a PL/SQL function is a reusable program unit stored as a schema object in the Oracle Database.
Trigger
A trigger is a named PL/SQL block stored in the Oracle Database and executed automatically when a triggering event takes place.
Package
A package is a schema object that contains definitions for a group of related functionalities. A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms.
