Veri Tabanı Yönetim Sistemleri 2
Ders 4
Oracle'da VT Nesneleri Yaratma
Yrd. Doç. Dr. Altan MESUT
Trakya Üniversitesi
Bilgisayar Mühendisliği Bölümü
Ders İçeriği
•
•
•
•
•
•
•
•
•
Table
View
Bu nesneler haricindeki diğer
Synonym
nesnelerin yaratılmasından sonraki
derslerde bahsedilecektir.
Sequence
Type
Index
Hareket (Transaction) İşleme [Commit, Rollback]
Veri Değişiminin İzlenmesi
Geri Dönüşüm Kutusu
Tablo Yaratma: Bölümler
CREATE TABLE Bolumler(
BolumNo number(2) PRIMARY KEY,
BolumAdi varchar(20) NOT NULL,
Sehir varchar(20)
);
SQL Server ile Oracle arasındaki bazı farklılıklar:
• SQL Server'da tek bir servis altında birden çok VT vardır ve use
komutu ile VT değiştirilir. Oracle'da Her VT için ayrı servis bulunur ve
VT değiştirme komutu yoktur.
• SQL Server'da numeric veya decimal olarak kullanılan sayısal veritipi
Oracle'da da kullanılabilir ama asıl adı number'dır.
• SQL Server'da GetDate fonksiyonu yerine Oracle'da SysDate
kullanılır.
Tablo Yaratma: Personel
CREATE TABLE Personel(
PerNo number CONSTRAINT pk_Personel PRIMARY KEY,
Ad varchar(10) CONSTRAINT nn_Ad NOT NULL,
Soyad varchar(10) CONSTRAINT nn_Soyad NOT NULL
CONSTRAINT upper_Soyad CHECK(Soyad=UPPER(Soyad)),
Gorev varchar(9),
Yonetici number CONSTRAINT fk_yonetici
REFERENCES Personel(PerNo),
BaslamaTarihi date DEFAULT sysdate,
Maas number(10,2) CONSTRAINT ck_Maas CHECK(Maas>900),
Komisyon number(9,0) DEFAULT NULL,
BolumNo number(2) CONSTRAINT nn_BolumNo NOT NULL
CONSTRAINT fk_BolumNo REFERENCES Bolum(Bolum_No)
);
Buraya ON DELETE CASCADE yazılırsa bir bölüm silindiğinde
o bölümdeki tüm personel de (yani çocuk kayıtlar) silinir
Kısıtlamalar (Constraints)
• Geçen dönem (VTYS 1: Ders 10*) değindiğimiz gibi kısıtlamalar
(Primary Key, Foreign Key, Unique, Not Null, Check) tablolar
oluşturulduktan sonra da tanımlanabilirler.
• TOAD Data Modeler gibi otomatik script yaratan uygulamaların
yarattıkları script'lerde constraint tanımları genellikle CREATE TABLE
içinde değil daha sonrasında ALTER TABLE ile verilir.
• Bölümler tablosunda birincil anahtara isim verilmemiş, Personel
tablosunda ise pk_Personel ismi verilmiştir (Otomatik yaratılan
script'lerde genellikle her kısıtlamaya isim verilmektedir). İsim
verilmeyecek ise CONSTRAINT kelimesi kullanılmadan sadece
kısıtlama türü yazılır. İsim verilmeyen kısıtlamalara ORACLE "SYS" ile
başlayan ve bir numara ile devam eden bir kısıtlayıcı ismi verir.
* Ders 10 - VTYS Yaşam Döngüsü ve DDL
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);
Detaylı bilgi için: "Oracle Database 11g: SQL Fundamentals II" altında "Les02.ppt"
View (Görüntü veya Görünüm)
• Veri sözlüğünde bir SELECT ifadesi olarak saklanan
görüntüler, Access'teki sorgu nesnesi gibidir.
• View kullanmanın avantajları (VTYS1: Ders 10)
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
• Tek tabloyu sorgulayan basit görüntüler üzerinde
DML (insert, delete, update) işlemleri uygulanabilir.
Detaylı bilgi için: "Oracle Database 11g: SQL Fundamentals I" altında "Les11.ppt"
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;
Veri Sözlüğü Görüntüleri
• Veri Sözlüğünün içeriğine erişim için yaratılmış
olan SYS şemasındaki sistem görüntüleri türlerine
göre ön eklere sahiptir:
• USER: Kendi şemanızdaki nesnelerin bilgileri
• ALL: Erişebildiğiniz nesnelerin bilgileri
• DBA: Tüm şemalardaki nesnelerin bilgileri
• V_$: Sistemin çalışmasını (performansını) izlemek
için bilgiler içerir. V$ ile başlayan public
synonym'leri vardır (bak: Ders 1 Slayt 30: Dinamik
Performans Görünümleri)
Detaylı bilgi için: SQL Fundamentals II – Les03
Örnek: V$DATABASE
• Sistem görüntülerinden biri olan V$DATABASE
ile veritabanı hakkında bilgi sahibi olunabilir.
• Örneğin aşağıdaki sorgu ile o an bağlı olunan
VT'nin ismi ve CDB olup olmadığı görülebilir:
SELECT NAME, CDB FROM V$DATABASE;
– CDB = TRUE ise kullanıcılar ortak kullanıcı olarak
yaratılmalı (isimleri C## ile başlamalı)
– CDB = FALSE ise kullanıcılar yerel kullanıcı olarak
yaratılmalı (isimleri C## ile başlamamalı)
Synonym
• Tablo, view, sequence, procedure, function,
package veya başka bir synonym için alternatif
bir isim vermek için kullanılır.
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 seqDeptNo
MINVALUE 1
Eğer CYCLE yazılırsa
MAXVALUE 999999
maksimum değere ulaşılınca
START WITH 10
minimum değere dönülür
INCREMENT BY 10
CACHE 20;
[sequence_ismi].CURRVAL ile sequence'in mevcut değerini öğrenilir, NEXTVAL ile
mevcut değeri alınıp bu değer "increment by" kadar arttırılır. Genellikle birincil
anahtara değer almak için NEXTVAL kullanılır: INSERT INTO … (seqDeptNo.NEXTVAL, …)
NOMAXVALUE, NOMINVALUE, NOCACHE, NOCYCLE gibi yazımlar değer verilmediğini
gösterir (ki varsayılan zaten verilmemesidir). SQL Fundamentals I – Les11 – Slayt 24-32
Type
• C dilindeki struct gibi kullanıcı tanımlı veri tipi
yaratmak için kullanılır.
CREATE TYPE SCOTT.Phone_List_Type
AS VARRAY(5) OF VARCHAR2(25);
CREATE TYPE SCOTT.Cust_Address_Type
AS OBJECT
( street_address
VARCHAR2(40)
, postal_code
VARCHAR2(10)
, city
VARCHAR2(30)
, state_province
VARCHAR2(10)
, country_id
CHAR(2)
, phone
Phone_List_Type
);
Index
• İndeks kullanmanın hangi durumlarda fayda
sağlayacağından geçen dönem (VTYS 1: Ders 10)
bahsedilmişti.
• Oracle'da 3 tip indeks vardır:
– Non-Unique: aynı verinin tekrar edebildiği alanlar için
– Unique: verileri tekil olan alanlar için
– Bitmap: verileri büyük oranda birbirini tekrar eden
alanların birlikte sorgulanması durumları için
• Tabloda PRIMARY KEY veya UNIQUE olarak
belirlenen alanlar otomatik olarak "unique"
türünde indekslenir.
SQL Fundamentals I – Les11 – Slayt 35-39
Index Yaratma
CREATE INDEX C##SELIM. INDEX_ISIM
ON C##SELIM.PERSONEL (AD, SOYAD);
AD ve SOYAD alanları birbirini tekrar edebilen veriler içereceği için non-unique indeks
oluşturulmalıdır. CREATE ile INDEX arasına UNIQUE veya BITMAP yazılarak bu türden
indeksler oluşturulabilir. Hiçbir ifade yazılmaz ise NON-UNIQUE indeks oluşturulur.
Bitmap Index
• Oracle varsayılan olarak unique ve non-unique indeksler için
B-Tree yapısını kullanır.
• Fakat, içerdiği veriler fazla değişiklik göstermeyen, büyük
oranda birbirini tekrar eden alanlar birlikte sorguda yer alacak
ise Bitmap indeks kullanmak daha iyi sonuç verir.
• Örneğin 'Mavi' renk '1981' model 'Toyota' marka arabalar
sorgulanacak ise; ilgili tüm sütunların birbirinden farklı
(distinct) değerlerinin 100'den az olduğunu düşünecek
olursak, her sütun için oluşturulan distinct listelerinin
kesişimini bulmak çok daha hızlı sonuç verecektir.
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;
Veri Değişiminin İzlenmesi
• Verilerin hangi zamanda değiştiğini öğrenmek
için sorguda VERSIONS BETWEEN SCN
MINVALUE AND MAXVALUE kullanabiliriz:
SELECT versions_starttime "START_DATE",
versions_endtime
"END_DATE",
salary
FROM
employees
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE last_name = 'Lorentz';
SQL Fundamentals II – Les04 – Slayt 38-41
Geri Dönüşüm Kutusu
• Silinen tablolar geri dönüşüm kutusunda (recyclebin)
tutulur. Aşağıdaki ifade geri dönüşüm kutusundaki
silinmiş olan nesnelerin kaydını gösterir:
SELECT original_name, operation,
droptime FROM recyclebin;
• FLASHBACK TABLE ifadesi ile silinen tabloları geri
getirebiliriz.
FLASHBACK TABLE emp2 TO BEFORE DROP;
• PURGE ifadesi ile (DROP TABLE … PURGE) tablo
sildiyseniz, tablo gerçek anlamda silineceği için geri
alamazsınız.
Detaylı bilgi için: SQL Fundamentals II – Les02
Download

Ders 4 - Oracle`da VT Nesneleri Yaratma - Altan MESUT