mysql存储过程学习笔记--php应用_l8v4g4s2e0的空间_
点烟看寂寞燃烧
孤单了,点支香烟,静静的看着它在指尖燃烧殆尽. 烟之点燃,是为了燃烧一切的寂寞. 连接数据库
错误检查
1)
query($sql) <> TRUE) {
printf("Statement failed %d: (%s) %s\n" ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);
}
?>
2)
query($sql) or printf("Statement failed %d:
(%s) %s\n" ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);
?>
3)
query($sql);
if ($mysqli->errno <> 0 ) {
printf("Statement failed %d: (%s) %s\n" ,$mysqli->errno,$mysqli->sqlstate,$mysqli->error);
}
?>
简单无返回查询
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
95000 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->surname,$row->salary);
}
?>
使用fetch_row返回结果集
95000 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]);
}
?>
事务管理
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语句
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语句中返回结果集
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结果集
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);
}
?>
调用无结果集的存储过程
query($sql);
if ($mysqli->errno) {
die("Execution failed: ".$mysqli->errno.": ".$mysqli->error);
}
else {
printf("Stored procedure execution succeeded\n");
}
?>
返回单个结果集的存储过程
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);
}
?>
有输进参数和返回结果集的存储过程
prepare($sql);
if ($mysqli->errno) {die($mysqli->errno.":: ".$mysqli->error);}
$stmt->bind_param("i", $in_sales_rep_id);
$in_sales_rep_id = 1;
$stmt->execute( );
if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}
$stmt->bind_result($customer_id,$customer_name);
while ($stmt->fetch( )) {
printf("%d %s \n", $customer_id,$customer_name);
}
?>
输出参数的处理
prepare($sql);
if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}
$stmt->execute( );
if ($mysqli->errno) {die($mysqli->errno.": ".$mysqli->error);}
$stmt->close( );
$results = $mysqli->query("SELECT @customer_count AS
customer_count");
$row = $results->fetch_object( );
printf("Customer count=%d\n",$row->cus百杰tome爱看小说r_count);
?> 多结果集处理
multi_query($query)) {
$result = $mysqli->store_result( );
while ($row = $result->fetch_object( )) {
printf("%d %s %s\n",$row->employee_id,$row->surname,$row->firstname);
}
$mysqli->next_result( );
$result = $mysqli->store_result( );
while ($row = $resu免备案空间lt->fetch_object( )) {
printf("%d %s \n",$row->customer_id,$row->customer_name);
}
}
?>
不确定结果集数的处理
multi_query($query)) {
do {
if ($result = $mysqli->store_result( )) {
while ($finfo = $result->fetch_field( )) {
printf("%s\t", $finfo->name);
}
printf("\n");
while ($row = $result->fetch_row( )) {
for ($i=0;$i<$result->field_count;$i++) {
printf("%s\t", $row[$i]);
}
printf("\n");
}
$result->close( );
}
} while ($mysqli->next_result());
}
?>
PDO 连接数据库
word
word文档格式规范word作业纸小票打印word模板word简历模板免费word简历
= 'secret';
try {
$dbh = new PDO($dsn, $user, $password);
}
catch (PDOException $e) {
die('Connection failed: '.$e->getMessage( ));
}
print "Connected\n";
?>
简单查询
exec($sql);
?>
$rows=$dbh->exec("INSERT INTO my_numbers VALUES
(1), (2), (3)");
printf("%d rows inserted\n",$rows);
?>
错误处理
exec($sql);
if ($dbh->errorCode( )<>'00000') {
$error_array=$dbh->errorInfo( );
printf("SQLSTATE : %s\n",$error_array[0]);
printf("MySQL error code : %s\n",$error_array[1]);
printf("Message : %s\n",$error_array[2]);
}
?>
exec($sql);
if ($dbh->errorCode( )<>'00000') {
die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");
}
?>
事务管理
beginTransaction( );
$dbh->exec("UPDATE account_balance SET balance=balance-
$tfer_amount WHERE account_id=$from_account");
if ($dbh->errorCode( )<>'00000') {
printf("transaction aborted: %s\n",implode(': ',$dbh->errorInfo( )));
$dbh->rollback( );
}
else {
$dbh->exec("UPDATE account_balance SET
balance=balance+$tfer_amount WHERE account_id=$to_account");
if ($dbh->errorCode( )<>'00000') {
printf("transaction aborted: %s\n",implode(': ',$dbh->errorInfo( )));
$dbh->rollback( );
}
else {
printf("transaction succeeded\n");
$dbh->commit( );
}
}
?>
结果集处理
query($sql) as $row) {
printf("%d
\t %s\n",$row['department_id'],$row['department_name']);
}
?>
query($sql) as $row) {
printf("%d \t %s\n",$row[0],$row[1]);
}
?>
prepare语句
prepare($sql);
$sth->execute() or die (implode(':',$sth->errorInfo( )));
?>
从prepare语句中返回结果集
prepare($sql) or die (implode(':',$sth->errorInfo( )));
$sth->execute() or die (implode(':',$sth->errorInfo( )));
while($row=$sth->fetch( )) {
printf("%d \t %s
\n",$row['department_id'],$row['department_name']);
}
?>
为prepare绑定数据
prepare($sql);
if ($dbh->errorCode( )<>'00000') {7711站长团购
die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");
}
$sth->bindParam(':sales_rep_id', $sales_rep_id, PDO::PARAM_INT);
$sth->bindParam(':surname', $surname, PDO::PARAM_STR, 30);
$sales_rep_id=41;
$surname = 'SMITH';
$sth->execute( );
if ($dbh->errorCode( )<>'00000') {
die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");
}
while($row=$sth->fetch( )) {
printf("%d %s \n",$row['customer_id'],$row['customer_name']);
}
?>
查询 metadata
prepare("SELECT employee_id,surname,date_of_birth
FROM employees where employee_id=1");
$sth->execute() or die (implode(':',$sth->errorInfo( )));
$cols=$sth->columnCount( );
for ($i=0; $i<$cols ;$i++) {
$metadata=$sth->getColumnMeta($i);
printf("\nDetails for column %d\n",$i+1);
printf(" Name: %s\n",$metadata["name"]);
printf(" Datatype: %s\n",$metadata["native_type"]);
printf(" Length: %d\n",$metadata["len"]);
printf(" Precision: %d\n",$metadata["precision"]);
}
?>
执行简单存储过程
exec($sql);
if ($dbh->errorCode( )<>'00000') {
die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");
}
?>
单个结果集的存储过程
query($sql) as $row) {
printf("%d \t %s\n",$row[0],$row[1]);
}
?>
prepare($sql) or die (implode(':',$sth->errorInfo( )));
$sth->execute() or die (implode(':',$sth->errorInfo( )));
while($row=$sth->fetch( )) {
printf("%s \t %s \n",$row['department_name'],$row['location']);
}
?>
绑定输进参数
prepare($sql);
if ($dbh->errorCode( )<>'00000') {
die("Error: ".implode(': ',$dbh->errorInfo( ))."\n");
}
$sth->bindParam(':sales_rep_id', $sales_rep_id, PDO::PARAM_INT);
$sth->bindParam(':surname', $surname, PDO::PARAM_STR, 30);
$sales_rep_id=41;
$surname = 'SMITH';
$sth->execute( );
?>
多结果集处理
prepare("call
stored_proc_with_2_results( $employee_id )");
$sth->execute() or die (implode(':',$sth->errorInfo( )));
while ($row1=$sth->fetch( )) {
printf("%d %s %s\n",$row1['employee_id'],$row1['surname'],$row1['firs
tname']);
}
$sth->nextRowset( );
while ($row2=$sth->fetch( )) {
printf("%d %s \n",$row2['customer_id'],$row2['customer_name']);
}
?>
不确定结果集数目的处理
CREATE PROCEDURE sp_employee_report(in_emp_id decimal(8,0)) READS SQL DATA BEGIN DECLARE customer_count INT; SELECT
surname,firstname,date_of_birth FROM employees WHERE employee_id=in_emp_id; SELECT department_id,department_name FROM departments WHERE department_id=(select department_id FROM employees WHERE employee_id=in_emp_id); SELECT count(*) INTO customer_count FROM customers WHERE sales_rep_id=in_emp_id; IF customer_count=0 THEN SELECT 'Employee is not a current sales rep'; ELSE SELECT customer_name,customer_status FROM customers HERE
sales_rep_id=in_emp_id; SELECT customer_name,sum(sale_value) FROM sales JOIN customers USING (customer_id) WHERE
customers.sales_rep_id=in_emp_id GROUP BY customer_name; END IF;
prepare($sql_text);
$sth->execute() or die(implode(':', $sth->errorInfo( )));
do {
if ($sth->columnCount( ) > 0) { //是结果集
//输出字段名
for ($i = 0; $i < $sth->columnCount( ); $i ++) {
$meta = $sth->getColumnMeta($i);
printf("%s\t", $meta["name"]);
}
printf("\n");
while ($row = $sth->fetch( )) {
for ($i = 0; $i < $sth->columnCount( ); $i ++) {
printf("%s\t", $row[$i]);
}
printf("\n");
}
printf("-------------------\n");
}
} while ($sth->nextRowset( ));
}
?> ( )));
?>
不使用bindParam获得输出参数
prepare($sql);
$sth->execute() or die (implode(':',$sth->errorInfo( )));
$sql="SELECT @customer_count";
foreach ($dbh->query($sql) as $row) {
printf("Customer count=%d\n",$row[0]);
}
?>
中国最大的站长团购网站www.7711tuan.com
中国最大的小说网站www.2xiaoshuo.net
我在财富杂志上看到一款旺道SEO网站优化软件,上面介绍了一些关于网站优化
浙公网安备 33021202002483