SQL is truly a powerful language and allows you to do amazing things once you understand its mathematical foundations or once you've worked long enough with it to feel the right way to approach data-related problems.
You can combine SQL and regular programming by using stored procedures.
In everyday coding, however, we sometimes need or can better express our thoughts using regular programming logic. You can combine both styles of programming and, in addition to that, benefit from storing your code in the database itself by using stored procedures.
(You can run the example code as you would a normal query; in a mysql> prompt or via any tool like PHPMyAdmin or MySQLWorkbench).
(For the next examples, I'll assume we have a table called
Executing a Stored Procedure
call. If you have created a stored procedure called
Simplest possible example in MySQL
DELIMITER $$ CREATE PROCEDURE sp_example1 () BEGIN SELECT * from users; END
Variables and selecting values into variables
DELIMITER $$ CREATE PROCEDURE sp_example2() BEGIN DECLARE my_var VARCHAR(511); SELECT username FROM users WHERE id=4 INTO my_var; -- my_var now holds your user's name -- you could insert it into another table, for instance. END
DELIMITER $$ CREATE PROCEDURE sp_example3(IN vr_name VARCHAR(100)) BEGIN SELECT username FROM users u WHERE u.username = vr_name; END
P.S.: A procedure that executes a normal query (with no
INTO keyword after) will work just like a normal query - its results will be displayed on the screen as if you had run the query yourself.
You use cursors to fetch data and execute some action for each result returned.
For instance, you would use a cursor to fetch all clients and, for each client retrurned, retrieve his/her neighbours given some conditions (much like a for loop in regular programming languages). Here is a simple example:
CREATE PROCEDURE sp_example4() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE vr_name VARCHAR(100); DECLARE cur1 CURSOR FOR SELECT username FROM users; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- now we open the cursor OPEN cur1; read_loop: LOOP FETCH cur1 INTO vr_name; IF done THEN LEAVE read_loop; END IF; -- now do something with the return, vr_name. END LOOP; CLOSE cur1; END;
- MySQL is waiting for more input lines after END
If you're using the command line, you might need to add an additional delimiter after the
mysql> delimiter $$ mysql> create procedure sp_example1 () -> begin -> select * from users; -> end -> $$ Query OK, 0 rows affected (0.00 sec)
- ERROR 1046 (3D000): No database selected
Prior to creating a stored procedure, you need to select a database. If your database is called
mysql> use my_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed