create or replace procedure proc_ClearDust is sTable t_Cleartable.name%type; cursor sTableName is select t.name from t_ClearTable t; str varchar2(100); begin open sTableName; loop fetch sTableName into sTable; exit when sTableName%notfound; str:='begin update '||sTable||' set trashflag = 5 where trashflag = 0; end;'; execute immediate str; end loop; close sTableName; end proc_ClearDust; /
create or replace procedure proc_ClearDust is sTable t_Cleartable.name%type; cursor sTableName is select t.name from t_ClearTable t; str varchar2(100); begin open sTableName; loop fetch sTableName into sTable; exit when sTableName%notfound; str:='begin update '||sTable||' set trashflag = 5 where trashflag = 0; end;'; execute immediate str; end loop; close sTableName; end proc_ClearDust; /
谢谢xxx2002() ,能否详细谈谈用dbms_sql怎么写?
create or replace procedure proc_ClearDust is sTable t_Cleartable.name%type; cursor sTableName is select t.name from t_ClearTable t; begin v_cursorid integer; v_sql varchar2(2000); v_Dummy INTEGER;--依次从t_ClearTable中提出需要清理的表名,在对这些表中的响应记录做出更新 open sTableName;loop fetch sTableName into sTable; exit when sTableName%notfound; bein v_CursorID := DBMS_SQL.OPEN_CURSOR; v_sql := ' update ' || sTable || 'set trashflag = 5 where trashflag = 0 '; DBMS_SQL.PARSE(v_CursorID, v_sql, DBMS_SQL.V7); v_dummy := DBMS_SQL.EXECUTE(v_CursorID); DBMS_SQL.CLOSE_CURSOR(v_CursorID); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(v_CursorID); RAISE; end; end loop;close sTableName;end proc_ClearDust;
不用dbms_sql也可以如 create or replace procedure proc_ClearDust is sTable t_Cleartable.name%type; sCommand varchar2(200); cursor sTableName is select t.name from t_ClearTable t; begin --依次从t_ClearTable中提出需要清理的表名,在对这些表中的响应记录做出更新 open sTableName; loop fetch sTableName into sTable; exit when sTableName%notfound; sCommand:='update ' || sTable || ' set trashflag = 5 where trashflag = 0 '; execute immediate sCommand; end loop; close sTableName; end proc_ClearDust;
is
sTable t_Cleartable.name%type;
cursor sTableName is select t.name from t_ClearTable t;
str varchar2(100);
begin
open sTableName;
loop
fetch sTableName into sTable;
exit when sTableName%notfound;
str:='begin update '||sTable||' set trashflag = 5 where trashflag = 0; end;';
execute immediate str;
end loop;
close sTableName;
end proc_ClearDust;
/
is
sTable t_Cleartable.name%type;
cursor sTableName is select t.name from t_ClearTable t;
str varchar2(100);
begin
open sTableName;
loop
fetch sTableName into sTable;
exit when sTableName%notfound;
str:='begin update '||sTable||' set trashflag = 5 where trashflag = 0; end;';
execute immediate str;
end loop;
close sTableName;
end proc_ClearDust;
/
is
sTable t_Cleartable.name%type;
cursor sTableName is select t.name from t_ClearTable t;
begin v_cursorid integer;
v_sql varchar2(2000);
v_Dummy INTEGER;--依次从t_ClearTable中提出需要清理的表名,在对这些表中的响应记录做出更新
open sTableName;loop
fetch sTableName into sTable;
exit when sTableName%notfound;
bein
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_sql := ' update ' || sTable || 'set trashflag = 5 where trashflag = 0 ';
DBMS_SQL.PARSE(v_CursorID, v_sql, DBMS_SQL.V7);
v_dummy := DBMS_SQL.EXECUTE(v_CursorID);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
end;
end loop;close sTableName;end proc_ClearDust;
create or replace procedure proc_ClearDust
is
sTable t_Cleartable.name%type;
sCommand varchar2(200);
cursor sTableName is select t.name from t_ClearTable t;
begin
--依次从t_ClearTable中提出需要清理的表名,在对这些表中的响应记录做出更新
open sTableName;
loop
fetch sTableName into sTable;
exit when sTableName%notfound;
sCommand:='update ' || sTable || ' set trashflag = 5 where trashflag = 0 ';
execute immediate sCommand;
end loop;
close sTableName;
end proc_ClearDust;