I've created a variable IDS TABLECLIENT.ID&type; and i fill this var. with:
OPEN V_ID;
LOOP
FETCH V_ID INTO IDS;
EXIT WHEN V_ID%NOTFOUND;
END LOOP;
CLOSE V_ID;
This works fine. it stores 5 id clients but when i use this in a select statement, i'm waiting 5 registers but i only get 1:
SELECT *
FROM TABLECLIENT
WHERE ID IN IDS;
Maybe i have to loop ids inside the statement? please help oracle friends
IDS - at a moment - contains only one row fetched by the cursor.
For example, this is table of departments in Scott's sample schema:
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
This code simulates what you have (though, it would have been better if you posted that info); cursor selects department numbers and - later - uses that value in another query.
SQL> set serveroutput on
SQL> declare
2 cursor v_id is select deptno from dept;
3 ids dept.deptno%type;
4 l_cnt number;
5 begin
6 open v_id;
7 loop
8 fetch v_id into ids;
9 exit when v_id%notfound;
10
11 -- display IDS's contents:
12 dbms_output.put_line('Department ' || ids);
13
14 -- you can do "something" with that value; for example,
15 -- count employees who work in that department
16 select count(*)
17 into l_cnt
18 from emp
19 where deptno = ids;
20 dbms_output.put_line('...Number of employees in DEPT ' || ids ||
21 ' = ' || l_cnt);
22 end loop;
23 close v_id;
24 end;
25 /
Result is:
Department 10 --> this is value fetched in the 1st loop round
...Number of employees in DEPT 10 = 3
Department 20 --> fetched in the 2nd round
...Number of employees in DEPT 20 = 5
Department 30
...Number of employees in DEPT 30 = 6
Department 40
...Number of employees in DEPT 40 = 0
PL/SQL procedure successfully completed.
SQL>