MySQL Forums
Forum List  »  Stored Procedures

how to retrieve data from variable database names?
Posted by: adheep shetty
Date: November 24, 2018 06:24PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
how to retrieve data from variable database names?
196
November 24, 2018 06:24PM


Sorry, only registered users may post in this forum.

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.