Can't Create Procedure Mysql

Question

I've already confused so that, I am asking here, about how to create a stored procedure in mysql with below code :

DELIMITER $$
CREATE PROCEDURE hitung_nilai_stok(sisa INT)

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE jumlah DECIMAL;
    DECLARE harga INT;
    DECLARE nilai_stok INT DEFAULT 0;
    DECLARE selisih DECIMAL DEFAULT 0.000;
    DECLARE data_stok CURSOR FOR SELECT
        Stok.masuk,
        Stok.harga_masuk

        FROM stoks Stok
        WHERE Stok.barang_id = 1
        AND Stok.tanggal <= '2013-11-19';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    TRUNCATE TABLE transaksi;

    SET selisih = sisa;
    OPEN data_stok;

    loop_data: LOOP
        FETCH data_stok INTO jumlah, harga;
        SET selisih = selisih - jumlah;

        IF done THEN
            LEAVE loop_data;
        END IF;

        IF (selisih = 0.000) THEN
            INSERT INTO transaksi VALUES (jumlah,harga);
        ELSE IF (selisih > 0.000) THEN
            SET selisih = selisih;
            INSERT INTO transaksi VALUES (jumlah,harga);
        ELSE IF (selisih < 0.000) THEN
            INSERT INTO transaksi VALUES (selisih,harga);
            SET done = TRUE;
        END IF;
    END LOOP loop_data;

    CLOSE data_stok;

    SELECT SUM(jumlah*harga) FROM transaksi;

END$$

DELIMITER ;

The error I got is :

#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 'LOOP loop_data; CLOSE data_stok; SELECT SUM(jumlah*harga) INTO nilai_stok FR' at line 49

Well, when I removed these lines :

ELSE IF (selisih > 0.000) THEN
    SET selisih = selisih;
    INSERT INTO transaksi VALUES (jumlah,harga);
ELSE IF (selisih < 0.000) THEN
    INSERT INTO transaksi VALUES (selisih,harga);
    SET done = TRUE;

It executed successfully. So, what is it that made the error occured? Can somebody gimme some clues?

Mad Dog Tannen · Accepted Answer · 2013-11-19 11:23:00Z

1

Your ELSE IF is wrong, it should be ELSEIF together

Try this

IF (selisih = 0.000) THEN
            INSERT INTO transaksi VALUES (jumlah,harga);
        ELSEIF (selisih > 0.000) THEN
            SET selisih = selisih;
            INSERT INTO transaksi VALUES (jumlah,harga);
        ELSEIF (selisih < 0.000) THEN
            INSERT INTO transaksi VALUES (selisih,harga);
            SET done = TRUE;
 END IF;

answered Nov 19, 2013 at 11:23

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thom Over a year ago

Oh my god.. I really believe it would be a 'simple' mistake. Thank you for pointing me the mistake :D Executed nicely now.

Mad Dog Tannen Over a year ago

Usually it is a simple mistake but its also easy to miss :). Do you make use of MySQL Workbench?

thom Over a year ago

Nope. I just using phpmyadmin :D I thought i more than enough for me (at least for now)

Collectives™ on Stack Overflow

Can't Create Procedure Mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related