Re: Ampersand in PL/SQL

In article <D70Hpp.904_at_mail.auburn.edu>, moorthy_at_eng.auburn.edu (Moorthy) writes:
|> Hello There,
|>
|> I typed the following PL/SQL block in a file.
|>
|> declare
|> zzz number(5);
|> aaa number(5);
|> begin
|> select sal into zzz from emp where empno = &aaa;
|> if (zzz > 2000) then
|> update emp set sal = zzz + 10 where empno = aaa;
|> else
|> update emp set sal = zzz + 500 where empno = aaa;
|> end if;
|> commit;
|> end;
|> /
|>
|> At the SQL prompt, I started the file.
|>
|> SQL> start vm_12.sql;
|> Enter value for aaa: 7369
|> old 5: select sal into zzz from emp where empno = &aaa;
|> new 5: select sal into zzz from emp where empno = 7369;
|>
|> PL/SQL procedure successfully completed.
|>
|> SQL> select sal, empno from emp;
|>
|> SAL EMPNO
|> ---------- ----------
|> 800 7369
|> 1600 7499
|> 1250 7521
|> 2975 7566
|> 1250 7654
|> 2850 7698
|> 2450 7782
|> 3000 7788
|> 5000 7839
|> 1500 7844
|> 1100 7876
|>
|> SAL EMPNO
|> ---------- ----------
|> 950 7900
|> 3000 7902
|> 1300 7934
|>
|> 14 rows selected.
|>
|> Salary(800) for empno 7369 is not changed. Is ampersand allowed in PL/SQL ? If so, why the salary is not being updated. When I invoke sqlplus, the following information is displayed on my screen.
|

Do it the following way, it just works fine....

declare

   zzz number(5);
   aaa number(5);
begin

   aaa := &given_emp_no;

   select sal into zzz from emp where empno = aaa;    if (zzz > 2000) then

      update emp set sal = zzz + 10 where empno = aaa;    else

      update emp set sal = zzz + 500 where empno = aaa;    end if;
   commit;
end;
/

Asks for the empno all the times you execute the pl/sql block

Good luck
Madhavi Lokam Received on Tue Apr 25 1995 - 00:00:00 CEST