Oracle varchar2 Felder vergleichen

9 12 2011

Vergleicht man im PL/SQL varchar2 Variablen, so sind gleiche Inhalte manchmal doch nicht wirklich gleich. Zur Verdeutlichung soll dieses Beispiel herhalten:

declare
v_var1 varchar(20);
v_var2 varchar(30);
v_var3 varchar(30);

begin
v_var1 := 'Test';
v_var2 := 'Test';
v_var3 := 'Test   ';

if v_var1 = v_var2 then dbms_output.put_line('Eins ist ok'); end if;
if v_var1 = v_var3 then dbms_output.put_line('Zwei ist ok'); end if;
if v_var2 = v_var3 then dbms_output.put_line('Drei ist ok'); end if;
end;
/

Was würde man nun vermuten, kommt als Ausgabe heraus? Im Grunde sind die Texte identisch. Allerdings erhalten wir nur die erste Zeile zurück:
Eins ist ok
Was sagt uns das? Ob die Variable mit einer Länge von 20 oder 30 Zeichen definiert ist, ist egal. So weit so gut, nichts anderes hätten wir erwartet. Warum aber ist die Variable 3 nicht identisch zu den anderen Beiden? Dazu beachte man die Leerzeichen am Ende des Strings. Diese werden im Vergleich also mit berücksichtigt.

Leuchtet ein? Vielleicht, doch nutzt man Character-Felder, sieht die Sache gleich wieder anders aus. Ändern wir also die Variablendefinition zu char, erhalten wir ein anderes Ergebnis:

v_var1 char(20);
v_var2 char(30);
v_var3 char(30);

Ergibt als Ausgabe:
Eins ist ok
Zwei ist ok
Drei ist ok

Warum ist das so? Dazu muss man wissen, dass laut Definition des SQL Standards zwei zu vergleichende Strings immer gleich lang sein müssen. Ist ein String kürzer, wird dieser zur Länge des zu vergleichenden Strings mit Blanks aufgefüllt.

v_var2 := 'Test '; -- Wird mit Blanks aufgefüllt
v_var3 := 'Test ';

Somit erkennen wir, die beiden Variablen sind tatsächlich identisch. Das funktioniert natürlich nur mit Blanks am Ende.

Da der varchar2 Typ aber genau dem Zweck dient, immer nur die exakte Länge eines Strings zu speichern, wird hier nicht aufgefüllt, sondern beide Strings werden genau mit der Länge verglichen, die sie auch tatsächlich besitzen. Will man Strings unabhängig nachgelagerter Leerzeichen vergleichen, sollten die Variablen also als pure Character definiert sein.

Doch was, wenn in dem eigenen Skript varchar2 Felder aus welchem Grund auch immer praktischer sind? Dann müssen wir darauf nicht verzichten. Mittels der trim Funktion können wir beim Vergleich dafür sorgen, dass diese Zeichen entfernt werden. Wir erweitern den Vergleich aus dem ersten Beispiel ein wenig…

if trim(v_var1) = trim(v_var2) then dbms_output.put_line('Eins ist ok'); end if;
if trim(v_var1) = trim(v_var3) then dbms_output.put_line('Zwei ist ok'); end if;
if trim(v_var2) = trim(v_var3) then dbms_output.put_line('Drei ist ok'); end if;

…und schon haben wir auch mit varchar2 Variablen das gewünschte Ergebnis:
Eins ist ok
Zwei ist ok
Drei ist ok

Grundsätzlich hätte hier auch ein rtrim gereicht, doch mit trim gehen wir zudem noch sicher, dass selbst führende Leerzeichen nicht stören.





Oracle: Gleichartige Daten aus zwei Tabellen abfragen

16 10 2009

Die Problemstellung war, gleichartige Daten, die über zwei verschiedene Tabellen verteilt sind in einer Ergebnismenge zu erhalten. Wir legen uns also zwei Test-Tabellen mit ein paar Daten an.

SQL> desc tab1
Name      Null?    Type
--------- -------- ---------------------------
FELD1              VARCHAR2(100 CHAR)
FELD2              VARCHAR2(100 CHAR)
FELD3              VARCHAR2(100 CHAR)

SQL> desc tab2
Name      Null?    Type
--------- -------- ---------------------------
FELD1              VARCHAR2(100 CHAR)
FELD2              VARCHAR2(100 CHAR)
FELD3              VARCHAR2(100 CHAR)
FELD4              VARCHAR2(100 CHAR)
FELD5              VARCHAR2(100 CHAR)

SQL> select * from tab1;

FELD1      FELD2      FELD3
---------- ---------- ----------
aaaaa      bbbbb      201001
ccccc      ddddd      201001
eeeee      fffff      201001
ggggg      hhhhh      201001
iiiii      jjjjj      201002

SQL> select * from tab2;

FELD1      FELD2      FELD3      FELD4      FELD5
---------- ---------- ---------- ---------- ----------
kkkkk      lllll      201001     uuuuu      vvvvv
mmmmm      nnnnn      201001     wwwww      xxxxx
ooooo      ppppp      201001     yyyyy      zzzzz
qqqqq      rrrrr      201002     11111      22222
sssss      ttttt      201002     33333      44444

So lange die abgefragten Spalten in beiden Tabellen identisch sind kein größeres Problem.

SQL> SELECT
feld1,
feld2,
feld3
FROM tab1
UNION
SELECT
feld1,
feld2,
feld3
FROM tab2
ORDER BY feld3 ASC;

FELD1      FELD2      FELD3
---------- ---------- ----------
aaaaa      bbbbb      201001
ccccc      ddddd      201001
eeeee      fffff      201001
ggggg      hhhhh      201001
kkkkk      lllll      201001
mmmmm      nnnnn      201001
ooooo      ppppp      201001
iiiii      jjjjj      201002
qqqqq      rrrrr      201002
sssss      ttttt      201002

10 rows selected.

Benötigen wir allerdings eine unterschiedliche Anzahl an Spalten je Tabelle, funktioniert dieses Vorgehen so nicht mehr:

SQL> SELECT *
FROM tab1
UNION
SELECT *
FROM tab2;

ERROR at line 1:
ORA-01789: query block has incorrect number of result columns

SAS bietet hierfür das OUTER UNION CORR.

SELECT * FROM (
SELECT * FROM tab1
OUTER UNION CORR
SELECT * FROM tab2
)
ORDER BY feld3 ASC;

Etwas vergleichbares gibt es in Oracle nicht. Wir müssen die Anzahl der Spalten in den Tabellen also faken.

SQL> SELECT
  feld1,
  feld2,
  feld3,
  null as feld4,
  null as feld5
FROM tab1
UNION
SELECT
  feld1,
  feld2,
  feld3,
  feld4,
  feld5
FROM tab2
ORDER BY feld3 ASC

FELD1      FELD2      FELD3      FELD4      FELD5
---------- ---------- ---------- ---------- ----------
aaaaa      bbbbb      201001
ccccc      ddddd      201001
eeeee      fffff      201001
ggggg      hhhhh      201001
kkkkk      lllll      201001     uuuuu      vvvvv
mmmmm      nnnnn      201001     wwwww      xxxxx
ooooo      ppppp      201001     yyyyy      zzzzz
iiiii      jjjjj      201002
qqqqq      rrrrr      201002     11111      22222
sssss      ttttt      201002     33333      44444

10 rows selected.




Oracle: Führende Leerzeichen bei to_char

21 11 2007

Es sollte ein numerisches Feld selektiert werden. Die enthaltenen Werte konnten maximal vierstellig sein. So weit so gut, mit to_char nicht wirklich ein Problem. Zur Illustrierung des Sachverhalts lege ich eine Tabelle mit ein paar Testdaten an.

SQL> CREATE TABLE testchar (nr number);
TABLE created.

SQL> INSERT INTO testchar VALUES (1);
1 row created.

SQL> INSERT INTO testchar VALUES (10);
1 row created.

SQL> INSERT INTO testchar VALUES (100);
1 row created.

SQL> INSERT INTO testchar VALUES (1000);
1 row created.

SQL> INSERT INTO testchar VALUES (-1);
1 row created.

SQL> SELECT to_char(nr, '0999') FROM testchar;

TO_CHAR(NR,'0999')
------------–
0001
0010
0100
1000
-0001

Wie in der Abfrage zu sehen ist, werden zu kleine Werte entsprechend mit führenden Nullen ausgegeben. Auch erkennt man allerdings ein Leerzeichen bei den positiven Werten. Dies ist ein Platzhalter für das Minuszeichen (-). Bei Abfragen wie der Folgenden, die zusammenhängend dargestellt werden sollen ist dies natürlich ein Problem.

SQL> SELECT '0000' || to_char(nr, '0999') FROM testchar;

'0000'||TO_CHAR(NR,'0999')
------------------------
0000 0001
0000 0010
0000 0100
0000 1000
0000-0001

Versteckt in den Tiefen der Oracle-Dokumentation findet sich glücklicherweise der Hinweis auf das Format “FM”, mit welchem ebendieses Leerzeichen verschwindet. Das Ergebnis sieht gleich sehr viel ansprechender aus.

SQL> SELECT '0000' || to_char(nr, 'FM0999') FROM testchar;

'0000'||TO_CHAR(NR,'FM0999')
------------------------
00000001
00000010
00000100
00001000
0000-0001




auto_increment mit Oracle

7 11 2007

Im MySQL gibt es das nette Attribut auto_increment, mit dem sich automatisch eindeutige IDs für neu eingefügte Zeilen vergeben lassen. Oracle bietet nichts vergleichbares, mit ein wenig Trickserei kann man sich diese Funktion aber selbst basteln.

Ich lege mir eine Test-Tabelle an mit einem Feld ID, das mit dem automatisch hochgezählten, numerischen Wert gefüllt werden soll. Außerdem noch eine Spalte TEXT, die von Hand gefüllt wird.

SQL> CREATE TABLE table_autoinc(
	id number,
	text varchar2(100)
);

TABLE created.

Jetzt benötige ich eine Sequence, die mit dem Wert 1 beginnend immer einen um 1 erhöhten Wert übergibt.

SQL> CREATE sequence sequence_autoinc
	start WITH 1
	increment BY 1
	nomaxvalue;

Sequence created.

Schließlich wacht ein Trigger über die Tabelle, der vor jedem INSERT angestartet wird und den Wert der Sequenz in das Feld ID einfügt.

SQL> CREATE TRIGGER trigger_autoinc
	before INSERT
	ON table_autoinc
	FOR each row
	begin
		SELECT sequence_autoinc.NEXTVAL INTO :new.id FROM dual;
	end;
/

TRIGGER created.

Nun wird das Feld TEXT mit Werten befüllt. Wie zu sehen ist, bleibt die ID hier unberücksichtigt.

SQL> INSERT INTO table_autoinc(text) VALUES ('text 1');

1 row created.

SQL> INSERT INTO table_autoinc(text) VALUES ('text 2');

1 row created.

SQL> INSERT INTO table_autoinc(text) VALUES ('text 3');

1 row created.

Wie erwartet bzw. erhofft hat der Trigger zugeschlagen und den autoincrement-Wert für die ID selbst vergeben.

SQL> SELECT * FROM table_autoinc;
ID         TEXT
------- ----------
1          text 1
2          text 2
3          text 3

3 rows selected.




Oracle: Varchar2 Felder als Integer ansprechbar

5 10 2007

Was erwartet man normalerweise von einem varchar Feld? Dass es Character-Werte speichert. Das normale Verhalten demnach wie im Beispiel unter DB2. Ich kann zwar Zahlen in ein varchar Feld speichern, diese lassen sich aber nur als Character abfragen.

db2 => CREATE TABLE formate (feld varchar(100))
DB20000I  The SQL command completed successfully.

db2 => INSERT INTO formate VALUES ('1')
DB20000I  The SQL command completed successfully.

db2 => INSERT INTO formate VALUES ('2')
DB20000I  The SQL command completed successfully.

db2 => SELECT * FROM formate
FELD
-------------------------------------------------------------------
1
2

-- Abfrage als Integer resultiert in einem Fehler
db2 => SELECT * FROM formate WHERE feld = 1
SQL0401N  The DATA types of the operands FOR the operation "=" are NOT compatible.  SQLSTATE=42818

-- Abfrage als Character funktioniert
db2 => SELECT * FROM formate WHERE feld = '1'
FELD
-------------------------------------------------------------------
1

Anders aber unter Oracle mit varchar2 Feldern. Diese gelten scheinbar so lange nur Zahlen eingefügt werden tatsächlich als Integer-Felder. Ich kann also Werte aus varchar2 Feldern im Integer Format abfragen. Dies allerdings nur so lange, bis der erste Character eingefügt wird. Wozu dieses Verhalten gut sein mag, ob das wirklich so gewollt ist, eine wirkliche Erklärung konnte ich dafür nicht finden.

SQL> CREATE TABLE formate (feld varchar2(100));
TABLE created.

SQL> INSERT INTO formate VALUES ('1');
1 row created.

SQL> INSERT INTO formate VALUES ('2');
1 row created.

SQL> SELECT * FROM formate;
FELD
----------------------------------------------------–
1
2

-- Abfrage als Integer funktioniert
SQL> SELECT * FROM formate WHERE feld=1;
FELD
----------------------------------------------------–
1

SQL> INSERT INTO formate VALUES ('a');
1 row created.

SQL> SELECT * FROM formate;
FELD
----------------------------------------------------–
1
2
a

-- Abfrage als Integer funktioniert nicht mehr, weil ein Character-Datensatz eingefügt wurde
SQL> SELECT * FROM formate WHERE feld=1;
ERROR:
ORA-01722: invalid number
no rows selected

SQL> DELETE FROM formate WHERE feld='a';
1 row deleted.

-- Abfrage als Integer funktioniert wieder, weil der Character-Datensatz gelöscht wurde
SQL> SELECT * FROM formate WHERE feld=1;
FELD
----------------------------------------------------–
1