Delete a row and output referenced value : Delete Data « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Delete Data

Delete a row and output referenced value

   

SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL>
SQL> DECLARE
  2     v_emp emp%ROWTYPE;
  3  BEGIN
  4     SELECT * INTO v_emp FROM emp WHERE id = 54;
  5
  6     DELETE FROM emp WHERE id = v_emp.id;
  7
  8     DBMS_OUTPUT.PUT_LINE('emp '||v_emp.fname||' '||v_emp.lname||' has been deleted');
  9
 10     COMMIT;
 11
 12  EXCEPTION
 13     WHEN OTHERS
 14     THEN
 15        DBMS_OUTPUT.PUT_LINE(SQLERRM);
 16        ROLLBACK;
 17  END;
 18  /
ORA-01403: no data found

PL/SQL procedure successfully completed.

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

Table dropped.








Related examples in the same category

1.DELETE statement can be used within PL/SQL programs to delete a row or a set of rows
2.DELETE statement in PL/SQL block
3.Delete data statement with WHERE clauses.
4.Delete statement with variable
5.Delete employee with stored procedure
6.Create dynamic delete from command
7.Use procedure to delete departments