Inteligentă »sql
Mulți dezvoltatori care lucrează cu Microsoft SQL Server, preferă să scrie logica aplicației în codul de program folosind SQL Server doar ca un depozit de date. Unul dintre motivele acestei abordări - lenea obișnuită (de exemplu, procesul de migrare de la accesul la SQL Server), în timp ce celălalt motiv - credința că limbajul SQL nu este la fel de flexibil ca Delphi, C ++ si alte limbaje de programare. Dar el nu a fost proiectat pentru a lucra cu utilizatorul final! De fapt - SQL oferă minim caracteristicile necesare pentru lucrul cu date; trebuie doar să le folosească în mod corect.
În acest articol vreau să vă împărtășesc câteva trucuri pentru a lucra cu Microsoft SQL Server, pe care le folosesc în proiectele mele. Doar specifică faptul că toate script-urile sunt scrise pentru MS SQL Server 7.0.
Se transferă valori tabele într-o procedură stocată
De multe ori se pune întrebarea: „Cum pot trece o matrice la o procedură stocată (tabel) valori?“. Soluții standard - un apel de procedură pentru fiecare valoare individuală sau transferul de matrice ca un șir delimitat, care este apoi analizat prin procedura stocată. Evident, în cazul în care valorile dimensiunii matrice este mare, prima abordare este foarte dificil pentru server (și performanța acestuia durează foarte mult timp), în timp ce al doilea tip este limitată la un varchar maxim (8000 bytes), în lungime.
Cu toate acestea, există o soluție care este lipsit de aceste dezavantaje. Puteți crea un tabel temporar, aduce o serie de valori - și rula o procedură stocată care va prelua datele din tabelul generat. Toate comenzile sunt executate într-o singură sesiune. Singurul dezavantaj al acestei abordări este evident - procedura trebuie să știe exact numele de tabele și câmpuri.
Să presupunem că avem nevoie pentru a selecta toate înregistrările din tabelul bazei de date autori pub-uri. stare câmp în care una dintre valorile [ „CA“, „MI“, „KS“].
Crearea unei proceduri dbo.TestProc:
CREATE PROCEDURA dbo.TestProc
AS
BEGIN
SELECT *
DE LA pubs..authors un
INTERIOARĂ TE #Params T ON
a.state = t.state
END
GO
Acum trebuie să scrie următorul script pentru a rezolva problema noastră:
CREATE TABLE #params
(Varchar de stat (2))
INSERT INTO #params (de stat)
VALORI ( 'CA')
INSERT INTO #params (de stat)
VALORI ( 'MI')
INSERT INTO #params (de stat)
VALORI ( 'KS')
EXEC dbo.TestProc
#params DROP TABLE
Simplu, nu-i așa? Apropo, comanda DROP TABLE #params nu se poate executa. tabel #params temporară va fi eliminată în mod automat la încheierea sesiunii.
Comparația dintre cele două tabele
Să presupunem că doriți să comparați cele două tabele cu condiția „ȘI“. De exemplu, - selectați editori producătoare de cărți pe teme „Psihologia“ ( „psihologie“) și „Business“ ( „business“). Elementar? Desigur; dar ce se întâmplă dacă condițiile sunt multe, iar numărul lor este necunoscut? Puteți declara o @count variabilă. care va stoca numărul de meciuri (numărul de intrări unice în tabel, cu care se compară). Apoi, este necesar să se compare numărul de partide inegale de interes pentru noi editor cu fiecare @count. Înregistrările necesare - cele care îndeplinesc ultima condiție. Să încercăm să scrie un scenariu:
/ * Crearea și completați un #TestTable tabel temporar, care conține termenii de căutare. * /
CREATE TABLE #TestTable
(Tip varchar (15))
INSERT INTO #TestTable
VALORI ( „afaceri“)
INSERT INTO #TestTable
VALORI ( „psihologie“)
/ * Declara o @count variabilă. * /
DECLAR
@count int
/ * Și păstrați-l în numărul de intrări non-recurente #test tabel * /
SELECT
@count = COUNT (tip DISTINCT) DIN #TestTable
SELECT
p.pub_name DISTINCT
DE LA #TestTable INTERIOARĂ-TE
titluri T ON
t.type = # TestTable.type INTERIOARĂ-TE
editori p ON t.pub_id = p.pub_id
GROUP BY
p.pub_name
AVÂND
COUNT (t.type distincte) = @count
Această tehnică este destul de compatibilă cu tehnicile descrise în paragraful precedent. Împreună, acestea sunt foarte utile pentru procedurile de căutare.
Efectuați o interogare dinamică în absența unor drepturi la recuperarea datelor din tabel
Uneori, există situații în care mai ușor pentru a forma o clauza WHERE în aplicația client, și apoi trece-l ca parametru la o procedură stocată. În cazul în care există permisiunea de a prelua datele din tabelul dorit, soluția va fi banal. Dar dacă utilizatorul este autorizat să utilizeze numai proceduri stocate?
Răspunsul este destul de simplu. Procedura funcționează cu baza de date cu „rezumatul“ drepturile proprietarului și utilizatorul care a cauzat-o. Astfel, orice procedură creată de către utilizator DBO, are toate drepturile asupra bazei de date. Dar, în cazul în care procedura se găsește declarația EXEC. este executat în numele utilizatorului, apelați procedura. Dacă nu ne-am dat drepturi suficiente pentru a îndeplini cererea, prezentă în declarația EXEC - apare o eroare. Solutia este simpla - încărca datele într-un tabel temporar, și apoi să le aducă, în conformitate cu termenii și condițiile noastre:
CREATE PROCEDURA dbo.TestProc
(Varchar @where (8000) = NULL)
/ * Crearea unei proceduri dbo.TestProc * /
AS
BEGIN
SET NOCOUNT ON
/ * Se va produce mostre de date din dbo.authors de masă
baza de date pub-uri, îndeplinesc condițiile stocate în @where variabila * /
/ * Crearea unui tabel #tmp. * /
CREATE TABLE #tmp (
varchar au_id (11) NOT NULL,
varchar au_lname (40) NOT NULL,
varchar au_fname (20) NOT NULL,
char telefon (12),
adresa varchar (40) NULL,
varchar oraș (20) NULL,
char (2) NULL de stat,
zip char (5) NULL,
biți de contract NOT NULL
)
/ * Pentru a descărca date de la pub-uri dbo.authors tabel de baze de date în #tmp. * /
INSERT INTO #tmp SELECT * ÎN #tmp DIN pubs..authors
/ *. și a alerga proba satisface condițiile noastre. * /
if (@while = '')
SELECT * FROM #tmp
ELSE
EXEC ( 'SELECT * FROM #tmp WHERE' + @where)
SET NOCOUNT OFF
END
Acum puteți verifica funcționarea procedurii. Să presupunem că avem un utilizator cu numele managerului. Interziceau-l pentru a selecta date din tabelul dbo.authors:
DENY SELECT ON dbo.authors la Managerul
Și să limiteze procedura dbo.TestProc.
GRANT EXECUTE ON dbo.TestProc la Managerul
Schimba utilizatorul curent la managerul.
SETUSER „administrator“
Și să urmeze procedura dbo.TestProc
'Au_lname LIKE' EXEC dbo.TestProc 'Gree%' 'sau de stat (' 'CA' '' 'MI' ')'
Folosind o bază de date tempdb
Baza de date Tempdb este pentru stocarea temporară a obiectelor și este re-creat de fiecare dată când porniți SQL Server. De asemenea, stochează rezultatele interogări și proceduri complexe. Aici aș vrea să vorbesc despre utilizarea de tabele temporare și proceduri temporare.
Destul de des programatori fac greșeala de a folosi construcție SELECT INTO interogări complexe cu descărcarea datelor din tabelul temporar. Problema este că baza de date Tempdb este blocat în timpul executării cererii. Acest lucru este rezonabil numai atunci când se știe că eșantionul de date va fi de scurtă durată. Este necesar să se ia în considerare faptul că unele dintre tabelele din care eșantionul de date poate fi blocat de alte procese. În cazul în care numărul de tabel temporar câmpuri mari sau necunoscute, pentru a crea este mai bine să folosească o astfel de cerere:
SELECT TOP 0 * ÎN #TempTable FROM.
În care cererea nu include condițiile de sortare și de selecție, iar masa trebuie să fie o listă separată prin virgulă.
Schimba utilizatorul curent la managerul.
SETUSER „administrator“
Și efectua dbo.TestProc.
'Au_lname LIKE' EXEC dbo.TestProc 'Gree%' 'sau de stat (' 'CA' '' 'MI' ')'
Ca răspuns, obținem:
Server: Msg 229, Level 14, Stat 5, Procedura TestProc, Linia 1
EXECUTE permisiunea de a negat „TestProc“, baze de date „pub-uri“, proprietarul „DBO“ obiect.
Acum, creați o procedură #TestProc temporară:
CREATE PROCEDURA #TestProc
(Varchar @where (8000) = NULL)
AS
BEGIN
EXEC dbo.TestProc @where
END
Și executa:
'Au_lname LIKE' EXEC #TestProc 'Gree%' 'sau de stat (' 'CA' '' 'MI' ')'
Totul funcționează bine!
Cum vă securizați datele din aceste situații? Procedura în sine va trebui să verifice, care a lansat, și dacă este eligibil. Să presupunem că avem un grup de manageri de utilizatori. Adăugarea unui manager de utilizator. Acum, cu funcția de ajutor IS_MEMBER () știu, în cazul în care utilizatorul face parte din acest grup. Adăugați la începutul procedurii dbo.TestProc linie:
IF (IS_MEMBER ( 'manageri')! = 1)
BEGIN
RAISERROR ( „Numai utilizatorii care aparțin grupului“ „în managerii“ „poate apela la această procedură“, 16, 1) END
Această verificare ar trebui să fie inclusă la începutul fiecărei proceduri.
Procedura de rezultatul operației de prelucrare a stocat
De multe ori există momente când cineva vrea să împartă o procedură de mare în mai multe mai mici; sau, în unele proceduri utilizează același cod, care este mai bine să facă o procedură separată. Dar se pune întrebarea: cum să scape de informații despre rezultatul muncii sale? Un cod de retur este de multe ori nu este suficient. Întrebarea mai interesantă este transmiterea datelor returnate de procedura. În Books Online, din păcate, acest moment important complet revizuite. Cu toate acestea, această posibilitate este utilizat pe scară largă la crearea unei baze de date maestru. Sintaxa unei astfel de cereri următoarele: INSERT INTO procedura de tabelă EXEC.
De exemplu, crearea unei proceduri dbo.TestProc. este selectat din dbo.authors de masă câmp au_id. au_lname și au_fname și tabel temporar #TestTable cu câmpuri au_id. au_lname și au_fname.
/ * Crearea unei proceduri dbo.TestProc * /
CREATE PROCEDURA dbo.TestProc
AS
BEGIN
SELECT au_id, au_lname, au_fname DIN dbo.authors
END
GO
/ * Crearea unui tabel temporar #TestTable * /
CREATE TABLE #TestTable
(AU_ID VARCHAR (20) NOT NULL,
AU_LNAME VARCHAR (40) NOT NULL,
AU_FNAME VARCHAR (20) NOT NULL)
GO
/ * Și pentru a introduce valorile din procedura dbo.TestProc stocate * /
INSERT INTO #TestTable EXEC dbo.TestProc
GO
/ * Acum putem vedea rezultatul * /
SELECT * FROM #TestTable
În aplicații care funcționează cu SQL Server, de multe ori aveți nevoie pentru a petrece o autentificare suplimentară de utilizator. De exemplu, pentru a efectua o anumită operațiune crucială este necesar să se verifice dacă utilizatorul poate executa. cu siguranță Puteți cere-l să introduceți o parolă. Dar cum se păstrează această parolă? Prescrierea-l în codul aplicației - cel puțin o prostie; stocate ca text simplu în orice tabel de baze de date - prea periculos. Există o singură cale de ieșire - pentru a cripta.
Din păcate, Microsoft nu a dorit să documenteze caracteristicile care vă permit să criptarea datelor. Aceste funcții sunt: encrypta (). pwdencrypt () și pwdcompare (). Funcția de criptare (valoare) returnează VARBINARY tip (6). Funcția pwdencrypt (valoare) returneaza nvarchar tip (32). Funcția pwdcompare (valoare verificată>, valoarea criptata>) compară valoarea de intrare cu criptat, în cazul în care valoarea la încercare - o linie de tip nvarchar. Valoarea criptată este de tip VARBINARY (16). Valori retur: - 0 (dacă este diferit), 1 (când identice), NULL (dacă a apărut o eroare). Diferențe cripta () și pwdencrypt (), care criptați () codifică valoarea unui singur algoritm și pwdencrypt () - pentru mine. Aceasta este, prin efectuarea de mai multe ori Encrypt SELECT ( „test“), vom obține aceeași valoare, și performanța de a converti SELECT (VARBINARY (16), pwdencrypt ( „test“)) dă toate valorile de timp diferite. Valoare criptate folosind pwdencrypt () poate fi comparat cu numai verificabile prin pwdcompare funcției (). și criptate folosind criptare () - comparând-o cu criptat verifica valoarea. Dar funcția pwdcompare () are un mare dezavantaj - în cazul în care este instalat pe serverul de cod sunt pagini caz independent, testul va fi, de asemenea caz insensibil. parole Ie „test“ și „încercare“ vor fi recunoscute ca fiind identice. Trebuie să aleagă - fie să utilizeze o funcție algoritm puternic pwdencrypt furnizat (). sau de a folosi encrypta mai puțin sigure (). Exemple de utilizare a acestor funcții:
/ * Crearea unui tabel dbo.TestTable, în care pentru a stoca nume de utilizator și parole * /
CREATE TABLE dbo.TestTable
(Varchar UserName (30),
Parola VARBINARY (16))
/ * Adăugați un utilizator 'Utilizator1' cu parola 'user1', cripta funcția criptate () * /
INSERT INTO dbo.TestTable
(UserName, parola)
VALORI ( 'Utilizator1', cripta ( 'user1'))
/ * ... membru 'User1_1' cu parola 'user1', criptați () funcția * /
INSERT INTO dbo.TestTable
(UserName, parola)
VALORI ( 'User1_1', cripta ( 'user1'))
/ * 'User1_2', parola 'Utilizator1', criptați () funcția * /
INSERT INTO dbo.TestTable
(UserName, parola)
VALORI ( 'User1_2', cripta ( 'Utilizator1'))
/ * 'User2', parola 'utilizator2', pwdencrypt () * /
INSERT INTO dbo.TestTable
(UserName, parola)
VALORI ( 'User2', conversia (VARBINARY pwdencrypt ( 'user2')))
/ * 'User2_1', parola 'utilizator2', pwdencrypt () * /
INSERT INTO dbo.TestTable
(UserName, parola)
VALORI ( 'User2_1', conversia (VARBINARY pwdencrypt ( 'user2')))
/ * 'User2_2', parola 'User2', pwdencrypt () * /
INSERT INTO dbo.TestTable
(UserName, parola)
VALORI ( 'User2_2', conversia (VARBINARY pwdencrypt ( 'User2')))
/ * Și acum - uita-te la rezultatul. * /
SELECT * FROM dbo.TestTable
Se poate observa că Utilizator1 și User1_1 au aceeași parolă, și User2 și User2_1 - diferite. Acum, experiment cu pwdcompare () și, de asemenea, uita-te la rezultatele:
SELECT FROM dbo.TestTable UNDE UserName pwdcompare ( 'utilizator2', parola) = 1
SELECT FROM dbo.TestTable UNDE UserName pwdcompare ( 'User2', parola) = 1
SELECT FROM dbo.TestTable UNDE UserName pwdcompare ( 'user1', parola) = 1
În mod similar pentru a cripta funcția ():
SELECT FROM dbo.TestTable UNDE UserName Encrypt ( 'utilizator1') = parola
SELECT FROM dbo.TestTable UNDE UserName Encrypt ( 'Utilizator1') = parola