游标的使用1

declare

l_dbid number;
l_instance_number number;
l_db_count number := 0;
l_instance_count number := 0;
l_dummy varchar2(1);
l_cursor integer;
l_sql varchar2(2000);
update_not_reqd exception;

[@more@]

begin

-- check to see if the PK and HV indexes exist

select count(1)
into l_dummy
from dba_ind_columns
where table_owner = 'PERFSTAT'
and table_name = 'STATS$SQL_PLAN_USAGE'
and index_name in ('STATS$SQL_PLAN_USAGE_PK','STATS$SQL_PLAN_USAGE_HV')
and column_name in ('SNAP_ID','DBID','INSTANCE_NUMBER'
,'HASH_VALUE','TEXT_SUBSET','PLAN_HASH_VALUE','COST');

if l_dummy = 8 then
-- The upgrade has been run successfully before - exit
raise update_not_reqd;--定义异常
end if;

dbms_output.put_line('Beginning upgrade of STATS$SQL_PLAN_USAGE');

-- Check to see if old I1 index exists, if so, drop it
select count(1)
into l_dummy
from dba_ind_columns
where table_owner = 'PERFSTAT'
and table_name = 'STATS$SQL_PLAN_USAGE'
and index_name = 'STATS$SQL_PLAN_USAGE_I1'
and column_name = 'SNAP_ID';

l_cursor := dbms_sql.open_cursor;--定义游标

if l_dummy = 1 then

-- old I1 index exists, drop it
l_sql := 'drop index STATS$SQL_PLAN_USAGE_I1';--sql statment
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);--解析执行游标
dbms_output.put_line('.. Dropped I1 index');

end if;

-- Check to see if old PK index exists, if so, drop it

select count(1)
into l_dummy
from dba_ind_columns
where table_owner = 'PERFSTAT'
and table_name = 'STATS$SQL_PLAN_USAGE'
and index_name = 'STATS$SQL_PLAN_USAGE_PK'
and column_name in ('SNAP_ID','DBID','INSTANCE_NUMBER'
,'HASH_VALUE','TEXT_SUBSET','PLAN_HASH_VALUE','COST');

if l_dummy = 4 then

-- old PK index still here - drop
l_sql := 'alter table STATS$SQL_PLAN_USAGE drop primary key drop index';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
dbms_output.put_line('.. Dropped PK');

end if;

-- Archive off the old table, if it doesn't already exist

select count(1)
into l_dummy
from dba_tables
where owner = 'PERFSTAT'
and table_name = 'STATS$SQL_PLAN_USAGE_90';

if l_dummy = 0 then

-- table not archived previously
l_sql := 'rename STATS$SQL_PLAN_USAGE to STATS$SQL_PLAN_USAGE_90';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
dbms_output.put_line('.. Archived original STATS$SQL_PLAN_USAGE table to STATS$SQL_PLAN_USAGE_90');

end if;

-- Create new table, PK, FK

l_sql := 'create table STATS$SQL_PLAN_USAGE
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,hash_value number not null
,text_subset varchar2(31) not null
,plan_hash_value number not null
,cost number
,address raw(8)
,optimizer varchar2(20)
,constraint STATS$SQL_PLAN_USAGE_PK primary key
(snap_id, dbid, instance_number
,hash_value, text_subset, plan_hash_value, cost)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_PLAN_USAGE_FK foreign key
(snap_id, dbid, instance_number)
references STATS$SNAPSHOT on delete cascade
) tablespace &&tablespace_name
storage (initial 5m next 5m pctincrease 0) pctfree 5 pctused 40';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);

dbms_output.put_line('.. Created new STATS$SQL_PLAN_USAGE table');

-- create HV index
l_sql := 'create index STATS$SQL_PLAN_USAGE_HV ON STATS$SQL_PLAN_USAGE (hash_value)
tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)';
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);dbms_output.put_line('.. Created new HV index');

dbms_output.put_line('Upgrade of STATS$SQL_PLAN_USAGE complete');

exception
when update_not_reqd then--
dbms_output.put_line('Upgrade of STATS$SQL_PLAN_USAGE not required - skipping');
when others then
rollback;
raise;end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/79291/viewspace-921742/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/79291/viewspace-921742/