So erstellen Sie gespeicherte MySQL-Prozeduren und -Funktionen

1.0 Gespeicherte Prozeduren und Funktionen erstellen

Das Erstellen gespeicherter Prozeduren und Funktionen besteht darin, einen Satz häufig verwendeter SQL-Anweisungen zusammenzufassen und diese SQL-Anweisungen als Ganzes auf dem MySQL-Server zu speichern# 🎜🎜#

1. Erstellen Sie eine gespeicherte Prozedur

CREATE PROCEDURE procedure_name ([proc_param[,...]])
    [characteristic ...] routine_body

• Prozedurname stellt den Namen der zu erstellenden gespeicherten Prozedur dar

• proc_param stellt die Parameter der gespeicherten Prozedur dar.

• characteristic stellt die Eigenschaften der gespeicherten Prozedur dar.

#🎜 🎜#routine_body repräsentiert die SQL-Anweisung der gespeicherten Prozedur
procedure_name darf keine doppelten Namen haben

proc_param Die Syntaxform jedes Parameters in proc_param Jeder Parameter besteht aus drei Teilen: Eingabe-/Ausgabetyp, Parametername und Parametertyp. Es gibt drei Ein- und Ausgabetypen, nämlich IN, OUT und INOUT. param_name stellt den Parameternamen dar, type stellt den Parametertyp dar

[ IN | OUT | INOUT ] param_name type

characteristic gibt die Eigenschaften der gespeicherten Prozedur mit den folgenden Werten an:

SPRACHE SQL: Beschreibung Die gespeicherte Prozedur ist in der SQL-Sprache geschrieben. Die gleiche Eingabe erhält die gleiche Ausgabe. NICHT DETERMINISTISCH bedeutet, dass das Ergebnis unsicher ist. Die gleiche Eingabe kann eine andere Ausgabe erhalten. Die Standardeinstellung ist NICHT DETERMINISTISCH 🎜#

• #🎜🎜 #{CONTAINS SQL | READS SQL DATA |. MODIFIES SQL DATA}: Zeigt die Einschränkungen bei der Verwendung von SQL-Anweisungen durch das Unterprogramm an enthält keine Anweisungen zum Lesen oder Schreiben von Daten. NO SQL bedeutet, dass das Unterprogramm kein SQL enthält. READS SQL DATA bedeutet, dass das Unterprogramm Anweisungen zum Lesen von Daten enthält, aber keine Anweisungen zum Schreiben von Daten. MODIFIES SQL DATA bedeutet, dass das Unterprogramm enthält Anweisungen zum Schreiben von Daten

• #SQL SECURITY {DEFINER |. Gibt an, wer die Berechtigung zum Ausführen hat. bedeutet, dass nur der Definierer ausführen kann mit Berechtigung kann ausgeführt werden. Der Standardwert ist DEFINER

COMMENT 'string ': Anmerkungsinformationen, die zur Beschreibung gespeicherter Prozeduren oder Funktionen
• #🎜🎜 verwendet werden können #
  Beispiel: Erstellen Sie eine gespeicherte Prozedur, um das Gehalt aller Mitarbeiter in der Tabelle t_employee abzufragen.

DELIMITER $$
CREATE PROCEDURE proc_employee()
COMMENT '查询员工薪水'
BEGIN
	SELECT salary FROM t_employee
END;
$$
DELIMITER ;

• MySQL verwendet standardmäßig Semikolon als Anweisungsabschlusszeichen Um Konflikte zu vermeiden, verwenden Sie zunächst DELIMITER $$, um das MySQL-Terminator auf $$ zu setzen, und verwenden Sie dann DELIMITER. Stellen Sie das Endzeichen auf ein Semikolon wieder her. Erstellen Sie eine gespeicherte Funktion

  CREATE FUNCTION fun_name([func_param,[,...]])
  	[characteristic ...] routine_body

• fun_name steht für den Funktionsnamen

  #🎜🎜 #

func_param steht für den Funktionsparameter #🎜🎜 #

characteristic stellt die Funktionsmerkmale dar, der Wert ist derselbe wie der Wert der gespeicherten Prozedur

#🎜 🎜#

routine_body stellt dar SQL-Anweisung der Funktion

• Die Syntaxform jedes Parameters in func_param ist wie folgt: Parametername und Parametertyp#🎜 🎜#

  #🎜 🎜#param_type type

• Es ist immer noch das vorherige Beispiel

  DELIMITER $$
  	CREATE FUNCTION func_employee(id INT(4))
  		RETURNS INT(6)
  	COMMENT '查询员工薪水'
  	BEGIN
  		SELECT salary FROM t_employee
  	END;
  	$$
  DELIMITER ;

2|0 Variable

• in In MySQL können Variablen definiert werden Der Gültigkeitsbereich dieser Variablen befindet sich im Programmabschnitt BEGIN ... END. 🎜#Das Schlüsselwort DECLARE wird zum Deklarieren von Variablen verwendet.

• Der Parameter var_name ist der Name der Variablen können mehrere Variablen gleichzeitig definiert werden

Der Parametertyp wird verwendet, um den Typ der Variablen anzugeben

# 🎜🎜#

DEFAULT-Wertklausel setzt den Standardwert der Variablen auf value Wenn die DEFAULT-Klausel nicht verwendet wird, ist der Standardwert NULL

Beispiel : Definieren Sie die Variable sql_test, der Datentyp ist INT, der Standardwert ist 10

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

2. Variablenzuweisung

DECLARE test_sql INT DEFAULT 10;

Das Schlüsselwort SET ist Wird verwendet, um Variablen Werte zuzuweisen Parameter expr ist ein Zuweisungsausdruck

• Beispiel: Weisen Sie die Variable sql_test 30 zu

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

3| 🎜🎜# Durch das Definieren von Bedingungen und Handlern werden die Probleme, die während der Programmausführung auftreten können, im Voraus definiert. Die Fähigkeit zur Lösung dieser Probleme kann im Handler definiert werden.
• #🎜 🎜#1 Bedingungen definieren#🎜🎜 #

  SET test_sql = 30;

Der Parameter Condition_Name repräsentiert den Bedingungsnamen
Der Parameter Condition_value repräsentiert den Bedingungstyp# 🎜🎜##🎜 🎜#

• Sowohl der Parameter sqlstate_value als auch der Parameter mysql_error_code können MySQL-Fehler darstellen

Beispiel: Definieren Sie ERROR 1146(42S02). can_not_find, kann auf zwei verschiedene Arten definiert werden

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

2. Definieren Sie den Handler

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

• handler_type gibt den Fehler an Verarbeitungsmethode: Dieser Parameter hat drei Werte:

• CONTINUE: Zeigt an, dass ein Fehler nicht verarbeitet wird und die Ausführung fortgesetzt wird
#🎜🎜 #

• EXIT: Zeigt das sofortige Beenden an, wenn ein Fehler auftritt.

UNDO: Zeigt an, dass der vorherige Vorgang rückgängig gemacht wird, nachdem ein Fehler aufgetreten ist.

#🎜 🎜##🎜 🎜#

condition_value stellt den Fehlertyp dar und kann die folgenden Werte haben:

SQLSTATE[VALUE]sqlstate_value Ein String-Fehler Wert mit 5 Zeichen
condition_name stellt den Fehlerbedingungsnamen dar, der durch DECLARE CONDITION
• definiert ist

• SQLWARNING 匹配所有 01 开头的 SQLSTATE 错误代码

• NOT FOUND 匹配所有 02 开头的 SQLSTATE 错误代码

• SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码

• mysql_error_code 匹配数值类型错误代码

proc_statement 匹配数值类型错误代码

下面是定义处理程序的几种示例方式：

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

4|0光标

在存储过程和函数中，可以使用光标逐条读取查询结果集中可能出现的多条记录。光标必须声明在处理程序之前，声明在变量和条件之后

1. 声明光标

DECLARE cursor_name CURSOR
FOR select_statement;

• 参数 cursor_name 表示光标名称

• 参数 select_statement 表示 SELECT 语句

下面声明一个名为 cur_employee 的光标

DECLARE cur_employee CURSOR
FOR SELECT name,age FROM t_employee;

2. 打开光标

OPEN cursor_name;

• 参数 cursor_name 表示光标的名称

下面打开一个名为 cur_employee 的光标

OPEN cur_employee;

3. 使用光标

FETCH cursor_name
INTO var_name[,var_name...]

• cursor_name 表示光标的名称

• var_name 表示将光标中的 SELECT 语句查询出来的信息存入该参数，该参数必须在声明光标之前就定义好

下面打开一个名为 cur_employee 的光标，将查询的数据存入 emp_name 和 emp_age 这两个变量中

FETCH cur_employee INTO emp_name,emp_age;

4. 关闭光标

CLOSE cursor_name

• cursor_name 表示光标的名称

5|0流程控制

1. IF 语句

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list]...
 [ELSE statement_list]
END IF

• 参数 search_condition 表示条件判断语句

• 参数 statement_list 表示不同条件的执行语句

下面是一个 IF 语句的示例

IF age>20 THEN SET @count1=@conut1+1;
	ELSEIF age=20 THEN @count2=@conut2+1;
	ELSE @count3=@count3+1;
END IF;

2. CASE 语句

CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END CASE

• 参数 case_value 表示条件判断的变量

• 参数 when_value 表示变量的取值

• 参数 statement_list 表示不同 when_value 的执行语句

下面的是一个 CASE 语句的示例

CASE age
	WHEN 20 THEN SET @count1=@count1+1;
	ELSE SET @count2=@count2+1;
END CASE;

3. LOOP 语句

[begin_label:]LOOP
	statement_list
END LOOP [end_label]

• 参数 begin_label 和参数 end_label 分别表示循环开始和结束的标志，必须相同，而且都可以省略

• 参数 statement_list 表示需要循环执行的语句

下面是一个 LOOP 语句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
END LOOP add_num

4. LEAVE 语句

主要用于跳出循环

LEAVE label

• 参数 label 表示循环的标志

下面是一个 LEAVE 语句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
	IF @count1=100 THEN
		LEAVE add_num;
END LOOP add_num

5. ITERATE 语句

也是用于跳出循环，不过是跳出本次循环，直接进入下次循环，类似 Java 的 continue

ITERATE label

ITERATE label

• 参数 label 表示循环的标志

下面是一个 ITERATE 语句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
	IF @count1=100 THEN
		LEAVE add_num;
	ELSE IF MOD(@count1,3)=0 THEN
		ITERATE add_num;
	SELECT * FROM employee;
END LOOP add_num

6. REPEAT 语句

REPEAT 语句是由条件的控制循环语句，当满足特定条件就会退出循环语句

[begin_label:]REPEAT
	statement_list
	UNTIL search_condition
END REPEAT [end_label]

• 参数 statement_list 表示需要循环执行的语句

• 参数 search_condition 表示结束循环的条件，满足该条件即结束循环

下面是一个 REPEAT 语句的示例

add_num:REPEAT
	SELECT @count1=@count1+1;
	UNTIL @count1=100
END REPEAT add_num

7. WHILE 语句

WHILE 语句也是有条件控制的循环语句，与 REPEAT 语句不同的是，WHILE 语句是当满足条件时执行循环内的语句

[begin_label:]WHILE search_condition DO
	statement_list
END WHILE [end_label]

• 参数 statement_list 表示需要循环执行的语句

• 参数 search_condition 表示结束循环的条件，满足该条件即结束循环

下面是一个 WHILE 语句的示例

WHILE @count<100 DO
	SET @count=@count+1;
END WHILE;

6|0操作存储过程和函数

1. 调用存储过程

在 MySQL 中，使用 CALL 语句来调用存储过程

CALL proc_name([parameter[,...]])

• proc_name 是存储过程的名称

• paramter 是指存储过程的参数

2. 调用存储函数

在 MySQL 中，存储函数的使用方法和 MySQL 内部函数是一样的

3. 使用 SHOW STATUS 语句查看存储过程和函数的状态

SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}

• 参数 PROCEDURE 表示查询存储过程，FUNCTION 表示存储函数

• 参数 LIKE 'pattern' 用来匹配存储过程或函数的名称

4. 使用 SHOW CREATE 语句查看存储过程和函数的定义

SHOW CREATE {PROCEDURE|FUNCTION} proc_name

5. 从 information_schema.Routine 表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routine
WHERE ROUTINE_NAME='proc_name'

6. 修改存储过程和函数

ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
characteristic:
	{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
|COMMENT 'string'

• 参数 proc_name 表示存储过程或函数的名称

• 参数 characteristic 指定存储过程的特性：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句

• NO SQL 表示子程序不包含 SQL 语句

• READS SQL DATA 表示子程序中包含读数据的语句

• MODIFIES SQL DATA 表示子程序中包含写数据的语句

• SQL SECURITY{DEFINER|INVOKER} 指明谁有权限执行：

• DEFINER 表示只有定义者才能执行

• INVOKER 表示调用者可以执行

• COMMENT 'string' 是注释信息

7. 删除存储过程和函数

通过 DROP 语句删除存储过程

DROP PROCEDURE proc_name;

通过 DROP FUNCTION 语句删除存储函数

DROP FUNCTION func_name;

Das obige ist der detaillierte Inhalt vonSo erstellen Sie gespeicherte MySQL-Prozeduren und -Funktionen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!