SQLScript Grundlagen

Deklarative Programmierung

(C) Brandeis Consulting.

Deklarative vs. Imperative Programmierung

Deklarativ (SQLScript)

  • Es wird das Ergebnis beschrieben
  • Alle Schritte basieren auf Tabellen
  • Nur SELECT Anweisungen und Tabellenvariablen sind erlaubt
  • Es gibt keine Verzweigungen im Programmfluss.

Imperativ (SQL oder ABAP)

  • Ein Algorithmus wird Anweisung für Anweisung vorgegeben
  • Die Datenverarbeitung findet zeilen- und feldweise statt
  • Der Programmfluss wird in Abhängigkeit von den Daten gesteuert
(C) Brandeis Consulting.

Beispiel für ein schlechtes ABAP Coding

 FIELD-SYMBOLS: <ls_data> TYPE dtfitx_4.
  
  LOOP AT c_c_t_data ASSIGNING <ls_data>.
    SELECT SINGLE bupla gsber FROM bseg
      INTO (<ls_data>-bupla,<ls_data>-gsber)
      WHERE bukrs  = <ls_data>-bukrs
        AND belnr  = <ls_data>-belnr
        AND gjahr  = <ls_data>-gjahr
        AND buzei  = <ls_data>-buzei.
  ENDLOOP.

Das hat zu Laufzeitproblemen geführt. Also gab es ein Refactoring....

(C) Brandeis Consulting.

Beispiel für ein besseres ABAP Coding

TYPES: BEGIN OF ty_bseg,
          bukrs TYPE bukrs,
          belnr TYPE belnr_d,
          gjahr TYPE gjahr,
          buzei TYPE buzei,
          gsber TYPE gsber,
          bupla TYPE bupla,
        END OF ty_bseg.

DATA: lt_bseg_sel TYPE STANDARD TABLE OF ty_bseg 
                      WITH DEFAULT KEY,
      ls_bseg     TYPE ty_bseg,
      lth_bseg    TYPE HASHED TABLE OF ty_bseg
                WITH UNIQUE KEY bukrs belnr gjahr buzei.

LOOP AT c_c_t_data ASSIGNING FIELD-SYMBOLS(<ls_data>).
  ls_bseg-bukrs = <ls_data>-bukrs.
  ls_bseg-belnr = <ls_data>-belnr.
  ls_bseg-gjahr = <ls_data>-gjahr.
  ls_bseg-buzei = <ls_data>-buzei.
  APPEND ls_bseg TO lt_bseg_sel.
ENDLOOP.

SORT lt_bseg_sel.
DELETE ADJACENT DUPLICATES FROM lt_bseg_sel.

SELECT bukrs belnr gjahr buzei gsber bupla
        FROM bseg
        INTO TABLE lth_bseg
        FOR ALL ENTRIES IN lt_bseg_sel
        WHERE bukrs = lt_bseg_sel-bukrs
          AND belnr = lt_bseg_sel-belnr
          AND gjahr = lt_bseg_sel-gjahr
          AND buzei = lt_bseg_sel-buzei.


LOOP AT c_c_t_data ASSIGNING <ls_data>.

  read TABLE lth_bseg ASSIGNING FIELD-SYMBOLS(<s_bseg>)
              with TABLE KEY bukrs = <ls_data>-bukrs
                            belnr = <ls_data>-belnr
                            gjahr = <ls_data>-gjahr
                            buzei = <ls_data>-buzei.
  if sy-subrc = 0.
    <ls_data>-bupla = <s_bseg>-bupla.
    <ls_data>-gsber = <s_bseg>-gsber.
  endif.
ENDLOOP.
Aber immer noch Schritt für Schritt deklariert. Kann also nicht parallelisiert werden.
(C) Brandeis Consulting.

Beispiel für die gleiche Logik in SQLScript

outtab = SELECT it.*,
                bseg.bupla,
                bseg.gsber
         FROM :intab as it
           LEFT OUTER JOIN bseg
           ON  it.bukrs  = bseg.bukrs
           AND it.belnr  = bseg.belnr
           AND it.gjahr  = bseg.gjahr
           AND it.buzei  = bseg.buzei;
(C) Brandeis Consulting.

Flipchart / Whiteboard

Vergleich Transformationsroutine Deklarativ (SQLScript) und Imperativ (ABAP)

(C) Brandeis Consulting.

Ansatz 1: Eine Abfrage in einer SELECT Anweisung

DO BEGIN  
  SELECT  task.id, 
          task.assignee,
          task.project,
          assignee.firstname AS as_firstname,
          assignee.lastname  AS as_surname,
          project.id         AS p_id,
          pm.id              AS pl_id,
          pm.firstname       AS pm_firstname,
          pm.lastname        AS pm_lastname
  
  FROM tasks            AS task
  
  LEFT OUTER JOIN users AS assignee
  ON task.assignee = assignee.id
  
  LEFT OUTER JOIN projects AS project
  ON task.project     = project.id
  
  LEFT OUTER JOIN users AS pm
  ON project.project_manager = pm.id     ;    
END;

Wie können wir das mit Tabellenvariablen in kleinere Schritte zerlegen?

(C) Brandeis Consulting.

Ansatz 2: DB-Tabellen vorab in Tabellenvariablen lesen

DO BEGIN
 lt_tasks    = SELECT id AS tasks_id, assignee, project FROM tasks;
 lt_user     = SELECT id AS user_id, firstname, lastname FROM users;
 lt_projects = SELECT id AS project_id, project_manager FROM projects;
 
 SELECT *
 FROM :lt_tasks AS a
     
 LEFT OUTER JOIN :lt_user as assignee
 ON a.assignee = assignee.user_id
     
 LEFT OUTER JOIN :lt_projects as project
 ON a.project     = project.project_id
     
 LEFT OUTER JOIN :lt_user as pm
 ON project. project_manager = pm.user_id;
     
END;
(C) Brandeis Consulting.

Ansatz 3: Schritt für Schritt

DO BEGIN
 lt_tasks = SELECT id, assignee, project FROM tasks;
 
 lt_with_user = SELECT tasks.*,
                       assignee.firstname, 
                       assignee.lastname
                FROM :lt_tasks AS tasks
                LEFT OUTER JOIN users as assignee
                ON tasks.assignee = assignee.id;
                     
 lt_with_project = SELECT tmp.*,
                          project.id AS projektid,
                          project.project_manager
                   FROM :lt_with_user AS tmp
                   LEFT OUTER JOIN projects AS project
                   ON tmp.project = project.id;

 lt_complete = SELECT tmp.*,
                      pm.firstname as PL_firstname,
                      pm.lastname as PL_surname
               FROM :lt_with_project AS tmp
               LEFT OUTER JOIN users  AS pm
               ON pm.id = tmp.project_manager;
              
 SELECT * FROM :lt_complete;
END;
(C) Brandeis Consulting.

Vergleich der Ansätze

Welcher Ansatz

  • braucht am meisten Speicherplatz (Ram)?
  • ist am schnellsten?
  • ist am besten lesbar?
  • eignet sich am besten für die Fehlersuche mit Debugging?
(C) Brandeis Consulting.