Routinendefinitionen in MySQL

SQL ist eine deklarative Sprache. Man schreibt nieder, welche Information man wünscht. Aus den Abfragen erzeugt der Abfrageoptimierer einen Abfrageplan, der die einzelnen Schritte enthält, die nötig sind, um das Ergebnis der Abfrage zu ermitteln. Der Abfrageoptimierer kennt die Stärken und Schwächen des verwendeten Datenkbanksystems oft gut und kann den Abfrageplan daher so erstellen, daß er oft sehr schnell abgearbeitet werden kann.

Würde man eine Information suchen, indem man mit einer prozeduralen Sprache auf die Basistabellen zugreift und dann die Daten mit der prozeduralen Sprache miteinander verknüpft, so würde dies in der Regel langsamer und fehlerträchtiger sein als bei Verwendung von SQL-Abfragen. Deswegen soll man für das was das deklarative SQL gut kann bevorzugt das deklarative SQL verwenden (also SELECT-Abfragen und View-Definitionen). Prozeduren sollten nur eingesetzt werden, um etwas zu erledigen, das mit SQL schlecht geht, wie beispielsweise die Erzeugung von Zeilennummern zur Ausgabe einer durchnumerierten Liste. Wenn man eine Abfrage in deklarativem SQL geschrieben hat, und dann Teile davon durch Funktionen oder Prozeduren ersetzt, die in SQL geschrieben sind, dann wird die Abfrage dadurch in der Regel deutlich langsamer.

Normalerweise verwendet jedes Datenbanksystem, das es erlaubt Prozeduren zu schreiben, eine eigene Prozedursprache dafür und nicht die erst 2003 standardisierte Standard-Prozedursprache von SQL 2003. Ausgerechnet MySQL ist jedoch eine Ausnahme: Die MySQL-Prozedursprache ist relativ standardkonform. Leider bedeutet dies derzeit aber noch nicht, daß man in MySQL geschriebene Prozeduren leicht auf andere Datenbanksysteme übertragen kann, da diese noch nicht standardkonform sind. Wenn man aber überhaupt Prozeduren schreiben will oder muß, dann ist gerade MySQL eine gute Wahl dafür, weil die Prozedursprache von MySQL dem Sprachstandard recht nahekommt. Alternativ können Prozeduren aber auch in Programmiersprachen wie Java oder PHP geschrieben werden, von denen aus ebenfalls auf MySQL-Datenbanken zugegriffen werden kann.

Systemvariablen

Systemvariablen (global und session)

Einiges dazu wurde schon in Lektion 723195 „Einstellungen “ gesagt.

Die folgende Ausgabe von »@@sql_mode« nach »SET sql_mode = 'ANSI';« zeigt, welche anderen Einstellungen die Einstellung »ANSI« umfaßt.

Konsole

SET sql_mode = 'ANSI'; SELECT @@sql_mode;

+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+

Benutzervariablen

Benutzervariablen beginnen mit @ und gelten nur für eine Sitzung

SET @v = …

SET @v = f( @v )

SET @v := …

SELECT @v := …

SELECT col INTO @v …

Im letzten Fall sollte nur eine Zeile zurückgegeben werden.

Lokale Variable

Mit DECLARE in Routine

Einfache Funktionsdefinitionen

—

mysql> CREATE FUNCTION R.NAND( A BOOL, B BOOL )RETURNS BOOL DETERMINISTIC RETURN NOT( A AND B );
Query OK, 0 rows affected (0.33 sec)
mysql> SELECT R.NAND( 0, 1 );
+-------------+
| R.NAND(0,1) |
+-------------+
|           1 |
+-------------+
1 row in set (0.19 sec)
mysql> DROP FUNCTION R.NAND;
Query OK, 0 rows affected (0.00 sec)
CREATE FUNCTION hello( name VARCHAR(20) )RETURNS VARCHAR(30)
BEGIN
   RETURN CONCAT('Hello ', name);
END
SELECT sayHello('peter');
CREATE FUNCTION reorderCostFn (a INT) RETURNS DECIMAL(8,2)
BEGIN
  SELECT IFNULL(sum(price*(a-q)), 0) INTO @tmp
  FROM products 
  WHERE q < a;
 
  RETURN @tmp;
END//

Prozedurdefinition

CREATE PROCEDURE example(parameters)
BEGIN
   DECLARE v1 type [DEFAULT value];
   statement1;
   ....
   statementN;
END
CREATE PROCEDURE example()
SELECT 1;
CALL example;

Routinendefinitionen mit Semikola

Manche Funktionsdefinitionen enthalten Semikola. Dann muß ein andere Kommandoterminierer eingestellt werden.

DELIMITER $$
CREATE FUNCTION …
$$
DELIMITER ;
CREATE PROCEDURE beginExample()
BEGIN
  SELECT 1;
  SELECT 2;
END
CREATE PROCEDURE example (IN a INT, OUT b DECIMAL(6,2))
BEGIN
  SELECT IFNULL(sum(price*(a-field)), 0) INTO b
  FROM example
  WHERE field < a;
END
CALL example(5000, @tmp);
SELECT @tmp;

Kontrollstrukturen

IF condition THEN statements; [ELSEIF condition THEN statements;] [ELSE statements;] END IF;

CASE caseValue WHEN value1 THEN statements; WHEN value2 THEN statements; ...... [ELSE sStatements;] END CASE;

CASE WHEN condition1 THEN statements; WHEN condition2 THEN statements; ....... [ELSE statements;] END CASE;

REPEAT statements UNTIL condition REPEAT statements; UNTIL condition END REPEAT;

WHILE condition is true DO statements WHILE condition DO statements; END WHILE;

LOOP statements; LEAVE label; END LOOP [end_label]; = break

label: LOOP ...... ITERATE label; ...... END LOOP = continue

IFNULL(value, nullResult) : nullResult, wenn value NULL, sonst value

ISNULL(value) : true wenn null

NULLIF(value1, value2) : null wenn beide gleich

Beispiel

Die folgende Prozedur soll eine SELECT-Abfrage für verschiedene Werte ausprobieren.

DROP SCHEMA S20160429;
CREATE SCHEMA S20160429;
USE S20160429;
CREATE TABLE TBL_SHIFT
( SHIFT_ID SERIAL,
  SHIFT_NAME VARCHAR( 255 ) UNIQUE NOT NULL,
  SHIFT_START CHAR( 8 ) NOT NULL,
  SHIFT_END CHAR( 8 ) NOT NULL );
INSERT INTO TBL_SHIFT ( SHIFT_NAME, SHIFT_START, SHIFT_END ) VALUES ( 'Morning', '09:00:00', '13:00:00' );
INSERT INTO TBL_SHIFT ( SHIFT_NAME, SHIFT_START, SHIFT_END ) VALUES ( 'Evening', '13:00:00', '16:15:00' );
INSERT INTO TBL_SHIFT ( SHIFT_NAME, SHIFT_START, SHIFT_END ) VALUES ( 'Night',   '16:01:00', '09:00:00' );
DELIMITER $$
CREATE PROCEDURE TRYOUT()
BEGIN
  DECLARE X INT DEFAULT 0;
  DECLARE CURRENT_TIME_ VARCHAR( 255 ) DEFAULT '';
  SET X = 1;
  WHILE x <= 24 DO
    SET CURRENT_TIME_ = CONCAT( IF( X < 10, '0', '' ), X, ':00:00' );
    SELECT 
      *, CURRENT_TIME_
    FROM
      TBL_SHIFT
    WHERE IF
    ( SHIFT_START <= SHIFT_END,
      CURRENT_TIME_ BETWEEN SHIFT_START AND SHIFT_END,
      CURRENT_TIME_ BETWEEN SHIFT_START AND '24:00:00' OR
      CURRENT_TIME_ BETWEEN '00:00:00' AND SHIFT_END );
    SET X = X + 1;
  END WHILE;
END$$
DELIMITER ;
CALL TRYOUT();
DELIMITER $$
CREATE FUNCTION Z( X VARCHAR ( 255 ) )
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE V INT DEFAULT 0;
  SET V = 0;
  SELECT ORT INTO V FROM ORT WHERE NAME = X;
  RETURN V;
  END$$
DELIMITER ;

Beispiel »`FILLPROC`«

Main.sql

WARNINGS; SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA IF EXISTS Sq9i7vl$1bd4$_20190421182933; 
CREATE SCHEMA         Sq9i7vl$1bd4$_20190421182933; 
USE                   Sq9i7vl$1bd4$_20190421182933;
CREATE TABLE DIET
( DIET SERIAL PRIMARY KEY, ENGLISH VARCHAR( 50 ) NOT NULL UNIQUE );
INSERT INTO DIET( ENGLISH )VALUES( 'vegan' );
INSERT INTO DIET( ENGLISH )VALUES( 'vetarian' );
CREATE TABLE FOODSTYLES
( FOODSTYLE SERIAL PRIMARY KEY,
  DIET BIGINT UNSIGNED NOT NULL,
  INGREDIENT SMALLINT NOT NULL,
  FOREIGN KEY( DIET )REFERENCES DIET( DIET ));
DELIMITER $$
DROP PROCEDURE IF EXISTS FILLPROC$$
CREATE PROCEDURE FILLPROC() 
BEGIN 
    DECLARE I INTEGER; 
    DECLARE VEGAN_INGREDIENTS JSON; 
    SET I = 0;
    SET VEGAN_INGREDIENTS = JSON_ARRAY( 4, 2, 8, 9, 3 );
    WHILE I < JSON_LENGTH( VEGAN_INGREDIENTS )DO 
        INSERT INTO FOODSTYLES( DIET, INGREDIENT )
        VALUES
        ( 1, JSON_EXTRACT( VEGAN_INGREDIENTS, CONCAT( '$[', I, ']' )));
        SET I = I + 1; 
    END WHILE; 
END$$
DELIMITER ;
CALL FILLPROC;
SELECT FOODSTYLE, ENGLISH, INGREDIENT FROM FOODSTYLES INNER JOIN DIET USING( DIET );

transcript

+-----------+---------+------------+
| FOODSTYLE | ENGLISH | INGREDIENT |
+-----------+---------+------------+
|         1 | vegan   |          4 |
|         2 | vegan   |          2 |
|         3 | vegan   |          8 |
|         4 | vegan   |          9 |
|         5 | vegan   |          3 |
+-----------+---------+------------+

Die Wandlung von »4, 2, 8, 9, 3« in eine Spalte ist gewissermaßen das Gegenteil von »GROUP_CONCAT«.