DBMS Practise

plsql to update salary of employee 20% manager, 15% salesman and 10% for others.

declare
job emp.job%type;
esal emp.sal%type;
eno emp.eit%type;
inc number(7,2);
begin
eno:=: eid;
select job into ejob from emp where eid=eno;
if ejob='Manager' then
inc:=0.2;
update emp set salary =salary+salary * inc where eid=eno;
dbms_output.put_line('Manager sal is updated');
else if
inc:=0.15;
update emp set salary=salary+salary * inc where eid=eno;
dbms_output.put_line('others sal is updated);
end if;
end;

Write a Procedure for Fibonacci series

create or replace procedure fib(n number) as
a number:=0;
b number:=1;
c number;
i number;
begin
dbms_output.put_line(a);
dbms_output.put_line(b);
for i in 3..n loop
c:=a+b;
a:=b;
b:=c;
dbms_output.put_line(c);
end loop;
end;
Procedure created.
declare
n number(10);
begin
n:=:n;
fib(n);
end;
input: 5

output:
0
1
1
2
3

Statement processed.

Update sal using sal

create or replace procedure empupdate(eno emp.empno%type, inc number)as
begin
update emp set sal=sal+inc where empno=eno;
commit;
dbms_output.put_line('update successfully');
end;
declare
eno emp.empno%type;
inc number;
begin
eno:=:eno;
inc:=:inc;
empupdate(eno,inc);
end;
select * from emp;

Swapping of two numbers

t number;
begin
t:=a;
a:=b;
b:=t;
end;

declare
a number:=:a;
b number:=:b;
begin
dbms_output.put_line('before swapping'||a||','||b);
swap(a,b);
dbms_output.put_line('after swapping'||a||','||b);
end;

Function to find max of two numbers

DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:=:a;
b:=:b;
c := findMax(a, b);
dbms_output.put_line(' Maximum of two nums is: ' || c);
END;

Function for total sal payable to emps

CREATE OR REPLACE FUNCTION get_total_sal
RETURN NUMBER
IS
totalsal NUMBER := 0;
BEGIN
SELECT SUM(sal) INTO totalsal
FROM emp;
RETURN totalsal;
END;
declare
sal NUMBER := 0;
BEGIN
sal := get_total_sal ();
DBMS_OUTPUT.PUT_LINE('Sal: ' || sal);
END;
Sal: 35740

Trigger that dosent allow salary to be updated if commission is null

create or replace trigger "empupdatesal" before
update of sal on emp
for each row
begin
if :old.commission is null then
raise_application_error(-20100,'commission is null, sal cannot be updated');
end if;
end;

Trigger that does not allow duplicates

CREATE OR REPLACE TRIGGER "DEPT_T1"
BEFORE
insert on "DEPT"
for each row
declare
a number;
begin
if(:new.deptno is Null) then
raise_application_error(-20001,'error::deptno cannot be null');
else
select count(*) into a from dept where deptno=:new.deptno;
if(a=1) then
raise_application_error(-20002,'error:: cannot have duplicate deptno');
end if;
end if;
end;

Cursor program to print emp number, name, etc

DECLARE
cursor c is select empno, ename, deptno, sal from emp ;
i emp.empno%type;
j emp.ename%type;
k emp.deptno%type;
l emp.sal%type;
BEGIN
open c;
dbms_output.put_line('Empno, name, deptno, salary of employees are:= ');
loop
fetch c into i, j, k, l;
exit when c%notfound;
dbms_output.put_line(i||' '||j||' '||k||' '||l);
end loop;
close c;
END;

Procedure for factorial

create or replace procedure factorial(n in number, fact out number) as
i int :=1;
begin
fact:=1;
while i<=n loop
fact :=fact*i;
i:=i+1;
end loop;
end;
declare
n int;
fact int;
begin
n:=:n;
factorial(n,fact);
dbms_output.put_line('Factorial is:'||fact);
end;