PL/SQL : Can we have same name for variable and column
In the tutorial i learned that we cannot have same name of variable and column
so i tried this
set SERVEROUTPUT ON; declare n1 number; name varchar(10); begin null; DBMS_OUTPUT.PUT_LINE('sparsh' || TO_CHAR(45)); select name into name from Employee; DBMS_OUTPUT.PUT_LINE(name); END; /
Here i have name column in the table and with the same name i declare the variable.
As per the tutorials it should give some error. But it is working fine
Could you please help me in order to clarify.
So can we have variable with same name of column?
The tutorial is slightly wrong. It is possible to have variables with the same name in SQL and PL/SQL but it is almost certainly a bad idea to do so.
Using variables with the same name will lead to confusing scope issues. The PL/SQL block will correctly use the PL/SQL
name for the
INTO clause, but it would also use it other places you might not expect. For example, if you tried to use it in the
WHERE clause the SQL
name would be used instead.
drop table employee; create table employee(id number, name varchar2(100)); insert into employee values (1, 'Alice'); declare name varchar(10) := 'Bob'; begin select name into name from Employee where name = name; dbms_output.put_line(name); END; / OUTPUT: Alice
This is the real reason why it's a good practice to name local variables
V_ for "variable", and
P_ for "parameter". It's not because of some stupid Systems Hungarian notation rules. (So don't also start naming your variables
_NUM_, etc!) It's because in practice you want to use the same names in PL/SQL and SQL but you don't want to get them confused.
PL/SQL Name Resolution, You could declare a variable that has the same name as a table, a column, or a view. The PL/SQL compiler won't get confused, but you might, and your SQL If the SQL subsystem cannot resolve a name in the scope of the SQL statement, then it sends the name back to the PL/SQL compiler. The name is called an escaped identifier. The PL/SQL compiler tries to resolve the escaped identifier. First, the compiler tries to resolve the identifier in the scope of the PL/SQL unit.
declare name varchar(10) := 'Bob'; begin select name into name from Employee where **id= id**; dbms_output.put_line(name); END; /
I am using the same example given by Jon. See the condition where id = id. If you use a variable with a name same as of the column name.It would not throw any error but it would certainly astonish you with a huge and unexpectedly large dataset. In Conditional predicates column names get prioritized over variable names. Hence in this case id = id both would be considered as table columns and condition will be evaluated into TRUE for all rows.
There is no such rule while choosing the name of the variables.but it's better to keep the standards.
PL/SQL : Can we have same name for variable and column, The developer can automatically define a variable with the same data The %TYPE keyword is preceded by the table/cursor name and column/variable name. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect.
Should this be something SQL tools like Toad should be picking up on? e.g. if you try to run a query like this it would fail with a message to say something like 'Declared variable name should not be the same as Column name in query'? Or should it be a SQL error thrown? I can't think of any situation where you would want to do this and want the outcome so surely it shouldn't be possible to do it?
SQL-01: Qualify PL/SQL variables with their scope names when , Names. When we create objects, variables, and columns, we should avoid almost identical, but these two objects obviously can't share the same name. In PL/SQL, we often have a variable that contains the same information as a column. In a DATA step, the program data vector does not allow two variables to have the same name. SQL is different. The namespace for a query can have multiple instances of the same column name. Usually, the SQL processor will generate an error message if there is an ambiguous reference to a column name. There are still situations, however, where it is quite possible to get into trouble.
Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, , END; You can reference local PL/SQL program data (PL/SQL variables and PROCEDURE improve_QOL IS /* Local variable with same name as column: In this chapter, we will discuss Variables in Pl/SQL. A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and the layout of the variable's memory; the range of values that can be stored within that memory and the set of operations
Pro Oracle SQL Development: Best Practices for Writing Advanced , In PL/SQL, the select list of a SELECT may contain PL/ SQL variables, END; We can refer local PL/SQL program data (PL/SQL variables and constants), Be careful about naming identifiers when you mix PL/ SQL variables in with database columns. It is a common practice to give a variable the same name as the column PL/SQL tables help you move bulk data. They can store columns or rows of Oracle data, and they can be passed as parameters. So, PL/SQL tables make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms.
Oracle PL/SQL Programming, Its fields have the same names and data types as the columns in the Dept table. DECLARE Emprec dept%ROWTYPE; — declare record variable You need to use dot notation Note The PL/SQL cursor will be discussed in the later chapters. The only naming conventions I have are in PLSQL. local variables must start with L_ parameters must start with P_ global package variables must start with G_ That is so a Parameter ENAME won't get confused with a column named ENAME in a query -- I never would have a column p_ename -- so there is no ambiguity.