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
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

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

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

Boolean Values Only the values TRUE, FALSE, & NULL can be assigned to a Boolean variable. For illustration, given the declaration DECLARE done BOOLEAN; the following statements

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

Question 1 . Compare SQL and PL/SQL Question 2 . Write a database trigger to implement the following check condition                          Given the following table

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera