0

i have written the function below containing a cursor while executing it an error appears . what' wrong please

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR FOR 
     SELECT * FROM psdms_feature_product 

   dept_rec ordernumbers%ROWTYPE
   o ordernumbers%ROWTYPE

   OPEN ordernumbers
   REPEAT
    FETCH ordernumbers INTO o

    IF (o.id_product ) THEN
      IF (o.id_feature  == 510) THEN
        update    psdms_feature_value_lang t1
        LEFT JOIN psdms_feature_product t2
        ON        t1.id_feature_value = t2.id_feature_value
        SET       value ='95090€'
        WHERE     t2.id_product = 1 
        AND       t2.id_feature = 510
      ELSE
        insert into psdms_feature_value ( id_feature ) values (510);
        insert into psdms_feature_product ( id_feature, id_product , id_feature_value) 
        values (o.id_feature , o.id_product , o.id_feature_value )
      END IF
    END IF

   UNTIL done END REPEAT
   CLOSE ordernumbers
END
END
  • Please reformat your question so that we can read it properly, and tell us what the error message says. – Randolph West Feb 9 '18 at 17:20
  • Erreur dans la requête (1064): Syntax error near 'END IF END IF UNTIL done END REPEAT CLOSE ordernumbers END' at line 3 – J.Luis Feb 9 '18 at 17:21
  • Could it be as simple as the second END after CLOSE ordernumbers? That doesn't logically seem to have any matching BEGIN or equivalent. – RDFozz Feb 9 '18 at 17:33
  • There seem to be a few missing semi-colons ... – dbdemon Feb 9 '18 at 23:30
  • MySQL do not support %ROWTYPE, and of course missed semicolons – a_vlad Feb 10 '18 at 10:26
0

Better is to avoid cursors. Remember that SQL loves to do things in bulk, but hates to work with Cursors.

UPDATE  psdms_feature_value_lang t1
    JOIN  ordernumbers AS o  ON ...
    LEFT JOIN  psdms_feature_product t2
          ON t1.id_feature_value = t2.id_feature_value SET
    value  ='95090€'
    WHERE  t2.id_product = 1
      AND  t2.id_feature = 510
      AND  o.id_product
      AND  o.id_feature = 510;
INSERT
     INTO  psdms_feature_value ( id_feature ) 
SELECT  510
    FROM  ordernumbers
    WHERE  o.id_product
      AND  o.id_feature = 510;
INSERT
     INTO  psdms_feature_product ( id_feature, id_product, id_feature_value) 
SELECT  o.id_feature , o.id_product , o.id_feature_value
    FROM  ordernumbers
    WHERE  o.id_product
      AND  o.id_feature = 510;

Index:

ordernumbers: INDEX(id_feature, id_product, id_feature_value)

Other notes:

== is not proper syntax

What do you mean by AND id_product? It probably tests for non-zero.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.