Úvod > Programování > Provedení dotazu dotazem aneb přenesení výpočtu do databáze

Provedení dotazu dotazem aneb přenesení výpočtu do databáze

02.04.2012 | Zobrazeno: 4016x Zanechte komentář Přejít na komentáře

Máme aplikaci, která pracuje s daty z databáze. Na těchto datech potřebujeme provést nějaký výpočet. Ve většině případů aplikace sestaví dotaz, který se následně v databázi provede a vrátí výsledek. S tímto výsledkem pak pracuje aplikace. Představme si, že se tento algoritmus výpočtu bude v čase často měnit. Znamená to přeprogramovat aplikaci a následně vydat novou verzi. Nechceme-li do nekonečna takovouto situaci řešit, je nutné převést algoritmus výpočtu do databáze a tam ho spravovat. Logika v aplikaci zůstane neměná. Jádrem celého algoritmu výpočtu bude procedura v databázi, která zpracuje tabulku šablon s výpočty. Výsledky těchto dotazů procedura uloží do tabulky s výsledky. Z těchto výsledků bude následně čerpat hodnoty aplikace.

Jak na to

Danou situaci nasimuluji na fiktivním příkladu, který ale nemá daleko od praktického použití. Před vytvořením klíčové procedury, je nutné vytvořit několik tabulek. Tabulku pro vstupní hodnoty nazveme hodnoty. V této tabulce bude uživatel spravovat nezbytná data. Další tabulka bude pro definici výpočtů. Tuto tabulku nazveme sablona_dotazy a nebude ji spravovat uživatel, ale programátor. Výsledné hodnoty bude procedura ukládat do tabulky vysledne_castky. Proto aby se tabulka s výsledky naplnila řádky, je nutná pomocná tabulka pomocna_castka.

Princip klíčové procedury zpracuj_dotaz spočívá v tom, že načte dotaz z tabulky sablona_dotazy a výsledek tohoto dotazu uloží do pomocné tabulky pomocna_castka. V dalším kroku procedura uloží částku s dalšími informacemi o výsledku do tabulky vysledne_castky. Pokud při spouštění dotazu vznikne chyba uloží se do tabulky error_log. Tento proces se aplikuje na všechny řádky v tabulce sablona_dotazy.

Vytvoření tabulek

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'hodnoty') BEGIN
  -- DROP TABLE hodnoty
  CREATE TABLE hodnoty ( id int,
                         kod varchar(30),
                         popis varchar(255),
                         hodnota money )
END
-- SELECT * FROM hodnoty
 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'sablona_dotazy') BEGIN
  -- DROP TABLE sablona_dotazy
  CREATE TABLE sablona_dotazy ( id_radku int,
                                dotaz varchar(8000),
                                popis varchar(8000) )
END
-- SELECT * FROM sablona_dotazy
 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'pomocna_castka') BEGIN
  -- DROP TABLE pomocna_castka
  CREATE TABLE pomocna_castka ( castka money )
END
-- SELECT * FROM pomocna_castka
 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'vysledne_castky') BEGIN
  -- DROP TABLE vysledne_castky
  CREATE TABLE vysledne_castky ( id_radku int,
                                 popis varchar(255),
                                 castka money )
END
-- SELECT * FROM vysledne_castky
 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'error_log') BEGIN
  -- DROP TABLE error_log
  CREATE TABLE error_log ( datum datetime,
                           id_radku int,
                           dotaz varchar(8000),
                           chyba varchar(max) )
END
-- SELECT * FROM error_log

Vložení dat do tabulek

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
IF NOT EXISTS (SELECT * FROM hodnoty) BEGIN
  -- DELETE FROM hodnoty
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (1, 'A', 'Áčko', '10')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (2, 'B', 'Béčko', '20')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (3, 'C', 'Céčko', '30')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (4, 'AB', 'Abéèko', '15')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (5, 'AC', 'Ácéčko', '25')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (6, 'ABC', 'Ábécéčko', '35')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (7, 'BC', 'Bécéčko', '40')
  INSERT INTO hodnoty (id, kod, popis, hodnota)
  VALUES (8, 'CB', 'Cébéčko', '50')
END
-- SELECT * FROM hodnoty
 
IF NOT EXISTS (SELECT * FROM sablona_dotazy) BEGIN
  -- DELETE FROM sablona_dotazy
  INSERT INTO sablona_dotazy (id_radku, dotaz, popis)
  VALUES (1, 'SELECT hodnota FROM hodnoty
              WHERE kod = ''A''', 'Suma za kód A')
  INSERT INTO sablona_dotazy (id_radku, dotaz, popis)
  VALUES (2, 'SELECT SUM(hodnota) FROM hodnoty
              WHERE kod LIKE (''AB%'')', 'Suma za to, kde kód začíná A')
  INSERT INTO sablona_dotazy (id_radku, dotaz, popis)
  VALUES (3, 'SELECT SUM(hodnota) FROM hodnoty
              WHERE kod IN (''A'',''B'',''C'')', 'Suma za kód A, B a C')
  INSERT INTO sablona_dotazy (id_radku, dotaz, popis)
  VALUES (3, 'SELECT hodnota FROM hodnoty
              WHERE kod IN (BC,CD)', 'Chybně napsaný dotaz')
END
-- SELECT * FROM sablona_dotazy

Vytvoření procedury zpracuj_dotaz

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'zpracuj_dotazy')
DROP PROCEDURE zpracuj_dotazy
 
GO
 
CREATE PROCEDURE zpracuj_dotazy AS
 
DECLARE @id_radku int
DECLARE @dotaz varchar(MAX), @popis varchar(255)
DECLARE @castka money
 
DELETE FROM vysledne_castky
 
DECLARE radky CURSOR FOR
  SELECT id_radku, dotaz, popis FROM sablona_dotazy
OPEN radky
FETCH NEXT FROM radky INTO @id_radku, @dotaz, @popis
WHILE @@FETCH_STATUS = 0 BEGIN
  DELETE FROM pomocna_castka
  BEGIN TRY
    IF ISNULL(@dotaz,'') <> '' BEGIN
      INSERT INTO pomocna_castka (castka) EXEC (@dotaz)
    END
  END TRY
  BEGIN CATCH 
    INSERT INTO error_log (datum, id_radku, dotaz, chyba)
    VALUES (GETDATE(), @id_radku, @dotaz, ERROR_MESSAGE() )
  END CATCH
  SET @castka = (SELECT castka FROM pomocna_castka)
  INSERT INTO vysledne_castky (id_radku, popis, castka)
  VALUES (@id_radku, @popis, @castka)
  FETCH NEXT FROM radky INTO @id_radku, @dotaz, @popis
END
CLOSE radky
DEALLOCATE radky
 
GO

Spuštění procedury a zobrazení výsledků

1
2
3
4
5
6
7
8
EXEC zpracuj_dotazy
 
SELECT * FROM hodnoty
SELECT * FROM pomocna_castka
SELECT * FROM sablona_dotazy
SELECT * FROM vysledne_castky
-- DELETE FROM error_log
SELECT * FROM error_log

Závěr

Po vytvoření takovéto logiky v databázi stačí v aplikaci zavolat proceduru zpracuj_dotazy, která provede celý výpočet. Následně musí aplikace čerpat výsledky z tabulky vysledne_castky. Proceduru je možno upravit i tak, aby rovnou vracela výsledky.

KategorieProgramování Štítky:
1 bod2 body3 body4 body5 bodů6 bodů7 bodů (5x hlasováno, průměr: 7,00 ze 7)
Loading...Loading...
  1. 23.10.2012 na 20:46 | #1

    @AndreC Pokud to co píšeš je od srdce pravda a komentář není pouze reklama, tak mě toto sdělení velice těší.

  2. 23.10.2012 na 15:29 | #2

    Dík za návod, rozhodně mi to pomohlo! :)

  1. Žádné zpětné odkazy
  • RSS
  • Twitter
  • Facebook
  • LinkedIn
  • Picasa
  • Flickr
  • YouTube