It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

'&' vs '&&' in ORACLE

“&” is used to create a temporary substitution variable. You will be prompted to enter the value every time the variable is referenced.
“&&” is used to create a permanent substitution variable. You need to enter the value only once.
1. Using ‘&’
SELECT employee_name FROM employee
    WHERE emp_id ='&emp'
Every time, you execute this select statement, you will have to pass the value of emp.

2. Using ‘&&’
Consider the following procedure.

SQL> set serveroutput on;
SQL> CREATE OR REPLACE PROCEDURE test_substitution AS
   emp_name VARCHAR2(10);
   emp_designation VARCHAR2(10);
   BEGIN
   SELECT employee_name INTO emp_name FROM employee
   WHERE emp_id ='&&emp';
   dbms_output.put_line(emp_name);
   SELECT employee_desg INTO emp_designation FROM employee
   WHERE emp_id='&emp';
   dbms_output.put_line(emp_designation);
   END;
 /
Procedure created

This procedure fetches the employee_name and employee_desg from the employee table for a particular emp_id.
It will prompt for the emp_id only once as ‘&emp’ is used. In the next select statement,although a substitution variable is used, you will not be prompted to enter a value for emp.
The next time you execute the procedure,it will not prompt for the value to be passed.
If you need to pass a different value each time you run the procedure, then you will have to undefine the variable emp.
SQL> undefine emp;

AND then compile the procedure again. This will prompt for a new value to be passed.
If you just undefine the variable and execute the procedure, it will not prompt for a new value.
It is important to undefine and then recompile the procedure.

0 comments:

You Might Also Like

Related Posts with Thumbnails

Pages