What is a procedure in MySQL? [duplicate]

Question

I've Googled but come up with nothing that I can get my head around.

Are the performance gains from using stored procedures significant?

Would I still want to use prepared statements in conjunction with stored procs or is it generally a one or the other thing?

Can I create stored procs through PHPMyAdmin and manage them from there as well?

What would a stored procedure look like for something simple like this-

SELECT * FROM table a 
INNER JOIN otherTable b 
ON a.join_id=b.join_id 
WHERE someVar = :boundParam

and how would the PHP work (PDO) to call it and bind its parameter?

Ieyasu

no contest - sprocs are more efficient on so many levels. Let's forget query optimisation and execution speed - let's think in terms of calls required, bytes sent..., grants required (exec vs all), security, how many calls does mysqli/pdo need to make to exec 1 parameterised query ? would that be 2 ??? lol – Sheba

Answer 1

A procedure:

• can have zero or more SQL statements with BEGIN ... END statement. *No statement without BEGIN ... END statement gets error.

• can have only single statement without BEGIN ... END statement not changing the delimiter to $$ without error.

• can have multiple parameters with IN, OUT and INOUT and without them.

• can get zero or more values with zero or more IN and INOUT parameters from the caller.

• can return zero or more values to the caller with OUT and INOUT parameters.

• can have local variables and user-defined variables. *My answer explains local variables and user-defined variables.

• can have transaction.

• cannot have RETURN statement otherwise there is the error.

• is not atomic by default so if there is error, it is not rollbacked automatically. *My answer explains it in detail.

*In this answer, I explain how to create a procedure with an IN parameter and my answer and my answer explain OUT and INOUT parameters respectively and the doc explains a procedure in detail.

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 2 as shown below:

INSERT INTO test (num) VALUES (2);

Now, you can create addition(IN value INT) procedure which adds value to num as shown below. *The IN parameter IN value INT can get the value from the caller and you can also use value INT without IN which is also recognized as an IN parameter and basically, you need to change the default delimiter ; to something like $$ when creating a procedure otherwise there is error, then after creating a procedure, you need to change the delimiter $$ back to ; as shown below and my answer explains delimiter and you must select a database when creating a procedure otherwise there is the error:

DELIMITER $$

CREATE PROCEDURE addition(IN value INT)
BEGIN
UPDATE test SET num = num + value;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE addition(INOUT v1 INT, INOUT v2 INT)
BEGIN
SELECT v1+1, v2+1 into v1, v2;
END$$

DELIMITER ;

Then, you can call addition(3) with CALL statement, then 3 is added to num as shown below:

mysql> CALL addition(3);
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  5   |
+------+

Or, you can set the user-defined session variable e.g. @v with 3, then 3 is added to num as shown below. *The initial value of a user-defined session variable is NULL:

mysql> SET @v = 3;
...
mysql> CALL addition(@v);
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  5   |
+------+

In addition, you can create addition() procedure with the user-defined session variable e.g. @v and without an IN parameter as shown below:

DELIMITER $$

CREATE PROCEDURE addition()
BEGIN                    -- ↓↓ Here
UPDATE test SET num = num + @v;
END$$

DELIMITER ;

Then, you set @v with 3 and call addition(), then 3 is added to num as shown below:

mysql> SET @v = 3;
...
mysql> CALL addition();
...
mysql> SELECT num FROM test;
+------+
| num  |
+------+
|  5   |
+------+

And, in this case below, you can create addition(IN value INT) procedure not changing the delimiter to $$ without error:

CREATE PROCEDURE addition(IN value INT)
UPDATE test SET num = num + value;

Improve

I wonder if this answer would have been better placed on one of the duplicates, especially considering that another, well-upvoted, answer to this question has been deleted. – Heel

@AndrewMorton I find the checkers related to database are strange, mean, close-minded, unreasonable or too strict. I ofter answer Python's questions and the checkers related to Python are good, open-minded and reasonable. – Improve