PHP operates Mysql stored procedures

MySQL stored procedures are a new feature added starting from MySQL 5.0. The advantages of stored procedures are mainly execution efficiency and SQL code encapsulation. Especially the SQL code encapsulation function, especially when the business logic is complex. Now with MySQL stored procedures, business logic can be encapsulated in stored procedures, which is not only easy to maintain, but also efficient in execution.

<?php 
/**
 * PHP操作Mysql存储过程示例
 * 
 * @author flyer0126
 * @date 2011-12-23
 * 
 */
//配置数据库连接信息
$hostname = &#39;localhost&#39;;
$username = &#39;******&#39;;
$password = &#39;******&#39;;
//连接mysql数据库
@$link = mysql_connect($hostname, $username, $password) or die("Connect Error:".mysql_error());
//选择目标库
mysql_select_db("flyer0126");
//展示目标库中数据表
@$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);
?>