MySQL存储过程

存储过程定义：是一组为了完成特定功能的sql语句的集合 类似于函数 写好了一个存储过程之后 可以像函数一样随时调用SQL语句的集合 主要用在复杂的 需要很多sql语句联合执行完成的任务

存储过程在执行上比sql语句的速度快 效率也更高

#先建一张表

CREATE TABLE info(

id INT PRIMARY KEY,

name VARCHAR(12),

score DECIMAL(5,2),

pass VARCHAR(12)

);

#创建存储过程 delimiter $$:将语句的结束符号从分号 临时改变成两个$$

delimiter $$

CREATE PROCEDURE proc ()

BEGIN

SELECT * FROM info;

END $$

delimiter;

#proc 不能重复在当前库唯一

#begin 过程体开始的关键字 后面跟需要执行的SQL语句

#end$$ 表示存储过程中所有语句结束 和上面的定义开始符号一一对应

#delimiter 将结束语句的分号恢复 作用就是保证整个存储过程中的SQL语句能够完整执行

#查看库中所有存储过程：

SHOW PROCEDURE STATUS WHERE db='kgc';

SHOW PROCEDURE STATUS LIKE '%proc';

#调用存储过程

CALL proc;

#删除存储过程

DROP PROCEDURE IF EXISTS test1;

#存储过程中有三种运用的方式

#1、in 输入参数 调用者向存储过程传入值

#2、out 输出参数 表示存储过程像调用者传输值 可以返回多个值

#3、inout 表示调用者先向存储过程传入值 存储过程对传入值可能进行额外的操作之后再返回给调用者

#给存储过程传参

delimiter $$

CREATE PROCEDURE test1 (in uname char(20))

BEGIN

SELECT * FROM info WHERE name='uname';

SELECT * FROM info;

UPDATE info SET name='李四' WHERE name=uname;

end $$

delimiter;

CALL test1('张三');

#数据清洗和指定数据入库 分析用户行为

#传出参数

delimiter $$

CREATE PROCEDURE test2(OUT num INT)

BEGIN

SET num = 100;

END $$

delimiter;

CALL test2(@num);

INSERT INTO info VALUES(2,'张三',@num,'否');

#输入和输出参数

delimiter $$

CREATE PROCEDURE test3(inout str VARCHAR(10))

BEGIN

SELECT str; -- 显示输入字符

SET str = CONCAT(str, '_ky32');-- 让传入的字符串做拼接

SELECT str; -- 加工之后的字符串

END $$

delimiter;

SET @str='麻子';

CALL test3(@str);

UPDATE info SET name=@str WHERE id = 1;

in :先传入 定义变量的值 初始值

call test3 调用存储过程 把变量的值传入存储过程

#存储过程中控制语句

delimiter $$

CREATE PROCEDURE test4 (in num INT)

BEGIN

if num>=10 THEN

SET num=num-6;

ELSE

SET num =num*2;

END IF;-- 结束if语句判断

SELECT num;

END $$

delimiter;

SET @num = 19;

CALL test4 (@num);

UPDATE info SET id = @num WHERE score = 100;

#调用多个参数 范围的方式匹配 完成传参

delimiter $$

CREATE PROCEDURE test5 (INOUT score INT,OUT grade VARCHAR(15))

BEGIN

 IF score BETWEEN 85 AND 100 THEN

 SET grade = '优秀';

 ELSEIF score BETWEEN 60 AND 84 THEN

 SET grade = '一般';

 ELSE

 SET grade = '不及格';

 END IF;

SELECT grade;

END $$

delimiter;

SET @score =55;

CALL test5(@score,@grade);

UPDATE info SET score=@score,pass=@grade WHERE id = 19;

#WHILE循环语句

delimiter $$

CREATE PROCEDURE test7 (OUT result INT)

BEGIN

DECLARE a INT;-- 在过程体中声明变量

DECLARE i INT;

SET a = 10;

SET i = 1;

WHILE i <=10 DO

SET a = a+10;

SET i = i+1;

END WHILE;

SET result = a;

END $$

delimiter;

SET @result=110;

CALL test7(@result);

SELECT @result;

使用存储过程中 在内部变量不需要加@ 外部调用时需要加@引用变量

在存储过程begin之后声明变量要用declare 声明变量要加数据类型

要使用存储过程里面的结果必须要 out才能传出参数 在声明存储变量时 要定义好参数的传参方式inout