DEV Community: AntDB

AntDB-Oracle Compatibility Developer’s Manual P4–30

AntDB — Thu, 28 Sep 2023 08:30:10 +0000

Loop Statements

You can use the LOOP, EXIT, CONTINUE, WHILE, and FOR statements to make the SPL program repeat a sequence of commands.

LOOP

statements

END LOOP;

The LOOP statement defines an unconditional loop that loops indefinitely and terminates when an EXIT or RETURN command is encountered.

EXIT

EXIT [ WHEN expression ];

The purpose of this statement is to end the innermost loop and then execute the statement that follows END LOOP. If the WHEN clause is present, then the loop can be exited only if the specified condition is true, otherwise the flow will go to the statement following EXIT. You can use the EXIT statement to exit from all types of loops early, and there is no restriction on the use of this statement in unconditional loops.

The following is a simple example of a loop that repeats 10 times and then exits the loop using the EXIT statement.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_1 ()
IS
    v_counter NUMBER(2);
BEGIN
    v_counter := 1;
    LOOP
        EXIT WHEN v_counter > 10;
        DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END;
/
\set PLSQL_MODE off

The following is the output of the program.

postgres=# select LOOP_PRO_1();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_1
------------

(1 row)

AntDB-Oracle Compatibility Developer's Manual P4–29

AntDB — Thu, 28 Sep 2023 08:26:28 +0000

Passive search CASE statement

Passive search CASE statements use one or more Boolean expressions to determine which statement to execute.

CASE WHEN boolean-expression THEN

statements

[ WHEN boolean-expression THEN

statements

[ WHEN boolean-expression THEN

statements ] ...]

[ ELSE

statements ]

END CASE;

The boolean-expression is computed in the order it appears in the CASE statement. When the first boolean-expression that evaluates to true is encountered, then the statement in the corresponding THEN clause is executed, and the process runs after the keyword END CASE. If no boolean-expression is evaluated as true, then the statement after ELSE is executed. If there is no boolean-expression that evaluates to true and no ELSE clause, an exception is thrown.

The following example assigns department names and locations to variables based on department numbers using a passive search CASE statement.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_4()
IS
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_deptno emp.deptno%TYPE;
    v_dname VARCHAR2(20);
    v_loc VARCHAR2(20);
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
    DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
            CASE
                WHEN v_deptno = 10 THEN v_dname := 'Accounting';
                    v_loc := 'New York';
                WHEN v_deptno = 20 THEN v_dname := 'Research';
                    v_loc := 'Dallas';
                WHEN v_deptno = 30 THEN v_dname := 'Sales';
                    v_loc := 'Chicago';
                WHEN v_deptno = 40 THEN v_dname := 'Operations';
                    v_loc := 'Boston';
                ELSE v_dname := 'unknown';
                    v_loc := '';
            END CASE;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of this program.

postgres=# select emp_case_4();
NOTICE:  EMPNO ENAME DEPTNO DNAME  LOC
NOTICE:  ----- ------- ------ ---------- ---------
NOTICE:  1001 SMITH      10 Accounting     New York
NOTICE:  1002 ALLEN      30 Sales          Chicago
NOTICE:  1003 SMITH      20 Research       Dallas
NOTICE:  1004 JACK       20 Research       Dallas
NOTICE:  1005 JANE       40 Operations     Boston
NOTICE:  1006 MILLER     20 Research       Dallas
NOTICE:  1007 ADAMS      30 Sales          Chicago
NOTICE:  1008 JONES      10 Accounting     New York
NOTICE:  1009 FORD       30 Sales          Chicago
 EMP_CASE_4
------------

(1 row)

AntDB-Oracle Compatibility Developer's Manual P4–28

AntDB — Thu, 28 Sep 2023 08:24:04 +0000

Active-select CASE statement

The active selective CASE statement tries to match the expression specified in the WHEN clause. When a match is found, the corresponding statement is executed.

CASE selector-expression

WHEN match-expression THEN

statements

[ WHEN match-expression THEN

statements

[ WHEN match-expression THEN

statements ] ...]

[ ELSE

statements ]

END CASE;

selector-expression returns a value that is compatible with each match-expression data type. The match-expression is evaluated in the order it appears in the CASE statement. statements are SPL statements, each statement is terminated by a semicolon. When the first match-expression is encountered that is equal to the value of the parameter selector-expression, the statement in the corresponding THEN clause is executed, and the process then runs after the END CASE keyword. If there is no match here, then the statement after ELSE is executed. If no matching expression is found and there is no ELSE clause, then an exception is thrown.

The following example assigns a department name and geographic location to a variable based on a department number using the active select CASE statement:

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_3()
IS
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_deptno emp.deptno%TYPE;
    v_dname VARCHAR2(20);
    v_loc VARCHAR2(20);
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp order by 1;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
    DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        CASE v_deptno
            WHEN 10 THEN v_dname := 'Accounting';
            v_loc := 'New York';
            WHEN 20 THEN v_dname := 'Research';
            v_loc := 'Dallas';
            WHEN 30 THEN v_dname := 'Sales';
            v_loc := 'Chicago';
            WHEN 40 THEN v_dname := 'Operations';
            v_loc := 'Boston';
            ELSE v_dname := 'unknown';
            v_loc := '';
        END CASE;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of this program:

postgres=# select emp_case_3();
NOTICE:  EMPNO ENAME DEPTNO DNAME  LOC
NOTICE:  ----- ------- ------ ---------- ---------
NOTICE:  1001 SMITH      10 Accounting     New York
NOTICE:  1002 ALLEN      30 Sales          Chicago
NOTICE:  1003 SMITH      20 Research       Dallas
NOTICE:  1004 JACK       20 Research       Dallas
NOTICE:  1005 JANE       40 Operations     Boston
NOTICE:  1006 MILLER     20 Research       Dallas
NOTICE:  1007 ADAMS      30 Sales          Chicago
NOTICE:  1008 JONES      10 Accounting     New York
NOTICE:  1009 FORD       30 Sales          Chicago
 EMP_CASE_3
------------

(1 row)

AntDB-Oracle Compatibility Developer’s Manual P4–27

AntDB — Thu, 28 Sep 2023 08:21:54 +0000

CASE expression

The CASE expression returns a numeric value that is used in place of the CASE expression in an expression.

CASE expressions come in two formats. One is called a passive search CASE expression, and the other is an active selection CASE expression.

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,10);
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,40);
INSERT INTO emp VALUES (1006,'MILLER','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'ADAMS','CLERK',7912,'19-DEC-80',800,452,30);
INSERT INTO emp VALUES (1008,'JONES','CLERK',7912,'19-DEC-80',800,2500,10);
INSERT INTO emp VALUES (1009,'FORD','CLERK',7912,'19-DEC-80',800,1500,30);

Active-selective CASE expressions

Active-select CASE expressions use an expression called a selector to match one or more expressions specified in the WHEN clause. The result is an expression that is compatible with the CASE expression type. If there is a match, then the CASE expression returns the value in the corresponding THEN clause. If there is no match here, then the value following the ELSE clause is returned. If the ELSE clause is omitted, then the CASE expression returns the null value.

CASE selector-expression

WHEN match-expression THEN

result

[ WHEN match-expression THEN

result

[ WHEN match-expression THEN

result ] ...]

[ ELSE

result ]

END;

match-expression is computed based on the order in which it appears in CASE expressions. The result is an expression compatible with the CASE expression type. When the first match-expression equal to the selector-expression is encountered, the result corresponding to the THEN clause is returned as the value of the CASE expression. If no match-expression is equal to selector-expression, then the result of the argument following ELSE is returned. If ELSE is not specified, then the CASE expression returns the null value.

The following example uses an active-selector CASE expression to assign a department name to a variable based on the department number.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_1()
IS
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_deptno emp.deptno%TYPE;
    v_dname VARCHAR2(20);
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp order by 1;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
    DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        v_dname :=
            CASE v_deptno
                WHEN 10 THEN 'Accounting'
                WHEN 20 THEN 'Research'
                WHEN 30 THEN 'Sales'
                WHEN 40 THEN 'Operations'
                ELSE 'unknown'
            END;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of the program:

postgres=# select emp_case_1();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_1
------------

(1 row)

Passive search CASE expression

A passive-search CASE expression is one or more Boolean expressions to confirm the result value and then return it.

CASE WHEN boolean-expression THEN

result

[ WHEN boolean-expression THEN

result

[ WHEN boolean-expression THEN

result ] ...]

[ ELSE

result ]

END;

The boolean-expression is computed according to the order in which it appears in the CASE expression. The result is the type-compatible expression in the CASE expression. When the first boolean-expression that evaluates to true is encountered, then result is returned as the value of the CASE expression in the corresponding THEN clause. If no true boolean-expression is encountered, then the value after ELSE is returned. If the ELSE clause is not specified, then the CASE expression returns null.

In the following example, a passive search CASE expression is used to assign a department name to a variable based on the department number.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_2()
IS
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_deptno emp.deptno%TYPE;
    v_dname VARCHAR2(20);
    CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp order by 1;
BEGIN
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
    DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
        EXIT WHEN emp_cursor%NOTFOUND;
        v_dname :=
            CASE
                WHEN v_deptno = 10 THEN 'Accounting'
                WHEN v_deptno = 20 THEN 'Research'
                WHEN v_deptno = 30 THEN 'Sales'
                WHEN v_deptno = 40 THEN 'Operations'
                ELSE 'unknown'
            END;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of the program:

postgres=# select emp_case_2();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_2
------------

(1 row)

CASE statement

The CASE statement executes one or more sets of statements when the specified search condition is true. The CASE statement is a separate statement by itself, and the CASE expressions discussed earlier appear in the CASE statement as part of the overall expression.

CASE statements come in two formats. One is called the passive search CASE statement and the other is called the active select CASE statement.

AntDB-Oracle Compatibility Developer’s Manual P4–26

AntDB — Thu, 28 Sep 2023 08:09:17 +0000

IF-THEN-ELSIF-ELSE

IF boolean-expression THEN

statements

[ ELSIF boolean-expression THEN

statements

[ ELSIF boolean-expression THEN

statements ] ...]

[ ELSE

statements ]

END IF;

IF-THEN-ELSIF-ELSE is used to execute the corresponding statement by detecting multiple conditions in the IF statement. In general, it is equivalent to the nested use of the IF-THEN-ELSE-IF-THEN command, with the difference that only one END IF statement is required.

The following example uses the IF-THEN-ELSIF-ELSE statement to calculate the number of employees in the $25,000 compensation range.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_4()
IS
    v_empno emp.empno%TYPE;
    v_comp NUMBER(8,2);
    v_lt_25K SMALLINT := 0;
    v_25K_50K SMALLINT := 0;
    v_50K_75K SMALLINT := 0;
    v_75K_100K SMALLINT := 0;
    v_ge_100K SMALLINT := 0;
    CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp order by 1;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_empno, v_comp;
        EXIT WHEN emp_cursor%NOTFOUND;
        IF v_comp < 25000 THEN
            v_lt_25K := v_lt_25K + 1;
        ELSIF v_comp < 50000 THEN
            v_25K_50K := v_25K_50K + 1;
        ELSIF v_comp < 75000 THEN
            v_50K_75K := v_50K_75K + 1;
        ELSIF v_comp < 100000 THEN
            v_75K_100K := v_75K_100K + 1;
        ELSE
            v_ge_100K := v_ge_100K + 1;
        END IF;
    END LOOP;
    CLOSE emp_cursor;
    DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
    DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
    DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
    DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
    DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
    DBMS_OUTPUT.PUT_LINE('100,000 and over : ' || v_ge_100K);
END;

/
\set PLSQL_MODE off

Here is the output of this program:

postgres=# select emp_test_4();
NOTICE:  Number of employees by yearly compensation
NOTICE:  Less than 25,000 : 4
NOTICE:  25,000 - 49,9999 : 3
NOTICE:  50,000 - 74,9999 : 1
NOTICE:  75,000 - 99,9999 : 1
NOTICE:  100,000 and over : 0
 EMP_TEST_4
------------

(1 row)

AntDB-Oracle Compatibility Developer’s Manual P4–25

AntDB — Wed, 27 Sep 2023 15:01:22 +0000

IF-THEN-ELSE IF

IF statements can be nested so that a different inner IF statement can be called depending on the conditional return value of the outer IF statement.

In the following example, the outer IF-THEN-ELSE statement tests whether an employee has a commission. The inner IF-THEN-ELSE statement tests whether the employee's total compensation is more or less than the company average.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_3()
IS
    v_empno emp.empno%TYPE;
    v_sal emp.sal%TYPE;
    v_comm emp.comm%TYPE;
    v_avg NUMBER(7,2);
    CURSOR emp_cursor IS SELECT empno, sal, comm FROM emp order by 1;
BEGIN
--
--  Calculate the average yearly compensation in the company
--
    SELECT AVG((sal + NVL(comm,0)) * 24) INTO v_avg FROM emp;
    DBMS_OUTPUT.PUT_LINE('Average Yearly Compensation: ' ||TO_CHAR(v_avg,'$999,999.99'));
    OPEN emp_cursor;
    DBMS_OUTPUT.PUT_LINE('EMPNO YEARLY COMP');
    DBMS_OUTPUT.PUT_LINE('----- -----------');
    LOOP
        FETCH emp_cursor INTO v_empno, v_sal, 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
            --
            --  Test if the employee's compensation with commission exceeds the average
            --
            IF (v_sal + v_comm) * 24 > v_avg THEN
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Exceeds Average');
            ELSE
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Below Average');
            END IF;
        ELSE
            --
            --  Test if the employee's compensation without commission exceeds the average
            --
            IF v_sal * 24 > v_avg THEN
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Exceeds Average');
            ELSE
                DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Below Average');
            END IF;
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

Note: We can also use the NVL function in the cursor's SELECT command to calculate the employee's annual compensation amount, which simplifies the logic of the program. The purpose of this example is to demonstrate that the IF statement can achieve the same function.

The following is the output of this program.

postgres=# select emp_test_3();
NOTICE:  Average Yearly Compensation: $  36,287.73
NOTICE:  EMPNO YEARLY COMP
NOTICE:  ----- -----------
NOTICE:  1001 $  39,741.60 Exceeds Average
NOTICE:  1002 $  45,600.00 Exceeds Average
NOTICE:  1003 $  19,200.00 Below Average
NOTICE:  1004 $  19,200.00 Below Average
NOTICE:  1005 $  19,200.00 Below Average
NOTICE:  1006 $  19,200.00 Below Average
NOTICE:  1007 $  30,048.00 Below Average
NOTICE:  1008 $  79,200.00 Exceeds Average
NOTICE:  1009 $  55,200.00 Exceeds Average
 EMP_TEST_3
------------

(1 row)

When using this form of IF statement, there is actually an IF statement nested inside the ELSE of the outer IF statement. Therefore, it is necessary to provide an END IF statement for each nested IF statement and an END IF statement for the outermost IF-ELSE.

AntDB-Oracle Compatibility Developer’s Manual P4–24

AntDB — Wed, 27 Sep 2023 14:58:52 +0000

IF-THEN-ELSE

IF boolean-expression THEN

statements

ELSE

statements

END IF;

If the return value of the conditional expression in the IF-THEN-ELSE statement is FALSE, then the statement after ELSE will be executed.

Now we have modified the previous example by replacing the IF-THEN statement with the IF-THEN-ELSE statement. This will display the text message 'Non-commission' when it detects that the employee has no commission.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_2()
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'));
        ELSE
            DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || 'Non-commission');
        END IF;
    END LOOP;
    CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

The following is the output of this program.

postgres=# select emp_test_2();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1003 Non-commission
NOTICE:  1004 Non-commission
NOTICE:  1005 Non-commission
NOTICE:  1006 Non-commission
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_2
------------

(1 row)

AntDB-Oracle Compatibility Developer’s Manual P4–23

AntDB — Wed, 27 Sep 2023 14:55:08 +0000

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)

AntDB-Oracle Compatibility Developer’s Manual P4–22

AntDB — Wed, 27 Sep 2023 14:53:22 +0000

Get the Result Status

There are some properties that can be used to confirm the execution of the command. SQL%FOUND is a boolean property that returns "true" when an INSERT, UPDATE, or DELETE command does a valid operation on a row, or when a SELECT INTO command takes out more than one row.

The following anonymous block inserts a row, and then displays the result of the insert operation.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert() 
IS
BEGIN
    INSERT INTO emp (empno,ename,job,sal,deptno) VALUES (9001, 'JONES', 'CLERK', 850.00, 40);
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Row has been inserted');
    END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_insert();
NOTICE:  Row has been inserted
 EMP_INSERT
------------

(1 row)

SQL%ROWCOUNT provides the number of valid operations performed on a row by the INSERT, UPDATE or DELETE commands. The following example updates the row inserted in the previous example and displays the value of SQL%ROWCOUNT.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
    UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9001;
    DBMS_OUTPUT.PUT_LINE('# rows updated: ' || SQL%ROWCOUNT);
END;

/
\set PLSQL_MODE off

postgres=# select emp_update();
NOTICE:  # rows updated: 1
 EMP_UPDATE
------------

(1 row)

SQL%NOTFOUND is the opposite of SQL%FOUND, and returns 'true' if the INSERT, UPDATE or DELETE command was unsuccessful on the record, or if the SELECT INTO command did not retrieve any data.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
    UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9000;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows were updated');
    END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_update();
NOTICE:  No rows were updated
 EMP_UPDATE
------------

(1 row)

AntDB-Oracle Compatibility Developer’s Manual P4–21

AntDB — Wed, 27 Sep 2023 14:50:22 +0000

Use the RETURNING INTO clause

A RETURNING INTO clause can be added after the INSERT, UPDATE, and DELETE commands. The purpose of this clause is to allow the SPL program to capture the latest inserted, modified, or deleted values of the INSERT, UPDATE, or DELETE clauses, respectively.

The following is the syntax of this clause.

{ insert | update | delete }

RETURNING { * | expr_1 [, expr_2 ] ...}

INTO { record | field_1 [, field_2 ] ...};

insert, update, and delete are valid INSERT, UPDATE, and DELETE commands, respectively. If '*' is specified, then the records successfully operated by INSERT, UPDATE or DELETE commands can be assigned to the fields to the right of the record type or INTO keyword. (Note that "*" is an extension of AntDB and is not Oracle compatible). expr_1, expr_2... are expressions calculated with the records operated by INSERT, UPDATE or DELETE commands. The final result of the calculation is assigned to the record or field to the right of the INTO keyword. record is the identifier of the record. The fields in this record must match the number and order of occurrence of the values returned in the RETURNING INTO clause, and be compatible with their data types. field_1, field_2,... are variables that must match the number and order of the set of values in the RETURNING INTO clause, and be compatible with their data types.

If the INSERT, UPDATE or DELETE command returns a result set with multiple rows, an SQLCODE exception of 01422 is thrown, indicating that the query returns multiple rows. If there are no rows in the result set, the variable following the INTO keyword will be set to a null value.

Note: There is another way to use the RETURNING INTO clause that allows a result set containing multiple rows to be returned to a collection by using the BULK COLLECT clause.

Example:

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 (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Create a stored procedure.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
    p_empno IN NUMBER,
    p_sal IN  NUMBER(7,2),
    p_comm IN NUMBER(7,2)
)
IS
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
    v_job emp.job%TYPE;
    v_sal emp.sal%TYPE;
    v_comm emp.comm%TYPE;
    v_deptno emp.deptno%TYPE;
BEGIN
    UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno
        RETURNING
            empno,
            ename,
            job,
            sal,
            comm,
            deptno
        INTO
            v_empno,
            v_ename,
            v_job,
            v_sal,
            v_comm,
            v_deptno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || v_empno);
        DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
        DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
        DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
        DBMS_OUTPUT.PUT_LINE('New Salary : ' || v_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission : ' || v_comm);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;
/
\set PLSQL_MODE off

The following is the call and output of this stored procedure.

postgres=# select  emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Department : 20
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

In the following example, we add a RETURNING INTO clause to this procedure that uses the record type.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
    r_emp emp%ROWTYPE;
BEGIN
    DELETE FROM emp WHERE empno = p_empno
        RETURNING
            empno,
            ename,
            job,
            sal,
            comm,
            deptno
        INTO
            r_emp.empno,
            r_emp.ename,
            r_emp.job,
            r_emp.sal,
            r_emp.comm,
            r_emp.deptno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || r_emp.empno);
        DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
        DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
        DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
        DBMS_OUTPUT.PUT_LINE('Commission : ' || r_emp.comm);
        DBMS_OUTPUT.PUT_LINE('Department : ' || r_emp.deptno);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;
/
\set PLSQL_MODE off

The following is the result of the execution of this procedure.

postgres=# select  emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Salary : 6540
NOTICE:  Commission : 1200
NOTICE:  Department : 20
 EMP_DELETE
------------

(1 row)

AntDB-Oracle Compatibility Developer's Manual P4–20

AntDB — Tue, 26 Sep 2023 15:20:53 +0000

DELETE

The DELETE command in standard SQL can also be used in SPL programs. Expressions in the SPL language can also be used where expressions appear in the DELETE command in standard SQL. Therefore, SPL variables and parameters can be used to provide values for the delete operation.

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 (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Create a stored procedure.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
BEGIN
    DELETE FROM emp WHERE empno = p_empno;
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || p_empno);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
/
\set PLSQL_MODE off

The SQL%FOUND conditional expression returns "true" if a row is successfully deleted, otherwise it returns "false".

The following procedure performs a delete operation in the employee table.

postgres=# select emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
 EMP_DELETE
------------

(1 row)
postgres=# select * from emp where empno=7369;
 EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

AntDB-Oracle Compatibility Developer’s Manual P4–19

AntDB — Tue, 26 Sep 2023 15:16:23 +0000

INSERT

The INSERT command in standard SQL can also be used in SPL programs.

The same expressions that appear in the standard SQL INSERT command can also be used in SPL. Thus, SPL variables and parameters can be used to provide values for insert operations.

In the following example a procedure performs the operation of inserting the value passed in the calling program into the emp data table as a new employee record.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert (
    p_empno IN NUMBER(4),
    p_ename IN VARCHAR2(10),
    p_job IN VARCHAR2(9),
    p_mgr IN NUMBER(4),
    p_hiredate IN DATE,
    p_sal IN NUMBER(7,2),
    p_comm IN NUMBER(7,2),
    p_deptno IN NUMBER(2)
)
IS
BEGIN
    INSERT INTO emp VALUES (
        p_empno,
        p_ename,
        p_job,
        p_mgr,
        p_hiredate,
        p_sal,
        p_comm,
        p_deptno);

    DBMS_OUTPUT.PUT_LINE('Added employee...');
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name : ' || p_ename);
    DBMS_OUTPUT.PUT_LINE('Job : ' || p_job);
    DBMS_OUTPUT.PUT_LINE('Manager : ' || p_mgr);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || p_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || p_sal);
    DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
    DBMS_OUTPUT.PUT_LINE('Dept # : ' || p_deptno);
    DBMS_OUTPUT.PUT_LINE('----------------------');

END;
/
\set PLSQL_MODE off

If the procedure encounters an exception during execution, all modifications to the database will be automatically rolled back. The exception section with the WHEN OTHERS clause in this example catches all exceptions. Two variables are displayed as output, SQLCODE is a numeric value that identifies an exception encountered, and SQLERRM is a text message that explains the exception error that occurred.

The following is the output produced by the stored procedure execution.

postgres=# select emp_insert(9503,'PETERSON','ANALYST',7902,'31-MAR-05',5000,NULL,40);
NOTICE:  Added employee...
NOTICE:  Employee # : 9503
NOTICE:  Name : PETERSON
NOTICE:  Job : ANALYST
NOTICE:  Manager : 7902
NOTICE:  Hire Date : 2005-03-31 00:00:00
NOTICE:  Salary : 5000
NOTICE:  Commission :
NOTICE:  Dept # : 40
NOTICE:  ----------------------
 EMP_INSERT
------------

(1 row)

postgres=# select * from emp WHERE empno = 9503;
 EMPNO |  ENAME   |   JOB   | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+----------+---------+------+---------------------+------+------+--------
  9503 | PETERSON | ANALYST | 7902 | 2005-03-31 00:00:00 | 5000 |      |     40
(1 row)

Note: INSERT commands can be included in a FORALL statement. The FORALL statement allows an INSERT command to insert multiple new rows using the values provided by one or more collections.

UPDATE

The UPDATE command in standard SQL can also be used in SPL programs.

Expressions in the SPL language can also be used where expressions appear in the UPDATE command in standard SQL. Therefore, SPL variables and parameters can be used to provide values for update operations.

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 (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Create a stored procedure.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
    p_empno IN NUMBER,
    p_sal IN NUMBER(7,2),
    p_comm IN NUMBER(7,2)
)
IS
BEGIN
    UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
        DBMS_OUTPUT.PUT_LINE('New Salary : ' || p_sal);
        DBMS_OUTPUT.PUT_LINE('New Commission : ' || p_comm);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
    END IF;
END;
/
\set PLSQL_MODE off

The SQL%FOUND conditional expression returns "true" if a row is successfully updated, otherwise it returns "false".

The following procedure performs the update operation in the employee table.

postgres=# select emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

postgres=# select * from emp where empno=7369;
 EMPNO | ENAME |  JOB  | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+-------+-------+------+---------------------+------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 6540 | 1200 |     20
(1 row)

Note: You can include the UPDATE command in a FORALL statement. The FORALL statement allows a single UPDATE command to update multiple rows with the values provided by one or more collections.