存储过程~~~

小机灵鬼儿~

已于 2022-11-30 10:31:07 修改

阅读量294

点赞数

文章标签： sql 数据库 database

于 2021-12-29 23:31:23 首次发布

本文链接：https://blog.csdn.net/Hyggn/article/details/122216753

版权

存储过程

SQL指令的执行过程

存储过程
将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。
存储过程的优点
1.SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意修改，保证安全性。
2.存储过程经过编译创建并保存在数据库中，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升。
3.存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支，循环），可以实现更为复杂的业务。
存储过程的缺点
1.存储过程是根据不同的数据库进行编译、创建并存储在数据库中，当我们需要切换到其他数据库产品时需要重新编写针对于新数据库的存储过程。
2.存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题。
3.在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连续执行时间（因为将复杂的业务交给数据库处理）

创建存储过程

语法

create procedure <程序名>([in/out args])
begin 
	---sql
end;

实例

*** 创建一个存储过程实现加法运算 ：java语法中，方法是有参数和返回值的
							  存储过程中，是有输入参数和输出参数的
create procedure p_name1(in a int,in b int,out c int)
begin
	set c = a+b;
end;

调用存储过程

*** 调用存储过程
*** 定义变量@m
set @m = 0;
*** 调用存储过程，将3传递给a,将2传递给b,将@m传递给c
call p_name1(3,2,@m);
*** 显示变量值
select @m from dual;

存储过程中变量的使用

存储过程中的变量分为两种：局部变量和用户变量

定义局部变量
局部变量：定义在存储过程中的变量，只能在存储过程内部使用

局部变量定义语法

*** 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare <attr_name> <type> [default value];

局部变量定义实例

create procedure p_name2(in a int,out r int)
begin
	declare x int default 0; *** 定义x  int类型  默认值为0
	declare y int default 0; *** 定义y 
	set x = a*a;
	set y = a/2;
	set r = x+y;
end;

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

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

给变量设置值
无论是局部变量还是用户变量，都是使用set关键字修改值
```
set @n=1;
call p_name2(6,@n);
select @n from dual;
```

将查询结果赋值给变量
在存储过程中使用select…into…给变量赋值

*** 查询学生数量
create procedure p_name3(out c int)
begin
	select count(*) into c from student; -- 将查询到学生数量赋值给c
end;
*** 调用存储过程
call p_name3(@n);
select @n from dual;

用户使用注意事项
因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量，用户变量过多会导致程序不易理解，难以维护.

in输入参数
输入参数----在调用存储过程中传递给存储过程的参数（在调用的过程中必须为具有实际值的变量或者字面值）

*** 创建存储过程 ：添加学生信息
create procedure p_name4(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 student(s_num,s_name,s_gender,s_age,cid,remark)
	values(snum,sname,gender,age,cid,remark)
end;
call p_name4('2021','小机灵鬼儿','女',18,1,'aa');

out 输出参数
输出参数----将存储过程中产生的数据返回给过程调用者，相当于java方法的返回值，但不同的是一个存储过程可以有多个输出参数

*** 创建存储过程，根据学号查询姓名
create procedure p_name5(in snum char(8),out sname varchar(20))
begin 
	select s_name into sname from student where s_num=snum;
end;
set @name='';
call p_name5('2021',@name);
select @name from dual;

inout 输入输出参数

create procedure p_name6(inout str varchar(20))
begin 
	select s_name into str from student where s_num=str;
end;
set @name='2021';
call p_test6(@name);
select @name from dual;

存储过程中流程控制语句

分支语句

if-then-else

*** 单分支 : 如果条件成立，则执行SQL
if conditions then
	---sql
end if;
*** 如果参数a的值为1，则添加一条班级信息
create procedure p_name7(in a int)
begin
	if a=1 then
		insert into class(c_name,remark) values('java','...');
	end if;
end;

*** 双分支 : 如果条件成立则执行SQL1，否则执行SQL
if conditions then
	--- sql
else 
	--- sql
end if;
*** 如果参数a为1，则添加一条班级信息，否则添加一条学生信息
create procedure p_name7(in a int)
begin 
	if a=1 then
		insert into class(c_name,remark) values('java','...');
	else
		insert into class(c_name,remark) values('python','...');
	end if;
end;

case

create procedure p_name8(in a int)
begin
	case a
		when 1 then
			---sql1  如果a的值为1  则执行SQL1
			insert into class(c_name,remark) values('java','娃哈哈');
		when 2 then
			---sql2  如果a的值为2  则执行SQL2
			insert into class(c_name,remark) values('python','娃哈哈');
		else
			---sql （如果变量的值和所有的where都不匹配，则执行else中的这个SQL）
			update student set s_age=18 where s_num='2022';
	end case;
end;

循环控制

while

create procedure p_name9(in num int)
begin
	declare i int;
	set i = 0;
	while i<num do
		---sql
		insert into class(c_name,remark) values('python','娃哈哈');
		set i = i+1;
	end while;
end
call p_name9(4);

repeat

create procedure p_name10(in num int)
begin
	declare i int;
	set i = 0;
	repeat
		---sql
		insert into class(c_name,remark) values(concat('python',i),'...');
		set i = i+1;
	until i>num end repeat;
end
call p_name10(4);

loop

create procedure p_name11(in num int)
begin
	declare i int;
	set i = 0;
	myloop : loop
		---sql
		insert into class(c_name,remark) values('c','娃哈哈');
		set i = i+1;
		if i=num then
			leave myloop;
		end if;
	end loop;
end
call p_name11(4);

存储过程操作

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

*** 根据数据库名，查询当前数据库中的存储过程
show procedure status where db='dbname';
*** 查询存储过程中的创建细节
show create procedure dbname.p_name1;

修改存储过程
修改存储过程的特征

alter procedure <pname> 特征1【特征2...】

存储过程中的特征函数

	* CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句
	* NO SQL 表示子程序中不包含SQL语句
	* READS SQL DATA 表示子程序中包含读数据的语句
	* MODIFIES SQL DATA 表示子程序中包含写数据的语句
	* SQL SECURITY {DEFINER INVOKER} 指明谁有权限来执行
		* DEFINER 表示只有定义者自己才能够执行
		* INVOKER 表示调用者可以执行
	* COMMENT 'string' 表示注释信息

alter procedure p_name1 reads sql data

删除存储过程

*** drop删除数据库中的对象 数据库，数据表，列，存储过程，视图，触发器，索引...
*** delete 删除数据表中的数据
drop procedure p_name1;

游标

游标可以用来依次取出查询结果集中的每一条数据----逐条读取查询结果集中的记录

使用步骤

声明游标

*** 声明语法
declare c_name cursor for select_statement;
*** 实例
declare mycursor for select <columnname1>,<columnname2>... from <tablename>;

打开游标

*** 语法
open mycursor

使用游标
- 提取游标当前指向的记录（提取之后，游标自动下移）
```
fetch mycursor into <columnname1>,<columnname2>...;
```
关闭游标

close mycursor;

实例

create procedure p_test(out result varchar(200))
begin
	declare bname varchar(20);
	declare bauthor varchar(20);
	declare bprice decimal(10,2);
	declare num int;
	declare i int;
	declare str varchar(50);
	-- 此查询语句执行后返回的是一个结果集（多条记录），使用游标可以来遍历结果集
	declare mycursor cursor for select b_name,b_author,b_price from books;
	select count(1) info num from books;
	--打开游标
	open mycursor;
	--使用游标要结合循环语句
	set i=0;
	while i<num do
	--使用游标 ：提取游标当前指向的记录（提取之后，游标自动下移）
	fetch mucursor into bname,bauthor,bprice;
	set i=i+1;
	-- set str=concat_ws('~',bname,bauthor,bprice);
	select concat_ws('~',bname,bauthor,bprice) into str;
	set result = concat_ws(',',result,str);
	end while;
	--关闭游标
	close mycursor;
end

小机灵鬼儿~

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储过程~~~

存储过程SQL指令的执行过程存储过程       将能够完成特定功能的SQL指令进行封装（SQL指令集），编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。存储过程的优点1.SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意修改，保证安全性。2.存储过程经过编译创建并保存在数据库中，执行过程无需重复的进行编译操作，对
复制链接

扫一扫