AntDB-Oracle Compatibility Developer’s Manual P4–23

Programming Language Control Structures

The following sections describe how the SPL programming language implements a fully process-oriented feature complement to standard SQL.

IF statement

We can use IF commands to execute statements based on specified conditions. SPL provides four ways of doing IF.

IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSE IF
IF ... THEN ... ELSIF ... THEN ... ELSE

Prepare test data.

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1006,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'JANE','CLERK',7912,'19-DEC-80',800,452,20);
INSERT INTO emp VALUES (1008,'JANE','CLERK',7912,'19-DEC-80',800,2500,20);
INSERT INTO emp VALUES (1009,'JANE','CLERK',7912,'19-DEC-80',800,1500,20);

IF-THEN

IF boolean-expression THEN

statements

END IF;

The IF-THEN statement is the simplest type of IF statement. If the condition is true, the statements between THEN and END IF will be executed; otherwise, they will not be executed.

In the following example, the IF-THEN statement is used to test and display employees with commissions.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_1()
IS
    v_empno emp.empno%TYPE;
    v_comm emp.comm%TYPE;
    CURSOR emp_cursor IS SELECT empno, comm FROM emp order by 1;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
    DBMS_OUTPUT.PUT_LINE('----- -------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_comm;
        EXIT WHEN emp_cursor%NOTFOUND;
        --
        --  Test whether or not the employee gets a commission
        --
        IF v_comm IS NOT NULL AND v_comm > 0 THEN
            DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of this program.

postgres=# select emp_test_1();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_1
------------

(1 row)