MySQL存储过程--实例教程

最新推荐文章于 2024-07-12 16:56:26 发布

longyangyangyang

最新推荐文章于 2024-07-12 16:56:26 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签：存储 mysql sql integer resources sql server

本文链接：https://blog.csdn.net/longyangyangyang/article/details/6260743

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL 5.0以后的版本开始支持存储过程，存储过程具有一致性、高效性、安全性和体系结构等特点，本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的。

技术要点

一个存储过程包括名字、参数列表，以及可以包括很多SQL语句的SQL语句集。下面为一个存储过程的定义过程：

create procedure proc_name ( in parameter integer )

begin

declare variable varchar (20 );

if parameter=1 thenset variable='MySQL ' ;

else

set variable='PHP ' ;

end if ;

insert into tb ( name) values ( variable);

end ;

MySQL中存储过程的建立以关键字create procedure开始，后面紧跟存储过程的名称和参数。MySQL的存储过程名称不区分大小写，例如PROCE1()和proce1()代表同一个存储过程名。存储过程名不能与MySQL数据库中的内建函数重名。

存储过程的参数一般由3部分组成。第一部分可以是in、out或inout。in表示向存储过程中传入参数；out表示向外传出参数；inout表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程，存储过程默认为传入参数，所以参数in可以省略。第二部分为参数名。第三部分为参数的类型，该类型为MySQL数据库中所有可用的字段类型，如果有多个参数，参数之间可以用逗号进行分割。

MySQL存储过程的语句块以begin开始，以end结束。语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程内部语句要以分号结束，所以在定义存储过程前应将语句结束标志“;”更改为其他字符，并且该字符在存储过程中出现的几率也应该较低，可以用关键字delimiter更改。例如： mysql>delimiter //

关于delimiter的更多作用见：delimiter的作用

存储过程创建之后，可用如下语句进行删除，参数proc_name指存储过程名。 drop procedure proc_name

第一个例子：hello word

创建一个helloword例子

CREATE PROCEDURE phelloword()

BEGIN SELECT 'Hello Word!' AS F;

END ;

在命令行下或者客户端工具下执行上面的语句将会报错，因为MYSQL默认的命令结束符为;一遇到;即立刻执行，因此应该为下面的语句

用delimiter声明结束符为//

DELIMITER //
CREATE PROCEDURE phelloword()
BEGIN SELECT 'Hello Word!' AS F;
END //

创建成功，执行CALL phelloword() ;将得到结果：Hello Word!

第二个例子：带参数的存储过程

delimiter //
create procedure proc_name ( in parameter integer )
begin
if parameter=0 then
select * from user order by id asc;
else
select * from user order by id desc;
end if ;
end ;
//

上面的例子参数：parameter，类型为integer,为输入类型，该存储过程解释为，如果输入的参数等于0则从user表中按照ID升序进行查询

否则按照降序查询结果

第三个例子：存储工程中声明变量并赋值

DROP PROCEDURE proc_name;
DELIMITER //
CREATE PROCEDURE proc_name ( IN parameter INTEGER )
BEGIN
DECLARE variable VARCHAR ( 20);
IF parameter=1 THEN
SET variable='Windows';
ELSE
SET variable='Linux';
END IF ;
SELECT variable;
END ;
//

解释：

DECLARE variable VARCHAR ( 20);声明变量 variable

SET variable='Windows';给 variable变量进行赋值

如果参数 paramete=1则 variable='Windows'; 否则 variable='Linux';

然后查询出variable的最终值

第四个例子：写一个包括参数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程

CREATE PROCEDURE plogin

(

p_username char (15),

p_password char (32),

p_ip char (18),

p_logintime datetime

)

LABEL_PROC:

BEGIN

DECLARE v_uid MEDIUMINT (8);

DECLARE v_realpassword CHAR (32);

DECLARE v_nickname VARCHAR (30);

DECLARE v_oltime SMALLINT (6);

SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime

FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;

IF (v_uid IS NULL ) THEN

SELECT 2 AS ErrorCode;

LEAVE LABEL_PROC;

END IF ;

IF (p_password <> v_realpassword) THEN

SELECT 3 AS ErrorCode;

LEAVE LABEL_PROC;

END IF ;

UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;

SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;

END LABEL_PROC //

注解：

1，赋值语句

SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime

FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;

2，LABEL_PROC

当满足某种条件，不继续执行下面的SQL时，在MS SQL SERVER中使用RETURN语法，在MySQL中我没有找到对应的关键字，但是这里可以利用LEAVE语法来满足要求，在存储过程的BEGIN前定义一个标签，如：“LABEL_PROC:” 然后再需要用到RETURN中断执行的地方执行“LEAVE LABEL_PROC;”即可

第五个例子：执行动态SQL的存储过程

CREATE PROCEDURE ipsp_getresourcedir

(

p_hashcode char (40)

)

LABEL_PROC:

BEGIN

DECLARE v_sql varchar (200);

SET v_sql = CONCAT ('SELECT filedir FROM ipsp_resources WHERE hashcode =/'', p_hashcode, '/' LIMIT 0, 1');

SET @sql = v_sql;

PREPARE sl FROM @sql;

EXECUTE sl;

DEALLOCATE PREPARE sl;

END LABEL_PROC //

注解：

这里提一下 “/” 是转义字符，拼接成的 SQL 类似 SELECT filedir FROM ipsp_resources WHERE hashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1

@sql 这个属于用户变量，基于会话变量实现的, 可以暂存值, 并传递给同一连接里的下一条sql使用的变量.当客户端连接退出时,

变量会被释放。

预编译语句的用法

PREPARE stmt_name
 FROM preparable_stmt
;--声明

EXECUTE stmt_name
 [USING @var_name
 [, @var_name
] ...];--执行

{DEALLOCATE | DROP} PREPARE stmt_name
;--删除

如下面的例子：

PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";
SET @p='1';
SET @q='2';
EXECUTE prod USING @p,@q;
SET @NAME='3';
EXECUTE prod USING @p,@NAME;
DEALLOCATE PREPARE prod;