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 fromuser
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
- 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. - 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. - 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.
- The first disadvantage is poor portability. Because stored procedures are bound to the database, such as
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;
This work adoptsCC agreement, reprint must indicate the author and the link to this article