Skip to content

Instantly share code, notes, and snippets.

@captainabap

captainabap/listings.sql

Created June 22, 2020 08:09
Show Gist options
  • Save captainabap/cdd47caeee5de60f245eef177e5f4cfd to your computer and use it in GitHub Desktop.
Save captainabap/cdd47caeee5de60f245eef177e5f4cfd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
listings.sql
SELECT * FROM m_cs_tables
Listing 1.1: Meldung der SQL-Konsole beim Ausführen einer Anweisung
CREATE TABLE namen ( id INT,
last_name VARCHAR(30),
first_name VARCHAR(30) );
INSERT INTO namen VALUES ( 1,'Brandeis','Jörg' );
INSERT INTO namen VALUES ( 2, 'Müller', 'Peter' );
SELECT * FROM namen;
DROP TABLE namen;
Listing 1.2: Beispiel für mehrere Anweisungen in der SQL-Konsole
1 IF lv_counter > 0
2 THEN
3 INSERT INTO farben VALUES ('Violett');
4 END IF;
Listing 2.1: IF-Anweisung enthält eine INSERT-Anweisung
SELECT col1,col2 FROM T1;
SELECT col1,
col2
FROM T1 ;
Listing 2.2: Beispiel für unterschiedliche Formatierung
/* Das ist ein Blockkommentar */
SELECT 'Jörg' AS string,
N'Jörg' AS unicode,
x'fff' AS binary,
-10 AS integer,
- 1.2345 AS decimal,
- 17.126E30 AS float,
0xff AS hex,
'2010-01-01' AS date_as_string,
DATE'2017-11-10' AS date,
'15:42:04.123' AS time_as_string,
TIME'15:42:04.123' AS time,
'2011-12-31 23:59:59' AS timestamp_string,
TIMESTAMP'2011-12-31 23:59:59' AS timestamp
FROM dummy;
Listing 2.3: Beispiele für Literale im Quelltext
SELECT id,
status,
titel
FROM aufgaben;
Listing 2.4: Beispiel für die einfache Notation
CREATE TABLE id_with_space("ID" int, " ID" int , "ID " int);
CREATE PROCEDURE get_name(IN id INT)
AS BEGIN
tmp = SELECT id, name, first_name FROM test;
SELECT * FROM :tmp WHERE id = :id;
END;
CALL get_name(1);
Listing 2.5: Zugriff auf lokale Felder und Parameter
Ergebnis = Zahl1 + Zahl2;
SELECT
"PURCHASEORDERID",
"PURCHASEORDERITEM",
"PRODUCT.PRODUCTID",
"CURRENCY",
case
when netamount <> 0
--- Mit Klammerung:
--- then ((grossamount / netamount) * 100) - 100
--- Ohne Klammerung:
then grossamount / netamount * 100 - 100
else 0
end as tax,
"GROSSAMOUNT",
"NETAMOUNT",
"TAXAMOUNT",
"QUANTITY",
"QUANTITYUNIT",
"DELIVERYDATE"
FROM "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::PO.Item";
Listing 2.6: Beispiel für Klammerung von Operatoren
SELECT
-- Feldname als Ausdruck
id,
-- Verkettungsoperation als Ausdruck
vorname || ' ' || nachname AS name,
-- CASE-Ausdruck ...
CASE
-- ... mit Funktionsaufruf als Ausdruck
WHEN geschlecht = 'F' THEN NCHAR('9792')
WHEN geschlecht = 'M' THEN NCHAR('9794')
ELSE ''
END AS MW,
-- Funktionsaufruf als Ausdruck
COALESCE(team, 0) AS team
FROM benutzer;
Listing 2.7: Ausdrücke in Feldlisten
DECLARE lv_count INTEGER;
lv_count = COALESCE((SELECT max(id) FROM aufgaben ), 0);
DO BEGIN
-- Typ einer DB-Tabelle
DECLARE lt_tab1 aufgaben;
-- Typ eines Tabellentyps
DECLARE lt_tab2 id_text;
-- Im Code mit TABLE definierter Tabellentyp
DECLARE lt_tab3 TABLE( id INT,
col1 NVARCHAR(12) );
lt_tab1 = SELECT * FROM aufgaben;
lt_tab2 = SELECT id, titel AS text FROM :lt_tab1;
lt_tab3 = SELECT id, titel AS col1 FROM :lt_tab1;
SELECT * FROM :lt_tab1;
SELECT * FROM :lt_tab2;
SELECT * FROM :lt_tab3;
END;
Listing 2.8: Unterschiedliche Typisierung von Tabellen
CREATE TABLE test_null(
id INT,
name VARCHAR(10)
);
INSERT INTO test_null VALUES(1, 'Peter');
INSERT INTO test_null VALUES(2, 'Paul');
INSERT INTO test_null VALUES(3, 'Petra');
INSERT INTO test_null VALUES(4, 'Andrea');
INSERT INTO test_null(id) VALUES(5);
SELECT * FROM test_null WHERE name LIKE 'P%';
SELECT * FROM test_null WHERE name NOT LIKE 'P%';
DROP TABLE test_null; --Um die Tabelle wieder zu entfernen
Listing 2.9: Selektion auf eine Spalte mit NULL-Werten
SELECT * FROM test_null WHERE name NOT LIKE 'P%'
OR name IS NULL;
SELECT * FROM test_null WHERE name NOT LIKE 'P%'
OR name = NULL;
5 + NULL = NULL
NULL || 'Zeichenkette' = NULL
SELECT a.id,
COALESCE(b.nachname,
to_varchar(a.bearbeiter),
'' ) AS bearbeiter
FROM aufgaben AS a
LEFT OUTER JOIN benutzer AS b
ON a.bearbeiter = b.ID
Listing 2.10: Beispiel für die Verwendung der SQL-Funktion COALESCE
-- Test mit DUMMY
SELECT TO_DATS('2016-01-01') FROM dummy;
-- Der gleiche Test mit einem anonymen Block
DO (OUT rv_result NVARCHAR(10) =>?)
BEGIN
rv_result = TO_DATS('2016-12-31');
END;
Listing 2.11: Die Tabelle DUMMY zum Testen von Ausdrücken
errorTab = SELECT '' AS ERROR_TEXT,
'' AS SQL__PROCEDURE__SOURCE__RECORD
FROM dummy
WHERE dummy = 'Y';
Listing 2.12: Erzeugen einer leeren Tabelle mit Hilfe von DUMMY
SELECT a.id,
udf_benutzername(a.bearbeiter),
a.titel,
s.status_text
FROM udf_aufgaben_in_status(4) AS a
LEFT OUTER JOIN udf_statustexte('DE') AS s
ON a.STATUS = s.id;
Listing 2.13: SELECT-Abfrage mit UDFs
--Alleinstehender Block
BEGIN
<Block>
END;
--Prozedurdefinition
CREATE PROCEDURE <Prozedurname> <Parameterdefinition>
AS BEGIN
<Block>
END;
--Blöcke in einer IF/ELSE Bedingung
IF <Bedingung>
THEN
<Block1>
ELSE
<Block2>
END IF;
--Block in einer FOR-Schleife
FOR <Laufvariable> IN <Intervall> DO
<Block>
END FOR;
--Block in einer WHILE-Schleife
WHILE <Bedingung> DO
<Block>
END WHILE;
Listing 2.14: Beispiele für unterschiedliche Blöcke
BEGIN
[<Deklarationen>]
[<Ausnahmebehandler>]
[<Anweisungsliste>]
END;
Listing 2.15: Syntax von Blöcken
DO [(<Parameter>)]
BEGIN
[<Deklarationen>]
[<Ausnahmebehandler>]
[<Anweisungsliste>]
END;
Listing 2.16: Syntax von anonymen Blöcken
DO (IN iv_status INT => 1,
OUT ot_aufgaben TABLE ( id INT,
titel NVARCHAR(40) ) => ?)
BEGIN
ot_aufgaben = SELECT id,
titel
FROM aufgaben
WHERE status = :iv_status;
END;
Listing 2.17: Anonymer Block mit Parametern
CREATE [OR REPLACE] PROCEDURE <Prozedurname>
[(<Parameterliste>)]
[LANGUAGE {SQLSCRIPT|RLANG} ]
[SQL SECURITY {DEFINER|INVOKER} ]
[DEFAULT SCHEMA Defaultschema]
[READS SQL DATA ]
[WITH ENCRYPTION]
AS
BEGIN [SEQUENTIAL EXECUTION]
<Quellcode>
END
Listing 2.18: Bestandteile der Anweisung CREATE PROCEDURE
CREATE PROCEDURE <Prozedurname>
AS
BEGIN
<Quellcode>
END;
Listing 2.19: Pflichtteil der Anweisung CREATE PROCEDURE
CREATE PROCEDURE parameter_test (
--Skalarer Datentyp als Eingabeparameter
IN iv_projekt INT,
--Datenbanktabelle als Typ für einen Ausgabeparameter
OUT ot_aufgaben aufgaben,
-- Ein Tabellentyp für einen Ausgabeparameter
OUT ot_status_text id_text,
-- Definition des Tabellentyps im Code:
OUT ot_restaufwand TABLE ( aufgabe INT,
restaufwand INT ) )
AS
BEGIN
ot_aufgaben = SELECT *
FROM aufgaben
WHERE projekt = :iv_projekt;
ot_status_text = SELECT id,
status_text AS TEXT
FROM status_text;
ot_restaufwand = SELECT id AS aufgabe,
plan_aufwand - ist_aufwand
AS restaufwand
FROM :ot_aufgaben;
END;
CALL parameter_test(2, ?, ?, ?);
DROP PROCEDURE parameter_test;
Listing 2.20: Beispiel für das Anlegen, Ausführen und Löschen einer einfachen Prozedur
--Definition einer Prozedur mit Standardwerten
CREATE PROCEDURE standardwerte (
IN iv_max_id INT DEFAULT 10,
it_table aufgaben DEFAULT aufgaben )
AS
BEGIN
SELECT id,
titel
FROM :it_table
WHERE id <= :iv_max_id;
END;
--Aufruf der Prozedur ohne Parameter
CALL standardwerte();
--Aufräumen
DROP PROCEDURE standardwerte;
Listing 2.21: Definition einer Prozedur mit Standardwerten für die Parameter
CREATE PROCEDURE add_column(IN it_any TABLE(...),
OUT ot_any TABLE(...))
AS BEGIN
ot_any = SELECT *,
'New' || id AS new_column
FROM :it_any AS it;
END;
--Aufruf in der Konsole funktioniert:
CALL add_column(benutzer, ?);
--Aufruf in einer anderen Prozedur ist nicht erlaubt
CREATE PROCEDURE test_add_column
AS BEGIN
lt_users = SELECT *
FROM benutzer;
add_column( :lt_users, lt_tmp );
SELECT *
FROM :lt_tmp;
END;
Listing 2.22: Beispiel für eine Prozedur mit dem Datentyp ANY TABLE
SAP DBTech JDBC: [7]: feature not supported: nested call on
procedure "SYSTEM"."ADD_COLUMN" has any table output parameter
CALL <Prozedurname> [(<Parameter>)]
DO BEGIN
CALL jbrandeis.statustexte(iv_sprache=>'DE',
et_result=> lt_statustexte) ;
END;
Listing 2.23: Interner Prozeduraufruf mit Angabe des Schemas
DO BEGIN
statustexte(iv_sprache=>'DE',
et_result=> lt_statustexte) ;
END;
Listing 2.24: Interner Prozeduraufruf ohne CALL
DO BEGIN
statustexte(iv_sprache=>'DE',
et_result=>lt_statustexte) ;
SELECT * FROM :lt_statustexte;
END;
Listing 2.25: Deklaration und Initialisierung einer neuen Tabellenvariable beim Prozeduraufruf
DO BEGIN
statustexte('DE',lt_statustexte) ;
SELECT * FROM :lt_statustexte;
END;
Listing 2.26: Prozeduraufruf über die Position
--Aufruf ohne Parameternamen
CALL parameter_test(2, ?, ?, ?);
--Der gleiche Aufruf mit benannten Parametern
CALL parameter_test(iv_projekt=>2,
ot_aufgaben=>?,
ot_status_text=>?,
ot_restaufwand=>?);
Listing 2.27: Parametrisierung aus der SQL-Konsole
CREATE PROCEDURE fibonacci(IN iv_value INT,
OUT rv_result INT)
AS BEGIN
DECLARE lv_tmp INT;
IF iv_value IN (1, 2)
THEN rv_result = 1;
ELSE
fibonacci( iv_value - 1 , rv_result );
fibonacci( iv_value - 2 , lv_tmp );
rv_result = rv_result + lv_tmp;
END IF;
END;
CALL fibonacci(20, ?);
Listing 2.28: Rekursive Berechnung von Fibonacci-Zahlen
CREATE FUNCTION <Funktionsname>
[(<IN-Parameterliste>)]
RETURNS <OUT-Parameterdefinition>
[LANGUAGE SQLSCRIPT]
[SQL SECURITY {DEFINER|INVOKER} ]
[DEFAULT SCHEMA Defaultschema]
[DETERMINISTIC]
AS
BEGIN
<Quellcode>
END
Listing 2.29: Syntax der CREATE FUNCTION-Anweisung
CREATE FUNCTION udf_name ( iv_vorname NVARCHAR(20),
iv_nachname NVARCHAR(20) )
RETURNS rv_name NVARCHAR(42)
AS BEGIN
rv_name = :iv_nachname || ', ' || :iv_vorname;
END;
Listing 2.30: Beispiel für eine skalare UDF
CREATE FUNCTION udf_statustexte (iv_sprache VARCHAR(2))
RETURNS TABLE ( id INT,
status_text VARCHAR(20) )
AS BEGIN
RETURN SELECT id,
status_text
FROM status_text
WHERE sprache = :iv_sprache;
END;
Listing 2.31: Beispiel für eine Tabellen-UDF
DO BEGIN
DECLARE lv_benutzername NVARCHAR(42);
--Zuweisung einer skalaren UDFs zu skalaren Variablen
lv_benutzername = udf_name( 'Jörg',
'Brandeis' );
SELECT lv_benutzername FROM dummy;
--Zuweisung einer Tabellen-UDF an eine Tabellenvariable
lt_statustexte = SELECT * FROM udf_statustexte('DE');
SELECT * FROM :lt_statustexte;
END;
Listing 2.32: Zuweisung von UDF-Ergebnissen an lokale Variablen
CREATE FUNCTION jetzt
RETURNS uhrzeit TIME,
datum DATE
AS BEGIN
datum = CURRENT_DATE;
uhrzeit = CURRENT_TIME;
END;
SELECT jetzt().datum,
jetzt().uhrzeit
FROM dummy;
Listing 2.33: UDF mit mehreren Rückgabewerten
DO BEGIN
DECLARE lv_uhrzeit TIME;
DECLARE lv_datum DATE;
(lv_uhrzeit, lv_datum) = jetzt();
SELECT lv_datum, lv_uhrzeit FROM dummy;
END;
Listing 2.34: Gleichzeitige Zuweisung aller Rückgabewerte einer UDF
CREATE LIBRARY testlib
AS BEGIN
PUBLIC VARIABLE cv_42 CONSTANT INTEGER DEFAULT 42;
PUBLIC PROCEDURE proc1 (OUT ov_result INTEGER)
AS BEGIN
ov_result = :cv_42;
END;
PUBLIC PROCEDURE call_proc1(OUT ov_result INTEGER)
AS BEGIN
CALL proc1(ov_result=>ov_result);
END;
END;
CALL testlib:call_proc1(?);
SELECT testlib:cv_42 FROM DUMMY;
DO BEGIN
USING testlib AS lib; --Definition des ALIAS
SELECT lib:cv_42 FROM DUMMY;
END;
Listing 2.35: Zugriff auf die Komponenten einer UDL
CREATE LIBRARY const
AS BEGIN
PUBLIC VARIABLE cv_max CONSTANT INTEGER DEFAULT 10;
PUBLIC VARIABLE cv_min CONSTANT INTEGER DEFAULT 3;
END;
Listing 2.36: Definition einer UDL mit Konstanten
SELECT *
FROM aufgaben
WHERE id < const:cv_max
AND id > const:cv_min;
Listing 2.37: Nutzung der Konstanten aus dem Listing 2.36
CREATE FUNCTION udf_versandpreis (
iv_laenge INT,
iv_breite INT,
iv_hoehe INT,
iv_gewicht INT,
iv_als_paeckchen VARCHAR(1),
iv_ist_online VARCHAR(1)
)
RETURNS rv_preis DEC(17,2),
rv_meldung NVARCHAR(30)
AS
BEGIN
--Ermitteln der Größenkategorie
--Ermitteln des Gurtmass
--Ermitteln der Preisklasse
--Ableiten des Preises anhand der Preisklasse
END;
Listing 2.38: Rahmen der Funktion UDF_VERSANDPREIS mit Pseudocode
CREATE FUNCTION udf_versandpreis (
iv_laenge INT,
iv_breite INT,
iv_hoehe INT,
iv_gewicht INT,
iv_als_paeckchen VARCHAR(1),
iv_ist_online VARCHAR(1)
)
RETURNS rv_preis DEC(17,2),
rv_meldung NVARCHAR(30)
AS
BEGIN
DECLARE lv_groessenkategorie INT;
DECLARE lv_preisklasse VARCHAR(3);
DECLARE lv_gurtmass INT;
--Ermitteln der Größenkategorie
lv_groessenkategorie = udf_groessenkategorie(:iv_laenge,
:iv_breite,
:iv_hoehe);
--Ermitteln des Gurtmass
lv_gurtmass = udf_gurtmass(:iv_laenge,
:iv_breite,
:iv_hoehe);
--Ermitteln der Preisklasse
call pr_preisklasse(:lv_groessenkategorie,
:iv_gewicht,
:lv_gurtmass,
:iv_als_paeckchen,
lv_preisklasse,
rv_meldung);
--Ableiten des Preises anhand der Preisklasse
rv_preis = udf_preis(:lv_preisklasse,
:iv_ist_online);
END;
Listing 2.39: Ermittlung des Versandpreis per UDF
CREATE FUNCTION udf_groessenkategorie (
iv_laenge INT,
iv_breite INT,
iv_hoehe INT )
RETURNS rv_result INT
AS
BEGIN
SELECT CASE
WHEN :iv_laenge <= 30
AND :iv_breite <= 30
AND :iv_hoehe <= 15
THEN 1
WHEN :iv_laenge <= 60
AND :iv_breite <= 30
AND :iv_hoehe <= 15
THEN 2
WHEN :iv_laenge <= 120
AND :iv_breite <= 60
AND :iv_hoehe <= 60
THEN 3
ELSE 4
END
INTO rv_result
FROM dummy;
END;
Listing 2.40: Ermittlung der Größenkategorie für die Pakete
SELECT udf_groessenkategorie(30, 30, 10) FROM dummy;
SELECT udf_groessenkategorie(35, 30, 15) FROM dummy;
SELECT udf_groessenkategorie(30, 30, 30) FROM dummy;
SELECT udf_groessenkategorie(121, 30, 30) FROM dummy;
Listing 2.41: Testfälle für die Funktion UDF_GROESSENKATEGORIE
CREATE FUNCTION udf_gurtmass ( iv_laenge INT,
iv_breite INT,
iv_hoehe INT )
RETURNS rv_result INT
AS
BEGIN
rv_result = :iv_laenge
+ (2 * :iv_breite)
+ (2 * :iv_hoehe);
END;
Listing 2.42: Funktion für die Berechnung des Gurtmaßes
CREATE PROCEDURE pr_preisklasse (
iv_groessenkategorie INT,
iv_gewicht INT,
iv_gurtmass INT,
iv_ist_paeckchen VARCHAR(1),
OUT ov_preisklasse VARCHAR(3),
OUT ov_meldung NVARCHAR(30)
) READS SQL DATA
AS
BEGIN
SELECT CASE
WHEN iv_gurtmass > 300
THEN 'PT4'
WHEN iv_groessenkategorie <= 1
AND iv_gewicht <= 1000
AND iv_ist_paeckchen = 'X'
THEN 'PN1'
WHEN iv_groessenkategorie <= 2
AND iv_gewicht <= 2000
AND iv_ist_paeckchen = 'X'
THEN 'PN2'
WHEN iv_groessenkategorie <= 2
AND iv_gewicht <= 2000
THEN 'PT1'
WHEN iv_groessenkategorie <= 3
AND iv_gewicht <= 5000
THEN 'PT2'
WHEN iv_groessenkategorie <= 3
AND iv_gewicht <= 10000
THEN 'PT3'
WHEN iv_groessenkategorie <= 3
AND iv_gewicht <= 31500
THEN 'PT4'
ELSE 'NA'
END
INTO ov_preisklasse
FROM dummy;
SELECT CASE
WHEN iv_groessenkategorie = 4
THEN 'Abmessungen zu groß'
WHEN iv_gewicht > 31500
THEN 'Gewicht zu hoch'
WHEN iv_ist_paeckchen = 'X'
AND :ov_preisklasse IN ('PT1','PT2','PT3','PT4')
THEN 'Nicht als Päckchen möglich!'
ELSE ''
END
INTO ov_meldung
FROM dummy;
IF ov_meldung <> ''
THEN
ov_preisklasse = 'NA';
END IF;
END;
Listing 2.43: Ableitung der Preisklasse und der Meldungen
CREATE FUNCTION udf_preis ( iv_preisklasse VARCHAR(3),
iv_ist_online VARCHAR(1) )
RETURNS rv_preis DEC(17, 2)
AS BEGIN
SELECT CASE :iv_ist_online
WHEN 'X' THEN CASE :iv_preisklasse
WHEN 'PN1' THEN 3.89
WHEN 'PN2' THEN 4.39
WHEN 'PT1' THEN 4.99
WHEN 'PT2' THEN 5.99
WHEN 'PT3' THEN 8.49
WHEN 'PT4' THEN 16.49
ELSE 2
END
ELSE --Filiale
CASE :iv_preisklasse
WHEN 'PN1' THEN 4
WHEN 'PN2' THEN 4.5
WHEN 'PT1' THEN 6.99
WHEN 'PT2' THEN 6.99
WHEN 'PT3' THEN 9.49
WHEN 'PT4' THEN 16.49
ELSE 2
END
END
INTO rv_preis
FROM dummy;
END;
Listing 2.44: Preisermittlung anhand der Preisklasse und dem Online-Flag
SELECT udf_versandpreis(30,30,30,3000,'','X')
FROM dummy;
CREATE TABLE test_versandpreise (
laenge INT,
breite INT,
hoehe INT,
gewicht INT,
als_paeckchen VARCHAR(1),
ist_online VARCHAR(1),
erwarteter_preis DEC(17, 2)
);
Listing 2.45: Tabelle für Testdaten
INSERT INTO test_versandpreise VALUES
(30,30,15,1000,'X','X',3.89);
INSERT INTO test_versandpreise VALUES
(30,30,15,1000,'X','',4);
INSERT INTO test_versandpreise VALUES
(60,30,15,1000,'X','X',4.39);
INSERT INTO test_versandpreise VALUES
(60,30,15,1000,'X','',4.5);
INSERT INTO test_versandpreise VALUES
(60,30,15,2000,'','X',4.99);
INSERT INTO test_versandpreise VALUES
(60,30,15,2000,'','',6.99);
INSERT INTO test_versandpreise VALUES
(61,30,15,2000,'','X',5.99);
INSERT INTO test_versandpreise VALUES
(60,30,15,10001,'','X',16.49);
INSERT INTO test_versandpreise VALUES
(120,60,60,2000,'','X',16.49);
INSERT INTO test_versandpreise VALUES
(60,30,15,2000,'','X',4.99);
INSERT INTO test_versandpreise VALUES
(61,30,15,2000,'','',6.99);
INSERT INTO test_versandpreise VALUES
(60,30,15,10001,'','',16.49);
INSERT INTO test_versandpreise VALUES
(120,60,60,2000,'','',16.49);
INSERT INTO test_versandpreise VALUES
(60,30,15,10000,'','',9.49);
INSERT INTO test_versandpreise VALUES
(60,30,15,40000,'','',0);
INSERT INTO test_versandpreise VALUES
(60,30,15,3000,'X','X',0);
INSERT INTO test_versandpreise VALUES
(121,60,60,2000,'','',0);
Listing 2.46: Testdaten für die Versandpreise
SELECT
erwarteter_Preis,
udf_versandpreis(laenge,
breite,
hoehe,
gewicht,
als_paeckchen,
ist_online).rv_preis
- erwarteter_preis AS abweichung,
udf_versandpreis(laenge,
breite,
hoehe,
gewicht,
als_paeckchen,
ist_online).rv_meldung AS meldung
FROM TEST_VERSANDPREISE
Listing 2.47: Ausführung des Tests
DO BEGIN
--Ableitung des Tabellentyps aus dem Parameter der Prozedur
CALL statustexte(iv_sprache => 'DE',
et_result => lt_statustext);
--Ableitung des Tabellentyps aus der Rückgabestruktur der
--SELECT-Abfrage
lt_status = SELECT * FROM STATUS;
SELECT * FROM :lt_statustext;
SELECT * FROM :lt_status;
END;
Listing 3.1: Implizite Typisierung von Tabellenvariablen
DECLARE <Variablenname>
[CONSTANT]
TABLE( <Spaltenname> <Typ> [,...] )|<Tabellentyp>
[{ DEFAULT | = } <Initialwert> ]
Listing 3.2: Syntax der Deklaration von Tabellenvariablen
lt_var2 = :lt_var1; --Nicht erlaubt
lt_var2 = SELECT * FROM :lt_var1;
SELECT <SELECT-Klausel>
FROM-Klausel
[WHERE <WHERE-Bedingung>]
[GROUP BY <Gruppierung>
[HAVING <HAVING-Bedingung>]]
[ORDER BY <Sortierung>]
[LIMIT <Begrenzung>]
Listing 3.3: Syntax der SELECT-Abfrage
SELECT * FROM procedures LIMIT 5;
SELECT * FROM procedures LIMIT 10 OFFSET 5;
SELECT DISTINCT country, city
FROM "sap.hana.democontent.epm.data::MD.Addresses";
SELECT Feld1, Feld2, Feld3, ...
SELECT '1' AS counter,
"NAME.FIRST" AS vorname,
"NAME.LAST" AS nachname
FROM "sap.hana.democontent.epm.data::MD.Employees";
Listing 3.4: Beispiel für die Verwendung von Aliasnamen für Spalten
SELECT tab.table_name, --Spalte table_name nicht eindeutig!
col.column_name
FROM m_cs_tables AS tab
INNER JOIN m_cs_columns AS col
ON tab.table_name = col.table_name;
Listing 3.5: Beispiel für die Verwendung des Korrelationsnamens in der Feldliste
SELECT t1.* ,
t2.spalte1
FROM tabelle1 AS t1
JOIN tabelle2 AS t2
ON t1.key = t2.key;
Listing 3.6: Beispiel für *-Sternchen in Feldlisten
SELECT id || ' - ' || titel AS Aufgabe,
plan_aufwand,
ist_aufwand,
round(ist_aufwand / plan_aufwand * 100, 0)
|| ' %' AS zeitverbrauch,
fertigstellung || ' %' AS fertigstellung
FROM aufgaben;
Listing 3.7: Operatorausdrücke in der Feldliste der SELECT-Anweisung
CASE <Ausdruck>
WHEN <Ausdruck1> THEN <Ergebnis1>
[WHEN <Ausdruck2> THEN <Ergebnis2>]
(...)
[ELSE <Ergebnis>]
END;
Listing 3.8: Syntax des einfachen CASE-Ausdrucks
CASE abteilung
WHEN 'IT' THEN 'EDV Abteilung'
WHEN 'MA' THEN 'Marketing'
WHEN 'VT' THEN 'Vertrieb'
ELSE abteilung
END AS "Abteilung"
Listing 3.9: Beispiel für einen einfachen CASE-Ausdruck
CASE WHEN <Bedingung1> THEN <Ergebnis1>
[WHEN <Bedingung2> THEN <Ergebnis2>
...]
[ELSE <Ergebnis>]
END
Listing 3.10: Syntax des komplexen CASE-Ausdrucks
lt_intab_with_flags =
SELECT *,
CASE WHEN stocktype IN ( 'F', 'H', 'W' )
THEN 1
ELSE 0
END AS is_transit_stock,
CASE WHEN stocktype IN ( 'D', 'I', 'J' ) AND
stockcat NOT IN ( 'K', 'R' )
THEN 1
ELSE 0
END AS is_blocked_stock,
CASE WHEN stockcat = '' OR
( stockcat IN ( 'E', 'Q' ) AND
indspecstk IN ( 'A', 'M' ) )
THEN 1
ELSE 0
END AS is_valued_stock,
CASE WHEN processkey IN ( '000', '001', '002',
'003', '004', '005',
'006', '007', '010' )
THEN 1
ELSE 0
END AS is_processkey_000_010,
CASE WHEN processkey IN ( '000', '001', '002',
'003', '004', '005',
'006', '007', '010',
'050', '051', '052' )
THEN 1
ELSE 0
END AS is_processkey_000_052,
CASE WHEN processkey IN ( '100', '101', '102',
'103', '104', '105',
'106', '107', '110' )
THEN 1
ELSE 0
END AS is_processkey_100_110,
CASE WHEN processkey IN ( '100', '101', '102',
'103', '104', '105',
'106', '107', '110',
'150', '151', '152' )
THEN 1
ELSE 0
END AS is_processkey_100_152,
CASE WHEN stockrelev = 1
THEN 1
ELSE 0
END AS is_stockrelev
FROM :intab;
Listing 3.11: Komplexe CASE-Ausdrücke zum Prüfen von unterschiedlichen Bedingungen
CASE WHEN wert2<>0 THEN wert1/wert2
ELSE 0
END AS division
Listing 3.12: CASE als Ersatz für eine IF-Bedingung
SELECT get_parcel_price(width, height, depth, weight )
FROM parcels;
Listing 3.13: Aufruf einer skalaren UDF in der Feldliste
DO BEGIN
--Konstante 1 als Spaltenwert für COUNTER
lt_tmp = SELECT 1 AS counter,
plan_aufwand - ist_aufwand AS restaufwand
FROM aufgaben
WHERE status NOT IN (5, 6 );
--Aggregation über die Spalten
SELECT SUM(counter),
SUM(restaufwand)
FROM :lt_tmp;
END;
Listing 3.14: Beispiel für die Verwendung von konstanten Werten und Aggregatfunktionen
SELECT SUM(ist_aufwand)
FROM aufgaben
WHERE projekt = 1;
Listing 3.15: Berechnung des Aufwands für ein Projekt
SELECT projekt,
SUM(ist_aufwand)
FROM aufgaben
GROUP BY projekt;
Listing 3.16: Berechnung des Aufwands für alle Projekte
SELECT projekt,
bearbeiter,
SUM(ist_aufwand)
FROM aufgaben
GROUP BY projekt,
bearbeiter
ORDER BY projekt,
bearbeiter;
Listing 3.17: Aggregation mit Gruppierung über zwei Spalten
SELECT sprache,
STRING_AGG(team_text, ', ')
FROM team_text
GROUP BY sprache;
Listing 3.18: Verkettung der Teamnamen mit der Aggregatfunktion STRING_AGG
SELECT STRING_AGG(team_text.team_text || '('
|| cnt
|| ')', ', ')
FROM (
SELECT team,
COUNT(*) AS cnt
FROM benutzer
GROUP BY team
) AS b
INNER JOIN team_text
ON team_text.id = b.team
WHERE sprache = 'DE'
Listing 3.19: Erweiterung des Beispiels um die Anzahl der Benutzer
SELECT
schema_name,
(SELECT
COUNT(*)
FROM tables
WHERE schema_name=schemas.schema_name)
AS table_count,
(SELECT
COUNT(*)
FROM procedures
WHERE schema_name=schemas.schema_name)
AS procedure_count,
schema_owner
FROM schemas;
Listing 3.20: Skalare Unterabfragen zur Ermittlung eines Feldes
--Aggregatfunktion COUNT
SELECT status,
COUNT(*) AS zaehler
FROM aufgaben
GROUP BY status
ORDER BY status;
--Window Function COUNT
SELECT id,
status,
COUNT(*) OVER (PARTITION BY status) AS zaehler
FROM aufgaben
ORDER BY id;
Listing 3.21: Vergleich zwischen COUNT als Aggregatausdruck und als Window Function
<Window Function> OVER (
[PARTITION <Gruppierung>]
[ORDER BY <Sortierung>]
[ROWS <Ausschnitt>] )
Listing 3.22: Syntax für Window Function
SELECT id,
status,
COUNT(*) OVER (PARTITION BY status ORDER BY id) AS zaehler
FROM aufgaben
ORDER BY status;
Listing 3.23: ORDER BY-Klausel in Window Functions
...ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
SELECT id,
projekt,
LEAD(id) OVER ( PARTITION BY projekt
ORDER BY id ) AS naechste_aufgabe
FROM aufgaben
ORDER BY id;
Listing 3.24: Beispiel mit der Window Function LEAD
SELECT ... FROM <Tabellenausdruck>
SELECT * FROM viewname(wert1, wert2);
SELECT "S1", "in_out"
FROM "system-local.private.jbrandeis/zjb_colview"
(placeholder."$$IP_STATUS$$"=>'a');
01 CREATE PROCEDURE test_anonymous_function
02 (IN iv_max INT,
03 OUT ot_result TABLE(number INTEGER,
04 letter VARCHAR ) )
05 AS BEGIN
06 ot_result =
07 SELECT number,
08 letter
09
10 FROM
11 SQL FUNCTION (IN iv_a INT => :iv_max)
12 RETURNS TABLE (number INT,
13 letter VARCHAR(1))
14 BEGIN
15 DECLARE lv_cnt INT;
16 DECLARE lv_chars VARCHAR(30)
17 DEFAULT 'ABCDEFGHIJKLMNOP';
18
19 lt_result = SELECT 0 AS number,
20 ' ' AS letter
21 FROM dummy
22 WHERE dummy <> 'X';
23
24 FOR lv_cnt IN 1..:iv_a DO
25
26 lt_result = SELECT * FROM :lt_result
27 UNION
28 SELECT lv_cnt AS number,
29 SUBSTRING(:lv_chars,
30 :lv_cnt,
31 1) AS letter
32 FROM dummy;
33
34 END FOR;
35
36 RETURN SELECT * FROM :lt_result;
37 END
38 WHERE MOD(number, 2) = 0 ;
39 END;
40
41 CALL test_anonymous_function(13, ?);
Listing 3.25: Eingebettete Funktion in einer SQL-Abfrage
SELECT ...
FROM <Tabellenausdruck1> CROSS JOIN <Tabellenausdruck2>
SELECT ...
FROM <Tabellenausdruck1> , <Tabellenausdruck2>
Listing 3.26: Syntax des Cross Join
--Kreuzprodukt mit CROSS JOIN
SELECT *
FROM farben
CROSS JOIN groessen;
--Kreuzprodukt nur mit Komma in der FROM-Klausel
SELECT *
FROM farben,
groessen;
Listing 3.27: Cross Join über Farben und Größen
SELECT ...
FROM <Tabellenausdruck1>
[INNER] JOIN <Tabellenausdruck2>
ON <Join-Bedingung>
Listing 3.28: Syntax des INNER JOIN
CREATE TABLE belege ( belegnr INT PRIMARY KEY,
betrag DEC(17,2),
waehrung VARCHAR(3) ) ;
CREATE TABLE waehrungen ( waehrung VARCHAR(3) PRIMARY KEY,
text NVARCHAR(20) ) ;
INSERT INTO belege VALUES(1,34.34,'EUR') ;
INSERT INTO belege VALUES(2,12.53,'EUR') ;
INSERT INTO belege VALUES(3,234.12,'ALL');
INSERT INTO belege VALUES(4,45.12,'USD') ;
INSERT INTO waehrungen VALUES('EUR','Europäischer Euro') ;
INSERT INTO waehrungen VALUES('USD','US-Dollar') ;
INSERT INTO waehrungen VALUES('GBP','Britisches Pfund') ;
SELECT *
FROM belege
INNER JOIN waehrungen
ON belege.waehrung = waehrungen.waehrung;
DROP TABLE belege;
DROP TABLE waehrungen;
Listing 3.29: Beispiel für einen Inner Join
SELECT * FROM belege
LEFT OUTER JOIN waehrungen
ON belege.waehrung = waehrungen.waehrung
where waehrungen.waehrung <> 'EUR';
SELECT * FROM belege
LEFT OUTER JOIN waehrungen
ON belege.waehrung = waehrungen.waehrung
and waehrungen.waehrung <> 'EUR';
Listing 3.30: Beispiel für die Position von Bedingungen
SELECT b.id as bearbeiter,
vorname,
nachname,
cnt
FROM benutzer AS b
CROSS JOIN LATERAL (SELECT COUNT(*) AS cnt,
bearbeiter
FROM aufgaben
WHERE bearbeiter = b.id
GROUP BY bearbeiter )
Listing 3.31: Beispiel für einen Lateral Join
ON 1 = 1;
--1. Alle Aufgaben mit einem Status kleiner/gleich 3
SELECT *
FROM aufgaben
WHERE status <= 3;
--2. Alle Aufgaben, die im Status 1, 2 oder 4 sind
SELECT *
FROM aufgaben
WHERE status = ANY ( 1, 2, 4 );
--3. Alle Aufgaben, die in einem finalen Status sind
SELECT *
FROM aufgaben
WHERE status = ANY ( SELECT id
FROM status
WHERE is_final = true );
--4. Alle Aufgaben, die nicht in einem finalen Status sind
SELECT *
FROM aufgaben
WHERE status <> ALL ( SELECT id
FROM status
WHERE is_final = true );
Listing 3.32: Beispiele für die Verwendung von Vergleichsprädikaten
--1. Negierung des ganzen Prädikats
SELECT count(*)
FROM aufgaben
WHERE NOT ( titel LIKE '%Aliquam%' );
--2. Äquivalente Abfrage mit NOT LIKE
SELECT count(*)
FROM aufgaben
WHERE titel NOT LIKE '%Aliquam%';
Listing 3.33: Verwendung von NOT LIKE
SELECT table_name
FROM tables
WHERE table_name LIKE 'P$_%' ESCAPE '$'
Listing 3.34: Vergleich mit LIKE und ESCAPE
--1. Vergleich mit BETWEEN
SELECT *
FROM benutzer
WHERE id BETWEEN 5 AND 10;
--2. Die selbe Abfrage mit Vergleichsprädikaten
SELECT *
FROM benutzer
WHERE id >= 5
AND id <= 10;
Listing 3.35: Vergleich mit BETWEEN
--1. Abfrage für eine Spalte mit mehreren Werten mit OR
SELECT *
FROM benutzer
WHERE vorname = 'Aldo'
OR vorname = 'Elvin'
OR vorname = 'Sascha';
--2. Analoge Abfrage mit dem IN-Prädikat
SELECT *
FROM benutzer
WHERE vorname IN ( 'Aldo', 'Elvin', 'Sascha' );
--3. Nutzung einer Unterabfrage mit dem IN-Prädikat:
-- Benutzer, denen aktuell keine Aufgabe zugeordnet ist
SELECT *
FROM benutzer
WHERE id NOT IN ( SELECT DISTINCT bearbeiter
FROM aufgaben );
Listing 3.36: Beispiele für die Nutzung des IN-Prädikat
--1. Abfrage mit EXISTS-Prädikat
-- Alle Benutzer mit mindestens einer Aufgabe
SELECT *
FROM benutzer
WHERE EXISTS (
SELECT bearbeiter
FROM aufgaben
WHERE aufgaben.bearbeiter = benutzer.id );
--2. Abfrage mit NOT EXISTS
-- Aufgaben, deren Status nicht in der Statustabelle steht
SELECT *
FROM aufgaben
WHERE NOT EXISTS (
SELECT id
FROM status
WHERE status.id = aufgaben.status );
--3. Analoge Abfrage mit NOT IN
SELECT *
FROM aufgaben
WHERE status NOT IN (
SELECT id
FROM status );
Listing 3.37: Beispiele für das EXISTS-Prädikat
CREATE TABLE t_fiscper (fiscper3 VARCHAR(3));
INSERT INTO t_fiscper VALUES ('000');
INSERT INTO t_fiscper VALUES ('001');
INSERT INTO t_fiscper VALUES ('002');
INSERT INTO t_fiscper VALUES ('003');
CREATE PROCEDURE where_clause (IN iv_fiscper3 VARCHAR(3))
AS
BEGIN
SELECT *
FROM t_fiscper
WHERE fiscper3 LIKE CASE :iv_fiscper3
WHEN ''
THEN '%'
ELSE :iv_fiscper3
END
AND fiscper3 NOT NULL;
END;
CALL where_clause('');
CALL where_clause('000');
CALL where_clause('001');
DROP TABLE t_fiscper;
DROP PROCEDURE where_clause;
Listing 3.38: CASE-Ausdruck in einer WHERE-Klausel
--1. Prüfung mit dem NULL-Prädikat
SELECT *
FROM benutzer
WHERE vorname IS NULL;
--2. Achtung! Diese Abfrage liefert ein anderes Ergebnis!
SELECT *
FROM benutzer
WHERE vorname = NULL; --Falsch! Nicht verwenden!
Listing 3.39: Prüfung auf NULL
--1. Unscharfe Suche nach einer Zeichenkette
SELECT *
FROM benutzer
WHERE CONTAINS ( ( vorname, nachname, email ),
'andy',
FUZZY(0.3) );
--2. Unscharfe Suche nach mehreren Zeichenketten
SELECT *
FROM benutzer
WHERE CONTAINS ( *,
'andy OR anette',
FUZZY(0.5) );
Listing 3.40: Unscharfe Suche mit dem CONTAINS-Prädikat
WITH <Name> [(<Spaltenliste>)] AS (<Unterabfrage>){,...}
SELECT ... ;
--Zunächst wird eine benannte Abfrage erstellt
WITH zu_pruefende_aufgaben AS (
SELECT a.id,
b.vorname || ' ' || b.nachname AS bearbeiter,
a.titel,
a.faelligkeit,
a.status,
a.plan_aufwand,
a.ist_aufwand
FROM aufgaben AS a
INNER JOIN benutzer AS b
ON a.bearbeiter = b.id
WHERE ( a.STATUS NOT IN ( 5, 6 ) ) )
--Diese benannte Abfrage wird dann verwendet
SELECT TO_NVARCHAR('Überfällig') AS grund,
*
FROM zu_pruefende_aufgaben
WHERE faelligkeit < TO_DATE('2017-12-01')
UNION ALL
SELECT TO_NVARCHAR('Aufwandsschätzung prüfen') AS grund,
*
FROM zu_pruefende_aufgaben
WHERE plan_aufwand <= ist_aufwand * 1.1;
Listing 3.41: SELECT-Anweisung mit WITH-Klausel
DO BEGIN
--Definition der Tabellenvariablen über den SELECT
lt_zu_pruefende_aufgaben = SELECT a.id,
b.vorname || ' ' || b.nachname AS bearbeiter,
a.titel,
a.faelligkeit,
a.status,
a.plan_aufwand,
a.ist_aufwand
FROM aufgaben AS a
INNER JOIN benutzer AS b
ON a.bearbeiter = b.id
WHERE (a.status NOT IN (5, 6));
--Verwendung der Tabellenvariablen in den beiden Abfragen
SELECT TO_NVARCHAR('Überfällig') AS grund,
*
FROM :lt_zu_pruefende_aufgaben
WHERE faelligkeit < TO_DATE('2017-12-01')
UNION ALL
SELECT TO_NVARCHAR('Aufwandsschätzung prüfen') AS grund,
*
FROM :lt_zu_pruefende_aufgaben
WHERE plan_aufwand <= ist_aufwand * 1.1;
END;
Listing 3.42: Verwendung von Tabellenvariablen statt der WITH-Klausel
SELECT CASE
WHEN faelligkeit < TO_DATE('2017-12-01')
AND status NOT IN ( 5, 6 )
THEN 'zu pruefen'
ELSE 'OK'
END AS "Prüfen?",
count(*) AS cnt
FROM aufgaben
GROUP BY CASE
WHEN faelligkeit < TO_DATE('2017-12-01')
AND status NOT IN ( 5, 6 )
THEN 'zu pruefen'
ELSE 'OK'
END;
Listing 3.43: Beispiel für einen CASE-Ausdruck in der GROUP BY-Klausel
DO BEGIN
--1. Neue Spalte mit CASE-Ausdruck erzeugen
lt_select = SELECT CASE
WHEN faelligkeit < TO_DATE('2017-12-01')
AND status NOT IN ( 5, 6 )
THEN 'zu pruefen'
ELSE 'OK'
END AS "Prüfen?"
FROM aufgaben;
--2. Gruppierung nach der neuen Spalte
SELECT "Prüfen?",
count(*) AS cnt
FROM :lt_select
GROUP BY "Prüfen?";
END
Listing 3.44: Trennung von CASE und GROUP_BY
DO BEGIN
lt_aggregation = SELECT bearbeiter,
status,
count(*) AS cnt
FROM aufgaben
GROUP BY GROUPING SETS(
( status, bearbeiter ),
( bearbeiter ) )
ORDER BY bearbeiter,
status NULLS LAST;
SELECT b.Vorname || ' ' || b.nachname AS Bearbeiter,
coalesce(s.status_text, ' Summe') AS STATUS,
aggr.cnt
FROM :lt_aggregation AS aggr
LEFT JOIN benutzer AS b --Hinzulesen der Benutzerdaten
ON aggr.bearbeiter = b.id
LEFT JOIN status_text AS s --Hinzulesen der Statustexte
ON aggr.status = s.id
AND s.sprache = 'DE';
END;
Listing 3.45: Verwendung von GROUPING SETS für die Bildung von Summenzeilen
SELECT ... GROUP BY ... HAVING <Bedingung>
SELECT bearbeiter,
AVG(plan_aufwand)
FROM aufgaben
GROUP BY bearbeiter
HAVING count(*) > 5;
Listing 3.46: Aggregation mit den Klauseln GROUP BY und HAVING
--1. Sortierung nach Spaltennamen
SELECT *
FROM aufgaben
ORDER BY plan_aufwand,
ist_aufwand;
--2. Sortierung nach Spaltennummern
SELECT *
FROM aufgaben
ORDER BY 7,
8;
--3. Sortierung nach einem Operatorausdruck
SELECT id,
titel
FROM aufgaben
ORDER BY plan_aufwand - ist_aufwand DESC;
Listing 3.47: Sortierung mit ORDER BY
<Abfrage1> <Mengenoperator> <Abfrage2>
--Abfrage mit UNION: Duplikate werden entfernt
SELECT 'A' AS spalte1,
'B' AS spalte2
FROM dummy
UNION
SELECT 'A' AS spalte1,
'B' AS spalte2
FROM dummy;
--Zweite Abfrage mit UNION ALL ohne Duplikatsentfernung
SELECT 'A' AS spalte1,
'B' AS spalte2
FROM dummy
UNION ALL
SELECT 'A' AS spalte1,
'B' AS spalte2
FROM dummy;
Listing 3.48: Verwendung von UNION und UNION ALL
--Abfrage aller Benutzer, die Projektleiter sind und eine
--Aufgabe zur Bearbeitung zugewiesen haben.
--Zunächst mit INTERSECT:
SELECT bearbeiter
FROM aufgaben
INTERSECT
SELECT projektleiter
FROM projekte;
--Und als INNER JOIN:
SELECT DISTINCT bearbeiter
FROM aufgaben
INNER JOIN projekte
ON bearbeiter = projektleiter;
Listing 3.49: Beispiel für die Verwendung von INTERSECT
--Abfrage aller Bearbeiter von Aufgaben, ohne Projektleiter
--Zunächst mit EXCEPT
SELECT bearbeiter
FROM aufgaben
EXCEPT
SELECT projektleiter
FROM projekte;
--Und die gleiche Abfrage mit NOT EXISTS
SELECT DISTINCT bearbeiter
FROM aufgaben AS a
WHERE NOT EXISTS (
SELECT id
FROM projekte AS p
WHERE p.projektleiter = a.bearbeiter );
Listing 3.50: Subtraktion von Mengen mit EXCEPT und NOT EXISTS
--1. Skalare Unterabfrage
SELECT *
FROM aufgaben
WHERE status = (
SELECT max(id)
FROM status );
--2. Spalten-Unterabfrage
SELECT *
FROM aufgaben
WHERE status IN (
SELECT id
FROM status
WHERE is_final = true );
--3. Tabellen-Unterabfrage
SELECT b.vorname || ' ' || b.Nachname AS PL,
p.titel
FROM (
SELECT *
FROM projekte
WHERE titel LIKE 'F%' ) AS p
INNER JOIN benutzer AS b
ON p.projektleiter = b.id;
Listing 3.51: Beispiele für Unterabfragen
SELECT aufgaben.id AS aufgabe_id,
aufgaben.titel AS aufgabe_titel,
bearbeiter.id AS bearbeiter_id,
bearbeiter.vorname
|| ' '
|| bearbeiter.nachname AS bearbeiter_name,
projektleiter.id AS projektleiter_id,
projektleiter.vorname
|| ' '
|| projektleiter.nachname AS projektleiter_name
FROM aufgaben
INNER JOIN projekte
ON aufgaben.projekt = projekte.id
INNER JOIN benutzer AS projektleiter
ON projekte.projektleiter = projektleiter.id
INNER JOIN benutzer AS bearbeiter
ON aufgaben.bearbeiter = bearbeiter.id;
Listing 3.52: Verwendung von Spalten- und Tabellenalias
"SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Addresses"
--Anlegen der MAP-Funktion
CREATE FUNCTION map_table_row_count(IN iv_tabname nvarchar(30))
RETURNS TABLE (tabname NVARCHAR(30),
rowcount INT )
AS BEGIN
DECLARE lv_sql NVARCHAR(100);
DECLARE lt_result TABLE( tabname NVARCHAR(30),
rowcount INT );
EXEC 'SELECT '''
|| :iv_tabname
|| ''' AS tabname, '
|| 'COUNT(*) as rowcount '
|| 'FROM '
|| :iv_tabname
INTO lt_result;
RETURN SELECT * FROM :lt_result;
END;
--Aufruf des MAP_MERGE Operators in einem Anonymen Block
DO BEGIN
lt_table = SELECT left(table_name,30) AS tabname
FROM m_cs_tables
WHERE schema_name = 'SYSTEM';
lt_result = MAP_MERGE( :lt_table,
map_table_row_count(
:lt_table.tabname) );
SELECT * FROM :lt_result;
END;
Listing 3.53: Verwendung des MAP_MERGE-Operators
MAP_REDUCE( <Eingabetabelle>,
<Map-Tabellenfunktion>,
<Reduce-Funktion oder -Prozedur>)
CREATE TABLE tab1 (
row_nr INT,
col_alphanum ALPHANUM(4),
col_varchar VARCHAR(4),
col_nvarchar NVARCHAR(4)
);
INSERT INTO tab1 VALUES ( 1,' ' , ' ' , ' ' );
INSERT INTO tab1 VALUES ( 2,' ' , ' ', ' ' );
INSERT INTO tab1 VALUES ( 3,'abcd' , 'abcd', 'abcd' );
INSERT INTO tab1 VALUES ( 4,'Jörg' , 'Jörg', 'Jörg' );
INSERT INTO tab1 VALUES ( 5,'öö' , 'öö' , 'öö' );
SELECT row_nr,
col_alphanum,
col_varchar,
col_nvarchar,
TO_NVARCHAR(col_varchar) AS decoded
FROM tab1 ;
DROP TABLE tab1;
Listing 4.1: Beispiel für das Verhalten des Datentyps VARCHAR
CREATE TABLE zeichenketten (
row_nr INT,
col_alphanum ALPHANUM(4) );
INSERT INTO zeichenketten VALUES ( 1,'12');
INSERT INTO zeichenketten VALUES ( 2,'012');
INSERT INTO zeichenketten VALUES ( 3,'0012');
INSERT INTO zeichenketten VALUES ( 4,'2');
INSERT INTO zeichenketten VALUES ( 5,'20');
INSERT INTO zeichenketten VALUES ( 6,'2a');
INSERT INTO zeichenketten VALUES ( 7,'a2');
SELECT *
FROM zeichenketten
ORDER BY col_alphanum;
DROP TABLE zeichenketten;
Listing 4.2: Beispiel für das Verhalten des Datentyps ALPHANUM
SELECT LENGTH('') AS length_space,
LENGTH('Peter') AS length_peter,
LENGTH('Jörg') AS length_joerg,
LENGTH(' ') AS length_china,
LENGTH(TO_VARCHAR(' ')) AS lenght_china_vc
FROM dummy;
Listing 4.3: Ermittlung der Länge von Zeichenketten
CREATE column TABLE tab(col1 NVARCHAR(10) ,
col2 NVARCHAR(10)) ;
INSERT INTO tab VALUES(' ' , 'A' ) ;
INSERT INTO tab VALUES('A ', ' B') ;
INSERT INTO tab VALUES(' A', 'B ') ;
SELECT '"' || col1 || col2 || '"' AS "|| Operator" ,
CONCAT(CONCAT('"' , col1) ,
CONCAT(col2, '"')) AS "CONCAT Function"
FROM tab;
DROP TABLE tab;
Listing 4.4: Test der Verkettung von Zeichenketten
SELECT ABAP_UPPER('Jörg') AS "ABAP_* Function" ,
UPPER('Jörg') AS "UPPER/LOWER Function",
UCASE('Jörg') AS "U/L-CASE Function"
FROM dummy
UNION
SELECT ABAP_LOWER('Jörg') AS "ABAP_* Function" ,
LOWER('Jörg') AS "UPPER/LOWER Function",
LCASE('Jörg') AS "U/L-CASE Function"
FROM dummy;
Listing 4.5: Funktionen für Groß- und Kleinschreibung
SELECT LEFT('ABCDEFGHI',3) AS links, -->'ABC'
SUBSTRING('ABCDEFGHI',4,3) AS mitte, -->'DEF'
RIGHT('ABCDEFGHI',3) AS rechts -->'GHI'
FROM
dummy;
Listing 4.6: Zerlegung von Zeichenketten mit Positionsangaben
SELECT SUBSTR_BEFORE('Raus aus dem Haus','aus') AS links ,
SUBSTR_AFTER('Raus aus dem Haus','aus') AS rechts,
SUBSTR_AFTER('Raus aus dem Haus','') AS alles ,
SUBSTR_AFTER('Raus aus dem Haus','ABC') AS nichts
FROM
dummy;
Listing 4.7: Zeichenkettenzerlegung mit SUBSTR_BEFORE und SUBSTR_AFTER
SUBSTR_REGEXPR(
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[FROM <Start>]
[OCCURENCE <N. auftreten>]
[GROUP <Gruppe>])
Listing 4.8: SQL-Funktion SUBSTR_REGEXPR()
CREATE TABLE gesellschaften( firma NVARCHAR(30) ) ;
INSERT INTO gesellschaften VALUES('BASF SE') ;
INSERT INTO gesellschaften VALUES('SAP SE') ;
INSERT INTO gesellschaften VALUES('Bauer GmbH') ;
INSERT INTO gesellschaften VALUES('Siemens AG') ;
SELECT SUBSTR_REGEXPR('GmbH|AG|SE' IN firma) AS rechtsform,
firma
FROM gesellschaften;
DROP TABLE gesellschaften;
Listing 4.9: Einfache Zeichenkettenzerlegung mit regulären Ausdrücken
CREATE TABLE logtabelle (zeile NVARCHAR(100)) ;
INSERT INTO logtabelle VALUES
('Fehlende Berechtigungen für elbert@brandeis.de') ;
INSERT INTO logtabelle VALUES
('Login fehlgeschlagen: sibrylle@tiergarten-ma.com.') ;
INSERT INTO logtabelle VALUES
('Benutzer ele.fant@tiergarten-ma.de wurde angemeldet.') ;
INSERT INTO logtabelle VALUES
('Server neu gestartet.') ;
SELECT SUBSTR_REGEXPR(
'\b([A-Z0-9._%+-]+)@([A-Z0-9.-]+\.[A-Z]{2,})\b'
FLAG 'i'
IN zeile
GROUP 1 ) AS name,
SUBSTR_REGEXPR(
'\b([A-Z0-9._%+-]+)@([A-Z0-9.-]+\.[A-Z]{2,})\b'
FLAG 'i'
IN zeile
GROUP 2 ) AS host,
SUBSTR_REGEXPR(
'\b([A-Z0-9._%+-]+)@([A-Z0-9.-]+\.[A-Z]{2,})\b'
FLAG 'i'
IN zeile ) AS email,
zeile
FROM logtabelle;
DROP TABLE logtabelle;
Listing 4.10: Beispiel für die Extraktion von E-Mail-Adressen aus einer Log-Tabelle
LOCATE_REGEXPR(
[START|AFTER]
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[FROM <Start>]
[OCCURENCE <N. auftreten>]
[GROUP <Gruppe>] )
Listing 4.11: Syntax der SQL-Funktion LOCATE_REGEXPR()
OCCURRENCES_REGEXPR(
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[FROM <Startposition>] )
Listing 4.12: Syntax der SQL-Funktion OCCURRENCES_REGEXPR()
REPLACE_REGEXPR(
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[WITH <Ersetzung>]
[FROM <Start>]
[OCCURENCE <N. auftreten>] )
Listing 4.13: Syntax der SQL-Funktion REPLACE_REGEXPR()
CREATE TABLE mitarbeiter (namen NVARCHAR(60));
INSERT INTO mitarbeiter VALUES ('Joerg Brandeis');
INSERT INTO mitarbeiter VALUES ('Peter Mueller');
INSERT INTO mitarbeiter VALUES ('Michael Maier');
INSERT INTO mitarbeiter VALUES ('Superman');
SELECT REPLACE_REGEXPR(
'([[:graph:]]+)[[:blank:]]+([[:graph:]]+)' IN namen
WITH 'Nachname: \2 Vorname: \1')
FROM mitarbeiter;
DROP TABLE mitarbeiter;
Listing 4.14: Beispiel für Suchen und Ersetzen mit regulären Ausdrücken
SELECT 'LPAD(''ABC'',8,''anfang'')' AS "Funktion",
LPAD('ABC',8,'anfang') AS "Ergebnis"
FROM dummy
UNION
SELECT 'RPAD(''ABC'',8,''ende'')' AS "Funktion",
RPAD('ABC',8,'ende') AS "Ergebnis"
FROM dummy;
Listing 4.15: Beispiel für die PAD-Funktionen
SELECT
TRIM(both 'HIA' FROM 'ABCDEFGHI') AS trim_both,
TRIM(leading 'HIA' FROM 'ABCDEFGHI') AS trim_leading,
TRIM(trailing 'HIA' FROM 'ABCDEFGHI') AS trim_trailing,
LTRIM('ABCDEFGHI','HIA') AS "LTRIM()",
RTRIM('ABCDEFGHI','HIA') AS "RTRIM()"
FROM dummy;
Listing 4.16: Beispiel für die TRIM-Funktionen
SELECT *
FROM benutzer
WHERE HAMMING_DISTANCE(vorname, 'Mandy') BETWEEN 0 AND 1;
Listing 4.18: Beispiel für die SQL-Funktion HAMMING_DISTANCE()
SELECT *
FROM benutzer
WHERE nachname LIKE_REGEXPR 'M(a|e)(i|y)(a|er)';
Listing 4.19: Suche mit regulären Ausdrücken
SELECT ASCII('A') AS char2ascii,
CHAR(65) AS asci2char,
UNICODE(' ') AS nchar2unicode,
NCHAR('30908') AS unicode2nchar
FROM dummy;
Listing 4.20: Konvertierung von Zeichen in ASCII bzw. Unicode und umgekehrt
<N-Tupel> = SPLIT(<Zeichenkette>, <Trenner>[,<# Trennungen>])
DO BEGIN
USING SQLSCRIPT_STRING AS STRING_LIB;
DECLARE A1 NVARCHAR(100);
DECLARE A2 NVARCHAR(100);
DECLARE A3 NVARCHAR(100);
-- Split funktioniert sauber:
(A1, A2, A3) = STRING_LIB:SPLIT('ABC-DEF-GHI', '-');
-- Zu wenige Abschnitte:
(A1, A2, A3) = STRING_LIB:SPLIT('ABC-DEF', '-');
-- Zu viele Abschnitte:
(A1, A2, A3) = STRING_LIB:SPLIT('ABC-DEF-GHI-JKL', '-');
-- Begrenzt auf 3 Abschnitte funktioniert:
(A1, A2, A3) = STRING_LIB:SPLIT('ABC-DEF-GHI-JKL', '-', 2);
SELECT A1,
A2,
A3 FROM dummy;
END;
Listing 4.21: Beispiele für die SPLIT-Funktion
DO BEGIN
USING SQLSCRIPT_STRING AS STRING_LIB;
DECLARE A1 NVARCHAR(100);
DECLARE A2 NVARCHAR(100);
(A1, A2) = STRING_LIB:SPLIT_REGEXPR('ABC-DEF',
'[A-Z]-[A-Z]');
SELECT A1,
A2 FROM dummy; --('AB', 'EF')
END;
Listing 4.22: Beispiel für die Funktion SPLIT_REGEXPR
DO BEGIN
USING SQLSCRIPT_STRING AS STRING_LIB;
DECLARE lt_split TABLE(RESULT NVARCHAR(5000));
lt_split = STRING_LIB:SPLIT_REGEXPR_TO_TABLE('ABC-DEF',
'[A-Z]-[A-Z]');
SELECT * FROM :lt_split;
END;
Listing 4.23: Beispiel für die Tabellenfunktion SPLIT_REGEXPR_TO_TABLE
DO BEGIN
USING sqlscript_string AS string_lib;
DECLARE lv_string NVARCHAR(100) ;
lv_string = string_lib:format('{1} {0} ist {2:f} Jahre alt',
'Müller',
'Peter',
21.3 );
SELECT :lv_string FROM dummy;
END;
Listing 4.24: Zeichenkettenformatierung mit FORMAT
DO BEGIN
USING sqlscript_string AS string_lib;
SELECT *
FROM string_lib:format_to_table(
'Aufgabe Nr. {id} - {titel:.10}',
aufgaben);
END
Listing 4.25: Formatierung einer Tabelle mit FORMAT_TO_TABLE
DO BEGIN
USING SQLSCRIPT_STRING AS STRING_LIB;
DECLARE lv_string nvarchar(5000);
lt_tasks = SELECT id,
titel
FROM aufgaben;
lv_string = string_lib:table_summary( :lt_tasks , 10);
SELECT :lv_string FROM dummy;
END;
Listing 4.26: Beispiel für die Verwendung der Funktion TABLE_SUMMARY
Listing 4.27: Ergebnis des Aufrufs von Listing 4.26
TABLE_SUMMARY(<Tabellenvariable>[, <Anzahl Zeilen>])
CREATE TABLE datum(line INT,datum DATE) ;
INSERT INTO datum VALUES(1,'1.12.2017') ;
INSERT INTO datum VALUES(2,'2017.12.02') ;
SELECT line,
datum,
TO_VARCHAR(datum) AS as_char
FROM datum;
DROP TABLE datum;
Listing 4.28: Ausgabe eines Datums als Datentyp DATE und VARCHAR
CREATE TABLE datum(line INT,datum DATE) ;
INSERT INTO datum VALUES(1,'0') ;
INSERT INTO datum VALUES(2,'') ;
INSERT INTO datum VALUES(3,'0000-00-00') ;
INSERT INTO datum VALUES(4,'00000000') ;--ABAP
INSERT INTO datum VALUES(5,'0001-01-01') ;
SELECT line,
datum,
TO_VARCHAR(datum) AS as_char,
DAYS_BETWEEN(datum, '00001-01-10') AS Delta
FROM datum;
DROP TABLE datum;
Listing 4.29: Erzeugung und Darstellung des leeren Datums
CREATE TABLE dates ( datum DATE ) ;
INSERT INTO dates VALUES('1.12.2017') ;
INSERT INTO dates VALUES('2017.12.01') ;
INSERT INTO dates VALUES('20171201') ;
INSERT INTO dates VALUES('2017/12/1') ;
SELECT * FROM dates;
DROP TABLE dates;
Listing 4.30: Verschiedene Datumsformate
Could not execute 'INSERT INTO tab_datum VALUES('01=02=2017')'
Error: (dberror) 303 - invalid DATE, TIME or TIMESTAMP value: Error while parsing
01=02=2017 as DATE
INSERT INTO tab_datum VALUES(TO_DATE('01=02=2017',
'DD=MM=YYYY')) ;
CREATE TABLE tab_format ( formatierung VARCHAR(10),
beschreibung NVARCHAR(40) ) ;
INSERT INTO tab_format VALUES('YY',
'Jahr 2-Stellig') ;
INSERT INTO tab_format VALUES('YYYY',
'Jahr 4-Stellig') ;
INSERT INTO tab_format VALUES('MM',
'Monat als zwei Ziffern') ;
INSERT INTO tab_format VALUES('DD',
'Tag im Monat') ;
INSERT INTO tab_format VALUES('Q',
'Quartal') ;
INSERT INTO tab_format VALUES('WW',
'Kalenderwoche') ;
INSERT INTO tab_format VALUES('RM',
'Monat in römischer Schreibweise') ;
INSERT INTO tab_format VALUES('MON',
'3-Stellige Abkürzung des Monats') ;
INSERT INTO tab_format VALUES('MONTH',
'Monat ausgeschrieben, in Englisch') ;
INSERT INTO tab_format VALUES('DDD',
'Tag im Jahr') ;
INSERT INTO tab_format VALUES('YYYY=MM=DD',
'Beispiel für individuelle Muster') ;
SELECT
formatierung,
TO_VARCHAR( TO_DATS( '08.12.2017'),
formatierung ) AS "Beispiel",
beschreibung
FROM tab_format ;
DROP TABLE tab_format ;
Listing 4.31: Beispiel für die Formatierung von Datumsfeldern
CREATE TABLE tab_format ( formatierung VARCHAR(10),
beschreibung NVARCHAR(40) ) ;
INSERT INTO tab_format VALUES('HH24',
'Stunde im 24-Stunden Format') ;
INSERT INTO tab_format VALUES('HH',
'Stunde im 12-Stunden Format') ;
INSERT INTO tab_format VALUES('HH12',
'Stunde im 12-Stunden Format') ;
INSERT INTO tab_format VALUES('AM',
'AM (=Vormittag) oder PM (=Nachmittag)') ;
INSERT INTO tab_format VALUES('PM',
'AM (=Vormittag) oder PM (=Nachmittag)') ;
INSERT INTO tab_format VALUES('MI',
'Minuten') ;
INSERT INTO tab_format VALUES('SS',
'Sekunden') ;
INSERT INTO tab_format VALUES('SSSSS',
'Sekunden seit Mitternacht') ;
SELECT
formatierung,
TO_VARCHAR( TO_TIME( '09:50:13'),
formatierung ) AS "Beispiel 09:50:13",
TO_VARCHAR( TO_TIME( '13:07:38'),
formatierung ) AS "Beispiel 13:07:38",
beschreibung
FROM tab_format ;
DROP TABLE tab_format ;
Listing 4.33: Konvertierung von Zeit nach Zeichenkette
SELECT CURRENT_DATE FROM dummy;
SELECT ADD_MONTHS('2017-01-31',1) FROM dummy; --> 28.02.2017
SELECT ADD_MONTHS_LAST('2017-02-28', 1) FROM dummy;
--> 31.03.2017
SELECT ADD_MONTHS('2017-02-28', 1) FROM dummy;
--> 28.03.2017
Listing 4.34: Unterschiede zwischen den SQL-Funktionen ADD_MONTHS() und ADD_MONTHS_LAST()
SELECT
1 AS row,
YEAR(CURRENT_DATE) AS "Jahr",
MONTH(CURRENT_DATE) AS "Monat",
DAYOFMONTH(CURRENT_DATE) AS "Tag",
HOUR(CURRENT_TIME) AS "Stunde",
MINUTE(CURRENT_TIME) AS "Minute",
SECOND(CURRENT_TIME) AS "Sekunde"
FROM dummy
UNION ALL
SELECT
2 AS row,
EXTRACT(YEAR FROM CURRENT_DATE) AS "Jahr",
EXTRACT(MONTH FROM CURRENT_DATE) AS "Monat",
EXTRACT(DAY FROM CURRENT_DATE) AS "Tag",
EXTRACT(HOUR FROM CURRENT_TIME) AS "Stunde",
EXTRACT(MINUTE FROM CURRENT_TIME) AS "Minute",
EXTRACT(SECOND FROM CURRENT_TIME) AS "Sekunde"
FROM dummy;
Listing 4.35: Zerlegung eines Datums oder einer Zeit in ihre Komponenten
SELECT
WEEK('2017-12-08'), -->50
ISOWEEK('2017-12-08') -->2017-W49
FROM dummy;
Listing 4.36: Bestimmung der Kalenderwoche
SELECT * FROM timezones;
SELECT *
FROM m_host_information
WHERE key LIKE '%timezone%';
Listing 4.37: Ermittlung der Zeitzone eines SAP-HANA-Systems
CREATE TABLE abap_laufzeit(von_datum VARCHAR(8),
von_zeit VARCHAR(6),
bis_datum VARCHAR(8),
bis_zeit VARCHAR(6));
INSERT INTO abap_laufzeit VALUES ( '20180101', '060000',
'20180101', '065900' );
INSERT INTO abap_laufzeit VALUES ( '20180101', '060000',
'20180101', '140000' );
INSERT INTO abap_laufzeit VALUES ( '20180101', '060000',
'20180103', '050000' );
INSERT INTO abap_laufzeit VALUES ( '20180101', '060000',
'20180201', '060000' );
SELECT FLOOR(
SECONDS_BETWEEN(
TO_SECONDDATE(
TO_DATE(von_datum)
||' '||
TO_TIME(von_zeit)),
TO_SECONDDATE(
TO_DATE(bis_datum)
||' '||
TO_TIME(bis_zeit)))
/ 60 /*Sekunden in Minuten*/
/ 60 /*Minuten in Stunden*/)
AS laufzeit_in_stunden
FROM abap_laufzeit;
DROP TABLE abap_laufzeit;
Listing 4.38: Berechnung der Maschinenlaufzeit in Stunden
CREATE TABLE bw_daten(calday VARCHAR(8));
INSERT INTO bw_daten VALUES (TO_DATS('10.06.2018'));
INSERT INTO bw_daten VALUES (TO_DATS('11.06.2018'));
INSERT INTO bw_daten VALUES (TO_DATS('13.05.2018'));
INSERT INTO bw_daten VALUES (TO_DATS('14.05.2018'));
SELECT *
FROM bw_daten
WHERE calday <= TO_DATS(ADD_DAYS(CURRENT_DATE, UMINUS(
WEEKDAY(CURRENT_DATE) + 1)))
AND calday >= TO_DATS(ADD_DAYS(CURRENT_DATE, UMINUS(
WEEKDAY(CURRENT_DATE) + 28)));
DROP TABLE bw_daten;
Listing 4.39: Beispiel für die Selektion der vier letzten abgeschlossenen Wochen
SELECT *
FROM bw_daten
WHERE calday <= TO_DATS(UDF_ADD_DAYS_TO_LAST_SUNDAY(0))
AND calday >= TO_DATS(UDF_ADD_DAYS_TO_LAST_SUNDAY(27));
Listing 4.40: Auslagerung der Datumsberechnung in eine Funktion
SELECT TO_DOUBLE(1.2)-TO_DOUBLE(0.1) FROM dummy;
SELECT TO_SMALLDECIMAL(1.2)-TO_SMALLDECIMAL(0.1) FROM dummy;
Listing 4.41: Vergleich von dezimaler und binärer Gleitkommazahl
CREATE TABLE calc( wert1 INTEGER,
wert2 INTEGER ) ;
INSERT INTO calc VALUES(1,1) ;
INSERT INTO calc VALUES(2,0) ;
INSERT INTO calc VALUES(0,2) ;
INSERT INTO calc VALUES(3,2) ;
SELECT
wert1-wert2 AS differenz,
wert1+wert2 AS summe,
wert1*wert2 AS produkt,
CASE
WHEN wert2<>0
THEN wert1/wert2
ELSE 0
END AS division,
CASE
WHEN wert2<>0
THEN MOD(wert1,wert2)
ELSE 0
END AS modulo
FROM calc;
Listing 4.42: Grundrechenarten in SQL
CREATE TABLE calc( wert double) ;
INSERT INTO calc VALUES(1) ;
INSERT INTO calc VALUES(1.1) ;
INSERT INTO calc VALUES(-1.1) ;
INSERT INTO calc VALUES(1.499999) ;
INSERT INTO calc VALUES(1.500000) ;
INSERT INTO calc VALUES(-1.499999) ;
INSERT INTO calc VALUES(-1.500000) ;
INSERT INTO calc VALUES(1.749999) ;
INSERT INTO calc VALUES(1.750000) ;
INSERT INTO calc VALUES(175) ;
SELECT
wert,
CEIL(wert) AS aufrunden,
FLOOR(wert) AS abrunden,
ROUND(wert, 0, ROUND_HALF_UP) AS rhu_0,
ROUND(wert, 0, ROUND_UP) AS ru_0,
ROUND(wert, 1, ROUND_HALF_UP) AS rhu_1,
ROUND(wert, 1, ROUND_UP) AS ru_1,
ROUND(wert, -1) AS rhu_m1
FROM calc;
DROP TABLE calc;
Listing 4.43: Beispiele für die Rundungsfunktionen
CREATE TABLE mengen (
menge DECIMAL(15,2),
einheit VARCHAR(3) ) ;
INSERT INTO mengen VALUES(1.0,'MG') ;
INSERT INTO mengen VALUES(1.0,'G') ;
INSERT INTO mengen VALUES(1.0,'KG') ;
INSERT INTO mengen VALUES(1.0,'TO') ;
INSERT INTO mengen VALUES(1.0,'XYZ') ;
SELECT
menge AS "Menge",
einheit AS "Einheit",
CONVERT_UNIT(
QUANTITY => menge,
SOURCE_UNIT => einheit,
SCHEMA => 'JBRANDEIS',
TARGET_UNIT => 'KG',
ERROR_HANDLING => 'set to null',
CLIENT => '000')
AS "Menge in Kg",
'Kg' AS "Kg"
FROM mengen;
Listing 4.44: Beispiel für die Mengenumrechnung in eine feste Einheit
CREATE TABLE betraege(
betrag DEC(17,2),
waehrung VARCHAR(3) ) ;
INSERT INTO betraege VALUES(89.90,'EUR') ;
INSERT INTO betraege VALUES(17.20,'USD') ;
INSERT INTO betraege VALUES(4.54,'GBP') ;
SELECT
betrag AS "Betrag",
waehrung AS "Währung",
CONVERT_CURRENCY( AMOUNT => betrag,
SOURCE_UNIT => waehrung,
SCHEMA => 'JBRANDEIS',
TARGET_UNIT => 'USD',
REFERENCE_DATE => '2013-09-23',
CLIENT => '000')
AS "Betrag in USD", 'USD'
AS "USD"
FROM betraege;
DROP TABLE betraege;
Listing 4.45: Beispiel für eine einfache Währungsumrechnung
SELECT BINTOSTR( X'48616C6C6F2057656C74' ) FROM dummy;
CREATE COLUMN TABLE test_varbin(
ascii_int INT,
ascii_char VARCHAR(1),
ascii_hex VARBINARY(1)) ;
DO BEGIN
DECLARE i INT;
FOR i IN 33..127 DO
INSERT INTO test_varbin VALUES(:i,
CHAR(:i),
BINTONHEX(CHAR(:i))) ;
END FOR;
END;
SELECT ascii_int AS "Int",
ascii_char AS "Char",
ascii_hex AS "Hex" FROM test_varbin;
DROP TABLE test_varbin;
Listing 4.46: Konvertierung von ASCII-Dezimal nach VARBINARY
CREATE FUNCTION udf_int_as_bit_string(iv_value INT)
RETURNS rv_value VARCHAR(8)
AS BEGIN
DECLARE lv_value INT default :iv_value;
DECLARE lv_mod INT;
rv_value = '';
WHILE lv_value > 0 DO
lv_mod = MOD(:lv_value, 2);
IF lv_mod = 1 THEN
rv_value = '1' || :rv_value;
ELSE
rv_value = '0' || :rv_value;
END IF;
lv_value = lv_value / 2;
END WHILE;
rv_value = LPAD(rv_value, 8, '0');
END;
Listing 4.47: Funktion zur Darstellung von Ganzzahlen als Bits
CREATE TABLE binaer(text NVARCHAR(50),
wert INT);
INSERT INTO binaer VALUES ('Zahl 248', 248 );
INSERT INTO binaer VALUES ('Zahl 31', 31 );
INSERT INTO binaer VALUES ('XOR(248, 31)', BITXOR(248, 31));
INSERT INTO binaer VALUES ('AND(248, 31)', BITAND(248, 31));
INSERT INTO binaer VALUES ('OR(248, 31)' , BITOR(248, 31));
SELECT text,
udf_int_as_bit_string(wert)
FROM binaer;
DROP TABLE binaer;
Listing 4.48: Beispiel für die bitweise Verarbeitung von Binärdaten
Could not execute 'select to_tinyint( 1000) from dummy'
[314]: numeric overflow: 1000 at function to_tinyint()
SELECT * FROM SERIES_GENERATE_INTEGER( 2, 3, 20);
SELECT * FROM SERIES_GENERATE_DATE( 'INTERVAL 1 MONTH',
date'2019-01-01',
date'2020-01-01');
Listing 4.49: Zeitreihe mit den Monaten des Jahres 2017
CREATE TYPE tt_word AS TABLE( word nvarchar(30) );
CREATE FUNCTION word2char( it_word tt_word )
RETURNS TABLE (val nvarchar(1))
AS BEGIN
lt_tmp = SELECT words.word,
pos.element_number AS pos
FROM :it_word AS words
CROSS JOIN SERIES_GENERATE_INTEGER(1, 0, 30) AS pos;
lt_tmp2 = SELECT SUBSTR(word, pos, 1) AS val
FROM :lt_tmp;
RETURN SELECT * FROM :lt_tmp2
WHERE val <> '';
END;
--Aufruf der Funktion
DO BEGIN
lt_vornamen = SELECT vorname AS word
FROM benutzer;
SELECT count(*),
val
FROM word2char( it_word => :lt_vornamen )
GROUP BY val
ORDER BY COUNT(*) DESC;
END;
Listing 4.50: Zerlegung von Wörtern in Buchstaben mit Hilfe der SERIES-Funktionen
CREATE TABLE tabelle_1(
key1 INT DEFAULT 5,
key2 INT,
wert1 NVARCHAR(200),
PRIMARY KEY (
key1,
key2 )
);
CREATE TABLE tabelle_2(
key1 INT PRIMARY KEY,
wert1 NVARCHAR(200),
wert2 NVARCHAR(200)
);
Listing 5.1: Tabellen für die Beispiele dieses Kapitels
INSERT INTO <Tabellenname> VALUES (<Spaltenwerte>, ...);
INSERT INTO tabelle_1 VALUES (1, 1, 'Erste Zeile');
INSERT INTO tabelle_1 VALUES (1, 2, 'Datum:'||CURRENT_DATE );
INSERT INTO <Tabellenname> <Spaltenreihenfolge>
VALUES (<Spaltenwerte>)
INSERT INTO tabelle_1 (key2, wert1)
VALUES ( 2, 'Defaultwert');
INSERT INTO tabelle_1 (wert1, key1, key2)
VALUES ('Vertauschte Spalten', 2, 3);
Listing 5.2: Beispiel für das Einfügen mit Spaltenreihenfolge
INSERT INTO <Tabellenname> <Abfrage>;
INSERT INTO tabelle_2
SELECT key1 + key2 * 100,
wert1,
'Einfügen aus einer Abfrage'
FROM tabelle_1;
Listing 5.3: Beispiel für eine INSERT-Anweisung mit mehreren Datensätzen
INSERT INTO <Tabellenname>
<Spaltenreihenfolge>
<Abfrage>;
Listing 5.4: Syntax der INSERT-Anweisung für mehrere Datensätze mit Spaltenreihenfolge
DO BEGIN
lt_tmp = SELECT key1 + 10 AS key1,
key2,
'Kopie: '|| wert1 AS wert1
FROM tabelle_1;
INSERT INTO tabelle_1
(wert1, key1, key2)
SELECT wert1,
key1,
key2
FROM :lt_tmp;
END;
Listing 5.5: INSERT mit Abfrage auf eine lokale Tabellenvariable und mit veränderter Spaltenreihenfolge
UPDATE tabelle_2
SET wert1 = 'Aktualisiert',
wert2 = 'Zeile ' || key1
WHERE key1 < 300;
Listing 5.6: Beispiel für eine einfache UPDATE-Anweisung
UPDATE tabelle_2 AS t2 --Alternativ: UPDATE t2
SET t2.wert1 = t1.wert1
FROM tabelle_2 AS t2,
tabelle_1 AS t1
WHERE t2.key1 = t1.key1 + t1.key2 * 100;
Listing 5.7: Beispiel für eine UPDATE-Anweisung mit Bezug auf eine andere Tabelle
DELETE FROM tabelle_1;
DELETE FROM tabelle_2;
--Einfügen einzelner Sätze
UPSERT tabelle_1 VALUES(1, 1, 'Eingefügt mit UPSERT')
WITH PRIMARY KEY;
UPSERT tabelle_1 VALUES(1, 2, 'Eingefügt mit UPSERT')
WITH PRIMARY KEY;
UPSERT tabelle_1 VALUES(1, 3, 'Eingefügt mit UPSERT')
WITH PRIMARY KEY;
--Aktualisieren eines Datensatzes über den Primärschlüssel
UPSERT tabelle_1 VALUES(1, 2, 'Geändert mit UPSERT')
WITH PRIMARY KEY;
SELECT * FROM tabelle_1;
Listing 5.8: Beispiel für das Einfügen und Aktualisieren über den Primärschlüssel
UPSERT tabelle_1 SELECT key1+10,
key2,
'Eingefügt aus Unterabfrage'
FROM tabelle_1;
UPSERT tabelle_1 (key1, key2, wert1)
SELECT key1+10,
key2,
'Geändert durch Unterabfrage'
FROM tabelle_1
WHERE key2 = 2;
Listing 5.9: Beispiel für UPSERT aus Unterabfrage
MERGE INTO <Zieltabelle> [AS <Zielalias>]
USING <Quelltabelle> [AS <Quellalias>]
ON (<Bedingungen>)
[WHEN MATCHED THEN
UPDATE SET <Spaltenaktualisierungen>]
[WHEN NOT MATCHED THEN
INSERT [<Spaltenliste>] VALUES( <Werte>)];
Listing 5.10: Syntax der Anweisung MERGE INTO
DELETE FROM tabelle_1;
DELETE FROM tabelle_2;
INSERT INTO tabelle_2 VALUES(1, '', 'Erste Zeile');
INSERT INTO tabelle_2 VALUES(2, '', 'Zweite Zeile');
INSERT INTO tabelle_2 VALUES(3, '', 'Dritte Zeile');
INSERT INTO tabelle_2 VALUES(4, '', 'Vierte Zeile');
MERGE INTO tabelle_1
USING tabelle_2
ON (tabelle_1.key1 = tabelle_2.key1)
WHEN MATCHED THEN
UPDATE SET tabelle_1.key1 = tabelle_2.key1,
tabelle_1.key2 = 1,
tabelle_1.wert1 = 'UPDATE: '
|| tabelle_1.wert1
WHEN NOT MATCHED THEN
INSERT VALUES( tabelle_2.key1,
1,
'INSERT: ' || tabelle_2.wert2);
MERGE INTO tabelle_1
USING tabelle_2
ON (tabelle_1.key1 = tabelle_2.key1)
WHEN MATCHED THEN
UPDATE SET tabelle_1.key1 = tabelle_2.key1,
tabelle_1.key2 = 1,
tabelle_1.wert1 = 'UPDATE: '
|| tabelle_1.wert1
WHEN NOT MATCHED THEN
INSERT VALUES( tabelle_2.key1,
1,
'INSERT: ' || tabelle_2.wert2);
SELECT * FROM tabelle_1;
Listing 5.11: Beispiel für die Anweisung MERGE INTO
DO BEGIN
lt_tmp = SELECT * FROM tabelle_2;
MERGE INTO tabelle_1
USING :lt_tmp AS t2
ON (tabelle_1.key1 = t2.key1)
WHEN MATCHED THEN
UPDATE SET tabelle_1.key1 = t2.key1,
tabelle_1.key2 = 1,
tabelle_1.wert1 = 'UPDATE: '
|| tabelle_1.wert1
WHEN NOT MATCHED THEN
INSERT VALUES( t2.key1,
1,
'INSERT: ' || t2.wert2);
END;
Listing 5.12: MERGE INTO mit lokaler Tabellenvariable
DELETE FROM <Tabellenname> [WHERE <Bedingungen>]
DECLARE <Variablenname>
[CONSTANT]
<Datentyp>
[NOT NULL]
[ '=' | DEFAULT) <Initialwert>;
Listing 6.1: Definition einer lokalen skalaren Variable
CREATE FUNCTION udf_statistik
RETURNS rv_projekte INT,
rv_bearbeiter INT AS
BEGIN
--Zuweisung von Variablen durch eine SELECT-Anweisung
SELECT
COUNT( DISTINCT projekt),
COUNT( DISTINCT bearbeiter)
INTO rv_projekte,
rv_bearbeiter
FROM
aufgaben;
END;
DO BEGIN
DECLARE lv_projekte INTEGER;
DECLARE lv_bearbeiter INTEGER;
DECLARE lv_div DEC;
--Zuweisung mehrerer Variablen durch skalare UDF
(lv_projekte, lv_bearbeiter) = udf_statistik( );
--Zuweisung durch einen Ausdruck
lv_div = :lv_bearbeiter / :lv_projekte;
SELECT
lv_projekte AS projekte,
lv_bearbeiter AS bearbeiter,
lv_div AS bearbeiter_pro_projekt
FROM
dummy;
END ;
DROP FUNCTION udf_statistik;
Listing 6.3: Beispiel für die Zuweisung von Werten zu lokalen skalaren Variablen
DO BEGIN
DECLARE lv_bearbeiter INT;
DECLARE lv_status INT;
DECLARE EXIT HANDLER
SELECT bearbeiter,
status
INTO lv_bearbeiter,
lv_status
FROM aufgaben
WHERE id = -1;
SELECT lv_bearbeiter,
lv_status FROM dummy;
END;
Listing 6.4: Beispiel für SELECT INTO in mehrere Variablen
CREATE PROCEDURE get_bearbeiter_status
(IN iv_id INT,
OUT ov_bearbeiter INT,
OUT ov_status INT )
AS BEGIN
--Bis HANA 2.0 SPS03:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ov_bearbeiter = -1;
ov_status = -1;
END;
SELECT bearbeiter,
status
INTO ov_bearbeiter,
ov_status
DEFAULT -1, --Ab SAP HANA 2.0 SPS03: Standardwerte
-1 --für Variablen bei der Zuweisung
FROM aufgaben
WHERE id = iv_id;
END;
CALL get_bearbeiter_status(1, ?, ?);
CALL get_bearbeiter_status(-1, ?, ?);
Listing 6.5: Beispiel für die Fehlerverarbeitung bei SELECT INTO
DO
BEGIN --Begin des äusseren Blocks
DECLARE var1 VARCHAR(10) default 'Aussen';
DECLARE var2 VARCHAR(10) default 'Aussen';
BEGIN --Begin des inneren Blocks
DECLARE var1 VARCHAR(10);--Var1 wird neu deklariert
var1 = 'Innen';
var2 = 'Innen';
SELECT 'Innerer Block:' AS COMMENT,
:var1,
:var2
FROM dummy;
END;-- Ende des inneren Blocks
SELECT 'Aeusserer Block' AS COMMENT,
:var1,
:var2
FROM dummy;
END;--Ende des äusseren Blocks
Listing 6.6: Verschachtelte Blöcke und Überdeckung von Variablen
<Tabelle>.<Spalte>[<Zeilennummer>]
DO (OUT ot_result TABLE( id INT,
titel NVARCHAR(50))=>?)
BEGIN
ot_result = SELECT TOP 10 id, titel FROM aufgaben;
ot_result.titel[1] = 'Titel: ' || :ot_result.titel[1];
END;
Listing 6.7: Indexbasierter Zugriff auf Tabellenvariablen
DO (OUT ot_result TABLE(text NVARCHAR(100))=>?)
BEGIN
DECLARE indx integer;
lt_aufgaben = SELECT TOP 10 *
FROM aufgaben;
lt_status = SELECT *
FROM status_text
WHERE sprache = 'DE'
ORDER BY id ASC;
FOR indx IN 1..10 DO
ot_result.text[:indx] =
:lt_status.status_text[:lt_aufgaben.status[:indx]]
|| ' - '
|| :lt_aufgaben.titel[:indx];
END FOR;
END;
Listing 6.8: Indexbasierter Zugriff mit skalaren Ausdrücken als Index
DO (OUT ot_result TABLE( id INT,
titel NVARCHAR(50))=>?)
BEGIN
ot_result = SELECT TOP 10 id, titel FROM aufgaben;
ot_result.titel[30] = :ot_result.titel[1];
ot_result.titel[1] = :ot_result.titel[31];
END;
Listing 6.9: Zugriff auf nicht vorhandene Zeilen
:<Tabellenvariable>[.<Spaltenliste>].INSERT(
(<Werteliste>)[, <Position>]);
DO (OUT rt_result TABLE( id INT,
text1 NVARCHAR(50),
text2 NVARCHAR(50))=>? )
BEGIN
DECLARE lv_index INT;
FOR lv_index IN 1..5
DO
-- Einfügen aller Spalten an der Position 1
:rt_result.INSERT((lv_index,
'Einfügen '||lv_index, '')
, 1);
END FOR;
FOR lv_index IN 1..5
DO
-- Anhängen an die Tabelle, nur für zwei Spalten
:rt_result.(id, text2).INSERT((lv_index,
'Anhängen '||lv_index));
END FOR;
END;
Listing 6.10: Beispiel für das zeilenweise Einfügen in eine Tabellenvariable mit INSERT
:<Tabellenvariable>[.<Spaltenliste>].INSERT(
<Tabellenvariable2[, <Position>])
DO (OUT ot_result TABLE( id INT,
text1 NVARCHAR(50),
text2 NVARCHAR(50))=>? )
BEGIN
DECLARE lv_index INT;
DECLARE lt_tmp TABLE (spalte1 INT,
spalte2 NVARCHAR(50) );
-- Tabelle LT_TMP zeilenweise aufbauen
:lt_tmp.INSERT((1, 'TMP_1'));
:lt_tmp.INSERT((2, 'TMP_2'));
-- Tabelle RT_RESULT zeilenweise aufbauen
:ot_result.INSERT((1, 'Erster Datensatz ', 'A'));
:ot_result.INSERT((2, 'Zweiter Datensatz', 'B'));
:ot_result.INSERT((3, 'Dritter Datensatz', 'C'));
-- Tabelle LT_TMP in RT_RESULT an Position 2 einfügen
:ot_result.(id, text1).INSERT(:lt_tmp, 2);
END;
Listing 6.11: Tabellenweises Einfügen mit der INSERT-Operation
:<Tabellenvariable>[.<Spaltenliste>].UPDATE((<Werteliste>),
<Position>);
<Tabellenvariable>[.<Spaltenliste>].[<Position>] =
(<Werteliste>);
DO BEGIN
DECLARE lt_tmp TABLE(text NVARCHAR(100));
-- Zeilenweiser Aufbau der Tabelle
:lt_tmp.INSERT(('Einfügen 1'), 1);
:lt_tmp.INSERT(('Einfügen 2'), 2);
:lt_tmp.INSERT(('Einfügen 3'), 3);
-- Update der Zeilen
:lt_tmp.UPDATE(('Aktualisieren 2'),2);
:lt_tmp.UPDATE(('Aktualisieren 4'),4);
-- Update mit indexbasiertem Zugriff
lt_tmp[6] = ('Aktualisieren 6');
END;
Listing 6.12: Zeilenweises Aktualisieren einer Tabellenvariablen mit UPDATE und indexbasiertem Zugriff
<Tabellenvariable>.DELETE([<Zeilen>]);
DO (OUT ot_result TABLE(text NVARCHAR(100))=>?)
BEGIN
DECLARE lv_index INT;
DECLARE lv_del INT ARRAY = ARRAY(1, 3);
-- Aufbau einer Tabelle mit den Zeilen 1 bis 10
FOR lv_index IN 1..10 DO
:ot_result.INSERT(('Zeile '||lv_index), lv_index);
END FOR;
-- Löschen der Zeile 9
:ot_result.DELETE(9);
-- Löschen der Zeilen 5 bis 7
:ot_result.DELETE(5..7);
-- Löschen der Zeilen 1 und 3 aus dem ARRAY LV_DEL
:ot_result.DELETE(:lv_del);
END;
Listing 6.13: Beispiele für die unterschiedlichen Varianten des Löschens von Zeilen aus einer Tabellenvariablen
:<Tabellenvariabe>.SEARCH((<Spaltenliste>),
(<Werteliste>),
[<Startindex>]);
Listing 6.14: Syntax des SEARCH-Operators
DO (OUT ot_result TABLE( id INT,
status INT,
bearbeiter INT,
titel NVARCHAR(50))=>? )
BEGIN
DECLARE lv_index INT = 0;
ot_result = SELECT id,
status,
bearbeiter,
titel
FROM aufgaben
ORDER BY id;
-- Suche mit SEARCH
lv_index = :ot_result.SEARCH((status, bearbeiter),
(3,3));
:ot_result.DELETE(lv_index);
-- Direkte Verwendung des SEARCH-Operators im DELETE-Operator
:ot_result.DELETE(:ot_result.SEARCH((status, bearbeiter),
(3,3)));
END;
Listing 6.15: Suche in Tabellen mit dem SEARCH-Operator
DECLARE lt_dml TABLE (vorname NVARCHAR(30),
nachname NVARCHAR(30));
INSERT INTO :lt_dml (SELECT vorname,
nachname
FROM benutzer);
SELECT *
FROM :lt_dml;
lt_normal = SELECT * FROM :lt_dml;
DECLARE <Tabellenvariable> TABLE(<Spaltendefinition>)
SEARCH KEY(<Suchschlüssel>)
DO BEGIN
DECLARE lv_index INTEGER;
DECLARE lt_task TABLE (id INTEGER,
bearbeiter INTEGER,
titel NVARCHAR(130))
SEARCH KEY (bearbeiter);
lt_task = SELECT id,
bearbeiter,
titel
FROM aufgaben;
--Erste Zeile mit SEARCH finden
lv_index = :lt_task.SEARCH(bearbeiter, 4);
--Schleife über die Tabelle, solange die Bedingung erfüllt ist
WHILE :lv_index > 0
AND :lt_task.bearbeiter[:lv_index] = 4
DO
if :lt_task.bearbeiter[:lv_index] = 4 THEN
lt_task.titel[:lv_index] = '>>'
|| :lt_task.titel[:lv_index];
end if;
lv_index = :lv_index + 1;
END WHILE;
SELECT *
FROM :lt_task;
END;
Listing 6.16: Suche und Schleife mit einer sortierten Tabelle
SET <Variablenname> = <Wert>;
DO BEGIN
SET 'TESTVARIABLE' = 'TESTWERT';
SELECT session_context( 'TESTVARIABLE' ) FROM dummy;
END;
Listing 6.17: Verwendung von Sessionvariablen
CREATE GLOBAL TEMPORARY TABLE test_gtt (id INT,
text NVARCHAR(40));
INSERT INTO test_gtt ( SELECT id, titel FROM aufgaben);
SELECT * FROM test_gtt;
Listing 6.18: Beispiel für eine globale temporäre Tabelle
CREATE LOCAL TEMPORARY TABLE #test_ltt (id INT,
text NVARCHAR(40));
INSERT INTO #test_ltt ( SELECT id, titel FROM aufgaben);
SELECT * FROM #test_ltt;
Listing 6.19: Beispiel für eine lokale temporäre Tabelle
IF <Bedingung_1> THEN <Block_1>
[ELSEIF <Bedingung_2> THEN <Block_2>]
...
[ELSE <Block_N>]
END IF;
Listing 6.20: Syntax der IF-Anweisung
IF EXISTS (SELECT id
FROM aufgaben
WHERE status = 2
AND faelligkeit < CURRENT_DATE)
THEN
<do something>
ELSEIF :lv_status IN (SELECT id
FROM status
WHERE is_final = true)
THEN
<do something else>
END IF;
Listing 6.21: Prädikate mit Unterabfragen
CREATE FUNCTION udf_anzeigename(lv_vorname NVARCHAR(20),
lv_nachname NVARCHAR(20),
lv_zwischenname NVARCHAR(20))
RETURNS rv_name NVARCHAR(65)
AS BEGIN
IF lv_vorname IS NOT NULL AND
lv_nachname IS NOT NULL AND
lv_zwischenname IS NOT NULL
THEN
DECLARE lv_initiale NVARCHAR(1);
lv_initiale = LEFT(lv_zwischenname,1) ;
IF lv_initiale<>''
THEN
rv_name = lv_vorname || ' ' || lv_initiale
|| '. ' || lv_nachname;
ELSE
rv_name = lv_vorname || ' ' || lv_nachname;
END IF;
ELSEIF lv_vorname IS NOT NULL AND
lv_nachname IS NOT NULL
THEN
rv_name = lv_vorname || ' ' || lv_nachname;
ELSEIF lv_nachname IS NOT NULL
THEN
rv_name = lv_nachname;
ELSEIF lv_vorname IS NOT NULL
THEN
rv_name = lv_vorname;
ELSE
rv_name = '';
END IF;
END;
SELECT udf_anzeigename('Jörg','Brandeis','Roland')
FROM dummy
UNION SELECT udf_anzeigename('Jörg','Brandeis',NULL)
FROM dummy
UNION SELECT udf_anzeigename(NULL,'Brandeis',NULL)
FROM dummy;
DROP FUNCTION udf_anzeigename;
Listing 6.22: Beispiel für verschachtelte IF-Anweisungen
CREATE FUNCTION udf_sort_2(iv_wert1 INT,
iv_wert2 INT )
RETURNS rv_klein INT,
rv_gross INT
AS BEGIN
IF iv_wert1 >= iv_wert2 THEN
rv_klein = iv_wert2;
rv_gross = iv_wert1;
ELSE
rv_klein = iv_wert1;
rv_gross = iv_wert2;
END IF;
END;
Listing 6.23: Funktion UDF_SORT_2 zur Sortierung von zwei Zahlen
CREATE FUNCTION udf_sort_3(iv_wert1 INT,
iv_wert2 INT,
iv_wert3 INT )
RETURNS rv_klein INT,
rv_mittel INT,
rv_gross INT
AS BEGIN
--Anfangszuweisung (wahrscheinlich falsch)
rv_klein = :iv_wert1;
rv_mittel = :iv_wert2;
rv_gross = :iv_wert3;
--Tauschen, bis die Sortierung richtig ist
(rv_klein, rv_mittel) = udf_sort_2(rv_klein, rv_mittel);
(rv_klein, rv_gross) = udf_sort_2(rv_klein, rv_gross);
(rv_mittel, rv_gross) = udf_sort_2(rv_mittel, rv_gross);
END;
Listing 6.24: Funktion UDF_SORT_3 für die Sortierung von drei Zahlen
DO (OUT ov_klein INT=>?,
OUT ov_mittel INT=>?,
OUT ov_gross INT=>?)
BEGIN
(ov_klein, ov_mittel, ov_gross) = udf_sort_3(1,11,9);
END;
Listing 6.25: Anonymer Block zum Testen der Funktion UDF_SORT_3
FOR <Variable> IN [REVERSE] <Ausgangswert>..<Endwert> DO
<Block>
END FOR;
Listing 6.26: Syntax der FOR-Schleife
DO BEGIN
DECLARE lv_sum INT = 0;
DECLARE lv_indx INT;
FOR lv_indx IN 1..10 DO
DECLARE lv_tmp INT = lv_sum;
lv_tmp = :lv_tmp + 10;
lv_sum = lv_tmp;
END FOR;
SELECT :lv_indx, :lv_sum FROM dummy;
END;
Listing 6.27: Beispiel für eine FOR-Schleife
WHILE <Bedingung> DO
<Block>
END WHILE;
Listing 6.28: Syntax der WHILE-Schleife
CREATE FUNCTION udf_fakultaet( IN iv_value INT)
RETURNS rv_result INT
AS BEGIN
DECLARE lv_counter INT = 1;
rv_result = 1;
WHILE lv_counter <= iv_value DO
rv_result = rv_result * lv_counter;
lv_counter = lv_counter + 1;
END WHILE;
END;
SELECT udf_fakultaet( 10 ) FROM dummy;
DROP FUNCTION udf_fakultaet;
Listing 6.29: Beispiel für eine WHILE-Schleife
CREATE FUNCTION udf_ggt(iv_zahl1 INT,
iv_zahl2 INT )
RETURNS rv_ggt INT
AS BEGIN
DECLARE lv_zahl1 INT DEFAULT :iv_zahl1;
DECLARE lv_zahl2 INT DEFAULT :iv_zahl2;
DECLARE lv_cnt INT DEFAULT 0;
WHILE lv_zahl1 <> lv_zahl2 DO
--FOR lv_cnt IN 0..100 DO
DECLARE lv_lower INT DEFAULT 0;
DECLARE lv_higher INT DEFAULT 0;
--IF lv_zahl1 = lv_zahl2 THEN BREAK;
--END IF;
(lv_lower, lv_higher) = udf_sort_2(:lv_zahl1,
:lv_zahl2);
lv_zahl1 = :lv_higher - :lv_lower;
lv_zahl2 = :lv_lower;
END WHILE;
--END FOR;
rv_ggt = :lv_zahl1;
END;
Listing 6.31: Übungsbeispiel für das Berechnen des ggT
DECLARE CURSOR <Cursorname> (<Parameterdefinition>)
FOR <SELECT-Abfrage>;
FOR <Zeile> AS <Cursorname>[(<Parameter>)]
DO
<Block>
END FOR;
Listing 6.32: FOR-Schleife über Cursor
CREATE PROCEDURE projekte_abschliessen
AS
BEGIN
DECLARE CURSOR lc_projekte (iv_status INT)
FOR SELECT id
FROM projekte
WHERE STATUS = :iv_status;
FOR ls_projekte AS lc_projekte(5) --Status = Geschlossen
DO CALL projektaufgaben_schliessen(ls_projekte.id);
END FOR;
END;
Listing 6.33: Beispiel für eine FOR-Schleife über einen Cursor mit Parameter
OPEN <Cursorname>(<Parameter>);
FETCH <Cursorname> INTO <Variablenliste>;
DO ( OUT ov_alle_titel NVARCHAR(900) => ?)
BEGIN
lt_aufgaben = SELECT TOP 10 titel FROM aufgaben;
ov_alle_titel = '';
BEGIN
DECLARE CURSOR lc_aufgaben
FOR SELECT titel FROM :lt_aufgaben;
DECLARE lv_indx INT;
DECLARE lv_titel NVARCHAR(40);
OPEN lc_aufgaben;
WHILE 1 <> 2 DO
FETCH lc_aufgaben INTO lv_titel;
IF lc_aufgaben::NOTFOUND THEN
BREAK;
END IF;
ov_alle_titel = ov_alle_titel || ' - ' || lv_titel;
END WHILE;
CLOSE lc_aufgaben;
END;
END;
Listing 6.34: Beispiel für die Verwendung eines Cursors
DECLARE CURSOR <Cursorname> (<Parameterdefinition>)
FOR <SELECT-Abfrage> FOR UPDATE [OF <Spaltenliste>];
UPDATE <DB-Tabelle>
SET <Set-Klausel>
WHERE CURRENT OF <Cursor>
Listing 6.35: Syntax der UPDATE-Anweisung
DELETE FROM <DB-Tabelle>
WHERE CURRENT OF <Cursor>
Listing 6.36: Syntax der DELETE-Anweisung
CREATE COLUMN TABLE array_demo(
keycol INT ,
namen NVARCHAR(30) ARRAY
);
INSERT INTO array_demo VALUES( 1,
ARRAY ('Jörg',
'Roland',
'Brandeis')
);
INSERT INTO array_demo VALUES( 2,
ARRAY ('Peter',
'Maier')
);
INSERT INTO array_demo VALUES( 3,
ARRAY ('Rotkäpchen')
);
SELECT keycol,
namen[1], --Erstes Element
namen[cardinality(namen)] --Letztes Element
FROM array_demo;
DROP TABLE array_demo;
Listing 6.37: Beispiel für den Zugriff auf ein Array in einer Datenbanktabelle
DO BEGIN
DECLARE i INT;
DECLARE demo VARCHAR(20) ARRAY;
-- Einfügen von zehn Elementen in das ARRAY
FOR i IN 1..10 DO
demo[:i] = concat ('Wert Nr.', :i);
END FOR;
SELECT :demo[10] FROM dummy;
-- Entfernen der hinteren fünf Elemente
demo = TRIM_ARRAY( :demo, 5 );
SELECT :demo[1],
:demo[5],
:demo[10]
FROM dummy;
-- Einfügen eines Elements an Position 20
demo[20] = 'Letztes Element';
SELECT :demo[5],
:demo[10],
:demo[20]
FROM dummy;
END;
Listing 6.38: Arrays in SQLScript-Code
DO BEGIN
DECLARE tiere VARCHAR(10) ARRAY = ARRAY('Hund', 'Katze',
'Maus');
DECLARE farben VARCHAR(10) ARRAY = ARRAY('rot', 'gelb',
'blau', 'orange', 'grün', 'violett');
tabelle = UNNEST(:tiere, :farben)
WITH ORDINALITY AS (
"Tier",
"Farbe",
"Nr"
);
SELECT *
FROM :tabelle;
END;
Listing 6.39: Umwandlung von ARRAYs in Tabellen mit UNNEST()
DO BEGIN
DECLARE lv_cardinality INT;
DECLARE la_bearbeiter INT ARRAY;
lt_aufgaben = SELECT DISTINCT bearbeiter FROM aufgaben;
la_bearbeiter = ARRAY_AGG(:lt_aufgaben.bearbeiter);
lv_cardinality = CARDINALITY(:la_bearbeiter);
SELECT :lv_cardinality
FROM dummy;
END;
Listing 6.40: Konvertierung von einer Tabellenspalte in ein Array und Abfrage der Kardinalität
--Tabellentyp TT_WERT anlegen
CREATE TYPE tt_wert AS TABLE(wert NVARCHAR(30));
--Funktion anlegen
CREATE FUNCTION udf_sort(in it_table tt_wert)
RETURNS tt_wert
AS BEGIN
DECLARE la_werte NVARCHAR(30) ARRAY;
DECLARE lv_zeilen INT; --Anzahl Zeilen
DECLARE lv_runde INT; --Aktuelle Runde
DECLARE lv_zeile INT; --Aktuelle Zeile
DECLARE lv_tmp NVARCHAR(30); --Für die Vertauschung
--Umwandeln der Tabelle in ein Array:
la_werte = ARRAY_AGG(:it_table.wert);
--Anzahl der Zeilen ermitteln:
SELECT COUNT(*) INTO lv_zeilen FROM :it_table;
FOR lv_runde IN 1..:lv_zeilen DO
FOR lv_zeile IN 2..:lv_zeilen DO
IF :la_werte[:lv_zeile-1] > :la_werte[:lv_zeile]
THEN --Vertauschung:
lv_tmp = :la_werte[:lv_zeile-1];
la_werte[:lv_zeile-1] = :la_werte[:lv_zeile];
la_werte[:lv_zeile] = :lv_tmp;
END IF;
END FOR; --Zeilen
END FOR; --Runden
--Umwandlung des sortierten Arrays in eine Tabelle:
rt_result = UNNEST(:la_werte) AS (wert);
RETURN :rt_result;
END;
Listing 6.41: Der Bubblesort-Algorithmus in SQLScript
DO BEGIN
DECLARE lt_testdaten tt_wert;
DECLARE la_testdaten NVARCHAR(30) ARRAY ;
la_testdaten = ARRAY('Z', 'Y', 'B', 'A');
lt_testdaten = UNNEST(:la_testdaten) AS ( wert );
SELECT * FROM udf_sort(:lt_testdaten);
END;
Listing 6.42: Test des Sortieralgorithmus mit einfachen Daten
BEGIN AUTONOMOUS TRANSACTION
...
END;
Listing 6.43: Syntax einer autonomen Transaktion
CREATE PROCEDURE write_error_log (
IN iv_error_code INT,
IN iv_error_message NVARCHAR(500) )
AS BEGIN
BEGIN AUTONOMOUS TRANSACTION
INSERT INTO error_log ( zeitstempel,
fehlercode,
nachricht )
VALUES ( current_timestamp,
:iv_error_code,
:iv_error_message );
END;
END;
Listing 6.44: Beispiel für eine Log-Prozedur mit einer autonomen Transaktion.
DO BEGIN
DECLARE lv_statement VARCHAR(1000);
DECLARE cursor lr_tables
FOR SELECT table_columns.table_name
FROM table_columns
INNER JOIN tables
ON table_columns.table_Name = tables.table_name
AND tables.is_user_defined_type = 'FALSE'
WHERE table_columns.schema_name = 'JBRANDEIS'
AND column_name = 'ID';
--Anlegen der LTT
CREATE LOCAL TEMPORARY TABLE #max_id(table_name NVARCHAR(256),
max_id INT );
FOR ls_tables AS lr_tables DO
--Zusammenbau der INSERT-Anweisung
lv_statement = 'INSERT INTO #max_id SELECT '''
|| ls_tables.table_name
|| ''' AS table_name, max(ID) AS max_id FROM '
|| ls_tables.table_name || ';';
--Ausführung des dynamischen SQLs
EXEC :lv_statement ;
END FOR;
--Ergebnis aus der LTT abholen
SELECT * FROM #max_id ORDER BY max_id DESC;
END;
Listing 6.45: Beispiel für dynamisches SQL mit einer lokalen temporären Tabelle
EXECUTE IMMEDIATE <SQL-Anweisung> INTO <Variablenliste>
USING <Ausdrucksliste>
CREATE PROCEDURE get_column_values
(IN iv_column NVARCHAR(30),
IN iv_table NVARCHAR(30),
OUT ot_result TABLE(value NVARCHAR(100)))
AS BEGIN
DECLARE lv_sql NVARCHAR(1000) DEFAULT '';
lv_sql = 'SELECT DISTINCT '
|| :iv_column
|| ' AS value FROM '
|| :iv_table;
EXEC lv_sql INTO ot_result;
END;
CALL get_column_values( 'TITEL', 'AUFGABEN', ?);
Listing 6.46: Ergebnistabelle einer dynamischen SQL-Abfrage in SQLScript übernehmen
CREATE PROCEDURE get_min_max
(IN iv_column NVARCHAR(30),
IN iv_table NVARCHAR(30),
OUT ev_min NVARCHAR(100),
OUT ev_max NVARCHAR(100) )
AS BEGIN
DECLARE lv_sql NVARCHAR(1000) DEFAULT '';
lv_sql = 'SELECT MIN(' || :iv_column
||'),MAX(' || :iv_column
||') FROM '|| :iv_table;
EXEC lv_sql INTO ev_min, ev_max;
END;
CALL get_min_max( 'TITEL', 'AUFGABEN', ?, ?);
Listing 6.47: Mehrere skalare Variablen aus einer dynamischen SQL-Abfrage übernehmen
CREATE PROCEDURE count_id( IN iv_maxid INT,
OUT ov_count INT )
AS BEGIN
EXEC 'SELECT COUNT(*) FROM aufgaben WHERE id < ?'
INTO ov_count
USING :iv_maxid;
-- Alternative mit benannten Parametern
-- EXEC 'SELECT COUNT(*) FROM aufgaben WHERE id < :iv_maxid'
-- INTO ov_count
-- USING :iv_maxid;
-- Alternative mit dynamischer Generierung der SQL Anweisung
-- EXEC 'SELECT COUNT(*) FROM aufgaben WHERE id <'
-- || :iv_maxid
-- INTO ov_count;
END;
CALL count_id( 10, ?);
Listing 6.48: Beispiel für Eingabeparameter in dynamischem SQL
CREATE PROCEDURE dynsel_from_local_table
( OUT ov_count INT )
AS BEGIN
lt_tmp = SELECT id FROM aufgaben;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :lt_tmp'
INTO ov_count
USING :lt_tmp;
END;
CALL dynsel_from_local_table(?);
Listing 6.49: Dynamische Selektion aus einer Tabellenvariablen
CREATE PROCEDURE gefilterte_aufgaben (
IN iv_where VARCHAR(100),
OUT rt_ergebnis TABLE ( id INT,
status INT,
titel NVARCHAR(40) ) )
AS BEGIN
lt_tmp = APPLY_FILTER(aufgaben, :iv_where);
rt_ergebnis = SELECT id,
status,
titel
FROM :lt_tmp;
END;
CALL gefilterte_aufgaben('status in ( 1, 2)', ?);
Listing 6.50: Dynamisches Filtern mit APPLY_FILTER
DO BEGIN
DECLARE lv_where NVARCHAR(1000) = '';
lv_where = 'titel LIKE (''v%'') ';
lt_tmp = SELECT * FROM aufgaben;
lt_tmp = apply_filter(:lt_tmp, :lv_where);
SELECT * FROM :lt_tmp;
END
Listing 6.51: Verwendung von Literalen in dynamischem Code
SELECT * FROM m_error_codes ORDER BY code;
DECLARE <Fehlername> CONDITION [FOR SQL_ERROR_CODE <Fehlercode>];
DECLARE unbekannte_aufgabe CONDITION;
DECLARE Division_durch_0 CONDITION SQL_ERROR_CODE 304;
--Nur mit Fehlercode und Text:
SIGNAL SQL_ERROR_CODE 10000 SET MESSAGE_TEXT = 'Mein Fehler';
--oder mit definiertem Fehlername und Text:
DECLARE my_error CONDITION FOR SQL_ERROR_CODE 10000;
...
SIGNAL my_error SET MESSAGE_TEXT = 'Mein Fehler';
--oder auch ohne Fehlercode und Text:
DECLARE my_error CONDITION;
...
SIGNAL my_error;
Listing 6.52: Beispiele für das Auslösen von benutzerdefinierten Ausnahmen
DECLARE EXIT HANDLER FOR <Fehlerliste> <Anweisung>;
WRITE_ERROR_LOG( <Fehlercode>, <Nachricht>)
und
SHOW_ERROR_LOG([<Zeitraum_in_Sekunden>])
CREATE PROCEDURE test_fehlerbehandlung(
IN iv_aktion VARCHAR(10))
AS BEGIN
/* Deklarationen*/
DECLARE lv_tmp INT;
DECLARE fehler1 CONDITION ;
DECLARE fehler2 CONDITION FOR SQL_ERROR_CODE 10001 ;
DECLARE division_durch_0 CONDITION FOR SQL_ERROR_CODE 304;
/* Fehlerbehandler*/
DECLARE EXIT HANDLER FOR fehler1
write_error_log( ::sql_error_code,
::sql_error_message );
DECLARE EXIT HANDLER FOR fehler2
write_error_log( ::sql_error_code, 'Fehler2' );
DECLARE EXIT HANDLER FOR division_durch_0
write_error_log( ::sql_error_code,
'SQL-Fehler: ' || ::sql_error_message );
/* Anweisungen*/
IF iv_aktion='F1' THEN
SIGNAL fehler1 ;
ELSEIF iv_aktion='F2' THEN
SIGNAL fehler2;
ELSEIF iv_aktion='D0' THEN
SELECT 1/0 FROM dummy ;
END IF;
END;
CALL test_fehlerbehandlung('F1');
CALL test_fehlerbehandlung('F2');
CALL test_fehlerbehandlung('D0');
CALL show_error_log();
DROP PROCEDURE test_fehlerbehandlung;
Listing 6.53: Beispiele für die Fehlerbehandlung von SQL-Fehlern und benutzerdefinierten Fehlern
CREATE PROCEDURE fehler_ausloesen
AS
BEGIN
--Deklaration
DECLARE mein_fehler CONDITION FOR SQL_ERROR_CODE 10001;
--Anweisungen
SIGNAL mein_fehler;
END;
CREATE PROCEDURE fehler_fangen
AS
BEGIN
--Deklaration
DECLARE mein_Fehler CONDITION FOR SQL_ERROR_CODE 10001;
--Fehlerbehandlung
DECLARE EXIT HANDLER FOR mein_fehler
CALL write_error_log(::SQL_ERROR_CODE, 'Hab ihn!');
--Anweisungen
CALL fehler_ausloesen();
END;
CALL fehler_fangen;
CALL show_error_log();
DROP PROCEDURE fehler_ausloesen;
DROP PROCEDURE fehler_fangen;
Listing 6.54: Beispiel für die Fehlerbehandlung bei Prozeduraufrufen
CREATE PROCEDURE demo_resignal
AS BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN --Block für die Fehlerbehandlung
IF ::sql_error_code > 219 --Die könnten interessieren
THEN
CALL write_error_log(::sql_error_code,
::sql_error_message);
ELSE
RESIGNAL;--Bei den Fehlern besser abbrechen
END IF;
END;--Ende der Fehlerbehandlung
...
END;
Listing 6.55: Weitergabe von Fehlern mit RESIGNAL
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RESIGNAL SET MESSAGE_TEXT = 'Prozedur'
|| ::CURRENT_OBJECT_SCHEMA
|| '.'
|| ::CURRENT_OBJECT_NAME
|| ::SQL_ERROR_MESSAGE;
Listing 6.56: Fehlerbehandler mit Ergänzung von Datenbankschema und Prozedur im Fehlertext
CREATE TABLE status (id INT ,
text NVARCHAR(30) );
<Spaltenname>
<Datentyp>
[DEFAULT <Standardwert>]
[<Einschränkung>]
Listing 7.1: Spaltendefinition
CREATE TABLE status (id INT PRIMARY KEY,
sortiernr INT NOT NULL UNIQUE,
text NVARCHAR(30) );
Listing 7.2: Beispiel für Einschränkungen auf einzelnen Spalten
CREATE TABLE test_unique (a INT,
b INT,
c INT,
UNIQUE(a, b),
UNIQUE(b, c));
Listing 7.3: Beispiel für mehrere UNIQUE-Einschränkungen auf mehreren Spalten
CREATE TABLE test_composite_key (a INT,
b INT,
c INT,
PRIMARY KEY(a, b));
Listing 7.4: Beispiel für einen zusammengesetzten Primärschlüssel
CREATE [<Typ>] TABLE
CREATE COLUMN TABLE test_identity (
a INT GENERATED BY DEFAULT AS IDENTITY,
b VARCHAR(10));
INSERT INTO test_identity (b) VALUES ('Eins');
INSERT INTO test_identity (b) VALUES ('Zwei');
INSERT INTO test_identity (a,b) VALUES (3, 'Drei');
INSERT INTO test_identity (b) VALUES ('Vier');
SELECT * FROM test_identity;
Listing 7.5: Automatische Nummernvergabe
CREATE TABLE <Tabellenname>
LIKE <Originaltabelle>
[WITH DATA];
CREATE TABLE tmp_aufgaben AS (
SELECT a.id,
b.vorname,
b.nachname,
t.team_text
FROM aufgaben AS a
LEFT OUTER JOIN benutzer AS b
ON a.bearbeiter = b.id
LEFT OUTER JOIN team_text AS t
ON b.team = t.id
)
WITH DATA;
Listing 7.6: Anlegen einer neuen Tabelle auf Basis einer SQL-Abfrage
CREATE ROW TABLE demo_table(
col1 INT,
col2 INT
);
--Hinzufügen einer Spalte
ALTER TABLE demo_table ADD (col3 VARCHAR(20));
--Ändern von Spalteneigenschaften, z. B. Standardwert setzen:
ALTER TABLE demo_table ALTER (col1 INT DEFAULT 42);
--Hinzufügen der Primärschlüsseldefinition:
ALTER TABLE demo_table
ADD CONSTRAINT pk PRIMARY KEY (col1, col2);
--Ändern des Typs:
ALTER TABLE demo_table COLUMN;
Listing 7.7: Beispiel für die Änderung von Tabelleneigenschaften
CREATE TYPE TESTTYP AS TABLE(
col1 INT,
col2 VARCHAR(10) );
CREATE PROCEDURE TESTPROC(
IN it_data TESTTYP )
...
Listing 7.8: Beispiel für die Definition eines Tabellentyps und die Verwendung in einer Prozedurdefinition
CREATE TABLE rechnungspositionen(
rechnungsnr INT,
position INT,
produkt NVARCHAR(30),
menge INT,
betrag DEC(17,2),
waehrung VARCHAR(5),
PRIMARY KEY (rechnungsnr, position)
);
CREATE VIEW rechnungen
AS SELECT rechnungsnr,
SUM(betrag*menge) AS betrag,
waehrung
FROM rechnungspositionen
GROUP BY rechnungsnr,
waehrung;
INSERT INTO rechnungspositionen
VALUES (1, 10, 'Fernseher', 1, 765.23, 'EUR');
INSERT INTO rechnungspositionen
VALUES (1, 20, 'Kabel' , 1, 12.99 , 'EUR');
INSERT INTO rechnungspositionen
VALUES (1, 30, 'Batterien', 4, 1.99 , 'EUR');
INSERT INTO rechnungspositionen
VALUES (2, 10, 'Maus ', 1, 23.99 , 'EUR');
INSERT INTO rechnungspositionen
VALUES (3, 10, 'Kabel' , 2, 12.99 , 'EUR');
INSERT INTO rechnungspositionen
VALUES (3, 20, 'Swich' , 1, 27.99 , 'USD');
--SELECT-Abfrage auf die Tabelle
SELECT rechnungsnr,
SUM(betrag*menge) AS betrag,
waehrung
FROM rechnungspositionen
GROUP BY rechnungsnr,
waehrung;
--SELECT-Abfrage auf den VIEW
SELECT * FROM rechnungen;
Listing 7.9: Beispiel für einen View als gespeicherte SELECT-Abfrage
CREATE VIEW <Viewname> AS <Abfrage>;
CREATE VIEW rechnungen (IN iv_waehrung VARCHAR(5))
AS SELECT rechnungsnr,
SUM(betrag*menge) AS betrag,
waehrung
FROM rechnungspositionen
WHERE waehrung = :iv_waehrung
GROUP BY rechnungsnr,
waehrung;
Listing 7.10: Definition von parametrisierten Views
SELECT * FROM rechnungen( 'USD' );
SELECT * FROM rechnungen( iv_waehrung=>'USD' );
CREATE SEQUENCE <Sequenzname> <Parameter>
CREATE SEQUENCE countdown INCREMENT BY-1
MAXVALUE 10
MINVALUE 0
CYCLE;
DO BEGIN
DECLARE lv_counter INT;
DECLARE lv_tmp INT;
DECLARE la_array INT ARRAY;
FOR lv_counter IN 1..100 DO
SELECT countdown.nextval
INTO lv_tmp
FROM dummy;
la_array[:lv_counter] = lv_tmp;
END FOR;
lt_output = UNNEST(:la_array);
SELECT *
FROM :LT_OUTPUT;
END;
Listing 7.11: Beispiel für die Nutzung einer Sequenz
CREATE SEQUENCE lognr; --Technischer Schlüssel des Log
CREATE TABLE rechnungspositionen(
rechnungsnr INT,
position INT,
produkt NVARCHAR(30),
menge INT,
betrag DEC(17,2),
waehrung VARCHAR(5),
PRIMARY KEY (rechnungsnr, position)
);
CREATE TABLE rp_log(
log_nr INT,
rechnungsnr INT,
position INT,
benutzer VARCHAR(80),
datum DATE,
zeit TIME,
aktion VARCHAR(1)
);
--Trigger für das Einfügen
CREATE TRIGGER rp_insert AFTER INSERT
ON rechnungspositionen
REFERENCING NEW ROW ls_new
FOR EACH ROW
BEGIN
INSERT INTO rp_log VALUES( lognr.nextval,
:ls_new.rechnungsnr,
:ls_new.position,
CURRENT_USER,
CURRENT_DATE,
CURRENT_TIME,
'I');
END;
--Trigger für das Aktualisieren von Daten
CREATE TRIGGER rp_update AFTER UPDATE
ON rechnungspositionen
REFERENCING NEW ROW ls_new
FOR EACH ROW
BEGIN
INSERT INTO rp_log VALUES( lognr.nextval,
:ls_new.rechnungsnr,
:ls_new.position,
CURRENT_USER,
CURRENT_DATE,
CURRENT_TIME,
'U');
END;
INSERT INTO rechnungspositionen
VALUES (1, 10, 'Schokolade', 1, 1.99, 'EUR');
INSERT INTO rechnungspositionen
VALUES (1,20, 'Espresso Kaffeebohnen', 1, 13.99, 'EUR');
UPDATE rechnungspositionen
SET betrag = '12.99'
WHERE rechnungsnr = 1
AND position = 20;
SELECT * FROM rp_log;
Listing 7.12: Beispiel für einen Trigger für eine automatische Änderungsprotokollierung
CREATE TRIGGER rp_update AFTER UPDATE
ON rechnungspositionen
REFERENCING NEW ROW ls_new
OLD ROW ls_old
FOR EACH ROW
...
Listing 7.13: Zuweisung von OLD und NEW an lokale Variablen
CREATE TRIGGER rp_insert AFTER INSERT
ON rechnungspositionen
REFERENCING NEW ROW ls_new
FOR EACH ROW
...
Listing 7.14: Parametrisierung für die zeilenweise Verarbeitung
CREATE TRIGGER rp_insert AFTER INSERT
ON rechnungspositionen
REFERENCING NEW TABLE AS lt_new
FOR EACH STATEMENT
...
Listing 7.15: Parametrisierung für die Verarbeitung pro Anweisung mit Tabellenparametern
CLASS zcl_amdp_demo DEFINITION
PUBLIC
CREATE PUBLIC.
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
TYPES gty_tt_countries TYPE TABLE OF t005t.
METHODS get_countries
IMPORTING
VALUE(iv_langu) TYPE langu
EXPORTING
VALUE(et_country) TYPE gty_tt_countries
CHANGING
VALUE(cv_subrc) TYPE sy-subrc.
ENDCLASS.
CLASS zcl_amdp_demo IMPLEMENTATION.
METHOD get_countries
BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
USING t005t.
et_country = select *
FROM t005t
WHERE spras = :iv_langu;
SELECT CASE
WHEN COUNT(*) > 0
THEN 0
ELSE 4
END AS subrc
INTO cv_subrc
FROM :et_country;
ENDMETHOD.
ENDCLASS.
Listing 8.1: Beispiel für eine einfache AMDP-Methode
1 METHOD get_countries
2 BY DATABASE PROCEDURE FOR HDB
3 LANGUAGE SQLSCRIPT
4 OPTIONS READ-ONLY
5 USING t005t.
6
7 <SQLScript-Code>
8 ENDMETHOD.
Listing 8.2: Schlüsselwörter bei der Implementierung einer AMDP-Methode
CREATE PROCEDURE
"ZCL_AMDP_DEMO=>GET_COUNTRIES"
(
IN "IV_LANGU" NVARCHAR (000001),
OUT "EV_SUBRC" INTEGER,
IN "CT_COUNTRY__IN__" "ZCL_AMDP_DEMO=>GET_COUNTRIES=>P00000#ttyp",
OUT "CT_COUNTRY" "ZCL_AMDP_DEMO=>GET_COUNTRIES=>P00000#ttyp"
)
LANGUAGE sqlscript SQL SECURITY INVOKER
AS BEGIN
"CT_COUNTRY" = select * from :CT_COUNTRY__IN__ ;
BEGIN
lt_countries = SELECT land1 FROM :CT_COUNTRY;
et_country = SELECT t5.*
FROM "ZCL_AMDP_DEMO=>T005T#covw" AS t5
INNER JOIN :LT_COUNTRIES AS countries
ON t5.land1 = countries.land1
WHERE t5.spras = :IV_LANGU;
SELECT CASE
WHEN COUNT(*) > 0
THEN 0
ELSE 4
END AS subrc
INTO ev_subrc
FROM :CT_COUNTRY;
END;
END;
Listing 8.3: Quellcode der Prozedur ZCL_AMDP_DEMO=>GET_COUNTRIES
CREATE PROCEDURE
"ZCL_AMDP_DEMO=>GET_COUNTRIES#stb2#20180301173139"
(
IN "IV_LANGU" NVARCHAR (000001),
OUT "EV_SUBRC" INTEGER
)
LANGUAGE sqlscript SQL SECURITY INVOKER AS BEGIN
"CT_COUNTRY__IN__" = SELECT * FROM "ZCL_AMDP_DEMO=>GET_COUNTRIES=>P00000#tft#20180301173139" ;
CALL "ZCL_AMDP_DEMO=>GET_COUNTRIES" (
"CT_COUNTRY__IN__" => :CT_COUNTRY__IN__ ,
"IV_LANGU" => :IV_LANGU ,
"EV_SUBRC" => :EV_SUBRC ,
"CT_COUNTRY" => :CT_COUNTRY
);
SELECT * FROM :CT_COUNTRY;
END;
Listing 8.4: Quellcode der Stub-Prozedur
METHOD get_instance.
DATA lv_classname TYPE classname.
CASE sy-dbsys.
WHEN 'HDB'.
lv_classname = 'ZCL_READ_XYZ_AMDP'.
WHEN OTHERS.
lv_classname = 'ZCL_READ_XYZ_OSQL'.
ENDCASE.
CREATE OBJECT ro_instance TYPE (lv_classname).
ENDMETHOD.
Listing 8.5: Statische Factory-Methode GET_INSTANCE in ABAP
CLASS zcl_amdp_call DEFINITION PUBLIC.
PUBLIC SECTION.
TYPES: BEGIN OF ty_s_price,
item TYPE numc4,
netto TYPE wertv9,
brutto TYPE wertv9,
mwst TYPE wertv9,
waehrs TYPE waers,
END OF ty_s_price.
TYPES ty_t_price TYPE STANDARD TABLE OF ty_s_price.
INTERFACES if_amdp_marker_hdb.
METHODS calculate_mwst
IMPORTING
VALUE(iv_mwst) TYPE int1
CHANGING
VALUE(ct_price) TYPE ty_t_price.
METHODS calculate_brutto
IMPORTING
VALUE(iv_mwst) TYPE int1
CHANGING
VALUE(ct_price) TYPE ty_t_price.
ENDCLASS.
CLASS zcl_amdp_call IMPLEMENTATION.
METHOD calculate_brutto BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
USING zcl_amdp_call=>calculate_mwst.
CALL "ZCL_AMDP_CALL=>CALCULATE_MWST"(
iv_mwst => :iv_mwst,
ct_price => :ct_price,
ct_price__in__ => :ct_price );
ct_price = SELECT item,
netto,
netto + mwst as brutto,
mwst,
waehrs
FROM :ct_price;
ENDMETHOD.
METHOD calculate_mwst BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT.
ct_price = SELECT item,
netto,
brutto,
netto * :iv_mwst / 100 as mwst,
waehrs
FROM :ct_price;
ENDMETHOD.
ENDCLASS.
Listing 8.6: Aufruf einer AMDP-Prozedur aus einer anderen AMDP-Methode
METHOD <Methodenname> BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
[USING <Verwendungen>].
<SQLScript-Code>
ENDMETHOD.
Listing 8.7: Grundgerüst der Definition einer CDS-Tabellenfunktion
CLASS zjb_cl_country DEFINITION PUBLIC.
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS get_country_text
FOR TABLE FUNCTION z_country_text.
ENDCLASS.
CLASS zjb_cl_country IMPLEMENTATION.
METHOD get_country_text BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING t005t.
RETURN SELECT mandt,
land1 AS country,
landx50 AS text
FROM t005t
WHERE spras = :sy_langu
AND mandt = :mandt;
ENDMETHOD.
ENDCLASS.
SELECT *
FROM z_country_text
INTO TABLE @DATA(lt_country)
##db_feature_mode[amdp_table_function].
cl_demo_output=>display( lt_country ).
Listing 8.8: Aufbau der Implementierung einer AMDP-Funktion
@EndUserText.label: 'Country texts'
define table function z_country_text
with parameters
@Environment.systemField: #CLIENT mandt:mandt,
@Environment.systemField: #SYSTEM_LANGUAGE sy_langu:langu
returns {
mandt:mandt;
country:land1;
text:landx50;
}
implemented by method zjb_cl_country=>get_country_text;
Listing 8.9: Beispiel für die Definition einer CDS-Tabellenfunktion
CLASS zjb_cl_country DEFINITION PUBLIC.
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
CLASS-METHODS get_country_text
FOR TABLE FUNCTION z_country_text.
ENDCLASS.
CLASS zjb_cl_country IMPLEMENTATION.
METHOD get_country_text BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING t005t.
RETURN SELECT mandt,
land1 AS country,
landx50 AS text
FROM t005t
WHERE spras = :sy_langu
AND mandt = :mandt;
ENDMETHOD.
ENDCLASS.
Listing 8.10: Beispiel für die Implementierung einer CDS-Tabellenfunktion
REPORT zjb_test.
SELECT *
FROM z_country_text
INTO TABLE @DATA(lt_country)
##db_feature_mode[amdp_table_function].
cl_demo_output=>display( lt_country ).
Listing 8.11: ABAP-Programm zum Test der CDS-Tabellenfunktion aus dem Beispiel
CREATE FUNCTION
"ZJB_CL_COUNTRY=>GET_COUNTRY_TEXT"
(
"MANDT" NVARCHAR (000003),
"SY_LANGU" NVARCHAR (000001)
)
RETURNS TABLE
(
"MANDT" NVARCHAR (000003) ,
"COUNTRY" NVARCHAR (000003) ,
"TEXT" NVARCHAR (000050)
)
LANGUAGE sqlscript SQL SECURITY INVOKER AS BEGIN
RETURN SELECT mandt,
land1 AS country,
landx50 AS text
FROM "ZJB_CL_COUNTRY=>T005T#covw"
WHERE spras = :SY_LANGU
AND mandt = :MANDT;
END;
Listing 8.13: Generierte UDF-Funktion zu der AMDP-Funktion
CLASS zcl_amdp_func DEFINITION PUBLIC.
PUBLIC SECTION.
TYPES: BEGIN OF ty_s_country,
mandt TYPE mandt,
country TYPE land1,
text TYPE landx50,
END OF ty_s_country.
TYPES ty_t_country TYPE STANDARD TABLE OF ty_s_country
WITH DEFAULT KEY.
INTERFACES if_amdp_marker_hdb.
METHODS test_amdp_table_function
IMPORTING VALUE(iv_langu) TYPE langu
VALUE(iv_mandt) TYPE mandt
EXPORTING VALUE(et_country) TYPE ty_t_country.
PRIVATE SECTION.
METHODS get_country_text
IMPORTING VALUE(iv_langu) TYPE langu
VALUE(iv_mandt) TYPE mandt
RETURNING VALUE(rt_country) TYPE ty_t_country.
ENDCLASS.
CLASS zcl_amdp_func IMPLEMENTATION.
METHOD test_amdp_table_function BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING zcl_amdp_func=>get_country_text.
et_country = SELECT *
FROM "ZCL_AMDP_FUNC=>GET_COUNTRY_TEXT"
( iv_langu => :iv_langu,
iv_mandt => :iv_mandt);
ENDMETHOD.
METHOD get_country_text BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING t005t.
RETURN SELECT mandt,
land1 AS country,
landx50 AS text
FROM t005t
WHERE spras = :iv_langu
AND mandt = :iv_mandt;
ENDMETHOD.
ENDCLASS.
Listing 8.14: Beispiel für die Verwendung einer AMDP-Funktion in einer anderem AMDP-Methode
CLASS zjb_bw_tools_amdp DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
METHODS replace_unallowed_characters
IMPORTING VALUE(iv_input) TYPE rschavl60
RETURNING VALUE(rv_output) TYPE rschavl60.
ENDCLASS.
CLASS zjb_bw_tools_amdp IMPLEMENTATION.
METHOD replace_unallowed_characters BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
rv_output = CASE WHEN LEFT( :iv_input, 1 ) = '!'
THEN replace( iv_input ,'!' , '$')
WHEN :iv_input = '#'
THEN ''
ELSE :iv_input
END;
rv_output = replace_regexpr( '[[:cntrl:]]'
IN :rv_output WITH '' );
rv_output = replace( :rv_output, nchar( '0130'), '');
ENDMETHOD.
ENDCLASS.
Listing 8.15: Beispiel für eine skalare AMDP-Funktion
outtab = SELECT ...
LPAD( ROW_NUMBER() OVER (ORDER BY <Sortierung>), 56, '0')
AS "RECORD",
SQL__PROCEDURE__SOURCE__RECORD
FROM :intab ;
Listing 9.1: Vergabe einer laufenden Nummer für das Feld RECORD für die Sortierung
METHOD procedure BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
OUTTAB = SELECT * FROM :INTAB;
ERRORTAB = SELECT '' AS ERROR_TEXT,
'' AS SQL__PROCEDURE__SOURCE__RECORD
FROM dummy
WHERE dummy <> 'X';
ENDMETHOD.
Listing 9.2: Minimale Implementierung einer Endroutine
METHOD procedure BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING /bic/AZJB_TEST2.
OUTTAB = SELECT
...
FROM :INTAB AS T1
LEFT OUTER JOIN "/BIC/AZJB_TEST2" AS T2
ON
... ;
ERRORTAB = SELECT * FROM :ERRORTAB;
ENDMETHOD.
Listing 9.3: USING-Klausel in AMDP-Routinen
outtab = SELECT ...
COALESCE( pplant.plantcat, ' ' ) AS plantcat,
it.record ,
it.sql__procedure__source__record
FROM :intab as it
LEFT OUTER JOIN "/BI0/PPLANT" AS pplant
ON it.plant = pplant.plant
Listing 9.4: Absicherung gegen NULL-Werte mit COALESCE
METHOD GLOBAL_EXPERT BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
outtab = SELECT * FROM :outtab;
errortab = SELECT * FROM :errortab;
ENDMETHOD.
Listing 9.5: Dummy-Implementierung einer Expertenroutine
METHOD GLOBAL_EXPERT BY DATABASE PROCEDURE
FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
lt_tmp = SELECT *,
fiscyear || '001' AS fiscper,
amount_01 AS amount
FROM :intab
UNION ALL
SELECT *,
fiscyear || '002' AS fiscper,
amount_02 AS amount
FROM :intab
UNION ALL
<… Für alle Perioden 1-12 einen SELECT>
UNION ALL
SELECT *,
fiscyear || '012' AS fiscper,
amount_12 AS amount
FROM :intab ;
--Ausgangsprojektion, um die Felder in die richtige Reihenfolge
-- zu bringen und FISCYEAR und AMOUNT_XX-Felder zu entfernen.
outtab = SELECT COMPCODE,
ACCOUNT,
CURR,
FISCPER,
RECORDMODE,
AMOUNT,
RECORD,
SQL__PROCEDURE__SOURCE__RECORD
FROM :lt_tmp;
ENDMETHOD.
Listing 9.6: Expertenroutine zum Transponieren von Kennzahlen
errortab = SELECT 'Währung ist leer!' AS ERROR_TEXT,
SQL__PROCEDURE__SOURCE__RECORD
FROM :intab
WHERE currency = ' '
AND amount <> 0;
Listing 9.7: Einfache Fehlerermittlung aus den Eingangsdaten der Tabelle INTAB
outtab = SELECT it.plant,
pplant.plantcat
FROM :intab AS it
LEFT OUTER JOIN "/BI0/PPLANT" AS pplant
ON it.plant = pplant.plant;
ERRORTAB = SELECT 'Fehler' AS error_text,
SQL__PROCEDURE__SOURCE__RECORD
FROM :outtab
WHERE plant = ' ' --Fehler aus INTAB
OR plantcat IS NULL --Join erfolglos
Listing 9.8: Fehlerermittlung aus der OUTTAB
outtab = SELECT * FROM :intab;
errortab = SELECT 'Feldinhalt prüfen!' AS ERROR_TEXT,
SQL__PROCEDURE__SOURCE__RECORD
FROM :intab
WHERE text LIKE_REGEXPR '.*[[cntrl]].*'
OR text LIKE '!%'
OR text = '#';
Listing 9.9: Prüfung auf Steuerzeichen
SELECT id,
-- Name formatieren
nachname || ', ' || vorname AS name,
-- Symbol für das Geschlecht
CASE
WHEN geschlecht = 'F'
THEN NCHAR('9792')
WHEN geschlecht = 'M'
THEN NCHAR('9794')
ELSE ''
END AS MW,
-- Text für das Team hinzufügen
COALESCE((
SELECT team_text
FROM team_text
WHERE id = team
AND sprache = 'DE'
), (
SELECT team_text
FROM team_text
WHERE id = team
AND sprache = 'EN'
), '') AS team
FROM benutzer;
Listing 10.1: Beispiel für Komplexität in einfachen Abfragen
SELECT id,
udf_name_formatieren(vorname, nachname) AS name,
udf_symbol_fuer_geschlecht(geschlecht) AS mw,
(SELECT text FROM udf_team_text() WHERE id = team)
AS team
FROM benutzer ;
Listing 10.2: Die gleiche Abfrage mit ausgelagertem Coding
CREATE FUNCTION udf_name_formatieren(
iv_vorname NVARCHAR(20),
iv_nachname NVARCHAR(20)
)
RETURNS rv_name NVARCHAR(42)
AS BEGIN
rv_name = iv_nachname || ', ' || iv_vorname;
END;
CREATE FUNCTION udf_symbol_fuer_geschlecht(
iv_geschlecht NVARCHAR(1)
)
RETURNS rv_symbol NVARCHAR(1)
AS BEGIN
rv_symbol = CASE WHEN iv_geschlecht = 'F'
THEN NCHAR('9792')
WHEN iv_geschlecht = 'M'
THEN NCHAR('9794')
ELSE '' END;
END;
CREATE FUNCTION udf_team_text( )
RETURNS TABLE(id INT, text NVARCHAR(20))
AS BEGIN
lt_team_id = SELECT DISTINCT id FROM team_text;
RETURN
SELECT input.id,
COALESCE(de.team_text,
en.team_text,
'') AS text
FROM :lt_team_id AS input
LEFT OUTER JOIN team_text AS de
ON de.id = input.id
AND de.sprache = 'DE'
LEFT OUTER JOIN team_text AS en
ON en.id = input.id
AND en.sprache = 'EN';
END;
Listing 10.3: Die drei Funktionen, die in Listing 10.2 verwendet werden
--Deklaration einer Variable lv_a vom Typ Integer:
DECLARE lv_a INT;
--Zuweisung des Wertes 0 zu der Variable lv_a
lv_a = 0;
Listing 10.4: Redundante Kommentare sind ohne Mehrwert
-- Ursprüngliche SELECT-Anweisung
SELECT projekt,
projekte.titel,
SUM(aufgaben.plan_aufwand) AS plan_aufwand,
SUM(aufgaben.ist_aufwand) AS ist_aufwand,
ROUND(SUM(aufgaben.ist_aufwand)
/SUM(aufgaben.plan_aufwand)*100,0)
|| '%' AS perc,
projekte.aufwandsschaetzung
FROM aufgaben
LEFT OUTER JOIN projekte
ON projekte.id=aufgaben.projekt
WHERE aufgaben.status IN (SELECT id
FROM status
WHERE is_final = true)
GROUP BY projekt,
projekte.titel,
projekte.aufwandsschaetzung
ORDER BY ROUND(SUM(aufgaben.ist_aufwand)
/SUM(aufgaben.plan_aufwand)*100,0);
-- Zerlegt in Schritte:
DO BEGIN
-- Eingangsprojektion STATUS
lt_status_in =
SELECT id
FROM status
WHERE is_final = true;
-- Eingangsprojektion AUFGABEN
lt_aufgaben_in =
SELECT projekt,
plan_aufwand,
ist_aufwand
FROM aufgaben
WHERE status in (SELECT * FROM :lt_status_in);
-- Aggregation der Aufgaben auf Projekte
lt_aufgaben_aggr =
SELECT projekt,
SUM(plan_aufwand) AS plan_aufwand,
SUM(ist_aufwand) AS ist_aufwand
FROM :lt_aufgaben_in
GROUP BY projekt;
-- Nachberechnung AUFGABEN
lt_aufgaben_calc =
SELECT *,
ROUND(ist_aufwand/plan_aufwand*100) AS perc
FROM :lt_aufgaben_aggr;
-- Eingangsprojektion PROJEKTE
lt_projekte_in =
SELECT id,
titel,
aufwandsschaetzung
FROM projekte;
-- JOIN-Schritt
lt_projekte_aufgaben =
SELECT aufgaben.*,
projekte.*
FROM :lt_aufgaben_calc AS aufgaben
LEFT OUTER JOIN :lt_projekte_in AS projekte
ON aufgaben.projekt = projekte.id
ORDER BY aufgaben.perc;
-- Ausgangsprojektion
SELECT projekt,
titel,
plan_aufwand,
ist_aufwand,
perc || '%' AS perc,
aufwandsschaetzung
FROM :lt_projekte_aufgaben;
END;
Listing 10.5: Beispiel für das Zerlegen einer Abfrage in mehrere Schritte
SELECT *
FROM farben groessen;
SELECT *
FROM farben, groessen;
Listing 10.6: Beispiel für Kurzformen in SQLScript
SELECT *
FROM farben AS groessen;
SELECT *
FROM farben CROSS JOIN groessen;
Listing 10.7: Das gleiche Beispiel ohne Kurzform
SET 'ABAPVARCHARMODE' = 'FALSE';
SELECT 'Peter' || ' ' || 'Mueller' FROM dummy;
SET 'ABAPVARCHARMODE' = 'TRUE';
SELECT 'Peter' || ' ' || 'Mueller' FROM dummy;
Listing 10.8: Code zum Testen
SELECT 'Peter' || CHAR(32) || 'Mueller' FROM dummy;
--Datenbankobjekte löschen
DROP TABLE test_quadrat;
DROP FUNCTION udf_quadrat;
--Funktion anlegen
CREATE FUNCTION udf_quadrat(IN iv_wert INT)
RETURNS rv_ergebnis INT
AS BEGIN
rv_ergebnis = iv_wert * iv_wert;
END;
--Testfälle anlegen
CREATE TABLE test_quadrat(wert INT, quadrat INT);
INSERT INTO test_quadrat VALUES (0,0);
INSERT INTO test_quadrat VALUES (1,1);
INSERT INTO test_quadrat VALUES (2,4);
INSERT INTO test_quadrat VALUES (-1,1);
INSERT INTO test_quadrat VALUES (-2,4);
--Testfälle ausführen
SELECT wert,
quadrat AS erwartungswert,
udf_quadrat(wert) AS ergebnis,
CASE WHEN udf_quadrat(wert) = quadrat
THEN 'OK'
ELSE 'Fehler'
END AS testergebnis
FROM test_quadrat;
Listing 11.1: Entwicklungszyklus in einem Skript
CREATE TABLE mylog LIKE sys.sqlscript_logging_table_type;
CREATE PROCEDURE do_something (IN iv_text nvarchar(15) )
READS SQL DATA
AS BEGIN
USING sqlscript_logging AS logger;
logger:log(logger:level_debug,
'c1',
'Procedure parameter: ' || :iv_text );
END;
CREATE TABLE mylog LIKE sys.sqlscript_logging_table_type;
DO BEGIN
USING sqlscript_logging AS logger;
logger:create_configuration('c1');
logger:add_sqlscript_object('c1',
current_schema,
'DO_SOMETHING');
logger:set_output_table('c1',
current_schema,
'MYLOG');
logger:set_level('c1', 'debug');
logger:start_logging('c1');
do_something( 'Hello Log!' );
END;
SELECT * FROM mylog;
Listing 11.2: Beispiel für die Nutzung der Logging-Bibliothek
CREATE LIBRARY test_demo_dm
LANGUAGE SQLSCRIPT TEST
AS BEGIN
@test()
PUBLIC PROCEDURE t_aufgaben_cnt
AS BEGIN
USING sqlscript_test AS test;
test:expect_eq((SELECT COUNT(*) FROM aufgaben),
1000 );
END;
END;
Listing 11.3: Testbibliothek
CALL SQLSCRIPT_RUN_TESTS_ON_ORIGINAL_DATA('{"schema":"SYSTEM",
"library":"TEST_DEMO_DM"}', ?, ?, ?);
CREATE LIBRARY TEST_DEMO_DM
LANGUAGE SQLSCRIPT TEST
AS BEGIN
PRIVATE PROCEDURE check_table_count
(IN iv_tabname NVARCHAR(30),
IN iv_expected_count INTEGER)
AS BEGIN
USING sqlscript_test AS test;
DECLARE lv_query NVARCHAR(5000);
DECLARE lv_count INTEGER;
lv_query = 'SELECT COUNT(*) FROM ' || :iv_tabname;
EXEC lv_query INTO lv_count;
test:expect_eq( :lv_count, :iv_expected_count );
END;
@test()
PUBLIC PROCEDURE t_table_count
AS BEGIN
check_table_count( 'AUFGABEN', 1000);
check_table_count( 'BENUTZER', 30);
check_table_count( 'STATUS', 6);
check_table_count( 'STATUS_TEXT', 12);
--check_table_count( 'STATUS_TEXT', 13);
END;
END;
Listing 11.4: Auslagern der Tests in eine private Prozedur
DROP PROCEDURE A;DROP PROCEDURE B;
CREATE PROCEDURE A (IN iv_id INT, OUT ot_benutzer benutzer)
AS BEGIN
ot_benutzer = SELECT *
FROM benutzer
WHERE id >= :iv_id
AND id <= :iv_id + 5;
END;
CREATE PROCEDURE B (OUT ot_benutzer benutzer)
AS BEGIN
CALL A(iv_id=>'1', ot_benutzer=>lt_tmp1);
CALL A(iv_id=>'20', ot_benutzer=>lt_tmp20);
ot_benutzer = SELECT * FROM :lt_tmp1
UNION
SELECT * FROM :lt_tmp20;
END;
CALL B(?);
Listing 11.5: Zwei Prozeduren zum Debuggen
SELECT a.id AS aufgabe,
a.titel,
b.vorname,
b.nachname,
b.email,
t.team_text
FROM aufgaben AS a
LEFT OUTER JOIN benutzer AS b
ON a.bearbeiter = b.id
LEFT OUTER JOIN team_text AS t
ON b.team = t.id;
Listing 11.6: Einfache SQL-Abfrage für die Performanceanalyse
EXPLAIN PLAN FOR
SELECT a.id AS aufgabe,
a.titel,
b.vorname,
b.nachname,
b.email,
t.team_text
FROM aufgaben AS a
LEFT OUTER JOIN benutzer AS b
ON a.bearbeiter = b.id
LEFT OUTER JOIN team_text AS t
ON b.team = t.id;
SELECT * FROM explain_plan_table;
DELETE FROM explain_plan_table;
Listing 11.7: Beispiel für die Verwendung der Anweisung EXPLAIN PLAN
SELECT * FROM sys.sqlscript_analyzer_rules;
DO BEGIN
lt_rules =
SELECT rule_namespace,
rule_name,
category
FROM sqlscript_analyzer_rules;
lt_objects =
SELECT schema_name ,
procedure_name AS object_name,
definition
FROM procedures
WHERE schema_name LIKE 'SHINE%';
CALL analyze_sqlscript_objects( :lt_objects,
:lt_rules,
lt_result_objects,
lt_result_findings );
SELECT objects.*,
findings.*
FROM :lt_result_objects AS objects
INNER JOIN :lt_result_findings AS findings
ON objects.object_definition_id
= findings.object_definition_id;
END;
Listing 11.8: Aufruf des SQLScript Code Analyzers für bestehende Prozeduren
DO BEGIN
lt_rules =
SELECT rule_namespace,
rule_name,
category
FROM sqlscript_analyzer_rules;
CALL analyze_sqlscript_definition(
'
CREATE procedure check_me(IN iv_value INT,
OUT ov_value INT)
AS BEGIN
DECLARE lv_unused VARCHAR(3) default '' '';
ov_value = iv_value;
END;
',
:lt_rules,
lt_result_findings );
SELECT * FROM :lt_result_findings;
END;
Listing 11.9: Aufruf des SQLScript Code Analyzers für eine neu anzulegende Prozedur
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment