DECLARE TYPE t_cursor IS ref CURSOR; v_cursor t_cursor; V_TNAME VARCHAR2(50); V_SQL VARCHAR2(1000); BEGIN OPEN v_cursor FOR select T.TNAME from sys.tab t where regexp_like(t.tname, '^RWD_[[:upper:]]*_[U,D]Y_085[1-9]_20...._OUT[0-9]+$'); --这里SQL只是查询满足某些条件的表名 loop fetch v_cursor into V_TNAME;---然后放到这里 EXIT WHEN v_cursor%NOTFOUND; V_SQL :='DROP TABLE '||V_TNAME; EXECUTE IMMEDIATE V_SQL; commit; end loop; CLOSE v_cursor; EXCEPTION WHEN OTHERS THEN ROLLBACK; END ;这个是我写的定期批量删除一些表的SQL 对你应该有帮助
create or replace procedure procedure_test ( cur_result out ys_refcursor --返回游标类型 ) is begin open cur_result for select * from all_tables where rownum <= 10;
end procedure_test;
create or replace procedure del_emp(v_empno emp.empno%type) is begin delete from emp where empno=v_empno; exception when others then null; --dbms_output.put_line'不存在这条记录'; end del_emp;
-- 静态游标 create or replace procedure p1 is cursor v_cur is select * from dual; begin for v_item in v_cur loop -- 逻辑处理 end loop end p1;-- 动态游标 create or replace procedure p2 is TYPE t_cursor IS ref CURSOR; v_cursor t_cursor; v_sql varchar2(1000); v_item yourNeedType; begin v_sql := ''; -- 拼凑你的SQL open v_cursor for v_sql ; loop fetch v_cursor into v_item -- 逻辑处理 EXIT WHEN v_cursor%NOTFOUND; end loop; close v_cursor ; end p2;
DECLARE
TYPE t_cursor IS ref CURSOR;
v_cursor t_cursor;
V_TNAME VARCHAR2(50);
V_SQL VARCHAR2(1000);
BEGIN
OPEN v_cursor FOR select T.TNAME from sys.tab t where
regexp_like(t.tname, '^RWD_[[:upper:]]*_[U,D]Y_085[1-9]_20...._OUT[0-9]+$');
--这里SQL只是查询满足某些条件的表名
loop
fetch v_cursor into V_TNAME;---然后放到这里
EXIT WHEN v_cursor%NOTFOUND;
V_SQL :='DROP TABLE '||V_TNAME;
EXECUTE IMMEDIATE V_SQL;
commit;
end loop;
CLOSE v_cursor;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END ;这个是我写的定期批量删除一些表的SQL 对你应该有帮助
cur_result out ys_refcursor --返回游标类型
)
is
begin open cur_result for
select * from all_tables where rownum <= 10;
end procedure_test;
begin
delete from emp where empno=v_empno;
exception
when others then
null;
--dbms_output.put_line'不存在这条记录';
end del_emp;
-- 静态游标
create or replace procedure p1
is
cursor v_cur is
select * from dual;
begin
for v_item in v_cur loop
-- 逻辑处理
end loop
end p1;-- 动态游标
create or replace procedure p2
is
TYPE t_cursor IS ref CURSOR;
v_cursor t_cursor;
v_sql varchar2(1000);
v_item yourNeedType;
begin
v_sql := ''; -- 拼凑你的SQL
open v_cursor for v_sql ;
loop
fetch v_cursor into v_item
-- 逻辑处理
EXIT WHEN v_cursor%NOTFOUND;
end loop;
close v_cursor ;
end p2;