裸奔的鸡蛋 发表于 2011-10-10 08:25:27

mysql存储过程学习笔记--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);

  }

  ?>

  简单无返回查询

  <?php

  $mysqli->query("CREATE TABLE guy_1 (guys_integersINT)");

  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 WHEREsalary> 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返回结果集

  <?php

  $sql="SELECT employee_id, surname, salary FROM employees WHEREsalary> 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,$row,$row);

  }

  ?>

  事务管理

  <?php

  $mysqli->autocommit(FALSE);

  $mysqli->query("UPDATE account_balance SETbalance=balance-$tfer_amount WHEREaccount_id=$from_account");

  if ($mysqli->errno) {

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

  $mysqli->rollback( );

  }

  else {

  $mysqli->query("UPDATE account_balance SETbalance=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语句

  <?php

  $insert_stmt=$mysqli->prepare("INSERT INTO xVALUES(?,?)") 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语句中返回结果集

  <?php

  $sql="SELECT employee_id,surname,firstname FROM employees WHEREdepartment_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) ordie($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);

  }

  ?>

  调用无结果集的存储过程

  <?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);}

  $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);

  }

  ?>

  输出参数的处理

  <?php

  $sql="CALL sp_rep_customer_count(1,@customer_count)";

  $stmt = $mysqli->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_countAS customer_count");

  $row = $results->fetch_object( );

  printf("Customercount=%d\n",$row->cus百杰tome爱看小说r_count);

  ?> 多结果集处理

  <?php

  $query = "call stored_proc_with_2_results( $employee_id )";

  if ($mysqli->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);

  }

  }

  ?>

  不确定结果集数的处理

  <?php

  $query = "call stored_proc_with_2_results( $employee_id )";

  if ($mysqli->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 连接数据库

  <?php

  $dsn = 'mysql:dbname=prod;host=localhost;port=3305';

  $user = 'root';

  $password = 'secret';

  try {

  $dbh = new PDO($dsn, $user, $password);

  }

  catch (PDOException $e) {

  die('Connection failed: '.$e->getMessage());

  }

  print "Connected\n";

  ?接近开关>

  简单查询

  <?php

  $sql="CREATE TABLE my_numbers (a_number INT)";

  $dbh->exec($sql);

  ?>

  <?php

  <br站长团购 />$rows=$dbh->exec("INSERT INTO my_numbers VALUES (1),(2), (3)");

  printf("%d rows inserted\n",$rows);

  ?>

  错误处理

  <?php

  $sql="CREATE TABLE my_numbers (a_number INT)";

  $dbh->exec($sql);

  if ($dbh->errorCode()<>'00000') {

  $error_array=$dbh->errorInfo( );

  printf("SQLSTATE : %s\n",$error_array);

  printf("MySQL error code : %s\n",$error_array);

  printf("Message : %s\n",$error_array);

  }

  ?>

  <br /免备案空间><?php

  $sql="CREATE TABLE my_numbers (a_number INT)";

  $dbh->exec($sql);

  if ($dbh->errorCode()<>'00000') {

  die("Error: ".implode(': ',$dbh->errorInfo())."\n");

  }

  ?>

  事务管理

  <?php

  $dbh->beginTransaction( );

  $dbh->exec("UPDATE account_balance SETbalance=balance-$tfer_amount WHEREaccount_id=$from_account");

  if ($dbh->errorCode()<>'00000') {

  printf("transaction aborted: %s\n",implode(':',$dbh->errorInfo( )));

  $dbh->rollback( );

  }

  else {

  $dbh->exec("UPDATE account_balance SETbalance=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( );

  }

  }

  ?>

  结果集处理

  <?php

  $sql = 'SELECT department_id,department_name FROMdepartments';

  foreach ($dbh->query($sql) as $row) {

  printf("%d \t %s\n",$row['department_id'],$row['department_name']);

  }

  ?>

  <?php

  $sql = 'SELECT department_id,department_name FROMdepartments';

  foreach ($dbh->query($sql) as $row) {

  printf("%d \t %s\n",$row,$row);

  }

  ?>

  prepare语句

  <?php

  $sql = 'INSERT INTO my_numbers VALUES(1),(2),(3)';

  $sth = $dbh->prepare($sql);

  $sth->execute() or die(implode(':',$sth->errorInfo( )));

  ?>

  从prepare语句中返回结果集

  <?php

  $sql='SELECT department_id,department_name FROM departments LIMIT5';

  $sth=$dbh->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绑定数据

  <?php

  $sql='SELECT customer_id,customer_name FROM customers WHEREsales_rep_id=:sales_rep_id AND contact_surname=:surname';

  $sth = $dbh->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

  <?php

  $sth = $dbh->prepare("SELECTemployee_id,surname,date_of_birth FROM employees whereemployee_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"]);

  }

  ?>

  执行简单存储过程

  <?php

  $sql='call simple_stored_proc( )';

  $dbh->exec($sql);

  if ($dbh->errorCode()<>'00000') {

  die("Error: ".implode(': ',$dbh->errorInfo())."\n");

  }

  ?>

  单个结果集的存储过程

  <?php

  $sql = 'call stored_proc_with_1_result( )';

  foreach ($dbh->query($sql) as $row) {

  printf("%d \t %s\n",$row,$row);

  }

  ?>

  <?php

  $sql='call stored_proc_with_1_result( )';

  $sth=$dbh->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']);

  }

  ?>

  绑定输进参数

  <?php

  $sql='CALL customers_for_rep(:sales_rep_id,:surname)';

  $sth = $dbh->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( );

  ?>

  多结果集处理

  <?php

  $sth = $dbh->prepare("callstored_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['firstname']);

  }

  $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)) READSSQL DATA BEGIN DECLARE customer_count INT; SELECTsurname,firstname,date_of_birth FROM employees WHEREemployee_id=in_emp_id; SELECT department_id,department_name FROMdepartments WHERE department_id=(select department_id FROMemployees WHERE employee_id=in_emp_id); SELECT count(*) INTOcustomer_count FROM customers WHERE sales_rep_id=in_emp_id; IFcustomer_count=0 THEN SELECT 'Employee is not a current sales rep';ELSE SELECT customer_name,customer_status FROM customers HEREsales_rep_id=in_emp_id; SELECT customer_name,sum(sale_value) FROMsales JOIN customers USING (customer_id) WHEREcustomers.sales_rep_id=in_emp_id GROUP BY customer_name; ENDIF;

  <?php

  function many_results($dbh, $sql_text) {

  $sth = $dbh->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获得输出参数

  <?php

  $sql="call sp_rep_customer_count(1,@customer_count)";

  $sth = $dbh->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);

  }

  ?>

  

转载自:www.oohni.netwww.cceo5.cn www.uuu9.inwww.admin5.in
mysql存储过程学习笔记--php应用
页: [1]
查看完整版本: mysql存储过程学习笔记--php应用