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:

[code=oracle]
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).e mpno||','||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.
[/code]

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

[code=oracle]
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).e mpno||','||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.
[/code]

[amitpatel66]

thanks a lot for the reply sir

You are welcome:)

MODERATOR