Mevlüt Becerikli

ORACLE veritabanımızdaki mevcut bir kullanıcıya, mevcut bir şemadaki tüm tablolara erişim yetkisi (SELECT, UPDATE,INSERT,DELETE …vs.) verilmesini istiyoruz. Bunun tek bir GRANT komutu ile yapamayız. TOAD gibi araçlar ile kolayca yapabilsekte bunu tek tek yapmak da sıkıcı olsa gerek ?
Bu iş için bir PLSQL bloğu yapabiliriz…

EGITIM şemasındaki tabloları için MEVLUT kullanıcısını yetkilendirelim;

FOR x IN (SELECT * FROM dba_tables WHERE owner = ‘EGITIM’)
LOOP
EXECUTE IMMEDIATE ‘GRANT SELECT, UPDATE, DELETE, INSERT ON ‘ || x.owner || ‘.’ || x.table_name || ‘ TO MEVLUT’;
END LOOP;

NOT : Tabiki bu script çalıştırıldıktan sonra veritabanında EGITIM şemasında oluşturulan tablolara MEVLUT kullanıcısının yetkisi olmayacaktır. Script kaydedilip düzenli olarak çalıştırılabilir.

Şu an sadece tablo için ve manual olarak konuşuyorum. Ama bunu DDL ifadelerine endeksli bir TRIGGER ile otomatik olarak da yapmamız mümkün. Mesela bir tablo yaratıldığında MEVLUT kullanıcısına SELECT yetkisi verilmesini sağlayan bir TRIGGER yazalım;

CREATE OR REPLACE TRIGGER do_grant
AFTER CREATE ON SCHEMA
DECLARE
l_str varchar2(255);
l_job number;
BEGIN
IF ( ora_dict_obj_type = ‘TABLE’ ) THEN
l_str := ‘EXECUTE IMMEDIATE “GRANT SELECT ON ‘ || ORA_DICT_OBJ_NAME || ‘ TO mevlut”;’;
DBMS_JOB.SUBMIT( l_job, replace(l_str,’”‘,””) );
END IF;
END;

NOT :Burada  ORA_DICT_OBJ_NAME ifadesi, bir DDL  ifadesi (System Event) çalıştırıldığında  oluşan nesne tipidir (Dictionary  Object Type). Burada oluşan tabloyu verir. Bunun gibi bir çok nesne tipi vardır. Bunlar için aşağıdaki linkleri incelemenizi öneririm.

http://www.java2s.com/Tutorial/Oracle/0560__Trigger/ORADICTOBJTYPE.htm
http://psoug.org/reference/system_events.html

İyi Çalışmalar…

Oracle’ da sayısız parametre var ve her geçen gün yeni bir tanesini öğreniyorum.  Az önce de yeni bir tanesini öğrendim ve hemen soluğu burada aldım
Sanırım yazının başlığından da hangi parametre olduğunu tahmin etmek zor olmaz : FIXED_DATE

Sanırım oracle ile biraz uğraşmış birisi SYSDATE ifadesinden haberdardır. Bu parametre  (FIXED_DATE) de SYSDATE değerine içinde bulunduğumuz an yerine sabit bir tarih değeri atamamızı sağlar. Ne zaman ki SYSDATE i sorguladığımızda artık bu sabit (değilmez) değerimizi elde edeceğiz. Özellikle yazdığımız prosedürlerde sık sık SYSDATE kullanıyoruz. FIXED_DATE parametresi ile ilk karşılaştığımda da PLSQL kodlarımızın test edilmesi açısından faydalı olabileceği idi.

Kullanımı faydalı da olsa bu parametrenin oturum (session) bazında olmadığı mevcut Instance için bir değişiklik olduğunu öğrendim. Şu an da siz öğrendiniz

Atama :

ALTER SYSTEM SET FIXED_DATE=’1982-12-26-00:00:00′;
SQL> select sysdate from dual;
SYSDATE
———
12/26/1982 00:00:00 AM

Geri Dönüş :

ALTER SYSTEM SET FIXED_DATE=NONE;
SQL> select sysdate from dual;
SYSDATE
———
3/11/2010 2:09:07 PM

NOT : Bu parametre SYSDATE ile benzer yapıda olan SYSTIMESTAMP ifadesinin değerini etkilemez.

İyi Çalışmalar…

Gene bir forum da sorulan güzel bir soruyu paylaşmak istiyorum.

Yukarıda ki yapıda ve verileri içeren tablo isminde bir tablomuz olsun. Bizden MYID alanı bazında kaç adet Y değerine sahip olduğumuzun istatistiği isteniyor. Bunu nasıl yaparız ? Önce tablomuzu oluşturup içini dolduralım.

CREATE TABLE tablo (myid varchar2(3),col1 VARCHAR2(1),col2 VARCHAR2(1),col3 VARCHAR2(1));

INSERT INTO tablo VALUES ( ‘abc’,'Y’,'Y’,'Y’);
INSERT INTO tablo VALUES ( ‘xyz’,'N’,'Y’,'N’);
INSERT INTO tablo VALUES ( ‘abc’,'Y’,'Y’,'N’);

Şimdi de istenen SQL sorgusunu yazalım.

SELECT myid, SUM(DECODE(col1, ‘Y’, 1, 0) + DECODE(col2, ‘Y’, 1, 0)+ DECODE(col3, ‘Y’, 1, 0)) AS TOTAL
FROM tablo
GROUP BY myid;

Sonuç mu ?

MYID    TOTAL
abc    5
xyz    1

İyi Çalışmalar…

Bazen SQL ifadelerimizin sonucunda bir kayıt dönmeyebilir. Biz de SELECT… INTO ifadesini kullanırsak o zaman bu bizim başımıza biraz sorun açabilir. Bu hatayı kontrol etmek de çok bilndik bir şey ama kayıt altına alalım;

BEGIN

SELECT empno INTO v_empno FROM emp WHERE ename = ‘MEVLUT’;

EXCEPTION
WHEN no_data_found THEN

<<Burda istediğiniz işlemi yapabilirsiniz>>

END;

Bir diğer yöntem de NVL fonksiyonunu kullanarak, kayıt dönmemesi halinde DEFAULT bir değer de atayabiliriz;

SELECT NVL(empno,0) INTO v_empno FROM emp WHERE ename = ‘MEVLUT‘;

Bu ifade yanlıştır. Çünkü bir kayıt dönmüyorsa uygulanacak veri (NULL bile olsa) olmayacaktır.
Zira bende anlık bir dalgınlık ile bu hataya düşmüştüm. Ama sevgili okurlarımızın desteği ile bunu da düzelttik.  Nasıl mı ???

Aşağıdaki yorumlarda arkadaşlar güzelce açıklamışlar.
Teşekkür ederim…

İyi Çalışmalar…

Önce bir tablo yaratalım;

CREATE TABLE tablo (sutun1 VARCHAR2(1), sutun2 VARCHAR2(1), sutun3 VARCHAR2(1));

Sonra sürun isimlerini user_tab_columns View’ inden elde edelim;

SELECT column_name FROM user_tab_columns WHERE table_name=’TABLO’

COLUMN_NAME
————-
SUTUN1
SUTUN2
SUTUN3

Şimdi bu sütunları yan yana elde edelim;

WITH test_data as (

SELECT column_name FROM user_tab_columns WHERE table_name=’TABLO’

)
SELECT *
FROM ( SELECT LTRIM(SYS_CONNECT_BY_PATH(column_name, ‘,’), ‘,’) AS deger
FROM ( SELECT column_name, LAG(column_name) OVER (ORDER BY column_name) AS prev_column_name FROM test_data )
START WITH prev_column_name IS NULL
CONNECT BY prev_column_name = prior column_name
ORDER BY 1 DESC
)
WHERE ROWNUM = 1

DEGER
———-
SUTUN1,SUTUN2,SUTUN3

Umarım faydalı olmuştur…

İyi Çalışmalar…

Farklar :

• DECODE, IF-THEN-ELSE in basit versiyonudur.
• DECODE sadece eşitlik operatörünü kullanırken, IF-THEN-ELSE ifadesi tüm  koşul operatörlerini (=, !=, <, <=, > , >=, LIKE, NOT LINKE, IS NULL, IS NOT NUL … vs) kullanır.
• DECODE,  tek bir koşulu içerirken, IF-THEN-ELSE ifadesi AND/OR operatörleri yardımı ile birden çok koşulu bir arada birleşik içerebilir.
• DECODE bir veritabanı fonksiyonu olup tekbir ifade döndürdüğü için bir SQL cümleciği içinde kullanılabilirken, IF-THEN-ELSE ifadesi, bir PL/SQL ifadesi olup sadece PL/SQL bloklarının içinde kullanılabilir (SQL ifadeleri içinde kullanılan bir diğer koşul ifadesi de CASE’ dir).

İyi Çalışmalar…

AYIN İLK GÜNÜ : Ay bazında Trunc fonksiyonunu kullanabiliriz

SELECT TRUNC(sysdate, 'MM') FROM dual

AYIN SON GÜNÜ : Hazır fonskiyon yazmış adamlar kullan diye ;

SELECT LAST_DAY(sysdate) FROM dual

HAFTANIN İLK GÜNÜ : Ay bazında Trunc fonksiyonunu kullanabiliriz

SELECT TO_CHAR(sysdate - (TO_CHAR(sysdate, 'D')-1),'dd.MM.yyyy') AS PAZARTESI
FROM dual

HAFTANIN SON GÜNÜ : Hazır fonskiyon yazmış adamlar kullan diye ;

SELECT TO_CHAR(sysdate - (TO_CHAR(sysdate, 'D')) + 7 , 'dd.MM.yyyy') AS PAZAR
FROM dual

İyi Çalışmalar…

Gene bir forum da sorulan güzel bir soruyu paylaşmak istiyorum.

Yukarıda ki yapıda ve verileri içeren tablo isminde bir tablomuz olsun. Bizden MYID alanı bazında kaç adet Y değerine sahip olduğumuzun istatistiği isteniyor. Bunu nasıl yaparız ? Önce tablomuzu oluşturup içini dolduralım.

CREATE TABLE tablo (myid varchar2(3),col1 VARCHAR2(1),col2 VARCHAR2(1),col3 VARCHAR2(1));

INSERT INTO tablo VALUES ( ‘abc’,'Y’,'Y’,'Y’);
INSERT INTO tablo VALUES ( ‘xyz’,'N’,'Y’,'N’);
INSERT INTO tablo VALUES ( ‘abc’,'Y’,'Y’,'N’);

Şimdi de istenen SQL sorgusunu yazalım.

SELECT myid, SUM(DECODE(col1, ‘Y’, 1, 0) + DECODE(col2, ‘Y’, 1, 0)+ DECODE(col3, ‘Y’, 1, 0)) AS TOTAL
FROM tablo
GROUP BY myid;

Sonuç mu ?

MYID    TOTAL
abc    5
xyz    1

İyi Çalışmalar…

Tablomuzda (TEST_DATE) gruplar (GRUP_NO) halinde haftanın günlerine (HAFTANIN_GUNU) ait tutar (TUTAR) değerlerimiz var.  Her güne ait en yüksek tutarlı 3 kaydı getirmemiz isteniyorsa, bu PARTITION BY ifadesi ve ROW_NUMBER() analitik fonksiyonunun kullanımı için gayet güzel bir örnek olacaktır;

WITH test_data AS (

SELECT 1 GRUP_NO, 1 HAFTANIN_GUNU, 3000 TUTAR FROM DUAL UNION ALL
SELECT 1, 4, 2600 FROM DUAL UNION ALL
SELECT 1, 3, 2700 FROM DUAL UNION ALL
SELECT 1, 6, 2600 FROM DUAL UNION ALL
SELECT 1, 2, 2600 FROM DUAL UNION ALL
SELECT 1, 5, 2600 FROM DUAL UNION ALL
SELECT 1, 7, 2600 FROM DUAL UNION ALL
SELECT 2, 1, 4600 FROM DUAL UNION ALL
SELECT 2, 7, 4200 FROM DUAL UNION ALL
SELECT 2, 5, 4200 FROM DUAL UNION ALL
SELECT 2, 2, 4200 FROM DUAL UNION ALL
SELECT 2, 4, 4200 FROM DUAL UNION ALL
SELECT 2, 3, 4200 FROM DUAL UNION ALL
SELECT 2, 6, 4200 FROM DUAL UNION ALL
SELECT 3, 1, 5000 FROM DUAL

)
SELECT *
FROM ( SELECT a.*, ROW_NUMBER() OVER (PARTITION BY grup_no ORDER BY tutar DESC) rn FROM test_data a)
WHERE rn < 4

Görüldüğü gibi ROW_NUMBER() OVER (PARTITION BY grup_no ORDER BY tutar DESC) ifadesi ile test verimizi gruplar halinde ayrı değerlendirerek tutarlarımızı azalan şekilde sıralayıp her birine bir sıra numarası veriyoruz. Bu sıra numaramızı da 4 ten küçük olacak şekilde sınırlandırınca istediğimiz sonuca ulaşmış oluyoruz

1    1    1    3000    1
2    1    3    2700    2
3    1    4    2600    3
4    2    1    4600    1
5    2    7    4200    2
6    2    5    4200    3
7    3    1    5000    1

İyi Çalışmalar…

TO_CHAR fonksiyonu sıkça kullanılan bir fonksiyon ve bende sık sık kullandım ama ilk defa parametre olarak Q aldığını görünce paylaşmak istedim. Bir yılı üçer aylık periyotlarla 4 parçaya ayırırsak, girdiğimiz tarihin hangi parçaya ait olduğunu bulmanın en kolay yolu da bu parametre oluyor. Nasıl derseniz aşağıdaki ifadeyi çalıştırın ve sonucunu kendiniz görün;

SELECT TO_CHAR(sysdate,‘Q‘) || ‘Q’ FROM dual  ;

İyi Çalışmalar…