how to retrieve data from variable database names?
delimiter $$
create procedure test()
BEGIN
declare wordpress_db varchar(100);
DECLARE wordpress_cursor_done INTEGER DEFAULT 0;
DECLARE wp_users_done INTEGER DEFAULT 0;
DECLARE wordpress_cursor CURSOR FOR
select SCHEMA_NAME from information_schema.schemata where SCHEMA_NAME like '%wordpress%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET wordpress_cursor_done = 1;
IF NOT EXISTS(select SCHEMA_NAME from information_schema.schemata where SCHEMA_NAME like '%wordpress%') THEN
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = 'No database named wordpress';
END IF;
OPEN wordpress_cursor;
wordpress_cursor_loop:LOOP
FETCH wordpress_cursor INTO wordpress_db;
IF wordpress_cursor_done = 1 THEN
LEAVE wordpress_cursor_loop;
END IF;
IF NOT EXISTS(select table_name from information_schema.tables where table_schema = wordpress_db and table_name like '%wp_users%') THEN
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = 'No table name wp_users';
else
set @get_id = concat('SELECT * FROM', wordpress_db , '.' , 'wp_users');
prepare stmt from @getid;
execute stmt;
deallocate prepare stmt;
END IF;
END LOOP;
CLOSE wordpress_cursor;
END $$
delimiter ;
I want to access wp_users from wordpress_db that is fetched from the cursor.
Subject
Views
Written By
Posted
how to retrieve data from variable database names?
1076
November 24, 2018 06:24PM
495
November 24, 2018 07:30PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.