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
Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Parameter & Keyword Description: function_name: The user-defined function is identifying by that keyword. parameter_name: This identifies the formal parameter that

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

Cursor Attributes   The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement. Syntax:

WHILE-LOOP The WHILE-LOOP statement relates a condition with the series of statements enclosed by the keywords LOOP and END LOOP, as shown: WHILE condition LOOP sequence_of_sta

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Using SET TRANSACTION You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a

Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.