PL/SQL cursors

PL/SQL block:

DECLARE
cursor aa is
select * from emp1;
aa1 aa%rowtype;
BEGIN
open aa;
if aa%isopen then
loop
fetch aa into aa1;
EXIT when aa%NOTFOUND;
if aa%FOUND then
dbms_output.put_line(aa1.eno || aa1.name);
end if;
end loop;
else
dbms_output.put_line('not open');
end if;
END;

PL/SQL to fetch data using cursors

Creating a table:

Create table emp_g(eno number,e_name varchar2(20))


Inserting values in the table:

Insert into emp_g(eno,e_name) values(1,'steve');


PL/SQL block:

DECLARE
cursor emp_detail (eno1 number) is select e_name from emp_g where eno=eno1;
eno2 emp_g.eno%type:=2;
name2 emp_g.e_name%type;
BEGIN
Open emp_detail(eno2);
Fetch emp_detail into name2;
dbms_output.put_line(name2);
Close emp_detail;
END;

PL/SQL program for explicit cursors

Creating a table:

Create table emp_29(e_no number, e_name varchar2(20),e_sal number)


Inserting values in the table:

Insert into emp3 (e_no,e_name,e_sal) values(1,'Mak',20000);
Insert into emp3 (e_no,e_name,e_sal) values(2,'Monish',25000);
Insert into emp3 (e_no,e_name,e_sal) values(3,'Murthy',25000);


PL/SQL block:

DECLARE
cursor emp_disp is
select e_name from emp3 ;
name1 emp3.e_name%type;
BEGIN
open emp_disp;
if emp_disp%ISOPEN then
    loop
    fetch emp_disp into name1;
    exit when emp_disp%NOTFOUND;
    if emp_disp%FOUND then
       dbms_output.put_line(name1);
    end if;
    end loop;
    commit;
else
     dbms_output.put_line('unable to open the cursor');
end if;
close emp_disp;
END;

PL/SQL program for simple cursors

Create table employee29(eno varchar2(30),salary number(10));


Inserting values in the table:

Insert into employee29 values('e1001',20000);
Insert into employee29 values('e1002',15000);
Insert into employee29 values('e1003',17000);
Insert into employee29 values('e1004',10000);
Insert into employee29 values('e1005',21000);


PL/SQL block:

BEGIN
update employee29 set salary=salary+1000 where eno='&eno';
if SQL%FOUND then
dbms_output.put_line('updated successfully');
end if;
if SQL%NOTFOUND then
dbms_output.put_line('not existing');
end if;
END;

Output:

PL/SQL program to show GOTO statement

CREATE TABLE MyTable29( num_col  NUMBER,char_col   VARCHAR2(60));

PL\SQL Block:

DECLARE
      v_Counter  BINARY_INTEGER := 1;
    BEGIN
      LOOP
       INSERT INTO MyTable29
         VALUES (v_Counter, 'Loop count');
       v_Counter := v_Counter + 1;
       IF v_Counter > 50 THEN
         GOTO l_EndOfLoop;
       END IF;
     END LOOP;

     <<l_EndOfLoop>>
     INSERT INTO MyTable29 (char_col)
       VALUES ('Done!');
   END;

PL/SQL program to show IF statement

PL\SQL Block:

DECLARE
counter NUMBER := 1;
BEGIN
WHILE (counter < 5) LOOP
IF counter = 2 THEN
GOTO loopindex;
ELSE
dbms_output.put_line('Index ['||counter||'].');
END IF;
      << loopindex >>
IF counter >= 1 THEN
counter := counter + 1;
END IF;
END LOOP;
END;

PL/SQL program to show Case statement

Creating the table:

Create table lecturer29(lid varchar2(10),major_sub varchar2(10));

Inserting values in the table:

Insert into lecturer29(lid,major_sub) values('l001','computer');

Code Block:

select lid,major_sub,
case major_sub
when 'computer' then 'BSC(cs)'
when 'accounts' then 'BCom'
when 'photography' then 'BMM'
when NULL then 'BMS'
else 'no course mentioned'
end as course
from lecturer29;

Loop in PL/SQL

Create table suppliers29 (sup_id varchar2(10), sup_name varchar2(20),sup_address varchar2(20));


PL\SQL Block:

DECLARE
contin varchar2(10);
BEGIN
contin:='&contin';
while(contin='y')
loop
Insert into suppliers29 values ('&sup_id','&sup_name','&sup_address');
dbms_output.put_line('Want another (y/n)');
contin:='&contin';
if contin='n' then
exit;
end if;
end loop;
END;

PL/SQL program to update Table with select query

Create table place_jack (room_id varchar2(5), building varchar2(20), room_no number(5), no_of_seats number(5),description varchar2(50)) ;

Inserting values in the table:

Insert into place_jack (room_id, building, room_no, no_of_seats) values(‘p001’,’A’,101,30);
Insert into place_jack (room_id, building, room_no, no_of_seats) values(‘p002’,’B’,202,15);
Insert into place_jack (room_id, building, room_no, no_of_seats) values(‘p003’,’C’,204,35);


PL\SQL Block:

DECLARE
nos number(5);
rid varchar2(5);
BEGIN
rid:='&rid';
SELECT no_of_seats INTO nos from place_jack where room_id=rid;
if nos<20 then
update place_jack set description='fairly small' where room_id=rid;
else
if nos between 20 and 40 then
update place_jack set description='a little bigger' where room_id=rid;
else
if nos between 20 and 40 then
update place_jack set description='lots of room' where room_id=rid;
end if;
end if;
end if;
END;

PL/SQL program to update a table

Create table emp29(name varchar2(22),startdate date,salary number(33),flag number(22));

Inserting values in the table:

Insert into emp29 values('abc','1-mar-99',4000,1);
Insert into emp29 values('ac','1-mar-99',40040,0);
Insert into emp29 values('bc','1-mar-99',1000,1);


PL/SQL block:

DECLARE
date_compare date;
date_ofjoin date;
BEGIN
date_compare:='&date_compare';
if(to_char(date_ofjoin) < to_char(date_compare)) then
update emp29
set salary=salary+(salary*0.15);
else
update emp29 set salary=salary+(salary*0.5) ;
end if;
END;

PL/SQL program for Inserting Values in a table.

Creating the table:

Create table suppliers29 (s_id varchar2(20), name varchar2(20), salary number(10));


PL\SQL Block:

DECLARE
status varchar2(20);
BEGIN
status:='&status';
loop
if(status='y')then
insert into supplier29 values('s1001','abc',8765);
end if;
status:='&status';
dbms_output.put_line('want  another(y/n)');
if(status='n')then
exit;
end if;
end loop;
END;

PL/SQL program for defining and calling simple function

create or replace function Addition(x number,y number)
return number is
z number;
begin
z:=x+y;
return z;
end;
---------------------------------------
declare
z number;
begin
z:=Addition(5,2);
dbms_output.put_line('The Addition:-'||z);
end;


Output:
The Addition:-7

PL/SQL procedure successfully completed.

Basic PL/SQL Loop Program

DECLARE
counts number(2);
BEGIN
counts:=1;
dbms_output.put_line('before my loop');
loop
if(counts>4)then
exit;
end if;
dbms_output.put_line('iteration no'||counts);
counts:=counts+1;
end loop;
END;