Google News
logo
PL/SQL Interview Questions
PL/SQL stands for Procedural Language extension of Structured Query Language (SQL). It is a block-structured language having logical blocks which are made up of three sub-blocks i.e. a declarative part, an executable part and an exception building part. PL/SQL is integrated with Oracle and the functionalities of PL/SQL extend after each release of Oracle database.
 
It includes procedural language elements like conditions and loops and allows declaration of constants and variables, procedures and functions. It also helps the users to develop complex database applications using control structures, procedures, modules, etc. PL/SQL is not limited by any case-sensitive letters so you are free to use lower case letters or upper case letters.
Following are the features of PL/SQL :
 
* PL/SQL provides the feature of decision making, looping, and branching by making use of its procedural nature.

* Multiple queries can be processed in one block by making use of a single command using PL/SQL.

* The PL/SQL code can be reused by applications as they can be grouped and stored in databases as PL/SQL units like functions, procedures, packages, triggers, and types.

* PL/SQL supports exception handling by making use of an exception handling block.

* Along with exception handling, PL/SQL also supports error checking and validation of data before data manipulation.

* Applications developed using PL/SQL are portable across computer hardware or operating system where there is an Oracle engine.
PL/SQL tables are nothing but objects of type tables that are modeled as database tables. They are a way to provide arrays that are nothing but temporary tables in memory for faster processing.

These tables are useful for moving bulk data thereby simplifying the process.
The basic structure of PL/SQL follows the BLOCK structure. Each PL/SQL code comprises SQL and PL/SQL statement that constitutes a PL/SQL block.

Each PL/SQL block consists of 3 sections :

* The optional Declaration Section
* The mandatory Execution Section
* The optional Exception handling Section

[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements (optional)
END;​

 

A list of some notable characteristics :
 
* PL/SQL is a block-structured language.
* It is portable to all environments that support Oracle.
* PL/SQL is integrated with the Oracle data dictionary.
* Stored procedures help better sharing of application.
There are various kinds of data types are available in PL/SQL which are :
 
Scalar data types : A scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, BOOLEAN are some examples of scalar data types.

Composite data types : A composite data type is made up of different data types that are easy to update and have internal components that can be utilized and modified together. For instance, RECORD, TABLE, VARRAY, and so on.

Reference data types : A reference data type stores pointers, which are values that relate to other programs or data elements. REF, CURSOR is an example of a reference data type.

Large object data type : A large object data type stores locators, which define the location of large items stored out of line (such as video clips, graphic images, and so on). BLOB, BFILE, CLOB, NCLOB, and others are examples of large object data types.
* A PL/SQL cursor is nothing but a pointer to an area of memory having SQL statements and the information of statement processing. This memory area is called a context area. This special area makes use of a special feature called cursor for the purpose of retrieving and processing more than one row.
 
* In short, the cursor selects multiple rows from the database and these selected rows are individually processed within a program.

View More : PL/SQL cursors
 
We use this clause while referencing the current row from an explicit cursor. This clause allows applying updates and deletion of the row currently under consideration without explicitly referencing the row ID.

Syntax : UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name
Function : The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
 
Procedure : A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
 
Package : A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
% ROWTYPE is used when a query returns an entire row of a table or view.
 
TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.


Ex: . TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
 
e_rec smp %ROWTYPE

Cursor c1 is select smpno,dept from smp;
 
e_rec c1 %ROWTYPE