存储过程
存储过程概述
1.产生背景
- 开发过程总,经常会遇到重复使用某一功能的情况
2.解决办法
- MySQL引人了存储过程(Stored Procedure)这一技术
3.存储过程
- 存储过程就是一条或多条SQL语句的集合
- 存储过程可将一系列复杂操作封装成一个代码块,以便重复使用,从而减少工作量提升开发效率。
4.编译
- SQL语句需先编译然后执行。
- 存储过程将为了完成特定功能的SQL语句集,经编译后存储在数据库中。
4.使用
- 通过指定存储过程的名字通过传参的方式对其进行调用。
- 存储过程可看做是编程的函数,它允许以传参调用的访问方式。
存储过程入门案例
1.准备数据
- 创建数据库
- 创建学生表
- 插入数据
DROP TABLE IF EXISTS student;
CREATE TABLE student (
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50) DEFAULT 'male'
);
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
2.编写存储过程
-- 改存储过程中依据指定年纪查询出符合条件的学生
delimiter //
create procedure procedureDemo01(in sage int)
begin
select * from student where age>sage;
end //
delimiter ;
3.关键词解释
(1)DELIMITER //
-
声明当前段分隔符
-
让编译器把两个
//
之间的内容当做存储过程的代码 -
MySQL 默认以 **;**为分隔符
- 若没有声明分隔符,则编译器会把存储过程当成SQL语句进行处理,从而造成编译过程会报错
(2)DELIMITER ;
-
把分隔符还原为默认分隔符
;
-
DELIMITER也可以指定其他符号作为结束符
-
注意:DELIMITER与要设定的结束符之间一定要有一个空格,否则设定无效
(3)CREATE PROCEDURE 存储过程名称(参数)
- 创建存储过程
(4)BEGIN END
-
过程体文章来源:https://www.toymoban.com/news/detail-685403.html
-
存储过程的过程体以BEGIN开始以END结束文章来源地址https://www.toymoban.com/news/detail-685403.html
4.调用存储过程
-
call 存储过程名(参数);
调用存储过程
call procedureDemo01(15);
5.删除存储过程
-
DROP PROCEDURE 存储过程名;
删除存储过程
drop procedure procedureDemo01;
存储过程的参数
- 存储过程定义中可传参,可不传参
1.传参分类
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])
2.IN
- 输入参数,表示该参数的值必须在调用存储过程时指定
-- findStudentByAge,该存储过程中有个IN参数,该参数为INT类型叫做sage
delimiter //
create procedure findStudentByAge(in sage int)
begin
select * from student where age=sage;
end //
delimiter ;
-- 调用存储过程
call findStudentByAge(16);
3.用户变量
(1)语法
@var_name
(2)创建用户变量
SET 用户变量=初始值;
(3)查看用户变量值
SELECT 用户变量;
(4)注意
- 定义用户变量后可方便开发过程的代码编写,只要连接未关闭我们均可直接使用该变量。
- 当连接关闭时所有客户变量将自动释放。
4.OUT参数
- 表示输出参数,可在存储过程内改变该值并将其返回
-- 存储过程countStudent,该存储过程中有个OUT参数,该参数为INT类型叫做total。
-- 存储体中将统计的结果利用`INTO`存入total中。
-- 调用存储过程中将用户变量@number作为参数传入,然后利用`SELECT @number`参看结果。
delimiter //
create procedure countStudent(out total int)
begin
select count(*) into total from student;
end //
delimiter ;
set @number=0;
call countStudent(@number);
select @number;
5.INOUT参数
- 表示输入输出参数,可在调用存储过程时指定该参数并在存储体中改变该值并将其返回
-- 存储过程searchStudentGender,该存储过程中有个INOUT参数,该参数为VARCHAR(50)类型叫做message。
-- message既当做输入参数又当做输出参数,即输入参数为学生的姓名lili返回的是学生的性别male。
-- 调用存储过程时将初始值为lili的用户变量@info传入存储过程,调用存储过程结束后再次查询@info的值为male。
delimiter //
create procedure queryStudentGender(inout message varchar(50))
begin
select gender into message from student where sname=message;
end //
delimiter ;
set @info='lili';
call queryStudentGender(@info);
select @info;
变量
1.使用场景
- 编写存储过程中有时需要使用变量保存数据处理过程中的值。
2.作用范围
- BEGIN…END
3.语法
DECLARE varName dataType [DEFAULT value];
- DECLARE:定义变量
- varName:局部变量的名称
- dataType:局部变量的类型
- DEFAULT value:变量默认值
(1)变量赋值或修改变量值
SET varName = value;
(2)示例一
delimiter //
create procedure varTest01(in number1 int)
begin
-- 声明变量
declare number2 int;
-- 声明变量result
declare result int;
-- 为变量number2赋值
set number2 = 99;
-- 为变量result赋值
set result = number1 + number2;
-- 显示result
select result;
end //
delimiter ;
-- 调用存储过程
call varTest01(1);
(2)示例二
-- 若存在存储过程findStudent,将之删除
drop procedure if exists findStudent;
delimiter //
create procedure findStudent(in studentID char(6))
begin
declare studentName varchar(59);
declare studentGender varchar(50);
-- 将查询结果保存至变量studentName和studentGender中
select sname,gender into studentName,studentGender from student where sid=studentID;
-- 显示studentName和studentGender中保存的值
select studentName,studentGender;
end //
delimiter ;
-- 调用存储过程
call findStudent('S_1001');
流程控制
1.流程控制概述
- 流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块。
2.流程控制语句
- IF语句
- CASE语句
- LOOP语句
- WHILE语句
- LEAVE语句
- ITERATE语句
- REPEAT语句
3.IF
(1)IF概述
- IF语句包含多个条件判断,根据结果为TRUE、FALSE执行语句。
- IF语句于编程语言中学得if、else if、else类似
(2)语法
IF expr_condition THEN statement_list
[ELSE expr_condtion THEN statement_list] ...
[ELSE statement_list]
END IF
(3)示例代码
drop procedure if exists ifTest;
delimiter //
create procedure ifTest(in num int)
begin
declare result varchar(20);
if num < 0 then
set result = 'negative number';
elseif num = 0 then
set result = 'number is zero';
else
set result = 'positive number';
end if;
select result;
end //
delimiter;
-- 调用存储过程
call ifTest(-1);
4.CASE 语句
(1)CASE概述
- CASE 是另一个条件判断的雨具,改雨具有两种语法格式
(2)CASE语法格式一
CASE case_expr
WHEN value THEN statement_list
[WHEN value THEN statement_list] ...
[ELSE statement_list]
END CASE;
(3)CASE语法格式一示例
DROP PROCEDURE
IF
EXISTS testCase01;
delimiter //
create procedure testCase01(in num int)
begin
declare result varchar(20);
case num
when num>0 set result = 'num is 正数';
when num=0 set result = 'num is zero';
else set result = 'num is 负数';
end case;
select result;
end //
delimiter ;
call testCase01(1);
(4)CASE语法格式二
CASE
WHEN expr_condition THEN statement_list
[WHEN expr_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;
(5)CASE语法格式二示例
delimiter //
create procedure testCase02(in num int)
begin
declare result varchar(20);
case
when num > 0 then set result = 'positive number';
when num = 0 then set result = 'number is zero';
else set result = 'positive number';
end case;
select result;
end //
delimiter ;
call testCase02(1);
5.LOOP语句
(1)LOOP语句概述
- LOOP 是一种循环语句,用来重复执行某些语句。
- 在执行过程中可使用LEAVE语句跳出循环,也可以嵌套IF等判断语句。
(2)LOOP语法
[loop_label:] LOOP
statement_list
END LOOP [loop_label]
- loop_label:标识标注名称,该参数可省略
(3)LOOP语句示例
delimiter //
create procedure testLoop(in start1 int ,in end1 int)
begin
declare sumResult int default 0;
add_loop: loop
set sumResult = sumResult + start1;
set start1 = start1 + 1;
if start1 > end1 then
leave add_loop;
end if;
end loop add_loop;
select sumResult;
end //
delimiter ;
call testLoop(0,100);
6.ITERATE
(1)ITERATE概述
- 表示再次循环,该语句作用是将执行顺序转到语句段开头处再执行,它与编程语言的continue非常类似;
(2)ITERATE 语法
ITERATE label
(3)示例
DROP PROCEDURE IF EXISTS testITERATE;
DELIMITER //
CREATE PROCEDURE testITERATE(IN start1 INT,IN end1 INT)
BEGIN
DECLARE sumResult INT DEFAULT 0;
add_loop: LOOP
SET sumResult=sumResult+start1;
SET start1=start1+1;
IF start1 <= end1 THEN
ITERATE add_loop;
ELSE LEAVE add_loop;
END IF;
END LOOP add_loop;
SELECT sumResult;
END //
DELIMITER ;
call testITERATE(0,100);
7.REPEAT
(1)REPEAT概述
- 表示带判断条件的循环过程
- 每次语句执行完毕之后,会对条件表达式进行判断,若表达式为TRUE则循环结束,否则重复执行。
- REPEAT类似于do…while。
(2)REPEAT语法
[repeat_label:] REPEAT
statement_list
UNTIL expr_condtion
END REPEAT [repeat_label]
(3)REPEAT语法实例
DELIMITER //
CREATE PROCEDURE testREPEAT(IN start INT,IN end INT)
BEGIN
DECLARE sumResult INT DEFAULT 0;
REPEAT
SET sumResult = sumResult + start;
SET start=start+1;
UNTIL start > end
END REPEAT;
SELECT sumResult;
END //
DELIMITER ;
call testREPEAT(0,100);
8.WHILE
(1)WHILE概述
- 用于带判断条件的循环过程。
- 与REPEAT不同,WHILE语句先判断表达式,为真则执行循环内的语句,否则退出循环。
- WHILE与编程语言的while类似。
(2)WHILE语法格式
[while_label:] WHILE expr_condition DO
statement_list
END WHILE [while_label]
(3)WHILE语法示例
DROP PROCEDURE IF EXISTS testWHILE;)
DELIMITER //
CREATE PROCEDURE testWHILE(IN start INT,IN end INT)
BEGIN
DECLARE sumResult INT DEFAULT 0;
WHILE start <= end DO
SET sumResult = sumResult + start;
SET start=start+1;
END WHILE;
SELECT sumResult;
END //
DELIMITER ;
call testWHILE(0,100);
游标(cursor)
1.游标概述
- 游标提供一种对 从表中检索出的数据进行操作的灵活手段。
- 本质而言,游标实际上是一种能 从包括多条数据记录的结果集中每次提取一条记录的机制。
- 例如,存储过程中查询语句可能会返回多条记录或大量记录,此时就需要游标来逐条读取查询结果集中的记录。
2.游标使用步骤
- 定义游标
- 打开游标
- 使用游标
- 关闭游标
- 释放游标
(1)定义游标
declare 有标明 cursor for select_statement;
(2)打开游标
open 游标名;
(3)使用游标
declare 变量1 与对应列值相同的数据类型
declare 变量2 与对应列值相同的数据类型
declare 变量3 与对应列值相同的数据类型
fetch next from 游标 [into 变量名1,变量名2,变量名3,...]
- declare:声明变量用于存储查询出来的数据;
- fetch:获取数据并保存至 declare 声明的变量中;
- fetch next:获取下一行数据
- 注意:初始状态中,游标默认指向数据集的第一行数据之前。故,在使用游标时应该执行fetch next 操作让其指向第一行数据。
(4)关闭游标
close 游标名;
(5)释放游标
deallocate 游标名;
3.游标示例一
delimiter //
create procedure cursorTest01()
begin
-- 声明与对应列类型相同的4个变量
declare studentID char(6);
declare studentName varchar(50);
declare studentAge int;
declare studentGender varchar(50);
-- 定义游标studentCursor
declare studentCursor cursor for select * from student;
-- 打开游标
open studentCursor;
-- 使用游标
fetch next from studentCursor into studentID,studentName,studentAge,studentGender;
-- 显示结果
select studentID,studentName,studentAge,studentGender;
-- 关闭游标
close studentCursor;
end //
delimiter ;
-- 调用存储过程
call cursorTest01();
- 在存储过程中,查询后得到的结果为表中所有数据。
- 在此,利用 fetch next 将结果集中的第一条数据的sid,sname,age,gender对应地保存至 studentID,studentName,studentName,studentAge,studentGender变量中。
4.游标示例二
drop procedure if exists cursorTest02;
delimiter //
create procedure cursorTest02()
begin
-- 声明与对应列类型相同的4个变量
declare studentID char(6);
declare studentName varchar(50);
declare studentAge int;
declare studentGender varchar(50);
-- 声明计数器
declare count int default 0;
declare total int default 0;
-- 定义游标studentCursor
declare studentCursor cursor for select * from student;
set total = (select count(*) from student);
-- 打开游标
open studentCursor;
-- 使用游标
-- 利用 REPEAT 语句循环取出结果集中的数据
REPEAT
fetch next from studentCursor into studentID,studentName,studentAge,studentGender;
-- 显示结果
select studentID,studentName,studentAge,studentGender;
set count = count + 1;
until count = total
end repeat;
-- 关闭游标
close studentCursor;
end //
delimiter ;
-- 调用存储过程
call cursorTest02();
到了这里,关于SQL-存储过程、流程控制、游标的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!