存储过程
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; - in输入参数
存储过程中流程控制语句
- 分支语句
- 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
668
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
