Mysql stored procedure-Basic Knowledge

Mysql stored procedures-basic knowledge stored procedures, such as the same programming language, also contain data types, process control, input and output, and its own function libraries. ------------------ Basic syntax -------------------- 1. create procedure sp_name () begin ......... end 2. call the Stored Procedure 1. basic Syntax: call sp_name () Note: The name of a stored procedure must be enclosed by brackets, even if the stored procedure has no parameters to pass. delete stored procedure 1. basic Syntax: drop procedure sp_name // 2. note: (1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure. (4. 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. show create procedure sp_name displays detailed information about a mysql Stored procedure ------------------ data type and Operation Character ------------------ 1. Basic data type: omitted 2. Variable: Custom variable: DECLARE a INT; SET a = 100; can be replaced by the following statement: DECLARE a int default 100; variables are divided into user variables and system variables. System variables are divided into session and full-local variables. User variables generally start, misuse of user variables will make the program difficult to understand and manage. 1. Use the user variable mysql> SELECT 'Hello world' into @ x on the mysql client; mysql> SELECT @ x; mysql> SET @ y = 'Goodbye Cruel world'; mysql> select @ y; mysql> SET @ z = 1 + 2 + 3; mysql> select @ z; 2. Use the user variable mysql> create procedure GreetWorld () SEL In the Stored PROCEDURE Ect concat (@ greeting, 'World'); mysql> SET @ greeting = 'hello'; mysql> CALL GreetWorld (); 3. Pass the global user variable mysql> create procedure p1 () SET @ last_procedure = 'p1' between stored procedures; mysql> create procedure p2 () select concat ('Last procedure was', @ last_procedure); mysql> CALL p1 (); mysql> CALL p2 (); 3. Operators: 1. arithmetic Operators + SET var1 = 2 + 2; 4-minus SET var2 = 3-2; 1 * multiplication SET var3 = 3*2; 6/Division SET var4 = 10/3; 3.3333 DIV Division SET Var5 = 10 DIV 3; 3% modulo SET var6 = 10% 3; 1 2. comparison operator> 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 two values 5 BETWEEN 1 AND 10 True not between is not between the two values 5 not between 1 AND 10 False IN the set 5 IN, 3, 4) False not in the set 5 not in (1, 2, 3, 4) 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 mode match "Guy Harrison" LIKE "Guy %" True REGEXP regular regular match "Guy Harrison" REGEXP "[Gg] reg" False is null 0 is null False is not null 0 is not null True 3. logical operators 4. bitwise operator | or & and <left shift> right shift ~ Non (single-object operation, bitwise inversion) Comment: mysql stored procedures can use two styles of comment horizontal bars: -- this style is generally used for single-row comment c style: /* Comment content */generally used for multi-line comment -------------------- Process Control ------------------ 1. Sequence Structure 2. branch structure if case 3. loop Structure for loop while loop repeat until loop note: block definition, commonly used begin ...... end; you can also create an alias for the block, such as lable: begin ........... end lable; you can use leave lable; to jump out of the block and execute the code begin and end after the block is like {And} in the C language }. -------------------- Input and Output ------------------ mysql stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, INOUT Create procedure | function ([[IN | OUT | INOUT] Parameter Name Data class...]) the IN input parameter indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned, default Value: OUT output parameter. This value can be changed within the stored procedure and can be specified when the INOUT input/output parameter is returned. IN parameter examples can be changed and returned: create procedure sp_demo_in_parameter (IN p_in INT) begin select p_in; -- Query input parameter SET p_in = 2; -- modify select p_in; -- view the modified value END; execution result: mysql> Set @ p_in = 1 mysql> call sp_demo_in_parameter (@ p_in) mysql> select @ p_in; we can see that, although p_in is modified during storage, but it does not affect the value of @ p_id. Example: mysql> create procedure sp_demo_out_parameter (OUT p_out INT) begin select p_out;/* view the output parameter */SET p_out = 2; /* modify the parameter value */SELECT p_out;/* check whether changes exist */END; execution result: mysql> SET @ p_out = 1 mysql> CALL sp_demo_out_parameter (@ p_out) for example, mysql> create procedure sp _ Demo_inout_parameter (INOUT p_inout INT) begin select p_inout; SET p_inout = 2; SELECT p_inout; END; execution result: set @ p_inout = 1 call sp_demo_inout_parameter (@ p_inout) // slightly select @ p_inout; slightly attached: function library mysql stored procedure basic functions include: string type, numeric type, date type 1, string CHARSET (str) // returns the string Character Set CONCAT (string2 [,... ]) // Connection string INSTR (string, substring) // returns the position where the substring first appears in the string. If no position exists, 0 LCASE (string2) is returned. // converts it to lower-case LEFT (string2, length) // take the length (string) from the left of string2 // string LENGTH LOAD_FILE (file_name) // read the content from the file LOCATE (substring, string [, start_position]) Same as INSTR, but you can specify the start position LPAD (string2, length, pad) // repeatedly add pad to the start of string until the string length is length LTRIM (string2) // remove front-end space REPEAT (string2, count) // REPEAT count REPLACE (str, search_str, replace_str) // REPLACE search_str RPAD (string2, length, pad) with replace_str in str) // 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 first character subscript is 1 by default, that is, the position parameter must be greater than or equal to 1 mysql> select substring ('abcd',); + -------- + | substring ('abcd) | + -------- + 1 row in set (0.00 sec) mysql> select substring ('abcd',); + -------- + | substring ('abcd ', 1, 2) | + -------- + | AB | + -------- + 1 row in set (0.02 sec) TRIM ([BOTH | LEADING | TRAILING] [padding] FROM] string2) // remove the specified character UCASE (string2) at the specified position // convert it to the upper RIGHT (string2, length) // take the last length character SPACE (count) of string2) // generate count space 2, numeric ABS (number2) // absolute BIN (decimal_number) // convert decimal to binary CEILING (number2) // rounded up to CONV (number2, from_base, to_base) // hexadecimal conversion FLOOR (number2) // round down the entire FORMAT (number, decimal_places) // retain the number of decimal places HEX (DecimalNumber) // convert to hexadecimal Note: HEX () can be passed into a string, return its ASC-11 code, such as HEX ('def ') returns 4142143 can also be passed into a decimal integer, returns the HEX Encoding. For example, HEX (25) returns 19 LEAST (number, number2 [,...]). // evaluate the minimum MOD (numerator, denominator) // evaluate the remainder POWER (number, power) // evaluate the exponent RAND ([seed]) // random number ROUND (number [, decimals]) // rounding, decimals is the number of decimal places] Note: The return type is not all integers. For example, (1) the default value is "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) the number of decimal places can be set, 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, positive and negative or 0 SQRT (number2) // square 3, date type TO_DAYS () # SELECT TO_DAYS (now ()/365 The result is 2014.8822 YEARWEEK () # select yearweek ('2017-07-18 ') result: 201328 ADDTIME (date2, time_interval) // Add time_interval to date2 CONVERT_TZ (datetime2, fromTZ, toTZ) // convert the time zone CURRENT_DATE () // current date CURRENT_TIME () // current time CURRENT_TIMESTAMP () // current timestamp DATE (datetime) // returns the datetime part DATE_ADD (date2, INTERVAL d_value d_type) // Add date or time DATE_FORMAT (datetime, FormatCodes) to date2 // display datetime DATE_SUB (date2, INTERVAL d_value d_type) in formatcodes format) // subtract a time DATEDIFF (date1, date2) from date2 // two date difference days (date) // return the day dayname (date) of the date) // english week DAYOFWEEK (date) // Week (1-7), 1 is Sunday DAYOFYEAR (date) // The day of the year EXTRACT (interval_name FROM date) // extract the specified MAKEDATE (year, day) of the date from date // give the day of the year and year, and generate the date string MAKETIME (hour, minute, second) // generate the time string MONTHNAME (date) // english month name NOW () // current time SEC_TO_TIME (seconds) // converts the second to the time STR_TO_DATE (string, format) // string converted to time, display TIMEDIFF (datetime1, datetime2) in format // two time difference TIME_TO_SEC (time) // time to seconds] WEEK (date_time [, start_of_week]) // week of the YEAR (datetime) // year dayofmonth (datetime) // day of the month HOUR (datetime) // HOUR LAST_DAY (date) // The last date of the MONTH of date MICROSECOND (datetime) // microsecond month (datetime) // month minute (datetime) // Note: available in INTERVAL Type: DAY, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MINUTE, HOUR_SECOND, MINUTE, MINUTE_SECOND, MONTH, SECOND, year declare variable_name [, variable_name...] datatype [DEFAULT value]; where datatype is the mysql DATA type, such as: INT, FLOAT, DATE, VARCHAR (length) Example: DECLARE l_int INT unsigned default 4000000; DECLARE l_numeric NUMERIC (9.95) DEFAULT 1999; DECLARE l_date date default '2017-12-31 '; DECLARE l_datetime DEFAULT '2017-12-31 23:59:59'; DECLARE l_varchar VARCHAR (1999) DEFAULT 'this will not be padded ';