怎样做安居客网站,用幽默的语言来形容网站开发,C2C电子商务网站管理系统,同一个公司可以做几个网站八、存储过程
8.1 存储过程介绍
8.1.1 SQL指令执行过程 从SQL执行执行的流程中我们分析存在的问题#xff1a; 如果我们需要重复多次执行相同的SQL#xff0c;SQL指令都需要通过连接传递到MySQL#xff0c;并且需要经过编译和执行的步骤#xff1b; 如果我们需要连续执行…八、存储过程
8.1 存储过程介绍
8.1.1 SQL指令执行过程 从SQL执行执行的流程中我们分析存在的问题 如果我们需要重复多次执行相同的SQLSQL指令都需要通过连接传递到MySQL并且需要经过编译和执行的步骤 如果我们需要连续执行多个SQL指令并且第二个SQL指令需要使用第一个SQL指令执行的结果作为参数
8.1.2 存储过程的介绍 存储过程 将能够完成特定功能的SQL指令进行封装SQL指令集编译之后存储在数据库服务器上并且为之取一个名字客户端可以通过名字直接调用这个SQL指令集获取执行结果。 8.1.3 存储过程优缺点分析
存储过程优点 SQL指令无需客户端编写通过网络传送可以节省网络开销同时避免SQL指令在网络传输过程中被恶意篡改保证安全性 存储过程经过编译创建并保存在数据库中的执行过程无需重复的进行编译操作对SQL指令的执行过程进行了性能提升 存储过程中多个SQL指令之间存在逻辑关系支持流程控制语句分支、循环可以实现更为复杂的业务;
存储过程的缺点 存储过程是根据不同的数据库进行编译、创建并存储在数据库中当我们需要切换到其他的数据库产品时需要重写编写针对于新数据库的存储过程 存储过程受限于数据库产品如果需要高性能的优化会成为一个问题 在互联网项目中如果需要数据库的高连接并发访问使用存储过程会增加数据库的连接执行时间因为我们将复杂的业务交给了数据库进行处理
8.2 创建存储过程
8.2.1 存储过程创建语法
-- 语法[为参数部分与java类似可以定义参数也可以不定义参数]:
create procedure proc_name([IN/OUT args])
begin-- SQL
end;8.2.2 示例
-- 创建一个存储过程实现加法运算: Java语法中方法是有参数和返回值的
-- 存储过程中是有输入参数 和 输出参数的
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin-- set代表的是定义变量的意思SET c ab;
end;-- 调用存储过程
-- 定义变量m
set m 0;
-- 调用存储过程将3传递给a将2传递给b将m传递给c
call proc_test1(3,2,m);
-- 显示变量值dual系统表无需创建定义变量的值都会在这里
select m from dual;8.3 存储过程中变量的使用 存储过程中的变量分为两种局部变量 和 用户变量 8.3.1 定义局部变量
局部变量定义在存储过程中的变量只能在存储过程内部使用
局部变量定义语法
-- 局部变量要定义在存储过程中而且必须定义在存储过程开始
declare attr_name type [default value];局部变量定义示例
create procedure proc_test2(IN a int,OUT r int)
begindeclare x int default 0; -- 定义x int类型默认值为0declare y int default 1; -- 定义yset x a*a;set y a/2;set r xy;
end;8.3.2 定义用户变量l
用户变量相当于全局变量定义的用户变量可以通过 select altreName from dual 进行查询
-- 用户变量会存储在mysql数据库的数据字典中dual
-- 用户变量定义使用set关键字直接定义变量名要以开头
set n1;8.3.3 给变量设置值
无论是局部变量还是用户变量都是使用 set 关键字修改值
-- 查询学生数量
-- 注意在储存过程中使用SQL语句需要将结果赋值给变量那么就需要使用into关键字来进行赋值
create procedure proc_test3(OUT c int)
beginselect count(stu_num) INTO c from students; -- 将查询到学生数量赋值给c
end;
-- 调用存储过程
call proc_test3(n);
select n from dual;8.3.4 用户变量使用注意事项 因为用户变量相当于全局变量可以在SQL指令以及多个存储过程中共享在开发中建议尽量少使用用户变量用户变量过多会导致程序不易理解、难以维护。 8.4 存储过程的参数 MySQL存储过程的参数一共有三种IN \ OUT \ INOUT 8.4.1 IN 输入参数
输入参数——在调用存储过程中传递数据给存储过程的参数在调用的过程必须为具有实际值的变量 或者 字面值
-- 创建存储过程添加学生信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN gender
char(2), IN age int, IN cid int, IN remark varchar(255))
begininsert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)values(snum,sname,gender,age,cid,remark);
end;
call proc_test4(20220108,小丽,女,20,1,aaa);8.4.2 OUT 输出参数
输出参数——将存储过程中产生的数据返回给过程调用者相当于Java方法的返回值但不同的是一个存储过程可以有多个输出参数
-- 创建存储过程根据学生学号查询学生姓名
create procedure proc_test5(IN snum char(8),OUT sname varchar(20))
beginselect stu_name INTO sname from students where stu_numsnum;
end;
set name;
call proc_test5(20220107,name);
select name from dual;8.4.3 INOUT 输入输出参数
注意此方式不建议使用一般我们输入就用 IN 输出就用OUT此参数代码可读性低容易混淆。
create procedure proc_test6(INOUT str varchar(20))
beginselect stu_name INTO str from students where stu_numstr;
end;
set name20220108;
call proc_test6(name);
select name from dual;8.5 存储过程中流程控制 在存储过程中支持流程控制语句用于实现逻辑的控制 8.5.1 分支语句
if-then-else
-- 单分支如果条件成立则执行SQL
if conditions then-- SQL
end if;
-- 如果参数a的值为1则添加一条班级信息
create procedure proc_test7(IN a int)
beginif a1 theninsert into classes(class_name,remark)values(Java2209,test);end if;
end;
-- 双分支如果条件成立则执行SQL1否则执行SQL2
if conditions then-- SQL1
else-- SQL2
end if;-- 如果参数a的值为1则添加一条班级信息否则添加一条学生信息create procedure proc_test7(IN a int)beginif a1 theninsert into classes(class_name,remark)values(Java2209,test);elseinsert intostudents(stu_num,stu_name,stu_gender,stu_age,cid,remark)values(20220110,小花,女,19,1,...);end if;
end;case
create procedure proc_test8(IN a int)
begincase awhen 1 then-- SQL1 如果a的值为1 则执行SQL1insert into classes(class_name,remark) values(Java2210,wahaha);when 2 then-- SQL2 如果a的值为2 则执行SQL2insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)values(20220111,小刚,男,21,2,...);else-- SQL (如果变量的值和所有when的值都不匹配则执行else中的这个SQL)update students set stu_age18 where stu_num20220110;end case;
end;8.5.2 循环语句
while
concat() 函数用于将两个字符串连接起来形成一个单一的字符串
-- while
create procedure proc_test9(IN num int)
begindeclare i int;set i 0;while inum do-- SQLinsert into classes(class_name,remark) values( CONCAT(Java,i),....);set i i1;end while;
end;
call proc_test9(4);repeat
-- repeat
create procedure proc_test10(IN num int)
begindeclare i int;set i 1;repeat-- SQLinsert into classes(class_name,remark) values( CONCAT(Python,i) ,....);set i i1;until i num end repeat;
end;
call proc_test10(4);loop注意如果需要停止循环需要通过if来进行结束条件的判断
-- loop
create procedure proc_test11(IN num int)
begindeclare i int ;set i 0;myloop:loop-- SQLinsert into classes(class_name,remark) values( CONCAT(HTML,i),....);set i i1;# 结束循环的条件if inum then# 离开循环leave myloop;end if;end loop;
end;
call proc_test11(5);8.6 存储过程管理
8.6.1 查询存储过程 存储过程是属于某个数据库的也就是说当我们将存储过程创建在某个数据库之后只能在当前数据库中调用此存储过程。 查询存储过程查询某个数据库中有哪些存储过程 -- 根据数据库名查询当前数据库中的存储过程
show procedure status where dbdb_test2;
-- 查询存储过程的创建细节
show create procedure db_test2.proc_test1;8.6.2 修改存储过程 修改存储过程指的是修改存储过程的特征/特性 alter procedure proc_name 特征1 [特征2 特征3 ....]存储过程的特征参数
CONTAINS SQL 表示子程序包含 SQL 语句但不包含读或写数据的语句NO SQL 表示子程序中不包含 SQL 语句READS SQL DATA 表示子程序中包含读数据的语句MODIFIES SQL DATA 表示子程序中包含写数据的语句SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行 DEFINER 表示只有定义者自己才能够执行INVOKER 表示调用者可以执行 COMMENT ‘string’ 表示注释信息
alter procedure proc_test1 READS SQL DATA;8.6.3 删除存储过程
-- 删除存储过程
-- drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....
-- delete 删除数据表中的数据
drop procedure proc_test1;8.7 存储过程练习案例 使用存储过程解决企业项目开发过程中的问题 案例使用存储过程完成借书操作 8.7.1 数据准备
-- 创建数据库
create database db_test;-- 使用数据库
use database db_test-- 创建图书信息表
create table books(book_id int primary key auto_increment,book_name varchar(50) not null,book_author varchar(50) not null,book_price decimal(10,2) not null,book_stock int not null,book_desc varchar(500)
);-- 添加图书信息
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values(Java程序设计,亮亮,38.80,12,亮亮老师带你学Java);
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values(Java王者之路,威哥,44.40,12,威哥带你学Java);-- 创建学生信息表
create table students(stu_num char(8) primary key,stu_name varchar(10) not null,stu_gender char(8) not null,stu_age int not null
);-- 添加学生信息
insert into students(stu_num,stu_name,stu_gender,stu_age)
values(1001,张三,男,20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values(1002,李四,男,21);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values(1003,张丽,女,18);8.7.2 业务分析 哪个学生借哪本书借了多少本 操作 保存借书记录修改图书库存 条件 判断学生是否存在判断图书是否存在、库存是否充足 创建借书记录表
-- 借书记录表
create table records(r_id int primary key auto_increment,snum char(4) not null, -- 学生借书数量b_id int not null,borrow_num int not null,is_return int not null, -- 0未归还-- 1已归还borrow_date date not null,constraint FK_RECORDS_STUDENTS foreign key(snum) references students(stu_num),constraint FK_RECORDS_BOOKS foreign key(b_id) references books(book_id)
);8.7.3 实现借书业务
-- 实现借书业务
-- 参数1a 输入参数 学号
-- 参数2b 输入参数 图书编号
-- 参数3m 输入参数 借书的数量
-- 参数4state 输出参数 借书的状态(1借书成功2学号不存在3图书不存在4库存不足)
create procedure proc_borrow_book(IN a int ,IN b int ,IN m int ,OUT state int )
begindeclare stu_count int default 0;declare book_count int default 0;declare stocks int default 0;-- 判断学号是否存在根据参数a到学生信息表查询是否有stu_numa的记录select count(stu_num) INTO stu_count from students where stu_numa;if stu_count 0 then -- 学号存在-- 判断图书是否存在根据参数b,查询图书记录总数select count(book_id) INTO book_count from books where book_idb;if book_count0 then -- 图书存在-- 图书库存是否充足根据参数m,查询当前图书库存并与参数m进行比较select book_stock INTO stocks from books where book_idb;if stocksm then -- 执行借书-- 1 在借书记录表中添加记录insert into records(snum,b_id,borrow_num,is_return,borrow_date)values(a,b,m,0,sysdate());-- 2 修改图书库存update books set book_stockstocks-m where book_idb;-- 3 借书成功set state1;else-- 库存不足set state4;end if; else -- 图书不存在set state 3;end if;else -- 学号不存在set state2;end if;
end;-- 调用存储过程借书
set state0;
call proc_borrow_book(1001,1,2,state);
select state from dual;8.7.4 实现还书业务 哪个学生还书, 还多少本 操作 保存还书记录修改图书库存 条件 判断学生是否借过书?判断图书是否存在? -- 实现还书业务
-- 创建还书记录表
create table return_records(r_id int primary key auto_increment,s_num char(4) not null, -- 学生还书数量b_id int not null,return_num int not null,return_date date not null,constraint FK_RETURN_RECORDS_BORROW_RCORDS foreign key(s_num) references borrow_records(snum)
);-- 参数1sid 输入参数 学号
-- 参数2bid 输入参数 图书编号
-- 参数3n 输入参数 还书的数量
-- 参数4state 输出参数 借书的状态(1还书成功2该生未借过图书3图书不存在)
create procedure proc_return_book(IN sid int, IN bid int , IN n int , OUT state int )
begindeclare stu_count int default 0;declare b_count int default 0;declare stocks int default 0;-- 判断该生是否有借书记录将参数sid与借书借书记录表中是否sidsnumselect count(snum) INTO stu_count from borrow_records where snumsid;if stu_count0 then -- 有借书记录-- 判断是否借过该本图书将参数bid与借书记录表中是否bidr_idselect count(r_id) INTO b_count from borrow_records where bidr_id;if b_count 0 then -- 图书编号正确-- 执行还书操作-- 1 在还书记录表中添加记录insert into return_records(s_num,b_id,return_num,return_date)values(sid,bid,n,sysdate());-- 2 修改图书库存update books set book_stockstocksn where book_idbid; -- 3 修改借书状态update borrow_records set is_return1 where r_idbid;-- 4 还书成功set state1;else-- 图书编号不正确set state 3;end if;else-- 无借书记录set state2;end if;end;set state0;
call proc_return_book(1003,2,3,state);
select state from dual;8.8 游标 问题如果我们要创建一个存储过程需要返回查询语句查询到的多条数据该如何实现呢 8.8.1 游标的概念 游标可以用来依次取出查询结果集中的每一条数据——逐条读取查询结果集中的记录 8.8.2 游标的使用步骤
1、声明游标
声明游标
语法
DECLARE cursor_name CURSOR FOR select_statement;示例
declare mycursor cursor for select class_id,class_name from classes;2、打开游标
语法:
open mycursor;3、使用游标
使用游标提取游标当前指向的记录提取之后游标自动下移
fetch mycursor into cid,cname;4、关闭游标
CLOSE mycursor;游标使用案例
MySQL中 Concat_WS() 函数 用来通过指定符号将2个或多个字段拼接在一起返回拼接后的字符串。
create procedure proc_test12(out result varchar(200))
begin
# 游标变量
declare cid int;
# 游标变量
declare cname varchar(20);
# 计数变量
declare num int;
# 计数变量
declare i int;
# 每条数据
declare str varchar(100);
# 查询语句执行之后返回的是一个结果集多条记录使用游标遍历查询结果集
declare mycursor cursor for select class_id,class_name from classes;
# 记录总数据量
select count(*) into num from classes;
# 打开游标
open mycursor;
set i 0;
# 开始遍历游标
while inum do# 提取游标中的数据并将结果赋值给游标变量fetch mycursor into cid,cname;set i i1;# set strconcat_ws(~,cid,cname); 不同的写法select concat_ws(~,cid,cname) into str;set result concat_ws(,,result,str);end while;close mycursor;
end;
# 案例测试
set r ;
call proc_test12(r);
select r from dual;
