PL SQL Interview Questions and Answers Database

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.

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 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.

Expect to come across this popular question in PL SQL questions.  

Oracle forms a memory area when an SQL statement is processed. This memory area is called the 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 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 attributes, use the following syntax:

sql%attribute_name

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

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      95979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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:

DECLARE  
  rows_count number(2);
BEGIN
  UPDATE deptbudget
  SET budget = budget + 3000;
  IF sql%notfound THEN
     dbms_output.put_line('Nothing selected!');
  ELSIF sql%found THEN
     rows_count := sql%rowcount;
     dbms_output.put_line( rows_count || ' rows affected! ');
  END IF;  
END;
/    

The output:

5 rows affected!

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

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 59678      |
|  2 | Marketing     | EAST |      90687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    79878 |
|  5 | Accounting   | NORTHWEST| 89767      |
+----------+------------------+------------------+--------------+

• Explicit Cursors (Programmer Defined)

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

The following is the syntax to create an explicit cursor:

CURSOR name_of_cursor IS select_statement;

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:

CURSOR d_deptbudget IS
  SELECT deptid, deptname, deptloc, budget FROM deptbudget;

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.

OPEN d_deptbudget;

• 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 that is already opened:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

• Close the Cursor

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

CLOSE d_deptbudget;

Let us now see an example of working with explicit cursors in PL/SQL:

DECLARE  
  d_deptid deptbudget.deptid%type;  
  d_deptname deptbudget.deptname%type;    
  CURSOR d_deptbudget IS  
     SELECT deptid, deptname FROM deptbudget;
BEGIN  
  OPEN d_deptbudget;  
  LOOP  
     FETCH d_deptbudget into d_deptid, d_deptname;  
     EXIT WHEN d_deptbudget%notfound;  
     dbms_output.put_line(d_deptid || ' ' || d_deptname);  
  END LOOP;  
  CLOSE d_deptbudget;  
END;  
/  

The output display department id and name:

1 Finance
2 Marketing
3 Operations
4 Technical
5 Accounting

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:

EXIT WHEN condition; 

Here is an example: 

DECLARE
  val number(2) := 2;
BEGIN
  WHILE val < 10 LOOP
     dbms_output.put_line ('Value = ' || val);
     val := val + 1;
        EXIT WHEN val = 5;
  END LOOP;
END;
/

The output: 

Value = 2
Value = 3
Value = 4

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:

-- (double hyphen)

The multi-line comments are enclosed by:

/* and */

Let us see both the comments in the following code:

DECLARE
  -- Variable declaration and initialization
  car varchar2(15) := 'Bentley';
BEGIN
  /*
  * UPPER() method in PL/SQL
  */
  dbms_output.put_line(UPPER(car));
END;
/

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

BENTLEY

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:

CREATE [OR REPLACE ] TRIGGER name_of_trigger  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE
  Declaration-statements
BEGIN  
  Executable-statements
EXCEPTION
  Exception-handling-statements
END;

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>:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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:

CREATE OR REPLACE TRIGGER budget_diff
BEFORE DELETE OR INSERT OR UPDATE ON deptbudget
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
  budget_diff number;
BEGIN
  budget_diff := :NEW.budget  - :OLD.budget;
  dbms_output.put_line('Old Budget Assigned =' || :OLD.budget);
  dbms_output.put_line('New Budget Assigned = ' || :NEW.budget);
  dbms_output.put_line('Differences in Budget = ' || budget);
END;
/

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

BEFORE DELETE OR INSERT OR UPDATE ON deptbudget 

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:

UPDATE budget
SET  = budget + 4500
WHERE id = 4;

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

Old Budget Assigned = 76878
New Budget Assigned = 81378
Differences in Budget = 4500

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.

A must-know for anyone heading into a PL SQL interview, this question is frequently asked in PL SQL developer interview questions.  

The types of Datatypes are categorized as the following:

1. Scalar Data Type

The Data Types have single values with no internal components. Here are the predefined Scalar Data Types with the description of 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 exceeds 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.

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.

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

'Mystring!'
'Web!'

• BOOLEAN Literals

Examples: TRUE, FALSE

• Date and Time Literals

DATE '2018-12-20';
TIMESTAMP '2018-12-15 11:10:30';

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:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number,
);
l1 laptop;
l1 laptop;

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:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number
);
lp1 laptop;
lp2 laptop;
lp3 laptop;


BEGIN
  -- Laptop 1 specification
  lp1.brand:= 'Dell';
  lp1.RAM:= 4;  
  lp1.SNO:=  87667;


  -- Laptop 2 specification
  lp2.brand:= 'Lenevo';
  lp2.RAM:= 4;  
  lp2.SNO:=  47656;


  -- Laptop 3 specification
  lp3.brand:= 'HP';
  lp3.RAM:= 8;  
  lp3.SNO:=  98989;
 -- Laptop 1 record
  dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand);
  dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM);
  dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO);
-- Laptop 2 record
  dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand);
  dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM);
  dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO);


-- Laptop 3 record
  dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand);
  dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM);
  dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO);
END;

The output: 

Laptop 1 Brand = Dell
Laptop 1 RAM = 4
Laptop 1 SNO = 87667
Laptop 2 Brand = Lenevo
Laptop 2 RAM = 4
Laptop 2 SNO = 47656
Laptop 3 Brand = HP
Laptop 3 RAM = 8
Laptop 2 SNO = 98989

It's no surprise that this one pops up often in PLSQL interview questions.  

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 array.
• DUP_VAL_ON_INDEX: This exception is raised when duplicate values are attempted to be stored in a column with a 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 a value in a variable having an incompatible data type.
• SELF_IS_NULL: This exception is raised when a member method is invoked, but the instance of the object type is not initialized.
• STORAGE_ERROR: This exception is raised when PL/SQL runs 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 an 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 allows users to create their 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 your own exception, first consider the following table <DEPTBUDGET>:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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

DECLARE
  id deptbudget.id%type := &d_id;
  deptname deptbudget.deptname%type;
  deptloc deptbudget.deptloc%type;  
  my_exception  EXCEPTION;
BEGIN
  IF d_id <= 0 THEN
     RAISE my_exception;
  ELSE
     SELECT  deptname, deptloc INTO deptname, deptloc
     FROM deptbudget
     WHERE id = d_id;
     DBMS_OUTPUT.PUT_LINE ('Name: '||  deptname);
     DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc);
  END IF;
EXCEPTION
  WHEN my_exception THEN
     dbms_output.put_line('ID is always greater than zero!');
  WHEN no_data_found THEN
     dbms_output.put_line('No such department in the company!');
  WHEN others THEN
     dbms_output.put_line('Error!');  
END;
/

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 executed, it raises the following user-defined error:

ID is always greater than zero!

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:

sql%attribute_name

Above, set any attribute name under “attribute_name”.

Let us consider the following table for our example:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      95979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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: 

DECLARE  
  rows_count number(2);
BEGIN
  UPDATE deptbudget
  SET budget = budget + 3000;
  IF sql%notfound THEN
     dbms_output.put_line('Nothing selected!');
  ELSIF sql%found THEN
     rows_count := sql%rowcount;
     dbms_output.put_line( rows_count || ' rows affected! ');
  END IF;  
END;
/    

The output: 

5 rows affected! 

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

<DEPTBUDGET> 
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 59678      |
|  2 | Marketing     | EAST |      90687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    79878 |
|  5 | Accounting   | NORTHWEST| 89767      |
+----------+------------------+------------------+--------------+

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:

COMMIT; 

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:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (1, 'Finance', 'NORTH');

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (2, 'Marketing', 'EAST');

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (3, 'Operations', 'WEST');

COMMIT;

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:

DECLARE
  CURSOR cur_dept is
     SELECT deptid, deptname, deptloc
     FROM deptbudget;
  rec_dept cur_dept %rowtype;
BEGIN
  OPEN cur_dept ;
  LOOP
     FETCH cur_dept into rec_dept;
     EXIT WHEN cur_dept %notfound;
     DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc);
  END LOOP;
END;
/

The output: 

1 Finance NORTH
2 Marketing EAST
3 Operations WEST
4 Technical SOUTH
5 Accounting NORTHWEST

PL/SQL procedure successfully completed.

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)

A common question in PL/SQL interview questions, don't miss this one.  

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:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

Here,

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

Example for a string variable:

name varchar2(15); 

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

a number(5, 2) 

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:

DECLARE
  msg varchar2(15) DEFAULT 'House';
BEGIN
  dbms_output.put_line(msg);
END;
/

The output: 

House 

Initialization with assignment operator: 

DECLARE
  device varchar2(11) := 'Laptop';
BEGIN
  dbms_output.put_line(device);
END;
/

The output: 

Laptop

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

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

The following is the syntax:

TIMESTAMP[(precision) 

Here,

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

The following is an example:

DECLARE
 mydate TIMESTAMP(2);
 BEGIN
   mydate := '20-Dec-2018 07:48:53.275';
   DBMS_OUTPUT.PUT_LINE( TO_CHAR(mydate ));
END;
/

The output:

20-DEC-18 07.48.53.28 AM 

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

TIMESTAMP WITH TIME ZONE 

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:

TIMESTAMP[(precision)] WITH TIME ZONE 

Here,

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

Let us see an example:

DECLARE
 mydate TIMESTAMP(2) WITH TIME ZONE;
 BEGIN
   mydate := '10-Dec-2018 11:28:55.114 AM +03:00';
   DBMS_OUTPUT.PUT_LINE( TO_CHAR(mydate ));
END;
/

The output:

10-DEC-18 11.28.55.11 AM +03:00 

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:

DECLARE
  device varchar2(15) := 'Laptop';
BEGIN
  dbms_output.put_line(device);
END;
/

The following are the symbols of Delimiters:

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:

CURSOR name_of_cursor IS select_statement;

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:

CURSOR d_deptbudget IS
  SELECT deptid, deptname, deptloc, budget FROM deptbudget;

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

OPEN d_deptbudget;

• 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:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

• Close the Cursor

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

CLOSE d_deptbudget;

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

DECLARE  
  d_deptid deptbudget.deptid%type;  
  d_deptname deptbudget.deptname%type;    
  CURSOR d_deptbudget IS  
     SELECT deptid, deptname FROM deptbudget;
BEGIN  
  OPEN d_deptbudget;  
  LOOP  
     FETCH d_deptbudget into d_deptid, d_deptname;  
     EXIT WHEN d_deptbudget%notfound;  
     dbms_output.put_line(d_deptid || ' ' || d_deptname);  
  END LOOP;  
  CLOSE d_deptbudget;  
END;  
/  

The output display department id and name:

1 Finance
2 Marketing
3 Operations
4 Technical
5 Accounting

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:

DECLARE
  val number;
PROCEDURE display(n IN OUT number) IS
BEGIN
 n := n * n;
END;  
BEGIN
  val:= 42;
  display(val);
  dbms_output.put_line('Square of 42 = ' || val);
END;
/

The output:

Square of 42 = 1764

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:

str1 || str2 [ || str_n ]

Here,

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

The following is an example:

DECLARE
  one varchar2(20) := 'Website';
  two varchar2(20) := ' World!';
BEGIN
  dbms_output.put_line(one || two);
END;
/

The output:

Website World!

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:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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:

DECLARE
  rec_dept deptbudget%rowtype;
BEGIN
  SELECT * into rec_dept
  FROM customerdeptbudgets
  WHERE deptid = 3;  
  dbms_output.put_line('Department ID =' || rec_dept.deptd);
  dbms_output.put_line('Department Name = ' || rec_dept.deptname);
  dbms_output.put_line('Department Location = ' || rec_dept.deptloc);
  dbms_output.put_line('Department Budget = ' || rec_dept.budget);
END;
/

The output:

Department ID = 3
Department Name = Operations
Department Location = WEST
Department Budget = 98979
PL/SQL procedure successfully completed.

• 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:

DECLARE
  CURSOR cur_dept is
     SELECT deptid, deptname, deptloc
     FROM deptbudget;
  rec_dept cur_dept %rowtype;
BEGIN
  OPEN cur_dept ;
  LOOP
     FETCH cur_dept into rec_dept;
     EXIT WHEN cur_dept %notfound;
     DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc);
  END LOOP;
END;
/

The output:

1 Finance NORTH
2 Marketing EAST
3 Operations WEST
4 Technical SOUTH
5 Accounting NORTHWEST
PL/SQL procedure successfully completed.

• 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:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number,
);
l1 laptop;
l1 laptop;

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:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number
);
lp1 laptop;
lp2 laptop;
lp3 laptop;
BEGIN
  -- Laptop 1 specification
  lp1.brand:= 'Dell';
  lp1.RAM:= 4;  
  lp1.SNO:=  87667;
  -- Laptop 2 specification
  lp2.brand:= 'Lenevo';
  lp2.RAM:= 4;  
  lp2.SNO:=  47656;
  -- Laptop 3 specification
  lp3.brand:= 'HP';
  lp3.RAM:= 8;  
  lp3.SNO:=  98989;
 -- Laptop 1 record
  dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand);
  dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM);
  dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO);
-- Laptop 2 record
  dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand);
  dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM);
  dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO);
-- Laptop 3 record
  dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand);
  dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM);
  dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO);
END;

The output:

Laptop 1 Brand = Dell
Laptop 1 RAM = 4
Laptop 1 SNO = 87667
Laptop 2 Brand = Lenevo
Laptop 2 RAM = 4
Laptop 2 SNO = 47656
Laptop 3 Brand = HP
Laptop 3 RAM = 8
Laptop 2 SNO = 98989

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.

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:

INSTR('Jack Sparrow', 'c')

The above returns the output in bytes:

3

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:

DECLARE
  <declarations section>
BEGIN
  <executable command(s)>
EXCEPTION
  <exception handling >
  WHEN exception1 THEN  
     exception1-handling-statements  
  WHEN exception2  THEN
     exception2-handling-statements  
  WHEN exception3 THEN  
     exception3-handling-statements
  ........
  WHEN others THEN
     exception-handling-statements
END;

Above, WHEN is used for exceptions like:

WHEN no_data_found THEN

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

WHEN others THEN

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

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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

DECLARE
 d_id deptbudget.deptid%type := 20;
 d_ name deptbudget.deptname%type;
 d_loc deptbudget.deptloc%type;  
BEGIN
     SELECT  deptname, deptloc INTO d_name, d_loc
     FROM deptbudget
     WHERE deptid = d_id;
     DBMS_OUTPUT.PUT_LINE ('Name: '||  d_name);
     DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc);
  END IF;
EXCEPTION
  WHEN no_data_found THEN
     dbms_output.put_line('No such department in the company!');
  WHEN others THEN
     dbms_output.put_line('Error!');  
END;
/

The output:

No such department in the company

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:

DECLARE
  -- Global variables  
  a number := 5;  
BEGIN  
  dbms_output.put_line('Outer Variable a = ' || a);
  DECLARE  
     -- Local variables
     a number := 10;  
  BEGIN  
     dbms_output.put_line('Inner Variable a = ' || a);
  END;  
END;
/

The output:

Outer Variable a = 5
Inner Variable a = 10

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:

TYPE type_name IS TABLE OF element_type [NOT NULL];
name_of_table type_name;

The following is an example:

DECLARE
  TYPE deptname IS TABLE OF VARCHAR2(10);
  TYPE budget IS TABLE OF INTEGER;  
  names deptname;
  deptbudget budget;
BEGIN
  names := deptname ('Finance', 'Sales', 'Marketing');
  deptbudget := budget (89899, 67879, 98999);
  FOR i IN 1 .. names.count LOOP
     dbms_output.put_line('Department = '||names(i)||', Budget = ' || deptbudget(i));
  end loop;
END;
/  

The output:

Department = Finance, Budget = 89899
Department = Sales, Budget = 67879
Department = Marketing, Budget = 98999

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:

sum := sum +10;

The following is another example:

DECLARE
  cost number := 3000;
BEGIN  
  dbms_output.put_line(cost);
END;
/

The output:

3000

This is a frequently asked question in PLSQL interview questions.  

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

COMMIT;

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:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (1, 'Finance', 'NORTH');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (2, 'Marketing', 'EAST');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (3, 'Operations', 'WEST');
COMMIT;

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

The syntax:

ROLLBACK [TO SAVEPOINT < name_of_savepoint>];

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.

ROLLBACK;

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:

SAVEPOINT name_of_savepoint;

Let us see an example of SAVEPOINT:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET)
VALUES (1, 'Finance', 'NORTH', 20000);
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET)
VALUES (2, 'Marketing', 'EAST', 30000);
SAVEPOINT s1;
UPDATE DEPTBUDGET
SET BUDGET = BUDGET - 4500;
ROLLBACK TO s1;
UPDATE DEPTBUDGET
SET BUDGET = BUDGET - 4500;  
WHERE DEPTNAME = 'MARKETING';
COMMIT;

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

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

COMMIT;

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:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (1, 'Finance', 'NORTH');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (2, 'Marketing', 'EAST');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (3, 'Operations', 'WEST');
COMMIT;

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:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number,
);
l1 laptop;
l1 laptop;

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:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number
);
lp1 laptop;
lp2 laptop;
lp3 laptop;
BEGIN
  -- Laptop 1 specification
  lp1.brand:= 'Dell';
  lp1.RAM:= 4;  
  lp1.SNO:=  87667;
  -- Laptop 2 specification
  lp2.brand:= 'Lenevo';
  lp2.RAM:= 4;  
  lp2.SNO:=  47656;
  -- Laptop 3 specification
  lp3.brand:= 'HP';
  lp3.RAM:= 8;  
  lp3.SNO:=  98989;
 -- Laptop 1 record
  dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand);
  dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM);
  dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO);
-- Laptop 2 record
  dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand);
  dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM);
  dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO);
-- Laptop 3 record
  dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand);
  dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM);
  dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO);
END;

The output:

Laptop 1 Brand = Dell
Laptop 1 RAM = 4
Laptop 1 SNO = 87667
Laptop 2 Brand = Lenevo
Laptop 2 RAM = 4
Laptop 2 SNO = 47656
Laptop 3 Brand = HP
Laptop 3 RAM = 8
Laptop 2 SNO = 98989

This question is a regular feature in PL SQL interview questions, be ready to tackle it.  

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 pairs. The key here can be an Integer or String. The following is the syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
name_of_table type_name;

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:

DECLARE
  TYPE budget IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
  dept_budget budget ;
  dept_name VARCHAR2(20);
BEGIN
  dept_budget ('Operations') := 30000;
  dept_budget ('Finance') := 25000;
  dept_budget ('Accounts') := 45000;  
  dept_name := dept_budget.FIRST;
  WHILE dept_name IS NOT null LOOP
     dbms_output.put_line
     ('Budget - ' || dept_name || ' Department = ' || TO_CHAR(dept_budget (dept_name )));
     dept_name := dept_budget .NEXT(dept_name );
  END LOOP;
END;
/

The output:

Budget - Accounts Department = 45000
Budget - Finance Department = 25000
Budget - Operations Department = 30000

This is a frequently asked question in PLSQL interview questions.  

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.

A must-know for anyone heading into a PL SQL interview, this question is frequently asked in PL SQL interview questions.  

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:

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
 else_statements ]
END CASE;]

Let us see an example:

DECLARE
  points number := 100;
BEGIN
  case  
     when points = 20 then dbms_output.put_line('Rank 4');
     when points = 50 then dbms_output.put_line('Rank 3');
     when points = 75 then dbms_output.put_line('Rank 2');
     when points = 100 then dbms_output.put_line('Rank1... Topper');
     else dbms_output.put_line('No ranking!');
  end case;
END;
/

The output:

Rank1... Topper

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:

DECLARE
  i number(1);
  j number(1);
BEGIN
  << outer >>
  FOR i IN 1..5 LOOP
     << inner >>
     FOR j IN 1..2 LOOP
        dbms_output.put_line('i = '|| i || ', j = ' || j);
     END loop inner;
  END loop outer;
END;
/

The output:

i = 1, j = 1
i = 1, j = 2
i = 2, j = 1
i = 2, j = 2
i = 3, j = 1
i = 3, j = 2
i = 4, j = 1
i = 4, j = 2
i = 5, j = 1
i = 5, j = 2

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:

CASE selector
  WHEN 'value1' THEN Statement1;
  WHEN 'value2' THEN Statement2;
  WHEN 'value3' THEN Statement3;
  ...
  ELSE Sn;  -- default case
END CASE;

The following is an example:

DECLARE
  points number := 100;
BEGIN
  case points
     when 20 then dbms_output.put_line('Rank 4');
     when 50 then dbms_output.put_line('Rank 3');
     when 75 then dbms_output.put_line('Rank 2');
     when 100 then dbms_output.put_line('Rank1... Topper');
     else dbms_output.put_line('No ranking!');
  end case;
END;
/

The output:

Rank1... Topper

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.

• %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:

sql%attribute_name

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:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      95979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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:

DECLARE  
  rows_count number(2);
BEGIN
  UPDATE deptbudget
  SET budget = budget + 3000;
  IF sql%notfound THEN
     dbms_output.put_line('Nothing selected!');
  ELSIF sql%found THEN
     rows_count := sql%rowcount;
     dbms_output.put_line( rows_count || ' rows affected! ');
  END IF;  
END;
/    

The output:

5 rows affected!

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

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 59678      |
|  2 | Marketing     | EAST |      90687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    79878 |
|  5 | Accounting   | NORTHWEST| 89767      |
+----------+------------------+------------------+--------------+

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:

DECLARE
   one varchar2(20) := 'Website';
   two varchar2(20) := ' World!';
BEGIN
   dbms_output.put_line(one || two);
END;
/

The output:

Website World!

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.

A common question in PL SQL interview questions, don't miss this one.  

At each iteration in a basic loop in PL/SQL, a 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:

DECLARE
  a number := 2;
BEGIN
  LOOP
     dbms_output.put_line(a);
     a := a + 2;
     IF a > 10 THEN
        exit;
     END IF;
  END LOOP;
  dbms_output.put_line('After exit, the value of a = ' || a);
END;
/

The output:

2
4
6
8
10
After exit, the value of a = 12

A subprogram in PL/SQL is either a procedure or a function that can be invoked repeatedly. Each subprogram has a name and a list of parameters. It performs a specific task and the following two subprograms exist in PL/SQL:

• Functions: Return a single value.
• Procedures: Do not return a value directly.

The following are the parts of a subprogram:

• Declarative

It contains declarations of cursors, constants, variables, exceptions, etc.

• Executable

Had statements that perform actions.

• Exception Handling

The code that handles run-time errors.

The following is the syntax to create a subprogram (procedure):

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a subprogram (procedure):

CREATE OR REPLACE PROCEDURE one
AS
BEGIN
  dbms_output.put_line('Welcome!');
END;
/

The output displays the procedure created successfully:

Procedure created.

The default value is NULL. PL/SQL assigns NULL as the default value of a variable. To assign another value, you can use the DEFAULT keyword or the assignment operator. Let us see them one by one.Initialization with DEFAULT keyword:

DECLARE
  msg varchar2(15) DEFAULT 'House';
BEGIN
  dbms_output.put_line(msg);
END;
/

The output:

House

Initialization with assignment operator:

DECLARE
  device varchar2(11) := 'Laptop';
BEGIN
  dbms_output.put_line(device);
END;
/

The output:

Laptop

PL/SQL has subtypes of datatypes. This means subtype is a subset of a datatype. The following are the types and subtypes of Numeric data type in PL/SQL

• 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)

The following are some of the Oracle Database PL/SQL packages:

• DBMS_ALERT

You can use the database triggers to alert an application when a database values change.

• DBMS_OUTPUT

You may have used PUL_LINE in this package to display output from PL/SQL blocks, subprograms, packages, and triggers. This also displays debugging information.

• DBMS_PIPE Package

This package lets different sessions to communicate over named pipes

• HTF and HTP Packages

This allows the PL/SQL programs to generate HTML tags.

• UTL_SMTP

Using this package, you can send emails over SMTP (Simple Mail Transfer Protocol).

Recursion works the same way as we saw in programming languages like C, C++, Java, C#, etc.

A program or subprogram calling itself is called a recursive call and the process is recursion.

Let us see an example wherein we are displaying the usage of Recursive call:

DECLARE
  n number;
  factorial number;  
FUNCTION fact(a number)
RETURN number  
IS
  f number;
BEGIN
  IF a=0 THEN
     f := 1;
  ELSE
     f := a * fact(a-1);
  END IF;
RETURN f;
END;  
BEGIN
  n:= 3;
  factorial := fact(n);
  dbms_output.put_line('Value of Factorial '|| n || ' is ' || factorial);
END;
/

The output:

Value of Factorial 3 is 6

To return the ASCII value of a character, you need to use the ASCII() method. It returns a numeric value, which is the ASCII value.

The following is the syntax:

ASCII(myCharacter)

Here, myCharacter is the character you need to mention to get the ASCICC value.

The following is an example:

DECLARE
  a varchar(1) := 'B';
BEGIN
  dbms_output.put_line(ASCII(a));
END;
/

The output:

66

NCHR is a function in PL/SQL that returns the character based on the specified number, which is an ASCII value. An example:

NCHR(77)

It displays the associated character:

M

The RPAD function is used to pad the right side of a string with characters. An example here pads the string:

RPAD(jack, 3)

The output is:

jac

A subprogram in PL/SQl is a standalone subprogram at the schema level. The CREATE PROCEDURE or the CREATE FUNCTION statement is used in PL/SQL to create a standalone procedure.

• If you want to execute a standalone procedure, then use the EXECUTE keyword.
• If you want to delete a standalone procedure in PL/SQL, then you need to use the DROP PROCEDURE or DROP FUNCTION statement.

The following is the syntax to create a procedure:

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a procedure:

CREATE OR REPLACE PROCEDURE one
AS
BEGIN
  dbms_output.put_line('Chalk and Duster!!');
END;
/

The output displays the procedure created successfully:

• Procedure created.
• Now to execute the procedure created above, you need to use the EXECUTE keyword. The name of the above procedure is “one”.
• To call it:

EXECUTE one;

The above call will give the following output:

Chalk and Duster!

The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types introduced in PL/SQL 11g Release 1. These are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively.

The SIMPLE_FLOAT and SIMPLE_DOUBLE datatypes are alike their base types, except for their NOT NULL constraint.

• SIMPLE_INTEGER:  When the value will never be NULL and overflow checking is unnecessary.
• SIMPLE_FLOAT: When the value will never be NULL.
• SIMPLE_DOUBLE: When the value will never be NULL.

The SIMPLE_FLOAT and SIMPLE_DOUBLE has better performance than BINARY_FLOAT and BINARY_DOUBLE.

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type and differs from it in its overflow semantics. It has the same range as PLS_INTEGER i.e. -2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint.

The DBMS_TRACE package has the interface to trace PL/SQL functions, procedures, and exceptions. The package provides subprograms to start and stop PL/SQL tracing in a session.

The following are the constants of the DBMS_TRACE package:

• TRACE_ALL_CALLS: Traces calls or returns
• TRACE_ALL_EXCEPTIONS: Traces exceptions
• TRACE_ENABLED_EXCEPTIONS: Traces exceptions and handlers
• TRACE_LIMIT: Save only the last few records.
• TRACE_ALL_SQL: Traces SQL statements
• TRACE_ENABLED_SQL: Traces SQL statements at PL/SQL level.
• TRACE_ALL_LINES: Traces each line
• TRACE_PAUSE: Pauses trancing
• TRACE_RESUME: Resume tracing
• TRACE_STOP: Stops tracing

The access operator (.) is used in PL/SQL to access the attributes and methods of the object using the instance name and the (.). The example shows the usage:

No, stored procedure in PL/SQL does not return a value. Stored Procedure call using CALL keyword.

Here are some of the predefined exceptions in PL/SQL:

1. PROGRAM_ERROR
2. TIMEOUT _ON_RESOURCE
3. INVALID_CURSOR
4. LOGON_DENIED
5. ZERO_DIVIDE
6. STORAGE_ERROR
7. NO_DATA_FOUND
8. TOO_MANY_ROWS
9. CURSOR_ALREADY_OPEN
10. INVALID_NUMBER

The EXIT statement is used to immediately terminate a loop. With this, the program control resumes at the next statement following the loop. It can also be used in a nested loop to stop the execution of the innermost loop.

When the EXIT statement is encountered inside a loop, the loop is immediately terminated and the program control resumes at the next statement following the loop.

The following is the syntax:

EXIT;

The example code:

DECLARE
  val number(2) := 2;
BEGIN
  WHILE val < 10 LOOP
     dbms_output.put_line ('value = ' || val);
     val := val + 1;
     IF val = 5 THEN
        EXIT;
     END IF;
  END LOOP;
END;
/

The output:

value = 2
value = 3
value = 4

Once a value is declared in PL/SQL, you cannot change it. The CONSTANT keyword is used in PL/SQL to declare a constant.

The following is a query to find the area of circle, wherein we have set the constant pi as:

pi constant number := 3.141592654;

Above, we have declared a constant.

Let us see the complete code:

DECLARE
  pi constant number := 3.141592654;
  radius number(5,2);  
  d number(5,2);  
  area number (10, 2);
BEGIN  
  radius := 3;  
  d:= radius * 2;  
  area := pi * radius * radius;
  dbms_output.put_line('Radius = ' || radius);
  dbms_output.put_line('Diameter = ' || d);
  dbms_output.put_line('Area = ' || area);
END;
/

The output displays the area of circle:

Radius = 3
Diameter = 6
Area = 28.27
Statement processed.
0.01 seconds

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).

Syntax

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

CREATE [OR REPLACE ] TRIGGER name_of_trigger  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE
  Declaration-statements
BEGIN  
  Executable-statements
EXCEPTION
  Exception-handling-statements
END;

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>:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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:

CREATE OR REPLACE TRIGGER budget_diff
BEFORE DELETE OR INSERT OR UPDATE ON deptbudget
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
  budget_diff number;
BEGIN
  budget_diff := :NEW.budget  - :OLD.budget;
  dbms_output.put_line('Old Budget Assigned =' || :OLD.budget);
  dbms_output.put_line('New Budget Assigned = ' || :NEW.budget);
  dbms_output.put_line('Differences in Budget = ' || budget);
END;
/

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

BEFORE DELETE OR INSERT OR UPDATE ON deptbudget

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:

UPDATE budget
SET  = budget + 4500
WHERE id = 4;

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

Old Budget Assigned = 76878
New Budget Assigned = 81378
Differences in Budget = 4500

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. Here, d_deptbudget is the cursor name:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.

The following is the syntax to create explicit cursor:

CURSOR name_of_cursor IS select_statement;

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,

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

CURSOR d_deptbudget IS
  SELECT deptid, deptname, deptloc, budget FROM deptbudget;

Above, “d_deptbudget” is our 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

OPEN d_deptbudget;

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:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

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

CLOSE d_deptbudget;

Let us now see an example:

DECLARE  
  d_deptid deptbudget.deptid%type;  
  d_deptname deptbudget.deptname%type;    
  CURSOR d_deptbudget IS  
     SELECT deptid, deptname FROM deptbudget;
BEGIN  
  OPEN d_deptbudget;  
  LOOP  
     FETCH d_deptbudget into d_deptid, d_deptname;  
     EXIT WHEN d_deptbudget%notfound;  
     dbms_output.put_line(d_deptid || ' ' || d_deptname);  
  END LOOP;  
  CLOSE d_deptbudget;  
END;  
/  

The output display department id and name:

1 Finance
2 Marketing
3 Operations
4 Technical
5 Accounting

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

There is a difference between an executed SQL statement and a committed transaction. On successful execution of SQL statement, if transaction containing the statement is not committed, then it can be rolled back and the changes made by the statement can be undone. Here, committed means permanent in the database. However, rolled back means the changes can be undone.

Beginning with transactions, let us understand its concepts:

• COMMIT

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

• ROLLBACK

The Rollback undone all the changes since the last COMMIT or ROLLBACK.

• ROLLBACK TO SAVEPOINT

Undone all the changes made since the specified savepoint was made.

• SAVEPOINT

The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs. A transaction begins when the first SQL statement is encountered after connecting to the database.

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

COMMIT;

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:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (1, 'Finance', 'NORTH');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (2, 'Marketing', 'EAST');
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC)
VALUES (3, 'Operations', 'WEST');
COMMIT;

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

The syntax:

ROLLBACK [TO SAVEPOINT < name_of_savepoint>];

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.

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:

SAVEPOINT name_of_savepoint;

Let us see an example of SAVEPOINT:

INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET)
VALUES (1, 'Finance', 'NORTH', 20000);
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET)
VALUES (2, 'Marketing', 'EAST', 30000);
SAVEPOINT s1;
UPDATE DEPTBUDGET
SET BUDGET = BUDGET - 4500;
ROLLBACK TO s1;
UPDATE DEPTBUDGET
SET BUDGET = BUDGET - 4500;  
WHERE DEPTNAME = 'MARKETING';
COMMIT;

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

The LIKE operator is a comparison operator in PL/SQL through which compares a character, string, or CLOB value to a pattern. The operator returns TRUE if the value matches the pattern and FALSE if it does not.

For an example, we will take a procedure here and check for values:

DECLARE
PROCEDURE compare (myvalue  varchar2, pattern varchar2 ) is
BEGIN
  IF myvalue LIKE pattern THEN
     dbms_output.put_line ('True');
  ELSE
     dbms_output.put_line ('False');
  END IF;
END;  
BEGIN
  compare('John', 'J%n');
  compare('Jack', 'Ja_k');
  compare('Amit', 'Am%');
  compare('Sac', 'Sac_');
END;
/

The output displays the following:

True
True
True
False
Statement processed.

0.00 seconds

Above we have used the following wildcards:

For example, above we used J%n for “John”, the output was true, since the % wildcard matches any string of any length.

The following are the methods available in PL/SQL to trim characters:

The following is how you can use LTRIM() in PL/SQL to trim characters from the left:

DECLARE
  cars varchar2(30) := '......Bentley.....';
BEGIN
 dbms_output.put_line(LTRIM(cars,'.'));
END;
/

The output:

Bentley.....

The following is how you can use RTRIM() in PL/SQL to trim characters from the right:

DECLARE
  cars varchar2(30) := '......Bentley.....';
BEGIN
 dbms_output.put_line(RTRIM(cars,'.'));
END;
/

The output:

......Bentley

The following is how you can use TRIM() in PL/SQL to trim characters from the right and left:

DECLARE
  cars varchar2(30) := '......Bentley.....';
BEGIN
 dbms_output.put_line(TRIM(cars,'.'));
END;
/

The output:

Bentley

When a cursor is closed, then the allocate memory is released.  To close a cursor, you need to use the CLOSE clause with the name of cursor you want to close.

CLOSE name_of_cursor;

Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.

The following is the syntax to create explicit cursor:

CURSOR name_of_cursor IS select_statement;

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,

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

CURSOR d_deptbudget IS
  SELECT deptid, deptname, deptloc, budget FROM deptbudget;

Above, “d_deptbudget” is our 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

OPEN d_deptbudget;

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:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;

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

CLOSE d_deptbudget;

Let us now see an example:

DECLARE  
  d_deptid deptbudget.deptid%type;  
  d_deptname deptbudget.deptname%type;    
  CURSOR d_deptbudget IS  
     SELECT deptid, deptname FROM deptbudget;
BEGIN  
  OPEN d_deptbudget;  
  LOOP  
     FETCH d_deptbudget into d_deptid, d_deptname;  
     EXIT WHEN d_deptbudget%notfound;  
     dbms_output.put_line(d_deptid || ' ' || d_deptname);  
  END LOOP;  
  CLOSE d_deptbudget;  
END;  
/  

The output display department id and name:

1 Finance
2 Marketing
3 Operations
4 Technical
5 Accounting

PL/SQL Block Structures and it executes as a whole block.

The block structure can be understood with the concept of subprograms in PL/SQL. A block structure has the following parts:

• Declarative

It contains declarations of cursors, constants, variables, exceptions, etc. Variables are declared here.

• Executable

Had statements that perform actions. It must have atleast one statement.

• Exception Handling

The code that handles run-time errors. It use EXCEPTION keyword. 

The following is the syntax to create a subprogram (procedure):

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a subprogram (procedure):

CREATE OR REPLACE PROCEDURE one
AS
BEGIN
  dbms_output.put_line('Welcome!');
END;
/

The output displays the procedure created successfully:

Procedure created.

Expect to come across this popular question in advanced PL SQL interview questions.  

With NOT INSTANTIABLE clause in PL/SQL, you can declare an abstract object. To work with it, you must create a subtype of such objects to use its functionalities.

It's no surprise that this one pops up often in PL SQL interview questions for experienced.  

The HEXTORAW in PL/SQL was introduced to convert a hexadecimal value into a raw value. An example here displays the same:

HEXTORAW('7E')

The output:

7E

One of the most frequently posed PL SQL developer interview questions, be ready for it.  

The INVALID_NUMBER exception is raised when the conversion of a character string into a number fails because the string does not represent a valid number.

The existing trigger is recreated by the optional argument [OR REPLACE] in a CREATE TRIGGER command.

A staple in PL SQL interview questions for experienced, be prepared to answer this one.  

Each subprogram in PL/SQL has a name and a list of parameters. With that, it has the following parts:

• Declaration
• Executable
• Exception Handling

Let us now see them one by one:

• Declaration

It contains declarations of cursors, constants, variables, exceptions, etc.

• Executable

Had statements that perform actions.

• Exception Handling

The code that handles run-time errors.

The following is the syntax to create a subprogram (procedure):

CREATE [OR REPLACE] PROCEDURE name_of_procedure
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
 < procedure_body >
END name_of_procedure;

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

With PL/SQL, you can easily pass a record as a subprogram parameter in the same way pass any other variable.

The following is an example:

DECLARE
TYPE laptop IS RECORD
(brand varchar(50),
  RAM  number,
  SNO  number
);
lp1 laptop;
lp2 laptop;
lp3 laptop;
PROCEDURE myDevice(lpt laptop) IS
BEGIN
 -- Laptop record
  dbms_output.put_line('Laptop Brand = '|| lpt.brand);
  dbms_output.put_line('Laptop RAM = '|| lpt.RAM);
  dbms_output.put_line('Laptop SNO = ' || lpt.SNO);
END;
BEGIN
  -- Laptop 1 specification
  lp1.brand:= 'Dell';
  lp1.RAM:= 4;  
  lp1.SNO:=  87667;
  -- Laptop 2 specification
  lp2.brand:= 'Lenevo';
  lp2.RAM:= 4;  
  lp2.SNO:=  47656;
  -- Laptop 3 specification
  lp3.brand:= 'HP';
  lp3.RAM:= 8;  
  lp3.SNO:=  98989;
  myDevice(lp1);
  myDevice(lp2);
  myDevice(lp3);
END;
/

The output:

Laptop Brand = Dell
Laptop RAM = 4
Laptop SNO = 87667
Laptop Brand = Lenevo
Laptop RAM = 4
Laptop SNO = 47656
Laptop Brand = HP
Laptop RAM = 8
Laptop SNO = 98989

Within a package, you can refer the objects and subprograms like this:

• name_of_package.object_name
• name_of_package.subprogram_name

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>:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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

DECLARE
  d_id deptbudget.id%type := &dd_id;
  d_name deptbudget.deptname%type;
  d_loc deptbudget.deptloc%type;  
  my_exception  EXCEPTION;
BEGIN
  IF d_id <= 0 THEN
     RAISE my_exception;
  ELSE
     SELECT  deptname, deptloc INTO d_name, d_loc
     FROM deptbudget
     WHERE deptid = d_id;
     DBMS_OUTPUT.PUT_LINE ('Name: '||  d_name);
     DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc);
  END IF;
EXCEPTION
  WHEN my_exception THEN
     dbms_output.put_line('ID is always greater than zero!');
  WHEN no_data_found THEN
     dbms_output.put_line('No such department in the company!');
  WHEN others THEN
     dbms_output.put_line('Error!');  
END;
/

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:

ID is always greater than zero!

The Large Object 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.

The %ROWCOUNT cursor attribute 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:

sql%attribute_name

Above, set any attribute name under “attribute_name”.

Let us consider the following table for our example:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET     |
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      95979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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:

DECLARE  
  rows_count number(2);
BEGIN
  UPDATE deptbudget
  SET budget = budget + 3000;
  IF sql%notfound THEN
     dbms_output.put_line('Nothing selected!');
  ELSIF sql%found THEN
     rows_count := sql%rowcount;
     dbms_output.put_line( rows_count || ' rows affected! ');
  END IF;  
END;
/   

The output:

5 rows affected!

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

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 59678      |
|  2 | Marketing     | EAST |      90687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    79878 |
|  5 | Accounting   | NORTHWEST| 89767      |
+----------+------------------+------------------+--------------+

To find the factorial of a number, the following is the code:

DECLARE
a number :=1;    
n number := 5;    
BEGIN
while n > 0 loop   
a:=n*a;         
n:=n-1;            
end loop;          
dbms_output.put_line(a);   
end;

The output:

120

To include single quotes in a string literal, you need to use it like:

“That wasn’’t a big blow to the team”

Above, we have set two single quotes to display one single quote.

Let us see an example.

Our string is:

It's my life

The following is the code to include single quotes:

DECLARE
  a varchar2(15):= 'It''s my life!';
BEGIN  
  dbms_output.put_line(a);
END;
/

The output:

It's my life!

The BETWEEN operator in PL/SQL checks whether a value lies in a specified range or not.

For example, the value a BETWEEN x AND y says that a >= x and a <= y.

Let us see an example:

DECLARE
  val number(2) := 3;
BEGIN
  IF (val between 1 and 5) THEN
     dbms_output.put_line('True');
  ELSE
     dbms_output.put_line('False');
  END IF;
END;
/

The output:

True

Let us see another example:

DECLARE 
   val number(2) := 15; 
BEGIN 
   IF (val between 1 and 10) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
END; 
/

The output:

False

The RAISE statement is used in PL/SQL to raise exceptions. Through this a user can raise exceptions explicitly.

The following is the syntax to display how to use raise exceptions with RAISE statement:

DECLARE
  exception_name EXCEPTION;
BEGIN
  IF condition THEN
     RAISE exception_name;
  END IF;
EXCEPTION
  WHEN exception_name THEN
  statement;
END;

An example can be if the ID is less than zero, the raise an exception:

IF d_id <= 0 THEN
     RAISE my_exception;

Above “my_exception” is the following we set above:

my_exception EXCEPTION;

Let us see an example wherein we are raising exceptions. The following is the table we are taking into consideration:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET|
+----------+------------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

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

DECLARE
  id deptbudget.id%type := &d_id;
  deptname deptbudget.deptname%type;
  deptloc deptbudget.deptloc%type;  
  my_exception  EXCEPTION;
BEGIN
  IF d_id <= 0 THEN
     RAISE my_exception;
  ELSE
     SELECT  deptname, deptloc INTO deptname, deptloc
     FROM deptbudget
     WHERE id = d_id;
     DBMS_OUTPUT.PUT_LINE ('Name: '||  deptname);
     DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc);
  END IF;
EXCEPTION
  WHEN my_exception THEN
     dbms_output.put_line('ID is always greater than zero!');
  WHEN no_data_found THEN
     dbms_output.put_line('No such department in the company!');
  WHEN others THEN
     dbms_output.put_line('Error!');  
END;
/

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:

ID is always greater than zero!

This question is a regular feature in PLSQL interview questions, be ready to tackle it.  

Actual parameters can be passed as Positional notation, Named notation as well as Mixed notation. In Positional Notation, the same parameters in the same order as they are declared in the procedure are specified.

The actual parameter is associated with the formal parameter in named notation using the arrow symbol ( => ) like:

x => a

The procedure call for this would be:

myFunction(x => a, y => b, z => c);

In PL/SQL, you can mix both notations in a procedure call with n mixed notation. Remember, the positional notation should precede the named notation.

myFunction(x, y, z => c);

The following is an example:

Let’s say we have below given table:

<DEPTBUDGET>
+-----------+-----------------+---------------+------------------+
| DEPTID | DEPTNAME  | DEPTLOC | BUDGET       |
+-----------+-----------------+---------------+------------------+
|  1 | Finance          | NORTH | 56678      |
|  2 | Marketing     | EAST |      87687 |
|  3 | Operations   | WEST |      98979 |
|  4 | Technical      | SOUTH |    76878 |
|  5 | Accounting   | NORTHWEST| 86767      |
+----------+------------------+------------------+--------------+

Now, we will call with Positional, mixed, and named notations:

DECLARE
 d_id NUMBER(6) := 2;
 d_budget NUMBER(6) := 500;
 PROCEDURE new_budget (id NUMBER, amount_add NUMBER) IS
   BEGIN
     UPDATE employees SET budget = budget + amount_add WHERE deptid = id;
 END new_budget ;
BEGIN
 new_budget (did, d_budget ); -- positional procedure call for actual parameters
 new_budget (amount_add => d_budget , id=> d_id); -- named parameters
 new_budget (d_id, amount_add => d_budget ); -- mixed parameters
END;

Above, you can see we have a procedure called “new_budget”. We are updating the budget here:

PROCEDURE new_budget (id NUMBER, amount_add NUMBER) IS
   BEGIN
     UPDATE employees SET budget = budget + amount_add WHERE deptid = id;
END new_budget ;

We are calling one by one with positional, mixed, and named notation:

new_budget (did, d_budget ); -- positional procedure call for actual parameters
new_budget (amount_add => d_budget , id=> d_id); -- named parameters
new_budget (d_id, amount_add => d_budget ); -- mixed parameters

The TIMESTAMP datatype stores the year, month, and day of the DATE datatype. It also allows you to store hour, minute, and second values.

Expect to come across this popular question in PLSQL interview questions for experienced.  

Yes, a %ROWCOUNT attribute returns the total number of rows returned by the FETCH command.

With PL/SQL, we can create a user-defined subtype. It already has predefined subtypes in STANDARD package.

Let us now see how to create an Integer subtype:

DECLARE
  SUBTYPE info IS varchar2(50);
  car info;
BEGIN
  car:= 'I love cars!';
  dbms_output.put_line(car);
END;
/

The output:

I love cars!

The WHEN clause has search conditions that output Boolean values in a searched CASE statement.

The following is the syntax of searched CASE that has the WHEN conditions:

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
 else_statements ]
END CASE;]

Let us see an example:

DECLARE
  points number := 100;
BEGIN
  case  
     when points = 20 then dbms_output.put_line('Rank 4');
     when points = 50 then dbms_output.put_line('Rank 3');
     when points = 75 then dbms_output.put_line('Rank 2');
     when points = 100 then dbms_output.put_line('Rank1... Topper');
     else dbms_output.put_line('No ranking!');
  end case;
END;
/

The output:

Rank1... Topper

In PL/SQL, packages are the schema objects that groups logically related PL/SQL types and variables. A Package has the following two parts:

• Package Specification

The specification has the information about the package content. It declares the types, variables, constant, cursors, etc. It excludes the code for subprograms. The objects in the specification are the public object, however, a subprogram not part of the specification is a private object.

• Package Body

The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement.

The following are the guidelines:

• You need to design and define the package specifications before the package bodies.
• Declare public cursors in package specifications and define them in package bodies.
• Assign initial values in the initialization part of the package body. Avoid this in declarations.

A must-know for anyone heading into a PLSQL interview, this question is frequently asked in PLSQL developer interview questions.  

VARRARY data structure stores a fixed-size sequential collection of elements of the same type. It has contiguous memory locations, with the lowest address as the first element and the highest address as the last element.

The CREATE TYPE statement is used to create a varray type. The following is the syntax for creating a VARRAY type within a PL/SQL block:

TYPE name_of_varray_type IS VARRAY(n) of <type_of_element>

Here,

n is the number of elements,
name_of_varray_type is a valid attribute name, and
type_of_element is the data type of the elements of the array

The following is an example:

Type marks IS VARRAY(10) OF INTEGER;

Let us see an example wherein we are displaying the appraisal points for employees according to their IDs:

DECLARE
  type points IS VARRAY(10) OF INTEGER;
  appraisal_points points;
  total integer;
BEGIN
  appraisal_points:= points(76, 79, 89, 96, 99, 86, 89, 97);
  total := appraisal_points.count;
  FOR i in 1 .. total LOOP
     dbms_output.put_line('Appraisal Points for Employee ' || i || ' = ' || appraisal_points(i));
  END LOOP;
END;
/

The output:

Appraisal Points for Employee 1 = 76
Appraisal Points for Employee 2 = 79
Appraisal Points for Employee 3 = 89
Appraisal Points for Employee 4 = 96
Appraisal Points for Employee 5 = 99
Appraisal Points for Employee 6 = 86
Appraisal Points for Employee 7 = 89
Appraisal Points for Employee 8 = 97

Sequence of characters are called strings. Just like any other language, a string can be letters, numbers, special characters, etc.

The following are the three types of strings in PL/SQL:

• Fixed-length strings

In fixed-length strings, the length is to be specified while declaring a string. The string is right-padded with spaces to the length so specified.

• Variable-length string

A maximum length up to 32,767 for the string is specified and no padding takes place.

• Character large objects (CLOBs)

CLOBs are variable-length strings that can be up to 128 terabytes.

Let us see how we can declare a String variable. We have a lot of string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB in PL/SQL.

The declaration:

DECLARE
   cars varchar2(25) := 'Bentley;

Let us see an example where we have two variables:

DECLARE
  car varchar2(15) := 'Bentley';
  device varchar2(11) := 'Laptop';
BEGIN
  dbms_output.put_line(UPPER(car));
  dbms_output.put_line(LOWER(car));
  dbms_output.put_line(device);
  dbms_output.put_line(UPPER(device));
END;
/

The output:

BENTLEY
bentley
Laptop
LAPTOP

To pad a string with zeros, you can use RPAD or LPAD function in PL/SQL. Let us see how to pad the right side of a string with zeros:

RPAD(tom, 6)

The output would have a width of 6 characters now. Since the string is of 3 characters, therefore rest of the space would be filled with 0s:

tom000

It's no surprise that this one pops up often in advanced PL SQL interview questions.  

The VSIZE() function returns the number of bytes in the internal representation of an expression. An example:

VSIZE('Selection Day')

The output:

13

The PL/SQL programming constructs include:

1. Embedded SQL support
2. Stored procedures
3. Packages
4. Triggers
5. Exception Handling
6. Cursors