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;
/
0 comments:
Post a Comment