存储过程

存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。

语法
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
– 代码 —SQL语句集
END

参数

存储过程根据需要可能会有输入、输出、输入输出参数，多个参数用","分割开。共有三种参数类型,IN,OUT,INOUT:

IN参数的值必须在调用存储过程时指定，在存储过程中可以修改该参数的值，但不能被返回

OUT该值可在存储过程内部被改变，并返回

INOUT调用时指定，并且可被改变和返回

调用

调用存储过程: CALL 存储过程名(参数列表);

删除

删除存储过程: DROP PROCEDURE 存储过程名;

实例1

输入参数有多个

定义

DROP PROCEDURE IF EXISTS p1;# 如果存储过程p1存在则删除
# 创建存储过程
CREATE PROCEDURE p1(IN a INT, IN b VARCHAR(20))
BEGIN
	select a;
	select b;
	set a=20;
	set b='hello world';
	select a;
	select b;
END;

调用1

call p1(10,'abcd')

调用2

set @x=12;
set @y='abc';
call p1(@x,@y);

select @x;
select @y;

@@:表示系统变量
@:表示自定义变量

实例2

带有传出参数

DROP PROCEDURE IF EXISTS p2;

CREATE PROCEDURE p2(out a INT, out b VARCHAR(20))
BEGIN
	select a;   # 会输出null，参数值传不进来
	select b;   # 会输出null，参数值传不进来
	set a=20;
	set b='hello world';
	select a;
	select b;
END;

set @m=12;
set @n='abc';
call p2(@m,@n);
select @m;   # 显示存储过程中设置的值  20
select @n;   # 显示存储过程中设置的值  hello world

实例3

输入输出参数

DROP PROCEDURE IF EXISTS p3;

CREATE PROCEDURE p3(INOUT a INT, INOUT b VARCHAR(20))
BEGIN
	select a;
	select b;
	set a=20;
	set b='hello world';
	select a;
	select b;
END;

调用

set @m=12;
set @n='abc';
CALL p3(@m,@n);

实例4

变量声明

DROP PROCEDURE IF EXISTS p4;

CREATE PROCEDURE p4(INOUT str VARCHAR(50))
BEGIN
  # DECLARE 变量名[,...] 变量类型 [DEFAULT 默认值]
  # 局部变量声明必须在最上面,并且中间还不能有任何其他代码
	DECLARE a VARCHAR(32);
	DECLARE b VARCHAR(32);
	DECLARE c VARCHAR(32) DEFAULT 'hello';

  select sno,sname into a,b from student where sno='200215121';

	set str=CONCAT(a,b,c);

END;

调用

set @s='';
CALL p4(@s);
select @s;

数据库编程-》

大数据-》

复杂的业务涉及到复杂的数据处理-》可以写成存储过程-》之后可以通过代码直接调用

-》存储过程执行需要占用服务器资源

实例5

IF-THEN–ELSEIF-THEN…–ELSE-END IF

DROP PROCEDURE IF EXISTS p5;

CREATE PROCEDURE p5(IN stu VARCHAR(10), IN cou INT)
BEGIN
    DECLARE DEG  INT;
    SELECT GRADE INTO DEG FROM sc WHERE SNO=stu and cno=cou;
    
    IF DEG>90
					THEN SELECT '优秀';
			ELSEIF DEG>85
					THEN SELECT '良好';
			ELSEIF  DEG>60
					THEN SELECT '及格';
			ELSE   select '不及格';
    END IF;
END;

调用

CALL p5('200215121',1)

实例6

[循环名:] LOOP
要循环的代码
END LOOP [循环名]

LEAVE 循环名:这个语句被用来退出任何被标注的流程控制构造 (跳出某个循环)
ITERATE 循环名:跳出某个循环,进入下一次循环

创建表，通过存储过程向表中插入100条数据

create table t1(
id int PRIMARY key auto_increment
)

存储过程

create PROCEDURE p6()
BEGIN
DECLARE i  INT DEFAULT 1;

	sta:LOOP
			 IF i>100
				 THEN LEAVE sta;
			 END IF;
			 
			 INSERT into t1 VALUES(null);
			 SET i = i + 1;
	END LOOP sta;

end;

调用

call p6();

# 清空表中的数据：删除表，重建表，可知id自增长序列，重新开始
TRUNCATE TABLE t1;
# 清空表中的数据：将数据删除，可知id自增长序列，一直在增加
delete from t1;

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。 说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

存储过程的游标cursor，遍历表中的每一行数据

cursor->指向查询结果-》之后可以依靠游标的移动，实现查询结果每一条记录的遍历操作

select * from student;