Pl/SQl Programs ADS

16.
declare
cursor cursor is select * from emp7567 order by salary desc;
id emp7567.id%type;
name emp7567.name%type;
salary emp7567.salary%type;
begin
open cursor;
loop
fetch cursor into id,name,salary;
dbms_output.put('Id: '||id||' ');
dbms_output.put('Name: '||name||' ');
dbms_output.put('Salary: '||salary||' ');
dbms_output.put_line(' ');
exit when(cursor%rowcount>5);
end loop;
close cursor;
end;

17.
declare
rows number;
begin

update emp7567
set
salary=salary+0.25*salary;

if sql%notfound then
dbms_output.put_line('No Row Updated');
else
rows:=sql%rowcount;
dbms_output.put_line(rows||' Rows Updated');
end if;


end;

18.

declare
cursor cursor is select * from emp7567;
id emp7567.id%type;
name emp7567.name%type;
salary emp7567.salary%type;
begin
open cursor;
loop
fetch cursor into id,name,salary;
dbms_output.put('Id: '||id||' ');
dbms_output.put('Name: '||name||' ');
dbms_output.put('Salary: '||salary||' ');
dbms_output.put_line(' ');
exit when(cursor%notfound);
end loop;
dbms_output.put_line(cursor%rowcount||' Results Found');
close cursor;
end;

19.

create trigger uppercase7567 before insert  or update on emp7567
for each row
when (New.id>0)
begin
:New.name:=UPPER(:new.name);
end;

20.

PL/SQL Programs ADS Lab

Programs:

1.      1.to display the word “HELLO”

Code: 

declare
word varchar2(10) :='Hello';
begin
dbms_output.put_line(word);
end; 

2.which will get the salary of an employee with particular id from

emp table and display it on the screen

Code:

declare 
ida number :=&a; 
salary emp7567.salary%type; 
begin 
select salary into salary from emp7567 where id=ida; 

dbms_output.put_line(salary); 
 
end;

3.

which

creates two variables in the outer block and assign their product to the third

variable created in the inner block
declare
a number:=75;
b number:=67;
begin
declare
c number;
begin
c :=a*b;
dbms_output.put_line('Product is : '||c);
end;
end;

 4.

which

will increase the salary of the employees by 25%, you can declare a constant

and use it thro

ughout the program

declare
increment number(2):=0.25;
begin
update emp7567
set
tsalary= tsalary + tsalary*increment;

if sql%notfound then
dbms_output.put_line('No Rows Updated');
else
dbms_output.put_line(sql%rowcount||' Rows Updated');
end if;
end;

5.

to declare a record called

employee_rec

based on a user

-

defined type

Code:

DECLARE
   employee_rec emp7567%rowtype;
BEGIN
   SELECT * into employee_rec
   FROM emp7567
   WHERE id = 1507567; 
   dbms_output.put_line('Employee ID: ' || employee_rec.id);
   dbms_output.put_line('Employee Name: ' || employee_rec.name); 
   dbms_output.put_line('Employee Salary: ' || employee_rec.tsalary);
END;  

6.

create procedure adslab  
as 
begin 
 
goto section3; 
 
<< Section1>> 
begin 
dbms_output.put_line('Section 1'); 
goto section4; 
end; 
 
 
<< Section2>> 
begin 
dbms_output.put_line('Section 2'); 
goto section1; 
end; 
 
 
<< Section3>> 
begin 
dbms_output.put_line('Section 3'); 
goto section2; 
end; 
 
 
<< Section4>> 
begin 
dbms_output.put_line('Section 4'); 
end; 
 
end;

7.

which

use the relational operators to compare character values for equality or

inequality.

declare
char varchar2(1):='b';
begin
if char='a' then
dbms_output.put_line('Equal to a');
else
dbms_output.put_line('Not Equal to a');
end if;
end;

8.

declare
input number:=&a;
begin
if Mod(input,2)=0 then
dbms_output.put_line('Even');
else
dbms_output.put_line('Odd');
end if;
end;

9.

declare
input number:=&a;
i number :=1;
begin
loop
dbms_output.put_line(input||' * '||i||' = '||input*i);
i:=i+1;
exit when(i=11);
end loop;
end;

10.

declare
input number:=&a;
i number(1);
begin
for i in 1..10 loop
dbms_output.put_line(input||' * '||i||' = '||input*i);
end loop;
end;

11.
declare
input number:=10;
i number:=1;
begin
while i<11 loop
dbms_output.put_line(input);
input:=input+1;
i :=i+1;
end loop;
end;

12.

declare
j number;
i number;
begin
for i in 1..3 loop
    for j in 1..10 loop
        dbms_output.put(i*j||' ');
    end loop;
    dbms_output.put_line('');
end loop;   

end;

13.

declare
j number;
i number;
begin
<< loop1 >> /*Label of loop*/
for i in 1..4 loop
<< loop2 >>
    for j in 1..10 loop
        dbms_output.put(i*j||' ');
    end loop loop2;
    dbms_output.put_line('');
end loop loop1;   

end;

14.

declare
a number:=1;
begin

while a<10 loop
if a=5 then
    goto breakloop;
else
a:=a+1;
dbms_output.put_line(a);
end if;
end loop;

<<breakloop>>
dbms_output.put_line('Loop Breaked');

end;

15.

declare
Type names is varray(10) of varchar(10);
Type salary is varray(10) of number;
namearray names;
salaryarray salary;
total number;
begin
namearray:=names('John','Jennie','Ben','Ash','May','Max','Broke','Misty','Sherry','Ammmy');
salaryarray:=salary(2500,1200,1400,1500,1700,1230,1450,1681,1421,7567);
total:=namearray.count;
for i in 1..total loop
    insert into emp7567 values(i,namearray(i),salaryarray(i));
end loop;
end;

16.