PHP Mysql5.6 存储过程

来源：互联网发布：广东省软件行业协会编辑：程序博客网时间：2024/04/30 01:15

一. 有输入参数，输出参数并返回结果集的存储过程

存储过程如下：

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_test_page`($pageIndex INT,  #查询页码$pageSize INT,   #每页记录数OUT $totalRows INT,  #总记录数OUT $totalPages INT  #总页数)BEGIN#计算起始行号SET @startRow = $pageSize * ($pageIndex - 1);SET @pageSize = $pageSize;#合并字符串SET @sqlstr = CONCAT('select sql_calc_found_rows id from test where 1=1 order by id asc limit ',@startRow,',',@pageSize);PREPARE sqlstr FROM @sqlstr;#定义预处理语句 EXECUTE sqlstr;#执行预处理语句 DEALLOCATE PREPARE sqlstr;#删除定义 #通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数SET $totalRows = FOUND_ROWS();#计算总页数IF $totalRows <= $pageSize THENSET $totalPages = 1;ELSEIF $totalRows % $pageSize > 0 THENSET $totalPages = $totalRows / $pageSize + 1;ELSESET $totalPages = $totalRows / $pageSize;END IF;END

1. 面向过程

$conn = mysqli_connect('127.0.0.1','root','begin110','ifel');$stmt = mysqli_query($conn, "call sp_test_page(1, 5, @totalRows, @totalPages)");$rss = array();$rs = array();while($row = mysqli_fetch_array($stmt, MYSQL_ASSOC)){    array_push($rs, $row);}array_push($rss, $rs);mysqli_free_result($stmt);mysqli_next_result($conn);$stmt = mysqli_query($conn, "select @totalRows as totalRows, @totalPages as totalPages;");$rs = array();while($row = mysqli_fetch_array($stmt, MYSQL_ASSOC)){    array_push($rs, $row);}array_push($rss, $rs);mysqli_close($conn);var_dump($rss);

2.面向对象

$mysqli = new mysqli('127.0.0.1','root','begin110','ifel');$rss = array();$results = $mysqli->query("call sp_test_page(1, 5, @totalRows, @totalPages)");$rs = array();while($row=$results->fetch_object()){    array_push($rs, $row);}array_push($rss, $rs);$results->free_result();$mysqli->next_result();$res = $mysqli->query("select @totalRows as totalRows, @totalPages as totalPages;");$rs = array();while($row=$res->fetch_object()){    array_push($rs, $row);}array_push($rss, $rs);$mysqli->close();var_dump($rss);

二.多结果集

存储过程如下：

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_get_user_list`(in $uid int)BEGINif $uid>0    thenselect * from tb_user where uid=$uid;        select * from tb_user where uid=$uid;        select * from tb_user where uid=$uid;    elseselect * from test;        select * from test;        select * from test;    end if;END

1.面向过程

$conn = mysqli_connect('127.0.0.1','root','begin110','ifel');$i = 1;$sp = "call sp_get_user_list($i)";$rss = array();if(mysqli_multi_query($conn, $sp)){    do    {        $rs = array();        if($result = mysqli_store_result($conn))        {            while($row = mysqli_fetch_assoc($result))            {                $rs[] = $row;            }            mysqli_free_result($result);            $rss[] = $rs;        }    }    while(mysqli_more_results($conn) && mysqli_next_result($conn));}echo "<br>";var_dump($rss[0]);echo "<br>";var_dump($rss[1]);echo "<br>";var_dump($rss[2]);

2.面向对象

$mysqli = new mysqli('127.0.0.1','root','begin110','ifel');$i = 1;$sp = "call sp_get_user_list($i)";$rss = array();if($mysqli->multi_query($sp)){    do    {        $rs = array();        if($result = $mysqli->use_result())        {            while($row = $result->fetch_array(MYSQL_ASSOC))            {                $rs[] = $row;            }            $result->close();            $rss[] = $rs;        }    }    while($mysqli->more_results() && $mysqli->next_result());    $mysqli->close();}echo "<br>";var_dump($rss[0]);echo "<br>";var_dump($rss[1]);echo "<br>";var_dump($rss[2]);

三.输出参数

存储过程：

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_test_out`(OUT msg VARCHAR(50))BEGIN SELECT "Hi!" INTO msg; END

1.面向对象

$mysqli = new mysqli('127.0.0.1','root','begin110','ifel');$mysqli->query("call sp_test_out(@msg)");$res = $mysqli->query("SELECT @msg as msg");$row = $res->fetch_assoc();echo $row['msg'];

四.输入参数，并返回结果集

存储过程：

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_test_in`(IN id_val INT)BEGIN declare mid int;    set mid = (select max(id)+1 from test);INSERT INTO test(id) VALUES(mid);     select * from test;END

1.面向过程

$conn = mysqli_connect('127.0.0.1','root','begin110','ifel');$id = 8;$sp = "call sp_test_in($id)";$stmt = mysqli_query($conn, $sp);$rs = array();while($row = mysqli_fetch_array($stmt, MYSQL_ASSOC)){    array_push($rs, $row);}mysqli_close($conn);var_dump($rs);

0 0