假设表中有字段:id , col1,col2,col3...,下面语句可以删除除id列都为空的纪录:DELETE FROM test WHERE COALESCE(col1,col2,col3...) IS NULL
回答 duanzilin(寻): 首先非常感谢你的回答。 我现在想知道,如何在存储过程中写一个循环,有一个表tableinfo,专门存储其它表的信息,写语句为:select * from tableinfo,我目前的存储过程写为: create or replace procedure A(aTableName IN VARCHAR2) as begin delete from aTableName where txt0 in (select * from aTableName where bytenum=0);end;我现在不想让这个A有参数,而是通过一个循环(sql语句为select * from tableinfo)查出表名,也就是获取aTableName的值,请问如何写啊
用表名循环操作就要用动态sqlcreate or replace procedure A as strsql varchar2(2000);v_tablename varchar2(20);CURSOR c1 is select tablename from tableinfo;begin open c1;
FETCH c1 INTO v_tablename; exit when c1%notfound; loop strsql := 'delete from ' || v_tablename || 'where ...'; execute immediate sql_str; end loop; close c1; commit; end;
to duanzilin(寻): 我现在的存储过程写成这样,在Oracle中保存后执行,却没有改变一条记录,请问这是为什么? create or replace procedure ljtest as strsql varchar2(1000); v_tablename varchar2(20); CURSOR c_ta is select tablename from tableinfo where isMain=0 or isMain=1;begin open c_ta;
loop FETCH c_ta INTO v_tablename; exit when c_ta%notfound;
strsql := 'delete from '||v_tablename||' where txt0 in (select txt0 from '||v_tablename||' where bytenum=0)'; end loop;
close c_ta; commit; end ljtest;
你的sql都没执行,怎么会有结果…… loop strsql := .. execute immediate sql_str; end loop;
如果 count(*) 为○ 表不就是空的
这个表中有些记录是有数据的,有些记录是没有数据的,我要找的空记录是指那些除了第一个字段(ID,自增序列)之外其他字段都没有数据的记录。
首先非常感谢你的回答。
我现在想知道,如何在存储过程中写一个循环,有一个表tableinfo,专门存储其它表的信息,写语句为:select * from tableinfo,我目前的存储过程写为:
create or replace procedure A(aTableName IN VARCHAR2)
as
begin
delete from aTableName where txt0 in (select * from aTableName where bytenum=0);end;我现在不想让这个A有参数,而是通过一个循环(sql语句为select * from tableinfo)查出表名,也就是获取aTableName的值,请问如何写啊
as
strsql varchar2(2000);v_tablename varchar2(20);CURSOR c1 is select tablename from tableinfo;begin
open c1;
FETCH c1 INTO v_tablename;
exit when c1%notfound;
loop
strsql := 'delete from ' || v_tablename || 'where ...';
execute immediate sql_str;
end loop;
close c1; commit;
end;
我现在的存储过程写成这样,在Oracle中保存后执行,却没有改变一条记录,请问这是为什么?
create or replace procedure ljtest
as
strsql varchar2(1000);
v_tablename varchar2(20);
CURSOR c_ta is select tablename from tableinfo where isMain=0 or isMain=1;begin
open c_ta;
loop
FETCH c_ta INTO v_tablename;
exit when c_ta%notfound;
strsql := 'delete from '||v_tablename||' where txt0 in (select txt0 from '||v_tablename||' where bytenum=0)';
end loop;
close c_ta; commit;
end ljtest;
strsql := ..
execute immediate sql_str;
end loop;
还有,我想跟duanzilin(寻) 交个朋友,以后经常会向你探讨问题,可以么?我的qq号是365581429