mysql stored procedures and functions

Posted by stephanie on Thu, 11 Jul 2019 01:28:00 +0200

Abstract: Stored procedures and functions are sets of SQL statements defined in the database, and then directly call these stored procedures and functions to execute the defined SQL statements. Stored procedures and functions can avoid developers repeating the same SQL statements. Moreover, stored procedures and functions are stored and executed in MySQL servers, which can reduce data transmission between client and server.

I. Stored Procedures

1.1. Basic Grammar

  1. CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
  2.   
  3. 　　[characteristic ...] routine_body  

Sp_name: The name of the stored procedure, which is created in the current database by default. This name should try to avoid the same name as MySQL's built-in functions

Proc_parameter: A list of stored procedure parameters
Format [IN|OUT|INOUT]param_name type
Param_name is the parameter name and type is the data type of the parameter. Multiple parameters are separated by commas. Input parameters, output parameters and input/output parameters are identified by in/out/inout, respectively. The name of the parameter should not be the same as the column name of the table.

Characteristic: Some feature settings for stored procedures are introduced separately
COMMENT'string': Used to describe stored procedures, where string is the description content and comment is the keyword.
2 LANGUAGE SQL: specifies that the language in which the stored procedure is written is the SQL language. This option may not be specified.
DETERMINISTIC: Represents that stored procedures produce the same results for the same input parameters; NOT DETERMINISTIC indicates that uncertain results are generated (default).

4 contains sql | no sql | reads sql data | modifies sql data Contains sql to indicate that the stored procedure contains statements for reading or writing data (default)
No sql means no sql statement
Reads sql data represents statements that stored procedures contain read-only data
Modifies sql data denotes that stored procedures contain statements that only write data
5 sql security: This feature specifies whether a stored procedure is executed with the permission of the user (definer) who created the stored procedure or with the permission of the invoker. Definition is Definer

Routine_body: The body of a stored procedure, which contains sql statements that must be executed when a procedure is called. Start with begin and end. If there is only one sql statement in the stored procedure body, the start-end flag can be omitted.

1.2. Data preparation

  1. CREATE TABLE  
  2.     t_user  
  3.     (  
  4.         USER_ID INT NOT NULL AUTO_INCREMENT,  
  5.         USER_NAME CHAR(30) NOT NULL,  
  6.         USER_PASSWORD CHAR(10) NOT NULL,  
  7.         USER_EMAIL CHAR(30) NOT NULL,  
  8.         PRIMARY KEY (USER_ID),  
  9.         INDEX IDX_NAME (USER_NAME)  
  10.     )  
  11.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  
Then here are some of the inserted data:


1.3 IN, OUT, INOUT parameters

(1) Storage process with IN

  1. //Create a stored procedure. Run in cmd
  2. CREATE  PROCEDURE  SP_SEARCH(IN p_name CHAR(20))  
  3. BEGIN  
  4. IF p_name is null or p_name='' THEN  
  5. SELECT * FROM t_user;  
  6. ELSE  
  7. SELECT * FROM t_user WHERE USER_NAME LIKE p_name;  
  8. END IF;  
  9. END  
Because; branch conflict, so add delimiter //. Set // to End Run Symbol

As follows:


Call:

  1. //Call and output the result.
  2. CALL SP_SEARCH('Pin-Wen Lin')  
Result


(2) Storage process with OUT

  1. //beltOUTReturn  
  2. CREATE  PROCEDURE  SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT)  
  3. BEGIN  
  4. IF p_name is null or p_name='' THEN  
  5. SELECT * FROM t_user;  
  6. ELSE  
  7. SELECT * FROM t_user WHERE USER_NAME LIKE p_name;  
  8. END IF;  
  9. SELECT FOUND_ROWS() INTO p_int;  
  10. END  
Invoke output: counting the number of people at the beginning of the forest

  1. //Call and output the result.
  2. CALL SP_SEARCH2('Forest%',@p_num);  
  3. SELECT @p_num;  


(3) Storage process with INOUT

  1. //Storage process with INOUT
  2. CREATE PROCEDURE sp_inout(INOUT p_num INT)  
  3. BEGIN  
  4. SET p_num=p_num*10;  
  5. END  
  6. //Call and output the result.
  7. SET @p_num=2;  
  8. call sp_inout(@p_num);  
  9. SELECT @p_num;  
Output results:

1.4. Stored Procedure Body

The combination of various sql statements and procedural statements can be used in stored procedure body to encapsulate complex business logic and processing rules in database application, so as to realize flexible programming of database application. Here are some common grammatical elements used to construct stored procedure bodies.
1. Local variables
Local variables can be declared in the stored procedure body to store temporary results in the stored procedure body.

  1. DECLARE var_name[,...] type [DEFAULT value]  
  2. Var_name:Specifies the name of a local variable  
  3. Type:Data types used to declare local variables  
  4. defaultclause:Used to specify a default value for a local variable. If not specified, default isnull.  
Such as:
  1. Declare cid int(10);  
Instructions:
Local variables can only be started in stored procedure bodies. Declare in the end statement block.
Local variables must be declared at the beginning of the stored procedure body.
Local variables can only be used to declare its begin..end statement block, but statements in other statement blocks cannot be used.
Local variables differ from user variables in that they are declared without the @ symbol and can only be used in the begin..end statement block, while user variables are declared with the @ symbol in front of their name and declared user variables exist throughout the session.
2. set statement
Assignment of Local Variables Using set Statement
  1. Set var_name=expr  
  2. Set cid=910;  
3. select... into statement
Store the values of selected columns directly in local variables, in grammatical format
  1. Select col_name[,...] into var_name[,...] table_expr  
  2. Col_name:Used to specify column names  
  3. Var_name:The variable name used to specify the assignment  
  4. Table_expr:ExpressselectIn the statementfromWords and sentences and the grammatical parts behind them  
Description: select in stored procedure body. The result set returned by the into statement can only have one row of data.

4. Defining Processing Procedures
It is a problem that may be encountered during the execution of pre-defined programs. And you can define solutions to these problems in the handler. This method can predict possible problems ahead of time and propose solutions.

  1. DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement  
  2. handler_type:CONTINUE | EXIT | UNDO  
  3. Condition_value:Sqlwarning | not found | sqlexception  
5. Process Control Statement
(1) Conditional Judgment Sentences
If statement

  1. If search_condition then statement_list  
  2. [elseif search_condition then statement_list]...  
  3. [else statement_list]  
  4. End if  
Search_condition parameter: conditional judgement statement
Statement_list parameter: Execution statement with different conditions

Storage process instance of multiple IF s

Data preparation

Student table:

  1. CREATE TABLE  
  2.     t_student  
  3.     (  
  4.         STU_ID INT NOT NULL,  
  5.         STU_NAME CHAR(10) NOT NULL,  
  6.         STU_CLASS INT NOT NULL,  
  7.         STU_SEX CHAR(2) NOT NULL,  
  8.         STU_AGE INT NOT NULL,  
  9.         PRIMARY KEY (STU_ID)  
  10.     )  
  11.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

The data are as follows:


Score sheet (STU_ID is the student sheet is a foreign key relationship):

  1. CREATE TABLE  
  2.     t_grade  
  3.     (  
  4.         STU_ID INT NOT NULL,  
  5.         STU_SCORE INT NOT NULL,  
  6.         FOREIGN KEY (STU_ID) REFERENCES t_student (STU_ID),  
  7.         INDEX STU_ID (STU_ID)  
  8.     )  
  9.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

Then write a stored procedure: the person who returns each score level

  1. //Storage Procedures with Multiple IF s
  2. CREATE  PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level char(1))  
  3. BEGIN  
  4. IF p_level ='A'  THEN  
  5. SELECT * FROM t_grade WHERE STU_SCORE >=90;  
  6. ELSEIF  p_level ='B'  THEN  
  7. SELECT * FROM t_grade WHERE STU_SCORE <90 AND  STU_SCORE>=80;  
  8. ELSEIF  p_level ='C'  THEN  
  9. SELECT * FROM t_grade WHERE STU_SCORE <80 AND  STU_SCORE>=70;  
  10. ELSEIF  p_level ='D'  THEN  
  11. SELECT * FROM t_grade WHERE STU_SCORE <60;  
  12. ELSE  
  13. SELECT * FROM t_grade;  
  14. END IF;  
  15. END  

Call procedure:
  1. //Call and output the result.
  2. CALL SP_SCHOLARSHIP_LEVEL('A');  

Case statement
Form 1
  1. Case case_value  
  2. When when_value then statement_list  
  3. [When when_value then statement_list]...  
  4. [else statement_list]  
  5. End case  
Form 2
  1. Case  
  2. When search_condition then statement_list  
  3. End case  
Example

  1. CREATE  PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1))  
  2. BEGIN  
  3. DECLARE p_num int DEFAULT  0;  
  4. CASE p_level  
  5. WHEN 'A'  THEN  
  6. SET p_num=90;  
  7. WHEN 'B'  THEN  
  8. SET p_num=80;  
  9. WHEN 'C'  THEN  
  10. SET p_num=70;  
  11. WHEN 'D'  THEN  
  12. SET p_num=60;  
  13. ELSE  
  14. SET p_num=0;  
  15. END CASE;  
  16. SELECT * FROM t_grade g, t_student s WHERE g.STU_ID=s.STU_ID AND g.STU_SCORE >= p_num ;  
  17. END  
Call:

  1. //Call and output the result.
  2. CALL SP_SCHOLARSHIP_LEVEL3('d');  



(2) Loop statement
While statement, repeat statement and loop statement.
The While Statement

  1. [begin_label:]  
  2. while search_condition do  
  3. Statement_list  
  4. End while  
  5. [end_label]  
Judge whether the condition search_condition is true, if it is true, execute the statement in statement_list, and then judge, if it is still true, continue to loop until the condition judgement is not true.
Example

  1. //Storage procedure with while
  2. CREATE PROCEDURE sp_cal(IN p_num INT,OUT p_result INT)  
  3. BEGIN  
  4.  SET p_result=1;  
  5.  WHILE p_num > 1 DO  
  6.   SET p_result = p_num * p_result;  
  7.   SET p_num = p_num-1;  
  8.  END WHILE;  
  9. END  
  10. //Call and output the result.
  11. CALL sp_cal(5,@result);  
  12. SELECT @result;  
Output: Calculate 5!


Repeat statement grammatical format

  1. [begin_label:]  
  2. repeat  
  3. Statement_list  
  4. Until search_condition  
  5. End repeat  
  6. [end_label]  
The Repeat statement first executes the statement in statement_list, and then determines whether the condition search_condition is true or not. If it is true, the loop ends and if it is not, the loop continues.
Repeat executes first and then judges, while executes first and then judges.
Use examples:

  1. //Storage process with repeat
  2. CREATE PROCEDURE sp_cal2(IN p_num INT,OUT p_result INT)  
  3. BEGIN  
  4.   SET p_result=1;  
  5.   REPEAT  
  6.     SET p_result = p_num * p_result;  
  7.     SET p_num = p_num-1;  
  8.     UNTIL p_num<=1  
  9.   END REPEAT;  
  10. END  
  11. //Call and output the result.
  12. CALL sp_cal2(5,@result);  
  13. SELECT @result;  


1.5. Calling stored procedures

  1. Call sp_name([parameter[,...]]);  
  2. Sp_name Name of the called stored procedure  
  3. Parameter:Specifies the parameters to be used to invoke stored procedures.  

1.6. Modifying stored procedures

  1. Alter procedure proc_name[characteristic...]  

You can only modify the characteristics of a stored procedure. If you want to modify the contents of a stored procedure, you can delete the stored procedure first, and then recreate it.

1.7. Delete stored procedures

  1. Drop procedure [if exists] sp_name;  

2. Functions

2.1. Definition

In MySQL, the basic form of creating storage functions is as follows:

  1. CREATE FUNCTION sp_name([func_parameter[,...]])  
  2. RETURNS type  
  3. [characteristic ...] routine_body  
  4. Return  

The clause is used to declare the data type of the return value of the stored function. Stored procedure is a set of sql statements defined by users, involving tasks of specific tables or other objects. Users can call stored procedures. Functions are usually defined methods of the database, which receive parameters and return certain types of values without involving specific user tables.

Calling storage functions

  1. Select sp_name([func_parameter...])  
  2. Select fn_search(2);  
Delete storage function drop
Modify the storage function alter to modify some of the relevant features of the storage function.

2.2. Examples of Function Use

(Compare sizes, return large numbers)

  1. /**Function usage**/  
  2. CREATE FUNCTION sp_cal_max(p_num1 INT,p_num2 INT)  
  3. RETURNS INT  
  4. BEGIN  
  5. IF p_num1 >= p_num2 THEN  
  6. RETURN p_num1;  
  7. ELSE  
  8. RETURN p_num2;  
  9. END IF;  
  10. END  
Call:

  1. SET @p_num1=2;  
  2. SET @p_num2=34;  
  3. SELECT sp_cal_max(@p_num1,@p_num2);  


2.3. Differences between stored procedures and functions

1) Generally speaking, the functions implemented by stored procedures are more complex, while the functions implemented by functions are more specific. Stored procedures, powerful, can perform a series of database operations including table modification; user-defined functions can not be used to perform a set of operations to modify the state of the global database.

2) For stored procedures, parameters, such as recordsets, can be returned, while functions can only return values or table objects. Functions can only return one variable; stored procedures can return more than one variable. The parameters of stored procedure can be IN, OUT and INOUT, while the function can only have IN class ~~stored procedure declaration without return type, while the function declaration needs to describe return type, and the function body must contain a valid RETURN statement.

3) Stored procedures can use uncertain functions, which are not allowed to be built in the body of user-defined functions.

4) Stored procedures are usually executed as a separate part (EXECUTE statement execution), while functions can be called as a part of the query statement (SELECT call), because functions can return a table object, so it can be placed behind the FROM keyword in the query statement. Stored procedures are not available in SQL statements, but functions can be used.

3. Cursor (Cursor)

3.1 Definition

Query statements may query multiple records and use cursors in stored procedures and functions to read records in the query result set one by one. The use of cursors includes declaring the cursor, opening the cursor, using the cursor and closing the cursor. The cursor must declare the cursor, open the cursor, use the cursor, and close the cursor. The cursor must be declared before the handler and after variables and conditions.

1. Declare cursor

  1. Declare cursor_name cursor for select_statement;  
  2. Cursor_name:Cursor name  
  3. Select_statement:selectContent of statement  
  4. Declare cur_employee cursor for select name,age from employee;  

2. Open the cursor

  1. Open cursor_name  
  2. Open cur_employee;  

3. Use cursors

Mysql uses fetch keyword to use cursor, grammatical form

  1. Fetch cur_name intovar_name[,var_name...];  
  2. Cur_name Represents the name of the cursor  
  3. Var_name Represents that the cursor will be inselectThe information queried by the statement is stored in this parameter. Var_name It must be defined before the cursor is declared.  
  4. Fetch cur_employee intoemp_name,emp_age;  

4. Close the cursor

  1. Close cursor_name;  
  2. Close cur_employee;  

Each cursor should be closed when it is no longer needed, and using the close statement releases all the resources used by the cursor. When a cursor is closed, it cannot be used if it is not reopened. For declared cursors, you do not need to declare them again, but you can open them directly using the open statement.

3.2. Examples of Use

(Copy table test_cur1 data to test_cur2)

  1. CREATE TABLE `test_cur1` (    
  2.   `id` int(11) NOT NULL auto_increment,    
  3.   `type` char(11) default NULL,    
  4.   `order1` char(11) default NULL,    
  5.   PRIMARY KEY  (`id`)    
  6. )   
  7. INSERT INTO `test_cur1` VALUES (1, '145', 'd1');    
  8. INSERT INTO `test_cur1` VALUES (2, '134', '1d');    
  9. INSERT INTO `test_cur1` VALUES (3, '123', '1ad');    
  10. INSERT  INTO `test_cur1` VALUES (4, '121', '1as');  
  11.    
  12. CREATE TABLE `test_cur2` (    
  13.   `id` int(11) NOT NULL auto_increment,    
  14.   `type` char(11) default NULL,    
  15.   `order1` char(11) default NULL,    
  16.   PRIMARY KEY  (`id`)    
  17. )   
Then write the cursor:

  1. create procedure get_cur ()  
  2. BEGIN  
  3.   DECLARE done INT DEFAULT 0;  
  4.   DECLARE ID int(11);  
  5.   DECLARE type char(11);  
  6.   DECLARE order1 char(11);  
  7.   DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;//Define cursor
  8.   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
  9.   //Open the cursor.
  10.   OPEN mycur;  
  11.   //Start the cycle.
  12.   REPEAT  
  13.     FETCH mycur INTO ID,type,order1;//Remove cursor contents to temporary variables.
  14.     IF NOT done THEN  
  15.       INSERT INTO test_cur2 VALUES (ID,type,order1);//Insert into another table
  16.     END IF;    
  17.   UNTIL done END REPEAT;//End the loop when done=1
  18.   //Close the cursor.
  19.   CLOSE mycur;  
  20. END  

Function:

  1. call get_cur()  

Let's look at the data from two tables: this is Table 2.


This is Table 1.

Indicates that the data has been successfully copied

Topics: Stored Procedure SQL Database MySQL

Hot Topics

©2024 Programmer Think