1. A stored procedure is a set of SQL statements to complete a specific function. In fact, it is a piece of code stored in the database. It can be composed of declarative SQL statements (such as CREATE, UPDATE and SELECT statements) and procedural SQL statements (such as IF THEN... ELSE control structure statement). The created stored procedure is saved in the data dictionary of the database.

Using stored procedures usually has the following advantages:

1) it can enhance the function and flexibility of SQL language;

2) good encapsulation;

3) high performance;

4) reduce network traffic;

5) stored procedure can be used as a security mechanism to ensure the security of database and the integrity of data.

 

2. Create a stored procedure

2.1) DELIMITER command

The delete command temporarily changes the end flag of MySQL statement to other symbols, so that the MySQL server can process all SQL statements in the stored procedure body completely, and then change the end flag of MySQL statement back to the default end flag of MySQL through the delete command, that is, semicolon (;).

Syntax format of the DELIMITER command: DELIMITER$$

　

2.2) create stored procedure

In MySQL, CREATE PROCEDURE statement is used to create a stored procedure. Its common syntax format is: CREATE PROCEDURE sp_name ([proc_parameter [,...) ]]) Routine_body  

"sp_name" is used to specify the name of the stored procedure, which is created in the current database by default;

"Proc? Parameter" is used to specify the parameter list of the stored procedure;

The "routine" represents the body part of the stored procedure, also known as the stored procedure body.

Where the syntax format of the syntax item "proc parameter" is: [in| out| inout] param| name type

IN input parameter: indicates the value passed IN by the caller to the procedure (the value can be literal or variable);
OUT output parameter: indicates the outgoing value from the procedure to the caller (multiple values can be returned) (the outgoing value can only be a variable);
INOUT I / O parameters: both the value passed in from the caller to the process and the value passed out from the process to the caller (the value can only be a variable);

2.3) stored procedure body

Local variables: local variables can be declared in the stored procedure body to store temporary results in the stored procedure body. In MySQL, you can use the DECLARE statement to DECLARE a local variable, and at the same time, you can also assign an initial value to the local variable. The syntax format used is: DECLARE var_name [ ] type [DEFAULT value]

Among them,

1) "var_name" is used to specify the name of the local variable;

2) "type" is used to declare the data type of the local variable;

3) the DEFAULT clause is used to specify a DEFAULT value for a local variable, or NULL if not specified Precautions are as follows:

1) local variables can only be in the BEGIN of the stored procedure body Declared in the END statement block.

2) local variables must be declared at the beginning of the stored procedure body.

3) the scope of a local variable is limited to the BEGIN that declares it END statement block, which cannot be used by statements in other statement blocks.

4) local variables are different from user variables. The difference between them is:

When a local variable is declared, the @ symbol is not used before it. Only its BEGIN can be declared The statement used in the END statement block;

When a user variable is declared, the @ symbol is used before its name, and the declared user variable exists in the entire session.

 

SET statement in MySQL, you can use SET statement to assign values to local variables. The syntax format is: SET var_name=expr [, var_name=expr]

　　  5)  SELECT… Into statement in MySQL, you can use Select The into statement stores the value of the selected column directly in the local variable. The syntax format is: SELECT col_name [ ] INTO var_name[,… ]Table expr where:

"col_name" is used to specify the column name;

"var_name" is used to specify the variable name to be assigned;

"Table expr" represents the FROM clause and subsequent syntax part of the SELECT statement.

Note: SELECT in stored procedure body The result set returned by the INTO statement can only have one row of data.

6) process control statement

In MySQL, two kinds of procedural SQL statements can be used in the stored procedure body to control the statement process: conditional judgment statement and circular statement.

(1) common conditional statements are IF THEN... ELSE statement and CASE statement;

(2) the commonly used LOOP statements are WHILE statement, REPEAT statement and LOOP statement. In addition, the ITERATE statement can be used in a LOOP statement, but it can only appear in the LOOP, REPEAT, and WHILE clauses of the LOOP statement to indicate that the current LOOP is exited and a LOOP is restarted.

eg: 　　

 

select...into...

mysql> delimiter $$
mysql> CREATE PROCEDURE proc_add_stu(
　　 -> IN sNo INTEGER,
　　 -> OUT sid int
　　 -> )

mysql> BEGIN #Stored procedure start
　　 -> insert into student(s_no) values(sNo);
　　 -> SELECT LAST_INSERT_ID() into sid;  #Stores the value of the selected column directly in a local variable
　　 -> END $$ #End of stored procedure

mysql> delimiter;　　#Returns the end of a statement to a semicolon

mysql> call pro_add_stu('0001'); 

 

in input parameter (default, can be omitted)

mysql> delimiter $$
mysql> create procedure in_proce(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=0; #Local variable assignment (between begin... And end)
    ->    select P_in;
    -> end$$
mysql> delimiter ;
 
mysql> set @p_in=1; #Global variable @ P inassignment
 
mysql> call in_param(@p_in); #Pass the value of the global variable @ P InAs a parameter to the local variable p in
+------+
| p_in |
+------+
|    1 |
+------+
 
+------+
| P_in |
+------+
|    0 |
+------+
 
mysql> select @p_in; #Output the result of global variable @ P in
+-------+
| @p_in |
+-------+
|     1 |
+-------+

As can be seen above, P ﹣ in is modified in the stored procedure, but does not affect the value of @ P ﹣ ID, because the former is a local variable and the latter is a global variable.

 

out output parameters

mysql> delimiter //
mysql> create procedure out_proce(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_proce(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#because out It outputs parameters to the caller and does not receive the input parameters, so in the stored procedure p_out by null
+-------+
| p_out |
+-------+
|     2 |
+-------+
 
mysql> select @p_out; #Output global variable (user variable) results
+--------+
| @p_out |
+--------+
|      2 |
+--------+
#The out Pro stored procedure is called to output parameters and change the value of the P out variable

 

inout input parameters (use as little as possible)

mysql> delimiter $$
mysql> create procedure inout_proce(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_proce(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#The inout param stored procedure is called, the input parameters are accepted, and the output parameters are also changed

 

Variable scope

Internal variables have a higher priority in their scope when executed to end. When a variable is in use, the internal variable disappears. At this time, the variable is no longer visible. The declared variable can no longer be found outside the stored procedure. However, you can save its value by out parameter or assigning its value to the session variable.

mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default 'outer';  
     -> begin 
     -> declare x1 varchar(5) default 'inner';  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //
mysql > DELIMITER ;

 

Conditional statement

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc2(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;  
     -> //  
mysql > DELIMITER ;

 

Loop statement

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2.4) cursor

A cursor is a result set retrieved by a SELECT statement. That is, the cursor can traverse the returned multiline results.

In MySQL, the specific steps for using cursors are as follows:

(1) declare cursor "name cursor for select" statement,

"Cursor" is used to specify the name of the cursor to be created, with the same naming rules as the table name;

"SELECT statement" is used to specify a SELECT statement, which will return one or more rows of data. Note that the SELECT statement here cannot have an INTO clause.

(2) open cursor open cursor name, where cursor name is used to specify the cursor to open.

(3) read the data fetch cursor name into var name [, VAR name] Where "cursor" is used to specify the opened cursor; the syntax item "var" is used to specify the variable name where the data is stored.

(4) close cursor. The syntax item "cursor name" is used for the cursor to be closed.

(5) when using cursors, you should pay attention to the following points:

1) cursors can only be used in stored procedures or stored functions, and cannot be used in query operations alone.

2) multiple cursors can be defined in a stored procedure or a stored function, but in a BEGIN The name of each cursor in the END statement block must be unique.

3) the cursor is not a SELECT statement, but a result set retrieved by the SELECT statement Module 4 calls the stored procedure CALL sp_name[parameter [ ]]CALL sp_name [()] in this syntax format:

The syntax item "SP" is used to specify the name of the stored procedure being called. If you want to call a stored procedure for a specific database, you need to precede it with the name of the database.

The syntax item "parameter" is used to specify the parameters to use when calling the stored procedure. The number of parameters in the call statement must be equal to the number of parameters in the stored procedure.

When calling a stored procedure with no parameters, the use of the call sp ﹣ name() statement is the same as the use of the call sp ﹣ name statement.

 eg:

create procedure p1()
begin
    declare id int;
    declare name varchar(15);
    -- declare cursor
    declare mc cursor for select * from class;
    -- Open cursor
    open mc;
    -- Get results
    fetch mc into id,name;
    -- This is to show the results
    select id,name;
    -- Close cursor
    close mc;
    
end;

 

 

3. Storage function

1. Difference between storage function and stored procedure:

(1) a storage function cannot have an output parameter because the storage function itself is an output parameter; a stored procedure can have an output parameter.

(2) you can CALL the storage function directly, and you don't need to use the CALL statement; you need to use the CALL statement to CALL the stored procedure.

(3) the storage function must contain a RETURN statement, which is not allowed in the stored procedure.

 

2. Create a storage function CREATE FUNCTION sp_name([func_parameter [,...) ]]) returns type routine? Body where the syntax format of the syntax item "func? Parameter" is: param? Name type

In this syntax format:

(1) the syntax item "sp_name" is used to specify the name of the storage function. Note that the storage function cannot have the same name as the stored procedure.

(2) the syntax item "func_parameter" is used to specify the parameters of the storage function. The parameters here only have the name and type, and the keywords "IN", "OUT" and "INOUT" cannot be specified.

(3) the RETURNS clause is used to declare the data type that stores the return value of the function, where type specifies the data type of the return value.

(4) the syntax item "routine" is used to specify the body part of the storage function, also known as the body of the storage function All SQL statements used in stored procedures are also applicable to stored functions, including local variables, SET statements, process control statements, cursors, etc. as described earlier. However, the body of the storage function must also contain a RETURN value statement, where value is used to specify the RETURN value of the storage function.

 eg:

#Delete existing storage function
DROP FUNCTION IF EXISTS func_stu;

#Create storage function (declaration return type is varChar(50))
CREATE FUNCTION func_stu(in_id INT) RETURNS VARCHAR(50)
BEGIN
    DECLARE o_name VARCHAR(50); #Declare local variables
    SELECT name INTO o_name FROM tb_stu WHERE id = in_id; #TB? Stu refers to the database created in advance
    RETURN o_name;
END;

 

 

3. Call storage function

After the storage function is created successfully, it can be called with the keyword SELECT just like the system built-in function. The syntax format is: SELECT sp_name([func_parameter [,...) ]]

 eg:

SELECT func_stu(1);

 

4. Delete storage function

In MySQL, you can use the DROP FUNCTION statement. Syntax format: DROP FUNCTION[IF EXISTS] sp_name, where the syntax item "sp_name" specifies the name of the storage function to be deleted.

eg:

DROP FUNCTION IF EXISTS func_stu;

 

5. Modify the storage function

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

 

Thank you for reading, if you need to reprint, please indicate the source, thank you! https://www.cnblogs.com/huyangshu-fs/p/11669708.html