Docstoc

Transact-SQL

Document Sample
Transact-SQL Powered By Docstoc
					Funkce Transact-SQL
                  Výběr

              Filip Čálek
Souhrn
 Funkce T-SQL
   Datum a čas
   Práce s řetězci


 Konverze mezi typy
Funkce – Datumčasu čas
 Funkce pro získání data a
                            a
   Tyto funkce můžeme rozdělit na funkce s větší
    přesností a funkce s menší přesností.

     F s větší přesností(ns)   F s menší přesností(ms)

     SYSDATETIME               CURRENT_TIMESTAMP

     SYSDATETIMEOFFSET         GETDATE

     SYSUTCDATETIME            GETUTCDATE


     Rozdíly si ukážeme na následujícím příkladu.
Funkce – Datum a čas
                                  Výstup:
  SELECT    SYSDATETIME()         2010-03-09 16:57:41.9156594
           ,SYSDATETIMEOFFSET()   2010-03-09 16:57:41.9156594 +01:00
           ,SYSUTCDATETIME()      2010-03-09 15:57:41.9156594

           ,CURRENT_TIMESTAMP     2010-03-09 16:57:41.913
           ,GETDATE()             2010-03-09 16:57:41.913
           ,GETUTCDATE() ;        2010-03-09 15:57:41.913


    Z ukázky je patrné, že v prvních třech případech
    se jedná o funkce s větší přesností.
    Je nutné dále rozlišit funkce, které vrací čas
    platný na vašem počítači a nebo GMT.
Funkce – Datum a čas
 Parametry funkcí
   První parametr těchto funkcí bývá tzv. datepart:
     Year, month, day, hour, minute atd.
     Definujeme jaká hodnota se bude vracet.


   Další parametry se odvíjí dle funkce.
     Jeden z dalších parametrů načítá datum a čas ve formátu
      datetime.
        Např. 2010-03-09, nebo 2010/03/09. může i obsahovat
         proměnnou, případně funkci Getdate().
Funkce – Datum a čas
 DATENAME/DATEPART/DATEDIFF
  DATENAME vrací nvarchar hodnotu z konkrétního
   data/času. Např. název měsíce.
    DATENAME(datepart,datetime)
  DATEPART funguje shodně akorát vrací číselnou
   hodnotu.
  DATEDIFF vrací číselnou hodnotu a sice rozdíl mezi tzv.
   startdate a enddate ve tvaru datetime.
    DATEDIFF(datepart,startdate,enddate)
Funkce – Datum a čas
 Příklady:
 DECLARE @datum datetime;
 SET @datum=GETDATE();                    Den     Mesic     Rok
 SET LANGUAGE Czech;                      9       březen    2010
 SELECT DATENAME(DAY, @datum) AS 'Den',
 DATENAME(MONTH, @datum) AS 'Mesic',
 DATENAME(YEAR,@datum) AS 'Rok';
…….
 SELECT DATENAME(MONTH, @datum) AS
  'Mesic_N’,                               Mesic_N Mesic_P
                                           březen  3
 DATEPART(MONTH, @datum) AS 'Mesic_P’;
Funkce – Datum a čas
 Příklady:
 SELECT DATEDIFF(YEAR,'2005', '2010’)              Rozdil
                                                    5
 as ‚Rozdil';


 SELECT
 DATEDIFF(millisecond, GETDATE(), SYSDATETIME())   Pocet ms
                                                    3
 as 'Pocet ms';
Funkce – Datum a čas
 DATEADD/SET LANGUAGE
  DATEADD upraví datum/čas o určitý námi zvolený
   interval. Např. posune měsíc o jedna,čili z ledna se
   stane únor.
    DATEADD(datepart,cislo,datetime)
    Druhý parametr „cislo“ určuje velikost intervalu.


  SET LANGUAGE upraví nejen názvy měsíce, ale i
   datetime format.
Funkce – Datum a čas
   DECLARE @cislo int;
   SET @cislo=0;
   WHILE @cislo<12
   BEGIN
         SELECT CONVERT(varchar,DATEADD(MONTH,@cislo,'2010-1-31'),104)
         SET @cislo=@cislo+1;
   END;



   31.01.2010
                                Z příkladu je patrné, že počet dnů je
   28.02.2010
                                automaticky upraven, aby odpovídal
   31.03.2010
                                skutečnosti.
   30.04.2010
   …
Funkce – práce s řetězci
 ASCII/CHAR/CHARINDEX/DATALENGTH
 ASCII převádí char na číselnou ASCII hodnotu.

 CHAR naopak převádí z ASCII kódu na znak.

 CHARINDEX vyhledává char nebo podřetězec v
  řetězci a sice, že nalezne jeho první znak.

 DATALENGTH vrací hodnotu odpovídající délce
  řetězce.
Funkce – práce s řetězci
 NCHAR/UNICODE/SUBSTRING

 V případě převádění do unicode a zpět použijeme
  funkce UNICODE/NCHAR

 SUBSTRING vrací část řetězce. Většinou se používá
  jako pomocná funkce k jiným funkcím, jako např.
  ASCII,UNICODE…
   SUBSTRING(retezec, index, rozsah)
Funkce – práce s řetězci
   DECLARE @cislo int;
   DECLARE @znak int;
   SET @cislo=0;                                         97     a
   WHILE @cislo<=DATALENGTH('ahoj')                      104    h
   BEGIN                                                 111    o
         SET @cislo=@cislo+1;                            106    j
                                                          NULL   NULL
         SET @znak=ASCII(SUBSTRING('ahoj', @cislo,1));
         SELECT @znak,
                   CHAR(@znak)
   END;



 Převedeme v cyklu „ahoj“ do ASCII a pak za
  pomoci funkce CHAR zase zpět na znaky.
Funkce – práce s řetězci
 SELECT SUBSTRING('AHOJ',2,2),
                                                       HO
      SUBSTRING('AHOJ',3,2),                          OJ
      SUBSTRING('AHOJ',1,3),                          AHO
      SUBSTRING('AHOJ',1,4);                          AHOJ

  Ukázka jak se chová SUBSTRING při změně parametrů.
Funkce – práce s řetězci
 DIFFERENCE/SOUNDEX
 DIFFERENCE porovnává dva řetězce a vrací číselnou
  hodnotu od 0 do 4.
   0 – největší možný rozdíl.
   4 – nejmenší nebo žádný rozdíl.
   Neporovnává přímo řetězce, ale kódy vytvořené pomocí
    funkce SOUNDEX – kód se skládá ze čtyř znaků. První
    obsahuje první char řetězce a dále tři číselné znaky.
   Např. SOUNDEX(‘Ahoj’) vrací kód A200.
Funkce – práce s řetězci
 select SOUNDEX('Ahoj') as 'S1',
                                               S1     S2     D
        SOUNDEX('Ahoj') as 'S2',              A200   A200   4
        DIFFERENCE('Ahoj','Ahoj') as 'D';

 select SOUNDEX('Ahoj') as 'S1',
                                               S1     S2     D
        SOUNDEX('Ahum') as 'S2',
                                               A200   A500   3
        DIFFERENCE('Ahoj','Ahum') as 'D';

 select SOUNDEX('Ahoj') as 'S1',
        SOUNDEX('Server') as 'S2',            S1     S2     D
                                               A200   S616   0
        DIFFERENCE('Ahoj','Server') as 'D';
Funkce – práce s řetězci
 SOUNDEX – stručné vysvětlení tzv. fonetického
  algoritmu.
   Tři číselné znaky ohodnocují souhlásky daného slova
    následujícím způsobem:
      b, f, p, v = 1               Soundex z AHOJ vrací A200,
                                   protože:
      c, g, j, k, q, s, x, z = 2
                                   A - je první znak.
      d, t = 3                     H - není nijak ohodnoceno
      l=4                          O – není souhláska
      m, n = 5                     J – ohodnoceno jako 2
      r=6                          Jelikož slovo dál nepokračuje,
                                   je kód doplněn nulami.
Funkce – práce s řetězci
 SOUNDEX – příklady
 SELECT SOUNDEX('robert')
 SELECT SOUNDEX('rupert')
  Vzhledem k tomu, že B a P má stejné ohodnocení, vychází nám v
  tomto případě stejný kód. R163

  V případě dvou nebo více stejně ohodnocených souhlásek po sobě je
  ohodnocena pouze první z nich. Řetězec GGGG by vrátil G200.
  Doplněním jiného znaku např.: GGPGG nám vyjde G212.

  Pokud soundex již ohodnotil tři souhlásky, tak případné další ignoruje.
Funkce – práce s řetězci
 LOWER/UPPER/LTRIM/RTRIM
 LOWER velké znaky převede na malé.

 UPPER naopak převede malé na velké.

 LTRIM odstraní mezery na začátku řetězce

 RTRIM odstraní mezery na konci řetězce.
Funkce – práce s řetězci
   DECLARE @Z CHAR(15);
   SET @Z='    ahoj';                    Bez upravy       Po uprave
                                               ahoj        ahoj
   SELECT @Z AS 'Bez upravy',
   LTRIM(@Z) AS 'Po uprave';



 SELECT LOWER(SUBSTRING('AHOJ', 1, 4))
                                          Lower    Upper
 AS Lower,
                                          ahoj     AHOJ
 UPPER(SUBSTRING('ahoj', 1, 4))
 AS Upper;

Funkce – práce s řetězci
 REPLACE/REPLICATE/REVERSE
 REPLACE může změnit námi zvoleny podřetězec za
  jiný. Můžeme tak ve větě změnit např. jedno slovo.

 REPLICATE zopakuje řetězec podle zvoleného čísla.

 REVERSE otočí pořadí znaků v řetězci.
Funkce – práce s řetězci
 SELECT REPLACE('Uceni je nuda!','nuda','zabava');   Uceni je zabava!

 SELECT REPLICATE('Ahoj ',5);                        Ahoj Ahoj Ahoj Ahoj Ahoj


 SELECT REVERSE('Ahoj'),
                                                      johA
      REVERSE('kobylamamalybok') ,                   kobylamamalybok
      REVERSE('12345');                              54321
Funkce – práce s řetězci
 STR/STUFF

 STR zaokrouhlí na jedno či více desetinných míst,
  případně na celá čísla.

 STUFF vloží jeden řetězec do druhého na určité místo.
  Také je možnost vymazat určitý počet znaků a na
  jejich místo vloží vkládaný řetězec.
Funkce – práce s řetězci
 SELECT STR(29.994, 5, 2),                 29.99
        STR(29.994,5,1);                    30.0



 DECLARE @auto char(20);
                                            Pekne            Osklive
 SET @auto='Mam auto';                     Mam pekne auto   Mam osklive auto
 SET @auto=STUFF(@auto, 4, 0, ' pekne');
 SELECT @auto as 'Pekne',
       STUFF(@auto,4,6,' osklive')
       as 'Osklive';
Konverze mezi typy
 CAST a CONVERT
   Základní funkce pro převody mezi datovými typy.
   Tyto funkce jsou funkčností téměř shodné. Liší se
    způsob zápisu a funkce CONVERT na rozdíl od funkce
    CAST umožňuje definování stylu.
   Styl se definuje zejména u funkcí data a času.
    Konkrétně ovlivňuje formát data/času.
Konverze mezi typy
 DECLARE @cislo decimal(10,3);
 SET @cislo = 193.57;

 SELECT CAST(@cislo AS int) as 'int',                int   decimal
                                                      193   193.570
      CAST (@cislo as decimal(10,3)) as 'decimal';

 SELECT CONVERT(int, @cislo) as 'int',               int   decimal
      CONVERT(decimal(10,3),@cislo) as 'decimal';    193   193.570



 V tomto případě je funkce shodná.
Konverze mezi typy
 Podívejme se na rozdíl mezi CAST a CONVERT v
  případě konverze datetime.

   DECLARE @datum datetime;
   SET @datum=GETDATE();
   SELECT CAST( @datum as varchar);
   SELECT CONVERT(varchar,@datum,109);



      Výstup bude již jiný kvůli stylu.
      Mar 3 2010 2:40PM
      Mar 3 2010 2:40:14:897PM
      Jak již bylo řečeno, styl se definuje pouze funkcí CONVERT.
Konverze mezi typy
                           Základní styly pro formát
                           data/času
                             -    Styl             Standard                  Výstup
Toto je výčet základních
typů stylu. Je jich          0   0 / 100 Default              mon dd yyyy hh:miAM (or PM)

mnohem více, ale většina     1   101     U.S.                 mm/dd/yyyy

je již více či méně          2   102     ANSI                 yy.mm.dd

odvozena z těchto            3   103     British/French       dd/mm/yyyy

prvních.                     4   104     German               dd.mm.yy

                             5   105     Italian              dd-mm-yy
Pokud styl nevyplníme,       6   106     -                    dd mon yy
automaticky se dosadí
                             7   107     -                    Mon dd, yy
základní „100“.
                             8   108     -                    hh:mi:ss

                             9   109     Default + ms         mon dd yyyy hh:mi:ss:mmmAM (or PM)
Konverze mezi typy
 Pár ukázek použití stylu.
   Pokud chceme pouze čas:
     DECLARE @datum time;
     SET @datum=GETDATE();
     SELECT CONVERT(varchar,@datum,100);   4:34PM
     SELECT CONVERT(varchar,@datum,108);   16:34:32

   Případně pouze datum:
   DECLARE @datum datetime;
   SET @datum=GETDATE();
                                            03/10/2010
   SELECT CONVERT(varchar,@datum,101),
                                            2010.03.10
     CONVERT(varchar,@datum,102),          10/03/2010
     CONVERT(varchar,@datum,103),          10.03.2010
     CONVERT(varchar,@datum,104);
Konverze mezi typy
 Možné problémy
 Občas konverze není možná z důvodu nevyhovujícího
  datového typu.
 DECLARE @hm varchar(10);                          V tomto případě
 SET @hm='125.25'                                  konverze nelze
 SELECT CAST(@hm as int);                          provést.


 DECLARE @hm varchar(10);                          Proto je nutné převést
 SET @hm='125.25'                                  varchar nejprve na
                                                    decimal a teprve pak
 SELECT CAST(CAST(@hm as decimal(10,5)) as int);
                                                    na int.
                                                    Výsledek: 125
Závěr
 Použitá literatura
   http://msdn.microsoft.com



                       Děkuji za pozornost

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:11/4/2013
language:Czech
pages:31