What are the advantages of PL/SQL?

PL/SQL is a transaction processing procedural language that has the following advantages:
a. Integration with database - PL/SQL supports SQL statements, thus enabling integration between procedural language capabilities and the database technology.
b. Better performance - SQL statements are grouped within a PL/SQL block and sent together to database for parsing;
therefore, it reduces the network traffic and increases the performance.
c. Higher productivity - PL/SQL helps the package reusable code within the well-defined modular blocks; and therefore, increases the productivity.
d. Portability - Programs packages and libraries written in PL/SQL can be reused in different environments.

Read more »

What is the difference between SQL and PL/SQL?

Structured Query Language (SQL) is a non-procedural language that interacts with the database, and is used for database manipulation using the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Control statements cannot be used in SQL, which is compiled and executed statement by statement at the runtime (late-binding).

PL/SQL is a programming language that allows the usage of Cursor Control statements and Transaction Control statements, such as if...then...else. It in egrates with SQL functions and statements to interact and manipulate with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the machine-readable code, which is executed at runtime (early binding); and therefore, improves the performance.

Read more »

What are the different datatypes that can be defined in a PL/SQL block?

The following categories of datatypes are supported by PL/SQL:
1. Scalar —Holds a single value and does not contain any internal component.
Some of its datatypes are given as follows:
CHAR
VARCHAR2
NUMBER
LONG
INT
INTEGER
BINARYINTEGER
DATE
BOOLEAN
SMALLINT
POSITIVE
POSITIVEN
2. Composite —Contains an internal component, which can be manipulated individually.
It is also known as collections and includes the following datatypes:
TABLE
RECORD
NESTED TABLE
VARRAY
3. Reference —Holds values called pointers, which designate to other objects.
It includes the following datatypes:
REF CURSOR
REF object_type
4. LOB —Holds values, which specify the location of large objects, such as images and video clips. It can store up to 4GB of unstructured data.
It includes the following datatypes:
BFILE
BLOB
CLOB
NCLOB

Read more »

What is the basic structure of a Procedural Language/Structured Query Language (PL/SQL) block?

The basic structure of a PL/SQL block contains the following three sections:
a. Declaration —Defines and initializes the variables and cursors of the block
b. Execution —Manipulates the variables and cursors defined in the declaration section using the control flow statements
c. Exception  —Handles the exceptions raised during the program
A typical PL/SQL block structure is given as follows:
Declare
<variable>
<CONSTANTS>
<CURSORS>
begin
<control statements>
exception
<exception handling statements>
end;

Read more »

PL SQL Exceptions | PL/SQL Tutorial pdf

PL/SQL Exceptions

While the PL/SQL parser can analyze the syntax of your PL/SQL program when you compile it, there is no guarantee that the database with which it interacts will even be up and running later when the PL/SQL program executes or that the database objects that are names within your PL/SQL program still exist, let alone reflect the structure that your program requires.

In order to handle these unpredictable situations, PL/SQL provides a mechanism known as exceptions. When an exception occurs in the course of your PL/SQL program unit's execution, the exception is said to be raised. After the exception is raised, execution within the particular block terminates. Any open cursors are automatically closed, and any loops are exited. Execution control leaves the processing section of the block.
Furthermore, when execution exits the block due to a raised exception, control will pass into the exception-handling section of the block, if such a section has been included with the block.
The exception-handling section is an optional section in which you declare exception handlers. An exception-handler is a section of the PL/SQL code that is included with the PL/SQL block, and that will only execute if and when the associated exception is raised within the processing section.

Types of PL/SQL Exceptions:
There are two general categories of exceptions:
System-defined exceptions
User-defined exceptions [Via: Oracle Ocp Dvlpr Pl/Sql Prgrm Units W/Cd By O'Hearn]

While writing a PL/SQL program:
- Always add exception handler in a PL/SQL program.
- Always try to use the named exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS rather than using WHEN OTHERS in exception handler.

An Example of PL/SQL Exception
DECLARE
v_student_id Number := &sv_student_id;
v_enrolled VARCHAR2(3) := 'NO;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Check if the student is enrolled');
SELECT 'YES'
INTO v_enrolled
FROM enrollment
Where student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE ('The student is enrolled into one course');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The student is not enrolled');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('The student is enrolled in too many courses');
END;

The above example contains two exceptions in a single exception-handling section. The first exception, NO_DATA_FOUND, is raised if there are no records in the ENROLLMENT table for a particular student. The second exception TOO_MANY_ROWS is raised if a particular student is enrolled in more than one course.
Built-in exceptions are raised implicitly. Therefore, you only need to specify what action must be taken in the case of a particular exception.
[Via: Oracle Pl/Sql By Example, 4/E By Rosenzweig]

Few useful links on PL/SQL Exception
1. A useful link on Handling PL/SQL Errors
It mainly covers:
Overview of PL/SQL Runtime Error Handling
Advantages of PL/SQL Exceptions
Summary of Predefined PL/SQL Exceptions
Defining Your Own PL/SQL Exceptions
How PL/SQL Exceptions Are Raised
How PL/SQL Exceptions Propagate
Reraising a PL/SQL Exception
Handling Raised PL/SQL Exceptions
Tips for Handling PL/SQL Errors
Overview of PL/SQL Compile-Time Warnings

2. Recommended book for understanding PL/SQL exceptions in-depth is Oracle Pl/Sql By Example, 4/E By Rosenzweig

Read more »

PL/SQL Cursors | PL/SQL Tutorial pdf

PL/SQL Cursors
We can manipulate the information within a SQL statement by means of assigning a name to a "select statement", this concept is known a cursor. A cursor is used for processing individual rows returned as a result for a query.

Oracle PL/SQL  Implicit Cursors Oracle PL/SQL Implicit Cursors
Oracle PL/SQL Explicit Cursors Oracle PL/SQL Explicit Cursors
Oracle PL/SQL Cursor For Loops Oracle PL/SQL Cursor For Loops
Oracle PL/SQL Cursors With Parameters Oracle PL/SQL Cursors With Parameters
Oracle PL/SQL REF Cursors Oracle PL/SQL REF Cursors
Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE
Examples of Oracle PL/SQL Cursors Examples of Oracle PL/SQL Cursors

Examples of Oracle PL/SQL Cursors:
1) Retrieving the records from the emp table and displaying them one by one using cursors:
CREATE OR REPLACE PROCEDURE my_proc IS
var_empno emp.empno%type;
var_ename emp.ename%type;
var_sal emp.sal%type;

//declaring a cursor//
CURSOR EMP_CURSOR IS
select empno, ename, sal from emp;
BEGIN

//opening a cursor//
open EMP_CURSOR;
LOOP

//fetching records from a cursor//
fetch EMP_CURSOR into var_empno, var_ename, var_sal;

//testing exit conditions//
EXIT when EMP_CURSOR%NOTFOUND;
IF (var_sal > 1000) then
DBMS_OUTPUT.put_line(var_empno || ' ' || var_ename || ' ' || var_sal);
ELSE
DBMS_OUTPUT.put_line(var_ename || ' sal is less then 1000');
END IF;
END LOOP;

//closing the cursor//
close EMP_CURSOR;
DBMS_OUTPUT.put_line('DONE');

END;

Read more »

PL/SQL Triggers | PL/SQL Tutorial pdf

PL/SQL Triggers

A PL/SQL trigger is a construct in PL/SQL that runs or "triggered" on event of changes being made to a table in the database. The triggering event is a INSERT, UPDATE or DELETE done on a table. The trigger can be made so it can be "fired" either BEFORE or AFTER the Data Manipulation Language is executed.
>A database trigger is a block of code that is automatically executed in response to certain events.
>Triggers are executed implicitly whenever the triggering event happens.
>The triggering event is an INSERT, DELETE, or UPDATE command.
>The timing can be either BEFORE or AFTER, INSTEAD OF trigger.

The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.
You can write triggers that fire whenever one of the following operations occurs:
DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

A trigger has three basic parts:
A triggering event or statement
A trigger restriction
A trigger action

Example of PL/SQL trigger
In the below example line 2 is A triggering event or statement, lines 4-9 are A trigger action.
Example of creating a trigger based on the following two tables:
CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);

We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.
1 CREATE TRIGGER tr1
2 AFTER INSERT ON T1
3 REFERENCING NEW AS newRow
4 FOR EACH ROW
5 WHEN (newRow.a <= 5)
6 BEGIN
7 INSERT INTO T2
VALUES(:newRow.a);
8 END tr1;
9 .
10 run;

Different types of triggers can be:
Row Triggers and Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects. A row trigger fires once for each row affected by the triggering event.
BEFORE and AFTER Triggers: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.
INSTEAD OF Triggers: INSTEAD OF triggers describe how to perform insert, update, and delete operations against views that are too complex to support these operations natively. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert, delete, update and select).

Triggers on System Events and User Events: You can use triggers to publish information about database events to subscribers. System events are for example Database startup and shutdown, Data Guard role transitions etc and User Events are User logon and logoff, DDL statements (CREATE, ALTER, and DROP) etc.

Displaying Trigger Errors:
If we get a message Warning: Trigger created with compilation errors. you can check the error messages with:
Show errors trigger <trigger_name>;
You can also type, SHO ERR (SHOW ERRORS) to see the most recent compilation error.

Viewing Defined Triggers
To view all the defined triggers, use:
select name_of_trigger from user_triggers;
For more details on a particular trigger:
select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '<name_of_trigger>';

Disabling Triggers:
To disable or enable a trigger:
alter trigger <name_of_trigger> {disable | enable};

Read more »