Ex.
No: 9
Date:
PL/SQL PROGRAM TO ACCEPT INPUT FROM USER
AIM:
To write a PL/SQL program to implement the concept of accepting input from the user.
ALGORITHM:
Step 1: Start the program.
Step 2: Declare the input and output variables.
Step 3: Get the input values.
Step 4: Display the result.
Step 6: Terminate the program.
PROGRAM:
SQL > set serveroutput on
declare
regno number(10);
name varchar2(10);
begin
regno:=®no;
name:='&name';
dbms_output.put_line('Regno :'||regno);
dbms_output.put_line('Name :'||name);
end;
/
OUTPUT:
SQL> /
Enter value for regno: 101
old 5: regno:=®no;
new 5: regno:=101;
Enter value for name: Ajay
old 6: name:='&name';
new 6: name:='Ajay';
Regno :101
Name :Ajay
PL/SQL procedure successfully completed.
PROGRAM
SQL > set serveroutput on
Declare
a number;
b number;
c number;
Begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line('Sum of ' || a || ' and ' || b || ' is ' || c);
End;
/
OUTPUT
SQL>Enter value for a: 45
old 6: a:=&a;
new 6: a:=45;
Enter value for b: 67
old 7: b:=&b;
new 7: b:=67;
Sum of 45 and 67 is 112
PL/SQL procedure successfully completed.
Result:
Thus the above PL/SQL program is executed and the output is verified.
Ex.No:10
Date:
PL/SQL PROGRAM USING CONTROL STRUCTURES AND
LOOPING STATEMENT
AIM:
To write a PL/SQL program using control structures and looping statement in RDBMS
using SQL.
ALGORITHM:
Step 1: Start the program.
Step 2: Declare the input and output variables.
Step 3: Get the input values.
Step 4: Check the condition.
Step 5: If the condition is true, display the true statement.
Step 6: If the condition is false, display the false statement.
Step 7: Terminate the program.
PROGRAM:
Using while statement:
SQL > set serveroutput on
declare
n number(3);
fact number(4);
i number(4);
begin
n:=&n;
fact:=1;
i:=1;
while i<=n loop
fact:=fact*i;
i:= i+1;
end loop;
dbms_output.put_line('Factorial'||fact);
end;
/
OUTPUT:
SQL> /
Enter value for n: 5
old 6: n:=&n;
new 6: n:=5;
Factorial 120
PL/SQL procedure successfully completed.
Using for loop:
SQL > set serveroutput on
declare
n number(3);
i number(3);
j number(3);
begin
n:=&n;
for i in 1..n loop
for j in 1..i loop
dbms_output.put('-');
end loop;
dbms_output.put_line('*');
end loop;
end;
/
OUTPUT:
SQL>/
Enter value for n: 6
old 6: n:=&n;
new 6: n:=6;
-*
--*
---*
----*
-----*
------*
PL/SQL procedure successfully completed.
Maximum of 3 Numbers
Using Control Structure
SQL > set serveroutput on
Declare
a number;
b number;
c number;
Begin
a:=&a;
b:=&b;
c:=&c;
if (a>b) and (a>c) then
dbms_output.put_line('A is Maximum');
elsif (b>a) and (b>c) then
dbms_output.put_line('B is Maximum');
else
dbms_output.put_line('C is Maximum');
end if;
End;
/
OUTPUT:
SQL> /
Enter value for a: 4
old 6: a:=&a;
new 6: a:=4;
Enter value for b: 3
old 7: b:=&b;
new 7: b:=3;
Enter value for c: 5
old 8: c:=&c;s
new 8: c:=5;
C is Maximum
PL/SQL procedure successfully completed.
PRIME NUMBER
SQL> set serveroutput on
declare
no number (3):= &no;
a number (4);
b number (2);
begin
for i in 2..no - 1
loop
a := no mod i;
if a = 0then
goto out;
end if;
end loop;
<<out>>
if a = 1
then
dbms_output.put_line (no || ' is a prime number');
else
dbms_output.put_line (no || ' is not a prime number');
end if;
end;
/
OUTPUT:
SQL> /
Enter value for no: 5
5 is a prime number
PL/SQL procedure successfully completed.
SQL> /
Enter value for no: 4
4 is not a prime number
PL/SQL procedure successfully completed.
REVERSE A NUMBER
SQL> declare
2 num integer;
3 revno integer:=0;
4 begin
5 num:=& num;
6 while(num>0) loop
7 revno:=revno*10 +mod(num,10);
8 num:=trunc(num/10);
9 end loop;
10 dbms_output.put_line('The reverse number is: '||revno);
11 end;
12 /
OUTPUT:
SQL>/
Enter value for num: 678
the reverse number is: 876
PL/SQL procedure successfully completed.
SQL> /
Enter value for num: 1234
the reverse number is: 4321
PL/SQL procedure successfully completed.
SQL> /
Enter value for num: 345
the reverse number is: 543
PL/SQL procedure successfully completed.
SUM OF 100 NUMBERS
SQL> Declare
2 a number;
3 s1 number default 0;
4 Begin
5 a:=1;
6 loop
7 s1:=s1+a;
8 exit when (a=100);
9 a:=a+1;
10 end loop;
11 dbms_output.put_line('Sum between 1 to 100 is '||s1);
12 End;
13 /
OUTPUT:
SQL >
Sum between 1 to 100 is 5050
PL/SQL procedure successfully completed.
Result:
Thus the above PL/SQL program is executed and the output is verified.
Ex.No: 11
Date: PROCEDURES
AIM:
To write a PL/SQL program to call a procedure.
ALGORITHM:
Step 1: Create the required tables insert few records.
Step 2: Create or replace the procedure.
Step 3: Start the main program.
Step 4: Declare the required variables.
Step 5: Get the input from the user, find the minimum value and display the output.
Step 6: Terminate the program.
Definition:-
A procedure is a subprogram that performs a specific action.
Syntax :
PROCEDURE name [ (parameter, [, parameter, ...]) ] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION]
exception-handlers]
END [name];
PROGRAM:
SQL>PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
SQL> /
Procedure created.
Main program
DECLARE
a number;
b number;
c number;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
OUTPUT:
Minimum of (23,45) : 23
PL/SQL procedure successfully completed.
Ex. No: 12
Date: FUNCTIONS
AIM:
To write a PL/SQL program to call a function.
ALGORITHM:
Step 1: Create the required tables and insert few records.
Step 2: Create or replace the function.
Step 3: Start the main program.
Step 4: Declare the required variables.
Step 5: Get the input from the user, calculate the factorial by calling the function and display it to
the user.
Step 6: Terminate the program.
Definition:-
A function is a subprogram that computes a value. Functions and procedure are structured
alike, except that functions have a RETURN clause.
Syntax :
FUNCTION name [ (parameter, [, parameter, ...]) ] RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception-handlers]
END [name];
PROGRAM:
FUNCTION:
SQL> create or replace function fact1(n number) return number as
fa number(10);
begin
fa:=1;
for i in 1..n
loop
fa:=fa*i;
end loop;
return fa;
end fact1;
SQL> /
Function created.
Main program
declare
n number;
f number;
begin
n:=&n;
f:=fact1(n);
dbms_output.put_line('Factorial :'||f);
end;
/
OUTPUT:
Enter value for n: 4
Factorial :24
PL/SQL procedure successfully completed.
SQL> /
Enter value for n: 8
Factorial :40320
PL/SQL procedure successfully completed.
Result:
Thus the above PL/SQL program is executed and the output is verified.
Ex.No:13
Date: Exception Handling
AIM:
To write a PL/SQL program to handle exceptions.
ALGORITHM:
Step 1: Create the required tables.
Step 2: Start the main program.
Step 3: Declare the required variables.
Step 4: Get the input from the user, check if it is an exception.
Step 5: Display the required output.
Step 6: Terminate the program.
Syntax :
PROCEDURE name [ (parameter, [, parameter, ...]) ] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION]
exception-handlers]
END [name];
PROGRAM:
EXCEPTION HANDLING
SQL> create table emp(eno number(3)primary key,ename varchar(15),dept varchar(5) ,salary
number(5));
Table created.
SQL> desc emp;
Name Null? Type
-----------------------------------------------------------------
ENO NOT NULL NUMBER(3)
ENAME VARCHAR2(15)
DEPT VARCHAR2(5)
SALARY NUMBER(5)
SQL> insert into emp values(&eno,'&ename','&dept',&salary);
Enter value for eno: 101
Enter value for ename: Kavi
Enter value for dept: cs
Enter value for salary: 25000
old 1: insert into emp values(&eno,'&ename','&dept',&salary)
new 1: insert into emp values(101,'Kavi','cs',25000)
1 row created.
SQL> /
Enter value for eno: 102
Enter value for ename: Tamil
Enter value for dept: cs
Enter value for salary: 25000
old 1: insert into emp values(&eno,'&ename','&dept',&salary)
new 1: insert into emp values(102,'Tamil','cs',25000)
1 row created.
SQL> /
Enter value for eno: 103
Enter value for ename: Ajay
Enter value for dept: IT
Enter value for salary: 27000
old 1: insert into emp values(&eno,'&ename','&dept',&salary)
new 1: insert into emp values(103,'Ajay','IT',27000)
1 row created.
SQL> /
Enter value for eno: 104
Enter value for ename: Harish
Enter value for dept:IT
Enter value for salary: 27000
old 1: insert into emp values(&eno,'&ename','&dept',&salary)
new 1: insert into emp values(104,'Harish','IT',27000)
1 row created.
SQL> /
Enter value for eno: 105
Enter value for ename: Viji
Enter value for dept: IT
Enter value for salary: 27000
old 1: insert into emp values(&eno,'&ename','&dept',&salary)
new 1: insert into emp values(105,'Viji','IT',27000)
1 row created.
SQL> select * from emp;
ENO ENAME DEPT SALARY
----------------------------------------------------
101 Kavi cs 25000
102 Tamil cs 25000
103 Ajay it 27000
104 Harish it 27000
105 Viji it 27000
CODING
SQL>declare
newsal number;
begin
select salary into newsal from emp where eno=&eno;
dbms_output.put_line(‘Newsal:’||newsal);
exception
when no_data_found then
dbms_output.put_line('no rows selected');
end;
OUTPUT:
SQL > /
Enter value for eno: 101
Newsal:25000
SQL>/
Enter value for eno: 106
no rows selected
Result:
Thus the above PL/SQL program is executed and the output is verified.
