Wykład 5
• funkcje i procedury pamiętane
• widoki (perspektywy)
• wyzwalacze
2016-12-15 Bazy danych-1 W5 2
Funkcje i procedury pamiętane
Następujące polecenie tworzy zestawienie zawierające
informację o tym ilu jest na naszej przykładowej uczelni
studentów, a ile studentek.
W poleceniu tym pojawiła się tzw. funkcja pamiętana.
Język SQL umożliwia pamiętanie wraz z danymi (w jednej
z systemowych tabel bazy) funkcji i procedur definiowanych przez użytkowników systemu.
Funkcje i procedury pamiętane są związane z bazą, a nie
z aplikacją. Mogą być wykorzystywane przez różne aplikacje,
które mają dostęp do bazy. Dalej pokazano ciąg poleceń SQL,
które definiują i tworzą w pliku bazowym funkcję PlecStud .
2016-12-15 Bazy danych-1 W5 4
CREATE FUNCTION [dbo].[PlecStud](@p char(1)) RETURNS char(10)
AS
BEGIN
declare @plec char(10);
if @p = 'K'
set @plec = 'dziewczyna' else set @plec = 'chłopak' --end;
return @plec;
END
2016-12-15 Bazy danych-1 W5 6
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to compute the return value here SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
-- Return the result of the function RETURN <@ResultVar, sysname, @Result>
END
Oprócz omówionych wcześniej funkcji zagregowanychoraz funkcji pamiętanych w systemie zarządzania bazą danych wykorzystującym język SQL istnieje zawsze pewna liczba funkcji wbudowanych.
Umożliwiają one wykonywanie najczęściej spotykanych w zastosowaniach praktycznych operacji na danych.
Następujące przykładowe polecenie pozwala znaleźć informację o liczbie pracowników uczelni zatrudnionych na poszczególnych stanowiskach oraz o ich zarobkach
minimalnych, średnich i maksymalnych.
2016-12-15 Bazy danych-1 W5 8
Polecenie to łączy tabele studenci, rejestr, punkty i pozwala sporządzić zestawienie liczby zaliczeń poszczególnych studentów z poszczególnych przedmiotów, minimalnych, średnich i maksymalnych uzyskanych ocen.
Funkcja ceiling jest funkcją zdefiniowaną w SQL, która zaokrągla
argument do najbliższej liczby całkowitej nie mniejszej niż wartość argumentu.
2016-12-15 Bazy danych-1 W5 10
Definicja funkcji NazwiskoImie
Procedura składowana/pamiętana (ang. stored procedure) jest nazwanym zbiorem zapytań w języku SQL, który jest przechowywany na serwerze (w SZBD) i jest kompilowany przy pierwszym wykonaniu.
Procedury wnoszą do środowiska serwera baz danych przetwarzanie warunkowe i możliwości programistyczne.
W SZBD wykonanie dowolnego fragmentu kodu języka SQL wiąże się z pewnym ciągiem procesów - począwszy od sprawdzenia składni aż do kompilacji i wykonania.
Proces wykonywania zapytania SQL przez SZBD
1. Kod zapytania zostaje sprawdzony pod względem poprawności składni (kontrola poprawności semantycznej - czy kod nie odwołuje się do nieistniejących obiektów lub używa nieistniejących poleceń oraz czy jego składnia jest poprawna).
2. Następnie kod jest rozdzielany na fragmenty (znaczniki) interpretowane przez SZBD. Proces ten nazywa się to ang. parsing.
3. Następnie SZBD standaryzuje wyodrębnione części kodu, tzn. zapisuje je w jednoznacznej postaci (usuwając niepotrzebne znaczniki).
4. Kolejnym etapem jest optymalizacja - każde zapytanie może posiadać wiele przygotowanych tzw.
planów wykonania (ang. execution plan). MS SQL Server posiada wewnętrzny proces (Query
Optimizer), który wybiera optymalny sposób dostępu do danych - taki plan wykonania zapytania, w którym serwer będzie przeszukiwał najmniejszą liczbę stron danych. Na optymalizację
szczególny wpływ ma struktura indeksów oraz sposób łączenia tabel.
5. Następuje kompilacja zapytania wg optymalnego planu wykonania i wykonanie skompilowanego zapytania.
6. Wyniki działania zapytania są zwracane do klienta.
2016-12-15 Bazy danych-1 W5 12
Wykonywanie procedur składowanych
Wykonywanie procedur składowanych odbywa się inaczej niż wykonywanie pojedynczych zapytań SQL. Schemat tworzenia i pierwszego wykonania procedury na przykładzie MS SQL Server.
1. Programista bazy danych tworzy definicję procedury składowanej - tzn. wykonuje polecenie CREATE PROCEDURE.
2. Kod procedury jest sprawdzany pod względem składni.
3. Nazwa procedury i jej kod (tzw. ciało) są zapisywane do odpowiednich tabel systemowych bazy danych (sysobjects oraz syscomments).
4. Użytkownik wywołuje procedurę z odpowiednimi parametrami za pomocą polecenia EXEC.
5. Dalej następuje właściwe wykonanie procedury - optymalizacja planu wykonania i kompilacja.
6. Skompilowany optymalny plan wykonania jest zapisywany w tzw. cache'u procedur.
W MS SQL Server przy kontroli poprawności kodu procedury w trakcie jej tworzenia serwer nie sprawdza, czy istnieją obiekty (tabele, widoki), do których procedura się odwołuje. Sprawdzenie to następuje dopiero przy wykonaniu procedury (w przypadku odwołania do nieistniejącego obiektu procedura zgłosi błąd).
Definicja procedury dopisującej do tabeli Studenci nowego studenta:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[NowyStudent](
-- Add the parameters for the stored procedure here
@Nr int, @Naz char(35), @Im char(35), @adr char(60), @du date, @p char(1)) AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
-- SET NOCOUNT ON;
insert into Student.dbo.Studenci (num_stud, nazwisko, imie, adres, data_ur, plec) values (@Nr, @Naz, @Im, @adr, @du, @p)
END
2016-12-15 Bazy danych-1 W5 14
Przykładowe wywołanie tej procedury:
2016-12-15 Bazy danych-1 W5 16
2. Dopisywanie nowego studenta do tabeli studenci – do kolumny plec wstawia M lub K w zależności od ostatniej litery imienia
2016-12-15 Bazy danych-1 W5 18
Widoki (perspektywy)
Są to tabele wirtualne – tworzone i przechowywane w bazie danych, w których występują odwołania do na ogół więcej niż jednej tabeli.
Widok (perspektywa) to logiczny byt (obiekt), osadzony na serwerze bazy danych.
Umożliwia dostęp do podzbioru kolumn i wierszy tabeli lub tabel na podstawie zapytania w języku SQL, które stanowi część definicji tego obiektu.
Do widoku jako źródła danych należy odwoływać się identycznie jak do tabeli.
Operacje wstawiania, modyfikowania oraz usuwania rekordów nie zawsze są możliwe (np. w sytuacji gdy widok udostępnia część kolumn dwóch tabel A oraz B bez kolumny z kluczem głównym tabeli B).
W niektórych SZBD widok służy wyłącznie do pobierania wyników i ograniczania dostępu do danych.
2016-12-15 Bazy danych-1 W5 20
Definicja przykładowego widoku łączącego tabele Studenci i Rejestr
Przykład użycia widoku Studenci_kursy
Perspektywalub widok(ang. view) jest wirtualną tabelą - odwzorowaniem globalnego schematu bazy danych na schemat „zewnętrzny”, przystosowany do potrzeb i przyzwyczajeń konkretnego użytkownika.
Perspektywy stosuje się, aby uprościć (sobie lub użytkownikowi) życie. Powodów stosowania perspektyw :
•Dążenie do uproszczenia z punktu widzenia użytkownika modeli pojęciowych – dzięki temu można znacznie uprościć schemat bazy, a tym samym ułatwić użytkownikowi dostęp do danych.
•Dostosowanie się do punktu widzenia i terminologii dziedziny zastosowań BD – zmiana schematu wprowadzana przez perspektywę może być wykorzystana na przykład do dostosowania nazw tabel i kolumn do języka, którym posługuje się użytkownik.
•Ograniczenie dostępu do obiektów – perspektywy powodują ukrycie przez użytkownikiem końcowym prawdziwego schematu bazy danych. Może to mieć również pozytywny wpływ na poprawę bezpieczeństwa danych, gdyż użytkownik nie ma bezpośredniego dostępu do schematu rzeczywistego.
•Współdziałanie systemów heterogenicznych (wspólny schemat) – w wypadku systemów
rozproszonych, zbudowanych z różnych systemów baz danych, perspektywa może pomóc w ukryciu różnic.
•Przystosowanie starszych systemów do nowszych technologii i wymagań. Z punktu widzenia procesów perspektywa powinna być przezroczysta, to znaczy, że można na niej wykonywać takie same operacje, jak na rzeczywistych tabelach. Warunek przezroczystości perspektyw jest bardzo trudny do spełnienia, gdyż dla pewnych odwzorowań danych przyjęte środki definicji perspektyw mogą okazać się niewystarczające.
2016-12-15 Bazy danych-1 W5 22
Korzyści wynikające ze stosowania widoków:
- wygoda
- ukrywanie efektów normalizacji - ograniczanie dostępu do danych
- tworzenie dodatkowej warstwy abstrakcji
Wyzwalacze
Wyzwalacze (ang. Triggers) są procedurami składowymi uruchamianymi
automatycznie w wyniku zaistnienia określonego zdarzenia. Zdarzeniami jakie mogą wywoływać uruchamianie wyzwalaczy są polecenia modyfikujące tabele w bazie danych, modyfikujące bazy danych, a także inne zdarzenia systemowe w bazie danych. Zwykle wyzwalaczy używa się do sprawdzania poprawności
wprowadzanych danych, ale można za pomocą nich uruchomić dowolne
procedury. Programując wyzwalacze można wykorzystywać wszystkie wyrażenia SQL dostępne dla procedur składowych. Wyzwalacze nie powinny zwracać
danych. Wyzwalacze mogą służyć do stworzenia własnej obsługi błędów i
generowania swoich komunikatów, które mogą być później użyte w aplikacji
używającej określonej bazy danych.
2016-12-15 Bazy danych-1 W5 24
W systemie MS SQL Server istnieją trzy typy wyzwalaczy:
Wyzwalacze DML – uruchamiają się gdy użytkownik próbuje modyfikować dane przy pomocy zapytań języka DML (Data Manipulating Language). Są to polecenia INSERT, DELETE i UPDATE na tablicy lub widoku.
Wyzwalacze DDL – uruchamiają się gdy wywoływane są polecenia języka DDL (Data
Definition Language), takie jak polecenia CREATE, ALTER, DROP a także niektóre procedury systemowe.
Wyzwalacze Logon – uruchamiają się podczas zdarzenia LOGON, gdy użytkownik nawiązuje sesje z bazą danych.
Wyzwalacze DML mogą być inicjowane zdarzeniami modyfikującymi wiersze tabeli lub widoku (polecenia INSERT, DELETE, UPDATE). Wyzwalacze uruchamiają się niezależnie od tego czy dane polecenia mają wpływ na wiersze w tabeli lub widoku. Dla tabel można wywoływać wyzwalacze po i zamiast danego polecenia (AFTER i INSTEAD OF), natomiast dla widoków, tylko zamiast danego polecenia (INSTEAD OF).
Wyzwalacze DML służą do wymuszenia różnych reguł biznesowych, sprawdzania
integralności danych, tworzenia zapytań do innych tabel oraz uruchamiania dowolnych innych procedur jakie można napisać przy użyciu języka Transact-SQL.
Wyzwalacz jak i wyrażenie, które go wywołuje traktowane jest jako jedna transakcja i może być cofnięta z wnętrza wyzwalacza. Jeżeli zostanie wykryty poważny błąd (np. za mało miejsca na dysku) cała transakcja zostaje cofnięta.
Od wersji MS SQL Server 2005 możliwe jest uruchomienie wyzwalaczy po oraz zamiast poleceń INSERT, UPDATE i DELETE.
Wyzwalacze DML sprawdzają się wtedy, kiedy za pomocą zwykłych więzów integralności nie jesteśmy w stanie zapisać reguł jakie chcemy wprowadzić w bazie danych, np. reguły dotyczące wprowadzania danych w kolumnie mogą odnosić się do innych kolumn, co nie jest możliwe w przypadku klauzuli CHECK.
2016-12-15 Bazy danych-1 W5 26
Wyzwalacze DML korzystają z tabel logicznych (koncepcyjnych) deleted i inserted.
Tabele te przechowują dane, które zostały właśnie usunięte lub wstawione przez polecenie inicjujące wyzwalacz.
Informacje zawarte w tych tabelach można wykorzystać do sprawdzenia poprawności
operacji, która uruchomiła wyzwalacz. Tabele te najczęściej używane są podczas transakcji.
Gdy po sprawdzeniu okaże się, że dane są nieprawidłowe wystarczy cofnąć transakcję i wrócić do stanu przed wywołaniem operacji. Wyzwalacze są uruchamiane po sprawdzeniu więzów integralności, więc jeśli zapytanie, które wywołuje dany wyzwalacz wcześniej
spowoduje niepoprawność więzów spójności, to wyzwalacz nie uruchomi się w przypadku wyzwalacza wywoływanego z klauzulą AFTER lub wyzwalacz jest cofany jeśli był
wykonywany z klauzulą INSTEAD OF. Należy pamiętać, że dodanie nowego wyzwalacza dla danej tabeli powoduje automatyczne usunięcie wyzwalacza istniejącego, jeśli wywołują go te same zdarzenia.
2016-12-15 Bazy danych-1 W5 28
Wyzwalacze wiąże się z następującymi zdarzeniami:
INSERT - wyzwalacz jest wywoływany zawsze wtedy, gdy do "jego" tabeli wstawiany jest nowy wiersz,
DELETE – wtedy, gdy wiersz w odpowiedniej tabeli jest kasowany, UPDATE – gdy wiersz jest aktualizowany,
UPDATE OF lista_kolumn – wtedy gdy wiersz będzie lub został poprawiony tak, że zmieniły się wartości w kolumnach wymienionych na liście.
Wyzwalacze można definiować jako wierszowe albo rozkazowe. Wyzwalacze wierszowe mogą działać przed (INSTEAD OF) albo po (AFTER) modyfikacji przez wstawianie, poprawianie lub kasowanie wiersza. Wyzwalacze rozkazowe są wykonywane po wykonaniu całej operacji. Jeżeli pojawi się błąd podczas wykonywania wyzwalacza operacja, która go „zapaliła” kończy się
niepowodzeniem. Serwer może wówczas wysłać do użytkownika odpowiedni
komunikat.
Do tworzenia wyzwalaczy służy polecenie create trigger . Usuwanie wyzwalacza z bazy następuje po wydaniu polecenia drop trigger nazwa_wyzwalacza.
Poprawić definicję wyzwalacza można za pomocą polecenia alter trigger ...
Działanie wyzwalacza można zablokować (bez usuwania go z bazy) za pomocą polecenia
DISABLE TRIGGER nazwa_wyzwalacza ON własciciel.nazwa_tabeli
Nieaktywny wyzwalacz można uaktywnić poleceniem
ENABLE TRIGGER nazwa_wyzwalacza ON właściciel.nazwa_tabeli
Do przechowywania wyzwalaczy w bazie służą tabele SYS.TRIGGERS oraz
SYS.TRIGGER .
2016-12-15 Bazy danych-1 W5 30
Przykład definicji wyzwalacza i działania
2016-12-15 Bazy danych-1 W5 32
Pełna składnia polecenia tworzącego wyzwalacz:
CREATE TRIGGER wyzwalacz ON {tabela} [WITH ENCRYPTION] { {{FOR | AFTER | INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]} [WITH APPEND] [NOT FOR REPLICATION]
AS instrukcja_SQL […n] } | {(FOR | AFTER | INSTEAD OF) {[INSERT] [,] [UPDATE]}
[WITH APPEND] [NOT FOR REPLICATION]
AS {IF UPDATE (kolumna) [{AND | OR} UPDATE (kolumna)] […n] | IF (COLUMNS_UPDATED () {operator_binarny} maska_uaktualnienia) {operator_porównania} maska […n] } instrukcja_SQL […n] } }
gdzie:
AFTER(wartość domyślna) powoduje wywołanie wyzwalacza po pomyślnym zakończeniu wykonywania instrukcji użytkownika,
INSTEAD OFpowoduje wykonanie wyzwalacza zamiast oryginalnej instrukcji użytkownika. W rezultacie wszystkie zmiany wprowadzone przez użytkownika zostaną zastąpione zmianami wprowadzonymi przez wyzwalacz,
[DELETE] [INSERT] [UPDATE] określają instrukcję języka Transact-SQL, której wykonanie wywoła wyzwalacz, WITH APPENDjest opcją używaną wyłącznie w systemach SQL SERVER 6.5 i starszych,
NOT FOR REPLICATIONpowoduje, że wyzwalacz nie będzie wywoływany przez zmiany danych spowodowane ich replikacją,
IF (COLUMNS_UPDATED()) jest funkcją zwracającą w postaci ciągu bitów informacje o tym, w których kolumnach zmodyfikowano lub dodano dane,
operator_binarnyjest operatorem porównania wykorzystywanym do odczytania wyniku funkcji COLUMNS_UPDATED().
Lewy, najmniej znaczący bit wyniku, odpowiada pierwszej kolumnie tabeli,
maska_uaktualnieniaokreśla, za pomocą liczby całkowitej, które kolumny będą sprawdzane przy pomocy funkcji COLUMNS_UPDATED(),
operator_porównaniapozwala na sprawdzenie wszystkich (=) lub tylko wybranych (>), określonych poprzez maskę_uaktualnienia, kolumn,
maskaokreśla, za pomocą liczby całkowitej, dla których kolumn modyfikacja lub dodawanie danych wywoła określoną akcję.
IF UPDATEpowoduje sprawdzanie wykonania instrukcji UPDATElub INSERT, Wykonanie instrukcji DELETEnie spowoduje wykonania żadnej akcji,
2016-12-15 Bazy danych-1 W5 34
Przykłady:
Tworzymy wyzwalacz sprawdzający, czy zmodyfikowano zawartość wybranych kolumn:
I sprawdzamy jego działanie:
Na stronie
http://devproconnections.com/sql-server/reasons-avoid-triggers można zapoznać się z opinią na temat używania wyzwalaczy.
