17,377
社区成员
发帖
与我相关
我的任务
分享
update t1 set col1=:col1 where NSEQ in (:list)
--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, ','))
)
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>
-- 因为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 是你传入的两个参数!