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. Sada je otestována na MS SQL Server 2014

CREATE SCHEMA zd_random
GO

CREATE VIEW zd_random.random AS
SELECT RAND() value
GO

CREATE FUNCTION zd_random.get_int (@i_min_range int, @i_max_range int) RETURNS int AS
BEGIN
   DECLARE @random float;
   DECLARE @x_output int;
   SELECT @random = value FROM zd_random.random;
   SET @x_output = @i_min_range + FLOOR(@random * (@i_max_range - @i_min_range + 1));
   RETURN @x_output;
END
GO 

CREATE FUNCTION zd_random.get_decimal (@i_min_range float, @i_max_range float, @i_decimal_count int) RETURNS float AS
BEGIN
   DECLARE @random float;
   DECLARE @x_output float;
   DECLARE @x_min_range float;
   DECLARE @x_max_range float;
   DECLARE @x_result float;
   SET @x_min_range = @i_min_range * POWER(10, @i_decimal_count);
   SET @x_max_range = @i_max_range * POWER(10, @i_decimal_count);
   SELECT @random = value FROM zd_random.random;
   SET @x_result = @x_min_range + FLOOR(@random * (@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
GO 

CREATE FUNCTION zd_random.get_char (@i_chars varchar(2000)) RETURNS char(1) AS
BEGIN
   DECLARE @x_output char(1);
   SET @x_output = SUBSTRING(@i_chars, zd_random.get_int(1, LEN(@i_chars)), 1);
   RETURN @x_output;
END
GO 

CREATE FUNCTION zd_random.get_date (@i_min_date date, @i_max_date date) RETURNS date AS
BEGIN
   DECLARE @x_output date;
   DECLARE @x_diff int;
   DECLARE @x_add int;
   SET @x_diff = DATEDIFF(day, @i_min_date, @i_max_date);   
   SET @x_add = zd_random.get_int(0, @x_diff);
   SET @x_output = DATEADD(day, @x_add, @i_min_date);
   RETURN @x_output;
END
GO 

CREATE FUNCTION zd_random.get_string (@i_length int, @i_chars varchar(2000)) RETURNS varchar(2000) AS
BEGIN
   DECLARE @x_output varchar(2000);
   DECLARE @x_count int;
   SET @x_output = '';
   SET @x_count = @i_length;
   WHILE @x_count > 0
   BEGIN
      SET @x_output = @x_output + zd_random.get_char(@i_chars);
      SET @x_count = @x_count - 1;
   END
   RETURN @x_output;
END
GO 

SELECT zd_random.get_int (1, 10);
GO
SELECT zd_random.get_decimal (1, 2, 1);
GO
SELECT zd_random.get_char ('ABCD');
GO
SELECT zd_random.get_date ('2014-01-01', '2014-01-05');
GO
SELECT zd_random.get_string (5, 'ABCD');
GO

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.

CREATE SCHEMA zd_log;
GO

--
-- Nastavení ID aplikačního uživatele pro DB session
--
CREATE PROCEDURE zd_log.set_appl_user (@id int) AS
BEGIN
   DECLARE @bin binary(128);
   SET @bin = CAST(@id AS binary(128));
   SET CONTEXT_INFO @bin;
END
GO 

--
-- Získání ID aplikačního uživatele
--
CREATE FUNCTION zd_log.get_appl_user() RETURNS int AS
BEGIN
   DECLARE @id int;
   DECLARE @bin binary(128);
   SELECT @bin = CONTEXT_INFO();
   SET @id = CAST(@bin AS int);
   RETURN ISNULL(@id, 0);
END
GO 

--
-- Vytvoření testovací tabulky
--
CREATE TABLE zd_log.test
     ( id                     int IDENTITY(1,1) NOT NULL,
       content                varchar(2000),
       created_at             datetimeoffset NOT NULL DEFAULT SYSDATETIMEOFFSET(),
       created_by             int NOT NULL DEFAULT zd_log.get_appl_user(),
       created_by_db          varchar(64) NOT NULL DEFAULT ORIGINAL_LOGIN(),
       updated_at             datetimeoffset,
       updated_by             int,
       updated_by_db          varchar(64),
       dbchanged              rowversion,
     CONSTRAINT PK_test PRIMARY KEY CLUSTERED 
     ( id ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Testovací tabulka logování' , @level0type=N'SCHEMA', @level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id záznamu' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'id';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datum a čas založení záznamu' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'created_at';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id aplikačního uživatele, který založil záznam' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'created_by';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Databázový uživatel, který založil záznam' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'created_by_db';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datum a čas poslední změny záznamu' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'updated_at';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Id aplikačního uživatele, který poslední změnil záznam' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'updated_by';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Databázový uživatel, který poslední změnil záznam' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'updated_by_db';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Značka pro optimistické zamykání záznamu' , @level0type=N'SCHEMA',@level0name=N'zd_log', @level1type=N'TABLE',@level1name=N'test', @level2type=N'COLUMN',@level2name=N'dbchanged';
GO
CREATE TRIGGER test_au ON zd_log.test AFTER UPDATE AS
   SET NOCOUNT ON;
   UPDATE T SET T.updated_at = SYSDATETIMEOFFSET(), T.updated_by = zd_log.get_appl_user(), T.updated_by_db = ORIGINAL_LOGIN()
   FROM   inserted I JOIN zd_log.test T ON T.id = I.id;
GO

--
-- Ukázka a ověření funkčnosti
--
EXEC zd_log.set_appl_user 62;
GO
SELECT zd_log.get_appl_user;
GO
INSERT INTO zd_log.test (content) VALUES ('Record 1');
INSERT INTO zd_log.test (content) VALUES ('Record 2');
GO
EXEC zd_log.set_appl_user 14;
GO
UPDATE zd_log.test SET content = 'Record 1 updated' WHERE content = 'Record 1';
GO
SELECT * FROM zd_log.test;
GO

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).

CREATE SCHEMA zd_sentence
GO

CREATE TABLE zd_sentence.sentence
     ( sentence              int NOT NULL,
     CONSTRAINT [PK_sentence] PRIMARY KEY CLUSTERED 
     ( sentence ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tabulka počtů' , @level0type=N'SCHEMA',@level0name=N'zd_sentence', @level1type=N'TABLE',@level1name=N'sentence';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Počet' , @level0type=N'SCHEMA',@level0name=N'zd_sentence', @level1type=N'TABLE',@level1name=N'sentence', @level2type=N'COLUMN',@level2name=N'sentence';
GO

CREATE PROCEDURE zd_sentence.insert_sentence
 ( @id_from int,
   @id_to int )
   WITH EXECUTE AS OWNER
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @id int;
   SET @id = @id_from;
   WHILE @id <= @id_to
   BEGIN  
      INSERT INTO zd_sentence.sentence (sentence) VALUES (@id);
      SET @id = @id + 1;
   END;  
END;
GO

EXEC zd_sentence.insert_sentence 1, 1000;
GO

--
-- Ukázka použití
--
SELECT 'Etiketa číslo ' + CAST(sentence AS varchar(2)) FROM zd_sentence.sentence WHERE sentence >= 1 AND sentence <= 50;

Databázové služby

Vzorové příklady pro MS SQL Server

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í