Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
What Happens If Variable Names Collide with Table/Column Names
What Happens If Variable Names Collide with Table/Column Names? - Oracle DBA FAQ - Working with Database Objects in PL/SQL
✍: FYIcenter.com
When a variable name collides with a column name, PL/SQL will use it as the variable if it is used where variable is allowed; It will be used as the column, if it is used where variable is not allowed but column is allowed. Here is a good example of variable names collide with column names:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL); Table created. DECLARE id NUMBER; first_name CHAR(10); BEGIN id := 29; first_name := 'Bob'; INSERT INTO student VALUES(id, first_name, 'Henry'); first_name := 'Joe'; INSERT INTO student VALUES(id+1, first_name, 'Bush'); first_name := 'Fyi'; UPDATE student SET first_name = first_name WHERE id = 29; -- 1 row updated -- Both 'first_name's are treated as column names UPDATE student SET first_name = first_name WHERE id = id+1; -- 0 rows updated -- Both "id"s are treated as variable names DELETE FROM student WHERE id = id; -- 2 rows deleted END; / SELECT * FROM student; 0 rows selected
Noticed that "id = id+1" in the WHERE clause will be evaluated to FALSE, because both "id"s are treated as variables. Similarly "id = id" will also be evaluated to TRUE in the WHERE clause. But both "first_name"s in the SET clause will be treated as column names.
2007-04-28, 4860👍, 0💬
Popular Posts:
How To Test Transaction Isolation Levels? - MySQL FAQs - Transaction Management: Commit or Rollback ...
How can method defined in multiple base classes with same name be invoked from derived class simulta...
How To Check the Oracle TNS Settings? - Oracle DBA FAQ - ODBC Drivers, DSN Configuration and ASP Con...
How can we format data inside DataGrid? Use the DataFormatString property.
How To Create an Add-to-Netvibes Button on Your Website? - RSS FAQs - Adding Your Feeds to RSS News ...