Explore topic-wise InterviewSolutions in .

At each iteration in a BASIC loop in PL/SQL, sequence of statements is executed. After the execution, the control resumes at the top of the loop. The EXIT STATEMENT is a must in the execution part to exit from the loop. The keyword LOOP begins the execution. It ENDS with the keyword END LOOP.

The following is an example:

The output:

To display output, PL/SQL has an in-built package known as DBMS_OUTPUT. With this package, we can also display debugging information.

The following is an example:

The output:

Let us see the subprograms of DBMS_OUTPUT:

• DBMS_OUTPUT.DISABLE; 

Disables message output.

• DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);

Enables message output. A NULL value of buffer_size represents unlimited buffer size.

• DBMS_OUTPUT.GET_LINE (LINE OUT VARCHAR2, status OUT INTEGER);

Retrieves a SINGLE line of buffered information.

• DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER);

Retrieves an array of lines from the buffer.

• DBMS_OUTPUT.NEW_LINE;

Puts an end-of-line marker.

• DBMS_OUTPUT.PUT(item IN VARCHAR2);

Places a PARTIAL line in the buffer.

• DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);

Places a line in the buffer.

• %FOUND

The %FOUND cursor attribute returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

• %NOTFOUND

The %NOTFOUND cursor attribute returns TRUE if record was not fetched successfully, FALSE otherwise. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

Cursor attributes are used like this in PL/SQL:

Above, set any attribute name under “attribute_name”. Here we are LEARNING about %FOUND and %NOTFOUND attribute names.

Let us consider the following table for our example:

Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

The output:

Since we updated the budget for all the departments above, therefore our table rows would look like this:

The schema objects CREATED with PL/SQL is Packages, Triggers, Cursors, etc.

• Packages
  

Packages are the schema objects that groups LOGICALLY related PL/SQL types and variables.

• Triggers
  

Triggers are used to enforce referential integrity, auditing, or EVEN applying security authorizations. It prevents invalid transactions and gather statistics on table access.

• Cursors

Cursors has information about processing the statement. It holds the rows (one or more) RETURNED by a SQL statement.

The CASE STATEMENT in PL/SQL selects one sequence of statements to execute. The statement USES a selector instead multiple Boolean expression.

The FOLLOWING is the syntax:

The following is an example:

The output:

Yes, we can label PL/SQL loops. Here are some key points about LABELS in a loop:

• The label is enclosed by << and >> and appears at the BEGINNING of the LOOP statement.
• The label name can also be included at the END of the LOOP statement.

Let us see an example:

The output:

Yes, this is correct. Here, the WHEN CLAUSE is used for conditions, which are from top to bottom. The SEQUENCE of statements with the WHEN clause whose condition evaluates to TRUE is executed. Only the FIRST statement executes, if more than ONE condition evaluates to TRUE.

The following is the syntax:

LET us see an example:

The output:

The TIMESTAMP WITH TIME ZONE datatype is USED for DATE information across geographic regions.

It has a time zone region name or a time zone offset in its VALUE. TIME ZONE offset = difference in hours and minutes between LOCAL time and UTC.

One of the Collection types in PL/SQL is Index-By table. These are also called Associative Arrays. It gets created only in the PL/SQL block. The subscript TYPE for Associative Array is a string or integer.

Associative Array is a set of key-value pair. The key here can be an Integer or String. The following is the syntax:

Here, index-by table is the name_of_table. The keys of the subscript_type. The associated VALUES will be of the element_type i.e. NUMBER, etc.

The following is an example:

The OUTPUT:

The dot(.) operator is to be used in PL/SQL to access the fields of a record. This is between the record variable and the field.

Let US take an example of a record. This is how you can DECLARE:

You can now access the fields using the dot(.) operator.

The following is the example WHEREIN we have created user-define records and accesses the fields:

The output:

The COLLECTION_IS_NULL exception is raised when a PROGRAM attempts to apply COLLECTION METHODS other than EXISTS to an uninitialized nested table or VARRAY, or the program attempts to assign values to the elements of an uninitialized nested table or varray.

The COMMIT STATEMENT is used to commit a transaction in PL/SQL, use the:

The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.

Let’s SAY the FOLLOWING is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:

The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs.
Savepoints SPLITS a long transaction into smaller units. This is simply done using checkpoints. These checkpoints allow you to ROLL back to a checkpoint in a long transaction using the following COMMAND:

Let us see an example of SAVEPOINT:

Above, we have created a SAVEPOINT s1, which will allow us to rollback.

The ROLLBACK undone all the changes since the last COMMIT or ROLLBACK.
To commit a transaction in PL/SQL, use the COMMIT command:

Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:

To rollback the transactions, use the ROOLBACK command. The changes you MADE to the database without COMMIT can be undone with ROLLBACK.

The syntax:

Here, name_of_savepoint is the name of the savepoint.

If savepoint is not USED, then simply use the “ROLLBACK” statement to rollback all the changes.

No, this is not a correct way to IMPLEMENT assignment in PL/SQL. In PL/SQL, the assignment STATEMENT is the following:

Let us SEE in the example:

The following is another example:

The output:

Nested tables are one of the collection types in PL/SQL. They are created EITHER in PL/SQL block or at schema level. These are LIKE a 1D array, but its size can GET increased dynamically.

The following is the syntax:

The following is an example:

The output:

Variable Scope defines that if you DECLARE a variable within an inner bloc, it is not accessible for the outer. On the contrary, if a variable is declared in outer BLOCK, then it is accessible in the ENTIRE program whether its outer, inner, NESTED inner blocks.

The following are the scope of variables in PL/SQL:

Local variables

• Declaration: Variables declared in an inner block
• Accessible: Not accessible to outer blocks

Global variables

• Declaration: Variables declared in the outermost block
• Accessible: Accessible to outer blocks, inner block, nested inner block.

Let us now see an example to learn the role of local and global variables in scope of variables in PL/SQL:

The output:

Like any other language, PL/SQL also provides a functionality to handle exceptions. This is done using the EXCEPTION block. To raise exceptions explicitly, use the RAISE command.

To handle exceptions in PL/SQL, here is the syntax:

Above, WHEN is used for exceptions like:

With that, the following is to be used for default exception:

Before beginning our example, LET’s say we have the following table:

The following is our example wherein we are handling exceptions. The dept

The output:

The INSTRB is a function used in PL/SQL to RETURN the location of a string within ANOTHER string. The value is RETURNED in bytes.

An example:

The above returns the OUTPUT in bytes:

The valid values for seconds:

• 00 to 59.9(n), where 9(n) is the PRECISION of time FRACTIONAL seconds
• The 9(n) PORTION is not applicable for DATE.

A record is a data structure in PL/SQL to hold data items of different kinds.

For our examples on records in PL/SQL, we have the following sample table:

PL/SQL handles the following types of records:

• Table-based records

To create table-based records, you need to use the %ROWTYPE attribute. Using the same we will fetch the records of a department with id = 3:

The output:

• Cursor-based records

In PL/SQL, use the %ROWTYPE attribute with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement.

The following is an example:

The output:

• User-defined records
  

In PL/SQL, it is possible to create your own records i.e. user-defined. With that, you can set the different record structures.

Let us take an example of a Laptop’s record. Here is how you can declare:

You can now ACCESS the fields using the dot(.) operator.

The following is the example wherein we have created user-define records and accesses the fields:

The output:

To join two or more strings i.e. concatenation, PL/SQL COMES with concatenation operator (||). This operator returns a string value.

The following is the syntax:

Here,

• str1 = String 1
• str2 = String 2
• str_n = nth string

The following is an example:

The output:

The MODES of parameters in a PL/SQL subprogram includes the following:

• IN

The IN parameter is a read-only parameter that allows you to PASS a value to the subprogram.

It acts as a constant in the subprogram. A constant, LITERAL, initialized variable, or expression can be PASSED as an IN parameter.

• OUT

The OUT parameter acts like a variable in a subprogram. Its value can be changed unlike the IN parameter.The value can be reference after assignment.

• IN OUT

The actual parameter is passed by value. The initial value is passed to a subprogram. The updated value is returned to the caller.

Let us see an example:

The output:

Explicit cursors are custom DEFINED i.e. programmer defined cursors that gives more control over the context area. Oracle FORMS a memory area, when an SQL statement is processed, which is called context area.

The following is the syntax to create explicit cursor:

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is CREATED on a SELECT statement. Let us now see how to work with Explicit Cursors,

• Cursor Declaration

Let us declare a cursor and set a cursor name with the SELECT statement:

Above, “d_deptbudget” is our cursor.

• Open the Cursor

The memory gets allocated for the cursor when it is opened in PL/SQL.

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it

• Fetch the Cursor

Access one row at a time when a cursor is fetched in PL/SQL.

The following is when you fetch a cursor which is already opened:

• Close the Cursor

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

Let us now see an example to work with explicit cursors in PL/SQL:

The output display department id and name:

A delimiter is a compound symbol with a special meaning to PL/SQL. For example, you USE delimiters to represent ARITHMETIC operations such division. For example, a delimiter symbol is also the assignment OPERATOR used in PL/SQL:

An example would be:

The following are the symbols of Delimiters:

The data type TIMESTAMP stores the year, month, DAY, hour, minute, and second. TIMESTAMP extends the data type DATE.

The FOLLOWING is the syntax:

Here,

Precision: The number of digits in the fractional part of the seconds field. Optional.

The following is an example:

The output:

20-DEC-18 07.48.53.28 AM 

In PL/SQL, you can also find a datatype: 

The above datatype includes a time-zone displacement. The time-zone displacement is the difference between LOCAL time and COORDINATED Universal Time (UTC,) formerly Greenwich Mean Time (GMT). This difference is in hours and minutes.

The following is the syntax:

Here,

Precision: The number of digits in the fractional part of the seconds field. Optional.

Let us see an example:

The output:

The Datetime Interval DATA type stores and MANIPULATE DATES, times, and intervals.

A variable with date and time data type stores values called datetimes. A variable with interval data type stores values called intervals.

Here are the field NAMES:

YEAR

• Valid Datetime Value: 4712 to 9999 (excluding year 0)
• Valid Interval Value: Any non-zero integer

MONTH

• Valid Datetime Value: 01 to 12
• Valid Interval Value: 0 to 11

DAY 

• Valid Datetime Value: 01 to 31
• Valid Interval Value: Any non-zero integer

HOUR

• Valid Datetime Value: 00 to 23
• Valid Interval Value: 0 to 23

MINUTE

• Valid Datetime Value: 00 to 59
• Valid Interval Value: 0 to 59

SECOND

• Valid Datetime Value: 00 to 59.9(n), where 9(n) is the precision of time fractional seconds
• Valid Interval Value: 0 to 59.9(n), where 9(n) is the precision of interval fractional seconds

TIMEZONE_HOUR

• Valid Datetime Value: -12 to 14 (range accommodates daylight savings time changes)
• Valid Interval Value: Not Applicable

TIMEZONE_MINUTE

• Valid Datetime Value: 00 to 59
• Valid Interval Value: Not Applicable

TIMEZONE_REGION

• Valid Datetime Value: Found in the dynamic performance view V$TIMEZONE_NAMES
• Valid Interval Value: Not Applicable

TIMEZONE_ABBR

• Valid Datetime Value: Found in the dynamic performance view V$TIMEZONE_NAMES
• Valid Interval Value: Not Applicable

PL/SQL allocates memory for the variable's value when a variable is declared. The storage location is IDENTIFIED by the variable name.

Declaration

The following is the syntax:

Here,

• variable_name: Name of the variable
• datatype: The datatype

Example for a string variable:

Let us see an example for a number precision limit. This is called CONSTRAINT declaration that needs less memory:

Initialization

To INITIALIZE a variable, you can use the DEFAULT keyword as well as the assignment operator. Let us see them one by one.

Initialization with DEFAULT keyword:

The output: 

Initialization with assignment operator: 

The output: 

Laptop

The predefined numeric datatypes are subtypes of the SCALAR Data Types.

The following are the subtypes of the numeric datatypes:

• PLS_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

• BINARY_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

• BINARY_FLOAT

Single-precision IEEE 754-format FLOATING-point number

• BINARY_DOUBLE

Double-precision IEEE 754-format floating-point number

• DEC(prec, scale)

ANSI specific fixed-point type with maximum precision of 38 decimal digits

• DECIMAL(prec, scale)

IBM specific fixed-point type with maximum precision of 38 decimal digits

• NUMERIC(PRE, secale)

Floating type with maximum precision of 38 decimal digits

• DOUBLE PRECISION

ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)

• SMALLINT

ANSI and IBM specific integer type with maximum precision of 38 decimal digits

• REAL

Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)

In PL/SQL, use the %ROWTYPE ATTRIBUTE with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement.

The following is an example:

The output: 

A transaction in PL/SQL is an ATOMIC unit that may consist of one or more related SQL statements.

To commit a transaction in PL/SQL, use the COMMIT command:

The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit REQUEST is made to commit a transaction.

Let’s say the following is the INSERT in the table. At the END, execute the COMMIT command if you want to commit the transaction:

Implicit cursors in PL/SQL are created AUTOMATICALLY by Oracle when an SQL statement is executed. This occurs when there is no explicit cursor for the statement.

An implicit cursor is associated, when INSERT, UPDATE and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which all rows got affected by the operation.

The attributes:

• %FOUND: It returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
• %NOTFOUND: The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
• %ISOPEN: It returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
• %ROWCOUNT: It returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

To USE any of the above attribute, use the following syntax:

Above, set any attribute name under “attribute_name”.

Let us consider the following table for our example:

Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well: 

The output: 

SINCE we updated the budget for all the departments above, therefore our table rows would look like this:

There are two types of EXCEPTIONS that exist in PL/SQL:

System Defined Exceptions

The predefined exceptions in PL/SQL are System Defined Exceptions. If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions are raised when an attempt is MADE to store DUPLICATE values in a database column constrained by a unique index.

Let us learn about the predefined exceptions in PL/SQL:

• ACCESS_INTO_NULL: This exception is raised when an attempt is made to the attributes of an uninitialized object.
• CASE_NOT_FOUND: This exception is raised when none of the choices in the WHEN clause of a CASE statement is selected and there is no ELSE clause.
• COLLECTION_IS_NULL: This exception is raised when an attempt is made to apply collection methods other than EXISTS to an uninitialized nested table or varray.
• DUP_VAL_ON_INDEX: This exception is raised when duplicate values are attempted to be stored in a column with unique index.
• INVALID_CURSOR: This exception is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
• INVALID_NUMBER: This exception is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
• LOGIN_DENIED: This exception is raised when a program attempts to log on to the database with an invalid username or password.
• NO_DATA_FOUND: This exception is raised when a SELECT INTO statement returns no rows.
• NOT_LOGGED_ON: This exception is raised when a database call is issued without being connected to the database.
• PROGRAM_ERROR: This exception is raised when there is an internal problem.
• ROWTYPE_MISMATCH: This exception is raised when a cursor fetches value in a variable having incompatible data type.
• SELF_IS_NULL: This exception is raised when a member method is invoked, but the instance of the object type was not initialized.
• STORAGE_ERROR: This exception is raised when PL/SQL ran out of memory.
• TOO_MANY_ROWS: This exception is raised when SELECT INTO statement returns more than one row.
• VALUE_ERROR: This exception is raised when arithmetic, conversion, or truncation error occurs.
• ZERO_DIVIDE: This exception is raised when an attempt is made to divide a number by zero.

User-Defined Exceptions

Like other programming languages, PL/SQL also allow users to create own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.

To create own exception, firstly consider the following table <DEPTBUDGET>:

Let’s say we created the following procedure and a user-defined exception in it:

On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

In PL/SQL, it is possible to create your own RECORDS i.e. user-defined. With that, you can set the different record structures.

Let us take an example of a Laptop’s record. Here is how you can declare:

You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:

The OUTPUT: 

Literals are syntactic representation of NUMERIC, character, string, or BOOLEAN value. The literals in PL/SQL are case-sensitive.

The FOLLOWING are the literals in PL/SQL:

• Numeric Literals
  Examples: 20 -20 0
• Character Literals
  Examples: 'A', '%', '9'
• String Literals

• BOOLEAN Literals

Examples: TRUE, FALSE

• Date and TIME Literals

If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions is raised when an attempt is MADE to store duplicate VALUES in a database column CONSTRAINED by a unique index.

Let us learn about the predefined exceptions in PL/SQL:

• ACCESS_INTO_NULL

This exception is raised when an attempt is made to the attributes of an uninitialized object.

• CASE_NOT_FOUND

This exception is raised when none of the choices in the WHEN clause of a CASE statement is selected and there is no ELSE clause.

• COLLECTION_IS_NULL

This exception is raised when an attempt is made to apply collection methods other than EXISTS to an uninitialized nested table or varray.

• DUP_VAL_ON_INDEX

This exception is raised when duplicate values are ATTEMPTED to be stored in a column with unique index.

• INVALID_CURSOR

This exception is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.

• INVALID_NUMBER

This exception is raised when the CONVERSION of a character string into a number fails because the string does not represent a valid number.

• LOGIN_DENIED

This exception is raised when a program attempts to log on to the database with an invalid username or password.

• NO_DATA_FOUND

This exception is raised when a SELECT INTO statement returns no rows.

• NOT_LOGGED_ON

This exception is raised when a database call is issued without being connected to the database.

• PROGRAM_ERROR

This exception is raised when there is an internal problem.

• ROWTYPE_MISMATCH

This exception is raised when a cursor fetches value in a variable having incompatible data type.

• SELF_IS_NULL

This exception is raised when a member method is invoked, but the instance of the object type was not initialized.

• STORAGE_ERROR

This exception is raised when PL/SQL ran out of memory.

• TOO_MANY_ROWS

This exception is raised when SELECT INTO statement returns more than one row.

• VALUE_ERROR

This exception is raised when arithmetic, conversion, or truncation error occurs.

• ZERO_DIVIDE

This exception is raised when an attempt is made to divide a number by zero.

The types of Datatypes are categorized as the following:

1. Scalar DATA Type

The Data Types that have SINGLE values with no internal components. Here are the predefined Scalar Data Types with the description about its data:

• Numeric: Numeric values. It includes the following sub-types: PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT, BINARY_DOUBLE, DOUBLE PRECISION, FLOAT, INT, INTEGER, SMALLINT, REAL, etc.
• CHARACTER: Single characters or strings of characters. It includes the following sub-types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, LONG, LONG RAW, ROWID, UROWID, etc.
• Boolean: Logical values
• Datetime: Date and Time
• Interval: Time Interval

2. Composite Data Type

Data items that have internal components that can be accessed individually

3. Reference Data Type

Pointers to other data items

4. Large Object Data Types (LOB)

These Data Types are pointers to large objects that are stored separately from other data items, like text, graphic images, video clips, etc. The following are the data types:

• BFILE: STORE large binary objects in operating system files outside the database. The size is system-dependent and exceed 4 GB.
• BLOB: Store large binary objects in the database. The size is 8 to 128 TB.
• CLOB: Store large blocks of character data in the database. The size is 8 to 128 TB.
• NCLOB: Store large blocks of NCHAR data in the database. The size is 8 to 128 TB.

PL/SQL is an extension for SQL and Oracle. It enhances the CAPABILITIES of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is strongly integrated with SQL and supports both static and dynamic SQL.  

The following are the advantages of PL/SQL:

• Static and Dynamic SQL

PL/SQL has both static and dynamic SQL. Static SQL with DML operations and transaction control from PL/SQL block. SQL allows embedding DDL statements in PL/SQL blocks in Dynamic SQL.

• Exception Handling

PL/SQL has exception handling that helps in EASIER debugging.

• For Programmers

PL/SQL allows faster update of database. Therefore, beneficial for programmers.

• Faster Execution

With PL/SQL, we can easily perform faster execution of queries.

• Block of Statements

PL/SQL executes as a whole block i.e. sends entire block of statements to the database at once.

• Extension of SQL

Since PL/SQL is an extension of SQL, you get access to predefined SQL packages.

• Triggers

PL/SQL also comes with the functionality of Triggers. Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP).

• Optimal Efficiency

PL/SQL and SQL brings optimal efficiency since it runs within the same server process.

• Error Management

Error Management in PL/SQL brings in internally defined, predefined, and user-define exceptions and process to handle them with error codes and messages. With that, PL/SQL also has RAISE statement for your own defined exceptions.

Triggers are stored in a database and fired when some event OCCURS. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.

Syntax

The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:

Here,

• CREATE [OR REPLACE] TRIGGER name_of_trigger – The statement creates or REPLACES existing trigger with the name_of_trigger.
• {BEFORE | AFTER | INSTEAD OF} – The BEFORE or AFTER specifies when the trigger will get executed. The INSTEAD OF is for CREATING trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} – These clauses specify the DML operation.
• [OF col_name] − The column name that will be updated.
• [ON table_name] – The name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n] − Refer new and old values for various DML statements.
• [FOR EACH ROW] − A row-level trigger, i.e. the trigger will be executed for each row being affected.
• WHEN (condition) − A condition for rows for which the trigger would fire.

Let us now take an example wherein we will create a trigger, trigger it, and even delete it.

For that, we are considering the following table <DEPTBUDGET>:

Create Trigger

Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget DIFFERENCES between new and old assigned budgets:

The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above: 

We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.

The statement would update an existing record in the table:

We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following: 

A code with a comment helps the developers in reading the source code properly. Like other programming languages, in PL/SQL, you can easily add comment to the code. Compiler IGNORES the content under the comment.

The single-LINE comments under PL/SQL begin with the DELIMITER:

The multi-line comments are enclosed by:

Let us see both the comments in the following code:

Above, we have used both the single line and multi-line comment. The output:

The EXIT WHEN statement has a condition under the WHEN clause. The statement acts like NULL before the condition is TRUE.

The following is the syntax:

Here is an example: 

The output: 

Oracle forms a memory area, when an SQL statement is processed. This memory area is called CONTEXT area. A cursor in PL/SQL is a pointer to this context area. It has information about processing the statement. A PL/SQL cursor holds the rows (one or more) returned by a SQL statement.

The following are the two forms of cursors:

• Implicit Cursors (Automatic)

Implicit cursors in PL/SQL are created automatically by Oracle when an SQL statement is executed. This OCCURS when there is no explicit cursor for the statement.

An implicit cursor is associated, when INSERT, UPDATE and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which all rows got affected by the operation.

The attributes:

• %FOUND: It returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
• %NOTFOUND: The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
• %ISOPEN: It returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
• %ROWCOUNT: It returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

To USE any of the above attribute, use the following syntax:

Above, set any attribute name under “attribute_name”.Let us consider the following table for our example:

Now, we will update the budget of all the DEPARTMENTS and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

The output:

Since we updated the budget for all the departments above, our table rows would look like this:

• Explicit Cursors (Programmer Defined)

Explicit cursors are custom defined i.e. programmer defined cursors that gives more control over the context area. Oracle forms a memory area, when an SQL statement is processed, which is called context area.

The following is the syntax to create explicit cursor:

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,

• Cursor Declaration

Let us declare a cursor and set a cursor name with the SELECT statement:

Above, “d_deptbudget” is our cursor.

• Open the Cursor

The memory gets allocated for the cursor when it is opened in PL/SQL.Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it

• Fetch the Cursor

Access one row at a time when a cursor is fetched in PL/SQL.The following is when you fetch a cursor which is already opened:

• Close the Cursor

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

Let us now see an example to work with explicit cursors in PL/SQL:

The output display department id and name:

PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is strongly integrated with SQL and supports both static and DYNAMIC SQL.

The following are the characteristics of PL/SQL:

• PL/SQL is a procedural language that extends SQL.
• PL/SQL has a built-in interpreted PROGRAMMING environment.
• PL/SQL's syntax is shaped ADA and Pascal programming language.
• It is an Extension to SQL
• Structured programming is possible with PL/SQL through functions and procedures.
• It has Block Structures as well as nest block structures. Executes as a whole block.
• PL/SQL has object-oriented features in it.
• Provides High PERFORMANCE for Applications
• Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP).
• Productivity to Programmers
• PL/SQL is a standard language for Oracle development.
• PL/SQL is used to write program blocks, functions, procedures triggers, etc.
• It has cursors. Oracle forms a memory area, when an SQL statement is PROCESSED. This memory area is called CONTEXT area. A cursor in PL/SQL is a pointer to this context area. It has information about processing the statement.
• PL/SQL also comes with the functionality of Triggers.

PL/SQL is an extension for SQL and Oracle. It enhances the CAPABILITIES of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is AVAILABLE in Oracle Database since version 6. However, PL/SQL is ALSO available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.

SQL is a domain-specific language used in programming. It is a Structured Query Language for database creation, deletion, fetching rows, modifying rows, etc. relational database management system

The following are the differences:

PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced.

It is available in Oracle Database since version 6. HOWEVER, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.

TL/SQL is Transact Structure Query language. T-SQL EXPANDS on the SQL standard to add procedural programming, local variables, etc. It is Microsoft's and Sybase's proprietary extension to the SQL.

The following are the differences:

PL/SQL is an EXTENSION for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced.

It is AVAILABLE in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.

PL/SQL is strongly integrated with SQL and supports both static and dynamic SQL.

The following are the features in PL/SQL:

• PL/SQL is a procedural language that extends SQL.
• PL/SQL has a built-in interpreted programming environment.
• PL/SQL's syntax has shaped ADA and Pascal programming language.
• Structured programming is possible with PL/SQL through functions and procedures.
• It has Block Structures as well as nest block structures. Executes as a WHOLE block.
• PL/SQL has object-oriented features in it.
• PL/SQL also comes with the functionality of Triggers. Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP).
• PL/SQL is a standard language for Oracle development.
• PL/SQL is used to write program blocks, functions, procedures triggers, etc.
• It has cursors. Oracle FORMS a memory area, when an SQL statement is processed. This memory area is called context area. A cursor in PL/SQL is a pointer to this context area. It has information about processing the statement.