PL/SQL is an advance version of SQL.
There are given top list of PL/SQL interview questions with answer.
1) What is PL/SQL?
PL/SQL stands for procedural language
extension to SQL. It supports procedural features of programming language and
SQL both. It was developed by Oracle Corporation in early of 90's to enhance
the capabilities of SQL.
2) What is PL/SQL table? Why it is used?
Objects of type tables are called
PL/SQL tables that are modeled as database table. They are a type of COLLECTION. They were initially introduced in pl/sql. Nested tables are Varrays were introduced only after Oracle 8i.
We can also say that PL/SQL
tables are a way to provide arrays. Arrays are like temporary tables in
memory that are processed very quickly. PL/SQL tables are used to move bulk
data. They simplifies moving collections of data.
3) What are the datatypes available in PL/SQL?
There are two types of datatypes in
PL/SQL:
1. Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
2. Composite datatypes Example are RECORD, TABLE etc.
4) What is the basic structure of PL/SQL?
PL/SQL uses BLOCK structure as its
basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which
form a PL/SQL block.
PL/SQL block contains 3 sections.
1. The Declaration Section (optional)
2. The Execution Section (mandatory)
3. The Exception handling Section (optional)
5) What is the difference between FUNCTION, PROCEDURE AND PACKAGE in
PL/SQL?
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.
6) What is exception? What are the types of exceptions?
Exception is an error handling part of
PL/SQL. There are two type of exceptions: pre_defined exception and
user_defined exception.
7) How exception is different from error?
Whenever an Error occurs Exception
arises. Error is a bug whereas exception is a warning or error condition.
8) What is the main reason behind using an index?
Faster access of data blocks in the
table.
9) What are PL/SQL exceptions? Tell me any three.
1. Too_many_rows
2. No_Data_Found
3. Value_error
4. Zero_error etc.
10) What is the maximum number of triggers, you can apply on a single
table?
12 triggers.
11) How many types of triggers exist in PL/SQL?
There are 12 types of triggers in
PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT,
UPDATE, DELETE and ALL keywords.
· BEFORE ALL ROW INSERT
· AFTER ALL ROW INSERT
· BEFORE INSERT
· AFTER INSERT etc.
12) What is stored Procedure?
A stored procedure is a sequence of
statement or a named PL/SQL block which performs one or more specific
functions. It is similar to a procedure in other programming languages. It is
stored in the database and can be repeatedly executed. It is stored as schema
object. It can be nested, invoked and parameterized.
13) How to execute a stored procedure?
There are two way to execute a stored
procedure.
From the SQL prompt, write EXECUTE or
EXEC followed by procedure_name.
1. EXECUTE or [EXEC] procedure_name;
Simply use the procedure name
1. procedure_name;
14) What are the advantages of stored procedure?
Modularity, extensibility, reusability,
Maintainability and one time compilation.
15) What are the cursor attributes used in PL/SQL?
%ISOPEN: it checks whether the cursor is open or not.
%ROWCOUNT: returns the number of rows affected by DML operations:
INSERT,DELETE,UPDATE,SELECT.
%FOUND: it checks whether cursor has fetched any row. If yes - TRUE.
%NOTFOUND: it checks whether cursor has fetched any row. If no - TRUE.
16) What is consistency?
Consistency simply means that each user
sees the consistent view of the data.
Consider an example: there are two
users A and B. A transfers money to B's account. Here the changes are updated
in A's account (debit) but until it will be updated to B's account (credit),
till then other users can't see the debit of A's account. After the debit of A
and credit of B, one can see the updates. That?s consistency.
17) What is cursor and why it is required?
A cursor is a temporary
work area created in a system memory when an SQL statement is executed.
A cursor contains information on a
select statement and the row of data accessed by it. This temporary work area
stores the data retrieved from the database and manipulate this data. A cursor
can hold more than one row, but can process only one row at a time. Cursor are
required to process rows individually for queries.
18) How many types of cursors are available in PL/SQL?
There are two types of cursors in
PL/SQL.
1. Implicit cursor, and
2. explicit cursor
