PL/SQL objects are precompiled. All dependencies are checked prior to execution, making program execution much faster. Dependencies are not related to data.
They are on other database objects, such as tables, views, synonyms, and other program structures. As such, DML that is run in a PL/SQL block stands no chance of changing a dependency that would cause a program failure.
DDL, on the other hand, which supports 1) CREATE, 2) DROP, and 3) ALTER commands, as well as permission control statements GRANT and REVOKE, can change the dependencies during execution, if allowed.
For example, if we have a block that first drops a table and then attempts to update that same table, it would of course fail to execute properly.
That dependency cannot be checked ahead of time, though. Until the time of execution, the UPDATE would look as if it would be successful, since the table currently exists.
It fails only when the block is run because of the dropped object. DDL statements are therefore not allowed directly in PL/SQL.
DML statements require an explicit COMMIT before changes become permanent. DML also supports ROLLBACK and SAVEPOINT to revert changes prior to commit when they should not be permanent
DELETE
DELETE statements remove data, following the same transaction rules as INSERTs and UPDATEs. Syntax for a DELETE statement is
DELETE FROM table_name
[WHERE where_clause | WHERE CURRENT OF cursor]
The table_name can be any table, synonym, or updatable view where the user has DELETE permissions. If no WHERE clause is provided, all records will be deleted. The where_clause can be any column in the table compared to any expression. The WHERE CURRENT OF clause works with UPDATEs and DELETEs, and says to operate against the current record from the cursor.
The following example performs a DELETE from the AUTHORS table:
Available online as part of Delete.sql
SET SERVEROUTPUT ON
DECLARE
v_author AUTHORS%ROWTYPE;
BEGIN
SELECT *
INTO v_author
FROM authors
WHERE id = 54;
DELETE FROM authors
WHERE id = v_author.id;
DBMS_OUTPUT.PUT_LINE('Author '||v_author.first_name
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
/
The delete is successful:
Author Charles Moffett has been deleted
1) Records can be deleted from object tables by two methods. The first method uses a WHERE clause, which is not a sub-query.
2) For the second method you must specify a subquery within the WHERE clause.