mysq--存储过程示例--各种存储过程的创建和执行

与java相比，用sql语句对数据查询结果做基础的数据操逻辑处理有更高的效率，从而可以提升系统的运行速度，而存储过程作为数据操作的一个重要工具，个人认为，作为一个经常与数据库打交道的 java程序员有必要了解一下，存储过程不仅可以提升查询效率，更可以完成大部分的数据逻辑处理，还可以有效防止SQL注入式攻击。

创建数据库表，用于测试

tb_user(id,name,sex,age)

drop table if EXISTS tb_user;
create table tb_user(
id int(11) primary key auto_increment,
name varchar(18) default null,
sex char(2) default null,
age int(11) default null
);

查看表结构

select * from tb_user;

插入一行记录

insert into tb_user(name,sex,age) values(‘张三丰’,‘男’,130);

查看表数据

select * from tb_user;

编写存储过程并测试

插入数据的存储过程

	DROP PROCEDURE IF EXISTS insert_user;
		DELIMITER // #这条命令是mysql容器解释的，该段命令是否已经结束，mysql是否可以执行了，在默认情况下，分号就是结束；
	# 存储过程头部分	
	CREATE PROCEDURE insert_user
		(
			OUT v_id INTEGER,			#出参，即返回时使用
			IN v_name VARCHAR(18), 	#入参
			IN v_sex VARCHAR(19),		#入参
			IN v_age INTEGER)				#入参
		#存储过程体
	BEGIN
		# 执行insert操作，values中的值是传入的参数
		INSERT INTO tb_user(NAME,sex,age) VALUES(v_name,v_sex,v_age);
		# 将insert成功自动生成的主键id值设置到变量v_id中返回
		SET v_id = LAST_INSERT_ID();
	END;

<!--mybatis使用时mapper.xml文件的写法-->
  <insert id="saveUser" parameterType="com.zfh.domain.User" statementType="CALLABLE">
  	{
  		call insert_user(
  			#{id,mode=OUT,jdbcType=INTEGER},
  			#{name,mode=IN},
  			#{sex,mode=IN},
  			#{age,mode=IN}
  		)
  	}
  </insert>

查询所有数据的存储过程

DROP PROCEDURE IF EXISTS select_user;
DELIMITER //
CREATE PROCEDURE select_user()
BEGIN
SELECT id,NAME,sex,age FROM tb_user;
END

<!--mybatis使用时mapper.xml文件的写法-->
   <select id="selectUser" resultType="com.zfh.domain.User" statementType="CALLABLE">
  	{
  		call select_user()
  	}
  </select>

根据id查询数据的存储过程

DROP PROCEDURE IF EXISTS select_user_by_id;
DELIMITER //
CREATE PROCEDURE select_user_by_id(IN v_id INTEGER)
BEGIN
SELECT id,NAME,sex,age FROM tb_user WHERE id = v_id;
END

<!--mybatis使用时mapper.xml文件的写法-->
    <select id="selectUserById" parameterType="int" resultType="com.zfh.domain.User" statementType="CALLABLE">
  	{
  		call select_user_by_id(
  			#{id,mode=IN}
  		)
  	}
  </select>

修改数据的存储过程

DROP PROCEDURE IF EXISTS update_user;
DELIMITER //
CREATE PROCEDURE update_user
(
IN v_id INTEGER,
IN v_name VARCHAR(18),
IN v_sex VARCHAR(19),
IN v_age INTEGER)
BEGIN
UPDATE tb_user SET NAME = v_name,sex = v_sex,age = v_age
WHERE id = v_id;
END

<!--mybatis使用时mapper.xml文件的写法-->
   <update id="modifyUser" parameterType="com.zfh.domain.User" statementType="CALLABLE">
  	{
  		call update_user(
  			#{id,mode=IN},
  			#{name,mode=IN},
  			#{sex,mode=IN},
  			#{age,mode=IN}
  		)
  	}
  </update>

删除数据的存储过程

DROP PROCEDURE IF EXISTS delete_user_by_id;
DELIMITER //
CREATE PROCEDURE delete_user_by_id(IN v_id INTEGER)
BEGIN
DELETE FROM tb_user WHERE id = v_id;
END

<!--mybatis使用时mapper.xml文件的写法-->
   <delete id="removeUser" parameterType="com.zfh.domain.User" statementType="CALLABLE">
  	{
  		call delete_user_by_id(
  			#{id,mode=IN}
  		)
  	}
  </delete>

在navicat中的写法和用法

select * from tb_user;
call select_user_by_id(2);
SELECT id,NAME,sex,age FROM tb_user WHERE id = 2;

更多的MYSQL存储过程操作

使用SHOW STATUS查看状态

SHOW STATUS查看存储过程和函数的状态，语法如下：
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘parttern’]
这个语句是MySQL的扩展，它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数名称。
示例：
SHOW PROCEDURE STATUS LIKE ‘proc_%’;

使用SHOW CREATE查看定义

使用SHOW CREATE语句查看存储过程和函数的状态，语法如下：
SHOW CREATE {PROCEDURE | FUNCTION} proc_or_func
这个语句是一个MySQL的扩展，类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数，proc_or_func表示匹配存储过程或函数名。
示例：
SHOW CREATE PROCEDURE proc_countByName;

从information_schema.Routines查看信息

MySQL存储过程和函数的信息存储在information_schema数据库下的Routines表中。通过查询该表的记录查询信息，SQL如下：
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = ‘proc_or_func’