Overview
ALTIBASE HDB supports use of the following control flow statements in the stored procedures
- The IF and CASE conditional statements
- The LOOP, WHILE and FOR loop constructs, which cause multiple statements to be repeatedly
executed - The EXIT and CONTINUE statements, which are used to control the iteration of loops
- The NULL statement, which indicates that nothing is to be executed
- The GOTO statement, which is used to transfer control to a particular point
The functionalities of control flow statement in the ORACLE PL/SQL have so much similarity to that of the ALTIBASE Stored Procedures as shown below.
Example
ORACLE | ALTIBASE HDB | Comments |
|---|
CREATE OR REPLACE PROCEDURE PROC1
AS
sales NUMBER(8,2) := 10100;
quota NUMBER(8,2) := 10000;
bonus NUMBER(6,2);
emp_id NUMBER(6) := 120;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
UPDATE employees SET salary = salary + bonus WHERE eno = emp_id;
END IF;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
sales NUMBER(8,2) := 10100;
quota NUMBER(8,2) := 10000;
bonus NUMBER(6,2);
emp_id NUMBER(6) := 120;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
UPDATE employees SET salary = salary + bonus WHERE eno = emp_id;
END IF;
END;
/ | IF-THEN Statement |
CREATE OR REPLACE PROCEDURE PROC1
AS
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;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN SYSTEM_.PRINTLN('Excellent');
WHEN 'B' THEN SYSTEM_.PRINTLN('Very Good');
WHEN 'C' THEN SYSTEM_.PRINTLN('Good');
WHEN 'D' THEN SYSTEM_.PRINTLN('Fair');
WHEN 'F' THEN SYSTEM_.PRINTLN('Poor');
ELSE SYSTEM_.PRINTLN('No such grade');
END CASE;
END;
/ | CASE-WHEN Statement |
CREATE OR REPLACE PROCEDURE PROC1
AS
credit_rating NUMBER := 0;
BEGIN
LOOP
credit_rating := credit_rating + 1;
IF credit_rating > 3 THEN
EXIT; -- exit loop immediately
END IF;
END LOOP;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
IF credit_rating > 3 THEN
RETURN; -- use RETURN not EXIT when outside a LOOP
END IF;
DBMS_OUTPUT.PUT_LINE ('Credit rating: ' || TO_CHAR(credit_rating));
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
credit_rating NUMBER := 0;
BEGIN
LOOP
credit_rating := credit_rating + 1;
IF credit_rating > 3 THEN
EXIT; -- exit loop immediately
END IF;
END LOOP;
-- control resumes here
SYSTEM_.PRINTLN ('Credit rating: ' || TO_CHAR(credit_rating));
IF credit_rating > 3 THEN
RETURN; -- use RETURN not EXIT when outside a LOOP
END IF;
SYSTEM_.PRINTLN ('Credit rating: ' || TO_CHAR(credit_rating));
END;
/ | EXIT Statement |
CREATE OR REPLACE PROCEDURE PROC1
ASs 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 a bunch of 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;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
s INTEGER := 0;
i INTEGER := 0;
j INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- sum a bunch of products
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
SYSTEM_.PRINTLN('The sum of products equals: ' || TO_CHAR(s));
END;
/ | EXIT With Labeled Loops |
CREATE OR REPLACE PROCEDURE PROC1
AS
p NUMBER := 0;
BEGIN
FOR k IN 1..500 LOOP -- calculate pi with 500 terms
p := p + ( ( (-1) ** (k + 1) ) / ((2 * k) - 1) );
END LOOP;
p := 4 * p;
DBMS_OUTPUT.PUT_LINE( 'pi is approximately : ' || p ); -- print result
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
p NUMBER := 0;
BEGIN
FOR k IN 1 .. 500 LOOP -- calculate pi with 500 terms
p := p + ( ( (-1) * (k + 1) ) / ((2 * k) - 1) );
END LOOP;
p := 4 * p;
SYSTEM_.PRINTLN( 'pi is approximately : ' || p ); -- print result
END;
/ | Simple FOR..LOOP Statement |
CREATE OR REPLACE PROCEDURE PROC1
AS
v_employees employees%ROWTYPE; -- declare record variable
CURSOR c1 is SELECT * FROM employees;
BEGIN
OPEN c1; -- open the cursor before fetching
-- An entire row is fetched into the 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;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
v_employees employees%ROWTYPE; -- declare record variable
CURSOR c1 is SELECT * FROM employees;
BEGIN
OPEN c1; -- open the cursor before fetching
-- An entire row is fetched into the 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;
/ | EXIT in a LOOP |
CREATE OR REPLACE PROCEDURE PROC1
AS
v_last_name VARCHAR2(25);
v_emp_id NUMBER(6) := 120;
BEGIN
<<get_name>>
SELECT e_lastname INTO v_last_name FROM employees
WHERE eno = 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; -- branch to enclosing block
END IF;
END;
END;
/ | CREATE OR REPLACE PROCEDURE PROC1
AS
v_last_name VARCHAR2(25);
v_emp_id NUMBER(6) := 120;
BEGIN
<<get_name>>
SELECT e_lastname INTO v_last_name FROM employees
WHERE eno = v_emp_id;
BEGIN
SYSTEM_.PRINTLN (v_last_name);
v_emp_id := v_emp_id + 5;
IF v_emp_id < 120 THEN
GOTO get_name; -- branch to enclosing block
END IF;
END;
END;
/ | GOTO Statement to Branch an Enclosing Block |
CREATE OR REPLACE PROCEDURE proc1
AS
v_job_id VARCHAR2(10);
v_emp_id NUMBER(6) := 110;
BEGIN
SELECT emp_job INTO v_job_id FROM employees
WHERE emp_job = v_emp_id;
IF v_job_id = 'SA_REP' THEN
UPDATE employees SET salary = salary * 1.2;
ELSE
NULL; -- do nothing if not a sales representative
END IF;
END;
/ | CREATE OR REPLACE PROCEDURE proc1
AS
v_job_id VARCHAR2(10);
v_emp_id NUMBER(6) := 110;
BEGIN
SELECT emp_job INTO v_job_id FROM employees
WHERE emp_job = v_emp_id;
IF v_job_id = 'SA_REP' THEN
UPDATE employees SET salary = salary * 1.2;
ELSE
NULL; -- do nothing if not a sales representative
END IF;
END;
/ | NULL Statement |