Faydali Script'ler
Bir kelime kelime icinde kac tane "," oldugunu bulmak :
select length(r)-length(replace(r,',','')) from dual
Takvim tablosu yapmak :
Bir x tablosu olusturun. icinde 0..9 a kadar sayilarin oldugu tek sutunlu
10 satirli bir tablo olsun alan adinada sayi diyelim.
select (a.sayi + b.sayi*10 + c.sayi*100 + d.sayi*1000 + e.sayi*10000 )+
to_date('01.01.1900','dd.mm.yyyy') date from x a, x b, x c, x d, x e
kartezyen carpim sonucu 01.01.1900 tarihinden 10^5 gun ilerisine kadar bir takvim olusur.
select nvl(length(translate(trim(kolon_adi),' +-.0123456789',' ')),0) from tablo_adi
where nvl(length(translate(trim(kolon_adi),' +-.0123456789',' ')),0) > 0
select i ile yazdiginiz tablodaki o kolon icindeki numerik olmayan kayitlari bulabilirsiniz.
+ dan once bosluk var
Long olarak tanımlı bir alanı varchar2 ye çevirmek için
DECLARE
long_var LONG;
var_var VARCHAR2(2000);
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
var_var := substr(long_var,1,2000);
INSERT INTO table_b
VALUES (var_var);
END;
Oracle9 ile birlikte gelen Merge komutu ile tek bir select de hem insert hem update yapabilirsiniz.
Böylece daha önce pl/sql kullanilarak yapilan kayıt varsa update et yoksa insert et kontrollerine gerek
kalmıyor.Kullanımı şu şekildedir :
Merge Into Tablo_adi T1
Using (select * from Tablo_Adi where ...) T2
On (T1.Alan1 = T2.Alan1 And ...)
When Matched Then
Update
Set T1.Alan2 = T2.Alan2
When Not Matched Then
Insert (T1.Alan2) Values (T2.ALan2)
Burada kaynak T2 tablosudur ve datanın atılacağı hedef tablo T1 dir. T2 tablosundan gelecek
datadan hangi koşullar sağlanırsa T1 tablosuna update edileceği ON kısmında yazılmaktadır.
(Update cümlesinin where koşulu olarak düşünün). Daha sonra update edilecek veya insert
edilecek alanlar yazılır.
Bir tablo da ki ID1,ID2.. alanlarına göre birden fazla kayıt varsa ve bunlardan biri dışında
diğerlerini silmek istediğiniz zaman şu select cümlesi işinizi görür :
delete from t1 A
where rowid != ( select min(rowid) from t1 B
where B.ID1 = A.ID1
and B.ID2 = A.ID2)
Excel'de şifrelenmiş sheet lerin şifresini kırmaya yarayan makro : Alt + F11 ile Visual Basic Editor e girin ve orada insert module diyin. Sonra aşağıdaki kodu kopyalayıp koyun. Visual Basic Editoru kapatıp daha sonra bu makroyu çalıştırın. Hepsi bu.
Sub PasswordBreaker()
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
ActiveWorkbook.Sheets(1).Select
Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
SQLTuning ipuçları(PDF)
Kümülatif olarak toplam almak :
deptno | ename | sal | CumDeptTot | SalByDept | CumTot | TotSal |
10 | Ahmet | 1300 | 1300 | 3400 | 1300 | 11300 |
10 | Mehmet | 1100 | 2400 | 3400 | 2400 | 11300 |
10 | Ali | 1000 | 3400 | 3400 | 3400 | 11300 |
20 | Veli | 1200 | 1200 | 3550 | 4600 | 11300 |
20 | Hale | 1250 | 2450 | 3550 | 5850 | 11300 |
20 | Jale | 1100 | 3550 | 3550 | 6950 | 11300 |
30 | Deniz | 1400 | 1400 | 4350 | 8350 | 11300 |
30 | Derya | 1400 | 2800 | 4350 | 9750 | 11300 |
30 | Barbaros | 1550 | 4350 | 4350 | 11300 | 11300 |
Diyelim ki pkey sizin müşterinizin numarası ve siz bu müşterileri aramışsınız. Aynı müşteri için iki arama arasındaki süreyi bulmak isterseniz :
Örnek tablo şu şekilde olsun.
pkey | datecreated |
10 | 06/02/2007 10:28:25 |
10 | 06/02/2007 11:28:25 |
10 | 06/02/2007 12:28:25 |
20 | 06/02/2007 10:28:25 |
20 | 06/02/2007 09:28:25 |
20 | 06/02/2007 10:20:25 |
30 | 06/02/2007 10:28:25 |
30 | 07/02/2007 10:28:25 |
30 | 07/02/2007 01:28:25 |
cozum 1 :
SELECT u.pkey,u.sirano,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) sirano FROM tablo e) u,
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) sirano FROM tablo e) g
WHERE u.pkey = g.pkey(+) AND u.sirano = g.sirano(+)+1
cozum 2 :
WITH std AS
(SELECT e.*,row_number() over (PARTITION BY pkey ORDER BY e.datecreated)
sirano FROM tablo e)
(SELECT
u.pkey,u.sirano,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24))
hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60))
minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60))
seconds
FROM std u , std g
WHERE u.pkey = g.pkey(+) AND u.sirano = g.sirano(+)+1
)
cozum 3 :
SELECT pkey, datecreated,
TRUNC(((((86400*(datecreated-lag (datecreated,1) over (ORDER BY
datecreated)))/60)/60)/24)/7) weeks,
TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY
datecreated)))/60)/60)/24) days,
TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY
datecreated)))/60)/60)-24*(TRUNC((((86400*(datecreated-lag (datecreated,1)
over (ORDER BY datecreated)))/60)/60)/24)) hours,
TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY
datecreated)))/60)-60*(TRUNC(((86400*(datecreated-lag (datecreated,1) over
(ORDER BY datecreated)))/60)/60)) minutes,
TRUNC(86400*(datecreated-lag (datecreated,1) over (ORDER BY
datecreated)))-60*(TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER
BY datecreated)))/60)) seconds
FROM tablo