DELETE v15

You can use the DELETE command available in the SQL language in SPL programs.

You can use an expression in the SPL language wherever an expression is allowed in the SQL DELETE command. Thus, you can use SPL variables and parameters to supply values to the delete operation.

CREATE OR REPLACE PROCEDURE emp_delete (
    p_empno         IN emp.empno%TYPE
)
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;

The SQL%FOUND conditional expression returns TRUE if a row is deleted, FALSE otherwise. See Obtaining the result status for a discussion of SQL%FOUND and other similar expressions.

This example deletes an employee using this procedure:

EXEC emp_delete(9503);

Deleted Employee # : 9503

SELECT * FROM emp WHERE empno = 9503;

 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)