Category: PL/SQL

PLSQL’de string ifadenin array’a çevrilmesi

PL/SQL’de parametre olarak string, belirtilen ayırac ile bölen fonksiyon. Eğer hiç ayırac kullanılmamışsa parametre olarak geçilen değer döner. Ayıraç birden fazla karakterden oluşabilir.

[sql]
create or replace function str2array(pStr in varchar2, pDelim in varchar2 ) return dbms_utility.uncl_array is
  Result dbms_utility.uncl_array;
  i number;
  j number;
  lastpos number;
  iCount number;
  bFound boolean;
begin
  i:=1;
  lastpos:=1;
  iCount:=1;
  
  while i<=length(pStr) 
  loop          
     bFound:=false;
     
     if  substr(pStr,i,1)=substr(pDelim,1,1) then 
         
         bFound:=true;
         
         for j in 1..length(pDelim)
         loop
            if substr(pStr,i+j,1)<>substr(pDelim,j+1,1)then
                bFound:=false;
            end if;                 
         end loop;
         
         if bFound then           
            Result(iCount):=substr(pStr,lastpos,i-lastpos);
            iCount:=iCount+1;            
            i:=i+length(pDelim);
            lastpos:=i; 
         end if;
         
     end if;
     
     i:=i+1;
  end loop;
  
  if substr(pStr,lastpos,length(pStr)-lastpos+1)<>pDelim then 
    Result(iCount):=substr(pStr,lastpos,length(pStr)-lastpos+1);
  end if;
   
  
  return(Result);
end;
[/sql]

Örnek kullanım :

[sql]
declare 
 arr dbms_utility.uncl_array;
 i number;
begin
  arr:= str2array('abc,d,ef',',');
  for i in 1..arr.count
  loop
     dbms_output.put_line( arr(i) );
  end loop;
end;
[/sql]

Oracle’da rtf’i text’e çevirmek.

Rtf dosyalarınızı text formatına çevirmeniz gerekebilir. Oracle’da bu işlemi oracle text fonksiyonlarını kullanarak yapabilirsiniz.

İlk önce policy oluşturmalısınız.

ctx_ddl.create_policy(policy_name => 'rtf2text_policy', FILTER => 'CTXSYS.AUTO_FILTER');

Çevirim Fonksiyonu

CREATE OR REPLACE FUNCTION "RTF2TEXT"(p_rtf clob) RETURN CLOB IS
c_text clob;
BEGIN
   ctx_doc.policy_filter(policy_name => 'rtf2text_policy',
                         document => p_rtf,
                         restab => c_text,
                         plaintext => TRUE);
   RETURN c_text;
END;

10gR2 Enterprise Edition ile test edilmiştir.

Oracle’da sql ile bakiye hesaplama

Oracle’da analitik fonksiyonları kullanarak bakiye hesaplanabilmektedir. Örneğimizde bakiye test tablosu oluşturup test verilerini giriyoruz. Hesaplama sutunu için SUM grup fonksiyonunu OVER ile kullanarak satır satır çalışmasını sağlıyoruz. ORDER BY ile toplama işlemini hangi sırayla yapılmasını gerektiğini belirtiyoruz.


create table bakiye_test (
  id number primary key,
  tarih date not null,
  giren number not null,
  cikan number not null
);

insert into bakiye_test values (1,'05.04.2010',100,0);
insert into bakiye_test values (2,'07.04.2010',0,200);
insert into bakiye_test values (3,'09.04.2010',300,0);
insert into bakiye_test values (4,'10.04.2010',100,0);
insert into bakiye_test values (5,'12.04.2010',0,50);
insert into bakiye_test values (6,'19.04.2010',50,0);
insert into bakiye_test values (7,'21.04.2010',0,30);
commit;


SQL> SELECT B.*, SUM( GIREN-CIKAN ) OVER (ORDER BY TARIH ASC ) BAKIYE FROM BAKIYE_TEST B;

        ID TARIH            GIREN      CIKAN     BAKIYE
---------- ----------- ---------- ---------- ----------
         1 05.04.2010         100          0        100
         2 07.04.2010           0        200       -100
         3 09.04.2010         300          0        200
         4 10.04.2010         100          0        300
         5 12.04.2010           0         50        250
         6 19.04.2010          50          0        300
         7 21.04.2010           0         30        270

7 rows selected

Foreign Key ile bağlantı kurulmuş kayıtlarda güncelleme yapmak.

Primary Key – Foreign Key ilişkisi kurulmuş olan bir yapıda,  foreign key olan alanların değerini yeni bir değerle nasıl değiştirebiliriz?

Bir örnek ile açıklamaya çalışalım; Müşteri ve ödeme  bilgilerini içeren iki tablomuz ve aralarında Müşteri id üzerinden foreign key bağlantısı olmuş olsun. Müşteri bilgileri tablosunda A firmasının ikinci bir kaydının A* şeklinde açılmış olduğunu ve bu yanlış açılan firma için ödeme bilgisi girildiğini kabul edelim. A* firmasının yanlış açılmış bir kayıt olduğunu, A* firmasına yapılan ödeme bilgilerinin A firması olarak değiştirilmesi gerektiğinde ödeme bilgilerindeki Müşteri id’sinin güncellenmesi gerekecektir.  Tek tablo için bir update yeterli iken 100’den fazla tabloda update yapılması gerektiğinde ne yapılmalıdır? İlgili tabloya foreign key’le bağlı olan tabloları ve ilişkili olan alanları bulup  alandaki değerleri güncellemek en kestirme yol olacaktır.

Şöyle ki:

drop table odeme_test;
drop table musteri_test;

create table musteri_test (
   musteri_id number primary key,
   adi varchar2(20)
);


create table odeme_test (
   musteri_id number references musteri_test(musteri_id) ,
   tutar number
);

insert into musteri_test values (1,'A FIRMASI');
insert into musteri_test values (2,'B FIRMASI');
insert into musteri_test values (3,'A* FIRMASI');

insert into odeme_test values (1,10);
insert into odeme_test values (2,-10);
insert into odeme_test values (3,20);
insert into odeme_test values (1,-20);
insert into odeme_test values (2,30);
insert into odeme_test values (3,-30);
commit;

SQL> select * from musteri_test;

MUSTERI_ID ADI
---------- --------------------
         1 A FIRMASI
         2 B FIRMASI
         3 A* FIRMASI

SQL> select * from odeme_test;

MUSTERI_ID      TUTAR
---------- ----------
         1         10
         2        -10
         3         20
         1        -20
         2         30
         3        -30

6 satırları seçildi.

Güncellemeleri yapacak sql’ler oluşturuluyor…

SQL> select
 'UPDATE '|| a.OWNER ||'.'|| c.TABLE_NAME ||
 ' SET '|| c.COLUMN_NAME ||'=&YENI'||
 ' WHERE '|| c.COLUMN_NAME ||'=&ESKI;' update_sql
 from
 all_constraints a,
 all_cons_columns c
 where
 a.constraint_type = 'R'
 and a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
 and r_constraint_name in
 (
 select constraint_name
 from all_constraints
 where constraint_type in ('P')
 and table_name = 'MUSTERI_TEST'
 );

yeni değerini girin: 1
eski 3: ' SET '|| c.COLUMN_NAME ||' = &YENI'||
yeni 3: ' SET '|| c.COLUMN_NAME ||' = 1'||
eski değerini girin: 3
eski 4: ' WHERE '|| c.COLUMN_NAME ||'= &ESKI;' update_sql
yeni 4: ' WHERE '|| c.COLUMN_NAME ||'= 3;' update_sql
UPDATE_SQL
-------------------------------------------------------
UPDATE ZGUVEN.ODEME_TEST SET MUSTERI_ID=1 WHERE MUSTERI_ID=3;

SQL> UPDATE ZGUVEN.ODEME_TEST SET MUSTERI_ID=1 WHERE MUSTERI_ID=3;

2 satırları güncellendi.

SQL> select * from odeme_test;

MUSTERI_ID      TUTAR
---------- ----------
         1         10
         2        -10
         1         20
         1        -20
         2         30
         1        -30

6 satırları seçildi.

SQL>

Bir tablo üzerinde kayıt varsa güncelleme yoksa ekleme yapmak

Oracle’da merge kelimesini kullanarak tek sql ile kayıt varsa güncelleme yoksa ekleme yaptırabiliriz (Upsert).
Upsert Sql’imiz:

 merge into tablo t
 using dual s on (t.alan1=1)
 when matched then
   update set alan2='UPDATE'
 when not matched then
   insert (alan1,alan2) values (1,'INSERT');

Test ediyoruz :

SQL> create table tablo (alan1 number(10),alan2 varchar2(10));

Table created

SQL> select * from tablo;

      ALAN1 ALAN2
----------- ----------

SQL> merge into tablo t
2 using dual s on (t.alan1=1)
3 when matched then
4 update set alan2='UPDATE'
5 when not matched then
6 insert (alan1,alan2) values (1,'INSERT');

Done

SQL> select * from tablo;

      ALAN1 ALAN2
----------- ----------
          1 INSERT

SQL> merge into tablo t
2 using dual s on (t.alan1=1)
3 when matched then
4 update set alan2='UPDATE'
5 when not matched then
6 insert (alan1,alan2) values (1,'INSERT');

Done

SQL> select * from tablo;

      ALAN1 ALAN2
----------- ----------
          1 UPDATE

SQL> commit;

Commit complete

SQL>