在MySQL中创建带分隔符的存储过程

您可以使用create procedure命令创建存储过程。语法如下 −

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

将上述语法应用于创建存储过程。查询如下 −

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();

以下是输出 −

+-----------+------+
| FirstName | Age  |
+-----------+------+
| John      | 23   |
| Bob       | 24   |
| David     | 20   |
+-----------+------+
3 rows in set (0.03 sec)
Query OK, 0 rows affected (0.06 sec)

在上面，我们使用了表格 ‘StoredProcTable’，这个表格有三条记录。使用存储过程显示了所有记录。

您可以使用存储过程检查表格中有多少条记录 –

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();

以下输出显示记录的计数 −

+------------+
| AllRecords |
+------------+
|          3 |
+------------+
1 row in set (0.31 sec)
Query OK, 0 rows affected (0.33 sec)