Name resolution-naming conventions, PL-SQL Programming

Name Resolution

In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g. the DELETE statement removes all the employees from the emp table, not just ’KING’, as Oracle suppose that both enames in the WHERE clause refer to the


database column:
DECLARE
ename VARCHAR2(10) := ’KING’;
BEGIN
DELETE FROM emp WHERE ename = ename;
In such cases, to avoid the uncertainty, prefix the names of the local variables and formal parameters with my_, as shown below:

DECLARE
my_ename VARCHAR2(10);
Or, use a block label to qualify references, as in
<

>
DECLARE
ename VARCHAR2(10) := ’KING’;
BEGIN
DELETE FROM emp WHERE ename = main.ename;

The next illustration shows that you can use a subprogram name to qualify references to a local variables and formal parameters:

FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS
job CHAR(10);
BEGIN
SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job;

Posted Date: 10/3/2012 4:57:19 AM | Location : United States







Related Discussions:- Name resolution-naming conventions, Assignment Help, Ask Question on Name resolution-naming conventions, Get Answer, Expert's Help, Name resolution-naming conventions Discussions

Write discussion on Name resolution-naming conventions
Your posts are moderated
Related Questions
Example of Foreign Key Constraint Example: Alternative formulation for 6.3 as a foreign key constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_take_exam

IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

Comparison Operators Usually, you use the comparison operators in the WHERE clause of a data manipulation statement to form the predicates, that compare one expression to anot

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Parameter and Keyword Description: cursor_name: This identifies an explicit cursor formerly declared within the present scope. cursor_variable_name: These identif

Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN

Advantages of Invoker Rights The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In su

Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security l