您可以使用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)
