The usage of mysql and Oracle cursors
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!
