mysql begin and_MySQL cursor and trigger

One 、 The cursor

Definition ： Is a store in MySQL Database query on server , It's a kind of quilt select Statement to retrieve the result set .

effect ： It is convenient to move forward or backward one or more lines in the retrieved result set .

PS： Cursors are mainly used for interactive applications ;MySQL Cursors in can only be used for stored procedures ( And the function ).

1、 Create cursors

Cursor use declare Sentence creation ;declare Named cursor , And define the corresponding select sentence , Bring... As needed where And other clauses ; for example ：

create procedureprocessorders()begin

declare ordernumbers CURSOR

for

select order_num fromorders;end;

In this stored procedure ,declare Defines and names the cursor ordernumbers, After the stored procedure is processed , The cursor disappears ( Limited to stored procedures ).

2、 Open and close cursors

After defining the cursor , You can turn it on , use open cursor Statement to open , for example ：open ordernumbers;

Processing open Statement , Store retrieved data for browsing and scrolling ;

When cursor processing is complete , use close Statement closing , for example ：close ordernumbers;

close Release all internal memory and resources used by the cursor , So every cursor should be closed when it is not needed .

PS：

When the cursor is closed , If it's not reopened , Cannot be used ; But if the cursor has been declared , There is no need to declare again , use open Open it and use it .

If you don't know if the cursor is closed ,MySQL Will arrive at end Statement to automatically close the cursor .

3、 Use cursor data

When the cursor is opened , Use fetch Statement to access each line of it separately ;fetch Specify what data to retrieve ( Required columns ), Where the retrieved data is stored , Also move forward the inner row pointer in the cursor , Make the next fetch Statement to retrieve the next line ( Do not read repeatedly ).

Retrieve the first line of data , for example ：

create procedureprocessorders()begin

--declare local variables

declare o int;--declare the cursor

declare ordernumbers cursor for select order_num fromorders;--open the cursor

openordernumbers;--get order number

fetch ordernumbers intoo;--close the cursor

closeordernumbers;end;

In this statement fetch To retrieve the current row order_num Column ( Automatically start with the first line ) To a place called o In the local declaration variable of ; Do nothing with the retrieved data .

Loop through the data , From the first line to the last line , as follows ：

create procedure processorders() --Create stored procedure

begin

--declare local variables

declare done boolean default 0;declare 0 int;--declare the cursor

declare ordernumbers cursor

for select order_num from orders; --Result set

--declare continue handler

declare continue handler for sqlstate '02000' set done=1; --ad locum ,done Set to true at the end

--open the cursor

openordernumbers;--loop through all rows

repeat--get order number

fetch ordernumbers intoo;--end of loop

until done endrepeat;--close the cursor

closeordernumbers;end;

As in the previous example , use fetch Retrieve current order_num To the statement of o variable . The difference lies in ： In this case fetch Is in repeat Inside , So it's repeated until done It's true ( from until done end repeat; Regulations ).

In statement (declare continue handler for sqlstate '02000' set done=1;) in , Defined CONTINUE HANDLER, It is the code that is executed when the condition occurs . When SQLSTATE '02000' When it appears ,SET done=1.

SQLSTATE '02000' Is a condition not found , When REPEAT When cannot continue because there are no more lines to cycle , This condition appears .done Set to true .

PS：

use declare Statement must be defined before any cursor or handle is defined , And the handle must be defined after the cursor .

MySQL Loop statements are also supported , It can be used to repeatedly execute code , Until you use leave Statement to exit manually ; Usually repeat The syntax of the statement makes it more suitable for looping cursors .

4、 Several notes on using cursors

1、 Before using the cursor , You must declare ( Definition ) it ; This process doesn't actually retrieve data , Just define what to use select sentence ;

2、 Once declared , You must open the cursor for use ( This process uses the select Statement to actually retrieve the data );

3、 For data filled cursors , Remove as needed ( retrieval ) All walks of life ;

4、 When ending cursor use , Cursor must be closed .

Two 、 trigger

MySQL Statements are executed when needed , The same goes for stored procedures , If you want a statement ( Or some statements ) Automatically when an event occurs , This requires triggers .

The trigger is MySQL Automatically executed in response to any of the following statements MySQL sentence ( Or located begin and end A set of statements between statements )：delete;insert;update. other MySQL Statement does not support triggers .

1、 Create trigger

Information needed ： Unique trigger name ; Trigger associated table ; Trigger should respond to the activity (delete、insert or update); When the trigger executes ( Before or after processing ).

PS：MySQL in , The trigger name must be unique in each table , But it's not unique in every database . namely ： Two tables in the same database can have triggers with the same name , But other DBMS It's not allowed to , So it's best to use a unique trigger name within the scope of the database .

Trigger use create teigger Sentence creation , Such as ：

create trigger newproduct after insert onproductsfor each row select 'product added';

establish newproduct trigger , given after insert, So this trigger is in insert Statement executed after successful execution ; It also specifies for each row, So the code executes for each insert line .

PS：

1、 Only tables support triggers , View does not support ( Temporary tables also don't support ).

2、 Triggers are defined per table per event per time , Only one trigger per event per table is allowed at a time ; So each table supports at most 6 Trigger ( Every one of them insert、update and delete Before and after ), A single trigger cannot be associated with multiple events or tables .

3、 If before Trigger failed , be MySQL The requested operation will not be performed ; Besides , If before The trigger or statement itself failed ,MySQL Will not perform after trigger ( If any ).

2、 Delete trigger

Delete trigger with drop trigger sentence , Such as ：

drop trigger newproduct;

PS： Triggers cannot be updated or overridden ; To modify a trigger , It must be deleted before it is built .

3、 Use triggers

1、inserttrigger

Use insert trigger , There are three things you need to know ：

1、 stay insert Trigger code inside , You can use a reference called new The virtual table of , Access the inserted row ;

2、 stay before insert Inside the trigger ,new Values in can also be updated ( Allow changes to inserted values );

3、 about auto increment Column ,new stay insert Include... Before execution 0, stay insert Include new auto generated values after execution ;

about AUTO_INCREMENT Automatically assigned values , How to determine the newly generated value , Examples are as follows ：

create trigger neworder after insert onordersfor each row select new.order_num;

Create a file called neworder The trigger of , according to after insert on order perform ; Insert a new data into orders Table time ,MySQL Generate a new order number and save it to order_num in ;

Trigger from new.order_num Take this value and return it ; This trigger must follow after insert perform , Because in before insert Before statement execution , new order_num It hasn't been generated yet .

PS： Will usually before For data validation and purification ( The purpose is to ensure that the data inserted into the table is really the required data ). This rule also applies to update trigger .

2、deletetrigger

Use delete trigger , You need to know two things ：

1、 stay delete Trigger code inside , You can quote a name as old The virtual table of , Access to deleted lines ;

2、old The values in are all read-only , Can't update ;

Use old Save the rows to be deleted into an archive table ：

create trigger deleteorder before delete onordersforeach rowbegin

insert intoarchive_orders(order_num,order_date,cust_id)values(OLD_order_num, OLD_order_date, OLD_cust_id);end;

Execute this trigger before any data is deleted ; It USES insert Statement will old The value in ( Data to be deleted ) Save to a file named archive_orders In the archive form of ;

PS： Use begin end The advantage of blocks is that triggers can hold multiple SQL sentence .

3、updatetrigger

Use update trigger , There are three things you need to know ：

1、 stay update Trigger code inside , You can quote a name as old Before virtual table access (update Statement before ) Value , Quote a name as new Virtual table access new updated values ;

2、 stay before update Trigger ,new Values in may also be updated ( Allow changes to be used for update Value in statement );

3、old The values in are all read-only , Can't update ;

The following example , It ensures that state abbreviations are always capitalized ：

create trigger updateevendor before update onvendorsfor each row set new.vend_state = upper(new.vend_state);

Every time you update a row ,new.vend_state All the values in are upper(new.vend_state) Replace .

4、 Something you need to know about triggers ：

1、 Creating triggers may require special security access , But trigger execution is automatic . If insert、update or delete Statement can execute , Then the corresponding trigger can also execute ;

2、 Triggers should be used to ensure data consistency ( Case write 、 Format, etc. ); The advantage is that it always does this , And it's transparent , Nothing to do with client applications ;

3、 A very interesting use of triggers is to create audit trails . Use triggers , It's very easy to log changes to another table ;

4、MySQL Trigger does not support call sentence , That is, a stored procedure cannot be called from within a trigger . The required stored procedure code needs to be copied into the trigger .