A preface

Mysql cursors and triggers

This set of tutorials

  • MYSQL is introduced (1)
  • MYSQL retrieval (2)
  • MYSQL retrieval (3)
  • MYSQL > insert into MYSQL
  • MYSQL things (5)
  • MYSQL data type (6)
  • MYSQL > alter table table_name;
  • MYSQL view (8)
  • MYSQL stored procedure (9)
  • MYSQL cursor and trigger (10)
  • MYSQL > alter table user permissions
  • Introduction to MYSQLl Architecture (12)
  • MYSQL lock wait and deadlock (13)
  • MYSQLl operating json (14)
  • MYSQL Execution Plan (15)
  • MYSQL index (16)

Public account: Knowledge seeker

Inheriting the spirit of open Source, Spreading technology knowledge;

The cursor

2.1 Concept of cursors

The essence of a cursor is the result set after a query; Cursors are used when we perform a similar operation on the previous or following row of the result set of the query

2.2 Cursor syntax

  • First, you need to define the cursor; Declare cursor name CURSOR for query statement;
  • Second, open the cursor; Open Cursor name
  • The result set of the query, i.e., the cursor, is then retrieved and used to provide variables
  • Finally close the cursor; Close Cursor name

2.3 Using cursors

Ready to watch

CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `detail_name` varchar(255) DEFAULT NULL COMMENT 'Order Details',
  `price` decimal(10.2) DEFAULT NULL COMMENT 'price',
  `oid` int(11) DEFAULT NULL COMMENT 'order id'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='Order List';
Copy the code

Prepared data

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1.'towel'.20.00.1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2.'toothpaste'.15.00.1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3.'cup'.5.00.1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4.'towel'.15.00.2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5.'cup'.15.00.2);

Copy the code

Simply use the cursor

Query the result set of the order detail name with OID 1 as a cursor;

After opening the cursor, fetch the result of each row and assign it to the variable name

CREATE PROCEDURE printName()
BEGIN
	-- Order Name
    declare name varchar(20);
    Create a cursor
    declare cur cursor for select detail_name from oder_detail where oid = '1';
    -- Open the cursor
    open cur;
		fetch cur into name;
		select name;
    -- Close the cursor
    close cur;
END;
Copy the code

Calling stored procedure

call printName;
Copy the code

The print result is as follows, with only one data, indicating that the above method only captures one data in the cursor, and it is the smallest row in the table.

name
------towelCopy the code

Use cursors in loops

Assign the result set with query OID 1 to the cursor; The name and price of the order details are assigned to variables name and detail_price respectively by capturing each line with a cursor. SQLSTATE ‘02000’ appears when the loop cannot continue; If done is set to 1 for true, the loop will end and the loop will break.

drop procedure if exists  printDetail;
CREATE PROCEDURE printDetail()
BEGIN
	-- Order Name
    declare name varchar(20);
		Price -
    declare detail_price decimal(8.2);
    -- End flag variable (false by default)
    declare done boolean default 0;
    Create a cursor
    declare cur cursor for select detail_name,price from oder_detail where oid = '1';
    -- Specifies the return value at the end of the cursor loop
    declare continue HANDLER for SQLSTATE '02000' set done = 1;
    -- Open the cursor
    open cur;
    -- Loop cursor data
    detail_loop:loop
			-- Based on the data the cursor is currently pointing to
			fetch cur into name,detail_price;
			select name , detail_price;
			-- Determines whether the cursor loop is complete
			if done then
					-- Jump out of the cursor loop
					leave detail_loop;    
			end if;
		-- Ends the cursor loop
    end loop;
    -- Close the cursor
    close cur;
END;
Copy the code

Calling stored procedure

Call the stored procedure
call printDetail();
Copy the code

The fly in the eye is that the last line will be repeated, if you want to fine processing or need to customize the flag bit out of the loop;

Three triggers

3.1 The concept of triggers

A trigger is an action that fires when the table changes; It sounds a little abstract, but for example, when you insert data into a table, the table changes, and now you want to check that all the input parameters are lowercase before you insert data, you can use triggers to check that; After the above analysis knows that using a basic trigger, at least the table must change, but also meet a triggered event;

Table change usually refers to add, delete and change, its action can occur before or after the increase, delete and change; Triggering events are stored procedures that we need to write;

  • update (after/ before)
  • insert (after/ before)
  • delete (after/ before)

3.2 Basic syntax of triggers

  • On table name for each row
  • Drop trigger Trigger name;
  • View triggers: show Triggers;

Triggers depend on table creation. There are no triggers without tables, such as views. Temporary tables are not real tables. In general, each table has a limit of triggers, and generally supports a maximum of six different types of triggers. Because the use of trigger will frequently change each row of the table, so it is very bad performance, especially for some large tables with fast update frequency, if set trigger will occupy system resources very much; Generally speaking, triggers are used in small table changes, do not use triggers immediately delete;

3.3 Insert trigger example

Create trigger; Create a trigger getPrice on each row of the oder_detail table and assign the price of the order details to @price whenever data is inserted. You might wonder what NEW is. It is a virtual table that records the rows into which the data is inserted. So we can get every insert in the NEW table;

Insert trigger
CREATE TRIGGER getPrice AFTER INSERT ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;
Copy the code

Detect insert trigger; Insert a data, use the query statement query variable display is 20;

-- Detects insert triggers

INSERT INTO `oder_detail`( `detail_name`, `price`, `oid`) VALUES ( 'washbasin'.20.00.2);

select @price;
Copy the code

Delete trigger;

Drop trigger
drop trigger getPrice;
Copy the code

3.4 Update Trigger Example

Change the trigger after insert to after update as follows.

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;
Copy the code

Modify the price of the previously inserted SQL statement and query the price. At this time, the price is 30; The NEW virtual table stores the data to be updated.

UPDATE `oder_detail` SET `price` = 30.00 WHERE `id` = 6;

select @price;
Copy the code

Delete trigger

Drop trigger
drop trigger getPrice;
Copy the code

Change the update trigger’s NEW table to the OLD table

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT OLD.price INTO @price;
Copy the code

The updated price is 40

UPDATE `oder_detail` SET `price` = 40.00 WHERE `id` = 6;
Copy the code

At this point, the query price is 30, indicating that the OLD table triggers the original data value.

select @price;
Copy the code

OLD stores the original data and NEW stores the data to be updated. The NEW table can be set to change the value, the OLD table is read-only;

3.5 Delete Trigger

Change the update trigger to delete trigger. We omitted begin and end if multiple statements are executed;

CREATE TRIGGER getPrice AFTER delete ON oder_detail FOR EACH ROW
begin 
SELECT OLD.price INTO @price;
end;
Copy the code

Delete the previous SQL data

delete from oder_detail where `id` = 6;
Copy the code

Select * from OLD where id = 40;

select @price;
Copy the code