MariaDB pl/sql to populate a table with dates from '1990-01-01' to '2000-12-31'

Question

I attempt to create a procedure or function to populate a table name Time_Dim in MariaDB.

DELIMITER /
CREATE FUNCTION filltimeDim ()
BEGIN
    DECLARE vQuarter VARCHAR(6);
    DECLARE vMonth_Name VARCHAR(20);
    DECLARE vdate_id date;
    DECLARE vyear_id CHAR(4);
    DECLARE vweekly_Name VARCHAR(20);
    DECLARE vMonth_Num TINYINT(10);
    DECLARE vweekday_Num TINYINT(10);
    BEGIN
        SET vdate_id =  CONVERT('1998-01-01', DATE);
        WHILE (CONVERT('vdate_id' USING utf8) <=  '2002-12-31')
        LOOP
            SET vyear_id =  YEAR(vdate_id);
            SET vQuarter =  QUARTER(vdate_id);
            SET vMonth_Name =  MONTHNAME(vdate_id);
            SET vweekly_Name =  DAYOFWEEK(vdate_id);
            SET vMonth_Num =  MONTH(vdate_id);
            SET vweekday_Num =  WEEKDAY(vdate_id);
            INSERT INTO Time_Dim VALUES 
            (vdate_id, vyear_id, vquarter, vMonth_Name,vweekly_Name,vMonth_Num,vweekday_Num);
            SET vdate_id = vdate_id + 1;
        END LOOP;   
    END;
END; /

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOOP SET vyear_id = YEAR(vdate_id); SET vQuarter = QUA' at line 13

Did you check the manual? Because it's literally in it whats wrong. — Wesley De Keirsmaeker, Dec 20 '19 at 15:04
'PL/SQL' is the name for Oracle's in-database programming language. I gather that MariaDB has an Oracle compatibility mode, but the code above is not PL/SQL. — William Robertson, Dec 20 '19 at 17:22
Does this answer your question? [mariadb fonction, procedure error You have an error in your SQL](https://stackoverflow.com/questions/59424533/mariadb-fonction-procedure-error-you-have-an-error-in-your-sql) — markusjm, Dec 21 '19 at 05:33
William Robertson you right and how do we call maria's database programming language? Yes markusjm, it does answer my question. — comocoder, Dec 22 '19 at 00:16

score 0 · Answer 1 · answered Dec 20 '19 at 15:09

0

The syntax for while in MariaDB is:

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

https://mariadb.com/kb/en/library/while/

You shouldn't use the 'LOOP' keywords

answered Dec 20 '19 at 15:09

Wesley De Keirsmaeker

1,026
9
29

score 0 · Answer 2 · answered Dec 24 '19 at 16:56

You can use Sequence Storage Engine:

-- SELECT
  -- DATEDIFF('2002-12-31', '1990-01-01'); -- 4747

INSERT INTO `Time_Dim` (
  `vdate_id`,
  `vyear_id`,
  `vquarter`,
  `vMonth_Name`,
  `vweekly_Name`,
  `vMonth_Num`,
  `vweekday_Num`
)
SELECT
  `der`.`day`,
  YEAR(`der`.`day`),
  QUARTER(`der`.`day`),
  MONTHNAME(`der`.`day`),
  DAYOFWEEK(`der`.`day`),
  MONTH(`der`.`day`),
  WEEKDAY(`der`.`day`)
FROM (
  SELECT
    '1990-01-01' + INTERVAL (`seq`) DAY `day`
  FROM
    `seq_0_to_4747`
) `der`;

See dbfiddle.

MariaDB pl/sql to populate a table with dates from '1990-01-01' to '2000-12-31'

2 Answers2