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