MySQL Forums
Forum List  »  Stored Procedures

Can't get a cursor to work.
Posted by: Nathan Muleski
Date: December 21, 2011 04:01PM

I am trying to write a query with a cursor. I know how to do it in MSSQL but everything I've tried in MySQL returns an error. I've changed what I know needs to be changed but I must be missing something.


DELIMITER ??
CREATE PROCEDURE demo()
BEGIN
DECLARE @cstmr_id VARCHAR(20);

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;

DECLARE cstmr_id_cursor CURSOR FOR
SELECT
cc_shipto_id_c
FROM
cc_link;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;

OPEN cstmr_id_cursor

SELECT FOUND_ROWS() INTO num_rows;

the_loop: LOOP
FETCH cstmr_id_cursor
INTO @cstmr_id

IF no_more_rows THEN
CLOSE cstmr_id_cursor;
LEAVE the_loop;
END IF;

-- count the number of times looped
SET loop_cntr = loop_cntr + 1;
-- Selects the table from linked server to update
-- Updates linked server table with data from cc_link view where customer id's match
INSERT INTO accounts_cstm (cc_class1_c, cc_class2_c, cc_class3_c, cc_sales_pot_c)
SELECT class1, class2, class3, class_1id FROM cc_link WHERE customer_id=@cstmr_id
-- Sets the prospect_c field to 1 if customer has not ordered in last two years
UPDATE accounts_cstm, cc_link
SET accounts_cstm.prospect_c = 1
WHERE cc_link.last_order_months>=24 AND @cstmr_id=cc_link.customer_id

END LOOP the_loop;

-- 'print' the output so we can see they are the same
select loop_cntr;
END
//
DELIMITER ;
CALL demo();

Sometimes I get this error:

Error Code: 1305. PROCEDURE bitnami_sugarcrm.test does not exist

Other times I get this error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() INTO num_rows;


Any help would be greatly appreciated!

Options: ReplyQuote


Subject
Views
Written By
Posted
Can't get a cursor to work.
2489
December 21, 2011 04:01PM
867
December 24, 2011 10:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.