Live ORACLE

DROP TABLE employees2 CASCADE CONSTRAINTS PURGE;

CREATE TABLE employees2
 AS
 SELECT last_name
 FROM   employees;


<<main>>

DECLARE

 last_name    VARCHAR2(10) := 'King';

  v_last_name  VARCHAR2(10) := 'King';

BEGIN

 -- deletes everyone, because both LAST_NAMEs refer to the column

 DELETE FROM employees2

 WHERE       last_name = last_name;



 dbms_output.Put_line('Deleted '

                      ||SQL%ROWCOUNT

                      ||' rows.');



 ROLLBACK;



 -- OK, column and variable have different names

 DELETE FROM employees2

 WHERE       last_name = v_last_name;



 dbms_output.Put_line('Deleted '

                      ||SQL%ROWCOUNT

                      ||' rows.');



 ROLLBACK;



 -- OK, block name specifies that 2nd last_name is a variable

 DELETE FROM employees2

 WHERE       last_name = Main.last_name;



 dbms_output.Put_line('Deleted '

                      ||SQL%ROWCOUNT

                      ||' rows.');



 ROLLBACK;

END;

/

Using a Block Label for Name Resolution