RE: Stored Procedure

I don't see that in the code you have submitted. The only way you can do
that is to declare the second cursor inside the repeat loop for the
first one. That would make things even slower.

By the way what you have coded in your stored procedure looks very like
a first attempt at a sequential file update. That is the sort of thing I
used to code in COBOL back in the 70s when our databases were stored on
magnetic tapes! I don't know about the nature of your data, but your
logic will only work if the rows match one-for-one in the two cursors.
If there are duplicate matches or mismatches your logic will fail badly.
The cursors must also be ordered. If you really need to do it this way I
can find you a good algorithm but it is much more complicated than what
you have.

So far I cannot see anything in what you want to do that cannot be done
much better by join queries where mismatches and duplicates will be
automatically handled correctly. Relational database engines are much
better at this than we are. That's why they exist! You will find the
sequential update logic buried inside all such engines.

John B.

-----Original Message-----
From: Melissa Dougherty [mailto:[email protected]] 
Sent: Monday, 19 June 2006 10:13 PM
To: John Bonnett, R&D Australia; [email protected]
Subject: Re: Stored Procedure

I just remember another issue with declaring the cursors.... I want to
use 
the doc_no from the first cursor as a variable in the second cursor.
That's 
where I'm having the issue.

Melissa

----- Original Message ----- 
From: <[email protected]>
To: <[email protected]>; <[email protected]>
Sent: Sunday, June 18, 2006 7:06 PM
Subject: RE: Stored Procedure


>I think what you are trying to do boils down to this
>
> INSERT INTO ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> SELECT t1.doc_type, t1.doc_no, t1.ref_doc_no,
> concat('-',t1.gv_dollar_amt)
> FROM test.ar_no_ins_outs_temp AS t1, test.ar_no_ins_outs_temp AS t2
> WHERE t1.doc-type in ('PV','GV')
> AND t2.doc_type in ('RG','RC','RT')
> AND t1.doc_type = t2.doc_no
> AND t1.ref_doc_no = t2.ref_doc_no
> AND concat('-',t1.gv_dollar_amt) = t2.gv_dollar_amt
>
> It is always better to use joins than to use cursors. Cursors should
> always be a last resort. Joins are MUCH more efficient.
>
> If gv_dollar_amt is a numeric type then you should replace
> concat('-',t1.gv_dollar_amt) with -gv_dollar_amt.
>
> You should check that I have not mixed up any of the column names
> because I can't test this.
>
> John B.
>
> -----Original Message-----
> From: Melissa Dougherty [mailto:[email protected]]
> Sent: Friday, 16 June 2006 4:46 AM
> To: [email protected]
> Subject: Stored Procedure
>
> I'm still new to the MySQL SPs.... Does anyone see a problem with this
> code?
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `test`.`test_AuditRun_SP`$$
> CREATE PROCEDURE `test`.`test_AuditRun_SP`(OUT OblDocNo varchar(50),
OUT
> OBLCNT INT)
> BEGIN
>  DECLARE done INT DEFAULT 0;
>  DECLARE Obldoctype varchar(10);
>  DECLARE Obldocno varchar(50);
>  DECLARE Oblrefdocno varchar(50);
>  DECLARE Obldollamt varchar(50);
>  DECLARE Recdoctype varchar(10);
>  DECLARE Recdocno varchar(50);
>  DECLARE Recrefdocno varchar(50);
>  DECLARE Recdollamt varchar(50);
>  DECLARE curObl1 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,concat('-',gv_dollar_amt) FROM
> test.ar_no_ins_outs_temp WHERE doc_type in ('PV','GV');
>  DECLARE curRec2 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,gv_dollar_amt FROM test.ar_no_ins_outs_temp
> WHERE doc_type in ('RG','RC','RT');
>  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
>
>  OPEN curObl1;
>  OPEN curRec2;
>
>  REPEAT
>    FETCH curObl1 INTO Obldoctype, Obldocno, Oblrefdocno, Obldollamt;
>    FETCH curRec2 INTO Recdoctype, Recdocno, Recrefdocno, Recdollamt;
>
>    IF NOT done THEN
>
>      IF obldocno = recdocno and Oblrefdocno = Recrefdocno and
> Obldollamt = Recdollamt THEN
>
>      INSERT into ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
>           values (Obldoctype, Obldocno, Oblrefdocno, Obldollamt);
>
>      END IF;
>   END IF;
>
>  UNTIL done END REPEAT;
>
>  CLOSE curObl1;
>  CLOSE curRec2;
>
> END$$
>
> DELIMITER ;
>
>
> Thanks,
>
> Melissa
>
> -- 
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/[email protected]
>
> 


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]