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;
/
