JavaWeb基础入门——（二）MySQL数据库基础（5-存储过程）

已于 2024-03-09 10:03:17 修改
阅读量747 收藏 10
点赞数 14
分类专栏： JavaWeb基础入门 文章标签： 数据库 mysql
于 2024-03-09 09:50:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Natural_Pride/article/details/136577715
版权

八、存储过程

8.1 存储过程介绍

8.1.1 SQL指令执行过程

在这里插入图片描述

从SQL执行执行的流程中我们分析存在的问题：

  1. 如果我们需要重复多次执行相同的SQL，SQL指令都需要通过连接传递到MySQL，并且需要经过编译和执行的步骤；

  2. 如果我们需要连续执行多个SQL指令，并且第二个SQL指令需要使用第一个SQL指令执行的结果作为参数；

8.1.2 存储过程的介绍

在这里插入图片描述

存储过程：

将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

8.1.3 存储过程优缺点分析

存储过程优点：

  1. SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性；

  2. 存储过程经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升；

  3. 存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务;

存储过程的缺点：

  1. 存储过程是根据不同的数据库进行编译、创建并存储在数据库中；当我们需要切换到其他的数据库产品时，需要重写编写针对于新数据库的存储过程；

  2. 存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题；

  3. 在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的业务交给了数据库进行处理）

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 = a+b;
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)
begin
    declare x int default 0; -- 定义x int类型，默认值为0
    declare y int default 1; -- 定义y
    set x = a*a;
    set y = a/2;
    set r = x+y;
end;

8.3.2 定义用户变量l

用户变量：相当于全局变量，定义的用户变量可以通过 select @altreName from dual 进行查询

-- 用户变量会存储在mysql数据库的数据字典中（dual）
-- 用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1;

8.3.3 给变量设置值

无论是局部变量还是用户变量，都是使用 set 关键字修改值

-- 查询学生数量
-- 注意在储存过程中使用SQL语句需要将结果赋值给变量，那么就需要使用into关键字来进行赋值
create procedure proc_test3(OUT c int)
begin
   select 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))
begin
    insert 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))
begin
	select stu_name INTO sname from students where stu_num=snum;
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))
begin
	select stu_name INTO str from students where stu_num=str;
end;
set @name='20220108';
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)
begin
    if a=1 then
        insert 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)
    begin
        if a=1 then
            insert into classes(class_name,remark)
            values('Java2209','test');
        else
            insert into
            students(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)
begin
    case a
    when 1 then
        -- SQL1 如果a的值为1 则执行SQL1
        insert into classes(class_name,remark) values('Java2210','wahaha');
    when 2 then
        -- SQL2 如果a的值为2 则执行SQL2
        insert 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_age=18 where stu_num='20220110';
    end case;
end;

8.5.2 循环语句

  • while

concat() 函数用于将两个字符串连接起来，形成一个单一的字符串

-- while
create procedure proc_test9(IN num int)
begin
    declare i int;
    set i = 0;
    while i<num do
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('Java',i),'....');
        set i = i+1;
    end while;
end;
call proc_test9(4);
  • repeat
-- repeat
create procedure proc_test10(IN num int)
begin
    declare i int;
    set i = 1;
    repeat
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('Python',i) ,'....');
        set i = i+1;
        until i > num end repeat;
end;
call proc_test10(4);
  • loop（注意如果需要停止循环，需要通过if来进行结束条件的判断）
-- loop
create procedure proc_test11(IN num int)
begin
    declare i int ;
    set i =0;
    myloop:loop
        -- SQL
        insert into classes(class_name,remark) values( CONCAT('HTML',i),'....');
        set i = i+1;
        # 结束循环的条件
        if i=num then
            # 离开循环
            leave myloop;
   		 end if;
	end loop;
end;
call proc_test11(5);

8.6 存储过程管理

8.6.1 查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库中调用此存储过程。

查询存储过程：查询某个数据库中有哪些存储过程

-- 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='db_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 实现借书业务

-- 实现借书业务：
-- 参数1：a	输入参数 	学号
-- 参数2：b	输入参数 	图书编号
-- 参数3：m	输入参数 	借书的数量
-- 参数4：state 输出参数 借书的状态(1借书成功，2学号不存在，3图书不存在，4库存不足)
create procedure proc_borrow_book(IN a int ,IN b int ,IN m int ,OUT state int )
begin
	declare stu_count int default 0;
	declare book_count int default 0;
	declare stocks int default 0;
	-- 判断学号是否存在，根据参数a到学生信息表查询是否有stu_num=a的记录
	select count(stu_num) INTO stu_count from students where stu_num=a;
	if stu_count >0 then 
		-- 学号存在
		-- 判断图书是否存在，根据参数b,查询图书记录总数
		select count(book_id) INTO book_count from books where book_id=b;
			if book_count>0 then 
					-- 图书存在
					-- 图书库存是否充足，根据参数m,查询当前图书库存，并与参数m进行比较
					select book_stock INTO stocks from books where book_id=b;
					if stocks>m 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_stock=stocks-m where book_id=b;
							-- 3 借书成功
							set state=1;
					else
						-- 库存不足
						set state=4;
					end if; 
			else 
					-- 图书不存在
					set state= 3;
			end if;
	else 
		-- 学号不存在
		set state=2;
	end if;
end;

-- 调用存储过程借书
set @state=0;
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)
);

-- 参数1：sid	输入参数 	学号
-- 参数2：bid 输入参数 	图书编号
-- 参数3：n	输入参数 	还书的数量
-- 参数4：state 输出参数 借书的状态(1还书成功，2该生未借过图书，3图书不存在)
create procedure proc_return_book(IN sid int, IN bid int , IN n int , OUT state int )
begin
		declare stu_count int default 0;
		declare b_count int default 0;
		declare stocks int default 0;
		-- 判断该生是否有借书记录，将参数sid与借书借书记录表中，是否sid=snum
		select count(snum) INTO stu_count from borrow_records where snum=sid;
		if stu_count>0 then 
			-- 有借书记录
			-- 判断是否借过该本图书，将参数bid与借书记录表中是否bid=r_id
			select count(r_id) INTO b_count from borrow_records where bid=r_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_stock=stocks+n where book_id=bid; 
				-- 3 修改借书状态
				update borrow_records set is_return=1 where r_id=bid;
				-- 4 还书成功
				set state=1;
			else
				-- 图书编号不正确
				set state =3;
				
			end if;
		else
			-- 无借书记录
			set state=2;
		
		end if;

end;

set @state=0;
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 i<num do
    # 提取游标中的数据，并将结果赋值给游标变量
    fetch mycursor into cid,cname;
    set i = i+1;
    # set str=concat_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;
Natural_Pride
关注
  • 14
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
javaweb学习总结——使用JDBC处理MySQL大数据
09-01
本篇文章主要介绍了JDBC处理MySQL大数据，有时是需要用程序把大文本或二进制数据直接保存到数据库中进行储存的，非常具有实用价值，需要的朋友可以参考下。
JavaWeb之利用jdbc通过前端向Mysql数据库中插入数据-附件资源
03-05
JavaWeb之利用jdbc通过前端向Mysql数据库中插入数据-附件资源
java回顾：数据库存储过程和函数、存储引擎、锁
m0_56678122的博客
09-28 398
数据库存储过程和函数、存储引擎、锁
在java中调用存储过程
02-26 406
在java中调用存储过程： 一、只返回一个数值 1.存储过程 create or replace procedure getCount(a in number,                                      b in number,                                      c out number) is   sql_str
java存储过程
weixin_54355236的博客
12-23 4088
day01 1、PLSQL编程 1.1 程序结构 存储过程优点：减少数据库和服务器网络交互，提高执行效率（将写到java程序中的代码抽到数据库） PLSQL不区分大小写，三个部分组成： //声明部分: DECLARE** -- 声明变量、游标(没有可省略) //可执行部分： BEGIN DBMS_output.put_line('hello world'); -- 打印语句 //异常部分:（没有的话可省略） END; 1.2 变量 1.2.1 普通变量 变量赋值的方式有两种： 1、直接赋值
2021/10/2 Java数据库编程 第五章 存储过程
Abcdzzr的博客
10-02 2678
一、存储过程 1.定义： 一般在大型的数据库系统中，一组为了完成特定功能的sql语句集，存储在数据库中，经过第一次编译后，再次调用时就不需要再次编译了，用户只需要指定存储过程名称并给定参数就可以执行完成任务。 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接和关闭是很耗费资源的），需要对数据库进行多次I/O读写，性能比较低。如果把这些放到存储过程中，就可以实现连接关闭一次数据库来实现业务，大大提高了效率。 2.语法： （1）创建存储过程 create procedure 存储过程名
JavaWeb基础之MySql数据库入门01.md
01-10
这是JavaWeb入门基础，数据库MySql基础的相关知识总结。之前自己学的和用的是SQL Server2008，对MySql掌握不够熟练，不过SQL语句都是差不多，所以上手比较容易。写博客就是为了方便自己复习查看，同时也将自己的学习...
javaweb+mysql数据库实现注册登录程序
03-06
javaweb+mysql数据库实现注册登录
JavaWeb基础入门
04-05
JavaWeb开发入门，目录：JavaScript，XML解析器，JavaWeb开发入门，Http协议，Servlet技术，请求和响应，Cookie和Session，MySQL数据库，用JDBC操作数据库，JDBC之数据库连接池，JDBC之事务，上传下载文件，酷炫的...
java 批量执行存储过程_执行存储过程有多少种方法java
weixin_39806779的博客
02-16 494
展开全部Java执行存储过程的方法32313133353236313431303231363533e78988e69d8331333339653666：简单的老的JDBC通过CallableStatement类支持存储过程的调用。该类实际上是PreparedStatement的一个子类。假设有一个poets数据库。数据库中有一个设置诗人逝世年龄的存储过程。下面是对老酒鬼Dylan Thomas(o...
【java学习】数据库的存储过程总结
weixin_34357887的博客
03-20 206
在学习JDBC的时候，CallableStatement对象出现了一个数据库存储过程，对这一概念不是很了解。所以就查阅相关资料，总结一下 什么是存储过程？ 根据百度百科的解释，存储过程是Store Procedure，是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，经过一次编译后再次调用就不需要编译了。 笔者认...
数据库存储过程
热门推荐
客亦知夫水与月乎？
03-27 2万+
【存储过程的概念】   存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中。 存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 存储过程是由 流控制 和 SQL
JDBC 存储过程（使用java代码编写sql 存储过程）
共勉
06-18 6587
1.定义一个存储过程（不带参数） 使用sql语句定义一个存储过程挺简单的，参考这一篇博文https://blog.csdn.net/qq_44973159/article/details/91491985 create procedure ccgc as select * from t_student 而使用java并不 那么简单了，，，如下 package java617存储过程; impor...
Java实现调用MySQL存储过程详解
GoodIdea
06-17 1万+
前言 存储过程（Stored Procedure）是存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 Java调用MySQL的存储过程，需要用JDBC连接，环境eclipse 首先查看MySQL中的数据库的存储过程，接着编写代码调用 ? 1 2 3 4 5 6 ...
java中的内存机制_java中存储机制堆栈。
weixin_34336068的博客
02-12 219
一、java的六种存储地址及解释1) 寄存器(register)：这是最快的存储区，因为它位于不同于其他存储区的地方——处理器内部。但是寄存器的数量极其有限，所以寄存器由编译器根据需求进行分配。你不能直接控制，也不能在程序中感觉到寄存器存在的任何迹象。2) 堆栈(stack)：位于通用RAM中，但通过它的“堆栈指针”可以从处理器哪里获得支持。堆栈指针若向下移动，则分配新的内存；若向上移动，则释放那...
黑马程序员javaweb基础教程,javaweb从入门到企业实战完整版
最新发布
09-16
《黑马程序员JavaWeb基础教程，JavaWeb从入门到企业实战完整版》是一本非常实用和全面的JavaWeb学习教程。本书以初学者为目标受众，逐步讲解了JavaWeb的基础知识，并结合实际项目进行了企业级的实战演练。 这本教程...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值