Skip to content

Instantly share code, notes, and snippets.

@captainabap

captainabap/Listing 1.1.sql

Created June 22, 2020 08:11
Show Gist options
  • Save captainabap/b93d6e2ea32e8533a61d2daba518a104 to your computer and use it in GitHub Desktop.
Save captainabap/b93d6e2ea32e8533a61d2daba518a104 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Listing 1.1.sql
-- Listing 1.1.sql
-- Meldung der SQL-Konsole beim Ausführen einer Anweisung
SELECT * FROM m_cs_tables
Raw
Listing 1.2.sql
-- Listing 1.2.sql
-- Beispiel für mehrere Anweisungen in der SQL-Konsole
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;
Raw
Listing 10.1.sql
-- Listing 10.1.sql
-- Beispiel für Komplexität in einfachen Abfragen
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;
Raw
Listing 10.2.sql
-- Listing 10.2.sql
-- Die gleiche Abfrage mit ausgelagertem Coding
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 ;
Raw
Listing 10.3.sql
-- Listing 10.3.sql
-- Die drei Funktionen, die in Listing 10.2 verwendet werden
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;
Raw
Listing 10.4.sql
-- Listing 10.4.sql
-- Redundante Kommentare sind ohne Mehrwert
--Deklaration einer Variable lv_a vom Typ Integer:
DECLARE lv_a INT;
--Zuweisung des Wertes 0 zu der Variable lv_a
lv_a = 0;
Raw
Listing 10.5.sql
-- Listing 10.5.sql
-- Beispiel für das Zerlegen einer Abfrage in mehrere Schritte
-- 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;
Raw
Listing 10.6.sql
-- Listing 10.6.sql
-- Beispiel für Kurzformen in SQLScript
SELECT *
FROM farben groessen;
SELECT *
FROM farben, groessen;
Raw
Listing 10.7.sql
-- Listing 10.7.sql
-- Das gleiche Beispiel ohne Kurzform
SELECT *
FROM farben AS groessen;
SELECT *
FROM farben CROSS JOIN groessen;
Raw
Listing 10.8.sql
-- Listing 10.8.sql
-- Code zum Testen
SET 'ABAPVARCHARMODE' = 'FALSE';
SELECT 'Peter' || ' ' || 'Mueller' FROM dummy;
SET 'ABAPVARCHARMODE' = 'TRUE';
SELECT 'Peter' || ' ' || 'Mueller' FROM dummy;
Raw
Listing 11.1.sql
-- Listing 11.1.sql
-- Entwicklungszyklus in einem Skript
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;
Raw
Listing 11.2.sql
-- Listing 11.2.sql
-- Beispiel für die Nutzung der Logging-Bibliothek
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;
Raw
Listing 11.3.sql
-- Listing 11.3.sql
-- Testbibliothek
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;
Raw
Listing 11.4.sql
-- Listing 11.4.sql
-- Auslagern der Tests in eine private Prozedur
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;
Raw
Listing 11.5.sql
-- Listing 11.5.sql
-- Zwei Prozeduren zum Debuggen
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(?);
Raw
Listing 11.6.sql
-- Listing 11.6.sql
-- Einfache SQL-Abfrage für die Performanceanalyse
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;
Raw
Listing 11.7.sql
-- Listing 11.7.sql
-- Beispiel für die Verwendung der Anweisung EXPLAIN PLAN
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;
Raw
Listing 11.8.sql
-- Listing 11.8.sql
-- Aufruf des SQLScript Code Analyzers für bestehende Prozeduren
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;
Raw
Listing 11.9.sql
-- Listing 11.9.sql
-- Aufruf des SQLScript Code Analyzers für eine neu anzulegende Prozedur
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;
Raw
Listing 2.1.sql
-- Listing 2.1.sql
-- IF-Anweisung enthält eine INSERT-Anweisung
1 IF lv_counter > 0
2 THEN
3 INSERT INTO farben VALUES ('Violett');
4 END IF;
Raw
Listing 2.10.sql
-- Listing 2.10.sql
-- Beispiel für die Verwendung der SQL-Funktion COALESCE
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
Raw
Listing 2.11.sql
-- Listing 2.11.sql
-- Die Tabelle DUMMY zum Testen von Ausdrücken
-- 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;
Raw
Listing 2.12.sql
-- Listing 2.12.sql
-- Erzeugen einer leeren Tabelle mit Hilfe von DUMMY
errorTab = SELECT '' AS ERROR_TEXT,
'' AS SQL__PROCEDURE__SOURCE__RECORD
FROM dummy
WHERE dummy = 'Y';
Raw
Listing 2.13.sql
-- Listing 2.13.sql
-- SELECT-Abfrage mit UDFs
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;
Raw
Listing 2.14.sql
-- Listing 2.14.sql
-- Beispiele für unterschiedliche Blöcke
--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;
Raw
Listing 2.15.sql
-- Listing 2.15.sql
-- Syntax von Blöcken
BEGIN
[<Deklarationen>]
[<Ausnahmebehandler>]
[<Anweisungsliste>]
END;
Raw
Listing 2.16.sql
-- Listing 2.16.sql
-- Syntax von anonymen Blöcken
DO [(<Parameter>)]
BEGIN
[<Deklarationen>]
[<Ausnahmebehandler>]
[<Anweisungsliste>]
END;
Raw
Listing 2.17.sql
-- Listing 2.17.sql
-- Anonymer Block mit Parametern
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;
Raw
Listing 2.18.sql
-- Listing 2.18.sql
-- Bestandteile der Anweisung CREATE PROCEDURE
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
Raw
Listing 2.19.sql
-- Listing 2.19.sql
-- Pflichtteil der Anweisung CREATE PROCEDURE
CREATE PROCEDURE <Prozedurname>
AS
BEGIN
<Quellcode>
END;
Raw
Listing 2.2.sql
-- Listing 2.2.sql
-- Beispiel für unterschiedliche Formatierung
SELECT col1,col2 FROM T1;
SELECT col1,
col2
FROM T1 ;
Raw
Listing 2.20.sql
-- Listing 2.20.sql
-- Beispiel für das Anlegen, Ausführen und Löschen einer einfachen Prozedur
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;
Raw
Listing 2.21.sql
-- Listing 2.21.sql
-- Definition einer Prozedur mit Standardwerten für die Parameter
--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;
Raw
Listing 2.22.sql
-- Listing 2.22.sql
-- Beispiel für eine Prozedur mit dem Datentyp ANY TABLE
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;
Raw
Listing 2.23.sql
-- Listing 2.23.sql
-- Interner Prozeduraufruf mit Angabe des Schemas
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;
Raw
Listing 2.24.sql
-- Listing 2.24.sql
-- Interner Prozeduraufruf ohne CALL
DO BEGIN
statustexte(iv_sprache=>'DE',
et_result=> lt_statustexte) ;
END;
Raw
Listing 2.25.sql
-- Listing 2.25.sql
-- Deklaration und Initialisierung einer neuen Tabellenvariable beim Prozeduraufruf
DO BEGIN
statustexte(iv_sprache=>'DE',
et_result=>lt_statustexte) ;
SELECT * FROM :lt_statustexte;
END;
Raw
Listing 2.26.sql
-- Listing 2.26.sql
-- Prozeduraufruf über die Position
DO BEGIN
statustexte('DE',lt_statustexte) ;
SELECT * FROM :lt_statustexte;
END;
Raw
Listing 2.27.sql
-- Listing 2.27.sql
-- Parametrisierung aus der SQL-Konsole
--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=>?);
Raw
Listing 2.28.sql
-- Listing 2.28.sql
-- Rekursive Berechnung von Fibonacci-Zahlen
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, ?);
Raw
Listing 2.29.sql
-- Listing 2.29.sql
-- Syntax der CREATE FUNCTION-Anweisung
CREATE FUNCTION <Funktionsname>
[(<IN-Parameterliste>)]
RETURNS <OUT-Parameterdefinition>
[LANGUAGE SQLSCRIPT]
[SQL SECURITY {DEFINER|INVOKER} ]
[DEFAULT SCHEMA Defaultschema]
[DETERMINISTIC]
AS
BEGIN
<Quellcode>
END
Raw
Listing 2.3.sql
-- Listing 2.3.sql
-- Beispiele für Literale im Quelltext
/* 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;
Raw
Listing 2.30.sql
-- Listing 2.30.sql
-- Beispiel für eine skalare UDF
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;
Raw
Listing 2.31.sql
-- Listing 2.31.sql
-- Beispiel für eine Tabellen-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;
Raw
Listing 2.32.sql
-- Listing 2.32.sql
-- Zuweisung von UDF-Ergebnissen an lokale Variablen
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;
Raw
Listing 2.33.sql
-- Listing 2.33.sql
-- UDF mit mehreren Rückgabewerten
CREATE FUNCTION jetzt
RETURNS uhrzeit TIME,
datum DATE
AS BEGIN
datum = CURRENT_DATE;
uhrzeit = CURRENT_TIME;
END;
SELECT jetzt().datum,
jetzt().uhrzeit
FROM dummy;
Raw
Listing 2.34.sql
-- Listing 2.34.sql
-- Gleichzeitige Zuweisung aller Rückgabewerte einer UDF
DO BEGIN
DECLARE lv_uhrzeit TIME;
DECLARE lv_datum DATE;
(lv_uhrzeit, lv_datum) = jetzt();
SELECT lv_datum, lv_uhrzeit FROM dummy;
END;
Raw
Listing 2.35.sql
-- Listing 2.35.sql
-- Zugriff auf die Komponenten einer UDL
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;
Raw
Listing 2.36.sql
-- Listing 2.36.sql
-- Definition einer UDL mit Konstanten
CREATE LIBRARY const
AS BEGIN
PUBLIC VARIABLE cv_max CONSTANT INTEGER DEFAULT 10;
PUBLIC VARIABLE cv_min CONSTANT INTEGER DEFAULT 3;
END;
Raw
Listing 2.37.sql
-- Listing 2.37.sql
-- Nutzung der Konstanten aus dem Listing 2.36
SELECT *
FROM aufgaben
WHERE id < const:cv_max
AND id > const:cv_min;
Raw
Listing 2.38.sql
-- Listing 2.38.sql
-- 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
--Ermitteln der Größenkategorie
--Ermitteln des Gurtmass
--Ermitteln der Preisklasse
--Ableiten des Preises anhand der Preisklasse
END;
Raw
Listing 2.39.sql
-- Listing 2.39.sql
-- Ermittlung des Versandpreis per UDF
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;
Raw
Listing 2.4.sql
-- Listing 2.4.sql
-- Beispiel für die einfache Notation
SELECT id,
status,
titel
FROM aufgaben;
Raw
Listing 2.40.sql
-- Listing 2.40.sql
-- Ermittlung der Größenkategorie für die Pakete
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;
Raw
Listing 2.41.sql
-- Listing 2.41.sql
-- Testfälle für die Funktion UDF_GROESSENKATEGORIE
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;
Raw
Listing 2.42.sql
-- Listing 2.42.sql
-- Funktion für die Berechnung des Gurtmaßes
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;
Raw
Listing 2.43.sql
-- Listing 2.43.sql
-- Ableitung der Preisklasse und der Meldungen
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;
Raw
Listing 2.44.sql
-- Listing 2.44.sql
-- Preisermittlung anhand der Preisklasse und dem Online-Flag
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;
Raw
Listing 2.45.sql
-- Listing 2.45.sql
-- Tabelle für Testdaten
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)
);
Raw
Listing 2.46.sql
-- Listing 2.46.sql
-- Testdaten für die Versandpreise
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);
Raw
Listing 2.47.sql
-- Listing 2.47.sql
-- Ausführung des Tests
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
Raw
Listing 2.5.sql
-- Listing 2.5.sql
-- Zugriff auf lokale Felder und Parameter
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);
Raw
Listing 2.6.sql
-- Listing 2.6.sql
-- Beispiel für Klammerung von Operatoren
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";
Raw
Listing 2.7.sql
-- Listing 2.7.sql
-- Ausdrücke in Feldlisten
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;
Raw
Listing 2.8.sql
-- Listing 2.8.sql
-- Unterschiedliche Typisierung von Tabellen
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;
Raw
Listing 2.9.sql
-- Listing 2.9.sql
-- Selektion auf eine Spalte mit NULL-Werten
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
Raw
Listing 3.1.sql
-- Listing 3.1.sql
-- Implizite Typisierung von Tabellenvariablen
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;
Raw
Listing 3.10.sql
-- Listing 3.10.sql
-- Syntax des komplexen CASE-Ausdrucks
CASE WHEN <Bedingung1> THEN <Ergebnis1>
[WHEN <Bedingung2> THEN <Ergebnis2>
...]
[ELSE <Ergebnis>]
END
Raw
Listing 3.11.sql
-- Listing 3.11.sql
-- Komplexe CASE-Ausdrücke zum Prüfen von unterschiedlichen Bedingungen
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;
Raw
Listing 3.12.sql
-- Listing 3.12.sql
-- CASE als Ersatz für eine IF-Bedingung
CASE WHEN wert2<>0 THEN wert1/wert2
ELSE 0
END AS division
Raw
Listing 3.13.sql
-- Listing 3.13.sql
-- Aufruf einer skalaren UDF in der Feldliste
SELECT get_parcel_price(width, height, depth, weight )
FROM parcels;
Raw
Listing 3.14.sql
-- Listing 3.14.sql
-- Beispiel für die Verwendung von konstanten Werten und Aggregatfunktionen
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;
Raw
Listing 3.15.sql
-- Listing 3.15.sql
-- Berechnung des Aufwands für ein Projekt
SELECT SUM(ist_aufwand)
FROM aufgaben
WHERE projekt = 1;
Raw
Listing 3.16.sql
-- Listing 3.16.sql
-- Berechnung des Aufwands für alle Projekte
SELECT projekt,
SUM(ist_aufwand)
FROM aufgaben
GROUP BY projekt;
Raw
Listing 3.17.sql
-- Listing 3.17.sql
-- Aggregation mit Gruppierung über zwei Spalten
SELECT projekt,
bearbeiter,
SUM(ist_aufwand)
FROM aufgaben
GROUP BY projekt,
bearbeiter
ORDER BY projekt,
bearbeiter;
Raw
Listing 3.18.sql
-- Listing 3.18.sql
-- Verkettung der Teamnamen mit der Aggregatfunktion STRING_AGG
SELECT sprache,
STRING_AGG(team_text, ', ')
FROM team_text
GROUP BY sprache;
Raw
Listing 3.19.sql
-- Listing 3.19.sql
-- Erweiterung des Beispiels um die Anzahl der Benutzer
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'
Raw
Listing 3.2.sql
-- Listing 3.2.sql
-- Syntax der Deklaration von Tabellenvariablen
DECLARE <Variablenname>
[CONSTANT]
TABLE( <Spaltenname> <Typ> [,...] )|<Tabellentyp>
[{ DEFAULT | = } <Initialwert> ]
Raw
Listing 3.20.sql
-- Listing 3.20.sql
-- Skalare Unterabfragen zur Ermittlung eines Feldes
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;
Raw
Listing 3.21.sql
-- Listing 3.21.sql
-- Vergleich zwischen COUNT als Aggregatausdruck und als Window Function
--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;
Raw
Listing 3.22.sql
-- Listing 3.22.sql
-- Syntax für Window Function
<Window Function> OVER (
[PARTITION <Gruppierung>]
[ORDER BY <Sortierung>]
[ROWS <Ausschnitt>] )
Raw
Listing 3.23.sql
-- Listing 3.23.sql
-- ORDER BY-Klausel in Window Functions
SELECT id,
status,
COUNT(*) OVER (PARTITION BY status ORDER BY id) AS zaehler
FROM aufgaben
ORDER BY status;
Raw
Listing 3.24.sql
-- Listing 3.24.sql
-- Beispiel mit der Window Function LEAD
...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;
Raw
Listing 3.25.sql
-- Listing 3.25.sql
-- Eingebettete Funktion in einer SQL-Abfrage
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, ?);
Raw
Listing 3.26.sql
-- Listing 3.26.sql
-- Syntax des Cross Join
SELECT ...
FROM <Tabellenausdruck1> CROSS JOIN <Tabellenausdruck2>
SELECT ...
FROM <Tabellenausdruck1> , <Tabellenausdruck2>
Raw
Listing 3.27.sql
-- Listing 3.27.sql
-- Cross Join über Farben und Größen
--Kreuzprodukt mit CROSS JOIN
SELECT *
FROM farben
CROSS JOIN groessen;
--Kreuzprodukt nur mit Komma in der FROM-Klausel
SELECT *
FROM farben,
groessen;
Raw
Listing 3.28.sql
-- Listing 3.28.sql
-- Syntax des INNER JOIN
SELECT ...
FROM <Tabellenausdruck1>
[INNER] JOIN <Tabellenausdruck2>
ON <Join-Bedingung>
Raw
Listing 3.29.sql
-- Listing 3.29.sql
-- Beispiel für einen 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;
Raw
Listing 3.3.sql
-- Listing 3.3.sql
-- Syntax der SELECT-Abfrage
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>]
Raw
Listing 3.30.sql
-- Listing 3.30.sql
-- Beispiel für die Position von Bedingungen
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';
Raw
Listing 3.31.sql
-- Listing 3.31.sql
-- Beispiel für einen Lateral Join
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 )
Raw
Listing 3.32.sql
-- Listing 3.32.sql
-- Beispiele für die Verwendung von Vergleichsprädikaten
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 );
Raw
Listing 3.33.sql
-- Listing 3.33.sql
-- Verwendung von NOT LIKE
--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%';
Raw
Listing 3.34.sql
-- Listing 3.34.sql
-- Vergleich mit LIKE und ESCAPE
SELECT table_name
FROM tables
WHERE table_name LIKE 'P$_%' ESCAPE '$'
Raw
Listing 3.35.sql
-- Listing 3.35.sql
-- Vergleich mit BETWEEN
--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;
Raw
Listing 3.36.sql
-- Listing 3.36.sql
-- Beispiele für die Nutzung des IN-Prädikat
--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 );
Raw
Listing 3.37.sql
-- Listing 3.37.sql
-- Beispiele für das EXISTS-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 );
Raw
Listing 3.38.sql
-- Listing 3.38.sql
-- CASE-Ausdruck in einer WHERE-Klausel
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;
Raw
Listing 3.39.sql
-- Listing 3.39.sql
-- Prüfung auf NULL
--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!
Raw
Listing 3.4.sql
-- Listing 3.4.sql
-- Beispiel für die Verwendung von Aliasnamen für Spalten
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";
Raw
Listing 3.40.sql
-- Listing 3.40.sql
-- Unscharfe Suche mit dem CONTAINS-Prädikat
--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) );
Raw
Listing 3.41.sql
-- Listing 3.41.sql
-- SELECT-Anweisung mit WITH-Klausel
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;
Raw
Listing 3.42.sql
-- Listing 3.42.sql
-- Verwendung von Tabellenvariablen statt der 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;
Raw
Listing 3.43.sql
-- Listing 3.43.sql
-- Beispiel für einen CASE-Ausdruck in der GROUP BY-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;
Raw
Listing 3.44.sql
-- Listing 3.44.sql
-- Trennung von CASE und GROUP_BY
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
Raw
Listing 3.45.sql
-- Listing 3.45.sql
-- Verwendung von GROUPING SETS für die Bildung von Summenzeilen
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;
Raw
Listing 3.46.sql
-- Listing 3.46.sql
-- Aggregation mit den Klauseln GROUP BY und HAVING
SELECT ... GROUP BY ... HAVING <Bedingung>
SELECT bearbeiter,
AVG(plan_aufwand)
FROM aufgaben
GROUP BY bearbeiter
HAVING count(*) > 5;
Raw
Listing 3.47.sql
-- Listing 3.47.sql
-- Sortierung mit ORDER BY
--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;
Raw
Listing 3.48.sql
-- Listing 3.48.sql
-- Verwendung von UNION und UNION ALL
<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;
Raw
Listing 3.49.sql
-- Listing 3.49.sql
-- Beispiel für die Verwendung von INTERSECT
--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;
Raw
Listing 3.5.sql
-- Listing 3.5.sql
-- Beispiel für die Verwendung des Korrelationsnamens in der Feldliste
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;
Raw
Listing 3.50.sql
-- Listing 3.50.sql
-- Subtraktion von Mengen mit EXCEPT und NOT EXISTS
--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 );
Raw
Listing 3.51.sql
-- Listing 3.51.sql
-- Beispiele für Unterabfragen
--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;
Raw
Listing 3.52.sql
-- Listing 3.52.sql
-- Verwendung von Spalten- und Tabellenalias
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;
Raw
Listing 3.53.sql
-- Listing 3.53.sql
-- Verwendung des MAP_MERGE-Operators
"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;
Raw
Listing 3.6.sql
-- Listing 3.6.sql
-- Beispiel für *-Sternchen in Feldlisten
SELECT t1.* ,
t2.spalte1
FROM tabelle1 AS t1
JOIN tabelle2 AS t2
ON t1.key = t2.key;
Raw
Listing 3.7.sql
-- Listing 3.7.sql
-- Operatorausdrücke in der Feldliste der SELECT-Anweisung
SELECT id || ' - ' || titel AS Aufgabe,
plan_aufwand,
ist_aufwand,
round(ist_aufwand / plan_aufwand * 100, 0)
|| ' %' AS zeitverbrauch,
fertigstellung || ' %' AS fertigstellung
FROM aufgaben;
Raw
Listing 3.8.sql
-- Listing 3.8.sql
-- Syntax des einfachen CASE-Ausdrucks
CASE <Ausdruck>
WHEN <Ausdruck1> THEN <Ergebnis1>
[WHEN <Ausdruck2> THEN <Ergebnis2>]
(...)
[ELSE <Ergebnis>]
END;
Raw
Listing 3.9.sql
-- Listing 3.9.sql
-- Beispiel für einen einfachen CASE-Ausdruck
CASE abteilung
WHEN 'IT' THEN 'EDV Abteilung'
WHEN 'MA' THEN 'Marketing'
WHEN 'VT' THEN 'Vertrieb'
ELSE abteilung
END AS "Abteilung"
Raw
Listing 4.1.sql
-- Listing 4.1.sql
-- Beispiel für das Verhalten des Datentyps VARCHAR
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;
Raw
Listing 4.10.sql
-- Listing 4.10.sql
-- Beispiel für die Extraktion von E-Mail-Adressen aus einer Log-Tabelle
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;
Raw
Listing 4.11.sql
-- Listing 4.11.sql
-- Syntax der SQL-Funktion LOCATE_REGEXPR()
LOCATE_REGEXPR(
[START|AFTER]
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[FROM <Start>]
[OCCURENCE <N. auftreten>]
[GROUP <Gruppe>] )
Raw
Listing 4.12.sql
-- Listing 4.12.sql
-- Syntax der SQL-Funktion OCCURRENCES_REGEXPR()
OCCURRENCES_REGEXPR(
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[FROM <Startposition>] )
Raw
Listing 4.13.sql
-- Listing 4.13.sql
-- Syntax der SQL-Funktion REPLACE_REGEXPR()
REPLACE_REGEXPR(
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[WITH <Ersetzung>]
[FROM <Start>]
[OCCURENCE <N. auftreten>] )
Raw
Listing 4.14.sql
-- Listing 4.14.sql
-- Beispiel für Suchen und Ersetzen mit regulären Ausdrücken
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;
Raw
Listing 4.15.sql
-- Listing 4.15.sql
-- Beispiel für die PAD-Funktionen
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;
Raw
Listing 4.16.sql
-- Listing 4.16.sql
-- Beispiel für die TRIM-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;
Raw
Listing 4.18.sql
-- Listing 4.18.sql
-- Beispiel für die SQL-Funktion HAMMING_DISTANCE()
SELECT *
FROM benutzer
WHERE HAMMING_DISTANCE(vorname, 'Mandy') BETWEEN 0 AND 1;
Raw
Listing 4.19.sql
-- Listing 4.19.sql
-- Suche mit regulären Ausdrücken
SELECT *
FROM benutzer
WHERE nachname LIKE_REGEXPR 'M(a|e)(i|y)(a|er)';
Raw
Listing 4.2.sql
-- Listing 4.2.sql
-- Beispiel für das Verhalten des Datentyps ALPHANUM
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;
Raw
Listing 4.20.sql
-- Listing 4.20.sql
-- Konvertierung von Zeichen in ASCII bzw. Unicode und umgekehrt
SELECT ASCII('A') AS char2ascii,
CHAR(65) AS asci2char,
UNICODE(' ') AS nchar2unicode,
NCHAR('30908') AS unicode2nchar
FROM dummy;
Raw
Listing 4.21.sql
-- Listing 4.21.sql
-- Beispiele für die SPLIT-Funktion
<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;
Raw
Listing 4.22.sql
-- Listing 4.22.sql
-- Beispiel für die Funktion SPLIT_REGEXPR
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;
Raw
Listing 4.23.sql
-- Listing 4.23.sql
-- Beispiel für die Tabellenfunktion SPLIT_REGEXPR_TO_TABLE
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;
Raw
Listing 4.24.sql
-- Listing 4.24.sql
-- Zeichenkettenformatierung mit FORMAT
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;
Raw
Listing 4.25.sql
-- Listing 4.25.sql
-- Formatierung einer Tabelle mit FORMAT_TO_TABLE
DO BEGIN
USING sqlscript_string AS string_lib;
SELECT *
FROM string_lib:format_to_table(
'Aufgabe Nr. {id} - {titel:.10}',
aufgaben);
END
Raw
Listing 4.26.sql
-- Listing 4.26.sql
-- Beispiel für die Verwendung der Funktion TABLE_SUMMARY
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;
Raw
Listing 4.27.sql
-- Listing 4.27.sql
-- Ergebnis des Aufrufs von Listing 4.26
Raw
Listing 4.28.sql
-- Listing 4.28.sql
-- Ausgabe eines Datums als Datentyp DATE und VARCHAR
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;
Raw
Listing 4.29.sql
-- Listing 4.29.sql
-- Erzeugung und Darstellung des leeren Datums
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;
Raw
Listing 4.3.sql
-- Listing 4.3.sql
-- Ermittlung der Länge von Zeichenketten
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;
Raw
Listing 4.30.sql
-- Listing 4.30.sql
-- Verschiedene Datumsformate
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;
Raw
Listing 4.31.sql
-- Listing 4.31.sql
-- Beispiel für die Formatierung von Datumsfeldern
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 ;
Raw
Listing 4.33.sql
-- Listing 4.33.sql
-- Konvertierung von Zeit nach Zeichenkette
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 ;
Raw
Listing 4.34.sql
-- Listing 4.34.sql
-- Unterschiede zwischen den SQL-Funktionen ADD_MONTHS() und ADD_MONTHS_LAST()
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
Raw
Listing 4.35.sql
-- Listing 4.35.sql
-- Zerlegung eines Datums oder einer Zeit in ihre Komponenten
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;
Raw
Listing 4.36.sql
-- Listing 4.36.sql
-- Bestimmung der Kalenderwoche
SELECT
WEEK('2017-12-08'), -->50
ISOWEEK('2017-12-08') -->2017-W49
FROM dummy;
Raw
Listing 4.37.sql
-- Listing 4.37.sql
-- Ermittlung der Zeitzone eines SAP-HANA-Systems
SELECT * FROM timezones;
SELECT *
FROM m_host_information
WHERE key LIKE '%timezone%';
Raw
Listing 4.38.sql
-- Listing 4.38.sql
-- Berechnung der Maschinenlaufzeit in Stunden
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;
Raw
Listing 4.39.sql
-- Listing 4.39.sql
-- Beispiel für die Selektion der vier letzten abgeschlossenen Wochen
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;
Raw
Listing 4.4.sql
-- Listing 4.4.sql
-- Test der Verkettung 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;
Raw
Listing 4.40.sql
-- Listing 4.40.sql
-- Auslagerung der Datumsberechnung in eine Funktion
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));
Raw
Listing 4.41.sql
-- Listing 4.41.sql
-- Vergleich von dezimaler und binärer Gleitkommazahl
SELECT TO_DOUBLE(1.2)-TO_DOUBLE(0.1) FROM dummy;
SELECT TO_SMALLDECIMAL(1.2)-TO_SMALLDECIMAL(0.1) FROM dummy;
Raw
Listing 4.42.sql
-- Listing 4.42.sql
-- Grundrechenarten in SQL
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;
Raw
Listing 4.43.sql
-- Listing 4.43.sql
-- Beispiele für die Rundungsfunktionen
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;
Raw
Listing 4.44.sql
-- Listing 4.44.sql
-- Beispiel für die Mengenumrechnung in eine feste Einheit
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;
Raw
Listing 4.45.sql
-- Listing 4.45.sql
-- Beispiel für eine einfache Währungsumrechnung
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;
Raw
Listing 4.46.sql
-- Listing 4.46.sql
-- Konvertierung von ASCII-Dezimal nach VARBINARY
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;
Raw
Listing 4.47.sql
-- Listing 4.47.sql
-- Funktion zur Darstellung von Ganzzahlen als Bits
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;
Raw
Listing 4.48.sql
-- Listing 4.48.sql
-- Beispiel für die bitweise Verarbeitung von Binärdaten
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;
Raw
Listing 4.49.sql
-- Listing 4.49.sql
-- Zeitreihe mit den Monaten des Jahres 2017
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');
Raw
Listing 4.5.sql
-- Listing 4.5.sql
-- Funktionen für Groß- und Kleinschreibung
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;
Raw
Listing 4.50.sql
-- Listing 4.50.sql
-- Zerlegung von Wörtern in Buchstaben mit Hilfe der SERIES-Funktionen
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;
Raw
Listing 4.6.sql
-- Listing 4.6.sql
-- Zerlegung von Zeichenketten mit Positionsangaben
SELECT LEFT('ABCDEFGHI',3) AS links, -->'ABC'
SUBSTRING('ABCDEFGHI',4,3) AS mitte, -->'DEF'
RIGHT('ABCDEFGHI',3) AS rechts -->'GHI'
FROM
dummy;
Raw
Listing 4.7.sql
-- Listing 4.7.sql
-- Zeichenkettenzerlegung mit SUBSTR_BEFORE und SUBSTR_AFTER
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;
Raw
Listing 4.8.sql
-- Listing 4.8.sql
-- SQL-Funktion SUBSTR_REGEXPR()
SUBSTR_REGEXPR(
<Muster>
[FLAG <flag>]
IN <Zeichenkette>
[FROM <Start>]
[OCCURENCE <N. auftreten>]
[GROUP <Gruppe>])
Raw
Listing 4.9.sql
-- Listing 4.9.sql
-- Einfache Zeichenkettenzerlegung mit regulären Ausdrücken
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;
Raw
Listing 5.1.sql
-- Listing 5.1.sql
-- Tabellen für die Beispiele dieses Kapitels
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)
);
Raw
Listing 5.10.sql
-- Listing 5.10.sql
-- Syntax der Anweisung MERGE INTO
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>)];
Raw
Listing 5.11.sql
-- Listing 5.11.sql
-- Beispiel für die 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;
Raw
Listing 5.12.sql
-- Listing 5.12.sql
-- MERGE INTO mit lokaler Tabellenvariable
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;
Raw
Listing 5.2.sql
-- Listing 5.2.sql
-- Beispiel für das Einfügen mit Spaltenreihenfolge
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);
Raw
Listing 5.3.sql
-- Listing 5.3.sql
-- Beispiel für eine INSERT-Anweisung mit mehreren Datensätzen
INSERT INTO <Tabellenname> <Abfrage>;
INSERT INTO tabelle_2
SELECT key1 + key2 * 100,
wert1,
'Einfügen aus einer Abfrage'
FROM tabelle_1;
Raw
Listing 5.4.sql
-- Listing 5.4.sql
-- Syntax der INSERT-Anweisung für mehrere Datensätze mit Spaltenreihenfolge
INSERT INTO <Tabellenname>
<Spaltenreihenfolge>
<Abfrage>;
Raw
Listing 5.5.sql
-- Listing 5.5.sql
-- INSERT mit Abfrage auf eine lokale Tabellenvariable und mit veränderter 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;
Raw
Listing 5.6.sql
-- Listing 5.6.sql
-- Beispiel für eine einfache UPDATE-Anweisung
UPDATE tabelle_2
SET wert1 = 'Aktualisiert',
wert2 = 'Zeile ' || key1
WHERE key1 < 300;
Raw
Listing 5.7.sql
-- Listing 5.7.sql
-- Beispiel für eine UPDATE-Anweisung mit Bezug auf eine andere Tabelle
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;
Raw
Listing 5.8.sql
-- Listing 5.8.sql
-- Beispiel für das Einfügen und Aktualisieren über den Primärschlüssel
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;
Raw
Listing 5.9.sql
-- Listing 5.9.sql
-- Beispiel für UPSERT aus Unterabfrage
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;
Raw
Listing 6.1.sql
-- Listing 6.1.sql
-- Definition einer lokalen skalaren Variable
DELETE FROM <Tabellenname> [WHERE <Bedingungen>]
DECLARE <Variablenname>
[CONSTANT]
<Datentyp>
[NOT NULL]
[ '=' | DEFAULT) <Initialwert>;
Raw
Listing 6.10.sql
-- Listing 6.10.sql
-- Beispiel für das zeilenweise Einfügen in eine Tabellenvariable mit INSERT
:<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;
Raw
Listing 6.11.sql
-- Listing 6.11.sql
-- Tabellenweises Einfügen mit der INSERT-Operation
:<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;
Raw
Listing 6.12.sql
-- Listing 6.12.sql
-- Zeilenweises Aktualisieren einer Tabellenvariablen mit UPDATE und indexbasiertem Zugriff
:<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;
Raw
Listing 6.13.sql
-- Listing 6.13.sql
-- Beispiele für die unterschiedlichen Varianten des Löschens von Zeilen aus einer Tabellenvariablen
<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;
Raw
Listing 6.14.sql
-- Listing 6.14.sql
-- Syntax des SEARCH-Operators
:<Tabellenvariabe>.SEARCH((<Spaltenliste>),
(<Werteliste>),
[<Startindex>]);
Raw
Listing 6.15.sql
-- Listing 6.15.sql
-- Suche in Tabellen mit dem SEARCH-Operator
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;
Raw
Listing 6.16.sql
-- Listing 6.16.sql
-- Suche und Schleife mit einer sortierten Tabelle
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;
Raw
Listing 6.17.sql
-- Listing 6.17.sql
-- Verwendung von Sessionvariablen
SET <Variablenname> = <Wert>;
DO BEGIN
SET 'TESTVARIABLE' = 'TESTWERT';
SELECT session_context( 'TESTVARIABLE' ) FROM dummy;
END;
Raw
Listing 6.18.sql
-- Listing 6.18.sql
-- Beispiel für eine globale temporäre Tabelle
CREATE GLOBAL TEMPORARY TABLE test_gtt (id INT,
text NVARCHAR(40));
INSERT INTO test_gtt ( SELECT id, titel FROM aufgaben);
SELECT * FROM test_gtt;
Raw
Listing 6.19.sql
-- Listing 6.19.sql
-- Beispiel für eine lokale 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;
Raw
Listing 6.20.sql
-- Listing 6.20.sql
-- Syntax der IF-Anweisung
IF <Bedingung_1> THEN <Block_1>
[ELSEIF <Bedingung_2> THEN <Block_2>]
...
[ELSE <Block_N>]
END IF;
Raw
Listing 6.21.sql
-- Listing 6.21.sql
-- Prädikate mit Unterabfragen
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;
Raw
Listing 6.22.sql
-- Listing 6.22.sql
-- Beispiel für verschachtelte IF-Anweisungen
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;
Raw
Listing 6.23.sql
-- Listing 6.23.sql
-- Funktion UDF_SORT_2 zur Sortierung von zwei Zahlen
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;
Raw
Listing 6.24.sql
-- Listing 6.24.sql
-- Funktion UDF_SORT_3 für die Sortierung von drei 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;
Raw
Listing 6.25.sql
-- Listing 6.25.sql
-- Anonymer Block zum Testen der Funktion UDF_SORT_3
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;
Raw
Listing 6.26.sql
-- Listing 6.26.sql
-- Syntax der FOR-Schleife
FOR <Variable> IN [REVERSE] <Ausgangswert>..<Endwert> DO
<Block>
END FOR;
Raw
Listing 6.27.sql
-- Listing 6.27.sql
-- Beispiel für eine 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;
Raw
Listing 6.28.sql
-- Listing 6.28.sql
-- Syntax der WHILE-Schleife
WHILE <Bedingung> DO
<Block>
END WHILE;
Raw
Listing 6.29.sql
-- Listing 6.29.sql
-- Beispiel für eine 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;
Raw
Listing 6.3.sql
-- Listing 6.3.sql
-- Beispiel für die Zuweisung von Werten zu lokalen skalaren Variablen
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;
Raw
Listing 6.31.sql
-- Listing 6.31.sql
-- Übungsbeispiel für das Berechnen des ggT
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;
Raw
Listing 6.32.sql
-- Listing 6.32.sql
-- FOR-Schleife über Cursor
DECLARE CURSOR <Cursorname> (<Parameterdefinition>)
FOR <SELECT-Abfrage>;
FOR <Zeile> AS <Cursorname>[(<Parameter>)]
DO
<Block>
END FOR;
Raw
Listing 6.33.sql
-- Listing 6.33.sql
-- Beispiel für eine FOR-Schleife über einen Cursor mit Parameter
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;
Raw
Listing 6.34.sql
-- Listing 6.34.sql
-- Beispiel für die Verwendung eines Cursors
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;
Raw
Listing 6.35.sql
-- Listing 6.35.sql
-- Syntax der UPDATE-Anweisung
DECLARE CURSOR <Cursorname> (<Parameterdefinition>)
FOR <SELECT-Abfrage> FOR UPDATE [OF <Spaltenliste>];
UPDATE <DB-Tabelle>
SET <Set-Klausel>
WHERE CURRENT OF <Cursor>
Raw
Listing 6.36.sql
-- Listing 6.36.sql
-- Syntax der DELETE-Anweisung
DELETE FROM <DB-Tabelle>
WHERE CURRENT OF <Cursor>
Raw
Listing 6.37.sql
-- Listing 6.37.sql
-- Beispiel für den Zugriff auf ein Array in einer Datenbanktabelle
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;
Raw
Listing 6.38.sql
-- Listing 6.38.sql
-- Arrays in SQLScript-Code
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;
Raw
Listing 6.39.sql
-- Listing 6.39.sql
-- Umwandlung von ARRAYs in Tabellen mit UNNEST()
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;
Raw
Listing 6.4.sql
-- Listing 6.4.sql
-- Beispiel für SELECT INTO in mehrere 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;
Raw
Listing 6.40.sql
-- Listing 6.40.sql
-- Konvertierung von einer Tabellenspalte in ein Array und Abfrage der Kardinalität
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;
Raw
Listing 6.41.sql
-- Listing 6.41.sql
-- Der Bubblesort-Algorithmus in SQLScript
--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;
Raw
Listing 6.42.sql
-- Listing 6.42.sql
-- Test des Sortieralgorithmus mit einfachen Daten
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;
Raw
Listing 6.43.sql
-- Listing 6.43.sql
-- Syntax einer autonomen Transaktion
BEGIN AUTONOMOUS TRANSACTION
...
END;
Raw
Listing 6.44.sql
-- Listing 6.44.sql
-- Beispiel für eine Log-Prozedur mit 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;
Raw
Listing 6.45.sql
-- Listing 6.45.sql
-- Beispiel für dynamisches SQL mit einer lokalen temporären Tabelle
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;
Raw
Listing 6.46.sql
-- Listing 6.46.sql
-- Ergebnistabelle einer dynamischen SQL-Abfrage in SQLScript übernehmen
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', ?);
Raw
Listing 6.47.sql
-- Listing 6.47.sql
-- Mehrere skalare Variablen aus einer dynamischen SQL-Abfrage ü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', ?, ?);
Raw
Listing 6.48.sql
-- Listing 6.48.sql
-- Beispiel für Eingabeparameter in dynamischem SQL
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, ?);
Raw
Listing 6.49.sql
-- Listing 6.49.sql
-- Dynamische Selektion aus einer Tabellenvariablen
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(?);
Raw
Listing 6.5.sql
-- Listing 6.5.sql
-- Beispiel für die Fehlerverarbeitung bei SELECT INTO
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, ?, ?);
Raw
Listing 6.50.sql
-- Listing 6.50.sql
-- Dynamisches Filtern mit APPLY_FILTER
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)', ?);
Raw
Listing 6.51.sql
-- Listing 6.51.sql
-- Verwendung von Literalen in dynamischem Code
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
Raw
Listing 6.52.sql
-- Listing 6.52.sql
-- Beispiele für das Auslösen von benutzerdefinierten Ausnahmen
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;
Raw
Listing 6.53.sql
-- Listing 6.53.sql
-- Beispiele für die Fehlerbehandlung von SQL-Fehlern und benutzerdefinierten Fehlern
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;
Raw
Listing 6.54.sql
-- Listing 6.54.sql
-- Beispiel für die Fehlerbehandlung bei Prozeduraufrufen
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;
Raw
Listing 6.55.sql
-- Listing 6.55.sql
-- Weitergabe von Fehlern mit RESIGNAL
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;
Raw
Listing 6.56.sql
-- Listing 6.56.sql
-- Fehlerbehandler mit Ergänzung von Datenbankschema und Prozedur im Fehlertext
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RESIGNAL SET MESSAGE_TEXT = 'Prozedur'
|| ::CURRENT_OBJECT_SCHEMA
|| '.'
|| ::CURRENT_OBJECT_NAME
|| ::SQL_ERROR_MESSAGE;
Raw
Listing 6.6.sql
-- Listing 6.6.sql
-- Verschachtelte Blöcke und Überdeckung von Variablen
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
Raw
Listing 6.7.sql
-- Listing 6.7.sql
-- Indexbasierter Zugriff auf Tabellenvariablen
<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;
Raw
Listing 6.8.sql
-- Listing 6.8.sql
-- Indexbasierter Zugriff mit skalaren Ausdrücken als Index
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;
Raw
Listing 6.9.sql
-- Listing 6.9.sql
-- Zugriff auf nicht vorhandene Zeilen
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;
Raw
Listing 7.1.sql
-- Listing 7.1.sql
-- Spaltendefinition
CREATE TABLE status (id INT ,
text NVARCHAR(30) );
<Spaltenname>
<Datentyp>
[DEFAULT <Standardwert>]
[<Einschränkung>]
Raw
Listing 7.10.sql
-- Listing 7.10.sql
-- Definition von parametrisierten Views
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;
Raw
Listing 7.11.sql
-- Listing 7.11.sql
-- Beispiel für die Nutzung einer Sequenz
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;
Raw
Listing 7.12.sql
-- Listing 7.12.sql
-- Beispiel für einen Trigger für eine automatische Änderungsprotokollierung
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;
Raw
Listing 7.13.sql
-- Listing 7.13.sql
-- Zuweisung von OLD und NEW an lokale Variablen
CREATE TRIGGER rp_update AFTER UPDATE
ON rechnungspositionen
REFERENCING NEW ROW ls_new
OLD ROW ls_old
FOR EACH ROW
...
Raw
Listing 7.14.sql
-- Listing 7.14.sql
-- Parametrisierung für die zeilenweise Verarbeitung
CREATE TRIGGER rp_insert AFTER INSERT
ON rechnungspositionen
REFERENCING NEW ROW ls_new
FOR EACH ROW
...
Raw
Listing 7.15.sql
-- Listing 7.15.sql
-- Parametrisierung für die Verarbeitung pro Anweisung mit Tabellenparametern
CREATE TRIGGER rp_insert AFTER INSERT
ON rechnungspositionen
REFERENCING NEW TABLE AS lt_new
FOR EACH STATEMENT
...
Raw
Listing 7.2.sql
-- Listing 7.2.sql
-- Beispiel für Einschränkungen auf einzelnen Spalten
CREATE TABLE status (id INT PRIMARY KEY,
sortiernr INT NOT NULL UNIQUE,
text NVARCHAR(30) );
Raw
Listing 7.3.sql
-- Listing 7.3.sql
-- Beispiel für mehrere UNIQUE-Einschränkungen auf mehreren Spalten
CREATE TABLE test_unique (a INT,
b INT,
c INT,
UNIQUE(a, b),
UNIQUE(b, c));
Raw
Listing 7.4.sql
-- Listing 7.4.sql
-- Beispiel für einen zusammengesetzten Primärschlüssel
CREATE TABLE test_composite_key (a INT,
b INT,
c INT,
PRIMARY KEY(a, b));
Raw
Listing 7.5.sql
-- Listing 7.5.sql
-- Automatische Nummernvergabe
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;
Raw
Listing 7.6.sql
-- Listing 7.6.sql
-- Anlegen einer neuen Tabelle auf Basis einer SQL-Abfrage
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;
Raw
Listing 7.7.sql
-- Listing 7.7.sql
-- Beispiel für die Änderung von Tabelleneigenschaften
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;
Raw
Listing 7.8.sql
-- Listing 7.8.sql
-- Beispiel für die Definition eines Tabellentyps und die Verwendung in einer Prozedurdefinition
CREATE TYPE TESTTYP AS TABLE(
col1 INT,
col2 VARCHAR(10) );
CREATE PROCEDURE TESTPROC(
IN it_data TESTTYP )
...
Raw
Listing 7.9.sql
-- Listing 7.9.sql
-- Beispiel für einen View als gespeicherte SELECT-Abfrage
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;
Raw
Listing 8.1.sql
-- Listing 8.1.sql
-- Beispiel für eine einfache AMDP-Methode
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.
Raw
Listing 8.10.sql
-- Listing 8.10.sql
-- Beispiel für die Implementierung 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.
Raw
Listing 8.11.sql
-- Listing 8.11.sql
-- ABAP-Programm zum Test der CDS-Tabellenfunktion aus dem Beispiel
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 ).
Raw
Listing 8.13.sql
-- Listing 8.13.sql
-- Generierte UDF-Funktion zu der AMDP-Funktion
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;
Raw
Listing 8.14.sql
-- Listing 8.14.sql
-- Beispiel für die Verwendung einer AMDP-Funktion in einer anderem AMDP-Methode
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.
Raw
Listing 8.15.sql
-- Listing 8.15.sql
-- Beispiel für eine skalare AMDP-Funktion
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.
Raw
Listing 8.2.sql
-- Listing 8.2.sql
-- Schlüsselwörter bei der Implementierung einer 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.
Raw
Listing 8.3.sql
-- Listing 8.3.sql
-- Quellcode der Prozedur ZCL_AMDP_DEMO=>GET_COUNTRIES
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;
Raw
Listing 8.4.sql
-- Listing 8.4.sql
-- Quellcode der Stub-Prozedur
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;
Raw
Listing 8.5.sql
-- Listing 8.5.sql
-- Statische Factory-Methode GET_INSTANCE in ABAP
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.
Raw
Listing 8.6.sql
-- Listing 8.6.sql
-- Aufruf einer AMDP-Prozedur aus einer anderen AMDP-Methode
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.
Raw
Listing 8.7.sql
-- Listing 8.7.sql
-- Grundgerüst der Definition einer CDS-Tabellenfunktion
METHOD <Methodenname> BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
[USING <Verwendungen>].
<SQLScript-Code>
ENDMETHOD.
Raw
Listing 8.8.sql
-- Listing 8.8.sql
-- Aufbau der Implementierung einer AMDP-Funktion
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 ).
Raw
Listing 8.9.sql
-- Listing 8.9.sql
-- Beispiel für die Definition einer CDS-Tabellenfunktion
@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;
Raw
Listing 9.1.sql
-- Listing 9.1.sql
-- Vergabe einer laufenden Nummer für das Feld RECORD für die Sortierung
outtab = SELECT ...
LPAD( ROW_NUMBER() OVER (ORDER BY <Sortierung>), 56, '0')
AS "RECORD",
SQL__PROCEDURE__SOURCE__RECORD
FROM :intab ;
Raw
Listing 9.2.sql
-- Listing 9.2.sql
-- Minimale Implementierung einer Endroutine
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.
Raw
Listing 9.3.sql
-- Listing 9.3.sql
-- USING-Klausel in AMDP-Routinen
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.
Raw
Listing 9.4.sql
-- Listing 9.4.sql
-- Absicherung gegen NULL-Werte mit COALESCE
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
Raw
Listing 9.5.sql
-- Listing 9.5.sql
-- Dummy-Implementierung einer Expertenroutine
METHOD GLOBAL_EXPERT BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY.
outtab = SELECT * FROM :outtab;
errortab = SELECT * FROM :errortab;
ENDMETHOD.
Raw
Listing 9.6.sql
-- Listing 9.6.sql
-- Expertenroutine zum Transponieren von Kennzahlen
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.
Raw
Listing 9.7.sql
-- Listing 9.7.sql
-- Einfache Fehlerermittlung aus den Eingangsdaten der Tabelle INTAB
errortab = SELECT 'Währung ist leer!' AS ERROR_TEXT,
SQL__PROCEDURE__SOURCE__RECORD
FROM :intab
WHERE currency = ' '
AND amount <> 0;
Raw
Listing 9.8.sql
-- Listing 9.8.sql
-- Fehlerermittlung aus der OUTTAB
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
Raw
Listing 9.9.sql
-- Listing 9.9.sql
-- Prüfung auf Steuerzeichen
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 = '#';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment