PL/SQL Control Structures define as follow two ways
Ø Conditional statements
Ø Loops
Conditional Statements
There are three forms of IF statements
IF-THEN-END IF;
IF-THEN-ELSE-END IF;
IF-THEN-ELSIF-END IF;
Syntax:
IF condition THEN
Statements;
ELSIF condition THEN
Statements;
ELSE
Statements;
END IF;
Note:- You can also have a IF within
a IF called as Nested IF.
Example: IF..ELSE..END IF
-- Block to demonstrate IF...ELSE...END
IF
DECLARE
vname emp.ename%TYPE;
veno emp.empno%TYPE := &emp_num;
vsal emp.sal%TYPE;
BEGIN
SELECT ename, sal
INTO vname, vsal
FROM emp
WHERE empno = veno;
-- Displays an appropriate
message if salary is greater than 1500
IF vsal > 1500
THEN
DBMS_OUTPUT.put_line
(vname || ' earns a salary greater than
1500');
--Else it shows the employee
name whose sal is <1500
ELSE
DBMS_OUTPUT.put_line
(vname || ' earns a salary not greater
than 1500');
END IF;
DBMS_OUTPUT.put_line
('This line executes
irrespective of the condition');
END;
Example: Nested IF
DECLARE
firstno NUMBER (5) := &firstno;
secondno NUMBER (5) := &secondno;
BEGIN
IF firstno IS NULL OR secondno IS NULL
THEN
DBMS_OUTPUT.put_line
('Improper Input');
ELSE
IF firstno = secondno
THEN
DBMS_OUTPUT.put_line
('The numbers are equal');
ELSE
IF firstno > secondno
THEN
DBMS_OUTPUT.put_line
('The first no. is greater');
ELSE
DBMS_OUTPUT.put_line
('The second no. is greater');
END IF;
END IF;
END IF;
END;
/
LOOP Statements
Simple Loops
WHILE Loops
FOR Loops
Simple Loops
LOOP
Sequence_of_statements;
END LOOP;
Add EXIT statement to exit from the
loop
WHILE Loops
WHILE
condition
LOOP
Statements;
END LOOP;
Condition is evaluated before each
iteration of the loop
Eg. #
1
DECLARE
v_count NUMBER (5) := &count;
-- If you enter count as 10
program will give all
-- even and odd numbers from
1 to 10.
v_even_count NUMBER (5) := 0;
v_odd_count NUMBER (5) := 0;
BEGIN
WHILE v_count > 0
LOOP
IF MOD (v_count, 2) = 0
THEN
v_even_count := v_even_count + 1;
ELSE
v_odd_count := v_odd_count + 1;
END IF;
v_count := v_count - 1;
END LOOP;
DBMS_OUTPUT.put_line
('Even Nos are :' || v_even_count);
DBMS_OUTPUT.put_line
('Odd Nos are :' || v_odd_count);
END;
LOOP Statement: Example
DECLARE
v_i NUMBER (2) := 1;
BEGIN
LOOP
DBMS_OUTPUT.put_line
('Value : ' || v_i);
EXIT WHEN v_i = 10;
v_i := v_i + 1;
END LOOP;
END;
WHILE Loop: Example
DECLARE
v_i NUMBER (2) := 1;
BEGIN
WHILE (v_i <= 10)
LOOP
DBMS_OUTPUT.put_line
('Value : ' || v_i);
v_i := v_i + 1;
END LOOP;
END;
FOR Loops
The number of iterations for
simple loops and WHILE loops is not known in advance, it depends on the loop
condition. Numeric FOR loops, on the other hand, have defined number of
iterations.
FOR counter IN
[REVERSE] low_bound ..
high_bound
LOOP
Statements;
END LOOP;
Where:
counter: is an implicitly declared
integer whose value automatically
increases or decreases by 1 on each iteration
REVERSE:
causes the counter to decrement from upper bound to lower bound
low_bound: specifies the lower
bound for the range of counter values
high_bound: specifies the upper
bound for the range of counter values
The following loop will print
the number series from 1 to 10.
BEGIN
FOR v_count IN 1 .. 10
LOOP
DBMS_OUTPUT.put_line
('Count is :' || v_count);
END LOOP;
END;
The following loop will print
the number series from 10 to 1.
BEGIN
FOR v_count IN REVERSE 1 .. 10
LOOP
DBMS_OUTPUT.put_line
('Count is :' || v_count);
END LOOP;
END;
FOR Loop: Example
BEGIN
FOR v_i IN 1 .. 10
/* The LOOP VARIABLE v_i of
type BINARY_INTEGER is declared
automatically */
LOOP
DBMS_OUTPUT.put_line
('Value : ' || v_i);
END LOOP;
END;
For Loop with EXIT condition
DECLARE
myno NUMBER (5) := &myno;
counter NUMBER (5) := 1;
BEGIN
FOR i IN 2 .. myno - 1
LOOP
counter := counter + 1;
EXIT WHEN myno MOD i = 0;
END LOOP;
IF counter = myno - 1
THEN
DBMS_OUTPUT.put_line
('The given
number is prime');
ELSE
DBMS_OUTPUT.put_line
('The given number is not
a prime number');
END IF;
END;
/
No comments:
Post a Comment