MySql(14)------mysql存储过程使用实例

来源:互联网 发布:哈桑.怀特塞德 数据 编辑:程序博客网 时间:2024/04/29 05:22

1. 存储过程简介

mysql 5.0开始支持存储过程,存储过程是存在数据库中的一段sql集合,调用存储过程可以减少很多工作量,

减少数据在数据库和应用服务器上的传输,对于提高数据处理的效率,同时注意,存储过程没有or replace的关键字,

mysql的存储过程参数包括in,out,inout三种模式。

创建存储过程语法:
CREATE PROCEDURE proc_name(proc_parameter[,...])
[characteristic...]
routine_body

参数代表含义:
proc_parameter: [IN | OUT | INOUT]  param_name type


characteristic:
LANGUAGE SQL | [NOT]DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'String'

routine_body: 存储过程


更改存储过程语法:
ALTER PROCEDURE proc_name [characteristic...]

characteristic:
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'String'

存储过程调用:

CALL pro_name([parameter,......]);


2. 实例分析准备条件

创建表:

CREATE TABLE `t_user_main` (  `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',  `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',  `f_age` int(3) DEFAULT NULL COMMENT '年龄',  PRIMARY KEY (`f_userId`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据:

INSERT INTO t_user_main (f_userName, f_age) VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);

3. 实例分析存储过程

创建一个简单的实例,该存储过程统计t_user_main表的条数,过程参数为out模式。

# 创建存储过程DELIMITER $$ -- 定界符# 开始创建存储过程CREATE PROCEDURE user_main_pro(OUT s INT)BEGIN  SELECT COUNT(*) INTO s FROM t_user_main;END $$ # 创建存储过程结束DELIMITER;# 恢复数据库定界符
关于创建存储过程的分析:

mysql中创建存储过程与oracle等数据库中不同的是,mysql需要使用定界符(DELIMITER),

因为mysql默认 ‘分号' 为运行结束,通过定界符指定结束符号,避免mysql创建存储过程

编译报错。当存储过程创建成功后,再通过DELIMITER ;将mysql语句结束符号恢复为分号。

这个地方使用out模式,存储过程再被调用时,会返回out参数,将t_user_main总条数返回,

通过变量接受存储过程返回的执行结果。

存储过程调用:

通过call去调用存储过程,set设置的变量接受call调用时存储过程out模式返回的处理结果。


in 和out一起使用:

DELIMITER $$CREATE PROCEDURE user_main_pro4(IN v_id INT,OUT v_count INT)BEGINSELECT COUNT(*) INTO v_count FROM t_user_main WHERE f_userId > v_id;END $$DELIMITER;

调用:


4. 变量声明和赋值

MySql中变量从5.1后不区分大小写。

变量的声明:

通过DECLARE可以定义一个局部变量,变量的作用范围BEGIN...END块中;

变量语句必须卸载复合语句开头,并且在其他语句的前面;

一次性可以声明多个变量;

变量定义语法:DECLARE var_name[,...] type [DEFAULT value]


***存储过程中定义变量的用法:

DELIMITER $$CREATE PROCEDURE user_main_pro3(IN v_id INTEGER)BEGIN  #定义两个变量  DECLARE v_userName VARCHAR(50);  DECLARE v_userName2 VARCHAR(50);  #set赋值  SET v_userName = 's';  #SELECT ... INTO 赋值  SELECT f_userName INTO v_userName2 FROM t_user_main   WHERE f_userId = v_id;  #DDL语句  INSERT INTO t_user_main (f_userName) VALUES(CONCAT(v_userName,'*',v_userName2));END $$DELIMITER;

存储过程调用:


变量赋值:变量可以通过直接赋值,也可以通过查询语句赋值。

直接赋值语法:SET var_name = expr[,var_name=expr]...

在上面存储过程中,定义一个v_testSet变量,通过set直接赋值,eg:

DECLARE v_testSet VARCHAR(50);

SET v_testSet = 't';


通过select...INTO...赋值,通过这种方式赋值,要求查询返回只有一行结果,

使用语法:

SELECT col_name[,...] INTO var_name[,...] table_expr;

eg:

SELECT f_userName INTO v_userName2 FROM t_user_main WHERE f_userId = v_id;


5. 查看存储过程状态和定义语句

查看存储过程状态语法:

SHOW PROCEDURE STATUS [LIKE 'pattern']

查看存储过程的定义语法:

SHOW CREATE PROCEDURE pro_name;

eg:


6. 删除存储过程

DROP PROCEDURE [IF EXISTS] pro_name;

0 0