What is a stored procedure and how do we create a MySQL stored procedure?

A stored procedure, in the context of a regular computing language, can be defined as a subroutine-like subroutine that is stored in a database. In the context of MySQL, it is a declarative SQL statement stored in the database directory. Before writing stored procedures in MySQL, we have to check the version because MySQL 5 introduced stored procedures. The following is the syntax to create a stored procedure:

Syntax

CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter: [ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA
| MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement

Example

The following is an example where we have created a simple procedure to get all from the table 'student_info' records, these records have the following data −

mysql> select * from student_info;
+-----+---------+------------+------------+
| id  | Name    | Address    | Subject    |
+-----+---------+------------+------------+
| 100 | Aarav   | Delhi      | Computers  |
| 101 | YashPal | Amritsar   | History    |
| 105 | Gaurav  | Jaipur     | Literature |
| 110 | Rahul   | Chandigarh | History    |
+------+--------+------------+------------+
4 rows in set (0.00 sec)

Now, with the following query, we have created a stored procedure named allrecords()

mysql> Delimiter //
mysql> Create Procedure allrecords()
    -> BEGIN
    -> Select * from Student_info;
    -> END//
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;

The above is the detailed content of What is a stored procedure and how do we create a MySQL stored procedure?. For more information, please follow other related articles on the PHP Chinese website!