MySQL stored procedures

Introduction to Stored Procedures

Reprint: https://www.cnblogs.com/mark-chan/p/5384139.html

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