语句Select * From Table Where Cursor语言 - 如何循环这个游标?

Question

我创建了一个变量IDS TABLECLIENT.ID&type;，我用:

OPEN V_ID;
LOOP
FETCH V_ID INTO IDS;
EXIT WHEN V_ID%NOTFOUND;
END LOOP; 
CLOSE V_ID;

这很好。它存储了5个id客户端，但当我在select语句中使用它时，我等待5个寄存器，但我只得到1:

SELECT * 
FROM TABLECLIENT
WHERE ID IN IDS;

也许我必须在语句内循环id ?请帮助oracle的朋友

Answer 1

IDS-目前-只包含游标获取的一行。

例如，这是Scott示例模式中的部门表:

SQL> select * from dept;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

这段代码模拟了你所拥有的(虽然，如果你发布那个信息会更好);游标选择部门号，然后在另一个查询中使用该值。

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  /

结果是:

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>