Databases
StackOverflow2013
Postsdbo
POHandling multiple nested cursors in stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHandling multiple nested cursors in stored procedure
    primarykey
    data
    text
    <p>How can I use two cursors in the same routine? I am getting "Variable or condition declaration after cursor or handler declaration" error while creating procedure. I have to use both integer outerDone &amp; innerDone to check whether cursor points to null or not. I haven't worked on stored-procedure yet. Could anybody sort this problem please. Thanks in advance!!</p> <pre><code>DELIMITER ##; create procedure updateStopTimeColumn() BEGIN DECLARE outerDone INT DEFAULT 0; DECLARE vehicle_record CURSOR FOR SELECT `vehicleId` FROM `vehicle`; DECLARE current_record CURSOR FOR SELECT `id`,`tsTime`,`teTime` FROM `trip` where `vehicleId`=vehId order by `tsTime`; DECLARE CONTINUE HANDLER FOR NOT FOUND SET outerDone = 1; DECLARE vehId,tripId CHAR(250); DECLARE currentTsTime,currentTeTime time; OPEN vehicle_record; REPEAT FETCH vehicle_record INTO vehId; block2 : BEGIN DECLARE innerDone INT DEFAULT 0; DECLARE tempTripId CHAR(250); DECLARE tempTsTime,tempTeTime time; DECLARE CONTINUE HANDLER FOR NOT FOUND SET innerDone = 1; OPEN current_record; FETCH current_record INTO tempTripId, tempTsTime,tempTeTime; REPEAT FETCH current_record INTO tripId,currentTsTime,currentTeTime; UPDATE trip set stopTime=(currentTeTime-tempTsTime) where id=tempTripId and tempTeTime IS NOT NULL; SET tempTripId=tempId; SET tempTsTime=currentTsTime; SET tempTeTime=currentTeTime; UNTIL innerDone END REPEAT; END block2; CLOSE current_record; UNTIL outerDone END REPEAT; CLOSE vehicle_record; END; ## DELIMITER; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAaron Bertrand
    1. USPrateek
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHandling multiple nested cursors in stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHandling multiple nested cursors in stored procedure
      1. USAnand Kushwaha
      1. VTFavorite
    1. COIf possible, can you create a SQL Fiddle, [http://sqlfiddle.com/](http://sqlfiddle.com/) for your problem? It just makes it much easier to test solutions.
      singulars
      1. POHandling multiple nested cursors in stored procedure
      1. USneelsg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload