在SQL中使用逗号分割的列表

在SQL中使用逗号分割的列表

DECLARE

       p_empno_list CONSTANT VARCHAR2(20) := '7369,7499,7839,7902';

BEGIN

       FOR r IN (

              SELECT * FROM emp

              WHERE  empno IN (p_empno_list)

              )

       LOOP

              DBMS_OUTPUT.PUT_LINE(RPAD(r.empno,9) || r.ename);

       END LOOP;

END;

/

执行以下查询后，将会得到以下错误：

ERROR at line 1:

ORA-01722: invalid number

ORA-06512: at line 4

但是当传递’ SMITH,JONES,FORD,MILLER’时并不会报错。

很显然，SQL不认为WHERE empno IN (p_empno_list)等价与WHERE empno IN (7369,7499,7839,7902)。

经过仔细研究才知道，不应该相等，因为如果提供'123, Fake St, Springfield'值，SQL怎么将这个列表处理成三个数字。

当然，Oracle SQL支持这种情况，使用集合。

使用以下脚本创建标量集合类型：

CREATE TYPE INTEGER_TT AS TABLE OF INTEGER

/

然后就可以使用：

DECLARE

    p_empno_list CONSTANT INTEGER_TT := INTEGER_TT(7369,7499,7839,7902);

BEGIN

    FOR r IN (

           SELECT empno, ename 2

           FROM   emp

           WHERE  empno IN

                  ( SELECT column_value 3

                    FROM   TABLE(p_empno_list) )

           )

    LOOP

           DBMS_OUTPUT.PUT_LINE(RPAD(r.empno,9) || r.ename);

    END LOOP;

END;

/

对于已存在过程，进行如下修改：

       YOURPROC(INTEGER_TT(7369,7499,7839,7902));代替

       YOURPROC('7369,7499,7839,7902');

       或者：

       不做代码声明改变，在过程中进行转换。

       在Oracle10g中，还可以使用WHERE empno MEMBER OF p_empno_list的形式。    注意：

SQL仅仅认识VARRAYs和Nested Table，这两者唯一区别是一个大小固定，另一个不定；除非数量已知，否则不应该使用varray。

在9i中，SELECT *…TABLE()必须显示设置CAST()，否则将返回ORA-22905错误。

COLUMN_VALUE是标量集合的默认属性。