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)
 begin 
      Stored procedure body
  end
  Call stored procedure name (parameter list)

example:

DROP PROCEDURE user_procedure;
 create PROCEDURE user_procedure(in x int)
 BEGIN 
         select * from 'user' where id=x;
 END
 --Execute
 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

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

The syntax of direct assignment is:
Set variable name 1 = expression 1;
*Assignment by query result:

DROP PROCEDURE user_procedure_2;
create PROCEDURE user_procedure_2(in x int, out y varchar(10))
BEGIN
declare s varchar(10) ;
select user_name into s from user where id = x;
set y = s;
END
call user_procedure_2(1, [[[[[[[[@a](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347));
select [[[[[[[[@a](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)](https://learnku.com/users/16347)

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
Syntax:

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

Case:

delimoter $$ 
create proceddure exchange（out count int）
begin 
         declare supply_id1 int default 0;
         declare amount int default 0;
         --Cursor ID
         declare blag int default 1;
         --Cursor
         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;
        --Traversal
        read_loop：LOOP
                       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;
end;
$$
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.

view

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

Syntax:

//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

advantage:
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\
Example:\

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

concept

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\

effect

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.

realization

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

trigger

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
  begin
          update test_trigger set count=count+1 where id=1;
end;

database
laravel

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