MySQL 第五章

解决存储过程中字符类型的返回值乱码的方法:
在声明的参数后面加上character set utf8
存储过程:相当于java中的方法，把一些常用的操作
写到存储过程中，便于循环使用
在doc命令中想把一个数据库中显示的存储过程
全部显示出来使用show procedure status\g;
\g可以美化视图
什么是存储过程
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象（与java中的方法相似）
存储过程优点：

• 封装处理，简化复杂的操作，简化对变动的管理
• 保证数据一致性
• 灵活，可编程性强，并且能完成较复杂的判断和运算
• 可以回传值，并可以接受参数。
• 提高性能，减少网络之间的数据传输，节省开销
• 权限划分

相对应的缺点：

• 编写比SQL语句复杂
• 性能调校与撰写，受限于各种数据库系统
• 移植性很差，切换数据库时，需要重新编写

存储过程中包含的元素：可带参数，也可返回结果，可包含数据操纵语句、变量、逻辑控制语句等
创建和调用存储过程:
创建存储过程：
语法:
CREATE PROCEDURE 过程名 (过程参数[,…]) [特性]————>可选项,用于设置存储过程的行为（可以省略不写） 存储过程体
过程体的标识
定义存储过程的过程体时，需要标识开始和结束
BEGIN 开始标签 具体操作 可以是sql语句
存储过程中的三种参数类型:
IN：指输入参数
该参数的值必须在调用存储过程时指定
存储过程中可以使用该参数，但它不能被返回
OUT：指输出参数
该参数可以在存储过程中发生改变，并可以返回
INOUT：指输入输出参数
该参数的值在调用存储过程时指定
在存储过程中可以被改变和返回
语法:
CREATE PROCEDURE 过程名 (in 参数名 数据类型,out 参数名 数据类型)
使用方法:
CREATE PROCEDURE 过程名 (in id int, out name varchar(50))
常见的特性:

用例：
delimiter // # 更改分隔符 mysql> create procedure porc_student_stNo() begin #过程体开始 select count() from student; end // #过程体结束
注意事项:如果没声明分隔符，编译器会把存储过程当成sql语句处理，这样会编译报错所以要修改分隔符
代码执行结果:

调用存储过程:
语法:
CALL 存储过程名([参数1,参数2, …]———>根据存储过程的定义包含相应的参数);
用例:
call porc_student_stNo();
运行结果:

存储过程中的变量(与Java语言类似，定义存储过程时可以使用变量):
声明变量的语法:
DECLARE 变量名[,变量名…] 数据类型 [DEFAULT 值]; 用例:DECLARE trade_time date DEFAULT ‘2020-07-10’;
注意：定义存储过程时，所有局部变量的声明一定要放在存储过程体的开始；否则，会提示语法错误
给变量赋值:
SET 变量名 = 表达式值[,变量名=表达式…] ; 用例:SET total=100;
具体用例:
BEGIN #创建变量 DECLARE pat_name varchar(50) character set utf8; #sql 语句 select patientName into pat_name from patient where patientID=stu_no; #输出变量中的值 select pat_name; #拿到变量做其他操作 select from patient where patientName=pat_name;
运行结果:
结果1

结果2

MySQL变量:
系统变量
指MySQL全局变量，以“@@”开头，形式为“@@变量名”
用户自定义变量
局部变量（定义在存储过程中的变量）
一般用于SQL的语句块中，如：存储过程中的BEGIN和END语句块
作用域仅限于定义该变量的语句块内
生命周期也仅限于该存储过程的调用期间
在存储过程执行到END时，局部变量就会被释放
会话变量（定义在存储过程中的参数）
是服务器为每个客户端连接维护的变量，与MySQL客户端是绑定的
也称作用户变量
可以暂存值，并传递给同一连接中其他SQL语句进行使用
当MySQL客户端连接退出时，用户变量就会被释放
用户变量创建时，一般以“@”开头，形式为“@变量名”
用户变量在存储过程间传递

变量的作用域
如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。
简单理解就是，作用范围在begin和end块之间，超过end就会失效。
Navicat中创建、调用存储过程
Navicat提供了良好的开发环境，比MySQL命令行操作更加便捷
创建和调用存储过程的基本步骤
创建存储过程
右键点击选定的数据库下的“函数”节点，在弹出的下拉菜单中选择“新建函数”
在右侧区域会自动创建存储过程模板，并在其中编写存储过程代码
运行存储过程
点击“保存”按钮，存储过程将自动保存在选定的数据库“函数”节点下
点击“运行”按钮调用存储过程
根据存储过程的定义，在弹出的对话框中输入设定的用户参数值
点击“确定”按钮，执行存储过程，并输出结果
设置用户执行存储过程的权限
通过DEFINER和SQL SECURITY特性控制存储过程的执行权限
语法:

DEFINER
默认DEFINER = CURRENT_USER
检查 ‘user_name’@’host_name’ 的权限
INVOKER
执行存储过程时，会检查调用者的权限
实列:
CREATE DEFINER = ‘admin’@’localhost’ PROCEDURE p1() SQL SECURITY DEFINER BEGIN UPDATE t1 SET counter = counter + 1; END;

CREATE DEFINER = ‘admin’@’localhost’ PROCEDURE p2() SQL SECURITY INVOKER BEGIN UPDATE t1 SET counter = counter + 1; END;

查看存储过程的状态:
在通过use命令指定数据库的情况
SHOW PROCEDURE STATUS;
没有选中数据库的情况
SHOW PROCEDURE STATUS WHERE DB = ‘hospital’;——>指定数据库名查询存储过程
使用LIKE关键字匹配存储过程名称
SHOW PROCEDURE STATUS LIKE ‘%patient%’;
查看存储过程的创建代码
SHOW CREATE PROCEDURE 存储过程名;
修改存储过程:
使用ALTER PROCEDURE语句修改创建存储过程时定义的特性
语法:
ALTER PROCEDURE 存储过程名 [特性……] ;
示例:
将存储过程proc_patient_countPatient的SQL SECURITY特性修改为INVOKER
ALTER PROCEDURE proc_patient_countPatient
注意:使用ALTER 关键字只能修改存储过程的特性，如果想修改存储过程中过程体的内容，需先删除该存储过程，再进行重新创建
在Navicat中，修改存储过程的内容后，可以直接保存
删除存储过程:
使用DROP PROCEDURE语句删除已创建的存储过程
DROP PROCEDURE 存储过程名;
示例:
删除已创建的存储过程proc_patient_countPatient
DROP PROCEDURE IF EXISTS proc_patient_countPatient;
注意:创建存储过程前，可以使用IF EXISTS语句检查其是否已存在,如果不存在，再进行创建
控制语言:
1.IF-ELSEIF-ELSE
语法:
if 条件 then 语句列表
[ELSEIF 条件 THEN 语句列表]
[ELSE 语句列表]
END IF;
用例:
CREATE DEFINER = niuma@localhost PROCEDURE proc_if_demo(IN num int,OUT results varchar(20) character set utf8) BEGIN #如果输入的参数 大于80 设置返回值为 优秀 if num>=80 then set results=’优秀’; elseif num>=60 then set results=’一般’; else set results=’不及格’; end if;
case语句:
语法:
CASE
WHEN 条件 THEN 语句列表
[WHEN 条件 THEN 语句列表]
[ELSE 语句列表]
END CASE;
用例:
#case 案例 DROP PROCEDURE IF EXISTS proc_when_demo; CREATE DEFINER = niuma@localhost PROCEDURE proc_when_demo(IN score int,OUT results varchar(20) character set utf8) BEGIN case score #拿到输入参数进行判断 #如果输入参数的值为80 设置返回值为优秀 when 80 then set results=’优秀’; when 60 then set results=’一般’; when 50 then set results=’差’; end case;
while语句:
[lable:] (相当与一个代表循环的记号名)WHILE 条件 DO
语句列表
END WHILE [lable]
用例:
#while 案例 DROP PROCEDURE IF EXISTS proc_while_demo; CREATE DEFINER = niuma@localhost PROCEDURE proc_while_demo(OUT sum int) BEGIN #计算1-10相加的和 declare num int default 1;#循环初始变量 1 set sum=0;#把用来存储和的返回值 归0 #写法1 loop_lable: while num<=10 do set sum=sum+num; #求和 set num=num+1; end while loop_lable; #写法2 #set sum=sum+num; #求和 #set num=num+1; #end while;
Loop循环
语法:
[lable:] (相当与一个代表循环的记号名)Loop
语句列表
END Loop [lable];
退出循环要用到：LEAVE lable;
用例:
#loop+leave案例 DROP PROCEDURE IF EXISTS proc_loop_demo; CREATE DEFINER = niuma@localhost PROCEDURE proc_loop_demo(OUT sum int) BEGIN #输出1-10的和 declare num int default 1; #设置循环初始化变量 set sum=0; #设置输出参数 默认为0 loop_lable: loop #指定循环模式为 loop循环 set sum=sum+num; #计算每次的和 set num=num+1; #循环次数累加 if num>=10 then #如果循环次数大于10 leave loop_lable;#停止循环 类似于Java中的break end if;#结束if end loop loop_lable;#结束循环 END;
REPEAT循环语句:
语法:
[lable:] REPEAT
语句列表
UNTIL 条件
END REPEAT[lable]
用例:
#repeat 案例 DROP PROCEDURE IF EXISTS proc_repeat_demo; CREATE DEFINER = niuma@localhost PROCEDURE proc_repeat_demo(OUT sum int) BEGIN #求1-10的和 declare num int default 1; set sum=0; repeat set sum=sum+num; set num=num+1; until num>=10 #循环做求和操作 直接num>=10 结束循环 end repeat; END;
常用方法:
1、DELIMITER //或者DELIMITER $$ 声明语句分隔符 2、CREATE PROCEDURE 过程名 ([过程参数[,…]]) 创建存储过程 BEGIN (过程体开始) 存储过程体 END (过程体结束) 3、CALL 存储过程名([参数1,参数2, …]); 调用存储过程 4、[IN | OUT | INOUT] 参数名 数据类型 ；定义存储过程的参数 5、DECLARE 变量名[,变量名…] 数据类型 [DEFAULT 值];存储过程中的变量 6、SET 变量名=值； 7、[SQL SECURITY { DEFINER | INVOKER } | …]指定DEFINER或INVOKER权限 8、SHOW PROCEDURE STATUS;查看存储过程的状态 9、SHOW CREATE PROCEDURE 存储过程名; 查看存储过程的创建代码 10、ALTER PROCEDURE 存储过程名 [特性……] ;修改存储过程 11、DROP PROCEDURE 存储过程名;删除存储过程 12、IF 条件 THEN 语句列表[ELSEIF 条件 THEN 语句列表] [ELSE 语句列表] END IF; IF-ELSEIF-ELSE条件语句 13、[label:] WHILE 条件 DO语句列表 END WHILE [label]WHILE循环语句 14、[label:] REPEAT语句列表UNTIL 条件END REPEAT [label] REPEAT循环语句
存储过程相关连接:
https://blog.csdn.net/whf_a/article/details/114871866?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163712718116780269877413%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=163712718116780269877413&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_click~default-3-114871866.first_rank_v2_pc_rank_v29&utm_term=%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B&spm=1018.2226.3001.4187