Transcript Ders 10 - Oracle Nesneleri - Altan MESUT

VERİ TABANI YÖNETİMİ
Ders 10: Oracle Nesneleri
Yrd. Doç. Dr. Altan MESUT
Trakya Üniversitesi
Bilgisayar Mühendisliği
Ders İçeriği
•
•
•
•
•
•
•
•
Tablo (Table) & Kısıtlamalar (Constraints)
İndeks (Index)
Görüntü (View)
Synonym & Sequence
Hareket (Transaction) İşleme
Kullanıcı, Rol ve Yetki İşlemleri
Tetikleyici (Trigger)
Saklı Yordam (Stored Procedure)
Önemli Not
• SQL dilinde kullanılan SELECT komutu ve DML
komutları (INSERT, DELETE, UPDATE) genellikle
tüm VTYS’lerde aynı şekilde kullanılır.
• DDL (CREATE, DROP, ALTER) ve DCL (GRANT,
REVOKE) komutlarında ise bazı farklılıklar vardır.
– Örneğin SQL Server’da önce bir LOGIN nesnesi
yaratılır, daha sonra yaratılan bir kullanıcı (USER) bu
LOGIN nesnesi ile ilişkilendirilir. Dolayısı ile SQL
Server’daki “CREATE USER” komutunun yazımı ile
ORACLE’daki yazım farklıdır.
• Bu ders sunusundaki tüm DDL ve DCL komutları
ORACLE’a göre verilmiştir.
Tablo Yaratma
CREATE TABLE scott.emp(
empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL
CONSTRAINT upper_ename CHECK (ename = UPPER(ename)),
job VARCHAR2(9),
mgr NUMBER CONSTRAINT fk_mgr REFERENCES
scott.emp(empno),
hiredate DATE DEFAULT SYSDATE,
sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500),
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) CONSTRAINT nn_deptno NOT NULL
CONSTRAINT fk_deptno REFERENCES scott.dept(deptno)
);
Bütünlük Kısıtlamaları
(Integrity Constraints)
• Oracle’da veri bütünlüğünü sağlamak için 5 farklı
bütünlük kısıtlaması tanımlanabilir:
– Primary Key, Foreign Key, Unique, Not Null, Check
• Kısıtlamalar tablolar oluşturulurken tanımlanabileceği
gibi tablolar oluşturulduktan sonra da
tanımlanabilirler.
• Ancak tabloya önceden girilmiş olan veriler varsa, bu
verilerin tanımladığımız kısıtlamaya uyması gerekir.
Tablo ve Sütun Kısıtlamaları
• Eğer kısıtlama sütun tanımının yanında verilirse sütun
kısıtlaması, tüm sütunlar tanımlandıktan sonra
CONSTRAINT ile başlayan ifadede verilirse tablo
kısıtlaması olarak adlandırılır.
CREATE TABLE supplier_item (
supp_no NUMBER NOT NULL, Column Constraint
item_no NUMBER NOT NULL,
qty NUMBER NOT NULL DEFAULT 0, Varsayılan Değer
CONSTRAINT pk_si PRIMARY KEY
(supp_no, item_no) );
Table Constraint: PK iki sütun ile olacaksa bu şekilde tanımlanmalı
Primary Key Constraint
• Birincil anahtar kısıtlayıcısı, bir tabloya girilen
her bir kaydın belirtilen sütun değeri olarak
diğer kayıtlardan ayırmak üzere tekil ve farklı
bir değer girmeye zorlar.
• Bir tabloda sadece bir tane Primary Key
Constraint tanımlanabilir ve bu sütun (veya
sütunlar) asla NULL değer kabul etmez.
Foreign Key Constraint
• Bir tablonun belli sütununa girilecek değerleri
başka bir tablonun indekslerle tekilleştirilmiş
bir sütunundaki değer kümesi ile
sınırlandırmak için kullanılır.
CREATE TABLE tablo_ismi (
sütun tanımlamaları...,
CONSTRAINT kısıtlayıcı-ismi
FOREIGN KEY(sütun-ismi)
REFERENCES tablo-ismi(sütun-ismi) );
Unique Constraint
• Bazı durumlarda, bir tabloda birincil anahtar alanın
yanı sıra başka bazı sütunlardaki verilerin de
tekrarlanmaması istenebilir. Bu türden alanlar için
Unique Constraint tanımı yapılır.
• Unique Constraint’i, Primary Key Constraint’ten
ayıran en önemli iki özellik,
– Unique bir tabloda birden fazla olabilir. Ancak Primary Key
tablo başına sadece bir adet alan olabilir.
– Unique Constraint tanımlı bir alan NULL değerler kabul
edebilir.
Not Null Constraint
• Eğer bir sütun için her zaman veri girilmesi
zorunlu olsun isteniyorsa “Boş Olamaz
Kısıtlayıcısı” kullanılır.
Check Constraint
• Bir sütuna girilebilecek değerleri, belli kıyaslara karşı kontrol
eden kısıtlayıcı türüdür ve üç farklı amaç için kullanılabilir:
– Bir sütuna girilebilecek değerleri belli bir küme ile kısıtlamak için
– Bir sütuna girilebilecek değerleri belli bir formata uygunluğunu
denetlemek için
– Bir sütuna girilebilecek değerleri, başka sütunlar üstünden bir kıyas ile
denetlemek için
CREATE TABLE tablo-ismi( sütun tanımlamaları...,
CONSTRAINT constraint-ismi
CHECK(ifade) FOR sütun-ismi )
İndeks Kullanımı
• İndeksler verilere hızlı erişim amacıyla kullanılır.
• OLAP sistemlerde mümkün olduğu kadar çok indeks
kullanılır, OLTP’de ise fazlası uygulamaya zarar
verebilir (özellikle çok sık güncellenen tablolarda).
• Primary Key’ler otomatik olarak indekslenir, Foreign
Key’ler de genellikle indekslenmesi gereken
alanlardır.
• WHERE koşulu ile seçilen sütunlar, aynı sırada indeks
yapılmalıdır.
Ne zaman indeks kullanmalı?
• Bir veya daha fazla sayıda sütun sıklıkla bir where koşulunda
yada tablo birleştirme işleminde kullanılıyorsa indeks
kullanmak hızı arttıracaktır.
• Eğer sorgunun sonucu tablodaki satırların %2 yada daha azı
gibi küçük bir bölümünü döndürecek ise indeks kullanımı
faydalı olacaktır.
• Sütundaki değerlerin çoğu NULL ise indeks kullanımı iyi
sonuç verir.
• Sütundaki değerler büyük ölçüde birbirini tekrar eden
değerler ise (cinsiyet sütununda sadece iki farklı değer
olması gibi) indeks kullanmanın anlamı yoktur.
• Tablo çok sık güncelleniyorsa, indeksin de güncellenmesi
gerekeceği için zaman kaybı yaratır.
İndeks Yaratma
• Aşağıdaki iki indeks aynı anda mevcut olabilir,
çünkü sütun sıraları farklıdır.
CREATE INDEX emp_idx1
ON emp (ename, job);
CREATE INDEX emp_idx2
ON emp (job, ename);
View (Görüntü)
• Gerçekte olmayan tablolardır. Sadece SELECT
ifadesinden ibarettirler.
• Neden view kullanılır?
1.
2.
3.
4.
Veri erişimini sınırlamak için
Karmaşık sorguları kolay hale getirmek için
Veri bağımsızlığını sağlamak için
Aynı verinin farklı görünümlerini sunmak için
• Eğer Primary Key View’da yer alıyorsa DML (insert,
delete, update) işlemleri o view üzerinde
uygulanabilir.
View Yaratma
• CREATE VIEW emp_view AS
SELECT empno, ename, sal, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.deptno = 10;
• CREATE VIEW dept20 AS
SELECT ename, sal*12 annual_salary
FROM emp
WHERE deptno = 20;
Synonym
• Tablo, view, sequence, procedure, function,
package veya başka bir synonym için alternatif
bir isimdir.
CREATE SYNONYM market
FOR scott.market_research;
CREATE PUBLIC SYNONYM emp
FOR scott.emp;
Public olarak tanımlanırsa, diğer
kullanıcılar scott.emp yazmadan sadece
emp yazarak ulaşabilirler.
Sequence
• Otomatik olarak artan sayılar üretmek için
kullanılır.
CREATE SEQUENCE seq_supplier
MINVALUE 1
MAXVALUE 999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
INSERT INTO (seq_supplier.NEXTVAL, …)
Transaction Nedir?
• Daha küçük parçalara ayrılamayan en küçük işlem yığınına
Transaction denir.
• Geçerli kabul edilmesi bir dizi işlemin tamamının yolunda
gitmesine bağlı durumlarda transaction kullanılır.
• Transaction bloğu ya hep ya hiç mantığı ile çalışır. Ya tüm
işlemler düzgün olarak gerçekleşir ve geçerli kabul edilir veya
bir kısım işlemler yolunda gitse bile, blok sona ermeden bir
işlem bile yolunda gitmese hiçbir işlem olmamış kabul edilir.
• Bir transaction COMMIT işlemi ile tamamlanır. Eğer ROLLBACK
işlemi uygulanırsa tüm transaction işlemleri geri alınır.
Transaction Neden Gerekli?
• Örneğin; bir havale işleminde, havale yapanın hesap
bilgilerinden havale yaptığı miktar düşüldükten sonra, elektrik
kesintisi, donanımsal veya yazılımsal bir arıza nedeniyle
alıcının hesabına bu miktar eklenemez ise; gönderenin
hesabından düşülen paranın iade edilmesi gerekir.
• Aksi halde bu paranın sahibinin kimliği kaybedilmiş olur. Bu da
sistemin olası haller dışında veri kaybetmeye müsait bir
durumda olması demektir. Bu tür aksaklıklar transaction
kavramı sayesinde kontrol altına alınır.
Transaction Örneği
• UPDATE savings_accounts
SET balance = balance – 500
WHERE account = 3209;
• UPDATE checking_accounts
SET balance = balance + 500
WHERE account = 3208;
• INSERT INTO journal VALUES
(journal_seq.NEXTVAL, '1B’, 3209, 3208, 500);
• COMMIT;
Kullanıcı Yaratma
• Kullanıcı yaratma CREATE USER komutu ile
yapılır. Bu komutu çalıştırma yetkisi genellikle
DBA yada güvenlik yöneticisi olan kişilerde
vardır.
CREATE USER AHMET
IDENTIFIED BY A853B
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
Kullanıcının Adı
Şifresi
Kullanıcının Tanımlarını Değiştirme
• Tüm nesne tanımlarının değiştirilmesinde
olduğu gibi kullanıcıların da (kullanıcılar da bir
nesnedir) tanımlarını ALTER komutu ile
değiştirebiliriz.
• Aşağıdaki komut AHMET kullanıcısının şifresini
değiştirir:
ALTER USER AHMET IDENTIFIED BY
XY358;
Kullanıcıyı Silme
• Diğer nesnelerde olduğu gibi kullanıcı silmede
de DROP komutu kullanılır:
DROP USER AHMET;
• Eğer kullanıcının şeması altında nesneler varsa
bu nesnelerin de silinmesi için CASCADE
ifadesi kullanılmalıdır:
DROP USER AHMET CASCADE;
• Eğer kullanıcı sistemde bağlı durumda ise
bağlantısını kesene kadar silinemez.
Sistem Yetkileri
• Kullanıcıların sistemde neler yapabileceklerini
sınırlayan tanımlamalara sistem yetkileri denir.
• Bir kullanıcı ilk yaratıldığı anda hiçbir yetkisi
yoktur.
• Sisteme bağlanabilmesi için CREATE SESSION
yetkisinin kullanıcıya verilmesi gerekir:
GRANT CREATE SESSION TO AHMET;
• GRANT komutu ile aynı anda birden çok yetki
birden çok kullanıcıya verilebilir:
GRANT CREATE TABLE, UNLIMITED
TABLESPACE TO AHMET, MEHMET;
Sistem Yetkileri (devam)
• Sistem Yetkileri çok fazladır, hepsinin ezbere
bilinmesinin gereği yoktur. İsminden yetkinin ne
olduğu konusunda çıkarım yapılabilir. Eğer yetki
isminde “ANY” sözcüğü geçiyorsa farklı şemalar
üzerinde işlem yapabilme yetkisi olduğunu
gösterir.
• CREATE TABLE: Kullanıcının sadece kendi şeması
üzerinde tablo oluşturma yetkisi.
• CREATE ANY TABLE: Kullanıcının tüm şemalar
üzerinde tablo oluşturma yetkisi.
Verilen Yetkilerin Geri Alınması
• Kullanıcılara verilen yetkilerin geri alınması
REVOKE komutu ile gerçekleştirilir:
REVOKE CREATE TABLE FROM AHMET;
Nesne Yetkileri
• Bir kullanıcının veri tabanındaki nesneler üzerinde
ne tür yetkileri olduğunu tanımlamak için
kullanılan yapıya nesne yetkisi denir.
• Bir kullanıcı kendi yarattığı tablolar üzerinde
SELECT yetkisine sahiptir, fakat başka şemalardaki
tablolar üzerinde SELECT sorgusu yapabilmesi için
gerekli yetkinin verilmesi gerekir.
GRANT yetkiler [(sütunlar)]
Tüm kullanıcılar
ON nesneler
TO kullanıcılar [roller] [PUBLIC];
Nesne Yetkisi Verme
• MEHMET kendisine ait olan PERSONEL tablosu
üzerinde SELECT sorgusu yapma yetkisini
AHMET’e vermek isterse:
GRANT SELECT ON PERSONEL TO AHMET;
• AHMET kendisine ait olan OGRENCILER
tablosunun sadece ADI ve SOYADI alanları
üzerinde UPDATE yapma yetkisini MEHMET’e
vermek isterse:
GRANT UPDATE (ADI, SOYADI) ON
OGRENCILER TO MEHMET;
Rol
• Rol, birçok yetkinin bir araya getirilmesi ile
oluşan yetki gruplarına verilen isimdir.
• Bir rol kendi içinde başka rolleri de içerebilir.
• Örneğin Oracle 11g’de DBA Rolünün;
– 16 Rolü
– 202 Sistem Yetkisi
– 284 Nesne Yetkisi
vardır.
* Oracle Enterprise Manager üzerinden görülebilir.
Rol Yaratma
• Rol de bir nesne olduğu için CREATE komutu
ile yaratılır. Fakat diğer nesnelerden farklı
olarak, yaratan kullanıcının şeması altında bu
roller görünmez (rol bir kişiye ait değildir).
CREATE ROLE DENEME;
GRANT CREATE TABLE, CREATE VIEW,
UNLIMITED TABLESPACE TO DENEME;
GRANT DENEME TO AHMET;
GRANT DENEME TO ALTAN WITH ADMIN
OPTION;
SYSDBA ve SYSOPER Sistem Yetkileri
• SYSDBA: Bu yetki ile veritabanına bağlanan kullanıcı tüm işlemleri
yapabilme iznine sahip olur. Bunlardan bazıları:
–
–
–
–
–
STARTUP ve SHUTDOWN işlemlerini gerçekleştirir.
CREATE DATABASE (Veritabanı yaratma)
ALTER DATABASE: open, mount, back up, karakter seti değiştirme
CREATE SPFILE (Sistem Parametre Dosyası yaratma)
ARCHIVELOG ve RECOVERY
• SYSOPER: Bu yetki ile veritabanına bağlanan kullanıcı temel işletim
görevlerini yapma iznine sahip olur, fakat kullanıcı verilerini
göremez.
–
–
–
–
STARTUP ve SHUTDOWN işlemlerini gerçekleştirir.
ALTER DATABASE: open, mount, back up
CREATE SPFILE (Sistem Parametre Dosyası yaratma)
ARCHIVELOG ve RECOVERY
SYS ve SYSTEM Kullanıcıları
• SYS kullanıcısı tüm veri tabanının ve veri
sözlüğünün sahibidir. Sisteme giriş yaparken
AS SYSDBA eki ile bağlanır. Sahip olduğu SYS
şeması üzerinde değişiklik yapılmamalıdır.
• SYSTEM kullanıcısı veri sözlüğü dışında başka
idari işler ile ilgili tabloları ve görünümleri
sağlayan SYSTEM şemasının sahibidir. Sisteme
Normal kullanıcı olarak giriş yapar.
SYS şemasının nesnelerine erişme
yetkisi olan roller
• SELECT_CATALOG_ROLE: Kullanıcılara veri
sözlüğü görünümleri üzerinde sorgu yapma
yetkisi verir.
• EXECUTE_CATALOG_ROLE: Kullanıcıların veri
sözlüğü üzerinde paket (package) ve prosedür
yürütme yetkisi olmasını sağlar.
• DELETE_CATALOG_ROLE: Sistem izleme
(audit) tabloları olan SYS.AUD$ ve
SYS.FGA_LOG$ tablolarında silme yetkisi verir.
Trigger (Tetikleyici)
• Bir veri tabanında belirli bir tabloda değişiklik
olduğunda otomatik olarak bir işlemi başlatan bir dizi
SQL ifadesidir.
• Bir trigger bir olaydan (insert, delete ya da update
ifadelerin belirtilen tabloda oluşması) ve bir
hareketten (ilgili prosedür) oluşur.
• Örneğin; personel tablosundan bir kaydın silinmesi
olayı gerçekleştiğinde, eski_personel tablosuna
personelin numarasını ve işten ayrılma tarihini
belirtmek için o anki tarihi kaydeden bir prosedür
yazılması ile bir tetikleyici oluşturulabilir.
Trigger Yaratma
CREATE [or REPLACE] TRIGGER TriggerName
[ BEFORE | AFTER ]
[ DELETE | INSERT | UPDATE [of ColumnName ] ]
ON [User.]TableName
[ FOR EACH ROW ] [ WHEN Condition ]
BEGIN
[PL/SQL Block]
END ;
• BEFORE / AFTER: DML ifadesinden önce / sonra tetikle
• FOR EACH ROW: Etkilenen her satır için tekrar tetikle
• WHEN: Tetikleyiciye koşul ekler
Trigger Örneği
Stored Procedure (Saklı Yordam)
• Stored Procedure’ler veritabanında saklanan ve gerektiğinde
ismi ile çağrılabilen PL/SQL bloklarıdır.
• Değer döndüren prosedürlere fonksiyon (function) denir.
• Önceden derlenmiş olduğu için, normal kullandığımız bir SQL
sorgusunun tekrar tekrar çalıştırılmasına nazaran daha fazla
performans elde etmemizi sağlarlar.
• Server üzerinde tutulduğundan, yükü istemci tarafına değil de,
sunucu tarafına yükleyerek programımızın performansını
arttırır. (Elbette ki bu bir seçimdir. Sunucunuz güçlüyse bu
seçimi tercih edebilirsiniz.)
Stored Procedure (Devam …)
• Bir kez yazılıp, tekrar tekrar kullanıldığı için modüler bir yapıda
program geliştirmiş oluruz.
• Aynı PL/SQL ifadesini birden fazla yerde kullanacağımız zaman,
bunu bir saklı yordam haline getirerek, kullanımını sadece
ismini çağırma ile gerçekleştirebiliriz.
• Belirli girdi ve çıktı parametreleri olduğu için, saklı yordamların
kullanımı ile güvenlik açısından kendimizi sağlama almış
oluruz.
• Ağ trafiğini azaltır. İstemci tarafından birçok satıra sahip SQL
komutunun sunucuya gitmesindense, sadece saklı yordamın
adının sunucuya gitmesi ağı daha az meşgul etmiş olur.
Stored Procedure Örneği
CREATE PROCEDURE para_yatirma
(hes_no IN NUMBER, miktar IN NUMBER)
AS
BEGIN
UPDATE hesaplar
SET bakiye = bakiye + miktar
WHERE hesap_no = hes_no;
END;
Package (Paket)
• Birbiriyle ilişkili olan prosedürlerin, fonksiyonların,
değişkenlerin ve diğer yapıların bir bütün haline
getirildiği ve veritabanında saklandığı yapıdır.
• Bir global değişkenin tanımlanıp paket içindeki
herhangi bir prosedürde çağrılabilmesi gibi ekstra
özellikler sağlar.
• Ayrıca paketler bir bütün halinde bir kerede parse
edilip, derlenip, belleğe yüklendiği için performans
artışı da sağlar.
• Paketlerde genellikle tanımlama (specification) ve
gövde (body) olmak üzere iki kısım bulunur.
Package Örneği
CREATE PACKAGE emp_actions AS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
DROP ve ALTER
• Yaratılan nesnelerin silinmesi için DROP, nesneler üzerinde değişiklik
yapmak için ALTER komutları kullanılır.
Tablo silme:
DROP TABLE ALTAN.PERSONEL;
Tabloya yeni kolon ekleme:
ALTER TABLE OGRENCI ADD (TCNO VARCHAR2(11));
Tablodaki SOYAD kolonunun veri türünü değiştirme:
ALTER TABLE OGRENCI MODIFY (SOYAD VARCHAR2(25));
Tablodan kolon silme:
ALTER TABLE DROP COLUMN TCNO;
PERSONEL'deki bölüm alanını BOLUMLER'deki BOLUM_NO alanına
ilişkilendirme:
ALTER TABLE PERSONEL ADD CONSTRAINT per_bölüm_fk
FOREIGN KEY(BOLUM) REFERENCES BOLUMLER(BOLUM_NO);
Not: Kullanıcılar ile ilgili DROP ve ALTER örnekleri önceki slaytlarda var
Detaylı bilgi için: "Oracle Database 11g: SQL Fundamentals II" altında "Les02.ppt"