Mysql-views, stored procedures, and triggers

Mysql-views, stored procedures, and triggers

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.