Quick Start to MySQL5.0 Stored Procedure Programming

First, read the syntax for creating a stored procedure in MySQL5.0 Reference Manual: CREATE [DEFINER {user | CURRENT_USER}] PROCEDUREsp_name ([proc_parameter [,...]) [characteristic...] routine_bodyproc_parameter: [IN | OUT | INOUT] param_nametypetype: Anyva

First, read the syntax for creating a stored PROCEDURE in MySQL 5.0 Reference Manual: CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter [,...]) [characteristic...] routine_body proc_parameter: [IN | OUT | INOUT] param_name type: Any va

First, read the syntax for creating a stored procedure in MySQL 5.0 reference manual:


CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter [,...])
[Characteristic...] routine_body

Proc_parameter:
[IN | OUT | INOUT] param_name type

Type:
Any valid MySQL data type

Characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

Routine_body:
Valid SQL procedure statement


If you are not familiar with MySQL, simply reading this syntax structure is certainly not enough for MySQL Stored Procedure programming. I used ms SQL server before, so the following records my familiarity with the MySQL stored procedure are also a major difference between ms SQL SERVER and MySQL.


The first step is to write a Hello Word stored procedure as follows:

Java code

1. Create procedure phelloword ()
2. BEGIN
3. SELECT 'Hello Word! 'As F;
4. END;

CREATE PROCEDURE phelloword()BEGIN  SELECT 'Hello Word!' AS F;END;

Copy the statement used to create the phelloword stored procedure to phpMyAdmin and run the command. The following error is returned:
#1064-You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''at line 3

I have been entangled for a long time in this issue. The execution in MySQL command line tool is also not successful, but according to the prompt information, we can know that the execution in SELECT 'Hello Word! 'As F; ends, followed by END; not executed, which obviously leads to errors.


Select a separator. The syntax is as follows: DELIMITER //
The Delimiter is the symbol that notifies the MySQL client that the input is complete. ";" Is always used, but not in the stored procedure, because many statements in the Stored Procedure require semicolons.
Therefore, the above stored procedure is changed:

Java code

1. Create procedure ptest ()
2. BEGIN
3. SELECT 'Hello Word! 'As F;
4. END //

CREATE PROCEDURE ptest()BEGIN  SELECT 'Hello Word!' AS F;END //

In addition, when executing in phpMyAdmin, enter // In the Delimiter text box. This stored procedure can be created successfully.


Step 2: Write a complete stored procedure that includes parameters, variables, variable assignment, condition judgment, UPDATE statements, and SELECT returned result set, as follows:

Java code

1. Create procedure plogin
2. (
3. P_username char (15 ),
4. P_password char (32 ),
5. P_ip char (18 ),
6. P_logintime datetime
7. )
8. LABEL_PROC:
9. BEGIN
10. DECLARE v_uid mediumint (8 );
11. DECLARE v_realpassword char (32 );
12. DECLARE v_nickname varchar (30 );
13. DECLARE v_oltime smallint (6 );
14. SELECT u. uid, u. password, f. nickname, u. oltime INTO v_uid, v_realpassword, v_nickname, v_oltime
15. FROM cdb_members u inner join cdb_memberfields f ON f. uid = u. uid WHERE u. username = p_username;
16. IF (v_uid is null) THEN
17. SELECT 2 AS ErrorCode;
18. LEAVE LABEL_PROC;
19. End if;
20. IF (p_password <> v_realpassword) THEN
21. SELECT 3 AS ErrorCode;
22. LEAVE LABEL_PROC;
23. End if;
24. UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;
25. SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
26. END LABEL_PROC //

CREATE PROCEDURE plogin(    p_username char(15),    p_password char(32),    p_ip char(18),    p_logintime datetime)LABEL_PROC:  BEGIN    DECLARE v_uid mediumint(8);    DECLARE v_realpassword char(32);    DECLARE v_nickname varchar(30);    DECLARE v_oltime smallint(6);    SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid,v_realpassword, v_nickname, v_oltime    FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;    IF (v_uid IS NULL) THEN        SELECT 2 AS ErrorCode;        LEAVE LABEL_PROC;    END IF;    IF (p_password <> v_realpassword) THEN        SELECT 3 AS ErrorCode;        LEAVE LABEL_PROC;    END IF;    UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;    SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;END LABEL_PROC //

The first thing to talk about is the syntax for assigning values to variables. MySQL uses SELECT u. uid, u. password, f. nickname, u. oltime INTO v_uid, v_realpassword, v_nickname, v_oltime FROM cdb_members u inner join cdb_memberfields f ON f. uid = u. uid WHERE u. username = p_username; assign a value to the variable.

The second is the syntax structure of condition judgment, as shown below:

Java code

1. IF... THEN
2. ...;
3. ELSE
4. IF... THEN
5. ...;
6. ELSEIF
7. ...;
8. ELSE
9. ...;
10. End if;
11. End if;

IF ... THEN    ...;ELSE    IF ... THEN      ...;    ELSEIF      ...;    ELSE      ...;    END IF;END IF;

Finally, let's talk about the use of LEAVE syntax. When the following SQL statement is not executed

Step 3: Create a stored procedure for executing dynamic SQL statements.

Java code

1. Create procedure ipsp_getresourcedir
2. (
3. P_hashcode char (40)
4. )
5. LABEL_PROC:
6. BEGIN
7. DECLARE v_ SQL varchar (200 );
8. SET v_ SQL = CONCAT ('select filedir FROM ipsp_resources WHERE hashcode =/'', p_hashcode, '/'limit 0, 1 ');
9. SET @ SQL = v_ SQL;
10. PREPARE sl FROM @ SQL;
11. EXECUTE sl;
12. Deallocate prepare sl;
13. END LABEL_PROC //

CREATE PROCEDURE ipsp_getresourcedir(    p_hashcode char(40))LABEL_PROC:BEGIN    DECLARE v_sql varchar(200);    SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode =/'', p_hashcode, '/' LIMIT 0, 1');    SET @sql = v_sql;    PREPARE sl FROM @sql;    EXECUTE sl;    DEALLOCATE PREPARE sl;END LABEL_PROC //

Here we mention that "/" is an escape character. The concatenated SQL statement is similar to SELECT filedir FROM ipsp_resources WHERE hashcode = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1

In addition, @ SQL is a user variable. For detailed usage, see the MySQL reference manual.

If you have experience writing stored procedures on ms SQL server, after reading these, I think the basic MySQL Stored Procedure programming should be able to cope with it!

For more information, see the MySQL reference manual or related books!