0

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

1 Answer 1

1
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 where scheck='NO'; 
 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

Just add the where clause in the cur1 .it's now working,check it.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.