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
Write a program to implement the inverted file shown in the slides (Simple Index file, LabelID file and Data file).  Use the Avail_List to point at the deleted Label IDs so that th

User-Defined Exceptions The PL/SQL defines the exceptions of your own. Dissimilar to the predefined exceptions, the user-defined exceptions should be declared and should be rai

Oracle 11 G new features associated with this release:- Enhanced ILM  - Information Lifecycle Management (ILM) has been around for the almost 10 years, but Oracle has made

Effects of NULL The numeric variable X, perhaps of type INTEGER, might be assigned NULL. In that case the result of evaluating X + 1 is NULL, and so SET Y = X + 1 assigns NULL

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must