Create procedure, Cursor example , execute immediate

Create procedure in mysql,  Cursor example in mysql, execute immediate in mysql

cursor/procedure example

DELIMITER ##

CREATE PROCEDURE cursorexam()

BEGIN

    DECLARE vempno BIGINT; 

    DECLARE vsal BIGINT;

    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR SELECT empno ,sal FROM emp;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP

    FETCH cur INTO vempno,vsal;

        IF done THEN

            LEAVE read_loop;

        END IF;

        INSERT INTO empchart (empno, sal, pcat) VALUES (vempno, vsal, 1);

    END LOOP;

    CLOSE cur;

END;##

Execute immediate in mysql

DELIMITER $$

CREATE PROCEDURE Exec_imme_ex()
BEGIN

DECLARE tName TEXT;
DECLARE tSchema TEXT;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
    select table_name,table_Schema from information_schema.tables where table_schema='scott';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
FETCH cur INTO tName,tSchema;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SET @s = CONCAT('insert into tab values (''',tName, ''',''', tSchema, ''')');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END LOOP;
CLOSE cur;
deallocate prepare stmt;
END
$$

DELIMITER ;



continue to post:

Dynamic Cursor in mysql