MySQL stored procedures

Source: Internet
Author: User
Tags month name variable scope

Introduction to Stored Procedures

SQL statements need to be compiled and executed, and stored procedures (Stored Procedure) are a set of SQL statements that are compiled in order to accomplish a particular function, stored in a database after compilation, and invoked by the user by specifying the name of the stored procedure and given a 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 stored procedures:

(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.

MySQL The stored Procedures

Stored procedures are an important feature of the database, and MySQL 5.0 does not previously support stored procedures, which makes MySQL a big compromise on applications. 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 creation of stored proceduresGrammar

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.

Parameters

The 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 stored procedure, there are three kinds of parameter types, In,out,inout:

    • The value of the in parameter must be specified when the stored procedure is called, and the value of the parameter modified in the stored procedure cannot be returned as the default value
    • out : This value can be changed inside the stored procedure and can be returned
    • INOUT: called when specified, and can be changed and returned

      Process Body

      The beginning and end of the process body is identified by using begin with end.

      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:
      As you can see, p_in is modified in the stored procedure, but does not affect the value of @p_id
       out parameter Examples
      #存储过程OUT参数
      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:
      INOUT parameter Examples
      #存储过程INOUT参数
      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:

      Variable

      Syntax: DECLARE variable name 1[, variable name 2 ...] data type [default];

      Data type is MySQL data type:

      Numeric type

      Date and Time type

      String type

      Assigning values to variables

      Syntax: SET variable name = variable value [, variable name = variable Value ...]

      User variables

      User variables typically begin with @

      Note: Misuse of user variables can cause the program to be difficult to understand and manage

      #在MySQL客户端使用用户变量
      SELECT ' Hello world ' into @x;
      SELECT @x;
      SET @y= ' Goodbye cruel World ';
      SELECT @y;
      SET @z=1+2+3;
      SELECT @z;
      Execution Result:
      #在存储过程中使用用户变量
      CREATE PROCEDURE Greetworld () SELECT CONCAT (@greeting, ' world ');
      SET @greeting = ' Hello ';
      Call Greetworld ();

      Execution Result:

      #在存储过程间传递全局范围的用户变量CREATE PROCEDURE p1 () SET @last_proc = ' P1 ';
      CREATE PROCEDURE p2 () SELECT CONCAT (' Last PROCEDURE is ', @last_proc);
      Call P1 ();
      Call P2 ();

      Execution Result:

      Comments

      MySQL stored procedures can use two styles of annotations:

    • Parallel bars:--this style is generally used for single-line annotations
    • C-style: Generally used for multi-line annotationsMySQLcalls to stored procedures

      With call and your procedure name and a parenthesis, enclose the parameters in parentheses as necessary, parameters include input parameters, output parameters, input and output parameters.

      MySQLquery for stored procedures
      #查询存储过程
      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 ';

      #查看存储过程详细信息
      SHOW CREATE PROCEDURE database. The name of the stored procedure;
      MySQLchanges to Stored procedures

      Alter PROCEDURE changes a 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}
      | COMMENT ' String '
    • The 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 statement in the subroutine that contains the 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 ';
      MySQLdeletion of stored procedures
      DROP PROCEDURE [Process 1[, Procedure 2 ...]

      Deletes one or more stored procedures from the MySQL table.

      MySQLcontrol statements for stored procedures

      Variable scope

      Internal variables have higher precedence within their scope, and when executed to end, internal variables disappear, are no longer visible, and are stored
      The internal variable cannot be found outside the 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:

      Conditional statements

      If-then-else Statements

      #条件语句IF-then-else
      DROP PROCEDURE IF EXISTS proc3;
      DELIMITER//
      CREATE PROCEDURE proc3 (in Parameter int)
      BEGIN
      DECLARE var int;
      SET var=parameter+1;
      IF Var=0 Then
      INSERT 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 statements

      #CASE-when-then-else Statements
      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
      INSERT into T VALUES (18);
      ELSE
      INSERT into T VALUES (19);
      END case;
      END;
      //
      DELIMITER;
      Looping statements
      While-do ... End-while
      DELIMITER//
      CREATE PROCEDURE proc5 ()
      BEGIN
      DECLARE var INT;
      SET var=0;
      While var<6 do
      INSERT into T VALUES (VAR);
      SET var=var+1;
      END while;
      END;
      //
      DELIMITER;
      REPEAT ... END REPEAT
      This statement is characterized by checking the results after performing an 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 LOOP
      DELIMITER//
      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;
      LABLES Marking

      The label 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 Iteration

      To start a compound statement from scratch by referencing the label of a compound statement

      #ITERATE
      DELIMITER//
      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 basic functions of stored procedures
      String class

      CHARSET (str)//return string character set
      CONCAT (string2 [,...])//connection string
      INSTR (string, substring)//returns the position where substring first appeared in string, no return 0
      LCASE (string2)//Convert to lowercase
      Left (string2, length)//The length of the string from string2
      Length (String)//string
      Load_file (file_name)//read content from File
      LOCATE (substring, string [, Start_position]) same as InStr, but can specify start position
      Lpad (string2, length, pad)//repeat pad to start with string until string length
      LTRIM (string2)//Remove front-end spaces
      REPEAT (string2, count)//Repeat Count times
      Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str
      Rpad (string2, length, pad)//after STR with pad supplement until length
      RTRIM (string2)//Remove back-end spaces
      STRCMP (string1, string2)//character comparison two string size,
      SUBSTRING (str, position [, length])//starting with the position of STR, taking a length character,
      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 1

      SELECT SUBSTRING (' ABCD ', 0,2);
      Results:
      SELECT SUBSTRING (' ABCD ',);
      Results:

      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

      Math class

      ABS (NUMBER2)//Absolute value
      BIN (Decimal_number)//decimal into binary
      CEILING (NUMBER2)//Up rounding
      CONV (number2,from_base,to_base)//Binary conversion
      Floor (NUMBER2)//Down rounding
      FORMAT (number,decimal_places)//number of decimal digits reserved
      Hex (Decimalnumber)//Turn 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) to return 19
      LEAST (number, number2 [,..])//Find minimum
      MOD (numerator, denominator)//redundancy
      Power (number, Power)//Index
      RAND ([seed])//random number
      ROUND (number [, decimals])//round, decimals is a decimal place] Note: Return types are not all integers, such as:

      #默认变为整型值
      SELECT ROUND (1.23);
      SELECT ROUND (1.56);
      #设定小数位数, returns floating-point data
      SELECT ROUND (1.567,2);
      Sign (NUMBER2)//Positive return 1, negative return-1
      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)//return symbol, plus or minus 0
      SQRT (NUMBER2)//Open Square

MySQL stored procedures

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.