To see previous session 1
case and %type example in pl/sql
declare
stu marks.name%type;
mat marks.math%type;
eng marks.english%type;
tam marks.tamil%type;
tot marks.tamil%type;
per number(5,2);
begin
select math,english,tamil into mat,eng,tam from marks where name = 'bala';
tot:=mat+eng+tam;
per:=((tot/300)*100);
case
when per = 100 then dbms_output.put_line('Ur grade is S');
when (per>90 and per<100) then dbms_output.put_line('Ur grade is A');
when (per>80 and per<90) then dbms_output.put_line('Ur grade is b');
when (per>50 and per<80) then dbms_output.put_line('Ur grade is c');
when (per<50) then dbms_output.put_line('Ur grade is u');
End case;
end;
/
Iteration like loop,while and for example in pl/sql
LOOP example in pl/sql
declare
var number(10):=5;
i number:=0;
begin
loop
dbms_output.put_line(i);
exit when (i>5);
i:=i+1;
end loop;
end;
/
while loop example in pl/sql
declare
var number(10):=5;
i number:=0;
begin
while (i<5) loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/
for loop ascending example in pl/sql
declare
var number(10):=5;
i number:=0;
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/
for loop for finding odd and even between 1 to 10
declare
var number(10):=5;
i number:=0;
begin
for i in 1..10 loop
if(mod(i,2)=0) then
NULL;
else
dbms_output.put_line(i||'is odd number');
end if;
end loop;
end;
/
In reverse
declare
var number(10):=5;
i number:=0;
begin
for i in reverse 1..10 loop
if(mod(i,2)=0) then
NULL;
else
dbms_output.put_line(i||'is odd number');
end if;
end loop;
end;
/
Factorial
declare
var number(10):=5;
i number:=0;
temp number:=1;
begin
for i in reverse 1..5 loop
temp:=temp*i;
dbms_output.put_line(temp);
end loop;
end;
/
%row type example in pl/sql
Declare
Employeerow emp %rowtype;
Deptrow dept%rowtype;
Begin
Select * into employeerow from emp where empno=102;
Select * into deptrow from dept where deptno=employeerow.deptno;
dbms_output.put_line(‘Employee name :’|| Employeerow.ename||‘working in:’||deptrow.deptname);
end;
/
