3.
Create a Stored Procedure to calculate maximum and minimum of three numbers
entered through users.
create or replace procedure maxmin(a in number,b in number,c in number)
is
max number(6);
min number(6);
begin
if (a>b and a>c) then
dbms_output.put_line('Maximum number is' || a);
elsif (b>a and b>c) then
dbms_output.put_line('Maximum number is' || b);
else
dbms_output.put_line('Maximum number is'||c);
end if;
if (a<b and a<c) then
dbms_output.put_line('Minimum number is'|| a);
elsif (b<a and b<c) then
dbms_output.put_line('Minimum number is'|| b);
else
dbms_output.put_line('Minimum number is'||c);
end if;
end;
/
OUTPUT:
=======
SQL> exec maxmin(10,30,20)
Maximum number is30
Minimum number is10
PL/SQL procedure successfully completed.
4. Write a PL/SQL stored procedure to find the sum of the first ten natural numbers.
create or replace procedure sumnum is
i number;
s number:=0;
begin
for i in 1..10
loop
s:=s+i;
end loop;
dbms_output.put_line('sum of first 10 numbers are: '||s);
end;
/
OUTPUT:
=======
SQL> exec sumnum
sum of first 10 numbers are: 55
PL/SQL procedure successfully completed.
5. Write a PL/SQL function that performs simple arithmetic like Addition, Subtraction, and
Multiplication & Division of input numbers.
declare
x number;
y number;
function add(a in number,b in number) return number
is
d number;
begin
d:=a+b;
return d;
end;
function subtract(e in number,f in number) return number
is
g number;
begin
g:=e-f;
return g;
end;
function multiply (h in number,i in number) return number
is
j number;
begin
j:=h*i;
return j;
end;
function divide (k in number,l in number) return number
is
m number;
begin
m:=k/l;
return m;
end;
begin
x:=&x;
y:=&y;
dbms_output.put_line('addition of two numbers:'||add(x,y));
dbms_output.put_line('Subtraction of two numbers:'||subtract(x,y));
dbms_output.put_line('Multiplication of two numbers:'||multiply(x,y));
dbms_output.put_line('Division of two numbers:'||divide(x,y));
end;
/
OUTPUT:
=======
Enter value for x: 10
old 33: x:=&x;
new 33: x:=10;
Enter value for y: 3
old 34: y:=&y;
new 34: y:=3;
addition of two numbers13
Subtraction of two numbers7
Multiplication of two numbers30
Division of two numbers3.33333333333333333333333333333333333333
PL/SQL procedure successfully completed.
6) Create an EMPLOYEE table with columns:
(EMP_ID,EMP_NAME,EMP_DOB,EMP_SAL,EMP_GRADE,EMP_DNO)
and an EMPLOYEE_LOG table with columns
(EMP_ID,EMP_NAME,EMP_DOB, EMP_PRE_GRADE,EMP_PRE_SAL).
EMP_GRADE contains values like, “A‟, ‟B‟,‟C‟, ‟D‟. Next solve the following:
i. Write a PL/SQL code to insert some rows in Employee table
ii. Create a PL/SQL trigger called EMPLOYEE_TRIGGER on the EMPLOYEE table.
This trigger will be executed after every update of EMP_SAL or EMP_GRADE which will
put the previous Employee value to EMPLOYEE_LOGtable.
iii. Write a PL-SQL cursor that increments the salary of employees of Grade “D‟ by 20%.
Table Creation
-------------------
create table employee1(emp_id number(3), emp_name varchar2(20), emp_dob date,
emp_sal number(7), emp_grade char(1), emp_dno number(2));
create table employee_log1(emp_id number(3), emp_name varchar2(20), emp_dob date,
emp_pre_grade char(1), emp_pre_sal number(7));
Record Insertion
--------------------
insert into employee1 values(101,'Allen','10-feb-1992',40000,'C',11);
insert into employee1 values(102,'Banu','21-oct-1996',60000,'B',12);
insert into employee1 values(103,'Deepa','21-Mar-1990',60000,'B',12);
insert into employee1 values(104,’David’,’23-Mar-1992’,50000,’B’,13);
i) PL/SQL to insert row
----------------------------
declare
emp_id1 employee1.emp_id%type;
emp_name1 employee1.emp_name%type;
emp_dob1 employee1.emp_dob%type;
emp_sal1 employee1.emp_sal%type;
emp_grade1 employee1.emp_grade%type;
emp_dno1 employee1.emp_dno%type;
n number(2);
i number(2);
begin
dbms_output.put_line('enter number of records to be inserted');
n := &n;
for i in 1 .. n
loop
insert into employee1
values(&emp_id1,'&emp_name1','&emp_dob1',&emp_sal1,'&emp_grade1',&emp_dno1);
dbms_output.put_line(i || ' row is inserted');
end loop;
end;
/
ii) PL/SQL triggers
------------------------
create or replace trigger trsal
before update of emp_sal
on employee1
for each row
begin
insert into employee_log1
values(:old.emp_id, :old.emp_name,:old.emp_dob,:old.emp_grade, :old.emp_sal);
dbms_output.put_line('previous values are updated in employee log file successfully');
end;
/
Output:
======
Trigger Created.
create or replace trigger trgrade
before update of emp_grade
on employee1
for each row
begin
insert into employee_log1
values(:old.emp_id,:old.emp_name,:old.emp_dob,:old.emp_grade, :old.emp_sal);
dbms_output.put_line('previous values are updated in employee log file successfully');
end;
/
Output:
======
Trigger Created.
iii) Cursor to update salary
------------------------------------
declare
cursor c1 is select * from employee1 where emp_grade='C' for update of emp_sal;
r1 employee1%rowtype;
begin
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
update employee1 set emp_sal= r1.emp_sal + r1.emp_sal* 0.2 where current of c1 ;
end loop;
dbms_output.put_line(c1%rowcount);
close c1;
end;
/
Output:
======
Previous values are updated in employee log file successfully
Previous values are updated in employee log file successfully
2
Output - Screenshots
---------------------------
Check Some More Screenshots scroll down.
Output 2 is Indicated how many rows are updated by this PL/SQL procedure.
Final Output:
===========
