城市文化建设的网站,福海网站制作,济南网站建设多少费用,链接生成1.概述 存储程序可以封装一些语句#xff0c;为用户提供一种简单的方式来调用这个存储程序#xff0c;从而间接执行其封装的语句。 根据调用方式的不同#xff0c;可把存储程序分为存储例程、触发器、事件几种类型。其中#xff0c;存储例程又可被细分为存储函数和存储过程…1.概述 存储程序可以封装一些语句为用户提供一种简单的方式来调用这个存储程序从而间接执行其封装的语句。 根据调用方式的不同可把存储程序分为存储例程、触发器、事件几种类型。其中存储例程又可被细分为存储函数和存储过程。
2.用户自定义变量 MYSQL中可通过SET语句来自定义一些自己的变量。 如SET a 1; 在我们的自定义变量前面必须加一个符号。 在使用SET语句时如变量名前没有加符号则MYSQL会把这个变量当作系统变量来对待。 之后想查看这个变量的值使用SELECT 变量名; 如SELECT a; 同一个变量也可存储不同类型的值。如再把一个字符串赋给变量a。 如SET a ‘哈哈哈’; 除了把一个常量赋给一个变量也可把一个变量赋给另一个变量。 如SET b a; 变量ab是独立的。此后a改变不会影响b。
当某个查询的结果集是一行一列时可将查询结果集赋给变量。 如SET a (SELECT m1 FROM t1 LIMIT 1); 也可用INTO子句完成类似功能。 如SELECT n1 FROM t1 LIMIT 1 INTO b;效果等价于SET b (SELECT n1 FROM t1 LIMIT 1);
当某个查询的结果集是一行多列。如想将结果集中各列赋值给不同的变量不能用SET只能用INTO。 如SELECT m1, n1 FROM t1 LIMIT 1 INTO a, b;
3.存储函数 存储程序可以分为存储例程触发器事件几种类型。 存储例程需我们去手动调用触发器和事件都是MySQL服务器在特定条件下自己调用的。 存储例程又可分为存储函数存储过程。
3.1.创建存储函数 存储函数是一种函数在函数定义中书写MySQL语句。
在MySQL中定义存储函数的语句如下
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN函数体内容
END定义一个存储函数时需指定存储函数名称参数列表返回值类型及函数体内容。 如该函数不需参数则参数列表可省略。 函数体内容被包裹在BEGIN … END中可包括一条或多条语句每条语句都要以分号;结尾。 上述语句中空格换行也可略去。不会影响含义。
实例
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGINRETURN (SELECT AVG(score) FROM student_score WHERE subject s);
END由于MySQL默认将;视为语句结束符。 所以执行上述函数定义应该这样写
DELIMITER $
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGINRETURN (SELECT AVG(score) FROM student_score WHERE subject s);
END $DELIMITER ;上述通过DELIMITER将MySQL的语句结束符临时修改为$之后再修改回来。
有时要创建函数可能得设置下SET global log_bin_trust_function_creators TRUE;
3.2.存储函数的调用 函数调用可单独使用也可作为一个操作数与其他操作数组成复杂的表达式。 如SELECT avg_score(‘MySQL是怎样运行的’);
3.3.查看和删除存储函数 如SHOW FUNCTION STATUS [LIKE 需要匹配的函数名];
查看函数定义如SHOW CREATE FUNCTION 函数名\G 删除存储函数如DROP FUNCTION 函数名;
3.4.函数体的定义 3.4.1.在函数体中定义局部变量 如果我们想在存储函数的函数体中使用变量的话必须提前使用DECLARE语句声明该变量。具体语法 DECLARE 变量名1, 变量名2, … 数据类型 [DEFAULT 默认值]; 这些在函数体内声明的变量只在该函数体内有用当存储函数执行完成后就不能访问这些变量了。所以这些变量也称为局部变量。
可在一条语句中声明多个相同类型的变量。 注意的是函数体中的局部变量不允许加前缀除非使用反引号将变量名引起来这一点与之前直接使用SET语句自定义变量截然不同。在声明了这个局部变量后在可使用它。
DELIMITER $
CREATE FUNCTION var_demo()
RETURNS INT
BEGINDECLARE c INT;SET c5;RETURN c;
END $如果不对声明的局部变量进行赋值它的默认值就是NULL当然也可通过DEFAULT子句来显式地指定局部变量的默认值比如
DELIMITER $
CREATE FUNCTION var_default_demo()
RETURNS INT
BEGINDECLARE c INT DEFAULT 1;RETURN c;
END $
DELIMITER ;与用户自定义变量类似也可把一个查询的结果赋给局部变量。
DELIMITER $
CREATE FUNCTION var_default_demo()
RETURNS DOUBLE
BEGINDECLARE c DOUBLE DEFAULT 1.0;SET c (SELECT AVG(score) FROM student_score WHERE subject s);return c;
END $
DELIMITER ;3.4.2.在函数体中使用用户自定义变量
DELIMITER $
CREATE FUNCTION user_defined_var_demo()
RETURNS INT
BEGINSET abc 10;RETURN abc;
END $
DELIMITER ;3.4.3.存储函数的参数 在定义存储函数的时候可以指定多个参数且每个参数都要指定对应的数据类型。 形参名不应与函数体语句中的其他变量中列名冲突。形参不支持提供默认值。 形参函数体内定义的参数均为局部变量。局部变量使用时候必须加前缀。全局变量使用时必须加前缀。 函数体内需要定义局部变量时局部变量定义必须出现在函数体内语句的最前方。 函数体内对形参的修改不会传递到外部实参。
3.4.4.判断语句的编写 在存储函数的函数体中可使用判断语句。
IF 表达式 THEN语句列表
[ELSEIF 表达式 THEN 语句列表]
...
[ELSE 语句列表]
END IF;如
DELIMITER $
CREATE FUNCTION condition_demo(i INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE result VARCHAR(10);
IF i 1 THENSET result 结果是1;
ELSEIF i 2 THENSET result结果是2;
ELSEIF i 3 THENSET result结果是3;
ELSESET result非法参数;
END IF;
RETURN result;
END $DELIMITER ;3.4.5.循环语句的编写 (1).WHILE循环语句 WHILE 表达式 DO 语句列表 END WHILE;
DELIMITER $
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;WHILE i n DOSET result result i;SET i i 1;END WHILE;RETURN result;
END $DELIMITER ;这样执行SELECT sum_all(3);结果将是6。
(2).REPEAT语句
REPEAT语句列表
UNTIL 表达式 END REPEAT表达式为真时跳出循环。
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;REPEATSET result result i;SET i i 1;UNTIL i n END REPEAT;RETURN result;
END(3).LOOP循环语句
LOOP 语句列表
END LOOP;LOOP下要么在语句列表通过RETURN实现返回要么通过LOOP添加标志与LEAVE的方式离开。
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;LOOPIF i n THENRETURN result;END IF;SET result result i;SET i i 1;END LOOP;
END上述为通过RETURN实现返回
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGINDECLARE result INT DEFAULT 0;DECLARE i INT DEFAULT 1;flag:LOOPIF i n THENLEAVE flag;END IF;SET result result i;SET i i 1;END LOOP flag;RETURN result;
END4.存储过程 存储函数和存储过程都属于存储例程都是对某些语句的一个封装。存储函数会给调用它的用户返回一个结果但存储过程没返回值。
4.1.创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN需要执行的语句
END存储过程不需声明返回值类型也无法返回值。
DELIMITER $
CREATE PROCEDURE t1_operation(m1_value INT, n1_value CHAR(1))
BEGINSELECT * FROM t1;INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);SELECT * FROM t1;
END $4.2.存储过程的调用 如果我们需调用某个存储过程需显式使用CALLCALL 存储过程([参数列表]); 针对上述过程这样调用CALL t1_operation(4, d);
4.3.查看和删除存储过程 查看SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]; 查看存储过程定义SHOW CREATE PROCEDURE 存储过程名称; 删除存储过程DROP PROCEDURE 存储过程名称;
4.4.存储过程中的语句 存储函数中使用的各种语句都可用在存储过程中。
4.5.存储过程的参数前缀 比存储函数强大点的是存储过程在定义参数的时候可选择添加一些前缀[IN | OUT | INOUT] 参数名 数据类型 IN参考高级语言值传参OUT参考高级语言引用传参。 (1).IN
DELIMITER $
CREATE PROCEDURE p_in(IN arg INT)
BEGINSELECT arg;SET arg 123;
END $
DELIMITER ;上述采用IN修饰形参
SET a 1;
CALL p_in(a);
SELECT a; // 这里a仍然为1对IN修饰的形参可以传递常量实参。
(2).OUT
DELIMITER $
CREATE PROCEDURE p_out(OUT arg INT)
BEGINSELECT arg;SET arg 123;
END $
DELIMITER ;上述采用OUT修饰形参
SET b 2;
CALL p_out(b);// 这里内部执行SELECT输出值将是NULL因为OUT修饰的形参不可用于读取值。只能被设置值。
SELECT b; // 这里b变为123对OUT修饰的形参不可以传递常量实参。
(3).INOUT INOUT修饰的变量即可在过程内部被读取又可被设置值且值会影响到外部实参。 如果不写明参数前缀默认前缀是IN。
4.5.存储函数与存储过程异同 (1).存储函数定义时需通过RETURNS指定返回类型函数体中需用RETURN指定返回值存储过程不需要。 (2).存储函数形参不支持INOUTINOUT修饰存储过程支持。 (3).存储函数只可返回一个值存储过程可通过OUT或INOUT返回零个或多个。 (4).存储函数不允许在函数内执行单独的selectselect xxx into xxx;这样利用select作为中间结果的允许存储过程执行过程允许支持单独的select且产生的结果集会显示在客户端。 (5).存储函数以函数调用形式调用存储过程使用CALL形式。
