PL/SQL
Interview Questions
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. We can also say that
PL/SQL tables are a way to providing 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
No comments:
Post a Comment