SQL'de VIEW Oluşturma ve Kullanımı

Temel amacı tabloların içerisinden veri kümesi getirip ortaya çıkan sonucu sanal tabloymuş gibi yeniden sorgulayabilmemizi sağlamaktır. Kısaca SQL'de verilerimizin isteğe bağlı alanlara ve tablolara göre ekranımızda görünteleme işlemi yapmamızı sağlar. Kullanıcının istediği verilere göre gösterim yapmak veya raporlamak SQL VIEW sayesinde kolaylıkla halledilmektedir.

Veritabanı VIEW ile oluşan datayı saklamaz. Bir VIEW geçen SQL deyimi her çalıştırıldığında ilgili VIEW sanal tabloyu yeniden oluşturur. VIEW üzerinde yapılan değişikler kendilerini oluşturan kaynak tabloları da etkiler ve aynı değişiklikler kaynak tablolara da yansır. VIEW kullanmanın önemli nedenlerinden bir tanesi sağladığı güvenliktir. Örneğin tablolarınızın tamanının görünmesini istemediğiniz zamanlarda sanal tablo kullanıp tablolarınızın tamamının görünmesini engelleyebilirsiniz. Karmaşık sorguları basitleştirmek, sorgu süresini kısaltmak ve ağ üzerindeki trafiği düşürmek, erişim izinlerini düzenlemek ve farklı sunuculardaki benzer verileri karşılaştırmak içinde kullanılır.

Oluşturulan VIEW'ler Object Explorer kısmında database/Views altında toplanır.

VIEW Oluşturma: VIEW oluşturmak için CREATE VIEW ifadesi kullanılır.

Genel Yapısı:

CREATE VIEW view_adı 
AS 
SELECT * FROM tablo_adi

Örnek1:

CREATE VIEW deneme_view 
SELECT EmployeeID, FirstName, LastName
FROM Employees

Örneğimizde Northwind veritabanında Employees tablosundan sadece "EmployeeID, FirstName, LastName" kullanarak yeni bir view oluşturalım ve "select * from deneme_view" ile sorgumuzu çalıştıralım.

Örnek2:

CREATE VIEW deneme2_view
AS
SELECT FirstName,LastName
FROM Employees
WHERE FirstName LIKE 'a%'
WITH CHECK OPTION

Bu örneğimizde diğerinden farklı olarak bir şart belirttik ve WITH CHECK OPTION ifadesini kullandık.

WITH CHECK OPTION: SQL Server'da VIEW tanımlarken örneğimizdeki gibi WHERE anahtar sözcüğüyle bir şart belirtmiş olabiliriz. Böyle bir VIEW nesnesini kullanırken de VIEW üzerinden tablolarımıza "INSERT, UPDATE, DELETE" işlemleri gerçekleştirebiliriz. 

Şimdi diyelimki oluşturduğumuz VIEW'e bir INSERT yapmamız gerekti;

insert into deneme2_view values('Bayraktar','İbrahim')

eklenme yapılmak istendiğinde "The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated." hatasıyla karşılaşırız (VIEW oluştururken kullandığımız WITH CHECK OPTION ifadesinden dolayı 'a' ile başlamayan bir ekleme yapılamaz)

VIEW Silme: VIEW'leri silmek için DROP ifadesi kullanılır.

Genel Yapısı:

DROP VIEW view_adı

Örnek3:

DROP VIEW deneme2_view

VIEW Değişiklik Yapma: VIEW'ler üzerinde değişiklik yapmak için ALTER ifadesi kullanılır.

Genel Yapısı:

ALTER VIEW view_adı 
WITH seçenekler 
AS SELECT ifadesi

with schemabinding ifadesi ile kilitleme:

with schemabinding ifadesi VIEW'in bağlı olduğu tablodaki kolonları kilitleyip o kolonların silinmesini ve değişiklik yapılmasını engeller.

Genel Yapısı:

ALTER VIEW view_adı 
WITH schemabinding 
AS 
SELECT ifadesi

Örnek3:

CREATE VIEW deneme3_view
WITH SCHEMABINDING --sütunla ilgili bir değişiklik yapılması engellenir
AS
SELECT FirstName,LastName,City
FROM dbo.EMPLOYEES

with encryption ifadesi ile gizleme: 

VIEW'leri şifreleyip sorguların görünür olmasını engeller. Kaynakların  gizliliğini sağlayan koddur, Kullanıcı sadece oluşturulan VIEW ile ilgili verilere erişebiliR kaynak tablolarla ilgili hiç bir bilgiye ulaşamaz.

Genel Yapısı:

ALTER VIEW view_adı 
WITH encryption 
AS 
SELECT ifadesi

Örnek4:

CREATE VIEW deneme4_view
WITH ENCRYPTION --kaynak gizleme ve pasif design
AS
SELECT * FROM PRODUCTS

with encryption ifadesi kullanıldığında Object Explorer'da VIEW üzerinde kilit işareti belirir ve sağ click yapıldığından Design seçeneğinin pasifleştiğini görebiliriz.

SQL'de sp_helptext System Prosedürünün Kullanımı

sp_helptext prosedürü bizim tanımladığımız ya da sistemde bulunan objelerin tanımlamalarını text formatında gösterir. Böylece istediğimiz objenin tüm içeriğini nerede olduğunu aramadan direk  text içeriği ekrana döndürebiliriz.

Genel Yapısı:

exec sp_helptext '@objname'

Şimdi bir örnek ile olayı daha iyi kavrayalım. SP_ORNEK_1 sp'sinin içeriğini görüntülemek için;

use Northwind
exec sp_helptext 'SP_ORNEK_1'

yazmamış yeterli olacak. Bu komutun sonucunda ilgili Prosedürün text içeriği ekrana dökülür. SQL Server'da bir nesnenin SQL kodunu merak ediyorsanız sp_helptext bizim için en iyi araç. Tabi bu sp ile SQL Server'ın system sp, view ve diğer bileşenlerinin kodunu da görüntüleyebilirsiniz. Böylelikle merak ettiğiniz herhangi bir nesnenin nasıl kodlandığı incelenebilir.

use Northwind
exec sp_helptext 'Sales by Year'

Northwind veritabanı ile birlikte gelen "Sales by Year" isimli Prosedürün text içeriğini alalım.

SQL'de sp_help System Prosedürünün Kullanımı

sp_help System Prosedürü SQL'de  veritabanı ve sistem üzerindeki tüm tablolar, view ler, stored procedure ler gibi nesne tipleri hakkında ayrıntılı bilgi almamızı sağlar.

Genel Yapısı:

exec sp_help '@objname'

sp_help ile Nesneler hakkında bilgi almak için:  

use Northwind
exec sp_help

Örneklerde sıklıkla kullandığım Northwind veritabanındaki herbir objenin bilgilerini listeleyelim.

sp_help ile istenilen Obje hakkında bilgi almak için: 

use Northwind
exec sp_help 'Customers'

Northwind veritabanındaki Customers tablosu ile ilgili bilgileri listeleyelim.

SQL'de sp_rename System Prosedürü ile Nesne ismi Değiştirmek

sp_rename System Prosedürünü SQL'de  nesnelerimizin adlarını değiştirmek amacıyla kullanırız.

NOT: Oluşturulan her bir nesne nin bir id si vardır ve SQL Server'da nesneler id leri ile saklanırlar.

Genel Yapısı

exec sp_rename @object_name, @new_object_name, @object_type

sp_rename ile Tablo adının değiştirilmesi :   

use KITAPLAR
SELECT * FROM Fantastikkurgu

SELECT object_id('Fantastikkurgu')
exec sp_rename  'Fantastikkurgu','Bilimkurgu' 

--Fantastikkurgu tablomuz Bilimkurgu olarak değişecektir

işlem sonrasında SELECT * FROM Fantastikkurgu sorgusunu çalıştırdığımızda Invalid Object Name 'Fantastikkurgu' hatası döndürecektir.  Sorgumuzu SELECT * FROM Bilimkurgu olarak çalıştırdığımızda ise Fantastikkurgu tablosunda mevcut olan kolonları göreceksiniz. Benzer şekilde Object Explorer üzerinden tables'a refresh yaparsanız yine tablo adının değiştiğini görebilirsiniz.

object_id system function'ını kullanarak objenin id'sini öğrenebiliriz

object_id (objectname) null döndürüyorsa o isimde nesne ilgili database de yok demektir.

 
sp_rename ile Kolon adının değiştirilmesi :  

Yukarıdaki örnekte adını değiştirdiğimiz tablonun "Yazar" kolonunu "müellif" olarak değiştirelim. sp_rename prosedürünün default'u "object" olan bir parametresi daha var. Kolonlar, sysobjects de yer almazlar ve birer database objesi değillerdir ve tablo ismi olmadan da birşey ifade etmezler. x tablonun y kolonu diye saklanırlar ve çağırılırlar. Bu nedenle adını değiştirdiğimiz nesnenin kolon tipinde olduğunu ve hangi tabloya ait olduğunu da belirtmemiz gereklidir.

exec sp_rename 'Bilimkurgu.Yazar','müellif','COLUMN'

Bilimkurgu tablomuza ait olan Yazar isimli kolonu müellif olarak değiştirdik. SELECT * FROM Bilimkurgu  sorgusu ile kolon adının değiştiğini görebiliriz. 

sp_rename ile Veritabanı adının değiştirilmesi:

Veri tabanının adını değiştirmek için sp_renamedb prosedürü kullanılır.

sp_renamedb 'KITAPLAR', 'ESERLER' veya sp_renamedb KITAPLAR , ESERLER

“KITAPLAR” ismindeki veri tabanımızın yeni adı “ESERLER” olacaktır.

sp_rename ile Constraint (Kısıtlama) adının değiştirilmesi:

Constraint adı değiştirme tablo adı değiştirme ile aynıdır. Bir veri tabanında aynı isimde birden fazla tablo olamayacağı için aynı isimde birden fazla Constraint olamaz. Bu nedenle Constraint adını değiştirince hangi tabloya aittir diye bakmıyoruz.

CREATE TABLE [EPOSTA] 
( 
[ADI SOYADI] VARCHAR(50),
 EPOSTA VARCHAR(200) 
 CONSTRAINT EPOSTA_KONTROL CHECK(EPOSTA LIKE '%@%.edu.tr') 
)

Örnek EPOSTA tablomuzda EPOSTA_KONTROL adında bir constraint (kısıtlama) mevcut. Bu constraint eposta içinde "@" işareti var mı ve eposta adresi ".edu.tr" ile bitiyor mu diye kontrol ediyor. Bu constraint in adını değiştirmek için sp_rename kullanalım.

exec sp_rename EPOSTA_KONTROL, MAIL_KONTROL

"EPOSTA_KONTROL" olan constraint adı "MAIL_KONTROL" olarak değişecektir.

SQL Server'da Stored Procedure Temel Örnekler

okul adında bir veritabanı oluşturalım ve aktif çalışılacak şekilde belirleyelim.

CREATE DATABASE okul
USE okul

Oluşturduğumuz veritabanı içerinde ogrenci adında bir tablo yaratalım ve tabloya ait veri tiplerini belirleyelim.

CREATE TABLE ogrenci 
(
adi nvarchar(20),
soyadi nvarchar(25), 
ogr_no numeric(10),
bolum nchar(5),sehir nvarchar(30)
)

Oluşturduğumuz ogrenci tablomuzu kontrol amaçlı "liste" adında bir prosedür yazalım ve çalıştıralım.

CREATE PROCEDURE liste
AS
SELECT * FROM ogrenci

EXEC liste

Oluşturduğumuz ogrenci tablomuza  veri ekleme amaçlı "ekle" adında bir prosedür yazalım ve çalıştıralım.

CREATE PROCEDURE ekle
@isim nvarchar(20),
@sisim nvarchar(25),
@num numeric(10),
@bol nchar(5),
@il nvarchar(30)
AS
INSERT INTO ogrenci (adi,soyadi,ogr_no,bolum,sehir)
values(@isim,@sisim,@num,@bol,@il)


EXEC ekle 'İbrahim','BAYRAKTAR',3005,'BLG','YOZGAT'

Benzer şekilde eklediğimiz kayıtları silebilmek için bir "sil" prosedürü yazalım ve çalıştıralım.

CREATE PROCEDURE sil
@id numeric(10)
AS
DELETE FROM ogrenci WHERE ogr_no=@id

EXEC sil 3005

Eklemiş olduğumuz veriler üzerinde id belirterek kolaylıkla şehir kaydını değiştirebileceğimiz "guncelle" adında prosedürü yazalım ve çalıştıralım.

CREATE PROCEDURE guncelle
@il nvarchar(30),
@id numeric(10)
AS
UPDATE ogrenci SET sehir=@il
WHERE ogr_no=@id

EXEC guncelle 'ANKARA',3005

tablomuzda arama yapmak amacıyla kullanılacak basit bir arama prosedürü yazalım ve çalıştıralım.

CREATE PROCEDURE ara
@isim nvarchar(20)
AS
SELECT * FROM ogrenci  WHERE adi LIKE '%'+@isim+'%'

EXEC ara 'ib'