一、存储过程介绍

        存储过程是一组为了完成特定功能的 SQL 语句集合。MySQL 5.0 终于开始已经支持存储过程,它是数据库中最重要的功能,将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。

        为什么使用存储过程:

  • 封装性:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的 SQL 语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。
  • 可增强 SQL 语句的功能和灵活性存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
  • 可减少网络流量:由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。
  • 高性能存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能。
  • 提高数据库安全性和数据的完整性:使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限。

二、MySQL创建存储过程

语法与参数

语法:

CREATE PROCEDURE 过程名称([proc_parameter[,...]])
 [characteristic ...] routine_body 

说明

proc_parameter :表示 [ IN | OUT | INOUT ] param_name type
  • IN 输入参数:表示调用者向过程传入值
  • OUT 输出参数:表示过程向调用者传出值
  • INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值
type :表示任何有效的MySQL数据类型
characteristic :表示 
 LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'
routine_body 表示有效的SQL过程语句或语句 指:“过程体”
  1. NOT DETERMINISTIC :如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定的”,否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC,默认的就是NOT DETERMINISTIC。
  2. CONTAINS SQL :表示子程序不包含读或写数据的语句,默认的是CONTAINS SQL
  3. NO SQL表示子程序不包含SQL语句
  4. READS SQL DATA表示子程序包含读数据的语句,但不包含写数据的语句
  5. MODIFIES SQL DATA表示子程序包含写数据的语句
  6. SQL SECURITY特征可以用来指定 子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行。默认值是DEFINER。在SQL:2003中者是一个新特性。创建者或调用者必须由访问 子程序关联的数据库的许可。在MySQL 5.1中,必须有EXECUTE权限才能执行子程序。必须拥有这个权限的用户要么是定义者,要么是调用者,这取决于SQL SECURITY特征是如何设置的。

        要在MySQL 5.1中创建存储程序,必须具有CREATE ROUTINE权限,并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。

        默认地,子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来,可以在创建子程序的时候指定其名字为db_name.sp_name

in、out、inout的使用

1、无参数存储过程

/*创建一个无参数存储过程 */
DELIMITER $$
CREATE PROCEDURE p_student() # 声明存储过程
 BEGIN # 存储过程开始符号
 SELECT * FROM student;
 END$$ #存储过程结束符号
DELIMITER $$ # 声明语句结束符 也可以使用DELIMITER // 

# 调用存储过程
call p_student

 

2、in参数存储过程

/*in 输入参数的存储过程 输入id查询的行*/
DELIMITER $$
CREATE PROCEDURE p_student2(IN stu_num INT)
BEGIN
 SELECT * FROM student WHERE num = stu_num;
END$$
DELIMITER $$
# 调用存储过程
call p_student2(2);

3、out参数存储过程说明

/*out 的存储过程*/
DELIMITER $$
CREATE PROCEDURE p_student3(OUT stu_name VARCHAR(20))
 BEGIN
 SELECT NAME INTO stu_name FROM student WHERE num=2;
 END$$
 DELIMITER $$
# 调用存储过程 将执行的结果存储到@name中
call p_student3(@name);
# 查询@name
select @name

 4、inout参数存储过程说明

/*inout的存储过程*/
DELIMITER$$
CREATE PROCEDURE p_student4(INOUT stu_name VARCHAR(20))
BEGIN
 SELECT NAME INTO stu_name FROM student WHERE name=stu_name;
END$$
DELIMITER $$
# 设置 @name的值
set @name='张三';
# 将 @name传入并返回结果
call p_student4(@name);
# 查询返回的结果
select @name

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐