Creating stored procedures in phpmyadmin. MySQL Stored Procedures Effectively Treat Drowsiness
Remove DEFINER=root@localhost
in local, while importing,
it will be completed.
Can you guys help me regarding stored procedures. When I export the stored procedure from phpmyadmin it is listed as
CREATE DEFINER = `root` @ `localhost` PROCEDURE `c4mo_get_cities_prc` (IN `p_state_code` VARCHAR (3), IN `p_country_code` VARCHAR (3), IN `p_language_code` VARCHAR (3)) NO SQL BEGIN SELECT city_name, city_code FROM `c4mo_cities` WHERE enabled = "Y" AND language_code = p_language_code AND state_code= p_state_code AND country_code= p_country_code ; ENDAnd when I import it from phpmyadmin it gives error like
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "" at line 13This is quite simple if you are using the phpmyadmin interface.
For export :
You will see a regular tab because this tab will only appear if you already have at least one stored procedure.
Just go to the routines tab and you will see your stored procedure (for the db you made).
Check the box below and then export. You just need to copy all the code and save it anywhere on your local machine with your_stored_procedure.sql file.
For import:
Just select the database and import the stored procedure your_stored_procedure.sql file as above as you usually import the .sql file (tables) for your db.
MySQL 5 has many new features, one of the most significant of which is the creation of stored procedures. In this tutorial, I'll talk about what they are and how they can make your life easier.
Introduction
A stored procedure is a way to encapsulate repetitive actions. Stored procedures can declare variables, manipulate data flow, and use other programming techniques.
The reason for their creation is clear and is confirmed by frequent use. On the other hand, if you talk to those who work with them irregularly, the opinions will be divided into two completely opposite flanks. Don't forget this.
Behind
- Sharing logic with other applications. Stored procedures encapsulate functionality; this provides connectivity for data access and management across different applications.
- Isolating users from database tables. This allows you to give access to stored procedures, but not to the table data itself.
- Provides a protection mechanism. In accordance with previous paragraph If you can only access data through stored procedures, no one else can erase your data through an SQL DELETE command.
- Improved execution as a result of reduced network traffic. Using stored procedures, multiple queries can be combined.
Against
- Increased load on the database server due to the fact that most of the work is performed on the server side, and less on the client side.
- You'll have to learn a lot. You will need to learn MySQL expression syntax to write your stored procedures.
- You are duplicating your application logic in two places: server code and code for stored procedures, thereby complicating the process of data manipulation.
- Migration from one DBMS to another (DB2, SQL Server etc.) can lead to problems.
The tool I work with is called MySQL Query Browser, which is pretty standard for interacting with databases. Command tool MySQL strings is another excellent choice. The reason I'm telling you this is because everyone's favorite phpMyAdmin doesn't support running stored procedures.
By the way, I'm using a basic table structure to make it easier for you to understand this topic. I’m talking about stored procedures, and they are complex enough to require delving into the cumbersome table structure.
Step 1: Place a limiter
A delimiter is a character or string of characters that is used to indicate to the MySQL client that you have finished writing the SQL expression. For ages, the semicolon has been the delimiter. However, problems may arise because there may be multiple expressions in a stored procedure, each of which must end with a semicolon. In this tutorial I use the string “//” as a delimiter.
Step 2: How to work with stored procedures
Creating a Stored Procedure
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//
The first part of the code creates a stored procedure. The next one contains optional parameters. Then comes the name and, finally, the body of the procedure itself.
Stored procedure names are case sensitive. You also cannot create multiple procedures with the same name. There cannot be expressions inside a stored procedure that modify the database itself.
4 characteristics of a stored procedure:
- Language: For portability purposes, the default is SQL.
- Deterministic: if the procedure always returns the same result and takes the same input parameters. This is for the replication and registration process. The default value is NOT DETERMINISTIC.
- SQL Security: user rights are checked during the call. INVOKER is the user calling the stored procedure. DEFINER is the “creator” of the procedure. The default value is DEFINER.
- Comment: For documentation purposes, the default value is ""
Calling a Stored Procedure
To call a stored procedure, you must type the keyword CALL, followed by the name of the procedure, followed by the parameters (variables or values) in parentheses. Parentheses are required.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
Modifying a Stored Procedure
MySQL has an ALTER PROCEDURE statement for changing procedures, but it is only suitable for changing certain characteristics. If you need to change the parameters or body of a procedure, you should delete and recreate it.
Removing a Stored Procedure
DROP PROCEDURE IF EXISTS p2;
This is a simple command. The IF EXISTS statement catches an error if such a procedure does not exist.
Step 3: Options
Let's see how we can pass parameters to a stored procedure.
- CREATE PROCEDURE proc1(): empty parameter list
- CREATE PROCEDURE proc1 (IN varname DATA-TYPE): one input parameter. The word IN is optional because the default parameters are IN (in).
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one parameter returned.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both input and return.
Naturally, you can specify several parameters of different types.
IN parameter example
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//
Example OUT parameter
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //
INOUT parameter example
DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //
Step 4: Variables
Now I will teach you how to create variables and store them inside procedures. You must declare them explicitly at the beginning of the BEGIN/END block, along with their data types. Once you have declared a variable, you can use it in the same way as session variables, literals, or column names.
The variable declaration syntax looks like this:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Let's declare some variables:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Working with Variables
Once you have declared a variable, you can set its value using the SET or SELECT commands:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //
Step 5: Thread Control Structures
MySQL supports IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs to control threads within a stored procedure. We'll look at how to use IF, CASE, and WHILE since they are the most commonly used.
IF design
Using the IF construct, we can perform tasks containing conditions:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; ENDIF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value<>0"; END IF; END //
CASE design
CASE is another method of testing conditions and selecting a suitable solution. This is a great way to replace many IF constructs. The construct can be described in two ways, providing flexibility in managing multiple conditional expressions.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
WHILE design
Technically, there are three types of loops: the WHILE loop, the LOOP loop, and the REPEAT loop. You can also loop using the Darth Vader programming technique: GOTO statements. Here's an example loop:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Step 6: Cursors
Cursors are used to traverse the set of rows returned by a query and process each row.
MySQL supports cursors in stored procedures. Here is a short syntax for creating and using a cursor.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Declaring a cursor and filling it */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*What to do when there are no more records*/ OPEN cursor-name; /*Open cursor*/ FETCH cursor-name INTO variable [, variable]; /*Assign a value to a variable equal to the current value of the column*/ CLOSE cursor-name; /*Close cursor*/
In this example we will perform some simple operations using a cursor:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; ENDIF; END WHILE; CLOSE cur1; SET param1 = c; END //
Cursors have three properties that you need to understand to avoid getting unexpected results:
- Not sensitive: a cursor that opens once will not reflect changes in the table that occur later. In reality, MySQL does not guarantee that the cursor will be updated, so don't rely on it.
- Read-only: Cursors cannot be modified.
- No rewind: the cursor can only move in one direction - forward, you will not be able to skip lines without selecting them.
Conclusion
In this tutorial, I introduced you to the basics of working with stored procedures and some of the specific properties associated with it. Of course, you will need to deepen your knowledge in areas such as security, SQL expressions and optimization before becoming a true MySQL procedure guru.
You should calculate the benefits of using stored procedures in your specific application, and then create only the necessary procedures. In general, I use procedures; In my opinion, they are worth implementing into projects due to their security, code maintenance and overall design. Also, keep in mind that MySQL procedures are still a work in progress. Expect improvements regarding functionality and improvements. Please feel free to share your opinions.
We continue to work with the mytest database created in the previous one. Today we’ll add to our application the ability to respond to a user’s comment, and we’ll also learn how to create stored procedures and functions.
Creating a Stored Procedure
Open phpmyadmin. Select the mytest database and click on its title or the Browse icon. Then go to the Routines tab and create a new procedure by clicking on Add routine.
A form will appear that you need to fill out.
Routine Name (procedure/function name) ReplyToComment.
Type (type) - procedure. The difference between a procedure and a function is that a function always returns some value and contains a return statement.
Parameters (parameters) our procedure will take two parameters: the text of the response and the id of the comment to which we are responding. Both parameters will be passed from our client application.
Creating Procedure Parameters
Let's create the first parameter
Direction indicate the direction of the parameter (IN, OUT, INOUT). In our procedure, both parameters passed will be incoming (IN).
Name (parameter name) Content.
Type INT, VARCHAR, DATETIME and so on. The Content parameter contains the text of the response, which will be stored in the comment_content column. This column has a specific type, to determine it, open the wp_comments table and go to the Structure tab, find the column name we need and look at its type in the Type column. IN in this example the column is of type - text, the same type must be specified for our parameter.
Length/Values (length or value) for the Text type this field cannot be set, but usually the length is indicated here, for example VARCHAR(20), INT(10), or some default value.
Options as additional options, you can specify the current column encoding; it can also be viewed on the Structure tab, Collation column. Let's set the value to utf8.
result
Let's add a second parameter by clicking on the Add parameter button.
Direction - IN Name - ComID Type - BIGINT Length/Values - 20 Options - UNSIGNED
Both parameters have been created, we continue to fill out the form.
Definition here we describe the body of the procedure. The body is a block that begins with the BEGIN keyword and ends with the END keyword. Inside the procedure body, you can place the query text, declare variables, use branching constructs, loops, and much more, just like in any programming language.
procedure body
First, let's create a block for the beginning and end of the body of our procedure.
BEGIN END;
Now let’s add a query text that will fill in the fields in the wp-comments table when adding a new comment (response).
BEGIN INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES; END;
We will store the substituted values in variables. To create a variable, use the DECLARE keyword, then specify the name, type and length of the variable, and you can also specify the default value. If a variable has a DEFAULT parameter, then the variable is initialized.
DECLARE name type (length) DEFAULT default value;
You can also set a value for any variable using the SET operator.
SET variable name = value;
And so we will create three variables: Author, Email, UsedID, which will store values for the columns: comment_author, comment_author_email, user_id.
BEGIN DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; -- Declared the Email variable DECLARE Email varchar(100); -- Set the value of the Email variable SET Email = " [email protected]"; END;
comment_content This column stores the text of the comment, which is passed to the procedure as the Content input parameter. We will not create a separate variable, but simply substitute the value of the input parameter into VALUES.
comment_date And comment_date_gmt both columns will have the same values when first filled. Let's create a Date variable and assign it as a value the result that the built-in NOW function will return. This function returns the current date and time in DATETIME format.
DECLARE MyCurDate DATETIME DEFAULT NOW();
DECLARE MyCurDate DATETIME; SET MyCurDate = NOW();
comment_approved Is the comment approved, 1 (yes) otherwise 0. Let's create the Approved variable, but before setting the value we'll do a little checking.
DECLARE Approved varchar(20); IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; ENDIF;
comment_parent here you need to specify the id of the comment to which we are replying as the value. The ID is passed to the procedure as the second input parameter. Let's create a variable ParentCom and assign it the value of the passed parameter.
DECLARE ParentCom varchar(20); SET ParentCom = ComID ;
Last parameter left comment_post_id Here you need to indicate the id of the post in which our comment will be posted. Let's declare a variable called PostID.
DECLARE PostID BIGINT(20);
on this moment The body of the procedure should look like this
BEGIN -- variable declaration block DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; DECLARE Email varchar(100); DECLARE Date DATETIME DEFAULT NOW(); DECLARE ParentCom varchar(20); DECLARE Approved varchar(20); DECLARE PostID BIGINT(20); -- Setting variable values IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; ENDIF; SET Email = " [email protected]"; SET ParentCom = ComID ; -- request INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (Author, Email, Content, Date, Date, PostID, ParentCom, Approved, UserID); END;
From the author: Why are you sleeping at work! Are you awake and waiting for the DBMS to execute the query? So it needs to be accelerated. Have you used MySQL stored procedures? Don't know how? Well, then wake up, because now we will consider exactly this topic.
What other procedures are there?
If you have a phobia about medical procedures, then these structures are “not the right topic.” So you don't have to be afraid. But seriously, stored procedures are a convenient and useful thing for the “health” of a DBMS. They are also called "MySQL stored functions", but this is not an entirely accurate definition. Although let's deal with everything in order.
Stored procedures can significantly optimize server performance and increase its speed, since their code is stored in the RAM cache after the first execution. For all subsequent calls, the procedure will be retrieved from the cache rather than sent again for execution.
In MySQL, calling a stored procedure means that the queries written into its code will only be processed halfway. And only if the values of their parameters are changed. But not everything is so perfect. Let us first describe the positive aspects of using procedures:
Functionality encapsulation – all code is stored in one place, making it easier for other applications to access. In this way, stored procedures are similar to software functions.
Data access isolation – all users do not have access to table rows, but only to stored procedures. Which in turn increases the level of security of all data.
Increasing server speed by caching and merging requests.
In MySQL, stored procedures, in theory, are structures related to “higher matters” - DBMS programming. So you and I (as professionals) at least slowly, but... But let’s return to the procedures and describe the negative aspects of their use:
The load on the database server increases - most of the procedure code is executed on the server side. This DBMS is built on a client-server model, which involves several devices.
Data manipulation becomes more complicated - when developing applications, you will have to write part of the code and client side.
The process of transferring databases to other rails (DBMS) is becoming more complicated.
Procedures in phpMyAdmin
First, let's look at the use of stored procedures in MySQL using phpMyAdmin as an example. This way it will be easier for us to understand this type of structure. Let's start!
We launch the software shell, select the test database on the right. My database is world. Then in the main menu at the top, go to the “Procedures” tab. Here click on “Add procedure”.
After this, the “Add Procedure” dialog box will appear. We fill in all the fields indicated in the picture. Specify the procedure name and type. In the “Definition” column, enter account user, and in the comments (optional) we indicate to ourselves that this is just an example of a stored procedure.
Already at this stage we get acquainted with the peculiarities of the syntax for creating MySQL stored procedures. In the “Definition” field we write the body of the structure. Note that the query being executed is between keywords BEGIN and END:
BEGIN SELECT "HELLO, WORD!"; END
BEGIN SELECT "HELLO, WORD!" ; |
This request does not perform any actions with the database, but only displays an inscription. We indicated this in the “Access to SQL Data” field.
To finish creating our first procedure, click “Ok” at the bottom. After this, the program displays a “green” message indicating that the request was successfully completed. Its code is presented below. In MySQL, stored procedures and functions are created using the special CREATE PROCEDURE command. But more on that later.
Now let's run the created structure for execution. To do this, in the “Procedures” section, click the “Run” link. But what a disgrace this is! Where did our favorite “green” go? Why does the program “swear” and “scream” that it does not have enough allocated memory?
Where was the author of this publication looking...! Sorry, a little confused. After all, the author is me. Calm down, we'll fix everything now! This error occurs because the value of the thread_stack parameter in the main configuration file is left unchanged. By default, 128 Kb is allocated for each stream. Dedicated RAM limit for execution simple queries quite enough, but not enough for procedures.
This once again proves that more resources are spent on executing triggers and stored procedures in MySQL.
Go to configuration file my.ini, and increase the RAM limit set for each thread to 256 kb. Now run the created procedure again. This time everything went as expected, and the program returned the result without an error.
Please note that the call is made using the CALL command, indicating the name of the procedure and the parameters accepted (in parentheses).
More complex example
But still, phpMyAdmin’s capabilities are more suitable for quickly creating procedures. And to develop a stored procedure in MySQL with a dynamic number of arguments (for example), you will need more convenient software. Why:
phpMyAdmin does not want to properly “understand” procedures that were not created through a special constructor.
The program does not execute structures launched under root and with an empty password, and in Denver creating a new user and logging into phpMyAdmin under it is a real problem.
If you carefully follow my publications and fulfill all the “wishes” stated in them, then you should already have MySQL Administrator installed. In connection with it, you just need to download MySQL Query Browser from this link. It is better to use these two programs together: create procedures in the first, and test them in the other. Go:
At the top left, go through the “Catalog” tab.
Select the desired database, and in the top menu click on “Stored procedures”, and at the bottom on “Create Stored Proc”
In the editor window that appears, enter the procedure code and click “Execute SQL”.
CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`() BEGIN declare a int; set a="SELECT COUNT(*) FROM city as a"; if(a > 1000)THEN SELECT "<1000"; ELSE SELECT ">1000"; END IF; END
CREATE DEFINER = ` roman ` @ ` localhost ` PROCEDURE ` proc5 ` () BEGIN declare an int ; set a = "SELECT COUNT(*) FROM city as a"; if (a > 1000 ) THEN SELECT "<1000" ; ELSE SELECT ">1000" ; END IF ; |
You may have noticed that we are not passing any values inside the procedure. Additionally, MySQL can have an unknown number of parameters in a stored procedure, which can then be passed through new variable declarations located inside loops.
To start the procedure, go to MySQL Query Browser. First, enter your account and password, and then on the left in “Object Explorer” we find the folder with the required database. The rest of the sequence of actions is shown in the next picture.
Running a procedure in PHP
Now let's look at how a MySQL stored procedure is called in PHP. To do this, we will have to slightly “redraw” the code of our previous example. We will add an output parameter to the procedure and also change the request code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal) BEGIN SELECT COUNT(*) into col FROM city; END
CREATE DEFINER = ` root ` @ ` localhost ` PROCEDURE ` proc6 ` (out col decimal ) BEGIN SELECT COUNT (*) into col FROM city; |
To call from PHP file procedure and result output we will use the capabilities of the PDOStatement class, created specifically for working with the database via SQL
This class has been implemented relatively recently and has been supported by PHP since version 5.1.0. Before use, I advise you to check the version of the language you are using using the built-in phpversion() function.
Simply put, stored procedures (“SPs”) are procedures stored in a database (written using SQL and other control statements) that can be executed by a database engine and called from program code that runs with that engine. """ Read completely
Stored procedures in MySQL and PHP. Part 2
Taylor Wren (Taylor Ren), 03.01.2014
Creating a Stored Procedure in MySQL
Since HPs are stored on the server, it is recommended to create them directly on the server, i.e. You should not use PHP or other programming languages to execute SQL commands to create stored procedures.
Let's look at how to create a HP on a MySQL server, how to create a user for it and how to assign him privileges to run our HP. Then we will check the correctness of the result. For this I will use MySQL Workbench. You can also use other programs (for example, PHPMyAdmin). You can choose the toolkit that suits you best.
Let's say our table looks like this:
CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
For our employee who needs statistical information on salaries (average, maximum, minimum, etc.) from this table, we will create a user "tr" as follows:
CREATE USER "tr"@"localhost" IDENTIFIED BY "mypass";
Now let's assign this user the only EXECUTE privilege in the schema where the salary table is located:
Grant execute on hris.* to tr@`%`
We can verify that we have assigned the correct privilege by opening "Users and Privileges" in MySQL Bench:
Now let’s create the HP itself as follows:
DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END
After executing this command in MySQL Workbench, a ready-to-use avg_sal HP will be created. It returns the average salary from the salary table.
To check whether the user tr can actually run HP and does not have access to the salary table, we need to reconnect to MySQL server, logged in as tr . In MySQL Workbench, this can be done by creating another connection and specifying the desired user and his password.
After connecting from under tr , the first thing we notice is that the user does not see any tables at all, he only sees the HP:
Obviously, the user tr cannot access any of the tables (and therefore cannot see detailed information about salaries from the salary table), but he can run the HP we created, which will return him the average salary for the company:
Call avg_sal(@out); select @out;
The average salary will be displayed.
So, we have done all the preparatory work: created a user, assigned privileges to him, created a HP and tested it. Now let's see how to call this HP from PHP.
Calling a stored procedure from PHP
When using PDO, calling HP is quite simple. Here is the corresponding PHP code:
$dbms = "mysql"; // Replace the following connection parameters with those appropriate for your environment: $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=new PDO($dsn, $user, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);
The $res variable contains the average salary from the salary table. Now the user can perform further output processing using PHP.
conclusions
In this article, we looked at a long-forgotten component of MySQL databases: stored procedures. The benefits of using HP are obvious, but let me remind you: Stored procedures allow us to apply stricter access control to certain data when business logic requires it.
In addition, we demonstrated the basic steps in creating stored procedures, users and assigning appropriate privileges, and showed how HP is called from PHP.
This article does not cover the entire topic of stored procedures. Some important aspects such as I/O parameters, control statements, cursors, full syntax etc. were not covered in this short article.
If you are interested, please leave a comment here. If needed, we'll be happy to offer more in-depth articles on the useful and powerful aspect of MySQL, stored procedures.
Taylor Wren
Taylor is a freelance web and desktop application developer based in Suzhou in eastern China. Started with Borland development tools (C++Builder, Delphi), published a book on InterBase. Since 2003 he has been a certified Borland expert. Then I switched to web development in a typical LAMP configuration. Later I started working with jQuery, Symfony, Bootstrap, Dart, etc.
Previous publications:
