Variable attributes, Flow control Statements
Ø
Variable attributes
1.
Column type variable
Sname student.Sname%type;
2.
Row type variable
i student%rowtype;
Ø
CASE expression
Declare
V_dname varchar2(20);
V_deptno number;
Begin
Select deptno into
V_deptno from emp where empno=:p_empno;
Case V_deptno
When 10 then
V_dname:=’Accounting’;
When 20 then
V_dname:=’RESEARCH’;
When 30 then
V_dname:=’SALES’;
Else
V_dname:=’UNKNOWN’;
End case;
Display(‘Emp department
is ‘||V_dname);
End;
Ø
Flow control statements
1.
Conditional control statements (if, if..else, if…elsif…elsif…else)
2.
Iterative control statements(loop,while,for)
Ø
1. Conditional control statements
a.
If then end if
Write PL/SQL program to hike salary 500 if
employee salary less then 2000 for entered employee no and display employee
name,hiked salary , deptno?
Declare
i emp%rowtype;
begin
select ename,sal,deptno into
i.ename,i.sal,i.deptno from emp where empno=:p_empno;
if i.sal<2000 then
i.sal:=i.sal+500;
update emp set sal=i.sal where empno=:p_empno;
display('The employee details are '||i.ename||'
'||i.sal||' '||i.deptno);
commit;
end if;
end;
b.
If then else
end if
Write PL/SQL program to find entered no either
even or odd no?
Declare
Vnum number:=:p_num;
Begin
If mod(Vnum,2)=0 then
Display(‘The entered number ‘||Vnum||’ is ‘||’
Even number’);
else
Display(‘The entered number ‘||Vnum||’ is ‘||’
Odd number’);
End if;
End;
c.
If then elsif
then else end if
Write PL/SQL program to insert no of employees
into temp_det table?
1.Create table temp_det(NOE_MSG varchar2(50));
2.Declare
Vtotal_emps number;
Begin
Select count(empno) into Vtotal_emps from emp;
If Vtotal_emps=0 then
Insert into temp_det values (‘There is no
employee’);
Elsif Vtotal_emps<5 then
Insert into temp_det values (‘There are few
employees’);
Elsif Vtotal_emps<10 then
Insert into temp_det values (‘There are little
more employees’);
Else
Insert into temp_det values (‘There are many
employees joined’);
End if;
Commit;
End;
d.
RAISE_APPLICATION_ERROR()
It Is built in function used to suspend the
task and as well as display message also
Syntax
RAISE_APPLICATION_ERROR(<error
no>,<error message>);
Note: - error no range is -20000 to -20999
Write a PL/SQL program to update bal after
transaction?
Declare
Vaccno KCB_ACC_TAB.ACCNO%type:=:accno;
Vttype KCB_TRAN2_TAB.TTYPE%type:=:ttype;
Vamt KCB_TRAN2_TAB.AMT%type:=:amt;
Cbal KCB_ACC_TAB.BAL%type;
Vacctype KCB_ACC_TAB.ACCTYPE%type;
begin
select bal,acctype into Cbal,Vacctype from
KCB_ACC_TAB where accno=Vaccno;
if upper(Vttype)='D' then
Cbal:=Cbal+Vamt;
elsif upper(Vttype)='W' then
Cbal:=Cbal-Vamt;
if
Vacctype='S' and Cbal<5000 then
raise_application_error(-20457,'The bal is too
low so there is no transaction');
elsif
Vacctype='C' and Cbal<10000 then
raise_application_error(-20458,'The bal is too
low so there is no transaction');
end if;
end if;
update KCB_ACC_TAB set bal=cbal where
accno=Vaccno;
insert into KCB_TRAN2_TAB values(nvl((select
max(TID) from KCB_TRAN2_TAB),100)+1,Vaccno,Vttype,sysdate,Vamt);
commit;
end;
Ø
2.Loops
a.
Simple loop
the most basic loop. Syntax is follows
Loop
Sequence of statements;
End loop;
The sequence of statements will be executed
between ‘loop’ and ‘end loop’.
It can be terminated the loop in three methods
i.)by using
EXIT statement
EXIT [when condition];
ii.)by using if condition
if condition then
executable statements;
EXIT;
End if;
iii.)by using GOTO statement. GOTO statement
uses label
if condition then
GOTO endofloop;
End if;
End loop;<<endofloop>>
Write PL/SQL program to display 1 to 10
numbers?
Declare
N number:=1;
Begin
Loop
Exit when N>10;
Display(N);
N:=N+1;
End loop;
End;
Write a PL/SQL program to display 100 adding by
25 to every result upto no reach 250.
Declare
A number:=100;
Begin
Loop
If A=250 then
Exit;
End if;
A:=A+25;
Display(A);
End loop;
End;
b.
While loop
It is pretested loop. If condition evaluates true
then sequence statements will be executed and again control passes to beginning
of the loop. If condition evaluates false then control passes to next
statement.
Write PL/SQL program to display 1 to 10 numbers
horizontally?
Declare
n number:=1;
v varchar2(100);
begin
while n<=10
loop
v:=v||' '||n;
n:=n+1;
end loop;
display(v);
end;
c.
For loop
It is numeric loop. Syntax is follows
For loop_counter in [REVERSE] lowerbound ..
higherbound
Loop
Executable statements;
End loop;
Note: - ‘loop_counter’ is implicit variable it
is declared by oracle, so no need to declare by user. ‘REVERSE’ will be used to
process from higherbound to lowebound value.
We can’t perform any athematic operations on
implicit variable that is ‘loop_counter’.
Write a PL/SQL program to display 1 to 10 no by
using for loop?
Begin
For i in 1 .. 10
Loop
Display(i);
End loop;
End;
Write a PL/SQL program to display the result as
follows
1
12
123
1234
12345
Declare
N number:=:p_no;
V varchar2(30);
Begin
For i in 1 .. N
Loop
For
j in 1 .. i
Loop
V:=V||j;
End
loop;
Display(V);
V:=null;
End loop;
End;
Write a PL/SQL program to display factorial of
given number?
Declare
V_fact_num number:=:p_factnum;
V_factorial number:=1;
Begin
For i in REVERSE 1 .. V_fact_num
Loop
V_factorial:=V_factorial*i;
End loop;
Display(‘The factorial of ‘||V_fact_num||’ is
‘||V_factorial);
End;
GOTO statement
1.create table temp_table(V_counter
number,Description varchar2(50));
2.Declare
V_counter number:=1;
begin
loop
insert into temp_table values(V_counter,'loop
count');
V_counter:=V_counter+1;
if V_counter>=50 then
GOTO endofloop;
end if;
end loop;<<endofloop>>
insert into temp_table (Description) values
('done');
end;
Comments
Post a Comment