用 PHP 调用 MySQL 存储过程

MySQL 5.0 以上支持存储过程。
PHP 5.0 以上的 mysqli 系列函数可以支持操作 MySQL 的存储过程。
以下是一些简单的存储过程和用 PHP 调用的示例。

一、返回单个数据:

 1:  <?php

 2:    header("Content-Type:text/html;charset=utf-8");

 3:  

 4:    $host = "localhost";

 5:    $user = "root";

 6:    $password = "mypassword";

 7:    $db = "test_store_proc";

 8:    $dblink = mysqli_connect($host, $user, $password, $db) or die("can't connect to mysql");

 9:  

10:    $dblink->query('SET NAMES UTF8');

11:    if ($result = $dblink->query("CALL sp_test0(@num, @x, 123)"))

12:    {

13:      $rs = $dblink->query("select @num");

14:      $row = $rs->fetch_array();

15:      echo $row['@num'], '<br>';

16:  

17:      $rs = $dblink->query("select @x");

18:      $row = $rs->fetch_array();

19:      echo $row['@x'];

20:  

21:      mysqli_free_result($rs);

22:    }

23:    else

24:      echo 'error...';

25:    mysqli_close($dblink);

26:  

27:  /*

28:  -- Procedure "sp_test0" DDL

29:  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test0`(OUT num INT, OUT x VARCHAR(16), IN n INT)

30:  BEGIN

31:    DECLARE nouse int;

32:    DECLARE tmp int;

33:  

34:    SELECT nId INTO nouse FROM open_news WHERE nID=39;

35:    SELECT count(*) INTO tmp FROM open_news;

36:    SET num = tmp;

37:  

38:    SET x = 'XXX';

39:  END;

40:  */

41:  ?>

42:  

二、返回结果集:

 1:  <?php

 2:    header("Content-Type:text/html;charset=utf-8");

 3:  

 4:    $host = "localhost";

 5:    $user = "root";

 6:    $password = "mypassword";

 7:    $db = "test_store_proc";

 8:    $dblink = mysqli_connect($host, $user, $password, $db) or die("can't connect to mysql");

 9:  

10:    $dblink->query('SET NAMES UTF8');

11:    if ($result = $dblink->query("call sp_test1()"))

12:    {

13:      while( $row = $result->fetch_array())

14:      {

15:        echo ($row['nId']. "--" . $row['sTopic2'] . "<br>");

16:      }

17:      mysqli_free_result($result);

18:    }

19:    else

20:      echo 'error...';

21:    mysqli_close($dblink);

22:  

23:  /*

24:  -- Procedure "sp_test1" DDL

25:  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test1`()

26:  BEGIN

27:     SELECT * FROM open_news WHERE nId<40;

28:  END;

29:  */

30:  ?>

31:  

三、返回多个结果集：

 1:  <?php

 2:    header("Content-Type:text/html;charset=utf-8");

 3:  

 4:    $host = "localhost";

 5:    $user = "root";

 6:    $password = "mypassword";

 7:    $db = "test_store_proc";

 8:  

 9:    $dblink = new mysqli($host, $user, $password, $db);

10:    if (mysqli_connect_errno())

11:    {

12:      print('Can not connect to MySQL server');

13:      exit;

14:    }

15:    else

16:      print('?????? MySQL ????????<br>');

17:  

18:    $dblink->query('SET NAMES UTF8');

19:    $rows = array();

20:    if($dblink->real_query("CALL sp_test2()"))

21:    {

22:      do

23:      {

24:        if($result = $dblink->store_result())

25:        {

26:          while ($row = $result->fetch_assoc())

27:          {

28:            array_push($rows, $row);

29:          }

30:          $result->close();

31:        }

32:      }

33:      while($dblink->next_result());

34:    }

35:    else

36:      echo 'error...';

37:  

38:    $dblink->close();

39:  

40:    print_r($rows);

41:  /*

42:  -- Procedure "sp_test2" DDL

43:  CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test2`()

44:  BEGIN

45:    SELECT nId,sTopic2 FROM open_news LIMIT 0, 5;

46:    SELECT count(nId) AS counter FROM open_news;

47:  END;

48:  */

49:  ?>

50:  

作者：张庆(网眼) 2009-3-5
来自“网眼视界”：http://blog.why100000.com
“十万个为什么”电脑学习网：http://www.why100000.com