MySQL — stored procedures and views


Stored procedures and views, triggers

stored procedure

1. Application scenario

When calculating, analyzing and summarizing data, especially when getting data from other companies and converting it into the data and format required by their own system

2. Concept

Stored procedures and functions can be understood as a collection of SQL statements, which are compiled in advance and stored in the database

Syntax for creating a stored procedure:

Name of the create procedure stored procedure (parameter list)
      Stored procedure body
  Call stored procedure name (parameter list)


DROP PROCEDURE user_procedure;
 create PROCEDURE user_procedure(in x int)
         select * from 'user' where id=x;
 call user_procedure(1);

Stored procedure parameter type
one   From the above process, we know that a stored procedure has parameter types. Its types can take three values: in, out and inout\
two   Their meanings are as follows:
(1) In means only for input.
(2) Out means only for output.
(3) Inout can be used as input or output.

3. Understanding of stored procedures

  1. Calling stored procedures has the same effect as directly executing SQL statements, but one advantage of stored procedures is that the processing logic is encapsulated on the database side.
  2. When we call a stored procedure, we don’t need to understand the processing logic. Once the processing logic changes, we just need to modify the stored procedure and the procedure that calls it
    Sequence has no effect at all.
  3. Calling stored procedures and functions can simplify a lot of work of application developers, reduce the transmission of data between database and application server, and improve the efficiency of data processing

4. Use variables in stored procedures

one   Variables can be used in stored procedures. We can define a local variable through declare. The scope of the variable is only in the begin… End block\
two   The definition of a variable must be written at the beginning of the matching statement and before any other statement. We can declare multiple variables of the same type at one time, and we can also use default to give default values\
three   The syntax for defining a variable is:\
Declare variable name 1 [, variable name 2…] variable type [default default]
4. The type of variable is the type supported by mysql. It can be assigned directly or through query

5. Data types in stored procedures:

  • Value type: int, float, double, decimal
    . date type: timestamp, date, year
    . string: char, varchar, text

5. Cursor

Understanding: that is, the cursor is used to cycle the result set in the stored procedure. The basic steps of using the cursor are: declaration, opening, value taking and closing

DECLARE test_ Cursor cursor for result set// declare cursor 
OPEN test_ cursor;  // open
CLOSE test_ cursor; // close
Declare continue handler for not found // if no data is found in the result set, the result will be displayed automatically


delimoter $$ 
create proceddure exchange(out count int)
         declare supply_id1 int default 0;
         declare amount int default 0;
         --Cursor ID
         declare blag int default 1;
         declare order_cursor cursor for select supply_id,amount from order_group;
         --Not found exception handling, exit
         declear continue handler for not found set blag=0;
        set count =0;
        --Open cursor
        open order_cursor;
                       fetch order_cursor into supply_id1,amount1;
                       if blag = 0 then
                                  leave read_loop;
                      end if;
                      if supply_id1 = 1 then 
                                set count = count +amount1;
                     end if;
         end loop  read_loop;
delimiter ;
call exchang(@count);
select @count;

6. Advantages and disadvantages of stored procedures

  • advantage
    one   The first advantage is fast execution. Because every SQL statement needs to be verified\
    Compiled and then run, but the stored procedures are directly compiled and run directly\
    Just run\
    two   The second advantage is to reduce network traffic. We transfer a stored procedure more than we do\
    The overhead of typing a large number of SQL statements is much smaller\
    three   The third advantage is to improve system security. Because stored procedures can use permission control\
    And parameterized stored procedures can effectively prevent SQL injection attacks. Guaranteed\
    Its security\
    four   The fourth advantage is the reduced coupling. When our table structure is adjusted or changed\
    After that, we can modify the corresponding stored procedure of our application to a certain extent\
    There are fewer changes in the\
    44 / 123\
    five   The fifth advantage is strong reusability. Because after we write a stored procedure, we can\
    It only needs a name for one call, that is, “write once, call anywhere”, and\
    The use of stored procedures can also enhance the modularity of programs.

  • shortcoming

    1. The first disadvantage is poor portability. Because stored procedures are bound to the database, such as
      If we want to change the database and other operations, many places may need to be changed.
    2. The second disadvantage is that it is inconvenient to modify. Because for stored procedures, we do not
      It can debug particularly effectively. Some of its bugs may be found later, which increases the response time
      Danger of using.
    3. The third disadvantage is that the advantages are not obvious and redundant functions. For small web applications,
      If we use statement caching, we find that the cost of compiling SQL is not large, but we use storage
      However, the storage process needs to check the overhead of permissions, and these redundant functions will be reduced to a certain extent
      Drag performance.


1.1 concept

The view can be understood as a virtual table. It only stores a table structure. There is no real data. The data is generated dynamically during the query process; Using views does not achieve optimization, but simplifies query statements

1.2 view operation


//Create view
create VIEW user_view as select id,user_name,email from `user`;
//View each column of the view
desc user_view;
//View the syntax for creating views
show create view user_view;
//Query view
select * from user_view;

1.3 advantages and disadvantages of view

1. Simplified SQL\
2. \ it is more secure and can be restricted. For example, you can only see the view but not the source data\
3. \ reduce the coupling and modify the original table. You only need to modify the view without updating the code\
Disadvantages: if the table structure is modified, the view needs to be modified manually

1.4 view IUD

Tables can update data. The update here refers to “add, delete and modify”, but for viewsnot always
The following are the cases where the view is not updatable\
one   Contains aggregate functions, distinct, group by, having, union, union all\
two   Constant view\
three   Select contains subqueries\
four   Contains connection operations\
five   From a view that cannot be updated\
six   The subquery of the where clause references the table in the from clause\
Sometimes we find that data can be inserted into the view, but it does not meet our needs, so we need to use the with check option\

DROP VIEW user_view_2;
create view user_view_2 as select id,user_name,password,email,status from `user` where status = 0  with check option;

It can be understood here that the function of with check option is to add a check function, that is, the check function, that is, the inserted data must meet the conditions of the view before it can be operated.

1.4 materialized view


The materialized view is relative to the view, but there is actually no relationship between the two. Like Java / JavaScript, the materialized view can understand that the achievement is to create a separate statistical table\


1. Materialized views can help speed up queries that rely heavily on some aggregate results\
2. If the insertion speed is not a problem, this function can help reduce the read load on the system.


According to the needs of the project (real-time data)
To update data regularly, = = > > using stored procedures has less overhead and larger error\

Updating data in real time, = = > > using triggers will affect the performance of database write operations, with high overhead


Create trigger

trigger    Similar to frame events\
Create trigger [trigger name] [trigger execution time] [action point of execution] on [table name] for each row [function or action]

Trigger execution time: before, after\
Action points executed: insert, update, delete\
Function: begin end\
Or action: update, insert\

2.1 case:

create trigger login_trigger_before
          before insert on login
          for each row
          update test_trigger set count=count+1 where id=1;

This work adoptsCC agreement, reprint must indicate the author and the link to this article