How DELETEs work with PL/SQL : Delete « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Delete

SQL>
SQL> CREATE TABLE authors (
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );

Table created.

SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (1, 'Marlene', 'Theriault');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (2, 'Rachel', 'Carmichael');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (3, 'James', 'Viscusi');

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> DECLARE
  2
  3     v_author AUTHORS%ROWTYPE;
  4
  5  BEGIN
  6
  7     SELECT *
  8     INTO v_author
  9     FROM authors
 10     WHERE id = 1;
 11
 12     DELETE FROM authors
 13     WHERE id = v_author.id;
 14
 15     DBMS_OUTPUT.PUT_LINE('Author '||v_author.first_name
 16                          ||' '||v_author.last_name
 17                          ||' has been deleted');
 18
 19     COMMIT;
 20
 21  EXCEPTION
 22     WHEN OTHERS
 23     THEN
 24        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 25        ROLLBACK;
 26  END;
 27  /
Author Marlene Theriault has been deleted

PL/SQL procedure successfully completed.

SQL>
SQL> set escape off
SQL>
SQL> drop table authors;

Table dropped.