Sunday, September 8, 2013

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


2 comments:

  1. Thank you. What if my cursor select query itself is dynamic? Does MySql support this functionality?

    ReplyDelete
  2. Please check below link ,... we can open cursor for dynamic query :-D
    http://j4info.blogspot.in/2014/06/dynamic-cursor-in-mysql.html

    ReplyDelete

web stats