Use the DELETE statement in SPL programs - PolarDB

In SPL programs, the SQL DELETE statement works the same way it does in plain SQL. SPL variables and parameters can be used to supply values to the delete operation.

How it works

After a DELETE statement runs, the implicit cursor attribute SQL%FOUND reports whether any rows were affected:

Value	Condition
`TRUE`	At least one row was deleted
`FALSE`	No rows matched the `WHERE` clause

Example

The following procedure deletes an employee record by employee number and prints a confirmation message.

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;

Run the procedure and verify the result:

EXEC emp_delete(9503);

Output:

Deleted Employee # : 9503

Query the table to confirm the deletion:

SELECT * FROM emp WHERE empno = 9503;

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