沈阳做网络推广的公司,关键字优化,2023新冠结束了吗,网站开发简易软件文章目录 1. 什么是存储过程#xff1f;2. 存储过程的优点3. MySQL中的变量3.1 系统变量3.2 用户自定义变量3.3 局部变量 4. 存储过程的相关语法4.1 创建存储过程#xff08;CREATE#xff09;4.2 查看存储过程#xff08;SHOW#xff09;4.3 修改存储过程#xff08;ALT… 文章目录 1. 什么是存储过程2. 存储过程的优点3. MySQL中的变量3.1 系统变量3.2 用户自定义变量3.3 局部变量 4. 存储过程的相关语法4.1 创建存储过程CREATE4.2 查看存储过程SHOW4.3 修改存储过程ALTER4.4 删除存储过程DELETE4.5 调用存储过程CALL 5. 参数6. 流程控制语句6.1 IF6.2 CASE6.3 WHILE6.4 REPEAT6.5 LOOP 7. 游标CURSOR8. 定义条件和处理程序8.1 定义条件8.2 处理程序 9. 存储函数 1. 什么是存储过程
存储过程是一组为了完成特定功能的 SQL 语句集合。**使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来这个过程经编译和优化后存储在数据库服务器中因此称为存储过程。**当以后需要数据库提供与已定义好的存储过程的功能相同的服务时只需调用“CALL 存储过程名字”即可自动完成。
2. 存储过程的优点
封装性 通常完成一个逻辑功能需要多条 SQL 语句而且各个语句之间很可能传递参数所以编写逻辑功能相对来说稍微复杂些而存储过程可以把这些 SQL 语句包含到一个独立的单元中使外界看不到复杂的 SQL 语句只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改而不会影响到调用它的应用程序源代码。 可增强 SQL 语句的功能和灵活性 存储过程可以用流程控制语句编写有很强的灵活性可以完成复杂的判断和较复杂的运算。 可减少网络流量 由于存储过程是在服务器端运行的且执行速度快因此当客户计算机上调用该存储过程时网络中传送的只是该调用语句从而可降低网络负载。 高性能 当存储过程被成功编译后就存储在数据库服务器里了以后客户端可以直接调用这样所有的 SQL 语句将从服务器执行从而提高性能。但需要说明的是存储过程不是越多越好过多的使用存储过程反而影响系统性能。 提高数据库的安全性和数据的完整性 存储过程提高安全性的一个方案就是把它作为中间组件存储过程里可以对某些表做相关操作然后存储过程作为接口提供给外部程序。这样外部程序无法直接操作数据库表只能通过存储过程来操作对应的表因此在一定程度上安全性是可以得到提高的。 使数据独立 数据的独立可以达到解耦的效果也就是说程序可以调用存储过程来替代执行多条的 SQL 语句。这种情况下存储过程把数据同用户隔离开来优点就是当数据表的结构改变时调用表不用修改程序只需要数据库管理者重新编写存储过程即可。
3. MySQL中的变量
在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
3.1 系统变量
系统变量 是MySQL服务器提供不是用户定义的属于服务器层面。分为全局变量GLOBAL、会话变量SESSION。 查看系统变量 SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE ......; -- 可以通过LIKE模糊匹配方式查找变量
SELECT [SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值设置系统变量 : SET [ SESSION | GLOBAL ] 系统变量名 值 ;
SET [SESSION | GLOBAL]系统变量名 值 ;注意事项 如果没有指定SESSION/GLOBAL默认是SESSION会话变量。全局变量(GLOBAL): 全局变量针对于所有的会话。会话变量(SESSION): 会话变量针对于单个会话在另外一个会话窗口就不生效了。
3.2 用户自定义变量 用户定义变量 是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用 “变量名” 使用就可以。其作用域为当前连接。 为自定义变量赋值 SET var_name expr [, var_name expr] ... ;
SET var_name : expr [, var_name : expr] ... ;SELECT var_name : expr [, var_name : expr] ... ;
SELECT 字段名 INTO var_name FROM 表名;使用自定义变量 SELECT var_name ;3.3 局部变量 局部变量是根据需要定义的在局部生效的变量访问之前需要DECLARE声明。可用作存储过程内的局部变量和输入参数局部变量的范围是在其内声明的BEGIN … END块。 声明局部变量 DECLARE 变量名 变量类型 [DEFAULT ... ] ;赋值 SET 变量名 值 ;
SET 变量名 : 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;4. 存储过程的相关语法
4.1 创建存储过程CREATE 可以使用 CREATE PROCEDURE 语句创建存储过程语法格式如下 CREATE PROCEDURE 过程名 ( [过程参数[,…] ] ) 过程体
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] 参数名 类型create procedure p1()
beginselect count(*) from student;
end;过程名 存储过程的名称默认在当前数据库中创建。若需要在特定数据库中创建存储过程则要在名称前面加上数据库的名称即 db_name.sp_name。需要注意的是名称应当尽量避免选取与 MySQL 内置函数相同的名称否则会发生错误。 过程参数 存储过程的参数列表。其中 参数名为参数名 类型为参数的类型可以是任何有效的 MySQL 数据类型。当有多个参数时参数列表中彼此间用逗号分隔。存储过程可以没有参数此时存储过程的名称后仍需加上一对括号也可以有 1 个或多个参数。MySQL 存储过程支持三种类型的参数即输入参数、输出参数和输入/输出参数分别用 IN、 OUT 和 INOUT 三个关键字标识。其中输入参数可以传递给一个存储过程输出参数用于存储过程需要返回一个操作结果的情形而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是参数的取名不要与数据表的列名相同否则尽管不会返回出错信息但是存储过程的 SQL 语句会将参数名看作列名从而引发不可预知的结果。 过程体 存储过程的主体部分也称为存储过程体包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始以关键字 END 结束。若存储过程体中只有一条 SQL 语句则可以省略 BEGIN-END 标志。 在 MySQL 中服务器处理 SQL 语句默认是以分号作为语句结束标志的在命令行中执行创建存储过程的SQL时需要通过关键字 DELIMITET 指定SQL语句的结束符。 DELIMITER $$4.2 查看存储过程SHOW 查看存储过程的状态 SHOW PROCEDURE STATUS LIKE 存储过程名;查看存储过程的定义 SHOW CREATE PROCEDURE 存储过程名;查询指定数据库的存储过程及状态信息 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA 数据库名;4.3 修改存储过程ALTER MySQL 中修改存储过程的语法格式如下 ALTER PROCEDURE 存储过程名 [ 特征 ... ] 特征指定了存储过程的特性可能的取值有 CONTAINS SQL 表示子程序包含 SQL 语句但不包含读或写数据的语句。NO SQL 表示子程序中不包含 SQL 语句。READS SQL DATA 表示子程序中包含读数据的语句。MODIFIES SQL DATA 表示子程序中包含写数据的语句。SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行。 INVOKER 表示调用者可以执行。COMMENT ‘string’ 表示注释信息。 ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容可以先删除原存储过程再以相同的命名创建新的存储过程 。
4.4 删除存储过程DELETE MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下 DROP PROCEDURE [ IF EXISTS ] 过程名4.5 调用存储过程CALL MySQL 中使用 CALL 语句来调用存储过程。调用存储过程后数据库系统将执行存储过程中的 SQL 语句并将结果返回给输出值。 CALL procedure_name([parameter[...]]);5. 参数 存储过程的参数的类型主要分为以下三种IN、OUT、INOUT。 具体的含义如下 类型含义备注IN该类参数作为输入也就是需要调用时传入值默认OUT该类参数作为输出也就是该参数可以作为返回值INOUT既可以作为输入参数也可以作为输出参数 案例1根据传入参数score判定当前分数对应的分数等级并返回。score 85分等级为优秀。score 60分 且 score 85分等级为及格。score 60分等级为不及格。 CREATE PROCEDURE p2(in score int ,out result varchar(10))
BEGINIF score 85 THENSET result 优秀;ELSEIF score 60 THENSET result 中等;ELSESET result 不及格;end IF;
ENDcall p2(90,result);
SELECT result;案例2将传入的200分制的分数进行换算换算成百分制然后返回。 create procedure p5(inout score double)
beginset score : score * 0.5;
end;set score 198;
call p5(score);
select score;6. 流程控制语句
在存储过程和自定义函数中可以使用流程控制语句来控制程序的流程。 MySQL 中流程控制语句有 IF 语句、 CASE 语句、 LOOP 语句、 LEAVE 语句、 ITERATE 语句、 REPEAT 语句和 WHILE 语句等。
6.1 IF IF 语句用来进行条件判断根据是否满足条件可包含多个条件来执行不同的语句是流程控制中最常用的判断语句。其语法的基本形式如下 IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list]...
[ELSE statement_list]
END IF案例根据定义的分数score变量判定当前分数对应的分数等级。score 85分等级为优秀。score 60分 且 score 85分等级为及格。score 60分等级为不及格。 create procedure p3()
begindeclare score int default 58;declare result varchar(10);if score 85 thenset result : 优秀;elseif score 60 thenset result : 及格;elseset result : 不及格;end if;select result;
end;
call p3();6.2 CASE CASE 语句也是用来进行条件判断的它提供了多个条件进行选择可以实现比 IF 语句更复杂的条件判断。 CASE 语句的基本形式如下 CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE案例根据传入的月份判定月份所属的季节要求采用case结构。 1-3月份为第一季度。4-6月份为第二季度。7-9月份为第三季度。10-12月份为第四季度 。 create procedure p6(in month int)
begindeclare result varchar(10);casewhen month 1 and month 3 thenset result : 第一季度;when month 4 and month 6 thenset result : 第二季度;when month 7 and month 9 thenset result : 第三季度;when month 10 and month 12 thenset result : 第四季度;elseset result : 非法参数;end case ;select concat(您输入的月份为: ,month, , 所属的季度为: ,result);
end;
call p6(16);6.3 WHILE WHILE 语句也是有条件控制的循环语句。 WHILE 语句和 REPEAT 语句不同的是 WHILE 语句是当满足条件时执行循环内的语句否则退出循环。 WHILE 语句的基本语法形式如下 [begin_label:] WHILE search_condition DO
statement list
END WHILE [end label]案例计算从1累加到n的值n为传入的参数值。 create procedure p7(in n int)
begindeclare total int default 0;while n0 doset total : total n;set n : n - 1;end while;select total;
end;
call p7(100);6.4 REPEAT REPEAT 语句是有条件控制的循环语句每次语句执行完毕后会对条件表达式进行判断如果表达式返回值为 TRUE则循环结束否则重复执行循环中的语句。REPEAT 语句的基本语法形式如下 [begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]案例计算从1累加到n的值n为传入的参数值。(使用repeat实现) 。 create procedure p8(in n int)
begindeclare total int default 0;repeatset total : total n;set n : n - 1;until n 0end repeat;select total;
end;
call p8(10);6.5 LOOP LOOP 实现简单的循环如果不在SQL逻辑中增加退出循环的条件可以用其来实现简单的死循环。 [begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];LOOP可以配合一下两个语句使用 LEAVE 配合循环使用退出循环。ITERATE必须用在循环中作用是跳过当前循环剩下的语句直接进入下一次循环。 案例计算从1到n之间的偶数累加的值n为传入的参数值。 create procedure p10(in n int)
begindeclare total int default 0;sum:loopif n0 thenleave sum;end if;if n%2 1 thenset n : n - 1;iterate sum;end if;set total : total n;set n : n - 1;end loop sum;
select total;
end;
call p10(100);7. 游标CURSOR
游标CURSOR是用来存储查询结果集的数据类型 , 在存储过程和存储函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE其语法分别如下。 声明游标 DECLARE 游标名称 CURSOR FOR 查询语句 ;打开游标 OPEN 游标名称 ;获取游标记录 FETCH 游标名称 INTO 变量 [, 变量 ] ;关闭游标 CLOSE 游标名称 ;案例根据传入的参数uage来查询用户表tb_user中所有的用户年龄小于等于uage的用户姓名name和专profession并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。 -- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name,profession from tb_user where age uage;drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname,upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;
end;
call p11(30);上述代码中循环的控制还需使用定义条件和处理程序进一步优化。
8. 定义条件和处理程序
定义条件是指事先定义程序执行过程中遇到的问题处理程序定义了在遇到这些问题时应当采取的处理方式和解决办法保证存储过程和函数在遇到警告或错误时能继续执行从而增强程序处理问题的能力避免程序出现异常被停止执行。
8.1 定义条件 MySQL 中可以使用 DECLARE 关键字来定义条件。其基本语法如下 DECLARE condition_name CONDITION FOR condition_value
condition value:
SQLSTATE [VALUE] sqlstate_value | mysql_error_codecondition_name 参数表示条件的名称 condition_value 参数表示条件的类型 sqlstate_value 参数和 mysql_error_code 参数都可以表示 MySQL 的错误。 sqlstate_value 表示长度为 5 的字符串 类型错误代码 mysql_error_code 表示数值类型错误代码。例如 ERROR 1146(42S02) 中 sqlstate_value 值是42S02,mysql_error_code 值是 1146。 案例下面定义“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;8.2 处理程序 MySQL 中可以使用 DECLARE 关键字来定义处理程序。其基本语法如下 DECLARE handler_type HANDLER FOR condition_value[...] sql_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 中暂时还不支持这种处理方式。 参数指明错误类型该参数有 6 个取值 更多操作sqlstate_value包含 5 个字符的字符串错误值 condition_name表示 DECLARE 定义的错误条件名称 SQLWARNING匹配所有以 01 开头的 sqlstate_value 值 NOT FOUND匹配所有以 02 开头的 sqlstate_value 值 SQLEXCEPTION匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 sqlstate_value 值 mysql_error_code匹配数值类型错误代码。 对游标案例进行优化 -- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name,profession from tb_user where age uage;-- 声明条件处理程序 当SQL语句执行抛出的状态码为02000时将关闭游标u_cursor并退出declare exit handler for SQLSTATE 02000 close u_cursor;drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname,upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;
end;
call p11(30);9. 存储函数 存储函数是有返回值的存储过程存储函数的参数只能是IN类型的。具体语法如下 CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;characteristic说明在mysql8.0版本中binlog默认是开启的一旦开启了mysql就要求在定义存储过程时需要指定characteristic特性否则就会报错 DETERMINISTIC相同的输入参数总是产生相同的结果。NO SQL 不包含 SQL 语句。READS SQL DATA包含读取数据的语句但不包含写入数据的语句。 案例计算从1累加到n的值n为传入的参数值。 create function fun1(n int)
returns int deterministic
begindeclare total int default 0;while n0 doset total : total n;set n : n - 1;end while;return total;
end;
select fun1(50);
