RE: Stored Procedure - jbon...@sola.com.au - com.mysql.lists.win32

5 messages in com.mysql.lists.win32RE: Stored Procedure

From	Sent On	Attachments
Melissa Dougherty	15 Jun 2006 12:15
jbon...@sola.com.au	18 Jun 2006 16:06
Melissa Dougherty	19 Jun 2006 04:41
Melissa Dougherty	19 Jun 2006 05:42
jbon...@sola.com.au	19 Jun 2006 16:00

Subject:	RE: Stored Procedure
From:	jbon...@sola.com.au (jbon...@sola.com.au)
Date:	06/19/2006 04:00:48 PM
List:	com.mysql.lists.win32

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.

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.

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:meli...@cse-corp.com] Sent: Friday, 16 June 2006 4:46 AM To: win...@lists.mysql.com 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,