Detaillierte Erläuterung der gespeicherten MySQL-Prozeduren_MySQL

mysql/>Gespeicherte MySQL-Prozedur

14.1.1 Gespeicherte Prozeduren erstellen

In MySQL sieht die Grundform zum Erstellen einer gespeicherten Prozedur wie folgt aus:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
         [Merkmal ...] routine_body

Dabei ist der Parameter sp_name der Name der gespeicherten Prozedur; der Parameter „characteristic“ gibt die Eigenschaften der gespeicherten Prozedur an; der Parameter „routine_body“ ist der Inhalt des SQL-Codes Mit ...END können Sie den Anfang und das Ende des SQL-Codes markieren.

Jeder Parameter in proc_parameter besteht aus 3 Teilen. Diese drei Teile sind Eingabe- und Ausgabetypen, Parameternamen und Parametertypen. Seine Form ist wie folgt:

[ IN | OUT |. INOUT ] param_name Typ

Darunter stellt IN den Eingabeparameter dar; OUT stellt entweder Eingabe oder Ausgabe dar; der Parametertyp gibt den Parametertyp der gespeicherten Prozedur an; Kann MySQL sein. Jeder Datentyp der Datenbank.

Der charakteristische Parameter hat mehrere Werte. Die Wertbeschreibung lautet wie folgt:

SPRACHE SQL: Erklären Sie, dass der routine_body-Teil aus SQL-Sprachanweisungen besteht, die auch die Standardsprache des Datenbanksystems sind.

[NICHT] DETERMINISTISCH: Gibt an, ob das Ausführungsergebnis der gespeicherten Prozedur deterministisch ist. DETERMINISTISCH bedeutet, dass das Ergebnis deterministisch ist. Jedes Mal, wenn die gespeicherte Prozedur ausgeführt wird, führt dieselbe Eingabe zu derselben Ausgabe. NICHT DETERMINISTISCH bedeutet, dass das Ergebnis nicht deterministisch ist und die gleiche Eingabe unterschiedliche Ausgaben erzeugen kann. Standardmäßig sind die Ergebnisse nicht deterministisch.

{ ENTHÄLT SQL |. LIES SQL-DATEN |. ÄNDERT SQL-DATEN }: Zeigt die Einschränkungen bei der Verwendung von SQL-Anweisungen durch Unterprogramme an. CONTAINS SQL bedeutet, dass das Unterprogramm SQL-Anweisungen enthält, aber keine Anweisungen zum Lesen oder Schreiben von Daten; NO SQL bedeutet, dass das Unterprogramm keine SQL-Anweisungen enthält. READS SQL DATA bedeutet, dass das Unterprogramm Anweisungen zum Lesen von Daten enthält dass das Unterprogramm Anweisungen zum Lesen von Daten enthält. Standardmäßig gibt das System CONTAINS SQL an.

SQL SECURITY { INVOKER }: Geben Sie an, wer die Berechtigung zur Ausführung hat. DEFINER bedeutet, dass nur der Definierer es ausführen kann; INVOKER bedeutet, dass der Aufrufer es ausführen kann. Standardmäßig ist die vom System angegebene Berechtigung DEFINER.

COMMENT 'string': Kommentarinformationen.

Tipp: Beim Erstellen einer gespeicherten Prozedur gibt das System standardmäßig CONTAINS SQL an, was darauf hinweist, dass SQL-Anweisungen in der gespeicherten Prozedur verwendet werden. Wenn in der gespeicherten Prozedur jedoch keine SQL-Anweisungen verwendet werden, ist es am besten, sie auf NO SQL zu setzen. Darüber hinaus ist es am besten, im Abschnitt KOMMENTAR einfache Kommentare zur gespeicherten Prozedur abzugeben, damit Sie den Code der gespeicherten Prozedur in Zukunft bequemer lesen können.

[Beispiel 14-1] Erstellen Sie unten eine gespeicherte Prozedur mit dem Namen num_from_employee. Der Code lautet wie folgt:

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id ; 
     END 

Im obigen Code ist der Name der gespeicherten Prozedur num_from_employee; die Eingabevariable ist emp_id; die Ausgabevariable ist count_num. Die SELECT-Anweisung fragt die Datensätze aus der Mitarbeitertabelle ab, deren d_id-Wert gleich emp_id ist, berechnet mithilfe von COUNT(*) die Anzahl der Datensätze mit demselben d_id-Wert und speichert schließlich die Berechnungsergebnisse in count_num. Das Ausführungsergebnis des Codes lautet wie folgt:

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id ; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ; 

Nachdem der Code ausgeführt wurde, wird keine Fehlermeldung gemeldet, was bedeutet, dass die Speicherfunktion erfolgreich erstellt wurde. Diese gespeicherte Prozedur kann später aufgerufen werden und die SQL-Anweisungen in der gespeicherten Prozedur werden in der Datenbank ausgeführt.

Hinweis: Das Standard-Anweisungsabschlusszeichen in MySQL ist ein Semikolon (;). SQL-Anweisungen in gespeicherten Prozeduren erfordern am Ende ein Semikolon. Um Konflikte zu vermeiden, setzen Sie zunächst mit „DELIMITER &&“ das MySQL-Terminator auf &&. Verwenden Sie abschließend „DELIMITER;“, um das Abschlusszeichen im Semikolon wiederherzustellen. Dies ist dasselbe wie beim Erstellen eines Triggers.

14.1.2 Gespeicherte Funktion erstellen

In MySQL ist die Grundform zum Erstellen einer gespeicherten Funktion wie folgt:

CREATE FUNCTION sp_name ([func_parameter[,...]]) 
    RETURNS type 
    [characteristic ...] routine_body 

Dabei ist der Parameter sp_name der Name der gespeicherten Funktion; der Typ RETURNS gibt den Typ des Rückgabewerts an Dieser Parameter ist derselbe wie der Wert in der gespeicherten Prozedur. Weitere Informationen finden Sie im Abschnitt 14.1.1. Der Parameter routine_body ist der Inhalt des SQL-Codes, und BEGIN ... END kann zum Markieren des Anfangs verwendet werden und Ende des SQL-Codes.

func_parameter kann aus mehreren Parametern bestehen, wobei jeder Parameter aus Parametername und Parametertyp besteht und die folgende Form hat:

param_name Typ
Unter diesen ist der Parameter param_name der Parametername der gespeicherten Funktion; der Typparameter gibt den Parametertyp der gespeicherten Funktion an, bei dem es sich um einen beliebigen Datentyp der MySQL-Datenbank handeln kann.

[Beispiel 14-2] Erstellen Sie als Nächstes eine gespeicherte Funktion mit dem Namen name_from_employee. Der Code lautet wie folgt:

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END 

Im obigen Code lautet der Name der Speicherfunktion name_from_employee; der Parameter dieser Funktion ist emp_id; der Rückgabewert ist vom Typ VARCHAR. Die SELECT-Anweisung fragt den Datensatz, dessen Num-Wert gleich emp_id ist, aus der Mitarbeitertabelle ab und gibt den Wert des Namensfelds des Datensatzes zurück. Das Ausführungsergebnis des Codes lautet wie folgt:

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ; 

Die Ergebnisse zeigen, dass die Speicherfunktion erfolgreich erstellt wurde. Die Verwendung dieser Funktion entspricht der Verwendung interner MySQL-Funktionen.

14.1.3 Verwendung von Variablen

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

1．定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

DECLARE var_name[,...] type [DEFAULT value]

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

【示例14-3】 下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

DECLARE my_sql INT DEFAULT 10 ;

2．为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

SET var_name = expr [, var_name = expr] ...

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

【示例14-4】 下面为变量my_sql赋值为30。代码如下：

SET my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

SELECT col_name[,…] INTO var_name[,…]
FROM table_name WEHRE condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

【示例14-5】 下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

SELECT d_id INTO my_sql
FROM employee WEHRE id=2 ;

14.1.4 定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

1．定义条件

MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

DECLARE condition_name CONDITION FOR condition_value 
condition_value: 
   SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

【示例14-6】 下面定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：

//方法一：使用sqlstate_value 
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ; 
//方法二：使用mysql_error_code 
DECLARE can_not_find CONDITION FOR 1146 ; 

2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement 
handler_type: 
  CONTINUE | EXIT | UNDO 
condition_value: 
  SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING 
    | NOT FOUND | SQLEXCEPTION | mysql_error_code 

其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。

condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

【示例14-7】 下面是定义处理程序的几种方式。代码如下：

//方法一：捕获sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND'; 
//方法二：捕获mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; 
//方法三：先定义条件，然后调用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND'; 
//方法四：使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
//方法五：使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
//方法六：使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR'; 

Der obige Code bietet 6 Möglichkeiten zum Definieren von Handlern. Die erste Methode besteht darin, den sqlstate_value-Wert zu erfassen. Wenn der sqlstate_value-Wert 42S02 ist, führen Sie die CONTINUE-Operation aus und geben Sie die Meldung „CAN NOT FIND“ aus. Die zweite Methode besteht darin, den mysql_error_code-Wert zu erfassen. Wenn der mysql_error_code-Wert 1146 ist, wird die CONTINUE-Operation ausgeführt und die Meldung „CAN NOT FIND“ ausgegeben. Die dritte Methode besteht darin, zuerst die Bedingung zu definieren und dann die Bedingung aufzurufen. Hier definieren wir zunächst die Bedingung can_not_find und führen die Operation CONTINUE aus, wenn ein Fehler 1146 auftritt. Die vierte Methode ist die Verwendung von SQLWARNING. SQLWARNING erfasst alle sqlstate_value-Werte beginnend mit 01, führt dann die EXIT-Operation aus und gibt „ERROR“-Informationen aus. Die fünfte Methode besteht darin, NOT FOUND zu verwenden. NOT FOUND erfasst alle sqlstate_value-Werte beginnend mit 02, führt dann die EXIT-Operation aus und gibt die Information „CAN NOT FIND“ aus. Die sechste Methode ist die Verwendung von SQLEXCEPTION. SQLEXCEPTION erfasst alle sqlstate_value-Werte, die nicht von SQLWARNING oder NOT FOUND erfasst werden, führt dann die EXIT-Operation aus und gibt „ERROR“-Informationen aus.

Vielen Dank für das Lesen dieses Artikels. Ich hoffe, er kann allen helfen. Vielen Dank für Ihre Unterstützung dieser Website!