21,887
社区成员
发帖
与我相关
我的任务
分享
$create =<<< SQL
CREATE PROCEDURE testit(out emp_id int)
begin
set emp_id=199;
select emp_id as emps;
end;
SQL;
$drop = 'DROP PROCEDURE IF EXISTS testit';
mysql_connect('localhost', 'root', '');
mysql_select_db('test');
mysql_query($drop);
mysql_query($create);
$rs = mysql_query('call testit(@b)') or die(mysql_error());
print_r(mysql_fetch_assoc($rs));
$rs = mysql_query('select @b') or die(mysql_error());
print_r(mysql_fetch_assoc($rs));
Array
(
[emps] => 199
)
Commands out of sync; you can't run this command now
$dsn = 'mysql:host=localhost;dbname=test';
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => "set names gbk",
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
try {
$dbh = new PDO($dsn, 'root', '', $options);
$dbh->query($drop);
$dbh->query($create);
$stmt = $dbh->prepare('call testit(@a)');//, array(PDO::ATTR_CURSOR, PDO::CURSOR_FWDONLY));
$pp = 0;
$stmt->bindParam(1, $pp, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 12);
$stmt->execute();
do {
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($rows) {
print_r($rows);
}
} while ($stmt->nextRowset());
print_r($dbh->query('select @a')->fetchALL());
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . PHP_EOL;
}
print_r($dbh->query('select @a')->fetchALL());
Array
(
[0] => Array
(
[emps] => 199
)
)
Error!: SQLSTATE[HY000]: General error
Array
(
[0] => Array
(
[@a] => 199
)
)
$db = new mysqli('localhost', 'root', '', 'test');
$db->query($drop);
$db->query($create);
$rs = $db->query('call testit(@a)');
print_r($rs->fetch_assoc());
$db->next_result();
$rs = $db->query('select @a') or die($db->error);
print_r($rs->fetch_assoc());
Array
(
[emps] => 199
)
Array
(
[@a] => 199
)
关于存储过程的建立和 out 参数的使用,MySQL 手册中是这样举例的一个使用OUT参数的简单的存储程序的例子。例子为,在程序被定义的时候,用mysql客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql_query("call testit(@a)");
$rs = mysql_query("select @a");
print_r(mysql_fetch_assoc($rs));
<?php
$emp_id = 199;
$result = $dbh->query("call testit($emp_id)");
print_r($result);
?>