第十一章:存储过程
编写存储过程并不是件简单的事情,但是使用存储过程可以简化操作,且减少冗余的操作步骤,同时,还可以减少操作过程中的失误,提高效率,因此应该尽可能的学会使用存储过程。
下面主要介绍如何创建存储过程。
本节目标:理解什么是存储过程及语法结构。
存储过程:存储过程是SQL语句和流程控制语句的预编译集合,并以一个名称存储并作为一个单元进行处理。
存储过程的语法结构
CREATE PROCEDURE proc_name([proc_parameter])
[characteristics...] routine_bodyproc_parameter指定存储过程的参数列表,形式如下︰
[IN|OUT|INOUT] param_name type过程体
过程体由合法的SQL语句构成;
过程体可以是任意sQL语句;
过程体如果为复合结构则使用BEGIN...END语句;
复合结构可以包含声明,流程控制语句;
-- 创建一个查询图书的编号、书名、价格和库存的存储过程。 delimiter // create procedure selectproc1() begin select book_id,book_name,price,store from bookinfo; end// delimiter ; -- 调用存储过程 call selectproc1();
本节目标:掌握存储过程的创建和使用
一、存储过程的语法结构
CREATE PROCEDURE proc_name([proc_parameter])
[characteristics...] routine_bodyproc_parameter指定存储过程的参数列表,形式如下︰
[IN|OUT|INOUT] param_name type
参数
IN:表示该参数的值必须在调用存储过程时指定;
OUT:表示该参数的值可以被存储过程改变,并且可以返回;
INOUT:表示该参数的调用时指定,并且可以被改变和返回
调用存储过程
CALL proc_name([parameter[...]]);
CALL proc_name[()];
1、创建无参的存储过程
举例︰创建查询图书编号、书名、图书类别的存储过程。
delimiter // create procedure proc1() begin select book_id,book_name,category from bookinfo t1 join bookcategory t2 on t1.book_category_id = t2.category_id; end// delimiter ; call proc1();
2、创建带有输入参数和输出参数的存储过程。
举例︰设计一个存储过程,删除一个读者,并输出剩余读者的个数。
delimiter // create procedure proc2(in cid char(18), out num int) begin delete from readerinfo where card_id = cid; select count(card_id) into num from readerinfo; end// delimiter ; select * from readerinfo; call proc2('210210199901011111', @num); select @num;
3、创建带有输入输出参数的存储过程。
举例︰设计一个存储过程,实现交换两个数的处理。
delimiter // create procedure proc3(inout num1 int, inout num2 int) begin declare t int default 0; set t = num1; set num1 = num2; set num2 = t; end// delimiter ; set @n1 = 3, @n2 = 5; call proc3(@n1,@n2); select @n1,@n2;
二、删除存储过程
语法结构:
DROP PROCEDURE [IF EXISTS] proc_name;
案例:删除存储过程 drop procedure proc1; drop procedure if exists proc2;
本节目标:掌握符合结构存储过程的操作。
一、使用条件判断语句的存储过程。
举例︰设计比较两个数大小的存储过程。
delimiter // create procedure proc5(in num1 int, in num2 int, out result varchar(30)) begin /*if num1 = num2 then set result = 'num1等于num2'; elseif num1>num2 then set result = 'num1大于num2'; else set result = 'num1小于num2'; end if;*/ case when num1 = num2 then set result = 'num1等于num2'; when num1 > num2 then set result = 'num1大于num2'; else set result = 'num1小于num2'; end case; end// delimiter ; call proc5(5,10,@result); select @result;
二、使用循环控制语句的存储过程
举例︰设计一个存储过程,向数据表插入100条数据的存储过程。
select floor(rand()*5); delimiter // create procedure proc6() begin declare n int default 7; while n<=107 do insert into bookcategory values(n,concat('图书类别',n),floor(rand()*n)); set n = n + 1; end while; end// delimiter ; call proc6();
本节目标:理解存储过程与函数的区别。
存储过程与函数的区别:
1、功能上的不同
存储过程:一般来说,存储过程实现的功能要复杂一点。功能强大,可以执行包括修改表等一系列数据库操作。
存储函数:实现的功能针对性比较强。
2、返回值上的不同
存储过程:可以返回多个值,也可以不返回值,只是实现某种效果或动作。
存储函数:必须有返回值,而且只能有一个返回值。
3、参数的不同
存储过程:存储过程的参数类型有三种,IN、OUT、INOUT。
存储函数:参数类型只有一种,类似于IN参数。调用函数时需要按照参数的类型指定值即可。
4、语法结构上的不同
存储过程:存储过程声明时不需要指定返回类型。
存储函数:函数声明时需要指定返回类型,且在函数体中必须包含一个有效的RETURN语句。
5、调用方式上的不同
存储过程:一般是作为一个独立的部分来执行,用CALL语句进行调用。
存储函数:嵌入在sql中使用的,可以在select中调用。