Methods of using mysql and Oracle cursors

The following content mainly brings you the methods of using mysql and Oracle cursors. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has certain experience sharing value. I hope to bring help to the majority of readers.

Use cursors to cycle through different tables based on the data in one table

In mysql

DELIMITER $$

DROP PROCEDURE IF EXISTS zy.jk_jkzl_political_location_pro $$

CREATE PROCEDURE zy.jk_jkzl_political_location_pro ()

BEGIN

DECLARE no_more_pro INT DEFAULT 0

DECLARE num VARCHAR (20)

# one declare a cursor

DECLARE cursor_create_user CURSOR FOR SELECT area_code FROM zy.jk_jkzl_political_location

# II declare a message that handles an exception

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_pro=1

# 3. Open the cursor

OPEN cursor_create_user

# 4 assign values to cursors

FETCH NEXT FROM cursor_create_user INTO num

# 5. Match the information of repeated cursor calls

REPEAT

SET @ sql=CONCAT ('create table zy.tf_f_user_p',num,' (user_name varchar (20), user_state int) engine=innodb default charset=utf8')

SELECT NOW ()

PREPARE create_sql FROM @ sql

EXECUTE create_sql

DEALLOCATE PREPARE create_sql

FETCH NEXT FROM cursor_create_user INTO num; # must be marked, otherwise the loop fails

UNTIL no_more_pro=1 # calls all cursor data

END REPEAT; # turns off repeated calls

CLOSE cursor_create_user; # close cursors

END

Testing of Oracle:

DELIMITER $$

DROP PROCEDURE IF EXISTS zy.jk_jkzl_political_location_pro $$

CREATE PROCEDURE zy.jk_jkzl_political_location_pro ()

BEGIN

DECLARE no_more_pro INT DEFAULT 0

DECLARE num VARCHAR (20)

# one declare a cursor

DECLARE cursor_create_user CURSOR FOR SELECT area_code FROM zy.jk_jkzl_political_location

# II declare a message that handles an exception

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_pro=1

# 3. Open the cursor

OPEN cursor_create_user

# 4 assign values to cursors

FETCH NEXT FROM cursor_create_user INTO num

# 5. Match the information of repeated cursor calls

REPEAT

SET @ sql=CONCAT ('create table zy.tf_f_user_p',num,' (user_name varchar (20), user_state int) engine=innodb default charset=utf8')

SELECT NOW ()

PREPARE create_sql FROM @ sql

EXECUTE create_sql

DEALLOCATE PREPARE create_sql

FETCH NEXT FROM cursor_create_user INTO num

UNTIL no_more_pro=1 # calls all cursor data

END REPEAT; # turns off repeated calls

CLOSE cursor_create_user; # close cursors

END

For the above methods of using mysql and Oracle cursors, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.