7.8K

Simply put, stored procedures (“SP”) are procedures stored in a database (written using SQL and other statements) that can be called a database engine or associated programming language.

In this article I will tell you how to create HP using MySQL and execute it on the MySQL server and via PHP.

Note: We are not going to cover every aspect of HP here. To obtain help on issues not covered in the article, you can always use the official MySQL documentation.

HP is also supported on other common database servers (Postgre, for example), so what we will discuss today also applies to them.

Most of us are very familiar with the usual settings that allow us to create a database application: database creation, table creation, index creation, CRUD data, client-side query generation and further processing if necessary.

This process works great in most cases, but there is one important aspect that database programming doesn't cover: the stored procedure.

I can think of at least four good reasons to use HP in database applications. Firstly, it reduces network traffic and server load.

There are four components to a standard PHP web database application:

• Client level, which is usually represented by a web browser. It provides user interaction and allows data entry through a user interface;
• Web server level, where user requests are processed and responses are sent back to the client level;
• PHP layer, which processes all PHP components, creates application logic and generates the PHP part of the response;
• Database level, which handles all database queries, including (but not limited to) SELECT queries, INSERT statements, etc.

Typically, when it comes to large application, these elements most likely are not on the same machine, it is even possible that they are not on the same network.

Although network speeds have increased significantly in the last few years, it is still the slowest and most unreliable (compared to others) data transfer channel (CPU cache, memory, hard drive etc.)

Thus, in order to increase application speed and improve reliability, some cases resort to having more data processing and logic performed on the server side (specifically a MySQL server) and less data transferred over the network.

Secondly, it improves productivity. HPs are stored and launched directly on the MySQL server. They can be pre-compiled and parsed on the database server.

This is quite different from processing the same query on the client side, where the query is parsed by database drivers, parsed, and optimized (if possible) each time the query statement is called.

This is similar to executing an interpreted language (client side) and a compiled language (database server side). And we know that the compiled program will run faster.

Thirdly, once written HP can be executed anywhere. SQL is standard and 100% platform independent. It relies only on the database server. Think about how many different languages/libraries there are that are used to work with a database.

They focus on improving the efficiency of data retrieval and processing on the server side, instead of specifying the same data processing logic, syntactically described differently by all these languages/libraries. Since this data processing logic is so widely used.

Last but not least, HP is a fundamental aspect of database security.

let's consider easy installation for the database. Let's say in information system Human Resources Management System (HRIS) has a table containing information about the salary of each employee. The HR employee should be able to get some data from this table: total salary amounts, average salary, etc.

But this employee should not have access to detailed information on the salary of each employee, since this information is confidential and may only be available to some managers.

We know that MySQL has a full privilege management system. Obviously, in this case, we cannot grant this HR employee even the SELECT privilege (because if we do this, it will mean that he/she can see the detailed salary of each employee).

But if he/she cannot access the salary table, then how will that employee be able to get the salary summary? How can we provide the HR employee with access to this information without compromising the company's HR policy?

A workaround for this problem is to use a stored procedure that issues the requested information and grants to an employee who has the EXECUTE privilege. (For a detailed list and description of MySQL privileges, you can find in the official documentation.

In this case, HP is a bridge between the user (our HR employee) and the table (salary), to which the user does not have direct access:

That's all! With the help of HP, we can provide the user with the ability to complete a task without compromising the security of the database (and personnel policy)!

Disadvantages of Using Stored Procedures

After listing all the advantages of using HP, we should get a clear understanding of some of the disadvantages and see if there are ways to improve the situation:

• Lack of version control of the HP itself. When the HP version changes, it changes without saving the history of actions for previous versions on the server side. This may create some inconvenience when the user would like to roll back changes.

  In such cases, I suggest writing HP on the client side and providing version control here. When the HP is ready, the code can be easily copied into, say, MySQL Workbench and created a server-side procedure. This way we can gain some degree of version control.

• Absence simple way « synchronize» implementing the changes and forcing all entities to use latest version, in particular when each team member has his own local database for development and testing.

  Version control may be a solution, but manual intervention is still required by updating the local copy of HP to local server DB. Another way is to use " conditional object" Team members can be divided so that at least one person is responsible for maintaining HP and solutions using its call through code.

  The rest of the group who need the results of the HP execution can develop and test their part using a conditional object, assuming that the “conditional” calls to the HP will produce the desired result. At a later stage, you can combine the different parts by removing the conditional code.

• Difficulty creating backups/exporting. HP is on the server side. Developers will only have basic privileges (SELECT, EXECUTE, etc.) and no admin rights for backup and export. In a sense, this is not a disadvantage at all, but rather one of the fundamental aspects of database security.

  And you cannot bypass this limitation, and it is not recommended. It is expected that the team will have a dedicated DBA assigned to this work. Regular backup The database can also serve for export (and import) purposes.

Creating a Stored Procedure in MySQL

Let's look at how to create HP in the MySQL server, create a user, assign privileges to him and run (under this login) HP to check whether the data is processed correctly. In my work environment I use MySQL Workbench.

There are also other tools (PHPMyAdmin, for example), so you can choose what works best for you.

Let's say we have a table like this:

CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

First, we create a user ‘tr’ for the HR employee, who needs to get summary information about the salary from this table (average salary for the company, maximum, minimum, etc.):

CREATE USER "tr"@"localhost" IDENTIFIED BY "mypass";

We assign only the EXECUTE privilege to this user for the table that contains salary data:

grant execute on hris.* to tr@`%`

We can clarify the need to provide certain privileges by reading the section “ Users and privileges» MySQL documentation:

Now let's create the HP as follows:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END

NOTE: All the above operations require administrator rights on the MySQL server.

After executing the command in MySQL Workbench, the avg_sal HP will be created and ready to be called. It outputs the average salary from the salary table.

To test whether user tr can run HP without accessing the salary table, we can change our role by logging into the MySQL server as user tr. This can be done by creating a new connection in MySQL Workbench with a different username and password.

After logging in as user tr, the first thing we will see is that we cannot see tables, only HP is available to us:

Clearly, user tr will not be able to retrieve any data from any table (i.e. will not be able to see specific salary amounts from the salary table), but he will be able to run the HP we just created to determine the average salary for the company :

call avg_sal(@out); select @out;

The result is the average salary.

At this point, we have completed all the preparatory work: we created a user, assigned privileges to him, created the HP and checked its execution. Next we will show how to call this HP from PHP.

Calling a stored procedure from PHP

Using PDO, calling HP is quite simple. The PHP code looks like this:

$dbms = "mysql"; //Replace the below connection parameters to fit 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);

$res will contain the average salary value from the salary table. After this, the user will be able to further process the outgoing data.

Conclusion

In this article, we looked at an integral component of the MySQL database: stored procedures.

The benefits of using HP are obvious, but let me emphasize them again: Stored procedures allow you to provide strict access control to certain data in the database to meet business requirements.

We also showed the basic steps of creating stored procedures, creating a user and assigning privileges, and how to call HP through PHP.

This article does not cover all aspects that relate to stored procedures. Some important points, such as I/O parameters, control statements, cursors, full syntax, etc. We did not cover this in this short article.

If you are interested in this article, please leave a comment and we will be happy to post more in-depth material about this useful and powerful element of MySQL.

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. As per the previous point, if you can only access data through stored procedures, no one else can erase your data through the SQL DELETE command.
• Improved performance as a consequence of reduction 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. Tool command line MySQL 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 great way replacing multiple 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.

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 the MySQL server, logging 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, complete 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:

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 program 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 on the 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. The allocated RAM limit is quite enough to perform simple queries, but not enough for procedures.

This once again proves that more resources are spent on executing triggers and stored procedures in MySQL.

Go to the my.ini configuration file 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 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.