AntDB-Oracle Compatibility Developer's Manual P4–20

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)