MySQL Forums
Forum List  »  Stored Procedures

Loop Through Cursor
Posted by: David Martin
Date: November 14, 2015 08:14AM

I need to loop through cursor, build a ';' delimiterd concatenated string, then periodically insert the concatenated string to table column.

This is as far as I got:

CREATE DEFINER=`root`@`localhost` PROCEDURE `build_email_list`()
BEGIN

DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_email varchar(500) DEFAULT "";
DECLARE email_list varchar(5000) DEFAULT "";
DECLARE intRowCtr integer DEFAULT 0;
DECLARE intRowCtr1 integer DEFAULT 0;

-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT SendTo FROM test.tblabc;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

OPEN email_cursor;


Source table tblabc SendTo column:
Build Source cursor from the SendTo column:
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R

Destination table sendtonew :
Desired result

SendTo BCC

A B;C;D;E
F G;H;I;J
K L;M;N;O
P Q;R

Loop thru cursor

Set the row 1 value to a variable named v_sendto
Concatonate the next 4 row values to variable named v_bcc
Insert Into sendtonew(sendto,bcc) VALUES(v_sendto,v_bcc)
Initialize v_sendto and v_bcc to ''

Set the row 5 value to a variable named v_sendto
Concatonate the next 4 row values to variable named v_bcc
Insert Into sendtonew(sendto,bcc) VALUES(v_sendto,v_bcc)
Initialize v_sendto and v_bcc to ''

Set the row 10 value to a variable named v_sendto
Concatonate the next 4 row values to variable named v_bcc
Insert Into sendtonew(sendto,bcc) VALUES(v_sendto,v_bcc)
Initialize v_sendto and v_bcc to ''

Set the row 15 value to a variable named v_sendto
Concatonate the next 4 row values to variable named v_bcc
Insert Into sendtonew(sendto,bcc) VALUES(v_sendto,v_bcc)
Initialize v_sendto and v_bcc to ''

Stop when the next cursor row has no value.

Any help will be appreciated.

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Loop Through Cursor
2479
November 14, 2015 08:14AM
681
November 30, 2015 08:10PM
696
November 30, 2015 11:46PM


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.