MySQL5存储过程视频教程,PHP操作MySQL存储过程示例

MySQL5存储过程视频教程,PHP操作MySQL存储过程示例


储过程介绍

存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量 。同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。


存储过程优点

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高得多。

(2)执行速度更快。存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接中读取。

(3)更强的安全性。存储过程是通过向用户授予权限(而不是基于表),它们可以提供对特定数据的访问,提高代码安全,比如防止 SQL注入。

(4) 业务逻辑可以封装存储过程中,这样不仅容易维护,而且执行效率也高。


存储过程优点

1 可移植性方面:当从一种数据库迁移到另外一种数据库时,不少的存储过程的编写要进行部分修改。

2 存储过程需要花费一定的学习时间去学习,比如学习其语法等。

在MYSQL中,推荐使用MYSQL Query Browswer(http://dev.mysql.com/doc/query-browser/en/)这个工具去进行存储过程的开发和管理。下面分步骤来学习MYSQL中的存储过程。


存储过程教程


1 定义存储过程的结束符

在存储过程中,通常要输入很多SQL语句,而SQL语句中每个语句以分号(;)来结束,因此要告诉存储过程,什么位置是意味着整个存储过程结束,所以我们在编写存储过程前,先定义分隔符,我们这里定义"//"为分隔符,我们使用DELIMITER //这样的语法,就可以定义结束符了,当然你可以自己定义其他喜欢的符号。


2 创建存储过程与存储函数

     格式:
    CREATE PROCEDURE过程名 ([过程参数[,...]])
         [特性 ...] 过程体

     过程参数: [ IN | OUT | INOUT ] 参数名 参数类型

    CREATE FUNCTION函数名 ([函数参数[,...]])
        RETURNS 返回类型
        [特性 ...] 函数体

        函数参数:参数名 参数类型
       返回类型: 有效的MySQL数据类型即可
 

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

    过程体/函数体:格式如下:    
    BEGIN
           有效的SQL语句
    END

    实例

 DELIMITER $$
 DROP PROCEDURE IF EXISTS `spTest` $$
 CREATE PROCEDURE `spTest`(p_title VARCHAR(200), p_description TEXT)
  DETERMINISTIC
  BEGIN
        INSERT INTO category (title, description) VALUES (p_title, p_description);
  END
 $$
 DELIMITER ;

 其中DELIMITER是用来决定SQL分隔符号的,在写Stored Procedure前,要先把预设的分号换掉,写完 Stored Procedure 的 routine_body 后,再回复成预设的分号。
 --删除
 drop procedure if exists up_common_select
 --创建
 CREATE  PROCEDURE `up_common_select`(
  in t_name varchar(50)
 )
 begin
  declare v_sql varchar(500);
  set v_sql= concat('select * from ',t_name);
  select v_sql;
  --
 --注意:prepare(预处理)execute stmt using @var,只能跟@var变量,declare和传入的变量不行!!!
  set @v_sql=v_sql;
  prepare stmt from @v_sql;
  EXECUTE stmt ;
  deallocate prepare stmt;

 end;
 --调用
 call up_common_select('admin_authority');
 ###########
 #    注意事项
 ###########
 1  mysql5.0.13之后支持在存储过程中调用prepare
 2, prepare stmt from 'select * from ?';  (错)
     mysql5.0.24,prepare尚不支持 表名做变量!
     解决方案:用 contat()函数,组合字符串
 3. execute stmt [using @var,@var2]
      必须是@var形式的变量,传入的参数变量,declare变量不行
 4.  deallocate prepare stmt; 显式的释放prepare,如果不释放,mysql会释放,!


下面看下一个简单的例子,代码如下:

 DELIMITER //
 CREATE PROCEDURE `p2` ()
 LANGUAGE SQL
 DETERMINISTIC
 SQL SECURITY DEFINER
 COMMENT 'A procedure'
 BEGIN
     SELECT 'Hello World !';
 END//

下面讲解下存储过程的组成部分:

1)首先在定义好终结符后,使用CREATE PROCEDURE+存储过程名的方法创建存储过程,LANGUAGE选项指定了使用的语言,这里默认是使用SQL。

2)DETERMINISTIC关键词的作用是,当确定每次的存储过程的输入和输出都是相同的内容时,可以使用该关键词,否则默认为NOT DETERMINISTIC。

3) SQL SECURITY关键词,是表示调用时检查用户的权限。当值为INVOKER时,表示是用户调用该存储过程时检查,默认为DEFINER,即创建存储过程时检查。

4) COMMENT部分是存储过程的注释说明部分。

5)在BEGIN END部分中,是存储过程的主体部分。


3 调用存储过程的方法

调用存储过程的方法很简单,只需要使用call命令即可,后面跟要调用存储过程的名称及输入的变量列表,比如:

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , 'string parameter' , @parameter_var);


4 修改和删除存储过程

可以用ALTER的语法去修改存储过程的主要特征和参数,要修改其存储过程的主体部分的话,必须要先删除然后再重建。比如下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA,并指明调用者可以执行。代码执行如下:

ALTER PROCEDURE num_from_employee
MODIFIES SQL DATA SQL SECURITY INVOKER ;  而删除存储过程的语法为使用DROP关键词即可。如下

DROP PROCEDURE IF EXISTS p2;


5 存储过程的参数

下面来学习下存储过程中的参数,先看下存储过程中的参数形式,如下:

CREATE PROCEDURE proc1 () 这个存储过程中是空的参数列表

CREATE PROCEDURE proc1 (IN varname DATA-TYPE) 这个存储过程中有一个输出参数,名称为varname,后面是跟数据类型DATA-TYPE,IN参数是默认的,因此可以省略不写

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) 这个存储过程中varname为输出参数

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) 这个存储过程中,varname既是输入参数也是输出参数

下面具体看个例子,首先是IN输入参数的例子,如下:

DELIMITER //
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
SELECT var1 + 2 AS result;
END//  输出OUT参数例子如下:

DELIMITER //
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END //  IN-OUT的例子:

DELIMITER //
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
SET var1 = var1 * 2;
END //


6) 如何定义变量

下面讲解下MYSQL 5存储过程中,如何定义变量。必须显式地在存储过程的一开始声明变量,并指出它们的数据类型,一但声明了变量后,就可以在存储过程中使用,定义变量的语法如下:

DECLARE varname DATA-TYPE DEFAULT defaultvalue

举例说明:

DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;  一旦定义好变量,就可以在存储过程中对其进行赋初值,并进行各类相关的操作,比如:

DELIMITER //
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;
INSERT INTO table1 VALUES (a);
SET str = 'I am a string';
SELECT CONCAT(str,paramstr), today FROM table2 WHERE b>=5;
END //


7 MYSQL存储过程的语法结构

MYSQL存储过程中支持IF,CASE,ITERATE,LEAVE LOOP,WHILE和REPEAT等语法结构和语句,在本文中,着重介绍IF,CASE和WHILE语法,因为它们使用的最为广泛。


IF 语句

if语句使用的是if…then end if的语法结构,例子如下:

DELIMITER //
CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
IF variable1 = 0 THEN
SELECT variable1;
END IF;
IF param1 = 0 THEN
SELECT 'Parameter value = 0';
ELSE
SELECT 'Parameter value <= 0';
END IF;
END //


CASE语句

当有很多IF语句时,就应该考虑使用CASE语句了,它是多分支选择语句,有两种写法:

第一种写法:

DELIMITER //
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
CASE variable1
WHEN 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;
END //
另外一种写法:

DELIMITER //
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
CASE
WHEN variable1 = 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN variable1 = 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;
END //


WHILE语句

WHILE语句跟普通编程语言中的while语句差不多,例子如下:

DELIMITER //
CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
DECLARE variable1, variable2 INT;
SET variable1 = 0;
WHILE variable1
INSERT INTO table1 VALUES (param1);
SELECT COUNT(*) INTO variable2 FROM table1;
SET variable1 = variable1 + 1;
END WHILE;
END //


8 MYSQL存储过程中的游标

MYSQL中的游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。MYSQL中的游标的语法如下:

DECLARE cursor-name CURSOR FOR SELECT ...; /* 声明一个游标,名称为cursor-name,并用CURSOR FOR SELECT*/
DECLARE CONTINUE HANDLER FOR NOT FOUND /*指定当遍历完结果集后,游标如何继续处理*/
OPEN cursor-name; /*打开游标 */
FETCH cursor-name INTO variable [, variable]; /* 将变量赋值给游标*/
CLOSE cursor-name; /*使用后关闭游标*/  一个具体的例子如下:

DELIMITER //
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
DECLARE a, b, c INT;
DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur1;
SET b = 0;
SET c = 0;
WHILE b = 0 DO
FETCH cur1 INTO a;
IF b = 0 THEN
SET c = c + a;
END IF;
END WHILE;
CLOSE cur1;
SET param1 = c;
END //

  其中,DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;

表示将从table1表中选取col1列的内容放到游标curl中,即每次游标遍历的结果都放在curl中,要注意游标只能向前遍历,而不能向后,并且注意,游标不能更新,最后关闭游标。


<?php
//PHP操作MySQL存储过程示例

//连接mysql数据库
@$link = mysql_connect('localhost', 'root', 'root') or die("Connect Error:".mysql_error());

//选择目标库
mysql_select_db("feiesoft");

//展示目标库中数据表
@$res = mysql_list_tables("flyerdb");
for ($i=0; $i<mysql_num_rows($res); $i++){
    echo mysql_tablename($res, $i)."<br/>";
}

//创建存储结构
mysql_query("DROP PROCEDURE IF EXISTS `my_test1`;", $link);
$create_pro_sql = "CREATE PROCEDURE `my_test1`(a INT, b INT)
                    BEGIN
                        DECLARE c INT;
                        IF a IS NULL THEN SET a = 0;
                        END IF;
                        IF b IS NULL THEN SET b = 0;
                        END IF;
                        SET c = a + b;
                        SELECT c AS SUM;
                    END;";

mysql_query($create_pro_sql, $link) or die("Query Invalid:".mysql_error());

//执行存储过程方式一
//$run_pro_sql = "CALL my_test1(1,2);";

//执行存储过程方式二
mysql_query("SET @a = 1", $link);
mysql_query("SET @b = 2", $link);
$run_pro_sql = "CALL my_test1(@a, @b);";

//执行存储过程
$result = mysql_query($run_pro_sql, $link) or die("Query Invalid:".mysql_error());;

//获取返回值
$row = mysql_fetch_row($result);
echo $row[0];
//3
//关闭连接
mysql_close($link);

?>

相关文章

使用PHP的ODBC函数库操作ACCESS数据库PHP下载文件代码
读书郎学生平板电脑G50,G12,G11详细介绍2013年平板电脑排行榜
如何删除Word页眉横线MySQL数据库定义语句:ALTER DATABASE语法
MySQL创建数据库:CREATE DATABASE语法爱学宝平板学习机A8,A33,A36,A511,AX730,AX750详细介绍
PHP:ODBC处理EXCEL电子表格数据库实例2013年平板电脑品牌排行榜
用汇编语言修改IE浏览器首页PHP+SQLite数据库操作教程与实例
MySQL5存储过程视频教程,PHP操作MySQL存储过程示例HTML5本地存储检测、保存、读取
Javascript读取与设置cookie代码电脑高手平时都怎么操作计算机的?
我要读书——农村儿童的心声!apache2.4.6 httpd.exe命令
ROWIDs and the INTEGER PRIMARY KEYSQLite的数据类型
版权所有 © 中山市飞娥软件工作室 证书:粤ICP备09170368号