数据库管理——存储过程和函数

标签：语句 tmp 存储 name -- 数据库 student id 函数

存储过程和函数

​ 存储过程和函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务端的数据传输。

创建存储过程

​ 创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。

CREATE PROCEDURE procedure_name([proc_param[,...]])
	routine_body

• 参数procedure_name表示要创建的存储过程名字
• 参数proc_param表示存储过程的参数
• 参数routine_body表示存储过程的SQL语句代码，可以用 BEGIN…END来标志SQL语句的开始和结束。

• 在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。

• proc_param中每个参数的语法形式如下：

  [IN|OUT|INOUT] param_name type
  

  • IN 输入类型、OUT 输出类型、INOUT（输入 / 输出类型）
  • param_name表示参数名
  • type表示参数类型，可以是任意一个数据类型

• use school;
  delimiter $$
  create procedure proc_delete_student(IN sid int)
  BEGIN
  	declare cid int;--定义变量cid
  	select class_id into cid from student where id = sid; --通过查询语句设置变量
  	delete from grade where id = sid;	--删除成绩表中的记录
  	delete from student where id = sid;	  --删除学生表中的记录
  	update class set count=count-1 where id = cid;	--更新班级表中的记录
  END;
  $$
  delimiter ; --把 结束符 换回来
  call proc_delete_student(2);--调用存储过程
  

在存储过程中使用变量

​ 在存储过程和函数中，可以定义和使用变量。用户可以使用关键字DECLARE来定义变量，然后为变量赋值。

定义变量

使用declare关键字定义变量

DECLARE var_name[,…] type [DEFAULT value];#可同时定义多个变量，默认值为 value，没写的默认值为NULL

定义变量cid，数据类型为INT型，默认值为10，代码如下：
declare cid INT DEFAULT 10;

为变量赋值

使用关键字SET来为变量赋值

SET var_name=expr[,var_name=expr]…#可以为多个变量赋值，参数var_name是变量的名称；expr是赋值表达式

SET tmp id = 88;

也可以使用SELECT...INTO语句赋值

select col_name[,...]INTO var_name[,...]
FROM table_name WHERE condition
--参数col_name表示查询的字段名称；
--参数var_name是变量的名称；
--参数table_name指表的名称；
--参数condition指查询条件。

• 从表employee中查询id为3的记录，将该记录的id值赋给变量tmp_id

  SELECT id INTO tmp_id FROM grade WHERE id = sid;
  

  use school;
  drop procedure if exists query_student_class_info;--如果有同名的存储过程先删除
  delimiter $$
  create procedure query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount int)
  	BEGIN
  		declare tmp_name varchar(128);
  		declare tmp_count int;
  		declare tmp_cid int;
  		select class_id into tmp_cid from student where id = sid;
  		select name,count into tmp_name, tmp_count from class where id = tmp_cid;
  		set cname = tmp_name, ccount=tmp_count;
  	END;
  	$$
  DELIMITER;
  --查询学生班级信息
  call query_student_class_info(4,@name,@count);--调用存储过程
  select @name,@count;--输出传入的参数的值
  

光标的使用

​ 查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标成为游标；光标必须声明在处理程序之前，并且声明在变量和条件之后。

• 声明光标

  • declare cursor_name CURSOR
    	FOR select_statement;
    --cursor_name表示光标的名称
    --select_statement表示SELECT语句
    
    --声明一个名为cur_student的光标
    use school;
    delimiter $$
    create procedure query_student(IN sid int, OUT cname varchar(128),OUT class_id int)
    BEGIN 
    	declare cur_student cursor for select name,class_id from student;
    END;
    $$
    delimiter;
    --cur_student光标的名称
    

• 打开光标

  • OPEN cursor_name; --参数cursor_name表示光标的名称
    OPEN cur_student; --
    

• 使用光标

  • FETCH cursor_name INTO var_name[,var_name...];
    --cursor_name表示光标的名称
    --var_name表示光标中的select语句查询出来的信息存储该参数中
    --var_name必须在声明光标之前就定义好
    

  • use school;
    delimiter $$
    create procedure query_student(IN sid int,OUT cname varchar(128),OUT cid int)
    BEGIN 
    	declare tmp_name varchar(128);
    	declare tmp_cid int;
    	declare  done int default 0;
    	declare cur_student CURSOR FOR SELECT name,class_id FROM student where id = sid;
    	declare continue handler for not found set done = 1;
    	open cur_student;--打开光标
    	select done;
    	fetch cur_student into tmp_name,tmp_cid;--使用光标
    	select done;
    	select tmp_name,tmp_cid;
    	close cur_student;--关闭光标
    	set cname=tmp_name,cid=tmp_cid;
    END;
    $$
    delimiter ;
    

• 关闭光标

  close cur_student;
  

流程控制的使用

IF语句

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list]...
[ELSE statement_list]
END IF
--search_condition表示条件判断语句
--statement_list表示不同条件的执行语句
eg：
IF age>20 THEN SET @count1=@count1+1;
	ELSEIF age=20 THEN @count2=@count2+1;
	ELSE@count3=@count3+1;
END IF;
-----------------------------------------------------------
use school;
DELIMITER $$
create procedure proc_test_if (IN input int, OUT output int)
begin
	if input>20 then set input=input+1;
	elseif input=20 then set input=input+2;
	else set input = input+3;
	end if;
	set output = input;
end;
$$
delimiter;

CASE语句

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]
[ELSE statement_list]
END CASE
--case_value表示条件判断的变量
--when_value表示变量额取值
--statement_list表示不同when_value值的执行语句
------------------------------------------------------------
CASE level
	WHEN 20 THEN SET attack = attack + 5; -- 当 level == 20 时
	WHEN 30 THEN SET attack = attack + 10;
	WHEN 40 THEN SET attack = attack + 15;
	ELSE SET attack = attack + 1;
END CASE

LOOP语句

LOOP语句可以使某些特定的语句重复执行，实行一个简单的循环。LOOP语句本身没有停止循环，只有遇到LEAVE语句等才能停止循环。

[begin_label:]LOOP -- begin_label、end_label表示循环开始和结束的标志
statement_list	   -- statement_list表示需要循环执行的语句
END LOOP[end_label]
----------------------------------------------------------
add_num:LOOP
	SET @count = @count + 1;
END LOOP and_num;
--这是一个死循环

LEAVE语句

主要用于跳出循环控制

LEAVE label --label表示循环标志
add_num:LOOP
	SET @count = @count + 1;
	Select@count;
	IF @count=100 THEN
		LEAVE add_num;
	END IF;
END LOOP and_num;

ITERATE iterate

ITERATE 语句也是用来跳出循环的语句，是指跳出本次循环（continue），进入下一次循环

ITERATE label
-----------------------------------------------------------
add_num1:LOOP
	SET @count = @count + 1;
	IF @count = 100 THEN
		LEAVE add_num1    --break;
	ELSE IF MOD(@count,3) = 0 then --count mod 3 == 0 
		ITERATE add_num1; --continue;
	Select * from student;
END LOOP and_num1;

REPEAT语句 repeat

REPEAT语句是有条件控制的循环语句。当满足特定条件时就会跳出循环语句

[begin_label:] REPEAT
	statement_list;
	UNTIL search_condition ---知道search_condition，结束循环
END REPEAT [end_label]
-------------------------------------------------------------
REPEAT
	SET @count=@count+1;
	UNITIL @count=100
END REPEAT;
--REPEAT循环都用END REPEAT结束

WHILE语句

WHILE也是有条件控制的循环语句。WHILE语句是当满足条件时执行循环内的语句

[begin_label:]WHILE search_condition DO 
	Statement_list
END WHILE[end_label]
--statement_condition表示循环执行的条件
--statement_list表示循环的执行语句
------------------------------------------------------------
WHILE @count<100 DO
	SET @count = @count + 1;
END WHILE;

流程控制综合运用

--循环访问光标操作，访问光标中的所有记录，代码如下：--
use school;
delimiter $$
create procedure query_all_students(IN sid int, OUT cname varchar(128),OUT cid int)
BEGIN
	declare tmp_name varchar(128);--#必须定义在声明光标之前
	declare tmp_cid int;
	declare done int default 0;
	declare cur_student CURSOR FOR SELECT name,class_id FROM student;
	declare continue handler for not found set done=1;--#将结束标志绑定到游标上
	open cur_student;
	read_loop:LOOP --循环读取
		fetch cur_student into tmp_name,tmp_cid;
		IF done=1 then
			Leave read_loop;
		END IF;
		select  tmp_name,tmp_cid; --打印从光标中获取到的值
	END LOOP read_loop;
	close cur_student;
	set cname = tmp_name,cid = tmp_cid;
END;
$$
delimiter;
--在学生表中插入一条记录，并返回记录的自增长id
use school;
DELIMITER $$
create procedure fetch_insert_student_id(IN p_name varchar(128),in p_class_id int, IN p_sex char(1),OUT rid int)
BEGIN
	insert into student(name,class_id,sex) values(p_name,p_class_id,p_sex);
	select last_insert_id() as rid;
END;
$$
DELIMITER;

查看存储过程

• SHOW STATUS语句查看存储过程

  SHOW PROCEDURE STATUS [like'pattern'];
  

• SHOW CREATE 语句查看存储过程的定义

  SHOW CREATE PROCEDURE proc_name
  --proc_name表示存储过程的名称
  --查询名为proc_delete_student的存储过程的状态
  show create procedure proc_delete_student\G
  

• 从``information_schema.Routine`表中查看存储过程的信息

  select * from information_schema.Routines
  	Where ROUTINE_NAME = 'proc_name';
  --ROUTINE_NAME是Routines存储 存储过程和函数的列 的名称
  --参数proc_name表示 存储过程或函数名称
  --从Routines表中查询名为proc_delete_student的存储过程信息
  select routine_definition from information_schema.Routines where routine_name='proc_delete_student';
  

  存储过程的删除

  DROP PROCEDURE proc_name;
  drop procedure proc_delete_student;
  

标签：语句,tmp,存储,name,--,数据库,student,id,函数
From： https://blog.csdn.net/acm10139/article/details/145482611