If not, go back through and check that all of the steps have been completed.
Stored Procedures
This is a simple stored procedure to list the users in the datavalues table (remember you will need to have selected the required database using the use datastorage command).
MySQL needs to differentiate between line delimiters within the stored procedure and within the command that creates the stored procedure. The delimiter needs to be changed to differentiate between the end of line within the procedure and the end of the definition. Remember to change the delimiter back.
DELIMITER //
CREATE PROCEDURE listusers()
BEGIN
SELECT DISTINCT user from datavalues;
END//
DELIMITER ;
Use SHOW PROCEDURE STATUS; to view stored procedure names.
Use SHOW CREATE PROCEDURE <procedure name> to view the procedures.
Stored Procedure with input parameter
This is a slightly more complicated procedure which takes a username as a parameter.
DELIMITER //
CREATE PROCEDURE list_values_for_user(IN username VARCHAR(50))
BEGIN
SELECT id,value,title,created FROM datavalues WHERE user=username;
END //
DELIMITER ;
Test with:
CALL list_values_for_user('root@localhost')
MariaDB [datastorage]> call list_values_for_user('root@localhost');
+----+-------+-------+---------------------+
| id | value | title | created |
+----+-------+-------+---------------------+
| 1 | 1 | test | 2019-02-27 11:06:09 |
| 2 | 2 | test | 2019-02-27 11:06:16 |
| 3 | 3 | test | 2019-02-27 11:06:23 |
+----+-------+-------+---------------------+
3 rows in set (0.00 sec)
