Detailed introduction to stored procedures in PDO

Detailed introduction to stored procedures in PDO

The stored procedures in PDO allow data to be manipulated closer to the data, thereby reducing bandwidth usage. They make data independent of script logic, allowing multiple systems using different languages ​​to access data in the same way, saving valuable time spent on coding and debugging, while he uses predefined scenarios to perform operations and increase query speed. And it can prevent direct interaction with data, thus protecting the data!

In the previous article "Detailed Introduction to Transaction Processing in PDO", we introduced the transaction processing of PDO, so in this article we will introduce to you the stored procedures in PDO!

First, let’s explain how to call a stored procedure in PDO. Here we first create a stored procedure with the following SQL statement:

drop procedure if exists pro_reg;
delimiter//
create procedure pro_reg(in nc varchar(80),in pwd varchar(80), in email varchar(80),in address varchar(50))
begin
insert into tb_reg(name,pwd,email,address)values(nc,pwd,email,address);
end;
//

The “drop” statement deletes the existing stored procedure pro_reg in the MySQL server. .

The function of "delimiter//" is to change the statement end character to "//".

"in nc varchar(80)....in address varchar(50)" indicates the parameters to be passed into the stored procedure.

"begin...end" represents the statement block in the stored procedure, and its function is similar to "{.......}" in the PHP language.

After the stored procedure is successfully created, the stored procedure is called below to add user registration information. The specific steps are as follows.

Create the index.php file. First, create a form and submit user information to this page through the POST method. Then, write a PHP script on this page, connect to the MySQL database through PDO, and set the database encoding format to UTF-8 to obtain the user registration information submitted in the form. Then, call the stored procedure pro_reg through the call statement to add the user registration information to the data table. Finally, error information is returned through the try...catch... statement block. The key code is as follows:

<form name="form1" action="4.php" method="post">
    用户昵称：<input type="text" name="nc"><br>
    密   码：<input type="password" name="password"><br>
    邮   箱：<input type="text" name="email"><br>
    <input type="submit" name="Submit" value="注册">
    <input type="submit" name="Submit" value="重写">
</form>
<?php
if($_POST["Submit"]){
header("Content-Type:text/html; charset=utf-8");    //设置页面的编码格式
$dbms = "mysql";                                  // 数据库的类型
$dbName ="php_cn";                                //使用的数据库名称
$user = "root";                                   //使用的数据库用户名
$pwd = "root";                                    //使用的数据库密码
$host = "localhost";                              //使用的主机名称
$dsn  = "$dbms:host=$host;dbname=$dbName";
try {
    $pdo = new PDO($dsn, $user, $pwd);//初始化一个PDO对象，就是创建了数据库连接对象$pdo
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $nc = $_POST[&#39;nc&#39;];
    $password = md5($_POST[&#39;password&#39;]);
    $email = $_POST[&#39;email&#39;];
    $query = "call pro_reg(&#39;$nc&#39;,&#39;$password&#39;,&#39;$email&#39;)";
    $res = $pdo->prepare($query);//准备查询语句
    if ($res->execute()) {
        echo "添加数据库成功";
    } else {
        echo "添加数据库失败";
    }
}catch (PDOException $e){
    echo "PDO Exception Caught";
    echo &#39;Error with the database:<br>&#39;;
    echo &#39;SQL Query;&#39;.$query;
    echo &#39;<pre class="brush:php;toolbar:false">&#39;;
    echo "Error:".$e -> getMessage()."<br>";
    echo "Code:".$e ->getCode()."<br>";
    echo "File:".$e ->getFile()."<br>";
    echo "Line:".$e ->getLine()."<br>";
    echo "Trace:".$e ->getTraceAsString()."<br>";
    echo  "

The result is as follows:

The main thing is to create a stored procedure, and then call this stored procedure. Friends can do it locally Try it yourself~! ~

Summary:

The PDO database abstraction layer is all over here. We focus on the database abstraction layer-PDO, from the overview, characteristics and The installation begins with an introduction to PDO example applications, including: how to connect to different databases, how to execute SQL statements, how to obtain result sets and perform error handling, and then to advanced applications of PDO: transactions and stored procedures, and each part is There are corresponding examples. Through studying this chapter, I believe that friends can master the application of PDO technology. Then see you in our next topic!

The above is the detailed content of Detailed introduction to stored procedures in PDO. For more information, please follow other related articles on the PHP Chinese website!