1, the use of the deposit process, a lot of similarity to delete, update, new and other operations became easy, and later also easy to manage! 2, stored procedures because the SQL statement has been pre-programmed Yi, so the speed of the run is relatively fast. 3. Stored procedures can accept parameters, output parameters, return single or multiple result sets, and return values. You can return the cause of the error to the program. 4, stored procedures run relatively stable, there will not be too many errors. Once successful, this program will be run later. 5, stored procedures are mainly run on the server, reduce the pressure on the client. 6. Stored procedures can contain program flow, logic, and queries to the database. Data logic can also be encapsulated and hidden by entities. 7. Stored procedures can execute a series of SQL statements in a single stored procedure. 8. Stored procedures can reference other stored procedures from within their own stored procedures, which simplifies a series of complex statements.
I. Creating a stored Procedure
1. Basic syntax:
CREATE PROCEDURE Sp_name ()
Begin
.........
End
2. Parameter passing
two. Calling a stored procedure
1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed
three. Delete a stored procedure
1. Basic syntax:
drop procedure sp_name//
2. Precautions
(1) cannot delete another stored procedure in one stored procedure, only another stored procedure can be called
four. Blocks, conditions, loops
1. Block definition, commonly used
Begin
......
End
You can also alias chunks, such as:
Lable:begin
...........
End lable;
You can use leave lable, jump out of chunks, execute code after chunk
2. Conditional statements
if condition Then
Statement
Else
Statement
End if;
3. Looping 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 all stored procedures in the database, including the owning database, stored procedure name, creation time, etc.
2.show CREATE PROCEDURE Sp_name
Show details of a stored procedure
Operators to use in MySQL stored procedures
MySQL Stored procedure Learning summary-operator
Arithmetic operators
+ Add SET var1=2+2; 4
-Minus SET var2=3-2; 1
* Multiply SET var3=3*2; 6
/except SET VAR4=10/3; 3.3333
div divisible SET var5=10 Div 3; 3
% modulus 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 between two values 5 between 1 and True
Not between is not between two values 5 not between 1 and False
In Set 5 in (1,2,3,4) False
Not in the collection 5 not in (1,2,3,4) True
= equals 2=3 False
<>! = is not equal to 2<>3 False
<=> strictly compares two NULL values for equality Null<=>null True
Like simple pattern matches "guy Harrison" like "guy%" True
REGEXP regular Match "Guy Harrison" REGEXP "[Gg]reg" False
Is null 0 is null False
Is isn't null 0 is not NULL True
logical Operators
with the (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 |
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
~ Bit non (Monocular operation, bitwise negation)
MYSQ functions commonly used in stored procedures, string type manipulation, math classes, datetime classes.
MySQL stored procedure basic functions
A. String class
charset (str)//return string character set
CONCAT (string2 [, ... ]) The connection string
INSTR (string, substring)//returns the position where substring first appeared in string, does not exist return 0
LCASE (string2)//converted to lowercase
left (string2, Length)//From the left of string2, take the length character
Length (string)//string
Load_file (file_name)//read contents from File
LOCATE (substr ING, string [, Start_position]) with InStr, but you can specify the start position
Lpad (string2, length, pad)//Repeat pad with the start of string until the string length is length< br> LTRIM (string2)//Remove front-end space
REPEAT (string2, count)//Repeat Count times
REPLACE (str, SEARCH_STR, REPLACE_STR)//In Str Replace_str Replace Search_str
Rpad (string2, length, pad)//with pad after str until length
RTRIM (string2)//Remove back-end space
STRCMP (string1, string2)//character comparison two-string size,
SUBSTRING (str, position [, length])//starting with the position of STR, taking a length of characters,
note: When working with strings in MySQL, the default first character subscript is 1, which means that 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 ',);
+-----------------------+
| SUBSTRING (' ABCD ', up) |
+-----------------------+
| AB |
+-----------------------+
1 row in Set (0.02 sec)
TRIM ([[[Both| Leading| TRAILING] [padding] from]string2)//remove specified characters from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Take string2 last length character
Space (count)//Generate Count of spaces
two. Math class
abs (number2)//Absolute value
BIN (decimal_number)// Decimal to Binary
CEILING (number2)//Up rounding
CONV (number2,from_base,to_base)//Binary conversion
Floor (NUMBER2)//Down rounding
FORMAT (n umber,decimal_places)//reserved decimal digits
Hex (decimalnumber)//To hex
Note: Hex () can pass in a string, then return its ASC-11 code, such as Hex (' DEF ') returned 4142143 The
can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) Return
LEAST (number, number2 [,..])//Minimum
MOD (numerator, denominator)/ /Balance
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounding, decimals to decimal place]
Note: Return types are not all integers, such as:
(1) The default becomes an integer 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 digits, return the 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 timestamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time to Date2
Date_format (datetime, Formatcodes)//Use formatcodes format to display datetime
Date_sub (Date2, INTERVAL d_value d_type)//Subtract one time from 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)//Extract the specified part of the date
Makedate (year, day)//gives the first days of the years and years, generating a date string
Maketime (hour, minute, second)//Generate time string
MONTHNAME (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//seconds turn into time
Str_to_date (string, format)//string turns into time, displayed in format
Timediff (datetime1, datetime2)//Two time difference
Time_to_sec (time)//times to seconds]
WEEK (Date_time [, Start_of_week])//weeks
Year (DateTime)//Years
DayOfMonth (DateTime)/day of the month
HOUR (DateTime)//hour
Last_day (date)//date The last date of the month
Microsecond (DateTime)//microseconds
Month (datetime)//month
MINUTE (DateTime)//min
attached: Types available in Interval
Day, Day_hour, Day_minute, Day_second, HOUR, Hour_minute, Hour_second, MINUTE, Minute_second,month, SECOND, year
The role and syntax of the MySQL stored procedure
