Stored Procedure Nedir ? Nasıl Kullanılır ?

Merhaba,

Bu yazımda stored procedure hakkında bilgiler vereceğim. Stored Procedureler SQL’de bize çok kolaylık sağlayan , sistemin yorulmasını en aza düşüren , bir kere derlendikten sonra defalarca kullanılabilen veritabanı için olmazsa olmaz nesnelerdir. Büyük projelerde mutlaka ihtiyaç duyduğumuz objelerdir. Kısaca SP şeklinde de kullanılır. Programlama dilleri ile DML dillerini birbirinden ayrı tutar.

• Çok hızlı çalışırlar.
• Bir kere derlendikten sonra defalarca kullanılabilirler. Uygulama katmanında eğer SQL kodu olsaydı her çağrımda tekrar tekrar derlenecekti bu da bizim network sistemimizin yorulmasına neden olacaktı . SP’ler bu durumum önüne geçer. (En önemli özellik)
• SP’ler de programlama dillerinde kullandığımız döngüleride (for,if,while) kullanabiliriz.
• Güvenlik açısından da sadece giriş ve çıkış parametrelerini uygulama katmanından kullandığı için daha güvenlidir.

Genellikle 4 çeşidi bulunmaktadır.

Sistem Stored Procedure : Master veritabanında tutulan kullanımında sp_ prefixi kullanılan stored procedure lardır. Veritabanı üzerinde sistemle alakalı yönetimsel işlerde kullanılırlar.

Kullanıcı Tabanlı Stored Procedure : Uygulamalarımız için kullandığımız sp lerdir.Bu sp leri çok rahat oluşturup silebilir ve değiştirebiliriz.

Extended Stored Procedure :  Genelde .dll şeklinde derlenmiş olup Database Server ın işlevselliğini artıran bir çeşit stored procedure lerdir. Yalnız Master veritabanına eklenirler.

CLR Stored Procedure : CLR ortamında herhangi bir dili kullanarak da stored procedure ler geliştirilen bir tür Stored procedure çeşididir.

Şimdi örnek INSERT , UPDATE , DELETE ve SELECT SP’leri yazalım.

Genellikle SP kalıbı şu şekildedir ;

[sql] CREATE OR REPLACE PROCEDURE prosedürAdı ( degiskenler )
Ek Bilgiler
BEGIN
yazılacak procedure
END prosedürAdı;
[/sql]

*REPLACE kavramı SP’nin sonradan değiştirebilir olması için kullanılır.

– İlk olarak bir çalışan tablosu oluşturalım.

[sql] create table TBL_EMPLOYEE
(
EMPLOYEEID NUMBER,
NAME NVARCHAR2(20),
SURNAME NVARCHAR2(20),
AGE NUMBER,
JOB NVARCHAR2(40),
SALARY NUMBER
)
[/sql]

– Oluşturduğumuz tablomuz için INSERT SP’mizi yazalım.

[sql]

CREATE OR REPLACE PROCEDURE TBL_EMPLOYEE_EKLE
(
p_EmployeeId out TBL_EMPLOYEE.EMPLOYEEID%TYPE — "out" dedik veriyi Sequenceden alacağız.
p_Name in TBL_EMPLOYEE.NAME%TYPE; — belirtilen tablonun veri tipinde olsun dedik.
p_Surname in TBL_EMPLOYEE.SURNAME%TYPE;
p_Age in TBL_EMPLOYEE.AGE%TYPE;
p_Job in TBL_EMPLOYEE.JOB%TYPE;
p_Salary in TBL_EMPLOYEE.SALARY%TYPE;
) IS

v_EmployeeId TBL_EMPLOYEE.EMPLOYEEID%TYPE;

BEGIN

SELECT TBL_EMPLOYEE.SEQ.NEXTVAL — Sequence kullanımından bahsetmiştik onu kullandık.
INTO v_EmployeeId
FROM dual;

INSERT INTO TBL_EMPLOYEE E
(
E.EMPLOYEEID,
E.NAME,
E.SURNAME,
E.AGE,
E.JOB,
E.SALARY
)
VALUES
(
v_EmployeeId,
p_Name,
p_Surname,
p_Age,
p_Job,
p_Salary
)

p_EmployeeId := v_EmployeeId;

END TBL_EMPLOYEE_EKLE;

[/sql]

– Oluşturduğumuz tablomuz için UPDATE SP’mizi yazalım.

[sql]

CREATE OR REPLACE PROCEDURE TBL_EMPLOYEE_GUNCELLE
(
p_EmployeeId in TBL_EMPLOYEE.EMPLOYEEID%TYPE
p_Name in TBL_EMPLOYEE.NAME%TYPE;
p_Surname in TBL_EMPLOYEE.SURNAME%TYPE;
p_Age in TBL_EMPLOYEE.AGE%TYPE;
p_Job in TBL_EMPLOYEE.JOB%TYPE;
p_Salary in TBL_EMPLOYEE.SALARY%TYPE;
) AS

BEGIN
UPDATE TBL_EMPLOYEE E
SET E.NAME = p_Name,
E.SURNAME = p_Surname,
E.AGE = p_Age,
E.JOB = p_Job,
E.SALARY = p_Salary

WHERE E.EMPLOYEEID = p_EmployeeId;

END TBL_EMPLOYEE_GUNCELLE;

[/sql]

– Oluşturduğumuz tablomuz için DELETE SP’mizi yazalım.

[sql]

CREATE OR REPLACE PROCEDURE TBL_EMPLOYEE_SIL
(
p_EmployeeId in TBL_EMPLOYEE.EMPLOYEEID%TYPE

) AS

BEGIN
DELETE FROM TBL_EMPLOYEE E

WHERE E.EMPLOYEEID = p_EmployeeId;

END TBL_EMPLOYEE_SIL;

[/sql]

– Oluşturduğumuz tablomuz için SELECT SP’mizi yazalım.

[sql]

CREATE OR REPLACE PROCEDURE TBL_EMPLOYEE_GOSTER
(
p_EmployeeId in TBL_EMPLOYEE.EMPLOYEEID%TYPE –tek tek numara bazlı çekmek için
RC1 in out SYS_REFCURSOR — hepsini çekebilmek için
) AS

BEGIN
IF p_EmployeeId > 0 THEN
OPEN RC1 FOR
SELECT E.EMPLOYEEID,
E.NAME,
E.SURNAME,
E.AGE,
E.JOB,
E.SALARY
FROM TBL_EMPLOYEE E
WHERE E.EMPLOYEEID = p_EmployeeId;
ELSE
OPEN RC1 FOR
SELECT E.EMPLOYEEID,
E.NAME,
E.SURNAME,
E.AGE,
E.JOB,
E.SALARY
FROM TBL_EMPLOYEE E
ORDER BY E.EMPLOYEEID ASC; — hepsini gösterirken küçükten büyüğe sırala dedik.

END IF;

END TBL_EMPLOYEE_GOSTER;

[/sql]

STORED PROCEDURE’ler hakkında aktaracaklarım bu kadar umarım yararlı olmuştur.
İyi çalışmalar.