求一个存储过程在线等!!oracle的 谢谢 删除单条delete from table where id in(1);这样可以,但是我传多个参数的时候由于是字符串的了('12,13,14')所以不能够完成删除操作,怎么写个存储过程完成这种操作delete from table where id in('12,13,14')呢 谢谢!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 --摘自asktom--str2tbl, 将逗号分隔的字符串转成表--考虑:写成piple row函数,可优化性能create or replace type str2tblType as table of varchar2(30);/create or replacefunction str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )return str2tblTypeas l_str long default p_str || p_delim; l_n number; l_data str2tblType := str2tbltype();begin dbms_application_info.set_client_info( userenv('client_info')+1 ); loop l_n := instr( l_str, p_delim ); exit when (nvl(l_n,0) = 0); l_data.extend; l_data(l_data.count) := ( ltrim(rtrim(substr(l_str,1,l_n-1))) ); l_str := substr( l_str, l_n+1 ); end loop; return l_data;end;/ 在存储过程中使用动态SQLexecute immediate 'delete from table where id in('|| i_param_list || ')'; delete from t where id in (select column_value from table(cast(str2tbl('1,2,3,4') as str2tblType)); 这个:delete from t where id in (select column_value from table(cast(str2tbl('1,2,3,4') as str2tblType))); 用tom的,可以用到绑定变量,不过10g完全可以用正则表达式一条sql搞定。动态sql缺点明显,数量不定,使用不了绑定变量,而且in中还有列表数小于1000的限制 id是数值型的时候不要加引号。delete from table where id in (12,13,14);id是字符型的时候,这样加引号。delete from table where id in ('12','13','14'); execute Immediate 'delete From table Where Id In (''regexp_replace(''12,13,14'','','','''','''')'') ' 在存储过程的参数中传入字符串(你要删除数据的ID)例如:v_del_id := '1,2,3,4';在存储过程中,动态调用SQL例如:execute immediate 'delete from tablename where id in('|| v_del_id || ')'; delete from table where ','||to_char(id)||',' in(',12,13,14,') delete from table where id in ('12','13','14');这样就可以了,是你sql的问题 delete from table where id in replace('12,13,14',',',''','''); 求一SQL语句。 请大家提供一些资料 用php开发oracle,数据库中有数据,oci_fetch_array返回空(在线等,有源码,帮忙~~) oracle中如何把查询结果导出到excel里面? .net访问oracle数据库问题 请问一个结果的排序问题 才鸟求救!! 一个oracle9i中最基本的问题! ***高手求助了!本人在用ADO.net连接Oracle8.0数据库时出现错误提示,向各位高手求助!谢谢了!!!*** 不小心删掉了DBA, CONNECT等角色,现在要向用户授予这些角色的时候没有了,怎么办啊? Pro*C 断开连接的问题 急需一个触发器
--str2tbl, 将逗号分隔的字符串转成表
--考虑:写成piple row函数,可优化性能create or replace type str2tblType as table of varchar2(30);
/create or replace
function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
return str2tblType
as
l_str long default p_str || p_delim;
l_n number;
l_data str2tblType := str2tbltype();
begin
dbms_application_info.set_client_info( userenv('client_info')+1 );
loop
l_n := instr( l_str, p_delim );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data(l_data.count) := ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
/
execute immediate 'delete from table where id in('|| i_param_list || ')';
delete from t where id in (select column_value from table(cast(str2tbl('1,2,3,4') as str2tblType)));
动态sql缺点明显,数量不定,使用不了绑定变量,而且in中还有列表数小于1000的限制
delete from table where id in (12,13,14);
id是字符型的时候,这样加引号。
delete from table where id in ('12','13','14');
execute Immediate 'delete From table
Where Id In (''regexp_replace(''12,13,14'','','','''','''')'') '
在存储过程的参数中传入字符串(你要删除数据的ID)
例如:v_del_id := '1,2,3,4';在存储过程中,动态调用SQL
例如:
execute immediate 'delete from tablename where id in('|| v_del_id || ')';
delete from table where ','||to_char(id)||',' in(',12,13,14,')
这样就可以了,是你sql的问题