10 sql przeglad

SQL

–

zyk baz danych

–

przegl

Tadeusz Pankowski

www.put.poznan.pl/~tadeusz.pankowski

SQL

historia

• SQL - "Structured Query Language" - język czwartej

generacji (4GL) wymowa: "es-kju-ell" lub "sequel [si:kwl]"

• SQL – język baz danych, strukturalny język zapytań, język

kwerend.

Język do definiowania, wyszukiwania i

administrowania danymi w relacyjnej bazie danej.

SQL

historia

1970, E.F.Codd, "

A relational model of data for large shared data

banks

", Communications of the ACM, Vol.13, No.6, 1970, podstawy

relacyjnego modelu danych

1974, SEQUEL "Structured English Query Language", D. Chamberlin

i in., IBM San Jose Research Laboratory, implementacja: SEQUEL-

XRM (1974-75)

1976-77, SEQUEL/2, implementacja i rozwój w ramach projektu

System R, IBM San Jose RL

1980-83, główne implementacje: ORACLE, DB2 (SQL/DS),

1986, SQL/86 - oficjalny standard ANSI (American National

Stanmdards Institute) oraz ISO (International Organization for

Standardization)

1992, SQL2 (lub SQL/92) - "International Standard ISO/IEC

9075:1992,

Database Language SQL

dalszy rozwój: SQL3 (SQL:1999), włącza idee podejścia

obiektowego, dane wielowymiarowe, heterogeniczne, XML, ...

Standaryzacja

zalety

•

redukcja kosztów szkolenia

•

przenośność aplikacji

•

wydłużenie żywotności aplikacji

•

komunikacja międzysystemowa,

interoperacyjno

ść

•

wygoda dla użytkownika, co robi system, a nie w jaki

sposób

•

wspólny interfejs do bazy danych

Standaryzacja

wady

•

ograniczenie kreatywności

•

SQL jest dość daleki od idealnego języka relacyjnego

•

braki samego SQL – wiele problemów pozostawiono jako

zależnych od implementacji "implementation

dependent", co utrudnia przenośność

•

złudzenie istnienia standardu (?)

Architektura aplikacji baz

danych

Oprogramowanie klienta

ADO

(ActiveX Data Objects)

ODBC

(Open Database Connectivity)

MS SQL

Server

ORACLE

Access

Warstwa

oprogramowania

klienta

Oprogramowanie standardowe

zapewniające wymianę danych

między serwerem i klientem:

Warstwa

oprogramowania

serwera

SQL

–

przeznaczenie

SQL = SQL2 = SQL/92
Uwaga: Używać będziemy notacji stosowanej w dialekcie języka SQL

systemu MS SQL Server, a więc języka Transact-SQL.

Główne funkcje zarządzania danymi:

•

definiowanie

•

manipulowanie,

•

administrowanie

Przeznaczony dla:

•

interakcyjnych użytkowników końcowych,

•

twórców programów użytkowych (aplikacji),

•

administratorów baz danych,

•

twórców oprogramowania specjalizowanego (narzędziowego,

systemów współpracujących z bazami danych, ...)

SQL

–

posta

polece

Zapytania w języku SQL mogą przybierać różną postać. Postać ta może

zależeć od:

Miejsca wydawania polecenia:

•

bezpośrednio w trybie interakcyjnym z terminala,

•

w oprogramowaniu serwera,

•

w oprogramowaniu kienta.

Kompletności posiadanej informacji w momencie formułowania

polecenia:

•

posiadamy pełną informację (w zapytaniu nie ma potrzeby używania

zmiennych),

•

używamy zmiennych do przekazywania i pobierania do/z zapytania

pewnych parametrów, ale postać zapytania jest ustalona,

•

używamy zmiennych do dynamicznego tworzenia postaci zapytania, a

więc zmienne mogą być zarówno parametry zapytania, jak również jego

struktura.

ne okre

lenie zapytania

Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)

Dochod(IDPrac, Rok, Kwota)

Wtrybie interakcyjnym, bezpośrednio z terminala

select Nazwisko, Placa

from

Pracownik

where Placa >= 2000

Wynikiem jest tabela o dwóch kolumnach: Nazwisko i Placa.

Do tabeli wynikowej należą wiersze, w których atrybut Placa

ma wartość większą lub równą 2000.

Z wykorzystaniem zmiennnych wierszowych (aliasów):

select P.Nazwisko, D.Rok, D.Kwota

from

Pracownik P, Dochod D

where P.IDPrac = D.IDPrac AND Rok > 1980

Zapytania sparametryzowane

Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)

Dochod(IDPrac, Rok, Kwota)

Wykorzystanie zmiennych wyjściowych w T-SQL (Transact-SQL):

declare @zm_placa money

select @zm_placa = Placa

from

Pracownik

where IDPrac = '00123'

W pierwszym wierszu deklarowana jest zmienna @zm_placa (nazwa

każdej zmiennej w języku Transact-SQL rozpoczyna się od znaku @) typu

money.

W drugim wierszu pod zmienną @zm_placa podstawiana jest wartość

kolumny Placa z ostatniego wiersza stanowiącego zbiór odpowiedzi na

zapytanie.

Zapytania sparametryzowane (c.d.)

Pracownik(IDPrac, Nazwisko, Placa, Stanowisko)

Dochod(IDPrac, Rok, Kwota)

Wykorzystanie zmiennych wejściowych i wyjściowych (T_SQL):

declare @IDPrac varchar(30)

declare @zm_placa money

set @IDPrac='00123'

select @zm_placa = Placa

from

Pracownik

where IDPrac = @IDPrac

Wartość zmienna @IDPrac określana jest przed wykonaniem zapytania.

Zauważmy, że dzięki znakowi @ system rozróżnia czy chodzi o nazwę

zmiennej @IDPrac, czy o nazwę kolumny IDPrac.

W obydwu powyższych przypadkach struktura zapytania jest znana.

Zapytania dynamiczne (T

SQL)

declare @zm_zapytanie varchar(100)

declare @zm_tabela varchar(100)

declare @zm_kolumny varchar(100)

set @zm_tabela = 'Pracownik'

set @zm_kolumny='Nazwisko, Placa'

set @zm_zapytanie='select ' + @zm_kolumny + ‘ from '+@zm_tabela

execute sp_sqlexec @zm_zapytanie

•

trzy pierwsze linie zawierają deklaracje zmiennych

•

w trzech kolejnych liniach następuje określenie wartości zmiennych,

tworzymy:

”select Nazwisko, Placa from Pracownik”

•

w ostatniej linii następuje wykonanie zapytania, procedury pamiętanej

sp_sqlexec, ktrórej parametr @zm_zapytanie przekazuje tekst

polecenia (zapytania, procedury)

Zapytania dynamiczne (T

SQL)

• W przypadku zapytań dynamicznych postać zapytania nie

jest znana na etapie kompilacji, w szczególności więc

system nie jest w stanie ani sprawdzić poprawności

składniowej zapytania, ani tym bardziej opracować

optymalnej strategii jego wykonania.

• Czynności te realizowane są dopiero na etapie wykonania,

co istotnie zmniejsza efektywność przetwarzania.

Zapytania dynamiczne (VB

ADO)

Zapytania dynamiczne (VB

ADO)

‘Podprogram tworzenia tabeli w bieżącej bazie danych

‘nazwa tabeli podawana jest w polu tekstowym NazwaTabeli i jest wykorzystana

‘do dynamicznego utworzenia polecenia SQL-owego CREATE TABLE

-----------------------------------------------------------------------------------------------------------

Private Sub TworzTabele_Click()
Dim c As ADODB.Command
On Error Resume Next
Set c = New ADODB.Command
Set c.ActiveConnection = db
c.CommandText = "Create Table " & NazwaTabeli.Text & " (KolA Char (2),KolB Int)"
c.CommandType = adCmdText
db.Errors.Clear
c.Execute
If db.Errors.Count > 0 Then

WriteError

Else

StatusBar1.SimpleText = "Tabela utworzona."

End If
End Sub

Definiowanie tabel

przyk

Definiowanie tabel

przyk

create table Dostawca (

NrDcy

char(3) primary key,

Nazwa

char(20),

Adres

char(20))

create table Magazyn (

NrMag

INT primary key,

Adres

char(20))

create table Towar (

NrTow

char(5) not null primary key,

Nazwa

char(20),

GrupaTow

char(3) not null,

CenaZak

money,

CenaSprz

money)

Definiowanie tabel

–

przyk

ad (c.d.)

create table Dostawa (

IdDost

int

identity(1,1),

NrDcy

char(3),

NrMag

int,

NrTow

char(5),

Data

datetime,

Ilosc

decimal(5),

constraint Kg_Dostawa primary key (IdDost),

constraint Ko_Dostawa_Dostawca

foreign key (NrDcy) references Dostawca,

constraint Ko_Dostawa_Towar

foreign key (NrTow) references Towar ,

constraint Ko_Dostawa_Magazyn

foreign key (NrMag) references Magazyn)

create table TowMag (

NrMag

int,

NrTow

char(5),

Stan

decimal(5),

primary key (NrMag, NrTow),

foreign key (NrMag) references Magazyn,

foreign key (NrTow) references Towar)

Definiowanie tabel

–

przyk

ad (c.d.)

1. W powyższych definicjach:

•

klucz główny składa się z kilku kolumn dlatego jego definicja

podana jest po definicji wszystkich kolumn, np: primary key

(NrMag, NrTow);

•

warunkom spójności bazy danych można nadać nazwy,

umieszczamy je wtedy we frazie constraint,

•

definiujemy zależności referencyjne przez określenie klucza

obcego foreign key oraz tabeli, od której klucza głównego ten

klucz obcy zależy, np: foreign key (NrMag) references Magazyn

oznacza, że każda wartość wprowadzana do kolumny

TowMag.NrMag musi występować jako wartość klucza głównego

w tabeli Magazyn.

2. Za pomocą create table tworzone są tablice bazowe

(ekstensjonalne), mające fizyczną reprezentację w bazie danych oraz

określamy niektóre warunki spójności bazy dnaych.

3. Tablice wirtualne (intencjonalne), tzw. widoki lub perspektywy (ang.

view) tworzone są za pomocą CREATE VIEW

Operowanie danymi

Cztery podstawowe operacje:

select

- wyszukiwanie,

insert

- dołączanie,

update

- aktualizacja,

delete

- usuwanie.

Przykłady operacji modyfikujących:

insert into Dostawa

values('10','100',1,'1996.11.26',3)

update Towar

set

CenaSprz = 1.1 * CenaSprz

where GrupaTow = 'AGD'

delete from Towar

whereCenaSprz < CenaZak

SELECT *

Problemy z SELECT *:

1. Wybiera wszystkie kolumny, ale w jakiej

kolejności? Kolejność kolumn w modelu
relacyjnym nie jest określona. Musimy się
więc odwoływać do poziomu fizycznego
określającego sposób pamiętania tabeli.

2. Jeśli za pomocą SELECT * został

zdefiniowany widok, to co się stanie jeśli do
bazowej tabeli dodam nową kolumnę? Ma
być ona włączana do widoku czy nie?

Co zadecydowało o sukcesie systemów relacyjnych baz
danych?

I think there are three elements of the System R prototype and its

deployment in test environments that had an effect on the eventual adoption

of relational data storage systems.

1.
The first one is the obvious one that everybody suggests: the invention of

a nonprocedural query specification was a tremendous simplification that

made it much easier to specify applications. No longer did you have to say

which index to use and which join method to use to get the job done.

This was a tremendous boon to application development.

2.
But the real reasons that the relational system won have nothing to do with the

relational model; they have to do with the fact that the early prototypes,

both System R and Ingres, supported ad hoc queries and online data

definition.
You could try out a query right away, rather than typing it into your application,

compiling the application, running it, and having a failure, usually a syntax error.

Even if there were no syntax errors, you still had the advantage that you could

look at the query answers and see if you had the right tuples.
Ad hoc online query execution was something that was unheard of in the

database community at that time, so this was a tremendous boon to

application developers and to people who were browsing the data.

3.
Maintaining the coherence between the metadata that the system

uses at run time to figure out where things are and which way's up and

which way's down and the actual use of the system.
So a difficult and rather successful effort in the System R project was to figure

out ways to synchronize the modification of metadata without

impacting or interrupting the ongoing work involving other data

objects.

Operowanie danymi

Operacja wyszukiwania:

select distinct W.NrTow, W.NrMag

from

Dostawa W

where NrDcy = '10' AND Ilosc > 2

distinct - usuwanie duplikatów

select W.NrDcy, D.Nazwa, T.Nazwa, T.CenaSprz

from

Dostawa W, Dostawca D, Towar T

where W.NrDcy = D.NrDcy AND W.NrTow = T.NrTow

Funkcje agreguj

count, sum, avg, max, min, count(*)

Liczba wszystkich dostawców:

select count(*) as Ld
from Dostawca

Liczba dostawców realizujących dostawy

select count(distinct W.NrDcy) as Ldd
from Dostawa W

bez distinct:

select count(W.NrDcy) AS Ldd
from Dostawa W

Ldd

Funkcje agreguj

ce (c.d.)

3. Sumaryczny wartość towaru 100 w cenach zakupu:

select

sum(T.CenaZak * TM.Stan) as W100

from

Towar T, TowMag TM

where

T.NrTow = TM.NrTow AND T.NrTow = '100'

W100

45 000

Funkcje agreguj

ce (c.d.)

Wartości towarów w poszczególnych grupach towarowych:

select

T.GrupaTow, sum(T.CenaZak * TM.Stan) as Wartość

from

Towar T, TowMag TM

where

T.NrTow = TM.NrTow

group by T.GrupaTow
having

GrupaTow <> 'AGD'

GrupaTow

Wartość

----------------- --------------

KOM 24 000.00

RTV 45 000.00

select

from

Towar T, TowMag TM

where

T.NrTow = TM.NrTow

Operacje z wykorzystaniem kursora

Kursor (ang. cursor) dostarcza mechanizmu dostępu do pojedynczych

wierszy wg zasady "jeden po drugim".

declare @zmNazwa char(20)
declare @zmCenaSprz Money
declare kurAGD cursor
for select Nazwa, CenaSprz

from Towar
where GrupaTow = 'AGD'

open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
while @@FETCH_STATUS = 0
begin

select @zmNazwa, @zmCenaSprz
fetch next from kurAGD into @zmNazwa, @zmCenaSprz

end
close kurAGD
deallocate kurAGD

Operacje z wykorzystaniem kursora

Aby przetwarzać dane za pomocą kursora należy:
• zadeklarować kursor DECLARE ... CURSOR i zdefiniować jego zakres

FOR SELECT,

• otworzyć kursor, OPEN - wykonywana jest operacja SELECT, kursor

wskazuje na miejsce bezpośrednio przed pierwszym wierszem w

utworzonym zbiorze wierszy (zakresie kursora),

• wykonać operację pobrania danych na kolejnym wierszu FETCH NEXT

FROM, pobierane dane można zapamiętać pod podanymi zmiennymi,

• pobieranie danych może być realizowane w pętli, różna od zera wartość

zmiennej (funkcji) globalnej @@FETCH_STATUS może kończyć

wykonywanie operacji pobierania danych,

• zmienne mogą być wykorzystywane w dowolnych operacjach,

wyrażenie SELECT @zmNazwa, @zmCenaSprz oznacza wypisanie

danych,

• w końcu kursor należy zamknąć CLOSE i dealokować DEALLOCATE.

Operacje pozycyjne: UPDATE i DELETE

declare @zmNazwa char(20)
declare @zmCenaSprz money
declare kurAGD cursor

for select Nazwa, CenaSprz

from Towar
where GrupaTow = 'AGD'

open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
while @@FETCH_STATUS = 0
begin

select @zmNazwa, @zmCenaSprz

update Towar set CenaSprz = CenaSprz + 1 where current of kurAGD

fetch next from kurAGD into @zmNazwa, @zmCenaSprz

end
close kurAGD
deallocate kurAGD

Widoki (

views

)

• Widoki są tabelami wirtualnymi (intencjonalnymi), nie mającymi

bezpośredniej reprezentacji w bazie danych. Zapamiętana jest jedynie

ich definicja.

• Definicja widoku wykorzystywana jest w momencie odwoływania się

do niego jak do każdej innej tabeli.

Widoki (

views

)

"Towary RTV w cenach sprzedaży "

create view RTV (NrTow, Nazwa, CenaSprz)

as select T.NrTow, T.Nazwa, T.CenaSprz

from Towar T

where T.GrupaTow = 'RTV‘

Definicja widoku wykorzystywana jest w momencie odwoływania się do

niego:

select

RTV.NrTow, RTV.Nazwa, RTV.CenaSprz

from

RTV

where

RTV.CenaSprz < 2000

Przekształcone jest do zapytania względem tabeli TOWAR:

select

RTV.NrTow, RTV.Nazwa, RTV.CenaSprz

from (select T.NrTow, T.Nazwa, T.CenaSprz

from Towar T

where T.GrupaTow = 'RTV' ) RTV

where RTV.CenaSprz < 2000

lub równoważnie:

select

T.NrTow, T.Nazwa, T.CenaSprz

from

Towar T

where

T.GrupaTow = 'RTV' and T.CenaSprz < 2000

Modyfikowalno

ść

widok

1. Jeśli widok jest tak zdefiniowany, że można

przetransformować operacje jego modyfikowania na

modyfikację tabel bazowych, to można go aktualizować.

2. Sytuacja taka występuje na przykład wtedy, gdy w

definicji kursora zawarto klucz główny tabel bazowych,

nad którymi jest definiowany i nie zawiera funkcji

agregujących ani słowa DISTINCT.

Modyfikowalno

ść

widok

w (c.d.)

W przypadku widoku RTV, jego definicja

create view RTV (NrTow, Nazwa, CenaSprz)
as

select T.NrTow, T.Nazwa, T.CenaSprz
from

Towar T

where T.GrupaTow = 'RTV'

zawiera klucz główny tabeli Towar. Można go więc modyfikować.
Wówczas polecenie:

update RTV
set CenaSprz = CenaSprz + 10

zostanie wykonane i spowoduje odpowiednią modyfikację tabeli Towar.

Modyfikowalno

ść

widok

w (c.d.)

Bardzo często widoki są niemodyfikowalne.
Przykłady niemodyfikowalnych widoków:

1. Widok zawierający łączną ilość poszczególnych towarów we

wszystkich dostawach:

create view TD(NrTow, IlDost)
as select W.NrTow, SUM(W.Ilosc)

from Dostawa W
group by W.NrTow

Widok jest niemodyfikowalny, gdyż zawiera funkcję agregującą.

Modyfikowalno

ść

widok

w (c.d.)

2. Widok Miasta definiuje pary (DAdres, MAdres), gdzie DAdres jest

adresem dostawcy, a MAdres jest adresem magazynu, do którego
dostawca dostarcza towary:

create view Miasta (DAdres, MAdres)
as select distinct D.Adres, M.Adres

from Dostawca D, Magazyn M, Dostawa W
where D.NrDcy = W.NrDcy and M.NrMag = W.NrMag

Widok ten jest niemodyfikowalny, gdyż zawiera słowo distinct.

Modyfikowalno

ść

widok

w (c.d.)

Rozważmy widok jak poprzednio, ale bez słowa distinct:

create view Miasta1 (DAdres, MAdres)

as select D.Adres, M.Adres

from Dostawca D, Magazyn M, Dostawa W

where D.NrDcy = W.NrDcy and M.NrMag = W.NrMag

Modyfikowalno

ść

widok

w (c.d.)

update Miasta1

set DAdres = 'Kraków'

where DAdres = 'Poznań'

Miasta1

Dostawca

Widok Miasta1 jest modyfikowalny, gdyż zawarte w jego definicji warunki na kluczach głównych
pozwalają dokonać odpowiednich modyfikacji na wyjściowych tabelach

Bezpiecze

stwo baz danych

Mechanizmy zapewnienia bezpieczeństwa:

•

uwierzytelnianie i autoryzacja:
(nazwa logowania, hasło)

→

użytkownik bazy danych

→

uprawnienia

•

operacje GRANT/REVOKE/DENY – przyznawanie,

odbieranie, odmawiane uprawnień,

•

wykorzystanie widoków (selektywne udostępnianie

danych),

•

warunki spójności bazy danych

•

archiwowanie i odtwarzanie

Bezpiecze

stwo baz danych (c.d.)

Przykład pokazuje, w jaki sposób tworzy się nowego użytkownika i nową rolę w systemie
SQL Server oraz w jaki sposób dodaje się użytkownika do istniejącej roli.

Tworzenie nowego konta logowania do systemu MS SQL Server umożliwiającego

użytkownikowi przyłączenie się do serwera:

EXEC sp_addlogin 'Piotr', 'piotr', ‘sklep'

'Piotr' jest nazwą użytkownika, 'piotr' jest hasłem, a ‘sklep' bazą danych.

Tworzenie nowego użytkownika (w tym przypadku nazwa użytkownika jest

identyczna z nazwą konta logowania):

EXEC sp_adduser 'Piotr', 'Piotr'

Tworzenie nowej roli:

EXEC sp_addrole 'Klient'

Dodanie użytkownika do roli:

EXEC sp_addrolemember 'Klient', 'Piotr'

Zdefiniowanie uprawnień:

grant SELECT on Pracownik to Klient
deny SELECT on Pracownik to Piotr

Odczytanie informacji o kontach logowania, użytkownikach i rolach (dostępne po

ponownym uruchomieniu SQL Servera):

sp_helplogins
sp_helpuser
sp_helprole

jno

ść

bazy danych

Spójność, niesprzeczność, integralność (ang. consistency, integrity
constraints).

Spójność oznacza poprawność, niesprzeczność danych w bazie
danych. Standard SQL zawiera środki do definiowania warunków
spójności (lub więzów integralności), np. w obrębie zdania CREATE
TABLE.

Każda próba naruszenia zdefiniowanych warunków spójności
(podczas modyfikacji bazy danych) zostaje udaremniona, operacja
ją podejmująca jest odrzucana, a baza danych pozostaje nie
zmieniona.

W systemie pamiętana jest więc baza danych (dane podstawowe)
oraz wiedza o bazie danych (dane opisujące struktury i warunki
spójności).

Rodzaje warunk

w sp

jno

Typ kolumny – typ może być standardowy lub pochodny zdefiniowany

przez użytkownika.

UNIQUE – dla określenia, że kolumna lub zestaw kolumn ma mieć

unikalną wartość w tabeli (jest kluczem potencjalnym - alternatywnym,

kandydującym – w tabeli), taki klucz może przyjmować wartości NULL.

PRIMARY KEY – specjalny przypadek UNIQUE dla zdefiniowania

klucza głównego – nie może przyjmować wartości NULL.

FOREIGN KEY - dla zdefiniowania zależności referencyjnych

(zależności odniesień). Wartość klucza obcego musi występować jako

wartość klucza w powoływanej tabeli.

CHECK - dla określenia, że kolumna lub zestaw kolumn (z jednej krotki)

mają mieć wartości spełniające określony warunek. Taka spójność

może być zdefiniowane jako reguła w wyrażeniu CREATE RULE.

Bardziej złożone warunki spójności można definiować za pomocą

procedur wyzwalanych w wyrażeniach CREATE TRIGGER. Procedury

te są automatycznie wyzwalane przy aktualizacji tabeli.

Programowanie SQL

Servera

¾ Dla wykonywania obliczeń, których nie można wyrazić za pomocą

pojedynczego wyrażenia w języku Transact-SQL (T-SQL), stosuje

się:

• systemowe procedury pamiętane (

system stored procedures

• paczki (wsady) (

batches

) i skrypty (

scripts

• funkcje (

functions

• procedury pamiętane (składowane) (

stored procedures

• procedury wyzwalane (trigery) (

triggers

• kursory (

cursors

Systemowe procedury pami

tane

Nazwy wszystkich systemowych procedur pamiętanych rozpoczynaj

ę od „sp_” lub „xp_”.

Systemowe procedury pami

ętana są prekompilowanymi zbiorami

wyra

żeń SQL-owych („sp_”) lub języka programowanie („xp_”).

Procedury typu „xp_” (extended stored procedures) s

ą to funkcje

zdefiniowane w bibliotekach do

łączanych dynamicznie (bibliotekach

DLL) i s

łużą do rozszerzenia funkcji SQL Servera. Aby zainstalować

tak

ą procedurę należy najpierw skopiować plik DLL do katalogu

zawieraj

ącego SQL Server – najczęścij jest to C:\Mssql\Binn, a

nast

ępnie wykonać systemową procedurę pamiętaną

sp_addextendedproc.

Systemowe procedury pami

tane

przyk

ady

Tworzenie typu PESEL opartego na standardowym typie danych varchar. Typ

nie może przyjmować wartości NULL, varchar(11) jest ujęte w apostrofy, gdyż

zawiera nawiasy:

USE master
EXEC sp_addtype PESEL, 'VARCHAR(11)', 'NOT NULL‘
¾

Odczytanie tekstu procedury wyzwalanej employee_insupd zawartej w bazie

danych pubs:

USE pubs
EXEC sp_helptext 'employee_insupd'
¾

Wykonanie prostego wyrażenia SELECT zawierającego wbudowany parametr

@level:

execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35

Systemowe procedury pami

tane

przyk

ady

Dodanie rozszerzonej procedury pamiętanej:

USE master
EXEC sp_addextendedproc xp_hello, 'xp_hello.dll'

Wykonuje podaną komendę systemu operacyjnego i zwraca wyjście jako zbiór

wierszy tekstowych:

xp_cmdshell 'dir *.exe'

Tworzenie wyrażenia INSERT:

DECLARE @ret_string varchar (255)
EXEC xp_sprintf @ret_string OUTPUT,
'INSERT INTO %s VALUES (%s, %s)', 'table1', '1', '2'
PRINT @ret_string
Wynik:
INSERT INTO table1 VALUES (1, 2)

Paczki (

batches

)

• Paczka stanowi grupę złożoną z jednej lub z wielu wyrażeń

Transact-SQLa. Standardowym końcem paczki jest polecenie GO

lub koniec pliku. Paczkę może też stanowić grupa „podświetlonych”

wyrażeń (przy korzystaniu z

Query Analysera

• SQL Server kompiluje paczkę w pojedynczą jednostkę wykonywalną

zwaną planem wykonania (

execution plan

• Wyrażenia tworzące plan wykonania wykonywane są jednorazowo

w jednym czasie. W przypadku wystąpienia błędu kompilacji, żadne

z wyrażeń w paczce nie zostanie wykonane.

• Zmienna zdefiniowana w paczce nie jest dostępna poza nią –

zmienne są lokalne w paczce.

• Nie wszystkie wyrażenia mogą występować w jednej paczce (np.

definiowanie i wywoływanie procedury).

Paczki i skrypty

¾ Przykład skryptu złożonego z dwóch paczek:

USE sklep
GO

-- wykonanie paczki, wybór bazy danych sklep

DECLARE @Powitanie VARCHAR(50)
SET @Powitanie = 'Witaj w sklepie!'
print @Powitanie
GO -- wykonanie paczki,
-- zmienna @Powitanie po GO nie jest już dostępna.

Funkcje

¾ Rodzaje funkcji:
• funkcje skalarne (

scalar user-defined functions

) – zwracaną

wartością jest pojedyncza liczba lub tekst, tj. wartość dowolnego

typu z wyjątkiem typów: text, ntext i image;

• proste funkcje tablicowe (

inline table-valued functions

) – zwracają

wartość typu TABLE, przy czym zwracana tabela jest określona za

pomocą pojedynczego wyrażenia SELECT (funkcje te nazywane są

sparametryzowanymi widokami);

• złożone funkcje tablicowe (

multi-statement table-valued functions

) –

zwracają zmienną typu TABLE, przy czym zwracana tabela jest

dowolną tabelą utworzoną w ciele funkcji.

Funkcje skalarne

Funkcja zwraca największy numer pracownika z podanego działu:

CREATE FUNCTION ostatni_prac_dzialu (@id int)
RETURNS int
BEGIN

declare @nr int
set @nr = (select max(IdPrac) from pracownik

where IdDzialu=@id)

RETURN @nr

END

Wykorzystanie funkcji:

select * from pracownik
where IdPrac = dbo.ostatni_prac_dzialu(1)

Proste funkcje tablicowe

Funkcja zwraca zbiór pracowników z działu o podanym identyfikatorze:

create function prac_dzialu(@id int)
RETURNS TABLE
as
RETURN(select * from pracownik where IdDzialu=@id)
go

Wykorzystanie funkcji (wywołanie funkcji występuje w roli tabeli):

select * from dbo.prac_dzialu(2)

lub bez dbo

select * from prac_dzialu(2)

one funkcje tablicowe

Funkcja zwraca listę dobrze zarabiających pracowników:
create function krezus(@z int)
RETURNS @lista table (id int PRIMARY KEY,

nazwisko varchar(22), dochod int)

as
begin

if @z > 5000
insert @lista select id_prac,nazwisko,zarobki

from pracownik1 where zarobki > @z
RETURN
end

Wykorzystanie funkcji:

select * from krezus(5500)
select * from krezus(1000) –- tabela pusta

Procedury pami

tane

Procedura oblicza dochód w podanym okresie:

CREATE PROC dochod

@idprac int,
@czas int,
@wynik int OUTPUT -- parametr wyjsciowy

as
select @wynik = (pensja + premia)*@czas
from pracownik where IdPrac = @idprac
go

Wywołanie:

declare @c int, @w int
select @c = 12
exec dochod 1,@c,@w output
select
cast(@c as varchar(4))+' miesiecy' as [dochod za okres],
cast(@w as varchar(11))+' zl' as wynosi
go

Wynik:

dochod za okres

wynosi

-------------------- --------
12 miesiecy

14400 zl

Procedury wyzwalane (

trigery

)

Procedury wyzwalane (triggery):

• rozszerzają możliwości definiowania warunków spójności,
• wywoływane są automatycznie w wyniku wykonania operacji:

INSERT, UPDATE lub DELETE,

• może specyfikować operację, która wykona się zamiast operacji

głównej – wariant INSTEAD OF,

• wykorzystują systemowe tabele INSERTED i/lub DELETED

(INSERTED zawiera dołączane krotki lub nową wersję krotek

zmienianych; DELETED zawiera usuwane krotki lub starą wersję

krotek zmienianych),

• mogą być przypisane do tabeli lub widoku,
• mogą być wywoływane rekurencyjnie.

Procedury wyzwalane

przyk

Zakaz dopisania pracownika, który zarabia więcej niż jego kierownik:

create trigger maks
on pracownik
for insert as
if (select zarobki from inserted) >

(select k.zarobki from pracownik k, inserted i

where k.IdPrac = i.IdKier)

begin

rollback

end

Procedury wyzwalane

–

INSTEAD OF

Trigger zaznacza w kolumnie del elementy przeznaczone do usunięcia:

create trigger zaznacz

on Pracownik

INSTEAD OF delete as

update Pracownik

set del='*'

where nazwisko in (select nazwisko from deleted)

delete from Pracownik

where nazwisko like 'K%‘

Wynik:

nazwisko

premia

del

-------------------- ----------- ----

Kowalski 200 *

Lipski

200 NULL

Kowal

200 *

Pawlak

200 NULL

Kursory

Składnia SQL-92:

DECLARE

nazwa_kursora

[INSENSITIVE] [SCROLL] CURSOR

FOR

wyra

enie_select

[FOR {READ ONLY | UPDATE [OF

nazwa_kolumny

[,...

]]}]

Składnia Transact-SQL:

DECLARE

nazwa_kursora

CURSOR

wyra

enie_select

[FOR UPDATE [OF

nazwa_kolumny

[,...

]]]

Kursory

Kursor (ang. cursor) dostarcza mechanizmu dostępu do pojedynczych

wierszy wg zasady "jeden po drugim".

declare @zmNazwa char(20)
declare @zmCenaSprz Money
declare kurAGD cursor
for select Nazwa, CenaSprz

from Towar
where GrupaTow = 'AGD'

open kurAGD
fetch next from kurAGD into @zmNazwa, @zmCenaSprz
while @@FETCH_STATUS = 0
begin

select @zmNazwa, @zmCenaSprz
fetch next from kurAGD into @zmNazwa, @zmCenaSprz

end
close kurAGD
deallocate kurAGD

Kursory (c.d.)

Aby przetwarzać dane za pomocą kursora należy:
• zadeklarować kursor DECLARE ... CURSOR i zdefiniować jego zakres

FOR SELECT,

• otworzyć kursor, OPEN - wykonywana jest operacja SELECT, kursor

wskazuje na miejsce bezpośrednio przed pierwszym wierszem w

utworzonym zbiorze wierszy (zakresie kursora),

• wykonać operację pobrania danych na kolejnym wierszu FETCH NEXT

FROM, pobierane dane można zapamiętać pod podanymi zmiennymi,

• pobieranie danych może być realizowane w pętli, różna od zera wartość

zmiennej (funkcji) globalnej @@FETCH_STATUS może kończyć

wykonywanie operacji pobierania danych,

• zmienne mogą być wykorzystywane w dowolnych operacjach,

wyrażenie SELECT @zmNazwa, @zmCenaSprz oznacza wypisanie

danych,

• w końcu kursor należy zamknąć CLOSE i dealokować DEALLOCATE.

Kursory a ADO

Recordset

Obiekt Recordset representuje zbiór rekordów z tabeli bazowej lub

wynik wykonania polecenia. W każdej chwili czasu obiekt Recordset

wskazuje jeden rekord w zbiorze zwany rekordem bieżącym.

Tworzenie nowego obiektu Recordset:

Set Klient = New ADODB.Recordset
Set Klient.ActiveConnection = db

If Len(StanUSA.Text) = 0 Then

Klient.Source = "Select CustomerId, Name, State From

Customers"

Else

Klient.Source = "Select CustomerId,Name,State From

Customers Where State = '" _

& StanUSA.Text & "'"

End If

Kursory a ADO

Recordset

Wyszukiwarka

Podobne podstrony:

więcej podobnych podstron