How to use cursor triggers in MySQL

cursor

The set of rows returned by the select query is called a result set. The rows in the result set are retrieved based on the SQL statement you entered. If you do not use a cursor, you will not be able to get the first row, the first ten rows, or the next row.

The following are some common cursor phenomena and characteristics

• Ability to mark a cursor as read-only, which means data can be read but not updated or deleted
• Ability to control the directional operations that can be performed (forward, backward, first, last, absolute and relative positions, etc.)
• Ability to mark some actions as editable and others as non-editable
• Can be scoped, making the cursor accessible to the specific request that created it or to all requests
• Cursor declarations must appear before handler declarations and after
• variable and condition declarations.
• It should be noted that when a cursor is opened, the cursor does not point to the first record, but to the front of the first record.

Here are the steps to use cursor

• Declare a cursor – you have not yet started retrieving data in this process
• Opens a cursor for use
• Retrieving rows
• Close the cursor and release the cursor

DECLARE cs CURSOR 
FOR 
SELECT *
FROM customers
WHERE cust_email IS NULL;

I thought everything was fine, but it gave me an error

There is nothing wrong with the syntax.

Try adding @ in front of cs, and the error message still appears when running.

Some people say that cursors can only be used in stored procedures. Is that true?

Now let's try using cursors in stored procedures

CREATE PROCEDURE procedure1
()
BEGIN
	DECLARE cur1 CURSOR 
	FOR 
	SELECT * 
	FROM customers 
	WHERE cust_id IS NULL tianyoutianyou;
END

Still error

Change again

delimiter //
CREATE PROCEDURE procedure1
()
BEGIN
	DECLARE cur1 CURSOR 
	FOR 
	SELECT * 
	FROM customers 
	WHERE cust_id IS NULL;
END

This time we successfully declared the cursor

Is it because we changed the delimiter that we successfully created the cursor?

Let's try again

CREATE PROCEDURE procedure1()
DECLARE cur1 CURSOR 
FOR 
SELECT * 
FROM customers 
WHERE cust_id IS NULL;

This is also wrong. Let's try to see if the cursor can be successfully created without using a stored procedure.

delimiter //
DECLARE cur2 CURSOR
FOR 
SELECT *
FROM orders ;

Or maybe this

delimiter //
DECLARE cur2 CURSOR
FOR 
SELECT *
FROM orders //

These two types also have 8 lines

After trying so many times, the only successful statement is the following

delimiter //
CREATE PROCEDURE procedure1()
BEGIN 
	DECLARE cur1 CURSOR 
	FOR 
	SELECT * 
	FROM customers 
	WHERE cust_id IS NULL;
END

Next we use the cursor to retrieve data

Unfortunately, MySQL does not have a data type like Oracle's %ROWTYPE. Let's try to use a cursor to retrieve a certain type of data in the customers table.

trigger

A trigger is a specific stored procedure that is automatically executed when a specific activity occurs in the database. Generally speaking, constraints are processed faster than triggers, so constraints should be used whenever possible.

This is the end of this article about how to use cursor triggers in MySQL. For more relevant MySQL cursor trigger content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!