Tworzenie procedur i funkcji w SQL


Procedury są bardzo przydatnym elementem t-sql, pozwalają zdefiniować dowolne zapytanie, insert, update na serwerze sql i wywołać je za pomocą komendy EXEC nazwa_procedury
Procedury mogą być tworzone na stałe lub jako czasowe działające w czasie trawania sesji tak jak tabele tymczasowe. Składnia polcenie CREATE PROCEDURE
CREATE PROCEDURE procedure_name { @parameter data_type } 
	AS 
	{transact sql code}


Przykład:
CREATE PROCEDURE [Raport] @miesiac int
AS
SELECT [Id]
      ,[Produktid]
      ,[Miastatid]
      ,[Ilosc]
      ,[Koszt]
      ,[DataZamowienia]
  FROM [trening].[dbo].[sprzedarz]
  WHERE
  MONTH([DataZamowienia]) = @miesiac

Porcedura w tym przpadku pozwala na wyświetlanie sprzedaży w podanym w parametrze miesiacu. Aby wykonać tak zdefiniowaną procedurę należy wpisać jedynie
EXEC Raport 1
go

I już mamy na naszym ekranie wynik z w/w "select". Jeśli chcemy dokonać modyfikacji, lub usunąć procedurę używamy DROP, ALTER. Pamiętać należy że serwer zapamiętuje na stałe zdefiniowaną procedurę.

W T-SQL możemy stosować jak w każdym jezyku programowania instrukcje sterujące takie jak IF, ELSE, WHILE.

CREATE PROCEDURE [Raport] @miesiac int
AS
IF (select max(MONTH([DataZamowienia])) from [sprzedarz]) < @miesiac
BEGIN
SELECT [Id]
      ,[Produktid]
      ,[Miastatid]
      ,[Ilosc]
      ,[Koszt]
      ,[DataZamowienia]
  FROM [trening].[dbo].[sprzedarz]
  WHERE
  MONTH([DataZamowienia]) = @miesiac
END
ELSE
	BEGIN
		SELECT 'BRAK danych'
	END

Możliwe jest również twożenie procedór czasowych, wystarczy tak jak w przypadku tabel poprzedzić nazwę "#"
CREATE PROCEDURE [#Raport] @miesiac int
AS
IF (select max(MONTH([DataZamowienia])) from [sprzedarz]) < @miesiac
BEGIN
SELECT [Id]
      ,[Produktid]
      ,[Miastatid]
      ,[Ilosc]
      ,[Koszt]
      ,[DataZamowienia]
  FROM [trening].[dbo].[sprzedarz]
  WHERE
  MONTH([DataZamowienia]) = @miesiac
END
ELSE
	BEGIN
		SELECT 'BRAK danych'
	END

Procedury posiadają dwa typy parametrów: wejsci IN oraz wyjścia OUTPUT. Dzięki nim możemu np pobrać wartość z procedury.
CREATE PROCEDURE test @input  VARCHAR(10),
                 @output VARCHAR(20) output
AS
  BEGIN
      
            SET @output = @input + ' output';

            RETURN;
        END
  END

DECLARE @get_output VARCHAR(20);

EXEC test
  'input ',
  @get_output output

SELECT @get_output 

Tworzenie funkcji w MS SQL


Zajmijmy się teraz tworzeniem funkcji w t-sql. Możemy tworzyć funkcje użytkownika które zwracają skalarne lub tablicowe wartości. Funkcji możemy używać w widokach, w innych funkcja, w aplikacjach oraz procedurach. W funkcjach nie możemy używać poleceń DML.
Składnia CREATE FUNCTION
CREATE FUNCTION function_name 
( @parameter_name parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END

Przykład:
CREATE FUNCTION czysta_data (@DATE smalldatetime)  
RETURNS smalldatetime 
AS 
BEGIN 
declare @return as smalldatetime 

  set @return =  (convert(smalldatetime,convert(nvarchar(11),left(@DATE,11),120),120) )
 
return @return 
END 

Powyższy przykład demonstruje w jaki sposób stworzyć funkcje czysta data, czyli datę bez godziny i minuty.
Możemy tworzyć również zmienne tablicowe niezbedne jest tylko zadeklarowanie typu TABLE.
create table dane (lp int identity(1,1), nazwa nvarchar(30), flaga int default 0)
insert into dane (nazwa) values ('a'),('b'),('c'),('d'),('e')
insert into dane (nazwa,flaga) values ('1',1),('2',1),('3',1),('4',1),('5',1)

GO
 
CREATE FUNCTION dbo.LookByFName(@flaga bit)
RETURNS TABLE
AS
RETURN select * from (select lp,nazwa,flaga from dane where flaga=@flaga ) as subk
 
go
 
select * from  dbo.LookByFName(1)

drop function dbo.LookByFName
drop table dane

Użycie zmiennych w T-SQL


Zmienne są bardzo przydatne podczas tworzenia procedur, kursorów, oraz czasem zwykłych zapytań aby zadeklarować zmienną używamy polecenia DECLARE i poprzedzamy nazwę zmiennej "@", następnie po AS deklarujemy jej typ.
DECLARE @dana as INT
SET @dana = 1
SELECT @dana

Przykład powyżej spowoduje wyświetlenie jako wynik wartości 1. Kolejne nowe polecenie to SET służy ono do nadania wartości dla zmiennej. Zmiennej możemy użyć nie tylko w selekcie ale również w warunkach.
DECLARE @dana as INT
SET @dana = 30
Select koszty, miesiace FROM t_dane WHERE koszty >= @dana 

Możemy również wykorzystać zmienne tablicowe są to tabele istniejące podczas wykonywania polcenia, nie tworzyć na nich indeksów w starczych werjach z wyjątkiem deklaracji. Zmienią deklarujemy w ten sam sposób co zwykłą zmienią DECLARE @dana następnie TABLE i w nawiasach nazwy kolumn i ich typy)
DECLARE @dana TABLE (koszty int, miesiac char(10));

INSER INTO @dane (koszty, miesiac)  
SELECT  
Koszty, 
MONTH([DataZamowienia])
FROM 
[sprzedarz]
 ---kolejny selekt---
SELECT * FROM @dane

Poniżej przykład jak tworzyć clustered index na zmieniej tablicowej.
DECLARE @TEMPTABLE TABLE (
  [ID]   [INT] NOT NULL PRIMARY KEY,
  [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL,
  UNIQUE NONCLUSTERED ([Name], [ID]) 
  ) 

Teraz przeanalizujmy powyższy kod. Na początku zadeklarowaliśmy zmienną tablicową @dane, jej kolumny i ich typy, przypadku zmiennej skalarnej nie możemy użyć SET ale możemy umieścić dane w tabeli przy użyciu INSERT INTO wyniku z poniższego zapytania. Użyłem wpisu ---kolejny selekt--- specjalnie by pokazać jak wprowadza się komentarze w t-sql "--" oznacza komentarz lub /* wykomentowany tekst */. Ostatni SELECT powoduje wyświetlenie zawartości tabeli @dane. Uwaga po zakończeniu ostatniego select wartość zmiennej jest wymazywana.








SQL-KURSY.pl poleca:

Książki

Copyright 2010-2011mariuszhk@op.pl

obob ob

Valid HTML 4.01 Transitional

Strona internetowa wykorzystuje pliki cookie zapisywane w pamięci przeglądarki internetowej.

OK