Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the use of mysql and Oracle cursors". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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

That's all for "the use of mysql and Oracle cursors". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.