Databáze ZoraData

Sada funkcí pro generování náhodných hodnot

Sada obsahuje funkce pro generování náhodného celého a desetinného čísla, náhodného znaku a řetězce a náhodného datumu.

DELIMITER //
CREATE FUNCTION zd_random_int ( i_min_range INT,
                                i_max_range INT ) RETURNS INT(11)
   COMMENT 'Generuje náhodné celé číslo'
   LANGUAGE SQL
   NOT DETERMINISTIC
   NO SQL
   SQL SECURITY INVOKER
BEGIN
   DECLARE x_output INT;
   SET x_output = i_min_range + FLOOR(RAND() * (i_max_range - i_min_range + 1));
   RETURN x_output;
END;
//

CREATE FUNCTION zd_random_double ( i_min_range DOUBLE,
                                   i_max_range DOUBLE,
                                   i_decimal_count INT ) RETURNS DOUBLE
   COMMENT 'Generuje náhodné desetinné číslo'
   LANGUAGE SQL
   NOT DETERMINISTIC
   NO SQL
   SQL SECURITY INVOKER
BEGIN
   DECLARE x_output      DOUBLE;
   DECLARE x_min_range   BIGINT;
   DECLARE x_max_range   BIGINT;
   DECLARE x_result      BIGINT;
   SET x_min_range = i_min_range * POWER(10, i_decimal_count);
   SET x_max_range = i_max_range * POWER(10, i_decimal_count);
   SET x_result = x_min_range + FLOOR(RAND() * (x_max_range - x_min_range + 1));
   SET x_output = ROUND(x_result / POWER(10, i_decimal_count), i_decimal_count);
   RETURN x_output;
END;
//

CREATE FUNCTION zd_random_char ( i_chars VARCHAR(2000) ) RETURNS CHAR(1)
   COMMENT 'Generuje náhodný znak'
   LANGUAGE SQL
   NOT DETERMINISTIC
   NO SQL
   SQL SECURITY INVOKER
BEGIN
   DECLARE x_chars      VARCHAR(200) DEFAULT IFNULL(i_chars, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789');
   DECLARE x_output     CHAR(1);
   DECLARE x_min_range  INT DEFAULT 1;
   DECLARE x_max_range  INT DEFAULT LENGTH(x_chars);
   DECLARE x_position   INT;
   SET x_position = zd_random_int(x_min_range, x_max_range);
   SET x_output = SUBSTR(x_chars, x_position, 1);
   RETURN x_output;
END;
//

CREATE FUNCTION zd_random_date ( i_min_date DATE,
                                 i_max_date DATE  ) RETURNS DATE
   COMMENT 'Generuje náhodné datum'
   LANGUAGE SQL
   NOT DETERMINISTIC
   NO SQL
   SQL SECURITY INVOKER
BEGIN
   DECLARE x_diff       INT;
   DECLARE x_add	INT;
   DECLARE x_output     DATE;
   SET x_diff = DATEDIFF(i_max_date, i_min_date);
   SET x_add = zd_random_int(0, x_diff);
   SET x_output = DATE_ADD(i_min_date, INTERVAL x_add DAY);
   RETURN x_output;
END;
//

CREATE FUNCTION zd_random_string ( i_length INT,
                                   i_chars VARCHAR(2000) ) RETURNS VARCHAR(2000)
   COMMENT 'Generuje náhodný řetězec'
   LANGUAGE SQL
   NOT DETERMINISTIC
   NO SQL
   SQL SECURITY INVOKER
BEGIN
   DECLARE x_output     VARCHAR(2000) DEFAULT '';
   DECLARE x_count      INT DEFAULT 1;
   WHILE x_count <= i_length DO
      SET x_output = CONCAT(x_output, zd_random_char(i_chars));
      SET x_count = x_count + 1;
   END WHILE; 
   RETURN x_output;
END;
//

DELIMITER ;

Sada procedur pro logování událostí nad záznamy tabulky

Vzorové řešení pro automatické logování údajů o založení záznamu a poslední změně záznamu v libovolné databázové tabulce.

DELIMITER //

CREATE PROCEDURE zd_log_record_set_appl_user ( i_user INT )
   COMMENT 'Nastaví id aktuálního aplikačního uživatele'
   SQL SECURITY INVOKER 
BEGIN
   SET @zd_appl_user := i_user;
END;
//

CREATE FUNCTION zd_log_record_current_appl_user () RETURNS INT
   NO SQL
   COMMENT 'Vrací id aktuálního aplikačního uživatele'
   SQL SECURITY INVOKER 
BEGIN
   RETURN IFNULL(@zd_appl_user, 0);
END;
//

CREATE PROCEDURE zd_log_record_set_stamp ( INOUT o_date DATETIME, INOUT o_user_id INT, INOUT o_user_db VARCHAR(64) )
   COMMENT 'Pro logování přístupu k záznamu'
   SQL SECURITY INVOKER 
BEGIN
   SET o_date = now();
   SET o_user_id = zd_log_record_current_appl_user();
   SET o_user_db = current_user();
END;
//

CREATE TABLE zd_log_record_test
 ( id              INT NOT NULL AUTO_INCREMENT COMMENT 'Id záznamu',
   content         VARCHAR(200) COMMENT 'Obsah záznamu',
   created_at      DATETIME NOT NULL COMMENT 'Datum a čas založení záznamu', 
   created_by      INT NOT NULL COMMENT 'Aplikační uživatel, který založil záznam', 
   created_by_db   VARCHAR(64) NOT NULL COMMENT 'Databázový uživatel, který založil záznam',
   updated_at      DATETIME COMMENT 'Datum a čas poslední změny záznamu',
   updated_by      INT COMMENT 'Aplikační uživatel, který poslední změnil záznam', 
   updated_by_db   VARCHAR(64) COMMENT 'Databázový uživatel, který poslední změnil',
PRIMARY KEY i0 (id) )
ENGINE=InnoDb COMMENT='Testovací tabulka pro logování záznamů'
//

CREATE TRIGGER zd_log_record_test_bi BEFORE INSERT ON zd_log_record_test
   FOR EACH ROW
BEGIN
   CALL zd_log_record_set_stamp(NEW.created_at, NEW.created_by, NEW.created_by_db);
END;
//

CREATE TRIGGER zd_log_record_test_bu BEFORE UPDATE ON zd_log_record_test
   FOR EACH ROW
BEGIN
   CALL zd_log_record_set_stamp(NEW.updated_at, NEW.updated_by, NEW.updated_by_db);
END;
//

Tabulka počtů a její naplnění

Vzorové řešení pro vytvoření a naplnění tabulky počtů (obsahuje vzestupnou číselnou řadu se zadaným krokem).

DELIMITER //

CREATE TABLE zd_sentence
  ( sentence INT NOT NULL,
PRIMARY KEY i0 (sentence))
ENGINE=InnoDb COMMENT='Tabulka počtů'
//

CREATE PROCEDURE zd_set_sentence
 ( p_from INT,
   p_to INT,
   p_step INT )
  COMMENT 'Nastavení tabulky počtů' 
BEGIN 
  DECLARE a_sentence INT;
  SET a_sentence = p_from;
  WHILE a_sentence <= p_to DO
     INSERT INTO zd_sentence (sentence) VALUES (a_sentence);
     SET a_sentence = a_sentence + p_step;
  END WHILE;
END;
//

DELIMITER ;
CALL zd_set_sentence(1, 1000, 1);

Tabulka kalendáře, její naplnění a zobrazení

Vzorové řešení pro vytvoření, naplnění a zobrazení kalendáře v zadaném rozsahu. Do kalendáře jdou následně doplnit státní svátky a jmeniny.

DELIMITER //

CREATE TABLE zd_calendar
 ( date                 DATE NOT NULL COMMENT 'Datum', 
   holiday              BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Je volno?', 
   name_day             VARCHAR(60) COMMENT 'Jmeniny',
   dbchanged            TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY i0 (date), UNIQUE KEY i1 (holiday, date))
ENGINE=InnoDb COMMENT='Tabulka kalendáře'
//

CREATE PROCEDURE zd_set_calendar
 (p_date_from DATE,
  p_date_to DATE)
  COMMENT 'Naplnění kalendáře' 
BEGIN 
DECLARE a_date DATE;
DECLARE a_holiday BOOLEAN;
  SET a_date = p_date_from;
  WHILE a_date <= p_date_to DO
     IF DATE_FORMAT(a_date, '%w') = 0 OR DATE_FORMAT(a_date, '%w') = 6
     THEN SET a_holiday = TRUE;
     ELSE SET a_holiday = FALSE;
     END IF;
     INSERT INTO zd_calendar (date, holiday) VALUES (a_date, a_holiday);
     SET a_date = DATE_ADD(a_date, INTERVAL 1 DAY);
  END WHILE;
END;
//

DELIMITER ;
CALL zd_set_calendar('2010-01-01', '2020-12-31');

CREATE VIEW zd_calendar_v
 ( date, 
   date_f, 
   holiday, 
   working, 
   day, 
   month, 
   month_name, 
   month_short, 
   year, 
   week, 
   quarter, 
   day_of_year, 
   day_of_week, 
   day_long, 
   day_short, 
   name_day, 
   dbchanged ) AS 
SELECT C.date, 
       DATE_FORMAT(C.date, '%d.%m.%Y'), 
       C.holiday, 
       NOT C.holiday, 
       DAY(C.date), 
       MONTH(C.date), 
       DATE_FORMAT(C.date, '%M'), 
       DATE_FORMAT(C.date, '%b'), 
       YEAR(C.date), 
       WEEK(C.date, 7), 
       QUARTER(C.date), 
       DAYOFYEAR(C.date), 
       WEEKDAY(C.date)+1,
       DAYNAME(C.date),
       DATE_FORMAT(C.date, '%a'), 
       C.name_day, 
       C.dbchanged
FROM zd_calendar C;

SET lc_time_names = 'cs_CZ';

SELECT * FROM zd_calendar_v ORDER BY date;

Databázové služby

Vzorové příklady pro MySQL

Sada funkcí pro generování náhodných hodnot

Sada procedur pro logování událostí nad záznamy tabulky

Tabulka počtů a její naplnění

Tabulka kalendáře, její naplnění a zobrazení