很久没写过PL/SQL存储过程了--删除SQL Plan Baseline的存储过程

很久没写过PL/SQL存储过程了--删除SQL Plan Baseline的存储过程

 很久没写过PL/SQL了，今天需要，写了一个用于删除Oracle数据库系统自动捕获的 SQL Plan Baseline的PL/SQL块和存储过程。
关闭系统自动捕获 SQL Plan Baseline 的参数 alter system/session set optimizer_capture_sql_plan_baselines=false;
PL/SQL程序块使用静态游标以及游标一次fetch记录集全部数据，而存储过程 p_drop_sql_bl 使用动态游标以及使用 fetch..bulk collect into..limit n，一次提取记录集n条数据，以提高程序代码和数据库的性能，以下会有两者的性能对比。
删除 SQL Plan Baseline的PL/SQL块程序如下：

19:11:30 SYS@orcl*SQL> declare  --删除 SQL Plan Baseline 的PL/SQL程序块

19:11:32   2    v_sql_handle varchar2(100);

19:11:32   3    v_plan_name varchar2(100);

19:11:32   4    v_out binary_integer;

19:11:32   5

19:11:32   6    cursor c_1 is select sql_handle,plan_name  --定义静态游标，SQL固定。

19:11:32   7      from dba_sql_plan_baselines;

19:11:32   8

19:11:32   9  begin

19:11:32  10    open c_1;

19:11:32  11

19:11:32  12    loop

19:11:32  13      fetch c_1 into v_sql_handle,v_plan_name;

19:11:32  14      exit when c_1%notfound;

19:11:32  15

19:11:32  16      v_out := dbms_spm.drop_sql_plan_baseline(sql_handle=>v_sql_handle,plan_name=>v_plan_name);

19:11:33  17

19:11:33  18      if v_out=1 then

19:11:33  19            dbms_output.put_line('The SQL Plan Baseline: '||v_sql_handle||' was deleted.');

19:11:33  20        else

19:11:33  21          dbms_output.put_line('Operation error: Please check...');

19:11:33  22      end if;

19:11:33  23

19:11:33  24    end loop;

19:11:33  25

19:11:33  26    close c_1;

19:11:33  27  end;

19:11:34  28  /

The SQL Plan Baseline: SYS_SQL_02a86218930bbb20 was deleted.

The SQL Plan Baseline: SYS_SQL_127f1215d03d8cb1 was deleted.

The SQL Plan Baseline: SYS_SQL_470d1594e3064af9 was deleted.

The SQL Plan Baseline: SYS_SQL_5452ff90a6ad4f91 was deleted.

The SQL Plan Baseline: SYS_SQL_85372e07e425b213 was deleted.

The SQL Plan Baseline: SYS_SQL_9091bbf1623118dd was deleted.

The SQL Plan Baseline: SYS_SQL_9c0d7998b1d28680 was deleted.