Name:Pravish Dwivedi Roll no :18

PLSQL PRACTICALS
PRACTICAL 1
Description:
Procedure

A procedure begins with a header that specifies its name and an optional parameter list .Each parameter can
be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a parameter can be read from or
write to.

IN

An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you cannot change
its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter
explicitly, Oracle will use the IN mode.

OUT

An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the
calling program. Note that a procedure ignores the value that you supply for an OUT parameter.

INOUT

An INOUT parameter is both readable and writable. The procedure can read and modify it.

Note that OR REPLACE option allows you to overwrite the current procedure with the new code.

Syntax :-

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)

IS

[declaration statements]

BEGIN

[execution statements]

EXCEPTION

[exception handler]

END [procedure_name ];

1
Name:Pravish Dwivedi Roll no :18

1) Write a plsql Procedures for generating calculator.

Code:

2
Name:Pravish Dwivedi Roll no :18

Output:

pravish

3
Name:Pravish Dwivedi Roll no :18
2) Write a plsql Procedures for generating LCM of number.
Code:

Output:

pravish

4
Name:Pravish Dwivedi Roll no :18
3) Write pl SQL Procedures for inserting some values in emp table.
Code:

Output:

pravish

4) Write plsql Procedures for updating value in emp table.

Code:

Output:

pravish

5
Name:Pravish Dwivedi Roll no :18
5) Create function for adding 2 numbers.
Code:

Output:

pravish

6) Create pl SQL Procedures for creating multiplication table.

Code:

Output:

6
Name:Pravish Dwivedi Roll no :18

pravish

7) Create function for multiplication table.

Code:

Output:

7
Name:Pravish Dwivedi Roll no :18

pravish

8
Name:Pravish Dwivedi Roll no :18
PRACTICAL 2
Description:
Function : A function consists of a header and body. The function header has the function name and
a RETURN clause that specifies the datatype of the returned value. Each parameter of the function
can be either in the IN, OUT, or INOUT mode. The function body is the same as the procedure body
which has three sections: declarative section, executable section, and exception-handling section.

The declarative section is between the IS and BEGIN keywords. It is where you declare variables,
constants, cursors, and user-defined types.

The executable section is between the BEGIN and END keywords. It is where you place the
executable statements. Unlike a procedure, you must have at least one RETURN statement in the
executable statement.

The exception-handling section is where you put the exception handler code.

Syntax:

CREATE [ORREPLACE] FUNCTION function_name (parameter_list)

RETURN return_type

IS

[declarative section]

BEGIN

[executable section]

[EXCEPTION]

[exception-handling section]

END;

1) Write function to find course name according to the course no

passed in parameter.
Code:

9
Name:Pravish Dwivedi Roll no :18

Output:

pravish

2) Create a function to get total balance from account table.

Code:

Output:

10
Name:Pravish Dwivedi Roll no :18

pravish

3) Create a function to get address of particular id or name.

Code:

Output:

pravish

4) Create a function to check whether given string is palindrome or

not.
Code:

11
Name:Pravish Dwivedi Roll no :18

Output:

pravish

5) create function to change Fahrenheit temparature to centigrade.

Code:

12
Name:Pravish Dwivedi Roll no :18

Output:

pravish

13
Name:Pravish Dwivedi Roll no :18
PRACTICAL 3
Description:
Function : A function consists of a header and body. The function header has the function name and
a RETURN clause that specifies the datatype of the returned value. Each parameter of the function
can be either in the IN, OUT, or INOUT mode. The function body is the same as the procedure body
which has three sections: declarative section, executable section, and exception-handling section.
The declarative section is between the IS and BEGIN keywords. It is where you declare variables,
constants, cursors, and user-defined types.
The executable section is between the BEGIN and END keywords. It is where you place the
executable statements. Unlike a procedure, you must have at least one RETURN statement in the
executable statement.
The exception-handling section is where you put the exception handler code.
Syntax:
CREATE [ORREPLACE] FUNCTION function_name (parameter_list)
RETURN return_type
IS
[declarative section]
BEGIN
[executable section]
[EXCEPTION]
[exception-handling section]
END;

1) Create calculator using functions.

Code:

14
Name:Pravish Dwivedi Roll no :18

15
Name:Pravish Dwivedi Roll no :18

Output:

pravish

16
Name:Pravish Dwivedi Roll no :18
2) Create a procedure and function for printing welcome message.
Code:

Output:

pravish

17
Name:Pravish Dwivedi Roll no :18
3) Write a function to find lcm of the number.
Code:

Output:

pravish

18
Name:Pravish Dwivedi Roll no :18
4) Write a procedure and function to find prime number.
Code:

Output:

pravish

Code:

19
Name:Pravish Dwivedi Roll no :18

Output:

pravish

20
Name:Pravish Dwivedi Roll no :18
5) Write a procedure and function to update values of employee table.
Code:

Output:

pravish

Code:

Output:

21
Name:Pravish Dwivedi Roll no :18

pravish

6) Write a procedure and function to calculate simple interest on

empsal.
Code:

Output:

pravish

22
Name:Pravish Dwivedi Roll no :18
Code:

Output:

pravish

23
Name:Pravish Dwivedi Roll no :18
PRACTICAL 4
Description:
A procedure begins with a header that specifies its name and an optional parameter list.Each
parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a
parameter can be read from or write to.
Syntax :-
CREATE [ORREPLACE ]PROCEDURE procedure_name (parameter_list)
IS
[declaration statements]
BEGIN
[execution statements]
EXCEPTION
[exception handler]
END [procedure_name ];

1) Write a plsql Procedures and function for updating salary of

employee table 10%.
Code:

Output:

pravish

Code:

24
Name:Pravish Dwivedi Roll no :18

Output:
pravish

2) Write a plsql Procedures and function for executing eg of exception.

Code:

Output:

25
Name:Pravish Dwivedi Roll no :18

pravish

3) Write pl SQL Procedures and function for printing multiplication

table.
Code:

Output:

pravish

Code:

26
Name:Pravish Dwivedi Roll no :18

Output:

pravish

4) Write plsql Procedures and function for inserting values in emp

table.
Code:

27
Name:Pravish Dwivedi Roll no :18

pravish

pravish

5) Write plsql Procedures for deleting row whose name is abc.

Code:

28
Name:Pravish Dwivedi Roll no :18

6) Write plsql Procedures and function for calculating fibonacci

series.
Code:

Output:

29
Name:Pravish Dwivedi Roll no :18

pravish

Code:

Output:

30
Name:Pravish Dwivedi Roll no :18

pravish

31
Name:Pravish Dwivedi Roll no :18
PRACTICAL 5
Description:
In PL/SQL, a package is a schema object that contains definitions for a group of related
functionalities.
A package includes variables, constants, cursors, exceptions, procedures, functions, and
subprograms. It is compiled and stored in the Oracle Database. Typically , a package has a
specification and a body.
Package Specification:
A package specification is mandatory while the package body can be required or optional,
depending on the package specification.The package specification declares the public objects that
are accessible from outside the package.If a package specification whose public objects include
cursors and subprograms, then it must have a body which defines queries for the cursors and code
for the subprograms.
Syntax:
CREATE [OR REPLACE] PACKAGE <package_name>
IS
<sub_program and public element declaration>
.
.
END <package name>
Package Body:
A package body contains the implementation of the cursors or subprograms declared in the package
specification. In the package body, you can declare or define private variables, cursors, etc., used
only by package body itself. A package body can have an initialization part whose statements
initialize variables or perform other one-time setups for the whole package.
A package body can also have an exception-handling part used to handle exceptions.
Syntax:
CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<global_declaration part>
<Private element definition>
<sub_program and public element definition>
.
<Package Initialization>
END <package_name>

1) Create package name bank transaction having procedure apply

transaction and enter transaction , apply transaction will use to insert
or delete some amount from bank and enter transaction is used to
check whether the amount is debited or credited.
Code:

Output:

32
Name:Pravish Dwivedi Roll no :18

2) Create package to check_date which will have function to return

current date or system date.
Code:

Output:

pravish

3) Create package to annual_salary which will have Function to return

annual salary for that particular empid.
Code:

33
Name:Pravish Dwivedi Roll no :18

Output:

pravish

4) Create a package even_odd having function to return the value is

even or odd.
Code:

34
Name:Pravish Dwivedi Roll no :18

Output:

pravish

5) Create a package even_odd having procedure to check whether the

given number is even or odd.
Code:

35
Name:Pravish Dwivedi Roll no :18

Output:

pravish

6) Create a package order_detail having procedure and function to

return total orders given by some particular customers defined.
Code:

36
Name:Pravish Dwivedi Roll no :18

Output:

pravish

7) Create a package purchase_detail having procedure, function and

exception to get total purchase data in year 2020.
Code:

37
Name:Pravish Dwivedi Roll no :18

Output:

pravish

38
Name:Pravish Dwivedi Roll no :18
PRACTICAL 6

1) Create procedure for updating grade of student to O whose marks is

above 80.
Code:

Output:

pravish

39
Name:Pravish Dwivedi Roll no :18
2) Create Function to calculate Fibonacci series.
Code:

Output:

pravish

40
Name:Pravish Dwivedi Roll no :18
3) Create function calculate 10% commission on salary.
Code:

Output:

pravish

4) Create package including procedure to check factorial of number.

Code:

41
Name:Pravish Dwivedi Roll no :18

Output:

pravish

5) Create package including functions to check greater of 3 numbers.

Code:

42
Name:Pravish Dwivedi Roll no :18

Output:

pravish

6) Create procedure ,function ,package for multiplication table.

Code:

43
Name:Pravish Dwivedi Roll no :18

Output:

pravish

44
Name:Pravish Dwivedi Roll no :18
PRACTICAL 7

1) Procedure, function, package for Armstrong number.

Code:

45
Name:Pravish Dwivedi Roll no :18

Output:

pravish

2) Procedure, function, Package to calculate incentive of emp id 110

Code:

46
Name:Pravish Dwivedi Roll no :18

Output:

pravish

3) Procedure, Function, Package to calculate electricity bill.

Code:

47
Name:Pravish Dwivedi Roll no :18

48
Name:Pravish Dwivedi Roll no :18

Output:

pravish

4) Procedure, function, Package to calculate age.

Code:

49
Name:Pravish Dwivedi Roll no :18

Output:

pravish

5) Create a sequence having interval 20, min value 10, max value 200,
cycle and cache with default value.
Code:

Output:

50
Name:Pravish Dwivedi Roll no :18

6) Create a sequence for empid.

Code:

Output:

51
Name:Pravish Dwivedi Roll no :18
Practical 8
Description:
LOOP :
This basic LOOP statement consists of a LOOP keyword, a body of executable code, and the
ENDLOOP keywords.
The LOOP statement executes the statements in its body and returns control to the top of the loop.
Typically, the body of the loop contains at least one EXIT or EXIT WHEN statement for terminating
the loop. Otherwise, the loop becomes an infinite loop.
The LOOP statement can have an optional label that appears at the beginning and the end of the
statement.
Syntax:
<<label>> LOOP
statements;
ENDLOOP loop_label;
FOR LOOP :
"FOR LOOP" statement is best suitable when you want to execute a code for a known number of
times rather than based on some other conditions.
In this loop, the lower limit and the higher limit will be specified and as long as the loop variable is in
between this range, the loop will be executed.
The loop variable is self-incremental, so no explicit increment operation is needed in this loop. The
loop variable need not to be declared, as it is declared implicitly.
Syntax :
FOR <loop_variable> in <lower_limit> ..<higher_limit>
LOOP
<execution block starts> . . . <execution_block_ends>
END LOOP;
WHILE LOOP :
WHILE loop statement works similar to the Basic loop statement except the EXIT condition is at the
very beginning of the loop.
It works like an entry-check loop in which execution block will not even be executed once if the
condition is not satisfied, as the exit condition is checking before execution part. It does not require
keyword 'EXIT' explicitly to exit from the loop since it is validating the condition implicitly each time
of the loop.
Syntax:
WHILE <EXIT condition>
LOOP
<execution block starts>
.
.
.
<execution_block_ends>
END LOOP;

1) Using LOOP ,FOR LOOP and WHILE LOOP Check whether given
number is even or odd.
Code:

52
Name:Pravish Dwivedi Roll no :18

Output:

pravish

2) Using LOOP ,FOR LOOP and WHILE LOOP Check the factorial of given
number.
Code:

Output:

pravish

53
Name:Pravish Dwivedi Roll no :18
3) Using LOOP ,FOR LOOP and WHILE LOOP Check the given number is
prime or not.
Code:

Output:

pravish

4) Using LOOP Print the multiplication of number.

Code:

Output:

54
Name:Pravish Dwivedi Roll no :18

pravish

5) Using LOOP Print the multiplication of table in reverse.

Code:

Output:

pravish

55
Name:Pravish Dwivedi Roll no :18
6) Using LOOP Print the number series in reverse.
Code:

Output:

pravish

7) Using while loop show the adding of two numbers.

Code:

56
Name:Pravish Dwivedi Roll no :18

Output:

pravish

8) Write a package to check leave of some particular empid.

Code:

57
Name:Pravish Dwivedi Roll no :18
Output:

pravish

9) Write a package to check salary of particular empid.

Code:

Output:

pravish

58
Name:Pravish Dwivedi Roll no :18
Practical 9
Description:
CASE:
Oracle CASE expression has two formats: the simple CASE expression and the searched CASE
expression. Both formats support an optional ELSE clause.
Simple CASE expression
The simple CASE expression matches an expression to a list of simple expressions to determine the
result.
Syntax:

CASE e
WHEN e1 THEN
r1
WHEN e2 THEN
r2
WHEN en THEN
rn
[ ELSE r_else ]
END
The searched CASE expression evaluates the Boolean expression (e1, e2, …) in each WHEN clause in
the order that the Boolean expressions appear. It returns the result expression (r) of the first
Boolean expression (e) that evaluates to true.
If no Boolean expression is true, then the CASE expression returns the result expression in the ELSE
clause if an ELSE clause exists; otherwise, it returns a null value.
Like the simple CASE expression, Oracle also uses short-circuit evaluation for the searched CASE
expression.
Syntax :
CASE
WHEN e1 THEN r1
[ WHEN e2 THEN r2]
...
[ELSE
r_else]
END

1) Retrieve c_name from customer table checking two credit limit

different conditions: low if it is equal to 100 and high if is 100000.
Code:

Output:

59
Name:Pravish Dwivedi Roll no :18

2) Retrieve sid and sname for student scoring marks between 80-100.
Code:

Output:

3) Find the average salary of employees in employee table using

$5000 as lowest salary possible.
Code:

Output:

60
Name:Pravish Dwivedi Roll no :18

4) Retrieve employee name from emp table and based salary divide
them into different class (10000-20000=class A…).
Code:

Output:

61
Name:Pravish Dwivedi Roll no :18

5) Using PLSQL block write the cases for checking marks of students
according to different categories (Excellent, good …)
Code:

Output:

pravish

62
Name:Pravish Dwivedi Roll no :18
6) Using PLSQL queries update the commission of emp to 80% for
salary range 10000-20000.
Code:

Output:

7) using PLSQL block check sales based on different commissions.

Code:

Output:

pravish

63
Name:Pravish Dwivedi Roll no :18
8) Draw the triangle pattern using any loop.
*

**

***

****

*****

******

*******

Code:

Output:

pravish

64
Name:Pravish Dwivedi Roll no :18
Practical 10
Description:
The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-
THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause lets you
add additional conditions.
When using IF-THEN-ELSIF statements there are a few points to keep in mind.
• It's ELSIF, not ELSEIF.
• An IF-THEN statement can have zero or one ELSE's and it must come after any ELSIF's.
• An IF-THEN statement can have zero to many ELSIF's and they must come before the ELSE.
• Once an ELSIF succeeds, none of the remaining ELSIF's or ELSE's will be tested.
Syntax
The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming language is −
IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN
S3; -- Executes when the boolean expression 3 is true
ELSE
S4; -- executes when the none of the above condition is true
END IF;

1) Calculate annual income for taking different employee.

Code:

Output:

pravish

2) Calculate vowel or consonant for given data .

Code:

65
Name:Pravish Dwivedi Roll no :18

Output:

pravish

3) Check whether given number is even or odd .

Code:

Output:

pravish

66
Name:Pravish Dwivedi Roll no :18

4) Retrieve job_id from employees check on conditions.

If job_id =’PU_CLERK’ THEN sal_raise := .09;

ELSIF jobid = ‘SH_CLERK’ THEN sal_raise := .08;

ELSIF jobid = ‘ST_CLERK’ THEN sal_raise := .07;

Code:

Output:

pravish

5) Calculate the grade of students using case statements.

Code:

67
Name:Pravish Dwivedi Roll no :18
Output:

pravish

6) Check the current month according to the given season .

Code:

Output:

pravish

7) Check the salary of employee according to the department.

Code:

Output:

68
Name:Pravish Dwivedi Roll no :18

8) Check the commission of particular employee .

Code:

Output:

9) Update the commission checking with different if conditions.

Code:

Output:

pravish

69
Name:Pravish Dwivedi Roll no :18
10) Check the condition of bonus according to different dept.
Code:

Output:

pravish

11) Illustrate example using case and if both .

Code:

Output:

pravish

70
Name:Pravish Dwivedi Roll no :18
Practical 11
Description:
CURSORS :
A Cursor is a pointer to this context area. Oracle creates context area for processing an SQL
statement which contains all information about the statement.
PL/SQL allows the programmer to control the context area through the cursor. A cursor holds the
rows returned by the SQL statement. The set of rows the cursor holds is referred as active set. These
cursors can also be named so that they can be referred from another place of the code.
Syntax:
DECLARE
CURSOR <cursor_name> IS <SELECT statement^>
<cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;

1) Create view for student table and create cursor stu and fetch the
sgrade returning the grade of the student.
Code:

Output:

pravish

71
Name:Pravish Dwivedi Roll no :18
2) Using above cursor update the percentage of student by 10% if
grade is A+
Code:

Output:

pravish

3) Update the table and increase the bonus of eah employee by 500
and use the SQL%ROWCOUNT attribute to determine the number of
rows affected.
Code:

Output:

pravish

72
Name:Pravish Dwivedi Roll no :18

4) Illustrate the concept of explicit cursor by fetching the mid, mname

from manager table using cursor retrieve the emp details from emp
table.
Code:

Output:

pravish

5) Using the fr cursor retrieve the emp details from emp table.
Code:

Output:

73
Name:Pravish Dwivedi Roll no :18

pravish

6) Get the last name for a specific employee ID.

Code:

Output:

pravish

7) Fetch an entire row from the employee table for a specific

employee ID.
Code:

74
Name:Pravish Dwivedi Roll no :18
Output:

pravish

8) Use a cursor FOR loop to display the last name of all employees in
department 10.
Code:

Output:

pravish

9) Retrieve list price and product name from product table using for
cursor to get all the data.
Code:

75
Name:Pravish Dwivedi Roll no :18

Output:

pravish

76