oracle恢复被覆盖的存储过程

前提：

拥有读写dba_source表的权限，一般此表权限属于系统管理员，最好拥有数据库管理员的登录用户和密码！

     （如果忘记用户密码，可参考此文章     点击打开链接    找回！）

建表脚本如下：

/*
--查找某个时间段的编译存过
select * from dba_source
AS OF TIMESTAMP TO_TIMESTAMP('2017-9-22 15:10:00', 'YYYY-MM-DD HH24:MI:SS')
where  name like upper('%PROC_BXJS_ZHJF_ZZ%') 
and owner ='TLJYB'
order by line;
*/
DROP TABLE p_temp;
DROP TABLE n_temp;

-----------------脚本----------------------
--创建临时表存放存过数据
create table p_temp  as  
select * from dba_source
AS OF TIMESTAMP TO_TIMESTAMP('2017-9-22 15:10:00', 'YYYY-MM-DD HH24:MI:SS')
where  name like upper('%PROC_BXJS_ZHJF_ZZ%') 
and owner ='TLJYB'
order by line;

select text
  from p_temp
 where name like upper('%PROC_BXJS_ZHJF_ZZ%')
   and owner = 'TLJYB'
 order by line;
 
--创建表，存放拼接后的存过
create table n_temp (
  xh VARCHAR2(4),
  text VARCHAR2(4000)
 );
 
 

存储过程脚本 如下：

CREATE OR REPLACE PROCEDURE PROC_temp IS
      s_text  VARCHAR2(4000) :='';
      i_Max_Line NUMBER(9) := 0; 
      i_cs NUMBER(9) := 0; 
      i NUMBER(9) := 0; 
      
CURSOR c_TJNY(i NUMBER) IS 
  SELECT *
    FROM P_TEMP A
   WHERE A.LINE > (100 * i)
     AND A.LINE <= (100 * (i + 1))
   ORDER BY LINE;
  
BEGIN
  --返回参数初始化
 SELECT MAX(a.line) 
 INTO i_Max_Line
 FROM p_temp a;
 
 SELECT ceil(i_Max_Line/100) 
 INTO i_cs
 FROM dual;
 
     --拼接字段  
 FOR count1 IN i..i_cs LOOP

  for cc in c_TJNY(count1) loop
   s_text :=s_text || cc.text ;  
  end loop;
  
  --将拼接后字段插入表
   INSERT INTO N_TEMP
     (XH, TEXT)
     SELECT count1, S_TEXT FROM DUAL;
   s_text :='';  

 END LOOP;

COMMIT;
END PROC_temp;
/