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!

Recommended Answers

All 3 Replies

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;

can we import a mysql database into oracle sql plus.....

one method is

export mysql in csv format
using sql develolper import csv files to oracle tables

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.