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)
Top comments (0)