Calling a Routine from Within Another Routine
It??™s possible to call a routine from within another routine, saving you the inconvenience
of having to repeat logic unnecessarily. An example follows:
DELIMITER //
CREATE PROCEDURE process_logs()
BEGIN
SELECT "Processing Logs";
END//
CREATE PROCEDURE process_users()
BEGIN
SELECT "Processing Users";
END//
CREATE PROCEDURE maintenance()
BEGIN
CALL process_logs();
CALL process_users();
END//
DELIMITER ;
Executing the maintenance() procedure produces the following:
+-----------------+
| Processing Logs |
+-----------------+
| Processing Logs |
+-----------------+
1 row in set (0.00 sec)
CHAPTER 32 ?– STORED ROUTINES 841
+------------------+
| Processing Users |
+------------------+
| Processing Users |
+------------------+
1 row in set (0.00 sec)
Modifying a Stored Routine
At present MySQL only offers the ability to modify stored routine characteristics, via
the ALTER statement. Its prototype follows:
ALTER (PROCEDURE | FUNCTION) routine_name [characteristic ...]
For example, suppose you want to change the SQL SECURITY characteristic of the
calculate_bonus method from the default of DEFINER to INVOKER:
ALTER PROCEDURE calculate_bonus SQL SECURITY invoker;
Deleting a Stored Routine
To delete a stored routine, execute the DROP statement. Its prototype follows:
DROP (PROCEDURE | FUNCTION) [IF EXISTS] sp_name
For example, to drop the calculate_bonus stored procedure, execute the following
command:
mysql>DROP PROCEDURE calculate_bonus;
As of version 5.
Pages:
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953