#=======================================================================
#
Author : Lokanadham Thandlam
#=======================================================================
/*PL/SQL has three categories
OF control STATEMENTS:
Conditional selection
STATEMENTS, which run different STATEMENTS FOR different DATA values.
THE conditional selection
STATEMENTS ARE IF AND CASE.
LOOP STATEMENTS, which run
THE same STATEMENTS WITH A series OF different DATA values.
THE LOOP STATEMENTS ARE THE
BASIC LOOP, FOR LOOP, AND WHILE LOOP.
THE EXIT STATEMENT transfers
control TO THE END OF A loop.
THE CONTINUE STATEMENT exits THE CURRENT
iteration OF A LOOP AND transfers control TO THE NEXT iteration.
BOTH EXIT AND CONTINUE have
an optional WHEN clause, WHERE you can specify A condition.
SEQUENTIAL control
STATEMENTS, which ARE NOT crucial TO PL/SQL programming.
THE SEQUENTIAL control
STATEMENTS ARE GOTO, which goes TO A specified STATEMENT, AND NULL, which does
nothing.*/
--Example 4–1 IF THEN
STATEMENT
DECLARE
PROCEDURE p (sales NUMBER, quota NUMBER, emp_id NUMBER)
IS
bonus NUMBER := 0;
updated VARCHAR2 (3) := 'No';
BEGIN
IF sales > (quota + 200)
THEN
bonus := (sales - quota) / 4;
UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;
updated := 'Yes';
END IF;
DBMS_OUTPUT.PUT_LINE (
'Table
updated? ' || updated || ', ' || 'bonus = ' || bonus || '.');
END p;
BEGIN
p (10100, 10000, 120);
p (10500, 10000, 121);
END;
/
---Example 4–2 IF THEN ELSE
STATEMENT
DECLARE
PROCEDURE p (sales NUMBER, quota NUMBER, emp_id NUMBER)
IS
bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200)
THEN
bonus := (sales - quota) / 4;
ELSE
bonus := 50;
END IF;
DBMS_OUTPUT.PUT_LINE ('bonus = ' || bonus);
UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;
END p;
BEGIN
p (10100, 10000, 120);
p (10500, 10000, 121);
END;
/
--Example 4–3 NESTED IF THEN
ELSE STATEMENTS
DECLARE
PROCEDURE p (sales NUMBER, quota NUMBER, emp_id NUMBER)
IS
bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200)
THEN
bonus := (sales - quota) / 4;
ELSE
IF sales > quota
THEN
bonus := 50;
ELSE
bonus := 0;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE ('bonus = ' || bonus);
UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;
END p;
BEGIN
p (10100, 10000, 120);
p (10500, 10000, 121);
p (9500, 10000, 122);
END;
/
--Example 4–4 IF THEN ELSIF
STATEMENT
DECLARE
PROCEDURE p (sales NUMBER)
IS
bonus NUMBER := 0;
BEGIN
IF sales > 50000
THEN
bonus := 1500;
ELSIF sales > 35000
THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
DBMS_OUTPUT.PUT_LINE (
'Sales = ' || sales || ', bonus = ' || bonus || '.');
END p;
BEGIN
p (55000);
p (40000);
p (30000);
END;
/
/*
--A single IF THEN ELSIF
statement is easier to understand than a logically equivalent nested IF THEN
ELSE statement:
-- IF THEN ELSIF statement
IF condition_1 THEN
statements_1;
ELSIF condition_2 THEN
statements_2;
ELSIF condition_3 THEN
statement_3;
END IF;
-- Logically equivalent
nested IF THEN ELSE statements
IF condition_1 THEN
statements_1;
ELSE
IF condition_2 THEN
statements_2;
ELSE
IF condition_3 THEN
statements_3;
END IF;
END IF;
END IF;*/
--Example 4–5 IF THEN ELSIF
STATEMENT Simulates Simple CASE STATEMENT
DECLARE
grade CHAR (1);
BEGIN
grade := 'B';
IF grade = 'A'
THEN
DBMS_OUTPUT.PUT_LINE ('Excellent');
ELSIF grade = 'B'
THEN
DBMS_OUTPUT.PUT_LINE ('Very Good');
ELSIF grade = 'C'
THEN
DBMS_OUTPUT.PUT_LINE ('Good');
ELSIF grade = 'D'
THEN
DBMS_OUTPUT.PUT_LINE ('Fair');
ELSIF grade = 'F'
THEN
DBMS_OUTPUT.PUT_LINE ('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE ('No such
grade');
END IF;
END;
/
--Example 4–6 Simple CASE
STATEMENT
DECLARE
grade CHAR (1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A'
THEN
DBMS_OUTPUT.PUT_LINE ('Excellent');
WHEN 'B'
THEN
DBMS_OUTPUT.PUT_LINE ('Very Good');
WHEN 'C'
THEN
DBMS_OUTPUT.PUT_LINE ('Good');
WHEN 'D'
THEN
DBMS_OUTPUT.PUT_LINE ('Fair');
WHEN 'F'
THEN
DBMS_OUTPUT.PUT_LINE ('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE ('No such
grade');
END CASE;
END;
/
--Example 4–7 Searched CASE
STATEMENT
DECLARE
grade CHAR (1);
BEGIN
grade := 'B';
CASE
WHEN grade = 'A'
THEN
DBMS_OUTPUT.PUT_LINE ('Excellent');
WHEN grade = 'B'
THEN
DBMS_OUTPUT.PUT_LINE ('Very Good');
WHEN grade = 'C'
THEN
DBMS_OUTPUT.PUT_LINE ('Good');
WHEN grade = 'D'
THEN
DBMS_OUTPUT.PUT_LINE ('Fair');
WHEN grade = 'F'
THEN
DBMS_OUTPUT.PUT_LINE ('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE ('No such
grade');
END CASE;
END;
/
--Example 4–8 EXCEPTION
INSTEAD OF ELSE Clause IN CASE STATEMENT
DECLARE
grade CHAR (1);
BEGIN
grade := 'B';
CASE
WHEN grade = 'A'
THEN
DBMS_OUTPUT.PUT_LINE ('Excellent');
WHEN grade = 'B'
THEN
DBMS_OUTPUT.PUT_LINE ('Very Good');
WHEN grade = 'C'
THEN
DBMS_OUTPUT.PUT_LINE ('Good');
WHEN grade = 'D'
THEN
DBMS_OUTPUT.PUT_LINE ('Fair');
WHEN grade = 'F'
THEN
DBMS_OUTPUT.PUT_LINE ('Poor');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('No such
grade');
END;
/
--Example 4–9 BASIC LOOP
STATEMENT WITH EXIT STATEMENT
DECLARE
x NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('Inside loop:
x = ' || TO_CHAR (x));
x := x + 1;
IF x > 3
THEN
EXIT;
END IF;
END LOOP;
-- After EXIT, control
resumes here
DBMS_OUTPUT.PUT_LINE
(' After loop: x = ' || TO_CHAR (x));
END;
/
--Example 4–10 BASIC LOOP
STATEMENT WITH EXIT WHEN STATEMENT
DECLARE
x NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('Inside loop:
x = ' || TO_CHAR (x));
x := x + 1; -- prevents infinite loop
EXIT WHEN x > 3;
END LOOP;
-- After EXIT statement,
control resumes here
DBMS_OUTPUT.PUT_LINE
('After loop: x = ' || TO_CHAR (x));
END;
/
--Example 4–11 NESTED,
Labeled BASIC LOOP STATEMENTS WITH EXIT WHEN STATEMENTS
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- Sum several products
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ( (i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE
('The sum of products equals: ' || TO_CHAR (s));
END;
/
--Example 4–12 CONTINUE
STATEMENT IN BASIC LOOP STATEMENT
DECLARE
x NUMBER := 0;
BEGIN
LOOP -- After CONTINUE statement,
control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop:
x = ' || TO_CHAR (x));
x := x + 1;
IF x < 3
THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE (
'Inside loop,
after CONTINUE: x = ' || TO_CHAR (x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE
(' After loop: x = ' || TO_CHAR (x));
END;
/
--Example 4–13 CONTINUE WHEN
STATEMENT IN BASIC LOOP STATEMENT
DECLARE
x NUMBER := 0;
BEGIN
LOOP -- After CONTINUE statement,
control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop:
x = ' || TO_CHAR (x));
x := x + 1;
CONTINUE WHEN x < 3;
DBMS_OUTPUT.PUT_LINE (
'Inside loop,
after CONTINUE: x = ' || TO_CHAR (x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE
(' After loop: x = ' || TO_CHAR (x));
END;
/
--Example 4–14 FOR LOOP
STATEMENTS
BEGIN
DBMS_OUTPUT.PUT_LINE
('lower_bound < upper_bound');
FOR i IN 1 .. 3
LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE
('lower_bound = upper_bound');
FOR i IN 2 .. 2
LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE
('lower_bound > upper_bound');
FOR i IN 3 .. 1
LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/
--Example 4–15 REVERSE FOR
LOOP STATEMENTS
BEGIN
DBMS_OUTPUT.PUT_LINE
('upper_bound > lower_bound');
FOR i IN REVERSE 1 .. 3
LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE
('upper_bound = lower_bound');
FOR i IN REVERSE 2 .. 2
LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE
('upper_bound < lower_bound');
FOR i IN REVERSE 3 .. 1
LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/
--Example 4–16 Simulating
STEP Clause IN FOR LOOP STATEMENT
DECLARE
step PLS_INTEGER := 5;
BEGIN
FOR i IN 1 .. 3
LOOP
DBMS_OUTPUT.PUT_LINE (i * step);
END LOOP;
END;
/
--Example 4–17 FOR LOOP
STATEMENT Tries TO CHANGE INDEX VALUE
BEGIN
FOR i IN 1 .. 3
LOOP
IF i < 3
THEN
DBMS_OUTPUT.PUT_LINE (TO_CHAR (i));
ELSE
i := 2;
END IF;
END LOOP;
END;
/
--Example 4–18 Outside
STATEMENT REFERENCES FOR LOOP STATEMENT INDEX
BEGIN
FOR i IN 1 .. 3
LOOP
DBMS_OUTPUT.PUT_LINE ('Inside loop,
i is ' || TO_CHAR (i));
END LOOP;
DBMS_OUTPUT.PUT_LINE
('Outside loop, i is ' || TO_CHAR (i));
END;
/
--Example 4–19 FOR LOOP
STATEMENT INDEX WITH Same NAME AS VARIABLE
DECLARE
i NUMBER := 5;
BEGIN
FOR i IN 1 .. 3
LOOP
DBMS_OUTPUT.PUT_LINE ('Inside loop,
i is ' || TO_CHAR (i));
END LOOP;
DBMS_OUTPUT.PUT_LINE
('Outside loop, i is ' || TO_CHAR (i));
END;
/
--Example 4–20 FOR LOOP
STATEMENT REFERENCES VARIABLE WITH Same NAME AS INDEX
<<main>>
-- Label
block.
DECLARE
i NUMBER := 5;
BEGIN
FOR i IN 1 .. 3
LOOP
DBMS_OUTPUT.PUT_LINE (
'local: ' || TO_CHAR (i) || ', global: ' || TO_CHAR (main.i) -- Qualify reference with
block label.
);
END LOOP;
END main;
/
--Example 4–21 NESTED FOR
LOOP STATEMENTS WITH Same INDEX NAME
BEGIN
<<outer_loop>>
FOR i IN 1 .. 3
LOOP
<<inner_loop>>
FOR i IN 1 .. 3
LOOP
IF outer_loop.i = 2
THEN
DBMS_OUTPUT.PUT_LINE (
'outer: '
|| TO_CHAR (outer_loop.i)
|| ' inner: '
|| TO_CHAR (inner_loop.i));
END IF;
END LOOP inner_loop;
END LOOP outer_loop;
END;
/
--Example 4–22 FOR LOOP
STATEMENT Bounds
DECLARE
FIRST INTEGER := 1;
LAST INTEGER := 10;
high INTEGER := 100;
low INTEGER := 12;
BEGIN
-- Bounds are numeric
literals:
FOR j IN -5 .. 5
LOOP
NULL;
END LOOP;
-- Bounds are numeric
variables:
FOR k IN REVERSE FIRST .. LAST
LOOP
NULL;
END LOOP;
-- Lower bound is numeric
literal,
-- Upper bound is numeric
expression:
FOR step IN 0 .. (TRUNC (high / low) * 2)
LOOP
NULL;
END LOOP;
END;
/
--Example 4–23 Specifying FOR
LOOP STATEMENT Bounds AT Run TIME
DROP TABLE temp;
CREATE TABLE temp
(
emp_no NUMBER,
email_addr VARCHAR2 (50)
);
DECLARE
emp_count NUMBER;
BEGIN
SELECT COUNT (employee_id) INTO emp_count FROM employees;
FOR i IN 1 .. emp_count
LOOP
INSERT INTO temp (emp_no, email_addr)
VALUES (i, 'to be added later');
END LOOP;
END;
/
--Example 4–24 EXIT WHEN
STATEMENT IN FOR LOOP STATEMENT
DECLARE
v_employees employees%ROWTYPE;
CURSOR c1
IS
SELECT * FROM employees;
BEGIN
OPEN c1;
-- Fetch entire row into
v_employees record:
FOR i IN 1 .. 10
LOOP
FETCH c1 INTO v_employees;
EXIT WHEN c1%NOTFOUND;
-- Process data here
END LOOP;
CLOSE c1;
END;
/
--Example 4–25 EXIT WHEN
STATEMENT IN INNER FOR LOOP STATEMENT
DECLARE
v_employees employees%ROWTYPE;
CURSOR c1
IS
SELECT * FROM employees;
BEGIN
OPEN c1;
-- Fetch entire row into
v_employees record:
<<outer_loop>>
FOR i IN 1 .. 10
LOOP
-- Process
data here
FOR j IN 1 .. 10
LOOP
FETCH c1 INTO v_employees;
EXIT outer_loop WHEN c1%NOTFOUND;
-- Process
data here
END LOOP;
END LOOP outer_loop;
CLOSE c1;
END;
/
--Example 4–26 CONTINUE WHEN
STATEMENT IN INNER FOR LOOP STATEMENT
DECLARE
v_employees employees%ROWTYPE;
CURSOR c1
IS
SELECT * FROM employees;
BEGIN
OPEN c1;
-- Fetch entire row into
v_employees record:
<<outer_loop>>
FOR i IN 1 .. 10
LOOP
-- Process
data here
FOR j IN 1 .. 10
LOOP
FETCH c1 INTO v_employees;
CONTINUE outer_loop WHEN c1%NOTFOUND;
-- Process
data here
END LOOP;
END LOOP outer_loop;
CLOSE c1;
END;
/
--Example 4–27 WHILE LOOP
STATEMENTS
DECLARE
done BOOLEAN := FALSE;
BEGIN
WHILE done
LOOP
DBMS_OUTPUT.PUT_LINE ('This line
does not print.');
done := TRUE; -- This assignment is not
made.
END LOOP;
WHILE NOT done
LOOP
DBMS_OUTPUT.PUT_LINE ('Hello,
world!');
done := TRUE;
END LOOP;
END;
/
--Example 4–28 GOTO STATEMENT
DECLARE
p VARCHAR2 (30);
n PLS_INTEGER := 37;
BEGIN
FOR j IN 2 .. ROUND (SQRT (n))
LOOP
IF n MOD j = 0
THEN
p := ' is not a prime number';
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE
(TO_CHAR (n) || p);
END;
/
--Example 4–29 Incorrect
Label Placement
DECLARE
done BOOLEAN;
BEGIN
FOR i IN 1 .. 50
LOOP
IF done
THEN
GOTO end_loop;
END IF;
<<end_loop>>
END LOOP;
END;
/
--
--Example 4–30 GOTO STATEMENT
Goes TO Labeled NULL STATEMENT
DECLARE
done BOOLEAN;
BEGIN
FOR i IN 1 .. 50
LOOP
IF done
THEN
GOTO end_loop;
END IF;
<<end_loop>>
NULL;
END LOOP;
END;
/
--Example 4–31 GOTO STATEMENT
Transfers Control TO Enclosing BLOCK
DECLARE
v_last_name VARCHAR2 (25);
v_emp_id NUMBER (6) := 120;
BEGIN
<<get_name>>
SELECT last_name
INTO v_last_name
FROM employees
WHERE employee_id = v_emp_id;
BEGIN
DBMS_OUTPUT.PUT_LINE (v_last_name);
v_emp_id := v_emp_id + 5;
IF v_emp_id < 120
THEN
GOTO get_name;
END IF;
END;
END;
/
--Example 4–32 GOTO STATEMENT
Cannot Transfer Control INTO IF STATEMENT
DECLARE
valid BOOLEAN := TRUE;
BEGIN
GOTO update_row;
IF valid
THEN
<<update_row>>
NULL;
END IF;
END;
/
--Example 4–33 NULL STATEMENT
Showing NO Action
DECLARE
v_job_id VARCHAR2 (10);
v_emp_id NUMBER (6) := 110;
BEGIN
SELECT job_id
INTO v_job_id
FROM employees
WHERE employee_id = v_emp_id;
IF v_job_id = 'SA_REP'
THEN
UPDATE employees
SET commission_pct = commission_pct * 1.2;
ELSE
NULL; -- Employee is not a sales
rep
END IF;
END;
/
--Example 4–34 NULL STATEMENT
AS Placeholder During Subprogram CREATION
CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER)
AUTHID DEFINER
AS
BEGIN -- Executable part starts
here
NULL;
--
Placeholder
-- (raises "unreachable
code" if warnings enabled)
END award_bonus;
--Example 4–35 NULL STATEMENT
IN ELSE Clause OF Simple CASE STATEMENT
CREATE OR REPLACE PROCEDURE print_grade (grade CHAR)
AUTHID DEFINER
AS
BEGIN
CASE grade
WHEN 'A'
THEN
DBMS_OUTPUT.PUT_LINE ('Excellent');
WHEN 'B'
THEN
DBMS_OUTPUT.PUT_LINE ('Very Good');
WHEN 'C'
THEN
DBMS_OUTPUT.PUT_LINE ('Good');
WHEN 'D'
THEN
DBMS_OUTPUT.PUT_LINE ('Fair');
WHEN 'F'
THEN
DBMS_OUTPUT.PUT_LINE ('Poor');
ELSE
NULL;
END CASE;
END;
/
BEGIN
print_grade ('A');
print_grade ('S');
END;
/