游标CURSOR问题

[dropping]

DECLARE 
   slist VARCHAR2 (50) := '1001,1002,1003';  
BEGIN
    DECLARE
         CURSOR list_rows 
         IS 
    SELECT DISTINCT STAFFID FROM TSS_STAFF_FUNCGRP WHERE FUNCGRPID IN (slist); 
      BEGIN 
        FOR list_row IN list_rows 
        LOOP 
            dbms_output.put_line(list_row.STAFFID);
        END LOOP;
      END;
     dbms_output.put_line('OK'); 
END;

这样执行之输出一个OK.
但是如果把"SELECT DISTINCT STAFFID FROM TSS_STAFF_FUNCGRP WHERE FUNCGRPID IN (slist)"中的slist换成1001,1002,1003;
就会返回正确的有记录的结果,
这是怎么回事呢?

[shiyiwan]

因为前者sql变成 id in ('1001,1002,1003'),是一个值，后者是 id in (1001,1002,1003)
输出结果当然不同

[dropping]

那这个问题怎么解决.

[zcs_1]

在PL/SQL块中的SELECT 等同于
SELECT DISTINCT STAFFID FROM TSS_STAFF_FUNCGRP WHERE FUNCGRPID IN ('1001,1002,1003');

而楼主想要的效果是
SELECT DISTINCT STAFFID FROM TSS_STAFF_FUNCGRP WHERE FUNCGRPID IN (1001,1002,1003);
这是两个不同的查询条件

第一个是查询funcgrpid在单个字符串'1001,1002,1003'的数据，等同于funcgrpid='1001,1002,1003'，这里'1001,1002,1003'是一个数据而不是3个。

而第二个语句中1001,1002,1003则是三个数据。

楼主可改为动态sql，不过要复杂些

DECLARE 
   TYPE Listcurtyp IS REF CURSOR;
   list_rows Listcurtyp;
   slist VARCHAR2 (50) := '1001,1002,1003';
   TYPE Staffidlist IS TABLE OF NUMBER;
   staffids Staffidlist;
BEGIN
  OPEN list_rows FOR 'SELECT DISTINCT STAFFID FROM TSS_STAFF_FUNCGRP WHERE UNCGRPID IN (' || slist || ')';
     FETCH list_rows BULK COLLECT INTO staffids;
  CLOSE list_rows;
  
  FOR i IN staffids.FIRST .. staffids.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(staffids(i));
  END LOOP;
END;
/

[阿三]

declare
TYPE tt_cursor_type IS REF CURSOR;
tt_cursor tt_cursor_type;
sSql varchar2(500);
slist varchar2(200) :='1,2';
vid varchar2(20);
vname varchar2(20);
begin
sSql := 'select id,name from tt where id in ('||slist||')';
dbms_output.put_line(sSql);
open tt_cursor for sSql;
loop fetch tt_cursor into vid,vname;
exit when tt_cursor%notfound;
dbms_output.put_line(vid||' '||vname);
end loop;
end;
/

类似这样

[非萨范德萨发]

～_～再提供一个FOR..LOOP方法：

CREATE TABLE  TSS_STAFF_FUNCGRP AS SELECT LEVEL STAFFID,1000+LEVEL UNCGRPID FROM dual CONNECT BY LEVEL < 4;
SET serveroutput ON;
DECLARE
    slist     VARCHAR2(50) := '1001,1002,1003';
BEGIN        
    FOR rc IN (SELECT STAFFID FROM TSS_STAFF_FUNCGRP 
    				WHERE UNCGRPID IN (SELECT REGEXP_SUBSTR(slist,'[0-9]{1,}',1,LEVEL) 
                                                        FROM dual CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(slist,'[0-9]*',''))+1))LOOP
        dbms_output.put_line(rc.staffid);
    END LOOP;
END;
/

[fxianxian]

这种问题很典型啊，收藏了！