SQL * LOADER

-----------METIN BELGESİNİ AKTARMAK İÇİN----------------

1-d: diskinde 'deneme.ctl' kontrol dosyası oluşturulup içine aşağıdaki bilgiler girilir,

LOAD DATA
INFILE 'd:\tablo.txt'
INTO TABLE sql_loader
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(no,ad,soyad)

yada belirli koordinatlardaki kolonları sql*loader ile yüklemek için;

LOAD DATA
INFILE '/tmp/orhan/20_02_2009.txt'
INTO TABLE sql_loader
(no POSITION(13:13),
ad POSITION(24:24),
soyad POSITION(25:35))

2-tabloya aktarmak istediğimiz dosya d: diskinde yer alıyor(tablo.txt),

3-istediğimiz kullanıcıya(örn. hr) 'sql_loader' adında tablo oluşturuyoruz,


CREATE TABLE sql_loader (no number,ad varchar2(10),soyad varchar2(10));


4-cmd'ye aşağıdaki komut satırını giriyoruz.

sqlldr userid=hr/o control=d:\deneme.ctl log=d:\deneme.log

tablomuzu okuttuğumuzda tüm kayıtların geldiğini görürüz.

select * from sql_loader;
select count(*) from sql_loader;


----------EXCEL BELGESİNİ AKTARMAK İÇİN-------------------

ecxel belgesini .csv formatında kaydedip kontrol dosyasını aşağıdaki gibi yapmak lazım,
csv dosyası olduğundan burada koşulumuz ; ile ayrılmasıdır,

LOAD DATA
INFILE 'd:\tablo.csv'
INTO TABLE sql_loader
FIELDS TERMINATED BY ';'
trailing nullcols
(no,ad,soyad,sayi)


d: diskinde tablo.csv dosyamız var,


hr kullanıcısına;

CREATE TABLE sql_loader (no number,ad varchar2(50),soyad varchar2(50),sayi number);


sqlldr userid=hr/o control=d:\deneme.ctl log=d:\deneme.log

tablomuzu okuttuğumuzda tüm kayıtların geldiğini görürüz.

select * from sql_loader;
select count(*) from sql_loader;

AUTONUMBER

Oracle'da autonumber alanı oluşturmak için öncelikle 'deneme' adında bir tablo oluşturalım,

create table hr.deneme (id number, adi varchar2(20));

sonra da bir sequence create edelim,

create sequence hr.autonumber
start with 1
increment by 1
nomaxvalue;

ve böylelikle aşağıdaki gibi tabloya her kayıt eklediğimizde ' id ' alanı otamatik sırayla artacaktır,

insert into hr.deneme values (hr.autonumber.nextval,'ahmet');

TRACE VE ALERT KÜTÜKLERİ

Oracle çalışırken hata oluştuğu zaman bu hata mesajları alert kütüğüne yazılır,eğer hata server yada arka plan görevlerince yakalanırsa bu hata mesajları trace kütüğüne boşaltılır,

Alert kütükleri mesaj ve hataları kronolojik olarak tutar,
İnternal(içsel),corruption(block bozulma),deadlock(kilitlenme),dml işlemleri,startup,shutdown işlemleri ve veritabanı ,instance başlatıldığı andaki parametre değerleri gibi bilgileri içerir,

Alert kütüğü BACKGROUND_DUMP_DEST parametresi ile belirtilen konumda bulunur.

Trace kütükleri eğer arka plan görevi tarafından yazılmışsa BACKGROUND_DUMP_DEST,
Server görevi tarafından yazılmışsa USER_DUMP_DEST ile belirtilen konumlarda bulunur.
Trace günlükleme, SQL_TRACE parametresinin TRUE ya da FALSE değerlerini alır.

SQL> alter session set sql_trace true;
SQL> alter session set sql_trace false;

Not: eğer trace dosyalarınızın çoğalmasından ötürü diskinizde yer kalmadıysa sql_trace parametresini false değeri ile değiştirmeniz gerekir.

BLOCK BUFFER CACHE

SGA (System Global Area) içinde yer alan ‘block buffer cache’ –block ön belleğinde veriler öncelikle burada tutulur ve istenildiğinde kullanılır,verilerin tutulduğu bellekten daha hızlı getirilmesi için LRU(Last Recently Used)
algoritması tasarlanmıştır. Performans açısından çok büyük bir etki sağlar.
dba_extents ve x$bh tablolarından faydalanarak veriyi isteme durumlarına bakalım;


conn sys as sysdba

create table test (a number);
insert into test (a) values(10);
commit;


SELECT ext.block_id FROM dba_extents ext
WHERE lower(ext.segment_name) = 'test'
AND ext.owner = 'SYS'

BLOCK_ID
--------------
86601

Tablomuz 86601 id numaralı blok içinde yer alıyor,

Select * from test;

İle tablomuzu çağıralım ve aşağıdaki select ile veriyi istenme sayısına bakalım;


SELECT x.tch FROM x$bh x
WHERE x.dbablk = 86601
AND x.file# = 1

TCH
-------
4

İstenme sayısı (tch) ‘nın 4 olduğunu görüyoruz,tekrar tablomuzu çağıralım ve istenme sayısına bakalım,

SELECT * FROM test

SELECT x.tch FROM x$bh x
WHERE x.dbablk = 86601
AND x.file# = 1

TCH
-----
5

istenme sayısının 5 olduğunu ve tekrarlandığında LRU algoritmasıyla sayının arttığını görüyoruz.

PACKAGE (PAKET)

Oracle’da paket olarak adlandırılan nesneler, birbiriyle ilişkili fonksiyon
ve prosedürlerin bir isim altında gruplanmasıyla oluşturulur.
PL/SQL paketleri sayesinde daha modüler programlar geliştirilebilir.
Birden fazla uygulama tarafından kullanılan bir paket üzerinde değişiklik yapılarak,
tüm bu uygulamaların güncellenmesi sağlanabilir. Bir paket içerisindeki bir alt program
çağrıldığında, tüm paket belleğe alınır ve bu paketle ilişkili diğer alt programlar
bu sayede hızlı çalıştırılabilir.

----------------------------------
PACKAGE paket_ismi IS

[değişkenlerin tanımlanması]
[cursor tanımlanması]
[modullerin tanımlanması]

END paket_ismi;

----------------------------------
PACKAGE BODY paket_ismi IS

[değişkenlerin tanımlanması]
[select cümleciği ile cursor tanımlanması]

[modullerin içeriğinin tanımlanması]

[ BEGIN

dml işlemlerimiz]

[EXCEPTION

yakalanan hata tanımlamalarımız]

END paket_ismi;

-------------------------PAKET ÖRNEK-1-----------------

create or replace package paket1 as
procedure pro1;
function fonk1 return varchar2;
end paket1;
/
----------------------------------------
create or replace package body paket1 as
procedure pro1 is
begin
dbms_output.put_line('merhaba prosedür!');
end pro1;
function fonk1 return varchar2 is
begin
return ('merhaba fonksiyon!');
end fonk1;
end paket1;
/
----------------------------------------
set serveroutput on
exec paket1.pro1;

--------------------
select paket1.fonk1 from dual;

--------------------PAKET ÖRNEK-2-----------------


CREATE OR REPLACE PACKAGE hasta_islem as

PROCEDURE hasta_kayit (hasta_id NUMBER, hasta_no NUMBER, hasta_adi VARCHAR2,klinik_id NUMBER);

PROCEDURE hasta_sil (hastaid NUMBER);

END hasta_islem;
/
----------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY hasta_islem as

PROCEDURE hasta_kayit (hasta_id NUMBER, hasta_no NUMBER, hasta_adi VARCHAR2,klinik_id NUMBER) IS

BEGIN

INSERT INTO hastalar2 VALUES (hasta_id, hasta_no, hasta_adi,klinik_id);

END hasta_kayit;


PROCEDURE hasta_sil (hastaid NUMBER) IS

BEGIN

DELETE FROM hastalar2 WHERE hasta_id = hastaid;

END hasta_sil;

END hasta_islem;
/

exec hasta_islem.hasta_kayit(85,787,'kemal',3);
exec hasta_islem.hasta_sil(85);

TRİGER (TETİKLEYİCİ)

----------------------------TRIGER ÖRNEK-1 ----------------------

alter table hastalar add cinsiyet char(1) default 'E';

insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(20,225,'can',2);

CREATE OR REPLACE TRIGGER TRG_CINSIYET
BEFORE INSERT
ON HASTALAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:NEW.CINSIYET := 'K';
END;
/

insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(30,250,'ayşe',1);

--------------------------TRIGER ÖRNEK-2----------------------
(DİĞER TABLOLARA DA AYNI DEĞİŞİKLİK)

create table hastalar2 as select * from hastalar;

CREATE OR REPLACE TRIGGER TRG_TABLOLARA_UYGULA
AFTER
UPDATE OF HASTA_ADI
ON HASTALAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
begin
UPDATE HASTALAR2 SET HASTA_ADI=:NEW.HASTA_ADI WHERE HASTA_ID=:NEW.HASTA_ID;
end TRG_TABLOLARA_UYGULA;
/

update hastalar set hasta_adi='canan' where hasta_adi='can';


-----------------TRIGER ORNEK-3(LOG TABLOSU İÇİN)---------------

CREATE TABLE HASTALAR_LOG AS SELECT * FROM HASTALAR;


CREATE OR REPLACE TRIGGER TRG_HASTALAR_LOG
AFTER
INSERT OR UPDATE
ON HASTALAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare

begin

if inserting then

INSERT INTO HASTALAR_LOG
(HASTA_ID,HASTA_NO,HASTA_ADI,KLINIK_ID,CINSIYET)
VALUES
(:NEW.HASTA_ID,:NEW.HASTA_NO,:NEW.HASTA_ADI,:NEW.KLINIK_ID,:NEW.CINSIYET);

elsif updating then

INSERT INTO HASTALAR_LOG
(HASTA_ID,HASTA_NO,HASTA_ADI,KLINIK_ID,CINSIYET)
VALUES
(:NEW.HASTA_ID,:NEW.HASTA_NO,:NEW.HASTA_ADI,:NEW.KLINIK_ID,:NEW.CINSIYET);
end if;

end TRG_HASTALAR_LOG;
/

insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(50,450,'mustafa',1);
select * from hastalar_log;

----------------------------TRIGER ORNEK-4-------------------------
(HASTA_NO KARAKTER UZUNLUĞU)


CREATE OR REPLACE TRIGGER TRG_HASTA_NO
AFTER
INSERT OR UPDATE OF HASTA_NO
ON HASTALAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare

begin

IF (:NEW.HASTA_NO IS NOT NULL) AND (LENGTH(:NEW.HASTA_NO) < 3)
THEN
raise_application_error(-20070,'HASTA NUMARASI 3 KARAKTERDEN AZ OLAMAZ!!!İŞLEM İPTAL!');
END IF;

end ;
/

insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(70,50,'mehmet',1);

------------------TRIGER ORNEK-5 (KARŞILAŞTIRMA)---------------

CREATE OR REPLACE TRIGGER TRG_SORGULAMA
BEFORE
INSERT
ON HASTALAR
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
VHASTA_NO NUMBER;

begin
begin
SELECT HASTA_NO INTO VHASTA_NO
FROM HASTALAR WHERE HASTA_ADI=:NEW.HASTA_ADI AND HASTA_NO <> :NEW.HASTA_NO;

IF VHASTA_NO > 0 THEN
raise_application_error(-20090,VHASTA_NO||' NOLU HASTA DA AYNI İSİM VAR.İŞLEM İPTAL!!!');
END IF;

exception when no_data_found then
null;
end;
end;
/

insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(70,50,'ahmet',1);

CURSOR KULLANIMI

-cursor,memory'i kullanan bir mekanizma olduğundan en hızlı data çekme işlemi yapılır.

open - ile cursor açılır,
fetch - ile tek tek kayıtlar okunur,
close - ile de kapanır.

cursor cursor_adi is
select cümlemiz.......

şeklinde tanımlanır,

open cursor-adi;
fetch cursor_adi into degisken_adi;
close cursor_adi;

şeklinde kullanılır.

-cursor ile veri bir defa çekilir ve istenildiği kadar kullanılır.

-fetch loop içinde kullanıldığında birden fazla kayıdı getirmiş oluruz.

exit when cursor_adi%NOTFOUND; // kayıt yoksa döngüden çık demektir.

FUNCTION(FONKSİYON)

-fonksiyonlar sadece tek değer döndürürler.

create [or replace] function fonksiyon_ismi return veritipi is

create or replace function func_no(number,number) return number is

---------------------------FONKSİYON ÖRNEK-1--------------------------

CREATE OR REPLACE FUNCTION deneme RETURN VARCHAR2 IS

BEGIN
RETURN 'deneme fonksiyon';
END deneme;
/

select deneme from dual;

----------------------------FONKSİYON ÖRNEK-2------------------------

CREATE OR REPLACE FUNCTION date_fark (max_date DATE, min_date DATE)
RETURN PLS_INTEGER IS
BEGIN
RETURN max_date - min_date;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END date_fark;
/

SELECT date_fark('30/11/2007 23:59:59','01/11/2007 00:00:00') FROM dual;

----------------------------FONKSİYON ÖRNEK-3-----------------------

CREATE OR REPLACE FUNCTION fonk_klinik_ad (vklinik_id number)
return varchar2 IS
vklinik_ad varchar2(20);

cursor cur_klinik_ad is
select klinik_adi from klinik where klinik_id = vklinik_id;

BEGIN
open cur_klinik_ad;
fetch cur_klinik_ad into vklinik_ad;
close cur_klinik_ad;
return vklinik_ad;
END;
/

select * from klinik where klinik_adi=fonk_klinik_ad(1);

------------------------------FONKSİYON ÖRNEK-4---------------------

CREATE OR REPLACE FUNCTION fonk_klinik_ad2 (vklinik_id number)
return varchar2 IS
vklinik_ad varchar2(20);

begin

select klinik_adi into vklinik_ad from klinik where klinik_id = vklinik_id;

return(vklinik_ad);

exception when others then
return('');
end;
/

select * from klinik where klinik_adi=fonk_klinik_ad2(1);

------------------------------FONKSİYON ÖRNEK-5------------------------

CREATE OR REPLACE FUNCTION fonk_hasta_ad_bul (no in number)
return varchar2 is
hasta_ad varchar2(20);
begin

if no is not null then
BEGIN
SELECT hasta_adi
INTO hasta_ad
FROM hastalar h
WHERE h.hasta_no =no;
return hasta_ad;
EXCEPTION
WHEN OTHERS THEN return null;
END;
else
return null;
end if;
end;
/
select * from hastalar;
insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(75,'','orhan',1);
select * from hastalar;
select * from hastalar where hasta_adi=fonk_hasta_ad_bul('100');
select * from hastalar where hasta_adi=fonk_hasta_ad_bul('');

-----------------------------FONKSİYON ÖRNEK-6-------------------------

CREATE OR REPLACE FUNCTION eski_lira (tutar number)
RETURN number IS

sonuc number(25,5);
bolum number;
carpan number;
tamsayi integer;
carpim number;

BEGIN

if nvl(tutar,0) = 0 then return(0);
else
carpan := 1000000;
carpim := tutar *carpan;
sonuc := carpim;
return(sonuc);

end if;
END;
/

not: NVL(KOLON,DEGER) : Kolonun aldığı değer null ise değer alanında belirtilen değeri verir.

select hasta_adi,eski_lira(maas) as lira from hastalar; //maaş alanı yoksa ekle.

---------------------------FONKSİYON ÖRNEK-7---------------------

CREATE OR REPLACE FUNCTION yas_hesapla (dtar date)
return number is
begin

RETURN(TRUNC((sysdate - dtar)/365.25));

end yas_hesapla;
/

select yas_hesapla('20/02/1982') from dual;

-------------------------FONKSİYON ÖRNEK-8-------------------

CREATE OR REPLACE FUNCTION turkce_ingilizce
(kelime varchar2) Return varchar2 IS
uzun number;
deger varchar2(32760);
yeni varchar2(32760);

begin

if kelime is null then
return(null);
end if;

uzun:= length(kelime);
deger := kelime;

for k in 1..uzun
loop

if substr(deger,k,1) ='Ç' then
yeni := yeni || 'C';
elsif substr(deger,k,1) ='Ğ' then
yeni := yeni || 'G';
elsif substr(deger,k,1) ='İ' then
yeni := yeni || 'I';
elsif substr(deger,k,1) ='Ö' then
yeni := yeni || 'O';
elsif substr(deger,k,1) ='Ü' then
yeni := yeni || 'U';
elsif substr(deger,k,1) ='Ş' then
yeni := yeni || 'S';
elsif substr(deger,k,1) ='ç' then
yeni := yeni || 'c';
elsif substr(deger,k,1) ='ı' then
yeni := yeni || 'i';
elsif substr(deger,k,1) ='ö' then
yeni := yeni || 'o';
elsif substr(deger,k,1) ='ü' then
yeni := yeni || 'u';
elsif substr(deger,k,1) ='ğ' then
yeni := yeni || 'g';
elsif substr(deger,k,1) ='ş' then
yeni := yeni || 's';
else
yeni := yeni || substr(deger,k,1);
end if;
end loop;

return(yeni);
end;
/

select turkce_ingilizce('çağatay') from dual;
select turkce_ingilizce(hasta_adi) from hastalar;

----------------------FONKSİYON ÖRNEK-9------------------
(TÜRKÇE KARAKTERE ÇEVİRME-2)

CREATE OR REPLACE FUNCTION STRING_DUZENLE(str_ VARCHAR2) RETURN VARCHAR2
IS
ret_ VARCHAR2(4000);
BEGIN
ret_ := replace(replace(str_,'ç','c'),'Ç','C');
ret_ := replace(replace(ret_,'ğ','g'),'Ğ','G');
ret_ := replace(replace(ret_,'ı','i'),'İ','I');
ret_ := replace(replace(ret_,'ö','o'),'Ö','O');
ret_ := replace(replace(ret_,'ş','s'),'Ş','S');
ret_ := replace(replace(ret_,'ü','u'),'Ü','U');
ret_ := replace(replace(replace(replace(replace(replace(ret_,',','.'),'-',' '),':','.'),'/',' '),'\',' '),';','.');
RETURN ret_;
END;
/

select STRING_DUZENLE('ÇAĞATAY') from dual ;

-------------------------FONKSİYON ÖRNEK-10-------------------

CREATE OR REPLACE FUNCTION password_function (
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN AS
BEGIN
IF LENGTH(password) < 8 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END password_function;
/

------------------------ÖRNEK FONKSİYON -11----------------

CREATE OR REPLACE FUNCTION fonk_min_tarih (hastaid NUMBER)
RETURN DATE IS
tarih DATE;
BEGIN
BEGIN
SELECT MIN (son_update)
INTO tarih
FROM hastalar
WHERE hasta_id = hastaid;
EXCEPTION
WHEN OTHERS THEN
tarih := '';
END;

RETURN tarih;
END;
/

select fonk_min_tarih(10) from dual;

IF-FOR-LOOP

--------PL/SQL içerisinde üç tip “IF” yapısı kullanılır-----------

1- IF şart THEN
Komutlar
END IF
-------------------------------
2-IF şart THEN
komutlar
ELSE
Şartın gerçekleşmemesi halindeki komutlar
END IF
-------------------------------
3-IF şart THEN
komutlar
ELSEIF şart
komutlar
END IF

------PL/SQL içerisinde temel LOOP, FOR ve WHILE döngüleri vardır--------

LOOP
Komut1
.....
KomutN
GOTO etiket adı
EXIT [WHEN şart]
END LOOP

---------------------------------------

FOR sayac IN [REVERSE] başlangıç..bitiş
LOOP
Komut1
...
KomutN
EXIT [WHEN şart]
END LOOP

---------------------------------------

WHILE şart LOOP
Komut1
...
KomutN
END LOOP

-----------------------Hata Durumları-----------------------------
EXCEPTION
WHEN <önceden tanımlanmış hata durumu> THEN
Komutlar
-----------------------------------------------------------------------------
Oracle tarafından tanımlı çok kullanılan hata durumları:


DUP_VAL_ON_INDEX Tekil olması gereken bir alana bu durumu ihlal eden bir kayıt eklenmeye çalışıldığında ortaya çıkar.

INVLAID_CURSOR “OPEN” komutu ile açılmamış bir imleç ile işlem yapılmaya çalışıldığında ortaya çıkar.

INVALID_NUMBER Değişkenin tanımından daha büyük bir sayı değişkene atanmak istendiğinde ortaya çıkar.

NO_DATA_FOUND SQL sonucu kayıt dönmediği zaman ortaya çıkar.

ZERO_DIVIDE Sıfıra bölme işleminde ortaya çıkan durumdur.

TOO_MANY_ROWS Bir kayıt dönmesi gereken SQL ‘den birden fazla kayıt döndüğünde ortaya çıkan durumdur.

VALUE_ERROR Numerik veya karakter tipli bir değişkenin diğerinin yerine kullanılmaya çalışıldığı durumdur.

CURSOR_ALREADY_OPEN Açık bir imlecin tekrar açılmaya çalışıldığı durumdur.

LOGIN_DENIED Veritabanına yanlı kullanıcı adı ve şifre ile bağlanılmaya çalışıldığı durumdur.

NOT_LOGGED_ON Veritabanına bağlanmadan SQL cümlesi çalıştırıldığında ortaya çıkan durumdur.

OTHERS EXCEPTION bölümünde yazılan hata durumlarından hiç biri oluşan hatayla eşleşmediğinde bu durum işleme girer.

-----------------------------------------------------------
DECLARE
a NUMBER;
BEGIN
select hasta_no into a from hastalar where hasta_adi='ahmet';

IF a<200 THEN
raise_application_error(-20090,a||' Küçük numaralı hasta!');
END IF;

END;

STORED PROCEDURE

-PL/SQL sorgularımızı database'e saklamak için stored procesur haline getiririz.

------------------PROCEDURE(YORDAM) ----------------

create [or replace] procedure procedure_ismi is
begin
end;

---------procedure çalıştırmak için----------

execute procedure_ismi;

------------------------PROCEDURE ÖRNEK-1--------------

create or replace procedure pro_insert is
begin
insert into klinik values(3,'üroloji');
commit;
end;
/

execute pro_insert;
select * from klinik;

-----------------PROCEDURE ÖRNEK-2-----------------

create or replace procedure pro_update (phasta_id in number,phasta_adi in varchar2) as
begin
update hastalar set hasta_adi=phasta_adi where hasta_id=phasta_id;
commit;
end;
/
select * from hastalar;

begin
pro_update(20,'semih');
end;
/
select * from hastalar;
----------------------PROCEDURE ÖRNEK-3------------------

create or replace procedure pro_update2 (phasta_id in number,p_eski_hasta_adi varchar2, p_yeni_hasta_adi in varchar2) as
begin
update hastalar set hasta_adi=p_yeni_hasta_adi where hasta_id=phasta_id and hasta_adi=p_eski_hasta_adi;

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'Hasta adını değiştiremediniz!.');
END IF;
commit;
end;
/
select * from hastalar;

begin
pro_update2(20,'asli','asiye');
end;
/

select * from hastalar;

---------------------PROCEDURE ÖRNEK-4------------

alter table hastalar add son_update TIMESTAMP DEFAULT SYSTIMESTAMP;
set line 200;
select * from hastalar;

UPDATE hastalar SET son_update = SYSTIMESTAMP;
COMMIT;

--------------------------------
create or replace procedure pro_update3 (phasta_id in number, p_yeni_hasta_adi in varchar2) as
begin
update hastalar set hasta_adi=p_yeni_hasta_adi,son_update=SYSTIMESTAMP
where hasta_id=phasta_id;

IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'Hasta adını değiştiremediniz!.');
END IF;
commit;
end;
/
select * from hastalar;

begin
pro_update3(20,'asli');
end;
/
---------------PROCEDURE ÖRNEK-5--------------------

create or replace procedure pro_insert2(klinik_id number,klinik_adi varchar2) is
begin
insert into klinik values(klinik_id,klinik_adi);
commit;
end;
/

execute pro_insert2 (7,'kadın dogum');

CONSTRAINT ENABLE-DISABLE

----ilişkili tablolarımızı ilişikten çıkarmak için yani anahtarları disable etmek için-----

ALTER TABLE hastalar MODIFY CONSTRAINT const_fk_klinik_id DISABLE;
ALTER TABLE klinik MODIFY CONSTRAINT const_pk_klinik_id DISABLE;

ALTER TABLE hastalar MODIFY CONSTRAINT const_fk_klinik_id ENABLE;
ALTER TABLE klinik MODIFY CONSTRAINT const_pk_klinik_id ENABLE;

PL/SQL

-------------------------PL / SQL ------------------------------

DECLARE
BEGIN
EXCEPTION
END;

DECLARE - değişken tanımlanması burada yapılır.körsırlar(cursors) verilir,hata tanımlanması yapılır.
BEGIN - END - bu iki blok içine dml işlemleri girilir.
EXCEPTION - hata çıktığında ne yapmamız gerektiğini söyler.

DBMS paket- oracle'ın plsql'i için hazır paketleri.DBMS_ ile başlarlar,
mesela DBMS_OUTPUT.PUT_LINE plsql'de begin-end; bloğu içindeki bilgiyi ekrana yazar.

çıktıyı görmek için 'set serveroutput on' kullanmamız gerekir.bir defa yazmak yeterli.
'set serveroutput off' dersek ekrana çıktısını göstermez.

set serveroutput on
begin
dbms_output.put_line('merhaba');
end;
/

-declare bölümünde değişken tanımlarken not null ve constraint'te muhakkak default değer vermek gerekir.

adi varchar2(10):='mehmet';
yada
adi varchar2(10) DEFAULT 'mehmet';

& - kullanıcıya sormak için ampersant kullanılır. =&soru

-- tek satırlık açıklama(comment) yada /* .......... */ uzun satırlık açıklama girebiliriz.

içiçe begin-end; kullanımında aynı değişken isimleri kullanılabilir.

------------------------PL/SQL ÖRNEK-1---------------------------
set serveroutput on
declare
degisken number(4):=10;
begin
dbms_output.put_line(degisken);
end;
/

-------------------------PL/SQL ÖRNEK-2------------------------
declare
degisken number(4):=10;
begin
degisken:=5;
dbms_output.put_line(degisken);
end;
/
-----------------------PL/SQL ÖRNEK-3-----------------------------
declare
a number;
b varchar2(20);
begin
select hasta_no,hasta_adi into a,b from hastalar where hasta_adi='ahmet';
insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(80,700,'kemal',3);
end;
/
select * from hastalar;

-------------------------PL/SQL ÖRNEK-4----------------------------
declare
soru number;
begin
delete from hastalar
where hasta_id=&soru;
if sql%notfound then
dbms_output.put_line('hastalar tablosunda bu id bulunamadı!.');
end if;
end;
/
select * from hastalar;

%isopen
%notfound
%found
%rowcount

EXPORT - IMPORT

exp ->
username: (damp i alacagimiz user) ->
password: (damp i alacagimiz user in sifresi) ->
->
Export file:EXPDAT:DMP> d:/damplarim/hastalar.dmp (damp i kaydedecegimiz yeri belirtiyoruz) ->
<1>E,<2>U, or <3>T: <2>U > 1,2 yada 3 ile seçimi yapariz ->
->
->
Table or Partition to be exported: seçim 3 ise tablo ismini(hasta_tablosu) yazariz->

-----------KULLANICININ DAMPINI ALMA VE SONRA İMPORT ETME
(TÜM NESNELERİYLE ESKİ HALİNE GERİ DÖNDÜRME)------------

exp orhan/o@orcl2 file=c:\user_orhan.dmp

imp orhan/o@orcl2 file=c:\user_orhan.dmp fromuser=orhan touser=orhan


(not: export ve import öncesi orhan user'i olusturulmalı ve aynı rol yada yetkiler verilmelidir)

-------------import log dosyasını da oluşturabiliriz-------------------


IMP ORHAN/O@ORCL FILE=C:\DB.DMP BUFFER=1000000 FROMUSER=ORHAN TOUSER=ORHAN LOG=d:\DAMPLARIM\LOG_DB.TXT
------------------------------------------------------------------

EXP ORLINE/O FILE=D:/DAMPLARIM/USER_ORLINE.DMP (ORLINE KULLANICISININ DAMP INI ALMAK)

------------------------------------------------------------------
exp ->
username:
password:
->
Export file:EXPDAT:DMP> d:/damplarim/database_orcl.dmp ->
<1>E,<2>U, or <3>T: <2>U > 1 ->
->
->
Table or Partition to be exported: orcl (database adini yaziyoruz) ->

----------------------------------------------------------------------


Tam Veritabani Modu örnekleri:
>exp system/manager full=Y file=deneme.dmp
Yukaridaki komut tüm veritabaninin yedegini alir. Eger tüm veritabani içerisinde istenilen belli
özelliklerin yedegi alinmak istenirse etkilesimli metod kullanilir(asagidaki gibi).
>exp system/manager
........
Enter array buffer size:4096>(return) ‘veri tamponunun büyüklügü
Export file:EXPDAT.DMP>deneme.dmp ‘varsayilan yedekleme dosyasi ismi EXPDAT.dmp
E(ntire databese), U(sers), T(ables):U>e ‘tüm veritabani mi? Kullanici ya da Tablo yedegi mi?
Export grants(Y/N):Y>y ‘kullanici haklarinin yedegi alinsin mi?
Export Table Data(Y/N):Y>y ‘ veriler kopyalansin mi?
.......

Kullanici Modu Örnekleri:
>exp scott/tiger file=scott.dmp
Scott kullanicisinin tüm nesneleri ve verileri scott.dmp dosyasina yedeklenir.
Etkilesimli mod için yine “>exp scott/tiger” seklinde komut girilir.
Tablo Modu Örnekleri:
>exp system/manager tables(a,scott.b,ahmet.d) rows=n
System kullanicisi kendisine ait olan a tablosunu, scott kullanicisina ait olan b tablosunu ve ahmet
kullanicisina ait olan d tablosunu yedekliyor. “rows=n” bildirisiyle tablo verilerinin alinmamasini sadece tablonun
yapilarinin kopyalanmasini sagliyor.
Import
Export ile yedeklenen veriler Import yardimci programiyla geri getirilebilir. Yedeklenmis dosyanin
tamami ya da bir kismi yedekleyen kullanici için geri getirilebildigi gibi bir baska kullaniciya da aktarilabilir.
>imp system/manager file=deneme.dmp fromuser=usr_esef tables=(isci,bolum)
usr_esef kullanicisinin dneme.dmp tablosuna yedekledigi isci ve bolum tablolari geri getirlir.
>imp system/manager file=scott.dmp fromuser=scott touser=esef tables=(*)
scott kullanicisinin scott.dmp ismli yedek dosyasindaki tüm tablolar esef kullanicisina aktarilir.
Tüm tablolar için “*” isareti kullanilir.


-------------TEK SATIRDA EXPORT VE IMPORT------------------

-------eğer damp dosyasını başka bir pc üzerinden çekerek import edecek isek;-----------------

imp user/password@sid file=\\pc_name\c$\damp.dmp fromuser=orhan touser=ahmet

imp ahmet/a@orcl file=\\10.1.15.114\c$\damp.dmp fromuser=orhan touser=ahmet

-------istediğimiz tabloları import etmek için------------------

imp ahmet/a@orcl file=c:\damp.dmp fromuser=orhan touser=ahmet tables=(hastalar10,hastalar30)

------tüm tabloları import etmek için-----------------------

imp ahmet/a@orcl fromuser=orhan touser=ahmet

------tüm database'in yedeğini(dampını) almak için---------------

exp system/o@orcl full=y file=c:\database.dmp

----kullanıcının tüm tablo ve nesnelerinin dampını almak için------

exp orhan/o@orcl file=c:\user_orhan.dmp

-------kullanıcının bazı tablolarının dampını almak için-----------

exp orhan/o@orcl tables(hastalar10,hastalar20)

-------eğer hem kendi kullanıcımızın hem de başka kullanıcının tablosunun dampını almak istiyorsak;---

exp orhan/o@orcl tables(hastalar10,hastalar20,ali.klinik,system.hastalar30)

BACKUPLARI BAŞKA BİR DİZİNDE TUTMAK İÇİN RECOVERY_FILE DİZİNİNİ DEĞİŞTİRMEK

sqlplus'tan ;
conn / as sysdba

1-shutdown immediate;
2-startup mount;
3-alter database archivelog;
4-ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='d:/orcl_database_backup' scope=both;

SELECT * FROM V$RECOVERY_FILE_DEST;

rman target/ ' den;

5-backup datafile 5; yada
backup database; yada
backup database plus archivelog;

sqlplus'tan ;
6-alter database open;

FLASHBACK

-flashback moduna geçmek için önce archivelog moduna zorunlu geçmemiz gerekir.

-select name,log_mode from v$database; //ile database'in archivelog modda olup olmadığını görebiliriz.

-----------------flashback modunu açmak için---------------
conn / as sysdba

shutdown immediate;
startup mount;
alter database archivelog;
alter database flashback on;
alter database open;

select name,log_mode from v$database;

--------------flashback modunu kapatmak için----------
shutdown immediate;
startup mount;
alter database flashback off;
alter database open;
--------database'deki değişiklikleri geri almak için------

conn / as sysdba
shutdown immediate;
startup mount exclusive;

flashback database to timestamp sysdate-1; (1 gün öncesine döner)

1 gün =1440 dakika

flashback database to timestamp sysdate-1/24; (1 saat öncesine döner)
// select to_char(sysdate-1/24,'dd-mm-yyyy hh24:mi:ss') "sysdate" from dual; ile 1 saat öncesine
döndüğümüzü sistem saatine bakarak kontrol edebiliriz.

flashback database to timestamp sysdate-15/1440; (15 dakika öncesine döner)
// select to_char(sysdate-15/1440,'dd-mm-yyyy hh24:mi:ss') "sysdate" from dual; ile 15 dakika
öncesine döndüğümüzü sistem saatine bakarak kontrol edebiliriz.

flashback database to timestamp sysdate-interval '15' minute; (15 dakika öncesine döner)

alter database open resetlogs;


-----tablodaki değişiklikleri geri almak için flashback----------------

bunun için user'in 'flashback any table' system privileges' ine sahip olması gerekir:
grant flashback any table to orhan;

-----------------------tabloyu drop etmişsek--------------------------

drop table tablo1;

flashback table tablo1 to before drop;
------------------tablodaki verilerde değişiklik yapmış isek-----------

select * from tablo1;

delete from tablo1;

select * from tablo1;

alter table tablo1 enable row movement;

flashback table tablo1 to timestamp sysdate - interval '1' minute;

------geçmişteki bilgileri (eski halini) sadece görmek için------------

select * from hastalar as of timestamp (sysdate-1);
-----------------------------------------------------------------------------

-----eğer tablo verileri truncate ile silinmiş ise mount modundayken ancak geri getirebiliriz--------

conn orhan

truncate table hastalar;

conn sys / as sysdba

shutdown immediate;
startup mount;

flashback database to timestamp sysdate - interval '20' minute;

conn orhan

select * from hastalar;
----------------------------------------------------------------------------------

2 AYRI DİSKE DATABASE'İN BACKUP'INI ALMAK

(mesela;100gb lık database'in backupını, c: nin 60 gb'ını, d: nin de 40 gb'ını kullanarak almak)

-c: ve d: disklerinde backup dosyalarının tutulacağı yer için 'backups' adında klasör oluşturuyoruz.

sqlplus'tan ;
1-shutdown immediate;
2-startup mount;
3-alter database archivelog;

rman ' den
4-

RUN
{ ALLOCATE CHANNEL c DEVICE TYPE DISK FORMAT 'c:/backups/%U';
ALLOCATE CHANNEL d DEVICE TYPE DISK FORMAT 'd:/backups/%U';
BACKUP DATABASE;}

yada

RUN
{
ALLOCATE CHANNEL c DEVICE TYPE DISK FORMAT 'c:/backups/%U';
ALLOCATE CHANNEL d DEVICE TYPE DISK FORMAT 'd:/backups/%U';
BACKUP AS COPY DATABASE;
}

yada

RUN
{
ALLOCATE CHANNEL c DEVICE TYPE DISK FORMAT 'c:/backups/%U';
ALLOCATE CHANNEL d DEVICE TYPE DISK FORMAT 'd:/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
}

sqlplus'tan ;
5-alter database open;



/ sonra bqckup'ı geri yüklemek için;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

RMAN TARGET/

restore database;
recover database;

SQLPLUS NOLOG /
CONNECT / AS SYSDBA

ALTER DATABASE OPEN;

DATAFILE KURTARMA (RECOVER DATAFILE)

-öncelikle database backup'ımızın olması lazım.

------------eğer database open modda ise yani database açık iken----------

SQL>conn sys / as sysdba
SQL>ALTER DATABASE DATAFILE 8 OFFLINE;

cmd -> rman target /

RMAN>REPORT SCHEMA; // ile datafile numaralarını görebiliriz.

RMAN>RESTORE DATAFILE 8;
RMAN>RECOVER DATAFILE 8;

SQL>ALTER DATABASE DATAFILE 8 ONLINE;

------eğer database kapalı ise yani startup mount modda iken---------

-datafile'lar mount modda iken zaten offline duruma düşerler.

SQL>conn sys / as sysdba
SQL>shutdown immediate;
SQL>startup mount;

cmd -> rman target /

RMAN>RESTORE DATAFILE 8;
RMAN>RECOVER DATAFILE 8;

SQL>alter database open;

DENEME (TEST TABLESPACE VE DATAFİLE'IN OLUŞTURULMASI VE KALDIRILMASI)

CONNECT / AS SYSDBA

create tablespace test datafile 'D:\oracle\product\10.2.0\oradata\orcl\test.dbf' size 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

create user orhan identified by o default tablespace test temporary tablespace temp;
grant dba,select_catalog_role,connect,resource to orhan;
grant create table to orhan;
grant create database link to orhan;
exec dbms_streams_auth.grant_admin_privilege('orhan', true);

conn orhan@orcl

create table klinik(klinik_id number(4),klinik_adi varchar2(20),constraint const_pk_klinik_id primary key(klinik_id));

insert into klinik values(1,'dahiliye');

select * from klinik;

CONNECT / AS SYSDBA

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;


ALTER DATABASE DATAFILE ‘D:\oracle\product\10.2.0\oradata\orcl\test.dbf’ OFFLINE DROP;

ALTER DATABASE OPEN;

DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

DATABASE BACKUP VE KURTARMA

SQLPLUS NOLOG /
CONNECT / AS SYSDBA

create tablespace test datafile 'D:\oracle\product\10.2.0\oradata\orcl\test.dbf' size 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

create user orhan identified by o default tablespace test temporary tablespace temp;

grant dba,select_catalog_role,connect,resource to orhan;
grant create table,flashback any table to orhan;
grant create database link to orhan;
exec dbms_streams_auth.grant_admin_privilege('orhan', true);

conn orhan/o@orcl

create table klinik(klinik_id number(4),klinik_adi varchar2(20),constraint const_pk_klinik_id primary key(klinik_id));

insert into klinik values(1,'dahiliye');

select * from klinik;

commit;
sql>shutdown immediate;
sql>startup mount;

RMAN TARGET/
rman>backup database;
exit;

SQLPLUS NOLOG /
CONNECT / AS SYSDBA

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE DATAFILE ‘D:\oracle\product\10.2.0\oradata\orcl\test.dbf’ OFFLINE DROP;

cmd -> rman target /

RMAN>RESTORE DATAFILE 8;
RMAN>RECOVER DATAFILE 8;

SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE DATAFILE 8 ONLINE;

SQL>SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

TABLESPACE YEDEKLEME(BACKUP TABLESPACE)

SQL>conn sys / as sysdba
SQL>shutdown immediate;
SQL>startup mount;

cmd -> rman target /

RMAN>BACKUP TABLESPACE TEST;

SQL>alter database open;

TABLESPACE KURTARMA (RECOVER TABLESPACE)

-database açık iken;

SQL>conn sys / as sysdba
sql>shutdown immediate;
sql>startup mount;
sql>alter database archivelog;
SQL>ALTER TABLESPACE test1 OFFLINE IMMEDIATE;

cmd -> rman target /

RMAN>RESTORE TABLESPACE test1;
RMAN>RECOVER TABLESPACE test1;

SQL>ALTER TABLESPACE test1 ONLINE;

DATABASE KURTARMA(RECOVER DATABASE)

-database de çıkan herhangi bir problemden kurtulmak için eskiye,geçmişe dönmek için database kurtarılır
yani yedeğinden çeker,geçmiş belirli zamanlara gidilebilir,mesela database'i 5 gün öncesine götür,
5 gün öncesinde olduğu gibi kalsın gibi.

SQL>conn sys / as sysdba
SQL>shutdown immediate;
SQL>startup mount;
cmd -> rman target /
RMAN>RESTORE DATABASE;
RMAN>RECOVER DATABASE; // database'i son alınan yedek haline götürür.
SQL>alter database open;

yada

RMAN>RESTORE DATABASE UNTIL TIME 'SYSDATE-5';
RMAN>RECOVER DATABASE UNTIL TIME 'SYSDATE-5'; //database'i 5 gün öncesine götürür.

yada saati de girerek tam o ana gitmek için;

RMAN>RESTORE DATABASE UNTIL TIME '2007-07-27:15:01:05';
RMAN>RECOVER DATABASE UNTIL TIME '2007-07-27:15:01:05';

yada

SQL>shutdown immediate;
SQL>startup mount;
cmd -> rman target /

RMAN> RUN { SET UNTIL TIME="TO_DATE(01/11/2007 11:57:00','DD/MM/YYYY HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; }


RMAN>REPORT OBSOLETE; //eskimiş,silinebilir backupları gösterir.
RMAN>DELETE OBSOLETE; // ile bu eski backupları silebiliriz.

RMAN>LIST BACKUP OF DATABASE; // backupset olarak alınmış backupları gösterir.

RMAN>REPORT NEED BACKUP; // ile '#bkps' kolonunda datafile'ların kaç tane backup alındığını gösterir.

RMAN>LIST COPY OF DATABASE ARCHIVELOG FROM TIME='SYSDATE-7';
// 7 gün öncesinin archiveloglarını gösterir.

RMAN>DELETE BACKUPSET 4; // 4 numaralı backup'ı siler.

DATABASE YEDEKLEME(BACKUP DATABASE)

-database archivelog modda iken backup alma.
(tüm arşivleriyle birlikte full backuplamak için)

SQL>conn sys / as sysdba
SQL>shutdown immediate;
SQL>startup mount;
cmd -> rman target / (eğer rman'e bağlanmazsa 'rman TARGET SYS/o@orcl NOCATALOG' )
RMAN>BACKUP DATABASE PLUS ARCHIVELOG ALL;
SQL>alter database open;


-sadece database'in backup'ını almak için;

RMAN>BACKUP DATABASE;

-database'in backup'ını zipleyerek(sıkıştırarak) almak için;

RMAN>BACKUP AS COMPRESSED BACKUP SET DATABASE;