Create delimited stored procedure in MySQL

You can use the create procedure command to create a stored procedure. The syntax is as follows −

delimiter //
CREATE PROCEDURE yourStoreProcedureName()
BEGIN
   Declare variable here
   The query statement
END //

delimiter //

Apply the above syntax to create a stored procedure. The query is as follows −

mysql> use test;
Database changed
mysql> delimiter //
mysql> create procedure Sp_callTableStoredProcTable()
   −> begin
   −> select *from StoredProcTable;
   −> end //
Query OK, 0 rows affected (0.54 sec)

Now you need to change the delimiter with ; to call stored procedure −

mysql> delimiter ;

You can call stored procedure using CALL command. The syntax is as follows −

CALL yourStoredProcedureName();

The above stored procedure can be called using CALL command as shown in the below query −

mysql> call Sp_callTableStoredProcTable();

The following is the output−

+-----------+------+
| FirstName | Age  |
+-----------+------+
| John      | 23   |
| Bob       | 24   |
| David     | 20   |
+-----------+------+
3 rows in set (0.03 sec)

Query OK, 0 rows affected (0.06 sec)

In the above, we used the table 'StoredProcTable', this The table has three records. All records are displayed using a stored procedure.

You can use stored procedure to check how many records are in the table -

mysql> delimiter //
mysql> create procedure CountingRecords()
   −> begin
   −> select count(*) as AllRecords from StoredProcTable;
   −> end //
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter ;

Call the stored procedure using CALL command. The query is as follows −

mysql> call CountingRecords();

The following output is shown Count of records −

+------------+
| AllRecords |
+------------+
|          3 |
+------------+
1 row in set (0.31 sec)

Query OK, 0 rows affected (0.33 sec)

The above is the detailed content of Create delimited stored procedure in MySQL. For more information, please follow other related articles on the PHP Chinese website!