存储过程概念及实操

存储过程

1、什么是存储过程？
存储过程：
事先经过编译并存储在数据库中的一段sql语句的集合。

使用好处：
1、简化应用开发人员的很多工作
2、减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率

2、创建存储过程和函数
（1）创建存储过程
create procedure 存储过程名 ([proc_parameter[,…]])
[characteristic…]routine_body

（2）创建函数
create function 函数名([func_parameter[,…]])
returns type
[characteristic…]routine_body

proc_parameter:
[in|out|inout] param_name type
Func_paramter:
param_name type
Type:
任何有效的mysql数据类型
Characteristic:
language sql(默认，且推荐)
|[not] deterministic
|{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’
Rountine_body:
有效的sql 过程语句

3、调用存储过程或函数

（1）调用存储过程
call 存储过程名(参数列表)

（2）调用函数
Select 函数名(参数列表)

4、修改存储过程或函数

修改存储过程：
alter procedure 存储过程名 [charactristic…]

修改函数：
alter function 函数名 [charactristic…]

characteristic:
{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’

5、删除存储过程或函数
说明：一次只能删除一个存储过程或者函数，并且要求有该过程或函数的alter routine 权限

删除存储过程：
drop procedure [if exists] 存储过程名

删除函数：
drop function [if exists] 函数名

6、查看存储过程或函数
1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名

2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名

3.通过查看information_schema.routines了解存储过程和函数的信息（了解）
select * from rountines where rounine_name =存储过程名|函数名

三十三、存储过程实操

1、sqlyog 存储 ，里面的案例在dos演示
sqlyog 这个第三方客户端，不支持BEGIN 和 END，但是navicat这个第三方客户端支持begin和end
存储过程是不能修改的，非要修改，只能删除了，重新建
#存储过程
#一、创建

#语法：
#CREATE PROCEDURE 存储过程的名称(【IN/OUT/INOUT】 参数名 参数类型)
#BEGIN

主体

#END

#类似于java的方法

#修饰符 返回类型 方法名(参数列表){

方法体

#}

###1.参数对应的in、out、inout的意思
#IN 代表该参数 作为输入（传统意义的参数）
#OUT 代表该参数 作为输出 （传统意义的返回值）
#INOUT 代表该参数既可以作为输入又可以作为输出 （参数+返回）
#支持：多个IN、多个OUT、多个INOUT
#支持：无参

###2.主体
#增删改查语句都可以、变量、循环、条件等

#每条SQL语句结尾分号
#DELIMITER 结束符
#默认结束符：分号，假如不想分号结尾，则可以自己设置

#二、调用存储过程
/*
①调用无参的存储过程
CALL 存储过程名()
②调用in类型参数的存储过程
CALL 存储过程名(常量值)
③调用out类型参数的存储过程
call 存储过程名（@变量名） $$select@变量名$$
④调用inout类型参数的存储过程
set @变量名=值 $$CALL存储过程名（@变量名）$$
SELECT @变量名 $$

#三、用户变量：
定义语法： set @变量名 ;
赋值语法：
方式一：普通赋值
set @变量名:=值;或set @变量名=值;
select @变量名:=值;

	方式二：通过查询结果为变量赋值
	select 字段|表达式 into 变量名
	from 表名 【where 条件】		

*/

#★★★★★★★★★★★★★★★★★★★案例演示★★★★★★★★★★★★★★★★★★★

#一、创建无参的存储过程
#案例：创建存储过程，实现 查询beauty表的大于3号id的记录
window+R -> cmd -> mysql –uroot –proot

DELIMITER $$CREATEPROCEDUREmypro1()BEGINSELECT\ast FROMbeautyWHEREid>3;END$$

调用存储过程
call mypro1()$$

解决乱码
set names gbk$$

#二、创建in类型参数的存储过程
#案例：根据女神名称，查询男神信息
CREATE PROCEDURE mypro2(IN bname VARCHAR(20))
BEGIN
SELECT bo.* FROM boys bo
JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=bname;
END $$

#三、创建多个in类型参数的存储过程
CREATE PROCEDURE mypro3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
SELECT COUNT(*) FROM admin
WHERE admin.username=username AND admin.password=PASSWORD;
END $$

#四、创建out类型参数的存储过程
CREATE PROCEDURE mypro4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20),OUT result INT)
BEGIN
SELECT COUNT(*) INTO result FROM admin
WHERE admin.username=username AND admin.password=PASSWORD;
END $$

#调用存储过程
SELECT @a $$查看返回的值CALLmypro4{(}^{\prime \prime }{,}^{\prime \prime },@a)$$

#五、创建out类型参数的存储过程
CREATE PROCEDURE mypro5(IN a INT,IN b INT,OUT c INT,OUT d INT)
BEGIN
SET c=a2;
SET d=b2;

END KaTeX parse error: Expected 'EOF', got '#' at position 2: #̲调用存储过程 CALL myp…
SELECT @a $$

#六、创建inout类型参数的存储过程
CREATE PROCEDURE mypro6(INOUT age INT)
BEGIN
SET age=age*3;
END $$

#删除存储过程
DROP PROCEDURE 存储过程名
DELIMITER ;
DROP PROCEDURE mypro6 ;

#查看某个存储过程
SHOW CREATE PROCEDURE 存储过程名
SHOW CREATE PROCEDURE mypro1;

2、dos 实操
（1）创建存储过程，实现 查询beauty表的大于3号id的记录
C:> mysql -uroot –proot
mysql> use girls;
//创建
mysql> DELIMITER $$mysql$$