1. What is PL/SQL? Explain about it.

PL / SQL:-
 PL/SQL stands for (Procedural Language / Structure Query Languages).
 It is an extension of SQL.
 It was developed by Oracle Corporation in the early 1990's to increase the
functionality of SQL.
Problems in SQL (or) PL/SQL uses:-
 SQL does not support looping and condition statements.
 In SQL we cannot execute more than one statement at a time.
 SQL always gives system defined error message, when user performs any wrong
transaction.
 SQL does not support the code reusability, it means we need to write insert, delete,
update, select command each and every time.
 SQL does not support procedure language features such as code reusability and
modularity and some other features of oops.
 To overcome all the above limitation of sql we use pl/sql.

The PL/SQL Engine

 Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL
language code can be stored in the client system (client-side) or in the database
(server-side).

2. Explain about short coming in sql.

Or
Explain about pl/sql features.

PL / SQL:-
 PL/SQL stands for (Procedural Language / Structure Query Language).
 It is an extension of SQL.
 It was developed by Oracle Corporation in the early 1990's to increase the
functionality of SQL.
PL/SQL Features:-

 It have number of advantage over sql like it have error handling mechanism, reduce
network traffic, better performance, highly secure, code re-usability.
High Secured: PL/SQL is high secured transaction processing language.

Code Re-usability: It support code re-usability, it means no need to write code again and
again same like SQL.

Looping statements: PL/SQL supports several looping statements like for loop, while
loop..etc

Portable application: Applications are written in PL/SQL are portable in any available
Operating system.

Conditional Statements: PL/SQL supports several conditional statements like

1. if-then
2. if-then-else
3. nested-if –then-else
4. if-then-elsif
Reduce Network Traffic: oracle engine processes multiple SQL statements simultaneously
as a single block, due to this reducing network traffic.

Procedural Language Capability: It consists of procedural language constructs such as

conditional statements (if...else) and loops statements (for loop)

Better performance: oracle engine processes multiple SQL statements simultaneously as a

single block, due to this reducing network traffic.

Declare variable: It give you control to declare variable and access them within the block.
Flexible: Pl/SQL programs are very flexible which means these programs can use any
operating environments and we can update the functionality.

Better performance: when compared to SQL, PL/SQL programs were give better
performance.

Error Handling: when any error are occurred then it return user friendly error message.

3. Explain about basic structure of PL/SQL?

Structure of pl-sql program:-

PL/SQL program is very easy to write and execute. PL/SQL program contains mainly four
section, they are given below;

 Declare section
 Begin section
 Exception section
 End section
Syntax

Declare section: In this section user declare all the variables, cursor declaration etc.

Begin section: In this section, you need to declare all the executable statements, logical
statements etc.

Exception section: In this section you need to write all the exception handling statements,
which is used to convert system defined error message into user friendly error message.

End section: In this section represent end of the pl-sql program or block.

Example
4. Explain about PL/SQL - Operators Precedence.

Operator precedence determines the grouping of terms in an expression. This affects

how an expression is evaluated. Certain operators have higher precedence than others; for
example, the multiplication operator has higher precedence than the addition operator.

For example, x = 7 + 3 * 2; here, x is assigned 13, not 20 because operator * has higher
precedence than +, so it first gets multiplied with 3*2 and then adds into 7.

Here, operators with the highest precedence appear at the top of the table, those with the
lowest appear at the bottom. Within an expression, higher precedence operators will be
evaluated first.

Operator Operation

*, / multiplication, division

+, -, || addition, subtraction, concatenation

>,>=,<,<=,<>, != comparisons

NOT logical negation

AND conjunction

OR inclusion

5. Explain about PL/SQL operators.

Operator is a symbol. It is used to perform particular operation based on the operands.

Several Operators are supported in PL/SQL.
These are supported in SQL, except assignment operator.
In place of assignment operator := are acting as a assignment.
Several operators in PL/SQL:-

 Arithmetic Operators.
 Comparison (or) Relational Operators.
 Logical Operators.
 Miscellaneous Operators.
Arithmetic Operators
Operator Name Example (int a=8, b=3)

+ Addition a+b=11

- Subtraction a-b=5

* Multiplication a*b=24

/ Division a/b=2

% Modulas (Remainder) a%4=0

Relational Operators
Example: a=10,
Operators Description
b=20

Check the left operands value are not equal to

!= a!=b=True
right operands, if yes return true.

Check the left operand value is greater than right

> a>b=False
Operand, if yes condition becomes true

Check the left operand value is less than right

< a<b=True
Operand, if yes condition becomes true

Check the left operand value is less than or equal

<= a<=b=True
to right Operand, if yes condition becomes true

Check the left operand value is greater than or

>= equal to right Operand, if yes condition becomes a>b=False
true
Logical Operators

Operator Description

And are use to combined two or more than two condition together.
1 AND
If both the condition is true then return true.

OR are use to combined two or more than two condition together,

2 OR In this case you need at least one condition is true then return
result.

3 Not NOT operator reverse the meaning of any logical operator

Truth table of Logical Operator

C1 AND
Condition(c1) Condition(C2) C1 OR C2 NOT C1 NOT C2
C2

T T T T F F

T F F T F T

F T F T T F

F F F F T T

Miscellaneous Operator
Between Not between In

Not in Like Exists

Not exists Is null Is not null

Any All Some

Union Union all Intersect

6. Explain about different control structures in pl/sql.

Conditional Statements are depending on the condition block need to be executed or

not which is decided by condition.
PL/SQL IF statement check condition and transfer the execution flow on that matched block
depending on a condition. IF statement execute or skip a sequence of one or more statements.
PL/SQL IF statement four different type,

 IF THEN Statement
 IF THEN ELSE Statement
 IF THEN ELSIF Statement
 Nested IF THEN ELSE Statement

IF THEN Statement:-
If the condition is "true" statement block will be executed, if condition is "false" then
statement block will not be executed.

Syntax

if (condition) then

statements;

end if;
Example
IF THEN ELSE Statement:-

If the condition is "true" then if statement block will be executed, if condition is "false" then
else statement block will not be executed.

Syntax

if (condition) then
statements;

else

statements;

end if;

Example
IF THEN ELSIF Statement :-

If the condition is "true" then statement-1 block will be executed, if condition is "false" then
again if condition is checked if it is true then statement-2is executed. In the same the process
is continues. If all the conditions are false then else block is executed.

syntax format:

IF ( condition-1 ) THEN
statement-1;
ELSIF ( condition-2 ) THEN
statement-2;
ELSIF ( condition-3 ) THEN
statement-3;
ELSE
statement;
END IF;
EXAMPLE:-

Nested IF THEN ELSE Statement :-

When a series of decisions are involved, we may have to use more than one if...else statement
in nested form.

Syntax:
Example:-

PL/SQL Case Statement:-

CASE statement can execute a sequence of statements based on the choice. A choice can be
anything such as variable, function.

Syntax for the CASE Statement:

CASE [expression]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
. . ..
WHEN condition_n THEN result_n
ELSE result
END
Example:
7. Explain about iterative or loop control statements in pl/sql.

PL/SQL Looping Statement

Loops are used to repeat the execution of one or more statements for specified number of
times. These are also known as iterative control statements.
Syntax for a basic loop:
LOOP
Sequence of statements;
END LOOP;
Types of PL/SQL Loops
There are 3 types of PL/SQL Loops.
 Basic Loop / Exit Loop
 While Loop
 For Loop
PL/SQL exit loop is used when a set of statements is to be executed at least once before the
termination of the loop. There must be an EXIT condition specified in the loop, otherwise the
loop will get into an infinite number of iterations. After the occurrence of EXIT condition,
the process exits the loop.
Syntax of basic loop:
LOOP
Sequence of statements;
END LOOP;
Syntax of exit loop:
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
Program: write a pl/sql program to print 1 to 10 numbers.
Must be follow below steps;
 Initialize a variable before the loop body.
 Increment the variable in the loop body.
 Use exit when statement to exit from loop body. If you use exit without when
condition statement will be execute only once.
While loop:-
 While loop is used when a set of statements has to be executed as long as a condition
is true, the While loop is used. The condition is decided at the beginning of each
iteration and continues until the condition becomes false.

Syntax of while loop:

WHILE <condition>
LOOP statements;
END LOOP;
Program:-

Write a pl/sql program to print 1 to 10 numbers using while loop

for loop:-

PL/SQL for loop is used when you want to execute a set of statements for a number of
times. The loop is iterated between the start and end integer values. The counter is always
incremented by 1 and once the counter reaches the value of end integer, the loop ends.

Syntax of for loop:

FOR counter IN initial_value .. final_value LOOP

LOOP statements;

END LOOP;

Example:-
Write a pl/sql program to print 1 to 10 numbers using for loop
8. What is a cursor? How to create a cursor in pl/sql.
Cursor:-
 A Cursor is a temporary work area created in the system memory .
 It is used to fetch more than one record at a time from existing table.
 This temporary work area is used to manipulate the store the data.
 A cursor can hold more than one row, but can process only one row at a time. The set
of rows the cursor holds is called the active set.
Cursors are divided into two types;
 Implicit cursor
 Explicit cursor
Implicit cursor:-

 The implicit cursors are automatically generated by Oracle while an SQL statement is
executed.
 These are created by default to process the statements when DML statements like
INSERT, UPDATE, and DELETE etc. are executed.
 Oracle provides some attributes known as Implicit cursors attributes to check the
status of DML operations.
 Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT.

Example:-
Explicit cursors:-
This type of cursor is performed by the user manually or programmatically those
cursor can be called as explicit cursor.
Steps to perform cursor:-

Steps Syntax

Declare the cursor open cursor_name;

Open the cursor open cursor_name;

Fetch the record from the cursor fetch cursor_name into variables;

Close the cursor close cursor_name;

9. What is procedure in pl/sql? Explain about it.
 A PL/SQL procedure is a named block that performs one or more actions.
 A procedure is a group of PL/SQL statements that you can call by name.
 A pl-sql Procedure may or may not return any value.
 Procedures in pl-sql have two sections.

Header of the procedure:

 Declaration of procedure always start with a keyword create ends with last variable
parameters.

Body of the procedure:

 The body contains a declaration section, execution section and exception section
similar to a general PL/SQL block.

In PL/SQL, we can pass parameters to procedures and functions in three ways.

1) IN: These types of parameters are used to send values to stored procedures.
2) OUT: These types of parameters are used to get values from stored procedures.
3) IN OUT: These types of parameters are used to send values and get values from stored
procedures.
NOTE: If a parameter is not defined then by default it is an IN type parameter.

Syntax:-
 create or replace procedure proc_name [list of parameters]
is
Declaration section
begin
Execution section
Exception
Exception section
end;
Example of procedure

10. What is a function in pl/sql? Explain about it.

A PL/SQL Function is a self control block which is used to perform some specific task.

Difference between function and procedure:-

The main difference between a procedure and a function is, a function must always return a
value, but a procedure may or may not return a value.
Syntax:-
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Example of function to add two number:-
11. What is a package? Explain about pl/sql packages.

 PL/SQL package is a group of related functions, procedures, cursors, etc.

 PL/SQL package is like a library once written, stored in the Oracle database and can
be used by many applications.
A PL/SQL package has two parts:
 Package specification.
 Package body.
A package body contains the code that implements the package specification.

Package specification Syntax:-

 Pac
kage body Syntax:-

Syntax for Drop package

drop package package_name;
12. What is an exception handling? Explain it.

PL/SQL Exception Handling:-

 An exception is an event, which occurs during the execution of a program, that stop’s
the normal flow of the program execution.
 An error occurs during the program execution is called Exception in PL/SQL.
 PL/SQL facilitates programmers to catch such conditions using exception block in the
program and an appropriate action is taken against the error condition.
There are two type of exceptions:
 System-defined Exceptions.
 User-defined Exceptions.
System defined Exception
An exception which is defined by the system along with the software. Those
exception can be called as system defined exception.

User defined Exception

An exception which is defined by the user manually or programatically. Those

exception are called as user defined exception.

Example
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
........
WHEN others THEN
 exception3-handling-statements
END;
Example Handling exception when number is divided by zero.

13. What is a trigger? Explain about triggers.

 Triggers are stored programs, which are automatically executed or fired when some
event occurs.
 Trigger is a pl/sql block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table
Advantages of Triggers:-

 Generating some derived column values automatically

 Event logging and storing information on table access Auditing
 Imposing security authorizations
 To avoid invalid transactions
 To generate the resulting data automatically.
Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
 ON table_name
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
Explanation
Syntax Description:-
CREATE [OR REPLACE] TRIGGER trigger_name : Create a trigger with the given name. If
already have overwrite the existing trigger with defined same name.
BEFORE | AFTER : Indicates when the trigger get fire. BEFORE trigger execute before
when statement execute before. AFTER trigger execute after the statement execute.
[INSERT, UPDATE, DELETE [COLUMN NAME..] : Determines the performing trigger
event. You can define more then one triggering event separated by OR keyword.
ON table_name : Define the table name to performing trigger event.
FOR EACH ROW | FOR EACH STATEMENT : Trigger must fire when each row gets
Affected (ROW Trigger). and fire only once when the entire sql statement is execute
(STATEMENT Trigger).
WHEN Condition : Optional. Use only for row level trigger. Trigger fire when specified
condition is satisfy.
Example:- Write a trigger to stop delete operation.

Explanation
 In the above example raise_application_error is a predefined package which contains
2 parameters that is error number and error message.
 Error number is the range between -40000 to -20999
 Parameter represent error message which can give maximum 1048 character.

14. What is a trigger? Explain types of triggers.

 Triggers are stored programs, which are automatically executed or fired when some
event occurs.
 Trigger is a pl/sql block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table
Trigger types are:-
  Based on the way it executes statements triggers are of two types
1. Statement level triggers.
2. Row level triggers.
A trigger fires for three actions
1. Insert
2. Delete
[Link]
And the trigger can the fired
1. Before action.
2. After action.
Inserting trigger:-

-This trigger execute BEFORE to convert ename field lowercase to uppercase.

update on trigger:- the following trigger will display salary changes on the emp table.