mysql存储过程和函数--学习

文章目录

一、存储过程1 .存储过程和函数概述2.创建存储过程3 调用存储过程4 查看存储过程5 删除存储过程6 语法6.1 变量6.2 游标/光标 二、存储函数

一、存储过程

1 .存储过程和函数概述

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开 发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。 函数 ： 是一个有返回值的过程 ； 过程 ： 是一个没有返回值的函数 ；

2.创建存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) begin -- SQL语句 end ;

eg：

delimiter $ --声明分隔符为$ create procedure pro_test1() begin select 'Hello Mysql' ; end$ delimiter ;

3 调用存储过程

call procedure_name() ;

4 查看存储过程

-- 查询存储过程的状态信息 show procedure status; -- 查询某个存储过程的定义,test是数据库名 show create procedure test.pro_test1 \G;

5 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

6 语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能

6.1 变量

DECLARE，通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。 DECLARE var_name[,...] type [DEFAULT value] eg:

delimiter $ create procedure pro_test2() begin declare num int default 5; select num+ 10; end$ delimiter ;

SET 直接赋值使用 SET，可以赋常量或者赋表达式

DELIMITER $ CREATE PROCEDURE pro_test3() BEGIN DECLARE NAME VARCHAR(20); SET NAME = 'MYSQL'; SELECT NAME ; END$ DELIMITER ;

存储过程可以使用语法，判断，传参 create procedure procedure_name([in/out/inout] 参数名 参数类型) ... IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认 OUT: 该参数作为输出，也就是该参数可以作为返回值 INOUT: 既可以作为输入参数，也可以作为输出参数

eg：

create procedure pro_test6(in height int , out description varchar(10)) begin if height >= 180 then set description='身材高挑'; elseif height >= 170 and height < 180 then set description='标准身材'; else set description='一般身材'; end if; end $

调用：

call pro_test5(168, @description)$ select @description $

小知识 @description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用 的，这个类似于全局变量一样。 @@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量

6.2 游标/光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用 包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

--声明光标： DECLARE cursor_name CURSOR FOR select_statement ; --OPEN 光标： OPEN cursor_name ; --FETCH 光标： FETCH cursor_name INTO var_name [, var_name] ... --CLOSE 光标： CLOSE cursor_name ;

eg：

-- 查询emp表中数据, 并逐行获取进行展示 create procedure pro_test11() begin declare e_id int(11); declare e_name varchar(50); declare e_age int(11); declare e_salary int(11); declare emp_result cursor for select * from emp; open emp_result; fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); close emp_result; end $

调用：

二、存储函数

语法结构:

CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN ... END;

eg： 定义一个存储过程, 请求满足条件的总记录数 ;

delimiter $ create function count_city(countryId int) returns int begin declare cnum int ; select count(*) into cnum from city where country_id = countryId; return cnum; end $ delimiter ;

调用:

select count_city(1); select count_city(2);