Introduction to Stored procedures SQL statements need to be compiled and then executed, whereas stored procedures (Stored Procedure) are a set of SQL statements that are compiled and stored in a database in order to accomplish a particular function. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters). Stored procedures are programmable functions that are created and saved in a database and can consist of SQL statements and control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or when you encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming that allow control over how data is accessed. Advantages of the stored procedure: (1). Enhance the functionality and flexibility of the SQL language: stored procedures can be written with control statements, with the flexibility to complete complex judgments and more complex operations. (2). Standard component programming: After a stored procedure is created, it can be called multiple times in a program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application. (3). Faster execution Speed: If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly. (4). Reduce network traffic: for operations of the same database object (such as queries, modifications), if the Transaction-sql statement involved in this operation is organized into a stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, This greatly reduces network traffic and reduces network load. (5). As a security mechanism to make full use of: through the implementation of a stored procedure permissions restrictions, can achieve the corresponding data access restrictions, avoid the unauthorized user access to data, to ensure the security of data. The mysql stored procedure stored procedure is an important feature of the database, and MySQL 5.0 does not support stored procedures before, which makes MySQL a great compromise in application. Fortunately, MySQL 5.0 began to support stored procedures, which can greatly improve the processing speed of the database, but also improve the flexibility of database programming. MySQL stored procedure creation syntax create PROCEDURE procedure name ([[in| Out| INOUT] Parameter name data type [, [in| Out| INOUT] Parameter name data type ...]) [Features ...] Process Body delimiter//CREATE PROCEDURE MyProc (out S int.) BEGIN SELECT COUNT (*) into s from students; END//delimiter; separator MySQL defaults to ";" is a delimiter, if there is no declaration of the separator, the compiler will treat the stored procedure as an SQL statement, so the compilation process will be error, so in advance with "DELIMITER//" To declare the current segment delimiter, let the compiler put two "//" between the contents of the stored procedure Code, will not execute the code; "DELIMITER;" means to restore the delimiter. The parameter stored procedure may have input, output, input and output parameters as required, if there are multiple parameters separated by ",". The parameters of the MySQL stored procedure are used in the definition of the stored procedure, there are three parameter types, the value of the In,out,inout:in parameter must be specified when the stored procedure is called, the value of the parameter modified in the stored procedure cannot be returned as the default value out: The value can be changed inside the stored procedure, And can be returned inout: Called when specified, and can be changed and returned to the process body of the beginning and end of the body of the use of begin to identify. In parameter example delimiter//CREATE PROCEDURE In_param (in p_in int) BEGIN SELECT p_in; SET p_in=2; SELECT p_in; END; DELIMITER; #调用SET @p_in = 1; Call In_param (@p_in); SELECT @p_in; execution Result: Imageimageimage above can be seen, p_in although in the stored procedure is modified, but does not affect the value of @p_id out parameter example # stored procedure out parameter delimiter//CREATE PROCEDURE Out_param (out p_out int) BEGIN SELECT p_out; SET p_out=2; SELECT p_out; END; DELIMITER; #调用SET @p_out = 1; Call Out_param (@p_out); SELECT @p_out; execution Result: imageimageimage inout parameter Example # stored procedure inout parameter delimiter//CREATE PROCEDURE Inout_param (inout p_inout int) BEGIN SELECT p_inout; SET p_inout=2; SELect P_inout; END; DELIMITER; #调用SET @p_inout = 1; Call Inout_param (@p_inout); SELECT @p_inout; execution Result: Imageimageimage variable syntax: DECLARE variable name 1[, variable name 2 ...] data type [default]; Data type data type for MySQL: Numeric type image date and time type image string type image variable assignment syntax: SET variable name = variable value [, variable name = variable Value ...] User variable user variables generally begin with @ Note: Misuse of user variables can cause the program to be difficult to understand and manage # in the MySQL client using the user variable select ' Hello world ' into @x; SELECT @x; SET @y= ' Goodbye cruel World '; SELECT @y; SET @z=1+2+3; Select @z; execution result: Imageimageimage #在存储过程中使用用户变量CREATE PROCEDURE greetworld () Select CONCAT (@greeting, ' world '); SET @greeting = ' Hello '; Call Greetworld (); execution result: Image #在存储过程间传递全局范围的用户变量CREATE PROCEDURE p1 () SET @last_proc = ' P1 '; CREATE PROCEDURE p2 () SELECT CONCAT (' Last PROCEDURE is ', @last_proc); Call P1 (); Call P2 (); execution Result: image Note the MySQL stored procedure can use two styles of annotations: Parallel bars:----this style is generally used for single-line comment C-style: Commonly used for multiline comments MySQL stored procedure is called with the name of your procedure and a parenthesis, enclosed in parentheses as needed, Add parameters, parameters include input parameters, output parameters, input and output parameters. Query for MySQL stored procedure # Query stored procedure select name from Mysql.proc WHERE db= ' database name '; SELECT routine_name from Information_schema.routines WHERE routine_schema= ' database name '; SHOW PROCEDURE STATUS WHERE db= ' database name '; #查看Stored procedure details show CREATE PROCEDURE database. The name of the stored procedure; Modification of the MySQL stored procedure alter PROCEDURE changes the pre-specified stored procedure established with create PROCEDURE, which does not affect the associated stored procedure or storage functionality. ALTER {PROCEDURE | FUNCTION} Sp_name [characteristic ...] characteristic:{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}| SQL SECURITY {definer | INVOKER}| The COMMENT ' string ' sp_name parameter represents the name of the stored procedure or function; The characteristic parameter specifies the attributes of the stored function. CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data; No SQL indicates that the subroutine does not contain SQL statements; READS SQL data represents the statements in the subroutine that contain read data; modifies SQL Data represents a statement in a subroutine that contains write data. SQL SECURITY {definer | INVOKER} indicates who has permission to execute, Definer indicates that only the definition is capable of execution; INVOKER indicates that the caller can execute it. COMMENT ' String ' is a comment message. Instance: #将读写权限改为MODIFIES SQL DATA and indicates that the caller can execute. ALTER PROCEDURE num_from_employee modifies SQL data SQL SECURITY INVOKER; #将读写权限改为READS SQL data and add the comment information ' FIND NAME '. ALTER PROCEDURE name_from_employee READS SQL DATA COMMENT ' FIND name '; Delete the MySQL stored procedure drop PROCEDURE [procedure 1[, Procedure 2 ...] Deletes one or more stored procedures from the MySQL table. The control statement variable scope internal variable of the MySQL stored procedure has a higher priority within its scope, and when executed to end, the internal variable disappears, is no longer visible, and cannot be found outside the stored procedure, but it can be saved by an out parameter or by assigning its value to a session variable. #变量作用域DELIMITER//CREate PROCEDURE proc () BEGIN DECLARE x1 VARCHAR (5) DEFAULT ' outer '; BEGIN DECLARE x1 VARCHAR (5) DEFAULT ' inner '; SELECT X1; END; SELECT X1; END; DELIMITER; #调用CALL proc (); execution Result: imageimage conditional Statement IF-THEN-ELSE Statement # conditional Statement If-then-elsedrop PROCEDURE IF EXISTS proc3;d Elimiter//create PROCEDURE proc3 (in parameter int) BEGIN DECLARE var int; SET var=parameter+1; IF var=0 then inserts into T VALUES (17); END IF; IF parameter=0 then UPDATE t SET s1=s1+1; ELSE UPDATE T SET s1=s1+2; END IF; END; DELIMITER; Case-when-then-else Statement #case-when-then-else statement delimiter//CREATE PROCEDURE proc4 (in parameter int) BEGIN DECLARE var int; SET var=parameter+1; Case Var when 0 then INSERT into T VALUES (17); When 1 then inserts into T VALUES (18); ELSE INSERT into T VALUES (19); END case; END; DELIMITER; Loop Statement while-do ... End-whiledelimiter//CREATE PROCEDURE proc5 () BEGIN DECLARE var INT; SET var=0; While Var<6 does INSERT into T VALUES (VAR); SET var=var+1; END while; END; DELIMITER; REPEAT ... END Repeat this statement's specialThe point is the check result after performing the operation delimiter//CREATE PROCEDURE proc6 () BEGIN DECLARE v INT; SET v=0; REPEAT INSERT into T VALUES (v); SET v=v+1; UNTIL v>=5 END REPEAT; END; DELIMITER; LOOP ... END loopdelimiter//CREATE PROCEDURE proc7 () BEGIN DECLARE v INT; SET v=0; Loop_lable:loop INSERT into T VALUES (v); SET v=v+1; IF v >=5 then LEAVE loop_lable; END IF; END LOOP; END; DELIMITER; The lables designator designator can be used before the begin repeat while or loop statement, and the statement designator can only be used before a valid statement. You can jump out of the loop so that the run instruction reaches the final step of the compound statement. Iterate iterate by referencing the label of the compound statement, to start from the new compound statement #iteratedelimiter//CREATE PROCEDURE Proc8 () begin DECLARE v INT; SET v=0; Loop_lable:loop IF v=3 then SET v=v+1; Iterate loop_lable; END IF; INSERT into T VALUES (v); SET v=v+1; IF v>=5 then LEAVE loop_lable; END IF; END LOOP; END; DELIMITER; MySQL stored procedure basic function String class CharSet (str)//return string Character set concat (string2 [,...])//Connection string InStr (string, substring)// Returns substring the first occurrence in a string where there is no return 0LCASE (string2)//converted to lowercase left (string2, length)//Length ( String)//string length Load_file (file_name)//FromThe file reads the contents locate (substring, string [, Start_position]) with InStr, but can specify the starting position Lpad (string2, length, pad)//Repeat pad with the start of string until the string length Degrees Lengthltrim (string2)//Remove front-end space Repeat (string2, count)//Repeat Count times replace (str, SEARCH_STR, REPLACE_STR)//with Replac in Str E_str Replace Search_strrpad (string2, length, pad)//after STR with pad, until length is Lengthrtrim (string2)//Remove back end space strcmp (string1, string2 )//character comparison two string size, SUBSTRING (str, position [, length])//from the position of STR, take length characters, note: When working with strings in MySQL, the default first character subscript is 1, That is, the parameter position must be greater than or equal to 1SELECT SUBSTRING (' ABCD ', 0,2); Result: Image SELECT SUBSTRING (' ABCD '); Result: Image TRIM ([[both| Leading| TRAILING] [padding] from]string2)//Remove the specified character from the specified position UCase (string2)//Convert to uppercase Right (string2,length)// Take string2 last length character space (count)//Generate count Spaces Math class ABS (NUMBER2)//Absolute Bin (decimal_number)//Decimal Turn binary ceiling (number2)// Rounding up Conv (number2,from_base,to_base)//Input conversion floor (NUMBER2)//Down rounding Format (number,decimal_places)//reserved decimal digits Hex ( Decimalnumber)//Turn hex Note: Hex () can pass in a string, then return its ASC-11 code, such as Hex (' DEF ') return 4142143 can also pass in a decimal integer, return its hexadecimal encodingCode, such as Hex (25), returns 19LEAST (number, number2 [,..])//Minimum mod (numerator, denominator)//seek Power (numbers, Power)//index rand (see D])//random numbers ROUND (number [, decimals])//rounded, decimals to decimal place] Note: Return types are not all integers, such as: #默认变为整型值SELECT ROUND (1.23); Imageselect ROUND (1.56); image# sets the number of decimal digits, returns the float data select ROUND (1.567,2); image sign (NUMBER2)//Positive return 1, negative return-1st period time Class Addtime (Date2, time_ Interval)//Add Time_interval to Date2convert_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 in Date2 or Time Date_format (datetime, Formatcodes)//Use formatcodes format to display datetimedate_sub (Date2, INTERVAL d_value d_type)//on Date2 Go to a time DateDiff (Date1, Date2)//Two date Difference Day (date)//Return date days dayname (date)//English week DayOfWeek (date)//week (1-7), 1 for Sunday DayOfYear (date)//The day of the year extract (Interval_name from date)//The specified part of the date in the extract (year, day)//gives the first days of the years and years, the date string maketime (ho ur, 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 to time, display Timediff in format format (DA TETIME1, DateTime2)//Two difference time_to_sec (time)//times to Seconds]week (Date_time [, Start_of_week])//Week of Year (datetime)//Years dayof Day of Month (datetime)//Month Hour (datetime)//hour Last_day (date)//date the last date microsecond (datetime)//microsecond month (datetime)// Month minute (datetime)//return symbol, plus or minus 0SQRT (number2)//Open Square
MySQL stored procedures
