这点代码哪里有问题？

[ymymym16888]

自己写了个存储过程，打算弄成JOBS每天执行，目地是删除分区表TRACE_BASE_INFO_ASK中旧分区，只保留5个最新的分区。
如果删除分区是出错，则将异常信息记录到表ExpTable中
现在创建这存储过程后，系统提示该存储过程处于无效状态，但不知道存储过程哪里出的错，有劳高手指明

代码如下：

CREATE OR REPLACE procedure SYSTEM.AutoDrop_ASKPart
as
BEGIN

declare i_rowcount int;

begin

SELECT count(1) into i_rowcount FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TRACE_BASE_INFO_ASK' AND PARTITION_NAME LIKE 'SYS_%';

if i_rowcount>5 then
declare cursor c_job is
SELECT PARTITION_NAME FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME='TRACE_BASE_INFO_ASK' and PARTITION_NAME like 'SYS_%' and rownum<=(i_rowcount-5);

c_row c_job%rowtype;
begin
for c_row in c_job loop
begin
execute immediate 'alter table TRACE_BASE_INFO_ASK drop partition ' || c_row.PARTITION_NAME;
exception
when others then
insert into ExpTable(ExpText)VALUES(SQLERRM);
end;
end loop;

end;

end if;

end;

END;

[一颗向往数据的心丶]

随意给你调整了下 （未编译测试）

CREATE OR REPLACE PROCEDURE system.autodrop_askpart IS
  i_rowcount NUMBER;
  CURSOR c_job(p_rowcount) IS
    SELECT partition_name
      FROM all_tab_partitions
     WHERE table_name = 'TRACE_BASE_INFO_ASK'
       AND partition_name LIKE 'SYS_%'
       AND rownum <= (p_rowcount - 5);

  c_row c_job%ROWTYPE;
BEGIN

  SELECT COUNT(1)
    INTO i_rowcount
    FROM all_tab_partitions
   WHERE table_name = 'TRACE_BASE_INFO_ASK'
     AND partition_name LIKE 'SYS_%';

  IF i_rowcount > 5 THEN
  
    BEGIN
      OPEN c_job(i_rowcount);
    
      LOOP
        FETCH c_job
          INTO c_row;
        EXIT WHEN c_job%NOTFOUND;
        BEGIN
          EXECUTE IMMEDIATE 'alter table TRACE_BASE_INFO_ASK drop partition ' ||
                            c_row.partition_name;
        EXCEPTION
          WHEN OTHERS THEN
            INSERT INTO exptable
              (exptext)
            VALUES
              (SQLERRM);
        END;
      END LOOP;
      CLOSE c_job;
    
    END;
  
  END IF;

END;

[ymymym16888]

不行啊，你还是测试一下吧

[卖水果的net]

-- 在 1# 的基础上编辑的
-- 1 。游标的参数，加一个数据类型
--  2。SQLERRM 先赋给一个变量
CREATE OR REPLACE PROCEDURE autodrop_askpart IS
  i_rowcount NUMBER;
  i_error VARCHAR2(500);

  CURSOR c_job(p_rowcount varchar2) IS
    SELECT partition_name
      FROM all_tab_partitions
     WHERE table_name = 'TRACE_BASE_INFO_ASK'
       AND partition_name LIKE 'SYS_%'
       AND rownum <= (p_rowcount - 5);

  c_row c_job%ROWTYPE;
BEGIN

  SELECT COUNT(1)
    INTO i_rowcount
    FROM all_tab_partitions
   WHERE table_name = 'TRACE_BASE_INFO_ASK'
     AND partition_name LIKE 'SYS_%';

  IF i_rowcount > 5 THEN

    BEGIN
      OPEN c_job(i_rowcount);

      LOOP
        FETCH c_job
          INTO c_row;
        EXIT WHEN c_job%NOTFOUND;
        BEGIN
          EXECUTE IMMEDIATE 'alter table TRACE_BASE_INFO_ASK drop partition ' ||
                            c_row.partition_name;
        EXCEPTION
          WHEN OTHERS THEN
            i_error:= SQLERRM;
            INSERT INTO exptable
              (exptext)
            select i_error from dual ;
        END;
      END LOOP;
      CLOSE c_job;

    END;

  END IF;

END;

[一颗向往数据的心丶]

引用 2 楼 ymymym16888 的回复:

不行啊，你还是测试一下吧

我只是把你大的语法问题给调整下。其他已注明未调试。 中肯或者说不中听的话：楼主 你这种要喂到嘴里嚼都不想嚼一下的思想 怎么面对问题处理问题？