MySql stored procedure learning summary attachment pdf download bitsCN.com stored procedure is a program stored in the database library (just like a subroutine in a regular language), accurate to speaking, mySql supports two types of "routine (routine)": one is the stored procedure, second, functions that can return values in other SQL statements (same as the pre-loaded functions in mysql, such as pi ()).
A stored procedure includes a name, a list of parameters, and a set of SQL statements that can contain many SQL statements. Here, we have a new syntax definition for local variables, exception handling, loop control, and if condition statements.
The following is an instance statement that includes a stored procedure:
Create procedure procedurel/* name stored PROCEDURE name */
(IN parameter1 INTEGER)/* parameters parameter */
BEGIN/* start of blokc statement header */
DECLARE variable1 CHAR (10);/* variables variable declaration */
IF parameter1 = 17 THEN/* start of IF condition start */
SET variable1 = 'Birds ';/* assignment value */
End if;/* end of if end */
Insert into table1 VALUES (variable1);/* statement SQL statement */
END/* end of block statement block END */
MySQL version: 5.0.45 phpMyAdmin version: 2.11.3
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:
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:
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:
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:
IF... THEN
...;
ELSE
IF... THEN
...;
ELSEIF
...;
ELSE
...;
End if;
End if;
Finally, let's talk about the use of LEAVE syntax. When a certain condition is met and the following SQL statement is not executed, the RETURN syntax is used in ms SQL SERVER. in MySQL, the corresponding keyword is not found, however, the LEAVE syntax can be used to meet the requirements. define a label before the BEGIN of the stored procedure, for example, "LABEL_PROC:", and then run "LEAVE LABEL_PROC" where the RETURN is interrupted;.
Step 3: Create a stored procedure for executing dynamic SQL statements.
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!
Mysql 5.0 stored procedure Learning Summary (more details)
1. create a stored procedure
1. basic syntax:
Create procedure sp_name ()
Begin
.........
End
2. parameter transfer
II. call the stored procedure
1. basic syntax: call sp_name ()
Note: the stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters
III. delete stored procedures
1. basic syntax:
Drop procedure sp_name //
2. Notes
(1) you cannot delete another stored procedure in one stored procedure. you can only call another stored procedure.
4. blocks, conditions, and loops
1. Block definition, commonly used
Begin
......
End;
You can also create an alias for the block, such:
Lable: begin
...........
End lable;
You can use leave lable to jump out of the block and execute code after the block.
2. conditional statements
If condition then
Statement
Else
Statement
End if;
3. loop statements
(1). while loop
[Label:] WHILE expression DO
Statements
End while [label];
(2) loop
[Label:] LOOP
Statements
End loop [label];
(3). repeat until Loop
[Label:] REPEAT
Statements
UNTIL expression
End repeat [label];
5. other common commands
1. show procedure status
Displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2. show create procedure sp_name
Displays detailed information about a stored procedure.
Operators used in mysql stored procedures
Mysql stored procedure Learning Summary-operators
Arithmetic operators
+ Add SET var1 = 2 + 2; 4
-Subtract SET var2 = 3-2; 1
* Multiply by SET var3 = 3*2; 6
/Except SET var4 = 10/3; 3.3333
P divisible SET var5 = 10 p 3; 3
% Modulo SET var6 = 10% 3; 1
Comparison Operators
> Greater than 1> 2 False
<Less than 2 <1 False
<= Less than or equal to 2 <= 2 True
>=Greater than or equal to 3> = 2 True
BETWEEN is 5 BETWEEN two values BETWEEN 1 AND 10 True
Not between two values 5 not between 1 AND 10 False
IN the set, 5 IN (,) is False.
Not in is not in the set 5 not in (,) True
= Equal to 2 = 3 False
<> ,! = Not equal to 2 <> 3 False
<=> Strictly compare whether two NULL values are equal NULL <=> NULL True
LIKE simple pattern matching "Guy Harrison" LIKE "Guy %" True
REGEXP regular expression match "Guy Harrison" REGEXP "[Gg] reg" False
Is null 0 is null False
Is not null 0 is not null True
Logical operators
AND)
AND |
TRUE |
FALSE |
NULL |
TRUE |
TRUE |
FALSE |
NULL |
FALSE |
FALSE |
FALSE |
NULL |
NULL |
NULL |
NULL |
NULL |
OR (OR)
OR |
TRUE |
FALSE |
NULL |
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
FALSE |
NULL |
NULL |
TRUE |
NULL |
NULL |
Exclusive OR (XOR)
XOR |
TRUE |
FALSE |
NULL |
TRUE |
FALSE |
TRUE |
NULL |
FALSE |
TRUE |
FALSE |
NULL |
NULL |
NULL |
NULL |
NULL |
Bitwise operators
| Bit or
& Bit and
<Left shift
> Right shift
~ Bitwise non (single object operation, bitwise inversion)
Common functions in mysq stored procedures, string operations, mathematics, and date and time.
Mysql stored procedure basic functions
I. string type
CHARSET (str) // returns the string character set
CONCAT (string2 [,...]) // connection string
INSTR (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 is returned.
LCASE (string2) // converts it to lowercase
LEFT (string2, length) // take the length from the LEFT of string2
LENGTH (string) // string LENGTH
LOAD_FILE (file_name) // read content from the file
LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified.
LPAD (string2, length, pad) // repeat pad to start with string until the string length is length
LTRIM (string2) // Remove leading spaces
REPEAT (string2, count) // REPEAT count times
REPLACE (str, search_str, replace_str) // REPLACE search_str with replace_str in str
RPAD (string2, length, pad) // use pad after str until the length is length.
RTRIM (string2) // remove backend spaces
STRCMP (string1, string2) // compare the size of two strings by character,
SUBSTRING (str, position [, length]) // starts from the position of str and takes length characters,
Note: when processing strings in mysql, the Default subscript of the first character is 1, that is, the parameter position must be greater than or equal to 1.
Mysql> select substring ('abcd );
+ ―――――――
