Özet: Veritabanında özellikle tekrar eden ardışık sorgularınızı hazırlarken ya da çalıştırırken işlerinizi çok kolaylaştıracak “Dinamik SQL/PlSQL” yapısı örneklerle anlatılıyor.

Bazen veritabanında birçok kullanıcıya yetki vermek/almak, birçok yetkiyi role vermek/almak, benzer yapıda birçok db objesini aynı zamanda oluşturmak veya birçok db objesi için alter işlemi yapmak gibi ihtiyaçlarımız çoğuz zaman oluyor. Bunu amacımıza göre dinamik sql veya da dinamik plsql yazarak oldukça kolaylaştırabiliriz.

Aşağıdaki örnekleri inceleyerek sizler de amacınıza göre “dinamik sql/plsql’ler” yazarak işlerinizi kolaylaştırabilirsiniz.

Her Oracle veritabanında default’ta hazır gelen dictionary tablolara veya view lere dinamik yapıları kullamak için başvururuz. “dict” tablosundan tüm 2668 adet dictionary tablo/view’ine erişebilirsiniz.

mesela yetkilerle ilgili olan tüm dictionary tablo/view ler için;

select * from dict where table_name like '%PRIV%';

mesela tablolarla ilgili olan tüm dictionary tablo/view ler için;

select * from dict where table_name like '%TAB%';

Örneğin DBA_TABLES view ini kullanarak ORHAN şeması altındaki tüm tablolar için select yetkisini MY_ROLE ismindeki role vermek istiyoruz. Tüm “grant select on table_name to role_name;” şeklinde grant cümlelerine ihtiyacımız var. Bunu en kısa yoldan aşağıdaki gibi tek bir satırda elde edebiliriz ve çıktısını alıp tek tuşla çalıştırabiliriz.

select 'grant select on ORHAN.' || table_name ||' to MY_ROLE;' from DBA_TABLES
where owner='ORHAN';

/*
grant select on ORHAN.TABLE1 to MY_ROLE;
grant select on ORHAN.TABLE2 to MY_ROLE;
grant select on ORHAN.TABLE3 to MY_ROLE;
*/

Fakat çıktımızda yüzlerce, binlerce satır varsa bunları editörde veya sqlplusta çalıştırmamız biraz zaman alır. Bunun için de dinamik plsql yazabiliriz. Eğer paket, prosedür veya fonksiyon içinde bu tarz çoklu db işlemi yapıyorsak zaten çoğu zaman kod içerisinde dinamik sql/plsql yapılarını kullanırız.
Aşağıdaki örnekleri inceleyelim:

Örnek -1:

declare
 v_result varchar2(200);
begin
 execute immediate 'select count(1) from table' into v_result;
 dbms_output.put_line(v_result);
end;

Örnek -2:

declare
 v_result pls_integer := 2000;
begin
 execute immediate
 'insert into test1(id, name) '
 || ' select id, name from test2 '
 || ' where id > 10'
 using v_result;

commit;
end;

Örnek -3:

begin
 execute immediate 'TRUNCATE TABLE table1;';
end;

Örnek -4:

declare
st varchar2(4000);
begin
st:='select ''GRANT EXECUTE ON ORHAN.'' || OBJECT_NAME || '' to MY_ROLE;'' from
(select distinct OBJECT_NAME from dba_objects where owner=''ORHAN''
and OBJECT_TYPE not in (''TABLE'',''INDEX'',''TABLE PARTITION'',
''INDEX PARTITION''))';
--execute immediate st;
dbms_output.put_line (st);
end;

Örnek -5:

select 'alter table ORHAN.' || table_name || ' move tablespace TS_ORHAN;'
"MOVE_SCRIPT" from dba_tables where owner='ORHAN';

select 'alter table ORHAN.' || table_name || ' move partition ' ||
 partition_name|| ' tablespace TS_ORHAN;' "PARTITION_MOVE_SCRIPT" from
 dba_tab_partitions where table_owner='ORHAN';

select 'alter index ORHAN.' || index_name || ' rebuild tablespace TS_ORHAN;'
 "MOVE_SCRIPT" from dba_indexes where owner='ORHAN';

select 'ALTER INDEX '||owner ||'.' ||index_name ||' REBUILD;' from dba_indexes
 where status = 'UNUSABLE';

Örnek -6:

set timing on
set serveroutput on
BEGIN
 FOR c IN ( SELECT USERNAME FROM DBA_USERS A WHERE USERNAME IN 
 ('SCHEMA1','SCHEMA2','SCHEMA3') ORDER BY 1
 ) LOOP
 SYS.DBMS_STATS.GATHER_SCHEMA_STATS( 
 OwnName => C.USERNAME
 ,Estimate_Percent => 10
 ,Method_Opt => 'FOR ALL COLUMNS SIZE AUTO'
 ,Degree => 16
 ,Cascade => TRUE
 ,No_Invalidate => FALSE);
 dbms_output.put_line(to_char(sysdate,'HH24:MI:SS' )
 ||': '|| c.USERNAME||' KULLANICISI ICIN ISTATISTIK TOPLAMA TAMAMLANDI.');
 END LOOP;
END;
/
Reklam