Day5 MySQL 学习笔记

Day5 MySQL 学习笔记

通过**“2024版MySQL数据库基础+进阶全套视频教程完整版”, 作者：一名00后程序媛，Bilibili.com **学习

8.2 创建存储过程

create procedure <procedurename> ([in/out args])
begin
  #SQL
end;

示例：

# in是输入参数，out是输出参数
create procedure proc1 (in a int, in b int,out c int)
begin
  set c = a+b;
end;

8.3 调用存储过程

#定义变量 @m
set @m=0;
#调用存储过程，3--a，2--b，@m--c
call proc1(3,2,@m);
#显示变量值
select @m from dual;

8.4 存储过程中变量的使用

分为局部变量和用户变量

1.定义局部变量

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

#定义在存储过程中的开始
declare <attr_name> <type> [default type];

示例：

create procedure proc2 (in a int, out r int)
begin
  declare x int default 0;
  declare y int default 1;
  set x = a*a
  set y = a/2
  set r = x+y;
end;

2.定义用户变量

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

#用户变量存储在mysql数据库的数据字典（dual）中
#通过set关键词定义，变量名要以@开头
set @n=1；

3. 给变量设置值
   • 两种变量都使用set关键字修改值

set @n=1;
call proc2(6,@n)
select @n from dual;

4. 将查询结果赋值给变量

在存储过程中使用 select…into…给变量赋值

#查询学生数量
create procedure proc3(out c int)
begin
	select count(stunum) into c from students;
end;

#调用
call proc3(@n);
select @n from dual;

5. 用户变量使用注意事项

因为用户变量相当于全局变量，可以在sql指令以及多个存储过程中共享，在开发中少用用户变量，用户变量太多导致程序不易理解，难以维护。

8.5存储过程的参数

MySQL存储过程的参数有三种：in/out/inout

1. in输入参数

在调用存储过程中传递数据给存储过程的参数

#添加信息的存储过程
create procedure proc5(in num char(8),in name varchar(20),in gender char(2),in age int, in cid int, in remark varchar(255))
begin

insert into students(stunum,stuname,stugender,stuage,cid,remark) values (num,name,gender,age,cid,remark);

end;

call proc5('2020305','H','女',20，1，'...');

2. out 输出参数

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

#根据学号查询姓名
create procedure proc6(in num char(8),out name varchar(20))
begin
select stuname into name from students where stunum=num;
end;

set @name = '';
call proc6('2020305',@name);
select @name from dual;

3. inout 输入输出参数

create procedure proc7(inout str varchar(20))
begin
select stuname into str from students where stunum=str;
end;

set @name = '2020305'
call proc7(@name);
select @ name from dual
#得到H

8.6存储过程中流程控制

用于实现逻辑控制

1. 分支语句

• if-then-else

#单分支：如果条件成立，执行sql
if conditions then

end if;

create procedure proc8(in a int)
begin
	if a = 1 then
		insert into classes(classname,remark) values ('C++','test');
	end if;
end;

#双分支：如果条件成立执行sql1，不成立执行sql2
if conditions then

else

end if;

create procedure proc8(in a int)
begin
	if a = 1 then
	insert into classes(classname,remark) values ('C++','test');
	else
	insert into students(stunum,stuname,stugender,stuage,cid,remark) values('2020305','H',"女"，19，1，'...');
	end if;
end;

• case

creat procedure proc9(in a int)
begin
	case a 
		when 1 then
			#如果a=1，执行sql1
			insert into classes(classname,remark) values ('C++','test');
		when 2 then
			#如果a=2，执行sql2
			insert into students(stunum,stuname,stugender,stuage,cid,remark) values('2020305','H',"女"，19，1，'...');
		else
			#如果a和所有when的值不匹配，执行此sql
			update students set stuage = 18 where stunum='2020305';
	end case;
end;

2. 循环语句

• while

create procedure proc10(in num int)
begin
	declare i int;
		set i = 0;
		while i<num do
			insert into classes(classname, remark) values(concat('Java',i),'...');
			set i = i+1;
		end while;
end;

call proc10(4);

• repeat

create procedure proc10(in num int)
begin
	declare i int;
		set i = 0;
		repeat
			insert into classes(classname, remark) values(concat('Java',i),'...');
			set i = i+1;
		until i>num end repeat;
end;

call proc10(4);

• loop

create procedure proc10(in num int)
begin
	declare i int;
		set i = 0;
		myloop:loop
			insert into classes(classname, remark) values(concat('Java',i),'...');
			set i = i+1;
			if i = num then
				leave myloop;
			end if;
		end loop;
end;

call proc10(4);

8.7 存储过程管理

1.查询存储过程

存储过程是属于某个数据库的，当我们在某个数据库创建存储过程，只能在当前数据库调用此存储过程。

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

#根据数据库名查询当前所有存储过程
show procedure status where db='dbname';

#查询存储过程的创建细节
show create procedure dbname.proc1;

2. 修改存储过程’

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

alter procedure <procname> 特征1 特征2...;

存储过程的特征参数：

• contains SQL 表示子程序包含SQL语句，但不包含读或写数据的语句
• no SQL 表示子程序不包含SQL语句
• READS SQL DATA 表示子程序中包含读数据的语句
• MODIFIES SQL DATA 表示子程序中包含写数据的语句
• SQL SECURITY {DEFINER/INVOKER} 之目谁有权限来执行
  • DEFINER表示只有定义者自己能执行
  • INVOKER表示调用者可以执行
• COMMENT ‘STRING’ 表示注释信息

alter procedure proc1 reads sql data;

3. 删除存储过程

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

ow procedure status where db=‘dbname’;

#查询存储过程的创建细节
show create procedure dbname.proc1;

2. 修改存储过程’

> 修改存储过程的特征、特性

```sql
alter procedure <procname> 特征1 特征2...;

存储过程的特征参数：

• contains SQL 表示子程序包含SQL语句，但不包含读或写数据的语句
• no SQL 表示子程序不包含SQL语句
• READS SQL DATA 表示子程序中包含读数据的语句
• MODIFIES SQL DATA 表示子程序中包含写数据的语句
• SQL SECURITY {DEFINER/INVOKER} 之目谁有权限来执行
  • DEFINER表示只有定义者自己能执行
  • INVOKER表示调用者可以执行
• COMMENT ‘STRING’ 表示注释信息

alter procedure proc1 reads sql data;

3. 删除存储过程

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