一. 对待存储过程和函数的态度
在实际项目中应该尽量少用存储过程和函数, 理由如下:
1. 移植性差, 在 MySQL 中的存储过程移植到 sqlsever 上就不一定可以用了.
2. 调试麻烦, 在 db 中报一个错误和在应用层报一个错误不是一个概念, 那将是毁灭性打击, 直接一个 error:1045 什么的更本毫无头绪.
3. 扩展性不高.
所以在互联网时代大型项目应该尽量少使用 (不使用) 存储过程和函数.
二. 创建存储过程
2.1 什么是存储过程?
存储过程和存储函数都是一组 sql 语句的集合. 这些语句集合被当做一个整体存入数据库中.
2.2 创建存储过程的语法:
create procedure 存储过程名(参数列表)
sql 语句
例子:
- delimiter //
- create procedure pro()
- reads sql data
- begin
- select * from stu;
- end
- //
那么我们现在就有一个存储过程 pro 了, 但是这个存储过程他是没有参数的, 他只是执行一次查询操作.
我们现在来讲解一下这个存储过程的结构:
delimiter // 是将分号转化为 // 因为在 sql 执行时当他遇到分号 ; 时他就讲停止所以我们必须将其转化为 // 直到最后一行才会停止执行.
reads sql data 解释 characteristic 的状态在这里是只读模式, 其他的模式还有: no sql 没有 sql 语句 , ins sql 不包含读和写的语句 , modifies sql data 包含写入数据的语句等等.
begin /***/ end 在存储过程中当有多条语句集合时我们必须使用 begin 和 end
// 结束整个存储过程
2.3 使用存储过程
在只是创建了一个存储过程, 那么我们怎么来使用这个存储过程呢?
语法: call 存储过程名()
将上一个存储过程 pro 使用的例子:
call pro();
2.4 创建一个带参数的存储过程
参数列表: 存储过程的参数有三种类型: in,out,inout 分别表示传入参数和传出参数, 和即传入也传出参数.
例子: 首先我们来创建两张表: 课程表是学生表的从表
- create table stu(
- stu_id bigint primary key auto_increment,# 学号
- stu_name varchar(10) not null,# 姓名
- stu_major int not null,# 专业号
- stu_sex char,# 性别
- stu_in date,# 入学日期
- stu_birth date,# 出生日期
- foreign key (stu_major) references major(ma_id)# 专业外键设置
- );
- create table major(
- ma_id int primary key,
- ma_name varchar(15),
- ma_boss varchar(10)
- );
- insert into major values(1,"信管","张三");
- insert into major values(2,"电子商务","李四");
- insert into stu values(1,"小明",1,"男","2017-09-01","1998-12-23");
- insert into stu values(2,"小高",1,"男","2017-09-01","1998-05-01");
- insert into stu values(3,"小李",2,"男","2017-09-01","1999-04-01");
我们再来创建一个带有参数的存储过程找到学生的主修课的名字, 代码如下:
- delimiter //
- create procedure pro1(in sname varchar(10),out ma varchar(10))
- reads sql data
- begin
- select ma_name into ma from major where ma_id = (select stu_major from stu where stu_name=sname);
- end
- //
使用这个存储过程: 代码如下:
- set @ma="没查询之前";
- call pro1("小李",@ma);
- select @ma;
结果如下:
解释一下代码: 首先使用 set @ma 定义一个全局变量, 然后在使用 call 存储过程名语法调用存储过程, 同时全局变量 ma 的值也改变了.
三. 创建一个存储函数
3.1 存储过程和存储函数的不同.
1. 在函数中必须要有 return 返回值
2. 在存储过程中参数有 in out inout 三种, 默认为 in 类型, 但是在函数中只有一种 in 类型.
3.2 创建一个函数
语法: create function 函数名()
return 返回类型
sql 语句集合
例子:
- delimiter //
- create function fun1(num int)
- returns int
- begin
- return num+1000;
- end
- //
显然函数与存储过程的最大的区别就是在于 return
3.3 调用函数
使用语法不在使用关键字 call, 而是关键字 select ,select 函数名
例子:
select fun1(100);
结果:
四. 删除存储函数和存储过程
语法: drop procedure | function 存储过程名或者是函数名
例子:
drop procedure pro;
注意他是不带括号的
五. 在存储过程和存储函数中使用游标
5.1 为什么需要游标?
当我们在使用存储过程的时候可能用到多条数据, 那么我们就需要用到游标来存放多条数据.
5.2 使用游标的注意点
游标不能单独存在, 必须在存储过程或者是存储函数中使用.
5.3 使用游标
语法:
1. 创建游标: declare 游标名 cursor for select 语句
2. 打开游标: open 游标名
3. 使用游标: fetch 游标名 into 变量名
4. 关闭光标: close 游标名
5.4 例子
- delimiter //
- create function fun3(id int)
- returns int
- reads sql data
- begin
- declare cur cursor for select stu_id from stu;
- open cur;
- fetch cur into id;
- close cur;
- return id;
- end
- //
使用
- set @id=0;
- select fun3(@id);
结果
可以发现游标只是将第一个值给了变量.
来源: https://www.cnblogs.com/SAM-CJM/p/9711458.html