INTRODUCTION:
PL/SQL stands for Procedural Language/Structured Query Language. PL/SQL offers a set of procedural commands (IF statements, loops), organized within blocks, that complement and extend the reach of SQL. PL/SQL is Oracle’s extension of SQL designed for developers working with the Oracle Database.
BULIDING BLOCKS OF PL/SQL PROGRAMS:
PL/SQL is a block-structured language. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.
1.Declarative: Statements that declare variables, constants which can then be used within that block
2.Executable: Statements that are run when the block is executed
3.Exception handling: A specially structured section you can use to catch, any exceptions that are raised when the executable section runs.
NESTING BLOCKS:
Nesting blocks within blocks as well as the use of the concatenation operator (||) to join together multiple strings.
SUBPROGRAMS(Procedure and Functions):
PL/SQL supports the definition of named blocks of code, also known as subprograms. Subprograms can be procedures or functions. Generally, a procedure is used to perform an action and a function is used to calculate and return a value. A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs.
Procedures:
Procedure do not return a value, mainly used to perform an action.
SYNTAX:
Example:
Result:
Functions:
The subprograms return a single value, mainly used to compute and return a value.
SYNTAX:
Example:
Result:
Difference of Function from Procedure:
- The subprogram name now describes the data being returned, not the action being taken.
- The body or implementation of the subprogram now contains a RETURN clause that constructs the message and passes it back to the calling block.
- The RETURN clause after the parameter list sets the type of data returned by the function.
CURSORS:
A Cursors is a temporary work area created in the system memory when a SQL statement is executed. It is a temporary memory which is used to fetch more than one record at a time from existing table.
Using %ROWTYPE with cursor (PL/SQL):
The % ROWTYPE attribute is used to define a record with fields corresponding to all of the columns that are fetched from a cursor or cursor variable. The %ROWTYPE attribute is prefixed by a cursor name or a cursor variable name.
TRIGGERS (PL/SQL):
A PL/SQL trigger is a set of actions that are to be performed in response to an insert, update, or delete operation against a table.
Triggers are written to be executed in response to any of the following events.
- A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
- A database definition (DDL) statement (CREATE, ALTER, or DROP).
PACKAGES (PL/SQL):
A package is a way of logically storing the subprogram like procedure, functions, exception or cursor into a single common unit.
Package has two basic components:
- Specification: It is the declaration section of a Package.
- Body: It is the definition section of a Package.
SYNTAX:
PACKAGE SPECIFICATION
PACKAGE BODY
REFERRING THE PACKAGE OBJECT:
Creating a package only defines it, to use it we must refer it using the package object.
SYNTAX:
CONCEPTS OF ERROR HANDLING:
An error condition during a program execution is called an exception in PL/SQL. PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message.
SYNTAX:
Raising Exceptions:
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Below syntax in raising Oracle standard exception or any user-defined exception.
SYNTAX:
User-defined Exceptions:
PL/SQL allows you to define your own exceptions according to the need of your program. A user defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
SYNTAX:
Unnamed system exceptions:
Oracle doesn’t provide name for some system exceptions called unnamed system exceptions. These exceptions doesn’t occur frequently. These exceptions have two parts code and an associated message.
The way to handle those exceptions is to assign them using Pragma Exception_init.
SYNTAX:
