firebird procedure to mysql

2019-09-01 13:49发布

I have trouble converting a stored procedure from Firebird to MySQL.

Here is my existing code from Firebird:

CREATE OR ALTER PROCEDURE GET_EOMONTH (
    selectmonth integer,
    selectyear integer)
returns (
    lastdate timestamp)
as
declare variable vmonth integer;
declare variable vyear integer;
begin
  VMONTH = selectmonth+1;
  VYEAR = selectyear;
  if (VMONTH=13) then
    BEGIN
      vmonth = 1;
      vyear =  selectyear + 1;
    END


  LASTDATE = CAST(VMONTH || '/1/' || VYEAR AS DATE) - 1;
  /* Procedure Text */
  suspend;
end^

Then I modify code above to:

CREATE PROCEDURE GET_EOMONTH (
    selectmonth integer,
    selectyear integer)
returns (
    lastdate timestamp)
as
declare variable vmonth integer;
declare variable vyear integer;
begin
  VMONTH = selectmonth+1;
  VYEAR = selectyear;
  if (VMONTH=13) then
    BEGIN
      vmonth = 1;
      vyear =  selectyear + 1;
    END


  LASTDATE = CAST(VMONTH || '/1/' || VYEAR AS DATE) - 1;
  /* Procedure Text */
  suspend;
end

From code above I got following errors like this:

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 '( lastdate timestamp) as declare variable vmonth integer' at line 4 */

--edited

I modified again in to this:

DELIMITER $$
CREATE PROCEDURE GET_EOMONTH (
    IN selectmonth INT,
    IN selectyear INT,
     OUT LASTDATE VARCHAR(100))
BEGIN
DECLARE VMONTH INT;
DECLARE VYEAR INT;
  SET VMONTH = selectmonth+1;
  SET VYEAR = selectyear;
  IF (VMONTH=13) THEN
    BEGIN
      SET VMONTH = 1;
      SET VYEAR =  selectyear + 1;
    END;
   END IF;
  SET LASTDATE = CAST(VMONTH || '/1/' || VYEAR AS DATE) - 1;
  SELECT @LASTDATE;
END
END$$

but, the error says:

SQL Error (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 'END' at line 19

Please advice.

--edited

Now I modify code like this:

DELIMITER $$
CREATE PROCEDURE GET_EOMONTH (
    IN selectmonth INT,
    IN selectyear INT,
     OUT LASTDATE VARCHAR(100))
BEGIN
DECLARE VMONTH INT;
DECLARE VYEAR INT;
  SET VMONTH = selectmonth+1;
  SET VYEAR = selectyear;
  IF (VMONTH=13) THEN
    BEGIN
      SET VMONTH = 1;
      SET VYEAR =  selectyear + 1;
    END;
   END IF;
  SET LASTDATE = str_to_date(CONCAT(VYEAR, "-", VMONTH, "-1"), '%y/%m/%d') -1;
  SELECT @LASTDATE;
END$$
DELIMITER ;

And it works.

0条回答
登录 后发表回答