存储过程
1. 创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
SQL语句
END;
2. 调用存储过程
CALL 名称([参数])
3. 查看存储过程
SELECT * FROM IMFORMATION_SCHEMA_ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; #查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称; #查询某个存储过程的定义
4. 删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
注意：在命令行中 执行创建存储过程的SQL语句时 需要通过关键子delimiter指定sql语句的结束符 否则会在BEGIN END的语句块中的sql语句作为结尾
变量
1. 系统变量
- 系统变量由Mysql服务器提供 不是用户定义 属于服务器层面 分为GLOBAL（全局变量和SESSION（会话变量 会话变量只针对当前客户端有效
查看系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '';
SELECT @@[SESSION|GLOBAL] 系统变量名; #查看指定变量的值
设置系统变量
SET [SESSION|GLOBAL] 系统变量名 = 值;
SET @@[SESSION|GLOBAL]系统变量名 = 值;
如果没有指定SESSION|GLOBAL 默认是会话变量SESSION
mysql服务器重新启动之后 所设置的全局参数会失效 可以在my.cnf或者my.ini中配置
2. 用户变量
- 用户定义变量是用户根据需要自己定义的变量 用户变量不用提前声明 在用的时候直接使用 @变量名 使用就可以 作用域为当前连接的客户端
赋值
SET @var_name = expression;
SET @var_name := expression;
SELECT @var_name := expression;
SELECT 字段名 INTO @var_name from 表名;
使用
SELECT @var_name;
例如
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
3. 局部变量
- 在存储过程中声明
begin
declare var_name var_type;
end
常用语法
1. if语句
begin
if 条件 then
```
elseif 条件 then
```
else
```
end if;
end
2. 传入参数
CREATE PROCEDURE 存储过程名称 ([IN/OUT/INOUT 参数名 参数类型])
BEGIN
```
END
INOUT : 即可作为输入参数 也可以作为输出参数
例如
create procedure p(in score int, out resule varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score>= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
call p(60, @p_result);
select @p_result; #查询返回结果
3. case
CASE case_value
WHEN when_value1 THEN statement_list1
WHEN when_value2 THEN statement_list2
ELSE default_statement_list
END CASE;
4. WHILE
BEGIN
while 条件 do
```
end while;
END;
5. REPEAT
BEGIN
repeat
```
until 条件
end repeat;
END;
例如
create procedure p(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n-1;
until n <= 0;
end repeat;
select total;
end;
6. 游标
- 游标 ：用来存储查询结果集的数据类型 在存储过程和函数中可以使用游标对结果集进行循环的处理
- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
- 打开游标
OPEN 游标名称;
- 获取游标记录
FETCH 游标名称 INTO 变量;
- 关闭游标
CLOSE 游标名称;
游标例子
7. 存储函数
- 存储函数是有返回值的存储过程 存储函数的参数只能是IN类型的
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic ...]
BEGIN
sql语句
RETURN ...;
END;
characteristic说明：
- DETERMINSTIC：相同的输入参数总是产生相同结果
- NO SQL：不包含SQL语句
- READ SQL DATA：包含读取数据的语句 但不包含写入数据的语句
