Rumah > Artikel > pangkalan data > Bagaimana untuk mencipta prosedur dan fungsi tersimpan MySQL
Mencipta prosedur dan fungsi tersimpan adalah untuk menggabungkan satu set pernyataan SQL yang kerap digunakan bersama-sama dan menyimpan pernyataan SQL ini secara keseluruhan dalam pelayan MySQL
CREATE PROCEDURE procedure_name ([proc_param[,...]]) [characteristic ...] routine_body
procedure_name mewakili nama prosedur tersimpan yang akan dibuat
proc_param mewakili parameter prosedur tersimpan
ciri mewakili ciri prosedur tersimpan
rutin_body mewakili pernyataan SQL prosedur tersimpan
procedure_name tidak boleh diulang Bentuk sintaks setiap parameter dalam
proc_param adalah seperti berikut Setiap parameter terdiri daripada tiga bahagian iaitu jenis input/output, nama parameter dan jenis parameter. Terdapat tiga jenis input dan output iaitu IN, OUT, dan INOUT. param_name mewakili nama parameter, jenis mewakili jenis parameter
[ IN | OUT | INOUT ] param_name type
ciri menentukan ciri prosedur tersimpan dan mempunyai nilai berikut:
BAHASA SQL: menunjukkan bahawa prosedur tersimpan digunakan
[TIDAK] ditulis dalam bahasa SQL DETERMINISTIC: Menunjukkan sama ada keputusan pelaksanaan prosedur tersimpan adalah betul menunjukkan bahawa keputusan adalah pasti, iaitu setiap apabila prosedur tersimpan dilaksanakan, input yang sama akan mendapat hasil yang sama
{CONTAINS SQL |. NO SQL |. MEMBACA DATA SQL | menunjukkan bahawa subprogram tidak mengandungi SQL READS SQL DATA menunjukkan bahawa subprogram mengandungi pernyataan untuk membaca data, tetapi tidak termasuk Penyata untuk menulis data, MENGUBAH SUAI DATA SQL bermakna subrutin mengandungi pernyataan untuk menulis dataSQL SECURITY {DEFINER |.
DELIMITER $$ CREATE PROCEDURE proc_employee() COMMENT '查询员工薪水' BEGIN SELECT salary FROM t_employee END; $$ DELIMITER ;
MySQL menggunakan koma bertitik secara lalai sebagai penamat pernyataan, pernyataan SQL dalam prosedur tersimpan perlu diakhiri dengan koma bertitik , untuk mengelakkan konflik, gunakan DELIMITER $$ dahulu untuk menetapkan akhir aksara MySQL kepada $$, dan kemudian gunakan DELIMITER ; untuk memulihkan aksara akhir kepada koma bertitik
CREATE FUNCTION fun_name([func_param,[,...]]) [characteristic ...] routine_body
fun_name mewakili fungsi. nama
Masih contoh sebelumnya
DELIMITER $$ CREATE FUNCTION func_employee(id INT(4)) RETURNS INT(6) COMMENT '查询员工薪水' BEGIN SELECT salary FROM t_employee END; $$ DELIMITER ;
2|0 pembolehubahDalam MySQL, anda boleh mentakrifkan dan menggunakan pembolehubah , ini Skop pembolehubah adalah di bahagian BEGIN ... END program
1 Tentukan pembolehubah
DECLARE var_name[,...] type [DEFAULT value]
Kata kunci DECLARE digunakan untuk mengisytiharkan pembolehubah
.DECLARE test_sql INT DEFAULT 10;
2 Pembolehubah tugasan
SET var_name=expr[,var_name=expr]...
Kata kunci SET ialah. digunakan untuk memberikan nilai kepada pembolehubah
SET test_sql = 30;
DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE[VALUE] sqlstate_value|mysql_error_code2. Takrifkan pengendali
// 方法一:使用sqlstate_value DECLARE can_not_find CONDITION FOR SQLSTATE '42S02'; // 方法二:使用mysql_error_code DECLARE can_not_find CONDITION FOR 1146;
BUTUH: Menunjukkan bahawa anda akan menarik diri selepas menghadapi ralat sebelum ini 🎜>
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';
在存储过程和函数中,可以使用光标逐条读取查询结果集中可能出现的多条记录。光标必须声明在处理程序之前,声明在变量和条件之后
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;
OPEN cursor_name;
参数 cursor_name 表示光标的名称
下面打开一个名为 cur_employee 的光标
OPEN cur_employee;
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;
CLOSE cursor_name
cursor_name 表示光标的名称
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;
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;
[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
主要用于跳出循环
LEAVE label
参数 label 表示循环的标志
下面是一个 LEAVE 语句的示例
add_num:LOOP SELECT @count1=@count1+1; IF @count1=100 THEN LEAVE add_num; END LOOP add_num
也是用于跳出循环,不过是跳出本次循环,直接进入下次循环,类似 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
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
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;
在 MySQL 中,使用 CALL 语句来调用存储过程
CALL proc_name([parameter[,...]])
proc_name 是存储过程的名称
paramter 是指存储过程的参数
在 MySQL 中,存储函数的使用方法和 MySQL 内部函数是一样的
SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}
参数 PROCEDURE 表示查询存储过程,FUNCTION 表示存储函数
参数 LIKE 'pattern' 用来匹配存储过程或函数的名称
SHOW CREATE {PROCEDURE|FUNCTION} proc_name
SELECT * FROM information_schema.Routine WHERE ROUTINE_NAME='proc_name'
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' 是注释信息
通过 DROP 语句删除存储过程
DROP PROCEDURE proc_name;
通过 DROP FUNCTION 语句删除存储函数
DROP FUNCTION func_name;
Atas ialah kandungan terperinci Bagaimana untuk mencipta prosedur dan fungsi tersimpan MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!