bind variable

[femina]

can i use the bind variable inside a procedure
and the query like update emp set empname=:name where empno=:no;

but i work like update emp set empname=&name where empno=&no inside procedures it works properly

declare
i number:=0;
begin
while i<3
loop
update dept set empno=&empno where dname='&deptname';
i:=i+1;
end loop;
end;

but here it prompts empno and deptname for first time and then uses it for all three times of the loop.how do i give three diff values for each of the three execution

[debasisdas]

If you want to pass 3 different values ,why using loop. directly execute three different SQL statments.

[femina]

no sir,
is there any provision to get different input from user for every time i enter the loop and dipslay the new value.
example

 declare
   eno number(4);
   begin
       for i in 1..5
        loop
            eno:=&eno;
           dbms_output.put_line(eno);
       end loop;
   end;

here i give an input 1 , but thats taken granted for all 5 times and it displays 1 1 1 1 1 but i want to give 5 differnet numbers each time i enter the loop.
please help me to know whether this is possible or not

If you want to pass 3 different values ,why using loop. directly execute three different SQL statments.

[amitpatel66]

no sir,
is there any provision to get different input from user for every time i enter the loop and dipslay the new value.
example

 declare
   eno number(4);
   begin
       for i in 1..5
        loop
            eno:=&eno;
           dbms_output.put_line(eno);
       end loop;
   end;

here i give an input 1 , but thats taken granted for all 5 times and it displays 1 1 1 1 1 but i want to give 5 differnet numbers each time i enter the loop.
please help me to know whether this is possible or not

No, that is not possible unless you make use of 5 different variables.
Alternative could be to use start and end value as an input parameter. Say you want employees whose empno is between 1 and 8 then you can use this code as shown below:

SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  TYPE empdet IS TABLE OF emp%ROWTYPE;
  3  empd empdet;
  4  eno number(4);
  5     eno1 NUMBER;
  6     begin
  7              eno:=&1;
  8              eno1:=&2;
  9  SELECT * BULK COLLECT INTO empd FROM emp WHERE empno between eno AND eno1;
 10  FOR I IN 1..empd.COUNT LOOP
 11  DBMS_OUTPUT.PUT_LINE(empd(i).empno||','||empd(i).ename);
 12  END LOOP;
 13*    end;
SQL> /
Enter value for 1: 1
old   7:             eno:=&1;
new   7:             eno:=1;
Enter value for 2: 2
old   8:             eno1:=&2;
new   8:             eno1:=2;
1,AAAA
2,AAAA
 
PL/SQL procedure successfully completed.
 

[femina]

thanks a lot for the reply sir

No, that is not possible unless you make use of 5 different variables.
Alternative could be to use start and end value as an input parameter. Say you want employees whose empno is between 1 and 8 then you can use this code as shown below:

SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  TYPE empdet IS TABLE OF emp%ROWTYPE;
  3  empd empdet;
  4  eno number(4);
  5     eno1 NUMBER;
  6     begin
  7              eno:=&1;
  8              eno1:=&2;
  9  SELECT * BULK COLLECT INTO empd FROM emp WHERE empno between eno AND eno1;
 10  FOR I IN 1..empd.COUNT LOOP
 11  DBMS_OUTPUT.PUT_LINE(empd(i).empno||','||empd(i).ename);
 12  END LOOP;
 13*    end;
SQL> /
Enter value for 1: 1
old   7:             eno:=&1;
new   7:             eno:=1;
Enter value for 2: 2
old   8:             eno1:=&2;
new   8:             eno1:=2;
1,AAAA
2,AAAA
 
PL/SQL procedure successfully completed.
 

[amitpatel66]

thanks a lot for the reply sir

You are welcome:)

MODERATOR