新手知识巩固【学PHP应用】如何使用mysql存储过程呢?

[0rh.cn 零入行官方网站]

人在哪,心就在哪?PHP应用如何使用存储过程呢?

茫茫学习路我还不会存储过程是多么痛的领悟,

零入行何尝不是学习的好帮手分享学习分享爱吧!

<?php

$mysqli = new mysqli(“localhost”, “root”, “secret”, “test”);

if (mysqli_connect_errno( )) {

printf(“Connect failed: %s\n”, mysqli_connect_error( ));

exit ( );

} else {

printf(“Connect succeeded\n”);

}

?> 错误检查

1)

<?php

if ($mysqli->query($sql) <> TRUE) {

printf(“Statement failed %d: (%s) %s\n” ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

}

?>

2)

<?php

$mysqli->query($sql) or printf(“Statement failed %d: (%s) %s\n” ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

?>

3)

<?php

$mysqli->query($sql);

if ($mysqli->errno <> 0 ) {

printf(“Statement failed %d: (%s) %s\n” ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

}

?>

简单无返回查询[0rh.cn 零入行官方网站]

<?php

$mysqli->query(“CREATE TABLE guy_1 (guys_integers INT)”);

if ($mysqli->errno <> 0 ) {

printf(“Statement failed %d: (%s) %s\n” ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);

}

?>

返回结果集fetch_object

<?php

$sql=”SELECT employee_id, surname, salary FROM employees WHERE salary> AND department_id=1 AND status=’G'”;

$results=$mysqli->query($sql);

if ($mysqli->errno) { die ($mysqli->errno.” “.$mysqli->error); }

while($row=$results->fetch_object( )) {

printf(“%d\t%s\t%d\n”,$row->employee_id,$row->surnam e,$row->salary);

}

?>

使用fetch_row返回结果集[0rh.cn 零入行官方网站]

<?php

$sql=”SELECT employee_id, surname, salary FROM employees WHERE salary> AND department_id=1 AND status=’G'”;

$results=$mysqli->query($sql);

if ($mysqli->errno) { die ($mysqli->errno.” “.$mysqli->error); }

while($row=$results->fetch_row( )) {

printf(“%d\t%s\t%d\n”,$row[0],$row[1],$row[2]);

}

?>

事务管理

<?php

$mysqli->autocommit(FALSE);

$mysqli->query(“UPDATE account_balance SET balance=balance-$tfer_amount WHERE account_id=$from_account”);

if ($mysqli->errno) {

printf(“transaction aborted: %s\n”,$mysqli->error);

$mysqli->rollback( );

}

else {

$mysqli->query(“UPDATE account_balance SET balance=balance+$tfer_amount WHERE account_id=$to_account”);

if ($mysqli->errno) {

printf(“transaction aborted: %s\n”,$mysqli->error);

$mysqli->rollback( );

}

else {

printf(“transaction succeeded\n”);

$mysqli->commit( );

}

}

?>

prepare语句[0rh.cn 零入行官方网站]

<?php

$insert_stmt=$mysqli->prepare(“INSERT INTO x VALUES(?,?)”) or die($mysqli->error);

$insert_stmt->bind_param(“is”, $my_number,$my_string); #i=integer

for ($my_number = 1; $my_number <= 10; $my_number++) {

$my_string=”row “.$my_number;

$insert_stmt->execute( ) or die ($insert_stmt->error);

}

$insert_stmt->close( );

?>

从prepared语句中返回结果集[0rh.cn 零入行官方网站]

<?php

$sql=”SELECT employee_id,surname,firstname FROM employees WHERE department_id=? AND status=? IMIT 5″;

$stmt = $mysqli->prepare($sql);

if ($mysqli->errno<>0) {die($mysqli->errno.”: “.$mysqli->error);}

$stmt->bind_param(“is”,$input_department_id,$input_ status) or die($stmt-error);

$stmt->bind_result( $employee_id,$surname,$firstname) or die($stmt->error);

$input_department_id=1;

$input_status=’G’;

$stmt->execute( );

if ($mysqli->errno<>0) {die($stmt.errno.”: “.$stmt->error) ;}

while ($stmt->fetch( )) {

printf(“%s %s %s\n”, $employee_id,$surname,$firstname);

}

?>

获得 Metadata结果集

<?php

$metadata = $stmt->result_metadata( );

$field_cnt = $metadata->field_count;

while ($colinfo = $metadata->fetch_field( )) {

printf(“Column: %s\n”, $colinfo->name);

printf(“max. Len: %d\n”, $colinfo->max_length);

printf(“Type: %d\n\n”, $colinfo->type);

}

?>

调用无结果集的存储过程[0rh.cn 零入行官方网站]

<?php

$sql = ‘call simple_stored_proc( )’;

$mysqli->query($sql);

if ($mysqli->errno) {

die(“Execution failed: “.$mysqli->errno.”: “.$mysqli->error);

}

else {

printf(“Stored procedure execution succeeded\n”);

}

?>

返回单个结果集的存储过程

<?php

$sql = “call department_list( )”;

$results = $mysqli->query($sql);

if ($mysqli->errno) {

die(“Execution failed: “.$mysqli->errno.”: “.$mysqli->error);

}

while ($row = $results->fetch_object( )) {

printf(“%s\t%s\n”, $row->department_name, $row->location);

}

?>

有输进参数和返回结果集的存储过程

<?php

$sql = “CALL customers_for_rep(?)”;

$stmt = $mysqli->prepare($sql);

if ($mysqli->errno) {die($mysqli->errno.”:: “.$mysqli->error);}

//[0rh.cn 零入行官方网站]

原文链接:,转发请注明来源!

发表评论