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
We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.

UPDATE Command- SQL Loosely speaking, UPDATE changes some of the column values of some existing rows of its target table. Thus, although some rows disappear from the target an

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

Package STANDARD package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available a

TYPES OF EVOLUTION - Sequential evolution                  :                    Minor changes in the gene pool of a population from one generation to the next, with the resul