在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)

以上是在MySQL中建立帶有分隔符號的預存程序的詳細內容。更多資訊請關注PHP中文網其他相關文章！