Collections:
Variable Names Collide with Column Names in Oracle
What Happens If Variable Names Collide with Table/Column Names in Oracle?
✍: 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.
⇒ Name Conflicts between Variables and Columns in Oracle
⇐ Use Variables in SQL Statements in Oracle
2018-09-24, 1762🔥, 0💬
Popular Posts:
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...