Stored Procedure Trong Mysql — Stdio

Summary: in this tutorial, you will learn how to call MySQL stored procedures using PHP.. PDO. We will show you how to điện thoại tư vấn stored procedures that return a result phối & stored procedures that accept input/output parameters.

Bạn đang xem: Stored procedure trong mysql — stdio

Calling stored procedures that return a result set

The steps of calling a MySquốc lộ stored procedure that returns a result phối using PHP PDO are similar khổng lồ querying data from MySQL database table using the SELECT statement. Instead of sending a SELECT statement to MySQL database, you send a stored procedure Call statement.

First, create a stored procedure named GetCustomers() in thesample databasefor the demonstration. The GetCustomers() stored procedureretrieves the name & credit limit of customers from the customers table.

The followingGetCustomers() stored procedure illustrates the logic:


DELIMITER $$CREATE PROCEDURE GetCustomers()BEGINSELECT customerName, creditlimitFROM customers; END$$
Code language: Squốc lộ (Structured Query Language) (sql)Second, create a new PHP tệp tin named phpmysqlstoredprocedure1.php with the following code:


Code language: Squốc lộ (Structured Query Language) (sql)We send the statement that calls the GetCustomers() stored procedurelớn MySQL. And we exexinh tươi the statement to lớn get a result mix.

Third, thử nghiệm the script in the web browser khổng lồ see how it works.

Xem thêm: Tra Từ Điển Cambridge Dictionary, Từ Điển Cambridge Online

*

You can download the script via the following link:

Download PHP MySQL Stored Procedure Source Code

Calling stored procedures with an OUT parameter

It is a little bit tricky to lớn Điện thoại tư vấn a stored procedure with the OUT parameter. We will use the GetCustomerLevel() stored procedure that accepts a customer number as an đầu vào parameter & returns the customer màn chơi based on credit limit.

Cheông chồng the MySQL IF statement tutorial for detailed information on the GetCustomerLevel() stored procedure.


DELIMITER $$CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10))BEGIN DECLARE creditlyên ổn double; SELECT creditlimit INTO creditlyên FROM customers WHERE customerNumber = p_customerNumber; IF creditlyên > 50000 THEN SET p_customerLevel = "PLATINUM"; ELSEIF (creditllặng = 10000) THEN SET p_customerLevel = "GOLD"; ELSEIF creditllặng SET p_customerLevel = "SILVER"; END IF;END$$
Code language: Squốc lộ (Structured Query Language) (sql)In MySquốc lộ, we can gọi the GetCustomerLevel() stored procedureas follows:


CALL GetCustomerLevel(103,
level);SELECT
cấp độ AS level;
Code language: Squốc lộ (Structured Query Language) (sql)In PHPhường., we have sầu to emulate those statements:

First, we need lớn exedễ thương the GetCustomerLevel() stored procedure.Second, lớn get the customer level, we need lớn query it from the variable
màn chơi . It is important that we must Gọi the method closeCursor() of the PDOStatement object in order lớn execute the next SQL statement.

Let’s take a look at how the lô ghích is implemented in the following PHP script:


return string */function getCustomerLevel(int $customerNumber) try $pvì chưng = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); // calling stored procedure commvà $sql = "CALL GetCustomerLevel(:id,
level)"; // prepare for execution of the stored procedure $stmt = $pdo->prepare($sql); // pass value to the command $stmt->bindParam(":id", $customerNumber, PDO::PARAM_INT); // exexinh đẹp the stored procedure $stmt->execute(); $stmt->closeCursor(); // exeđáng yêu the second query to lớn get customer"s level $row = $pdo->query("SELECT
cấp độ AS level")->fetch(PDO::FETCH_ASSOC); if ($row) return $row !== false ? $row<"level"> : null; catch (PDOException $e) die("Error occurred:" . $e->getMessage()); return null;$customerNo = 103;echo sprintf("Customer #%d is %s", $customerNo, getCustomerLevel($customerNo));
Code language: PHP. (php)If you demo the script in the web browser, you will see the following screenshot:

*

You can tải về the script via the following link:

Download PHP MySquốc lộ Stored Procedure with the OUT Parameter Source Code

In this tutorial, you have learned how khổng lồ Gọi MySQL stored procedures using PHPhường PDO.