I created two cursor for loop through each values in outer loop and go through whole inner loop . So i created following nested cursor .but it works when one of my table has one row of data .then i insert more data i first table but the cursor is not working as it should be
BEGIN
DECLARE done int default false;
DECLARE a,b varchar(20);
DECLARE c,d date;
DECLARE f,j text;
DECLARE cur2 CURSOR FOR SELECT tran_id,tran_date FROM bank_tran where tcheck='NO';
DECLARE cur1 CURSOR FOR SELECT stran_id,stran_date,scheck FROM student_tran;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a,c,j;
IF done THEN
LEAVE read_loop;
END IF;
OPEN cur2;
inside_loop:LOOP
FETCH cur2 INTO b,d;
IF done THEN
LEAVE inside_loop;
END IF;
IF (a = b) and (c=d) THEN
update student_tran set valid='YES',scheck='YES' where stran_id=a;
update bank_tran set matched_sub_id=a ,tcheck='YES' where tran_id=a;
CLOSE cur2;
LEAVE inside_loop;
ELSE
ITERATE inside_loop;
END IF;
END LOOP inside_loop;
END LOOP read_loop ;
CLOSE cur1;
END
and the structure of my two table that i used in the cursor are
CREATE TABLE IF NOT EXISTS `bank_tran` (
`tran_id` varchar(20) NOT NULL,
`tran_date` date NOT NULL,
`tcheck` text NOT NULL,
`matched_sub_id` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `student_tran` (
`student_id` varchar(20) NOT NULL,
`sub_apply_id` varchar(20) NOT NULL,
`stran_id` varchar(20) NOT NULL,
`stran_date` date NOT NULL,
`scheck` text NOT NULL,
`valid` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
And some data for both tables are
INSERT INTO `bank_tran` (`tran_id`, `tran_date`, `tcheck`, `matched_sub_id`) VALUES
('4HZZGl', '2015-02-03', 'NO', ''),
('LkvUG5', '2015-02-03', 'NO', ''),
('fKbl8H', '2015-02-03', 'NO', ''),
('fIS7kg', '2015-02-03', 'NO', ''),
('GQqBic', '2015-02-03', 'NO', ''),
('ZPU3Yx', '2015-02-03', 'NO', ''),
('gKSNz7', '2015-02-03', 'NO', ''),
('DeyvKU', '2015-02-03', 'NO', ''),
('pKarTd', '2015-02-03', 'NO', ''),
('nVbJkW', '2015-02-03', 'NO', ''),
('1EuzDN', '2015-02-03', 'NO', ''),
('QrIxQD', '2015-02-03', 'NO', ''),
('vHtJID', '2015-02-03', 'NO', ''),
('cQzbYy', '2015-02-03', 'NO', ''),
('MGhWy0', '2015-02-03', 'NO', ''),
('6DDsSH', '2015-02-03', 'NO', '');
INSERT INTO `student_tran` (`student_id`, `sub_apply_id`, `stran_id`, `stran_date`, `scheck`, `valid`) VALUES
('', '', '4HZZGl', '2015-02-03', 'NO', ''),
('', '', 'LkvUG5', '2015-02-03', 'NO', ''),
('', '', 'fKbl8H', '2015-02-03', 'NO', ''),
('', '', 'fIS7kg', '2015-02-03', 'NO', ''),
('', '', 'GQqBic', '2015-02-03', 'NO', ''),
('', '', 'ZPU3Yx', '2015-02-03', 'NO', ''),
('', '', 'gKSNz7', '2015-02-03', 'NO', ''),
('', '', 'DeyvKU', '2015-02-03', 'NO', ''),
('', '', 'pKarTd', '2015-02-03', 'NO', ''),
('', '', 'nVbJkW', '2015-02-03', 'NO', ''),
('', '', '1EuzDN', '2015-02-03', 'NO', ''),
('', '', 'QrIxQD', '2015-02-03', 'NO', ''),
('', '', 'vHtJID', '2015-02-03', 'NO', ''),
('', '', 'cQzbYy', '2015-02-03', 'NO', ''),
('', '', 'MGhWy0', '2015-02-03', 'NO', ''),
('', '', '6DDsSH', '2015-02-03', 'NO', '');
what i am doing is that if in student transaction table the stran_id is valid or not if yest then to set corresponding bank_trans 's matched_sub_id to stran_id(for now only to see if its working or not) .i am beginner in pl/sql programming