A stored procedure is a program stored in a database library (like a subroutine in a regular language), and, to be exact, there are two types of MySQL-supported "routine": one is the stored procedure we say, The second is a function that can return a value in other SQL statements (using the same functions as MySQL preloaded, such as Pi ()).
A stored procedure includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements. There is a new syntax definition for local variables, exception handling, loop control, and if conditional statements.
The following is an instance declaration that includes a stored procedure:
Copy Code code as follows:
CREATE PROCEDURE procedurel/*name Stored Procedure name * *
(in Parameter1 INTEGER)/*parameters parameter * *
BEGIN/*start of BLOKC statement size * *
DECLARE variable1 CHAR (10); /*variables Variable Declaration * *
If Parameter1 = THEN/*start of If If condition starts */
SET variable1 = ' birds '; /*assignment Assigned Value * *
End IF; /*end of If End/
INSERT into table1 VALUES (variable1);/*statement SQL statement * *
End/*end of block statement blocks/
MySQL version: 5.0.45 phpmyadmin version: 2.11.3
First look at the description of the syntax for creating a stored procedure in the 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, just look at this syntax structure is certainly not enough for MySQL stored procedure programming. I used to basically use MS SQL Server, so the following records the process of my familiarity with MySQL stored procedures, and also highlights the difference between MS SQL Server and MySQL.
The first step, of course, is to write a hello Word stored procedure, as follows:
Copy Code code as follows:
CREATE PROCEDURE Phelloword ()
BEGIN
SELECT ' Hello word! ' As F;
End;
The statement that creates the Phelloword stored procedure above is executed in phpMyAdmin, and the following error is reported:
#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've been obsessing about this for a long time, and it's not as successful in MySQL's command-line tools, but it's possible to know the execution in SELECT ' Hello word! ' As F; ends, trailing end; This will obviously result in an error.
Here you need to select a delimiter, the syntax is as follows: DELIMITER//
The separator is a symbol that notifies the MySQL client that the completion has been entered. Always use ";", but not in stored procedures, because many of the statements in the stored procedure need to use a semicolon.
Therefore, the above stored procedure should read:
Copy Code code as follows:
CREATE PROCEDURE ptest ()
BEGIN
SELECT ' Hello word! ' As F;
End//
In addition, when executing in phpMyAdmin, fill in the Delimiter text box//, the stored procedure can be created successfully.
The second step is to write a complete stored procedure that includes parameters, variables, variable assignments, conditional judgments, UPDATE statements, and select returns the result set, as follows:
Copy Code code 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 say is the syntax for assigning values to variables, using select U.uid in MySQL, 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; this way, assign a value to a variable.
The second is the grammatical structure of conditional judgments, as follows:
Copy Code code as follows:
IF ... THEN
...;
ELSE
IF ... THEN
...;
ELSEIF
...;
ELSE
...;
End IF;
End IF;
Finally, the use of leave grammar. When certain conditions are met, the return syntax is used in MS SQL server when you do not continue with the following SQL, and I do not find the corresponding keyword in mysql, but here you can use the leave syntax to satisfy the requirement and define a label before the begin of the stored procedure, such as: " Label_proc: "Then you need to use the place where return is interrupted to execute" LEAVE Label_proc. Can.
The third step is to create a stored procedure that executes dynamic SQL.
Copy Code code as follows:
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 mention "\" is an escape character, spliced into SQL similar to SELECT filedir from ipsp_resources WHERE hashcode = ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ' LIMIT 0, 1
In addition @sql this belongs to the user variable, the specific usage please consult the MySQL reference manual.
If you have the experience of writing stored procedures on MS SQL Server, after reading these, I think the basic MySQL stored procedure programming should be able to deal with it!
For more information, check the MySQL reference manual or related books!
MySQL 5.0 stored Procedure Learning Summary (more details)
I. Creating a stored Procedure
1. Basic grammar:
CREATE PROCEDURE Sp_name ()
Begin
.........
End
2. Parameter transfer
Two. Call the stored procedure
1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be followed by parentheses, even if the stored procedure has no parameter passing
Three. Delete stored procedures
1. Basic grammar:
drop procedure sp_name//
2. Matters needing attention
(1) cannot delete another stored procedure in one stored procedure, can only call another stored procedure
Four. blocks, conditions, loops
1. Block definition, commonly used
Begin
......
End
You can also alias a block, such as:
Lable:begin
...........
End lable;
You can use leave lable to jump out of blocks and execute code after blocks
2. Conditional statement
If condition Then
Statement
Else
Statement
End If;
3. Circular statements
(1). While loop
[Label:] While expression do
Statements
End while [Label];
(2). Loop loop
[Label:] LOOP
Statements
End LOOP [Label];
(3). Repeat until cycle
[Label:] REPEAT
Statements
UNTIL expression
End REPEAT [Label];
Five. Other common commands
1.show Procedure Status
Displays basic information about stored procedures stored in the database, including the owning database, stored procedure name, creation time, and so on
2.show CREATE PROCEDURE Sp_name
Show details of a stored procedure
The operator to use in the MySQL stored procedure
MySQL Stored procedure Learning summary-operator
Arithmetic operators
+ Add SET var1=2+2; 4
-Minus SET var2=3-2; 1
* by SET Var3=3*2; 6
/except SET VAR4=10/3; 3.3333
div divisible SET var5=10 Div 3; 3
% modulo SET var6=10%3; 1
Comparison operators
> Greater than 1>2 False
< less than 2<1 False
<= is less than or equal to 2<=2 True
>= is greater than or equal to 3>=2 True
BETWEEN 5 BETWEEN 1 and True between two values
Not BETWEEN is not between two values 5 not BETWEEN 1 and False
In the Set 5 in (1,2,3,4) False
Not in the set 5 not in (1,2,3,4) True
= equals 2=3 False
<>!= not equal to 2<>3 False
<=> strictly compare two NULL values for equality Null<=>null True
Like simple pattern matching "guy Harrison" like "guy%" True
REGEXP regular-match "Guy Harrison" REGEXP "[Gg]reg" False
Is Null was NULL 0 is null False
is not NULL isn't NULL 0 is not NULL True
logical operators
With (and)
and |
TRUE |
FALSE |
Null |
TRUE |
TRUE |
FALSE |
Null |
FALSE |
FALSE |
FALSE |
Null |
Null |
Null |
Null |
Null |
or (OR)
| OR |
TRUE th> |
FALSE |
NULL |
TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
FALSE |
Null |
Null |
TRUE |
Null |
Null |
Exclusive OR (XOR)
| XOR |
TRUE /th> |
FALSE |
NULL |
TRUE |
FALSE |
TRUE |
Null |
FALSE |
TRUE |
FALSE |
Null |
Null |
Null |
Null |
Null |
Bitwise operators
| Bit or
& Position and
<< left Shift
>> Right Shift
~ Bit non (single eye operation, bitwise reverse)
mysq functions commonly used in stored procedures, string type operations, mathematical classes, date-time classes.
MySQL stored procedure basic function
A. String class
CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//Returns the position that substring first appeared in string and does not exist return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length characters from the left-hand side of the string2
Length (string)//string lengths
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) with InStr, but you can specify the start position
Lpad (string2, length, pad)//repeat pad at beginning of string until length of string
LTRIM (string2)//Remove front space
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaced with REPLACE_STR in STR search_str
Rpad (string2, length, pad)//after STR supplemented with pad until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//begins with the position of STR, takes the length character,
Note: When handling strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1
mysql> Select substring (' ABCD ', 0,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 0,2) |
+ ——————— –+
+ ——————— –+
1 row in Set (0.00 sec)
mysql> Select substring (' ABCD ', 1,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 1,2) |
+ ——————— –+
| AB |
+ ——————— –+
1 row in Set (0.02 sec)
TRIM ([[both| Leading| Trailing] [padding] from]string2)//Remove the specified character from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Fetch string2 last length character
Space (count)//Generate Count spaces
Two. Mathematics class
ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal Turn binary
CEILING (number2)//Upward rounding
CONV (Number2,from_base,to_base)//in-process conversion
FLOOR (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//Reserved Decimal places
HEX (Decimalnumber)//Hex
Note: HEX () can pass in a string, then return its ASC-11 code, such as HEX (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) Returning 19
Least (number, number2 [,..])//Minimum value
MOD (numerator, denominator)//remainder
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal places]
Note: The return type is not all integers, such as:
(1) Change the default to a cosmetic value
Mysql> Select round (1.23);
+ ————-+
| Round (1.23) |
+ ————-+
| 1 |
+ ————-+
1 row in Set (0.00 sec)
Mysql> Select round (1.56);
+ ————-+
| Round (1.56) |
+ ————-+
| 2 |
+ ————-+
1 row in Set (0.00 sec)
(2) You can set the number of decimal places, return floating-point data
Mysql> Select round (1.567,2);
+ —————-+
| Round (1.567,2) |
+ —————-+
| 1.57 |
+ —————-+
1 row in Set (0.00 sec)
SIGN (NUMBER2)//return symbol, plus or minus 0
SQRT (NUMBER2)//Open Square
Three. Date Time class
Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//Current time stamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time in Date2
Date_format (datetime, Formatcodes)//display datetime using Formatcodes format
Date_sub (Date2, INTERVAL d_value d_type)//Minus one time on date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)//days of return date
Dayname (date)/English week
DayOfWeek (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//To extract the specified portion of a date from
Makedate (year, day)//gives the first days of years and years, generating a date string
Maketime (hour, minute, second)//Generate time series
MonthName (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//sec turns into time
Str_to_date (string, format)//String turn to time, displayed in format format
Timediff (datetime1, datetime2)//two time lag
Time_to_sec (time)//times in seconds]
WEEK (Date_time [, Start_of_week])//first few weeks
Year (datetime)/Years
DayOfMonth (DateTime)//day of the month
HOUR (DateTime)//hour
Last date of the month of Last_day (date)//date
Microsecond (datetime)/microsecond
MONTH (DateTime)//month
MINUTE (datetime)/min
Attachment: Types that can be used in interval
Day, Day_hour, Day_minute, Day_second, HOUR, Hour_minute, Hour_second,
