Yıldız Teknik üniversitesinde yapılacak olan kongre bir ilki göstermesi bakımından önemli. Detaylar için adres incelenebilir. Aşağıda kısa bir özet bulunmaktadır:

Amaç ve Kapsam

Konferans yalnızca yazılım mimarisi ve ilgili konuları içermektedir ve Türkiye’de yazılım mühendisliği konusu ile ilgili pratisyenleri  ve akademisyenleri, her yönüyle yazılım mimarisi ile ilgili deneyim, sonuç ve fikir paylaşımı yapmak üzere bir araya getirmeyi amaçlamaktadır.  Yazılım mimarisi tasarımı ile ilgili bu konferansın temel hedefleri aşağıda sıralanmıştır:

• Yazılım mimarisi tasarımı bilincini geliştirmek

Temel hedef, Yazılım Mühendisliği için bir anahtar kavram olan bu alanı geliştirmektir.  Bu hedefi desteklemek üzere konferansın başlangıcında yazılım mimarisi tasarımının değişik konuları ile ilgili seminerler verilerek başlangıç düzeyinde olduğu gibi ileri düzeyde de konuşmalar yapılacaktır.

• Yazılım mimarisi tasarımı konusunda araştırma ve eğitimi özendirmek

Yazılım mimarisi tasarımı araştırma ve eğitimini hareketlendirmeyi ummaktayız.  Araştırmacılar, fikirlerini sunma ve paylaşma ortamı bulacaklardır.  Eğitimciler ise derslere eklenmek üzere yazılım mimarisi tasarımında önemli olan konuları bulacaklardır.

• Yazılım mimarisi tasarımının uygulanmasını özendirmek ve tartışmak

Yazılım mühendisliği ve yazılım geliştirme, Türk toplumunda bir anahtar rol üstlenmek üzeredir.  Geçen on yıl zarfında ciddi uluslararası rekabet kabiliyeti olan bir çok saygın yazılım kuruluşu ortaya çıktı.  Bu şirketlerin genelde uluslararası yazılım mühendisliği camiası ile bir uyum sağladığı gözlemlenmektedir.  Bu konferans ile yazılım mimarisi tasarımı konusunda bir durum değerlendirmenin oluşturulabileceğini umuyoruz. Düzenlenecek olan bu konferans, endüstrideki en son gelişmeleri temsil etmek ve ayrıca belirlenmiş problemler ve çözümlerini de öne çıkarmak için bir fırsat oluşturacaktır.

Sometimes you can need to convert row values to spesific column values. In Oracle, it is possible to achive this goal by analytical functions. ROW_NUMBER analytical function is gets the number of like rownum  pseudocolumn. The difference is ROW_NUMBER gets partition based row numbers whereas rownum not.

I showed how to convert rows to columns in example below. The question is every class has at most 3 teacher. Give teacher names like TEACHER_1, TEACHER_2, TEACHER_3.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> drop table classes;

Table dropped

SQL> create table classes( class_name varchar2(1), teacher_name varchar2(32) );

Table created

SQL> insert into classes values('A','Margaritta Gonson');

1 row inserted

SQL> insert into classes values('A','Eric Billy');

1 row inserted

SQL> insert into classes values('A','Faruq Omar');

1 row inserted

SQL> insert into classes values('B','Antouan Schensez');

1 row inserted

SQL> insert into classes values('B','Michael Gabriella');

1 row inserted

SQL> insert into classes values('C','Haluk Gumuskaya');

1 row inserted

SQL> insert into classes values('D','Gokhan Yavuz');

1 row inserted

SQL> insert into classes values('D','Banu Diri');

1 row inserted

SQL> /

1 row inserted

SQL> SELECT * FROM classes;

CLASS_NAME TEACHER_NAME
---------- --------------------------------
A          Margaritta Gonson
A          Eric Billy
A          Faruq Omar
B          Antouan Schensez
B          Michael Gabriella
C          Haluk Gumuskaya
D          Gokhan Yavuz
D          Banu Diri
D          Banu Diri

9 rows selected

SQL> SELECT cc.class_name, cc.TEACHER_1, cc.TEACHER_2, cc.TEACHER_3
  2    FROM (SELECT MAX(CASE
  3                       WHEN MOD(r, 3) = 1 THEN
  4                        c.teacher_name
  5                       ELSE
  6                        NULL
  7                     END) TEACHER_1,
  8                 MAX(CASE
  9                       WHEN MOD(r, 3) = 2 THEN
 10                        c.teacher_name
 11                       ELSE
 12                        NULL
 13                     END) TEACHER_2,
 14                 MAX(CASE
 15                       WHEN MOD(r, 3) = 0 THEN
 16                        c.teacher_name
 17                       ELSE
 18                        NULL
 19                     END) TEACHER_3,
 20                 c.class_name
 21            FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
 22                         class_name,
 23                         teacher_name
 24                    FROM classes) c
 25           GROUP BY c.class_name
 26           ORDER BY c.class_name) cc;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz

SQL> --
SQL> SELECT c.class_name,
  2         MAX(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
  3         MAX(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
  4         MAX(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   GROUP BY c.class_name
 10   ORDER BY c.class_name;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz

SQL> --
SQL> SELECT c.class_name,
  2         decode(r, 1, c.teacher_name, NULL) TEACHER_1,
  3         decode(r, 2, c.teacher_name, NULL) TEACHER_2,
  4         decode(r, 3, c.teacher_name, NULL) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   ORDER BY c.class_name;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                                                       
A                                           Faruq Omar                      
A                                                                            Margaritta Gonson
B          Antouan Schensez                                                 
B                                           Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                                                        
D                                           Banu Diri                       
D                                                                            Gokhan Yavuz

9 rows selected

SQL> --
SQL> SELECT c.class_name,
  2         MIN(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
  3         MIN(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
  4         MIN(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
  5    FROM (SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,
  6                 class_name,
  7                 teacher_name
  8            FROM classes) c
  9   GROUP BY c.class_name
 10   ORDER BY c.class_name;

CLASS_NAME TEACHER_1                        TEACHER_2                        TEACHER_3
---------- -------------------------------- -------------------------------- --------------------------------
A          Eric Billy                       Faruq Omar                       Margaritta Gonson
B          Antouan Schensez                 Michael Gabriella               
C          Haluk Gumuskaya                                                  
D          Banu Diri                        Banu Diri                        Gokhan Yavuz

SQL>

Oracle has INTERVAL data types to store days or years. Sometimes it helps you to do less work. I demonstrate a simple example to show the usage.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> DECLARE
  2    diff INTERVAL DAY TO SECOND;
  3 
  4    d1 DATE := SYSDATE;
  5    d2 DATE := SYSDATE +
  6               (6 + 1 / 24 * 3 + 1 / 24 / 60 * 7 + 1 / 24 / 60 / 60 * 9);
  7 
  8    t1 TIMESTAMP;
  9    t2 TIMESTAMP;
 10  BEGIN
 11    t1 := to_timestamp(d1);
 12    t2 := to_timestamp(d2);
 13 
 14    diff := t2 - t1;
 15 
 16    dbms_output.put_line('Time 2 is ' || to_char(t2));
 17    dbms_output.put_line('Time 1 is ' || to_char(t1));
 18 
 19    dbms_output.put_line('Total time difference is ' || diff);
 20 
 21    dbms_output.put_line('Details:');
 22    dbms_output.put_line('Day is    ' || extract(DAY    FROM diff));
 23    dbms_output.put_line('Hour  is  ' || extract(HOUR   FROM diff));
 24    dbms_output.put_line('Minute is ' || extract(MINUTE FROM diff));
 25    dbms_output.put_line('Second is ' || extract(SECOND FROM diff));
 26  END;
 27  /

Time 2 is 29/09/2006 23:50:39,000000
Time 1 is 23/09/2006 20:43:30,000000
Total time difference is +06 03:07:09.000000
Details:
Day is    6
Hour  is  3
Minute is 7
Second is 9

PL/SQL procedure successfully completed

SQL>

Oracle has powerful commands to execute SQL commands dynamically. It is possible to use DBMS_SQL package or EXECUTE IMMEDIATE command. I demonstrate a simple example to show how to call a function or procedure in other schema dynamically.

Below there is a function get_hello which defined in SYS schema. I call this function in HR schema by giving name of schema and function name

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE FUNCTION get_hello(lang IN VARCHAR2) RETURN VARCHAR2 IS
  2  BEGIN
  3    IF upper(lang) = 'EN' THEN
  4      RETURN 'Hello';
  5    ELSIF upper(lang) = 'TR' THEN
  6      RETURN 'Merhaba';
  7    ELSE
  8      RETURN 'Hello';
  9    END IF;
 10  END;
 11  /

Function created

SQL>
SQL> DECLARE
  2    lang VARCHAR2(2) := 'Tr';
  3  BEGIN
  4    dbms_output.put_line(get_hello(lang));
  5  END;
  6  /

Merhaba

PL/SQL procedure successfully completed

SQL> grant execute on get_hello to hr;

Grant succeeded

SQL> conn hr/hr
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    lang VARCHAR2(2) := 'Tr';
  3  BEGIN
  4    dbms_output.put_line(sys.get_hello(lang));
  5  END;
  6  /

Merhaba

PL/SQL procedure successfully completed

SQL>
SQL> DECLARE
  2    lang  VARCHAR2(2) := 'Tr';
  3    hello VARCHAR2(16);
  4  BEGIN
  5    EXECUTE IMMEDIATE 'select sys.get_hello(:lang) from dual'
  6      INTO hello
  7      USING lang;
  8    dbms_output.put_line(hello);
  9  END;
 10  /

Merhaba

PL/SQL procedure successfully completed

SQL>
SQL> CREATE OR REPLACE FUNCTION generic_get_hello(lang          IN VARCHAR2,
  2                                               schema_name   IN VARCHAR2,
  3                                               function_name IN VARCHAR2)
  4    RETURN VARCHAR2 IS
  5    res VARCHAR2(16);
  6  BEGIN
  7    EXECUTE IMMEDIATE 'select ' || schema_name || '.' || function_name ||
  8                      '(:lang) from dual'
  9      INTO res
 10      USING lang;
 11    RETURN res;
 12  END;
 13  /

Function created

SQL>
SQL> BEGIN
  2    dbms_output.put_line(generic_get_hello('en','sys','get_hello') );
  3  END;
  4  /

Hello

PL/SQL procedure successfully completed

SQL>

There are some important points when using aggregation functions. Although you have no data, this functions(MAX, MIN etc.) return  row and if you add NO_DATA_FOUND exception, exception block never executes.

Aggregation functions al least return one record when your table is empty except

• when you use HAVING clause
• when you use GROUP BY expression

Otherwise they return one NULL record.


I demonstrate a small example to make clear:

DECLARE
a VARCHAR2(12) := '';
b NUMBER;
BEGIN

SELECT COUNT(a.dummy)
INTO b
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('COUNT(a.dummy) without MAX is ' || b);

SELECT COUNT(*)
INTO b
FROM (SELECT MAX(a.dummy)
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T');
dbms_output.put_line('COUNT(a.dummy) with MAX is ' || b);

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('MAX(a.dummy)[No HAVING, No GROUP BY] passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) [No HAVING, No GROUP BY]...');
END;

---
BEGIN
SELECT MAX(a.dummy)
INTO a
FROM (SELECT * FROM dual) a
HAVING MAX(a.dummy) = 'T';
dbms_output.put_line('MAX(a.dummy) with HAVING passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in MAX(a.dummy) with HAVING...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T'
GROUP BY a.dummy;
dbms_output.put_line('a.dummy with group by passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy with group by...');
END;

---
BEGIN
SELECT a.dummy
INTO a
FROM (SELECT * FROM dual) a
WHERE a.dummy = 'T';
dbms_output.put_line('a.dummy passed..');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found exception in a.dummy...');
END;

END;

The output is

COUNT(a.dummy) without MAX is 0
COUNT(a.dummy) with MAX is 1
MAX(a.dummy)[No HAVING, No GROUP BY] passed..
no_data_found exception in MAX(a.dummy) with HAVING...
no_data_found exception in a.dummy  with group by...
no_data_found exception in a.dummy...

CHAR and VARCHAR types are not the same whereas some thinks they are. When you create a CHAR variable with some size and you do not fill the variable completely, Oracle fills the empty slots with blank characters. So if you want to compare a CHAR string with NULL you can confuse. Follow the ex:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> DECLARE
  2    char_string    CHAR(5) := '';
  3    varchar_string VARCHAR(5) := '';
  4  BEGIN
  5 
  6    IF char_string IS NULL THEN
  7      dbms_output.put_line('char_string is null');
  8    END IF;
  9 
 10    IF varchar_string IS NULL THEN
 11      dbms_output.put_line('varchar_string is null');
 12    END IF;
 13 
 14    IF rtrim(char_string) IS NULL THEN
 15      dbms_output.put_line('rtrim(char_string) is null');
 16    END IF;
 17 
 18  END;
 19  /

varchar_string is null
rtrim(char_string) is null

PL/SQL procedure successfully completed

SQL>

If you want to read more about please follow the link....

Colleciton ları Oracle Veritabanında Saklama