Oracle查询中使用in提示ORA-01722: 无效数字

Question

sql语句是update t1 set col1=:col1 where NSEQ in (:list)
其中list存放id的列表。调用的时候提示：ORA-01722: 无效数字。
为什么会出现这个错误，应该怎样修改，谢谢！

Answer 1

-- 因为list存放id的列表。所以应该用动态SQL执行：
execute immediate 'update t1 set col1=:v_col1 where nseq in (:v_list)' using v_col1, v_list;-- 其中 v_col1 、v_list 是你传入的两个参数！

Answer 2

scott@SZTYORA> select * from emp where empno in (7499,7521);     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 1981-02-20 00:00:00       8999        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22 00:00:00       8999        500         30已用时间:  00: 00: 00.00
scott@SZTYORA>
scott@SZTYORA> create or replace procedure up_emp_proc(
  2    i_sal emp.sal%type,
  3    i_empno_list varchar2
  4  )
  5  is
  6  begin
  7    execute immediate 'update emp set sal=:i_sal where empno in ('||i_empno_list||')' using i_sal;
  8    commit;
  9  end;
 10  /过程已创建。已用时间:  00: 00: 00.03
scott@SZTYORA>
scott@SZTYORA> exec up_emp_proc(8663,'7499,7521');PL/SQL 过程已成功完成。已用时间:  00: 00: 00.01
scott@SZTYORA> select * from emp where empno in (7499,7521);     EMPNO ENAME                JOB                       MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 1981-02-20 00:00:00       8663        300         30
      7521 WARD                 SALESMAN                 7698 1981-02-22 00:00:00       8663        500         30已用时间:  00: 00: 00.01
scott@SZTYORA>

Answer 3

-- 上面是例子，其中 i_empno_list 是字符类型，所以不能用绑定变量的形式，只能用SQL拼接！

Answer 4

不能这样使用in列表的绑定参数，execute immediate也不行！如果一定要使用绑定变量可以先声明一个表类型，然后创建一个返回这个类型的一个函数，
最后转换成一个表来访问。具体可参考这个

Answer 5

这个也可以使用绑定变量
--10g可用
UPDATE t1
   SET col1 = :col1
 WHERE NSEQ IN
       (WITH a AS (SELECT :list s FROM dual)
         SELECT regexp_substr(s, '[^,]+', 1, rownum)
           FROM a
         CONNECT BY rownum <= length(s) - length(REPLACE(s, ',')) + 1
        );--9i可用
UPDATE t1
   SET col1 = :col1
 WHERE NSEQ IN
       (WITH t AS (SELECT :list s FROM dual)
         SELECT substr(s,
                       instr(s, ',', 1, LEVEL) + 1,
                       instr(s, ',', 1, LEVEL + 1) - instr(s, ',', 1, LEVEL) - 1)
           FROM (SELECT ',' || s || ',' s FROM t)
         CONNECT BY LEVEL < length(s) - length(REPLACE(s, ','))
        )