Mysql-views, stored procedures, triggers, and mysql-Views

Mysql-views, stored procedures, triggers, and mysql-Views

I. VIEW

A view is a virtual table and does not store any data. Only the running time contains the dynamically retrieved data.

Eg: SELECT sid, name, sex, s_num FROM student, school WHERE sid = 2 AND student. sid = scholl. sid;

This simple query involves two tables. Therefore, anyone who needs this data must be familiar with the two tables and their relationships. To retrieve other student information, you must modify the WHERE clause. If you can wrap the entire Query into a virtual table studentinfo, you can get the data as follows:

Eg: SELECT sid, name, sex, s_num FROM studentinfo WHERE sid = 2;

You can use views to reuse SQL statements. Users do not have to know the details. It also protects raw data.

Views also have some limitations, such as the inability to index and the absence of associated triggers. The name must be unique.

Create View:

Eg: create view studentinfo as select sid name, sex, s_num FROM student, school WHERE student. sid = school. sid;

Views can also be updated, but only under specific circumstances. If a view has these definitions, it cannot be updated: group, join, subquery, and aggregate function DISTINCT and calculation column.

Ii. Stored Procedure

When the business logic to be processed is complex, you can write SQL statements one by one, and all the details and data integrity to be processed must be taken into account. You can create a stored procedure instead. It is like a batch processing that contains a set of one or more pre-defined SQL statements. However, its role is not limited to this.

Create:

Eg: creat procedure prostudent ()

BEGIN

SELECT Max (score) AS scoremax FROM student;

END;

Delete:

Eg: drop procedure prostudent;

Parameters used:

Eg: create procedure prostudent (OUT scorehigh DECIMAL (8, 2), OUT scorelow DECIMAL (8, 2 ))

BEGIN

SELECT Max (score) INTO scorehigh FROM student;

SELECT Min (score) INTO scorelow FROM student;

END;

Run:

Eg: CALL prostudent (@ scorelow, @ scorehigh );

SELECT @ scorehigh, @ scorelow;

The stored procedure named prostudent is executed, and the highest and lowest scores are returned.

IN addition, stored procedures can also write comments, define temporary variables, input parameters IN, and process control statements.

Show create procedure *****; you can view the statements used to CREATE a stored PROCEDURE.

Show procedure status; you can view when and who created the stored PROCEDURE.

Iii. cursor

The SELECT statement returns a result set, which may be multiple flights that meet the conditions. What should we do if we want to process each row of the result set or have special requirements such as the first, last, and previous rows? Here, the cursor is used. The cursor in mysql can only be used for Stored Procedures, which is different from other databases.

To use a CURSOR, you must first define DECLARE *** cursor for, then OPEN ***, use, and CLOSE ***. The lifecycle of a cursor is only stored in the stored procedure. If you do not close it, it is automatically closed when the stored procedure ends.

After the cursor is opened, you can use FETCH to retrieve a row and point it to the next row internally. When FETCH is executed again, the next row is taken.

For example, we need to add scores of all students whose sid is greater than 3.

Eg: DELIMITER //

Create procedure sumofscore (OUT sum INT)

BEGIN

DECLARE done booleean default 0;

DELCARE tmp INT;

DECLARE s int default 0;

DECLARE yb cursor for select score FROM student WHERE sid> 3;

Declare continue handler for sqlstate '000000' SET done = 1;

OPEN yb;

REPEAT

FETCH yb INTO tmp;

SET s = s + tmp;

UNTIL done end repeat;

CLOSE yb;

SELECT s INTO sum;

END

//

DELIMITER;

DELIMITER redefines the end character of mysql. 02000 is an error code not found in the data. It is used to determine whether to traverse all data.

4. triggers

If you want some statements to be automatically executed when a specific event occurs under certain requirements, a trigger is used. Mysql triggers can only respond to delete, insert, and update statements.

Create a trigger:

Eg: creste trigger newstudent after insert on student for each row select new. sid INTO @ s;

The trigger name is newstudent, and INSERT specifies that the response event is an INSERT operation. AFTER/BEFORE specifies whether to trigger BEFORE or AFTER the event is executed. For each row specifies that each row to be inserted is operated. Therefore, each row to be inserted passes the sid of this ROW to the variable s. Only one trigger is allowed for each event in each table. Therefore, each table can have up to six triggers. One trigger can only respond to one event.

Delete trigger:

Eg: drop trigger newstudent;

Trigger:

In INSERTZHONG, You can reference a virtual table named NEW to access the inserted row. In befroe insert, you can also reference NEW or even update data to change the content of the inserted data.

In the DELETE trigger, you can reference a virtual table named OLD to access the deleted row.

Usage of stored procedures, triggers, and views in mysql

Create a stored procedure

Create procedure, Create function

Below are their formats:
Create proceduresp_Name ([proc_parameter])
Routine_body

Here, the parameter type can be in out inoutt, which means the same as the word, and IN indicates the passed parameter,
"OUT" indicates the outgoing parameters, and "INOUT" indicates the parameters passed in but finally returned.
Create functionsp_Name ([func_parameter])
Returns type

Routine_body
The Returns type specifies the returned type. The given type is the same as the returned type. Otherwise, an error is returned.

The following are two simple examples:

1. display the current Mysql version

Execution result
Mysql> use welefen;
Database changed
Mysql> delimiter // # define // as the end mark
Mysql> create procedure getversion (out param1 varchar (50) # param1 is the outgoing Parameter
-> Begin
-> Select version () into param1; # assign version information to param1
-> End
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> call getversion (@ a); # call the getversion () Stored Procedure
-> //
Query OK, 0 rows affected (0.00 sec)

Mysql> select @;
-> //
+ -------------------------- +
| @ A |
+ -------------------------- +
| 5.1.14-beta-community-nt |
+ -------------------------- +
1 row in set (0.00 sec)

2. display "hello world"

Execution result
Mysql> delimiter //
Mysql> create function display (w varchar (20) returns varchar (50)
-> Begin
-> Return concat ('hello', w );
-> End
... The remaining full text>

What are the advantages of SQL view stored procedure triggers?

Advantages of a view:

Improve data security and prevent users from seeing a field in the table. For example, if you only grant them the view execution permission and do not grant the table execution permission, they will not be able to view all the data.

You can also create a view that contains two tables. During update, you only need to specify the ID instead of the table from which the view comes from. The data in the corresponding table is automatically updated.

Advantages of stored procedures:

Including all the advantages of the view, it also allows people who do not know the database to also use the database, there is also convenient programming, such as I am responsible for front-end programming, you are responsible for writing the stored procedure, I don't have to worry about how you write it, but I only answer the call at last. We have a clear division of labor, and I don't need to understand what you know, this provides a good platform for people who do not know languages to work with different professionals. Improve development efficiency.

Advantages of triggers:

Ensure data correctness and logic. For example, if a data entry is added to the order table, a product is reduced in the inventory table.
For example, when you delete table A, you can determine whether it is A user in the Admin group. If not, an error message is displayed and the transaction is rolled back.

That's all I know... Hey.