create or replace procedure test (ID varchar,TIME varchar)
as
ID varchar(20); begin
delete from person where person.BM=ID;现在的问题是,在执行调用存储过程的时候,只能传入一个ID参数,如果我想一次传入多个ID号,如何解决?
as
ID varchar(20); begin
delete from person where person.BM=ID;现在的问题是,在执行调用存储过程的时候,只能传入一个ID参数,如果我想一次传入多个ID号,如何解决?
as
begin
delete from person where person.BM in (ID);在外面传入之前,把ID组装成下面的格式:
'xx','yy'即可
2 BEGIN
3 DELETE FROM person
4 WHERE person.BM IN
5 (WITH t AS (SELECT id s FROM dual)
6 SELECT substr(s,
7 instr(s, ',', 1, LEVEL) + 1,
8 instr(s, ',', 1, LEVEL + 1) -
9 instr(s, ',', 1, LEVEL) - 1)
10 FROM (SELECT ',' || s || ',' s FROM t)
11 CONNECT BY LEVEL < length(s) - length(REPLACE(s, ','))
12 );
13 COMMIT;
14 END;
15 /
Procedure created
SQL> select * from person;
BM TIME
-------------------- -----------
0a0 2011-6-21
001 2011-6-16
002 2011-6-13
003 2011-6-21
004 2011-6-21
SQL> exec test('001,002,0a0');
PL/SQL procedure successfully completed
SQL> select * from person;
BM TIME
-------------------- -----------
003 2011-6-21
004 2011-6-21
SQL>