0

Im trying to convert a procedure from using sql plus to mysql but cannot get it to work.

SQL Plus:

CREATE OR REPLACE PROCEDURE PRC_CUS_BALANCE_UPDATE (W_IN IN NUMBER) AS
W_CUS NUMBER := 0;
W_TOT NUMBER := 0;
BEGIN
    -- GET THE CUS_CODE
    SELECT CUS_CODE INTO W_CUS 
    FROM INVOICE
    WHERE INVOICE.INV_NUMBER = W_IN;

    -- UPDATES CUSTOMER IF W_CUS > 0
    IF W_CUS > 0 THEN
        UPDATE CUSTOMER
        SET CUS_BALANCE = CUS_BALANCE + 
                    (SELECT INV_TOTAL FROM INVOICE WHERE INV_NUMBER = W_IN)
        WHERE CUS_CODE = W_CUS;
    END IF;
END;

mySQL:

DELIMITER $$

CREATE PROCEDURE prc_cus_balance_update (IN W_IN NUMBER) AS
W_CUS NUMBER = 0;
W_TOT NUMBER = 0;
BEGIN
    -- GET CUS_CODE
    SELECT CUS_CODE INTO W_CUS 
    FROM INVOICE
    WHERE INVOICE.INV_NUMBER = W_IN;

    -- UPDATES CUSTOMER IF W_CUS > 0
    IF W_CUS > 0 THEN
        UPDATE CUSTOMER
        SET CUS_BALANCE = CUS_BALANCE + 
                    (SELECT INV_TOTAL FROM INVOICE WHERE INV_NUMBER = W_IN)
        WHERE CUS_CODE = W_CUS;
    END IF;
END $$

DELIMITER ;

I keep getting an error on the line "CREATE PROCEDURE prc_cus_balance_update (IN W_IN DOUBLE) AS" and get an syntax error over AS.

Any help is greatly appreciated!

3
Contributors
3
Replies
6
Views
5 Years
Discussion Span
Last Post by urtrivedi
1

use declare work after begin

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;

    SELECT xname,id INTO newname,xid 
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.