Смарт »SQL
Много разработчици, работещи с Microsoft SQL Server, предпочитат да пишат на логиката на приложението в своя програмен код с помощта на SQL Server само като хранилище за данни. Една от причините за този подход - обичайните мързел (например, процесът на миграция от достъп до SQL Server), а от друга причина - убеждението, че SQL език не е толкова гъвкав, колкото Delphi, C ++ и други езици за програмиране. Но той не е проектиран да работи с крайния потребител! В действителност - SQL осигурява минимално необходимите функции за работа с данни; просто трябва да ги използват правилно.
В тази статия искам да споделя някои трикове за работа с Microsoft SQL Server, който използвам в моите проекти. Само уточни, че всички скриптове са написани за MS SQL Server 7.0.
Трансфер маси стойности, за да съхраняват процедура
Често възниква въпросът: "Как мога да мине масив на съхранена процедура (маса) ценности?". Стандартни разтвори - процедура покана за всяка стойност на индивидуално, или прехвърлянето на масива като низ разграничена, които след това се извлича от съхранена процедура. Очевидно е, че в случаите, когато стойностите на размера на масив е голям, първия подход е много трудно за сървъра (и изпълнението му отнема много дълго време), а вторият тип е ограничено до максимум VARCHAR (8000 байта) в дължина.
Въпреки това, има решение, което е свободно от тези недостатъци. Можете да създадете временна таблица, тя донесе множество ценности - и пуснете съхраняват процедура, която ще отнеме данните от генерира таблица. Всички команди се изпълняват в една сесия. Единственият недостатък на този подход е очевиден - процедурата трябва да се знае точно имената на таблици и полета.
Да кажем, че ние трябва да изберете всички записи от таблицата на базата данни на авторите кръчми. поле държава, в която една от стойностите [ 'Калифорния "," МВР "," СК "].
Създаване на процедура dbo.TestProc:
СЪЗДАВАНЕ НА ПРОЦЕДУРА dbo.TestProc
AS
ЗАПОЧНЕТЕ
SELECT *
ОТ pubs..authors на
ВЪТРЕШЕН УЧАСТВА #params т ON
a.state = t.state
END
GO
Сега ние трябва да напишете следния скрипт за решаване на нашите проблеми:
Създаване на таблица #params
(ДВ, VARCHAR (2))
INSERT INTO #params (държава)
Стойности ( "CA)
INSERT INTO #params (държава)
СТОЙНОСТИ ( "МВР")
INSERT INTO #params (държава)
СТОЙНОСТИ ( "KS")
EXEC dbo.TestProc
DROP TABLE #params
Обикновено, не е тя? Между другото, DROP TABLE #params команда не може да изпълни. #params временна таблица автоматично ще бъдат премахнати в края на сесията.
Сравнение на двете таблици,
Да речем, че искате да сравните двете плочи от състоянието "И". Например - изберете издатели, които произвеждат книги, посветени на теми "Психология" на ( "психология") и "Бизнес" ( "бизнес"). Елементарно? Разбира се; Но какво, ако условията са много, като техният брой не е известен? Можете да декларирате променлива @count. който ще се съхранява броя на съвпаденията (броя на уникалните записи в таблицата, с които ние се сравняват). След това е необходимо да се сравни броят на неравностойни мачове от интерес за нас издателят помежду @count. Изискваните документи - тези, които отговарят на последното условие. Нека се опитаме да напиша сценарий:
/ * Създаване и запълване на временна #TestTable маса, съдържаща думите за търсене. * /
Създаване на таблица #TestTable
(Тип VARCHAR (15))
Вмъкнете в #TestTable
СТОЙНОСТИ ( "бизнес")
Вмъкнете в #TestTable
СТОЙНОСТИ ( "психология")
/ * Установи променлива @count. * /
ДЕКЛАРИРАМЕ
@count вътр
/ * И я държи в броя на невъзобновяемите записи #test маса * /
SELECT
@count = COUNT (отделен тип) ОТ #TestTable
SELECT
DISTINCT p.pub_name
ОТ #TestTable INNER JOIN
заглавия т ЗА
t.type = # TestTable.type ВЪТРЕШЕН УЧАСТВА
издателите р ON t.pub_id = p.pub_id
GROUP BY
p.pub_name
КАТО
COUNT (важно t.type) = @count
Тази техника е доста съвместими с техниките, описани в предходния параграф. Взети заедно, те са много полезни за процедурите за търсене.
Извършване на динамична заявка при липсата на права за извличане на данни от таблицата
Понякога има ситуации, в които по-лесно да образуват КЪДЕ клауза в клиентското приложение, а след това преминават като параметър на запомнена процедура. В случая, когато има разрешение за извличане на данни от желаната маса, решението ще бъде незначителен. Но какво, ако потребителят има право да използва само съхранени процедури?
Отговорът е много прост. Процедурата работи с базата данни с "обобщение" на правата на собственика и на потребителя, който го е причинило. По този начин, всяка процедура, създадена от потребителя на DBO, има всички права за базата данни. Но ако процедурата е намерена EXEC изявление. се изпълнява от името на потребителя, се обадете на процедурата. Ако ние не са дали достатъчно права за изпълнение на искането, присъства в отчета EXEC - възникне грешка. Решението е просто - качване на данни в временна таблица, и след това да ги приведе в съответствие с нашите правила и условия:
СЪЗДАВАНЕ НА ПРОЦЕДУРА dbo.TestProc
(@where VARCHAR (8000) = NULL)
/ * Създаване на процедура dbo.TestProc * /
AS
ЗАПОЧНЕТЕ
SET NOCOUNT ON
/ * Това ще произвежда примерни данни от dbo.authors на масата
базата данни на заведения, отговарят на условията, съхранявани в променлива @where * /
/ * Създайте таблица #tmp. * /
Създаване на таблица #tmp (
au_id VARCHAR (11) NOT NULL,
au_lname VARCHAR (40) NOT NULL,
au_fname VARCHAR (20) NOT NULL,
телефон Чар (12),
адрес VARCHAR (40) NULL,
град VARCHAR (20) NULL,
състояние знак (2) NULL,
цип CHAR (5) NULL,
договор малко NOT NULL
)
/ * За да се разтоварят данни от кръчмите на масата база данни dbo.authors в #tmp. * /
Вмъкнете в #tmp SELECT * В #tmp ОТ pubs..authors
/ *. и стартирайте пробата удовлетворяване на нашите условия. * /
ако (@while = '')
SELECT * FROM #tmp
ELSE
EXEC ( "SELECT * ОТ #tmp където" + @where)
SET NOCOUNT OFF
END
Сега можете да проверите работата на процедурата. Да кажем, че имаме потребител с име на мениджъра. Забрани него, за да изберете данни от dbo.authors маса:
DENY SELECT ON dbo.authors КЪМ мениджър
И ограничи процедура dbo.TestProc.
Дарение изпълни на dbo.TestProc ДА мениджър
Промяна на текущия потребител на мениджъра.
SETUSER "управител"
И следват процедура dbo.TestProc
EXEC dbo.TestProc "au_lname LIKE '' Gree% '' и държавата в ( '' СА '', '' МИ '')"
С помощта на база данни TempDB
База данни TempDB е за временно съхранение на предмети и се Възстановиха всяко стартиране на SQL Server. Тя съхранява резултатите от сложни заявки и процедури. Тук бих искал да говоря за използването на временни таблици и временни процедури.
Доста често програмисти правят грешката при използване на строителството SELECT INTO сложни заявки с разтоварване на данни във временната таблица. Проблемът е, че базата данни TempDB е блокиран по време на изпълнение на искането. Това е разумно само тогава, когато е известно, че извадката от данни ще бъде краткотрайно. Необходимо е да се вземе предвид факта, че някои от таблиците, от които проба данни може да се заключи чрез други процеси. Ако броят на временна таблица полета голям или неизвестен, за да създадете по-добре е да се използва такова искане:
SELECT TOP 0 * В #TempTable ОТ.
При което искане не включва подбор и условия за сортиране, и на масата трябва да бъде разделен със запетаи списък.
Промяна на текущия потребител на мениджъра.
SETUSER "управител"
И изпълнява dbo.TestProc.
EXEC dbo.TestProc "au_lname LIKE '' Gree% '' и държавата в ( '' СА '', '' МИ '')"
В отговор, ние получаваме:
Сървър: Msg 229, Level 14, 5 бала, Процедура TestProc, Линия 1
Права за изпълнение на обект отрече "TestProc", база данни "кръчми", собственик "DBO".
Сега, да създаде временна процедура #TestProc:
СЪЗДАВАНЕ НА ПРОЦЕДУРА #TestProc
(@where VARCHAR (8000) = NULL)
AS
ЗАПОЧНЕТЕ
EXEC dbo.TestProc @where
END
И го изпълни:
EXEC #TestProc "au_lname LIKE '' Gree% '' и държавата в ( '' СА '', '' МИ '')"
Всичко работи добре!
Как се грижите за сигурността на данните от тези ситуации? Процедурата себе си ще трябва да се провери, който го пусна, и дали тя отговаря на изискванията. Да кажем, че имаме една група от мениджъри на потребителите. Добавянето на програма за управление на потребителите. Сега с функцията за помощ IS_MEMBER () знам, ако потребителят принадлежи към тази група. Добави в началото на процедурата по линия dbo.TestProc:
АКО (IS_MEMBER ( "мениджъри")! = 1)
ЗАПОЧНЕТЕ
RAISERROR ( "Само потребители, принадлежащи към групата '' в мениджърите '' да се обадите на тази процедура", 16, 1) END
Тази проверка трябва да бъде в началото на всяка процедура.
операция за обработка резултат съхраняват процедура
Често има моменти, когато някой иска да сподели с голям процедура в няколко по-малки; или в някои процедури използва същата парола, която е по-добре да се направи отделна процедура. Но възниква въпросът: как да се измъкне от него информация за резултата от работата си? Един код за връщане често не е достатъчен. По-интересното е въпрос на предаването на данни, върнати от процедурата. В книги онлайн, за съжаление, този важен момент се направи цялостен преглед. Въпреки това, тази възможност се използва широко при създаване на капитана база данни. Синтаксисът на такова искане следното: INSERT INTO процедура маса EXEC.
Така например, създаване на процедура dbo.TestProc. е избрана от трапезни dbo.authors поле au_id на. au_lname и au_fname и #TestTable временна таблица с полета au_id. au_lname и au_fname.
/ * Създаване на процедура dbo.TestProc * /
СЪЗДАВАНЕ НА ПРОЦЕДУРА dbo.TestProc
AS
ЗАПОЧНЕТЕ
SELECT au_id, au_lname, au_fname ОТ dbo.authors
END
GO
/ * Създаване на временна #TestTable маса * /
Създаване на таблица #TestTable
(AU_ID VARCHAR (20) NOT NULL,
AU_LNAME VARCHAR (40) NOT NULL,
AU_FNAME VARCHAR (20) NOT NULL)
GO
/ * И за да вмъкнете стойностите от съхранена процедура dbo.TestProc * /
Вмъкнете в #TestTable EXEC dbo.TestProc
GO
/ * Сега можем да видим резултата * /
SELECT * FROM #TestTable
В приложения, които работят с SQL Server, често ще трябва да прекарат допълнително удостоверяване на потребителя. Например, за да изпълняват някои от решаващо значение операция е необходимо да се провери дали потребителят може да го изпълни. Вие със сигурност може да го помоли да въведете парола. Но как да се съхранява тази парола? Предписват го в кода на приложението - най-малко глупаво; съхраняват като обикновен текст във всяка таблица от база данни - твърде опасно. Има само един изход - да криптирате.
За съжаление, Microsoft не е искал да се документира функции, които ви позволяват да криптира данните. Тези функции са: криптиране (). pwdencrypt () и pwdcompare (). криптиране функция (стойност) връща VARBINARY тип (6). pwdencrypt функция (стойност) връща nvarchar тип (32). pwdcompare функция (проверена стойност>, кодираната стойност>) сравнява стойността вход с криптирани, когато стойността на тест - линия на тип nvarchar. кодираната стойност е от тип VARBINARY (16). стойности за връщане: - 0 (ако е различен), 1 (когато идентични), NULL (ако е налице грешка). Различията криптиране () и pwdencrypt (), че криптиране () кодира стойността на един алгоритъм и pwdencrypt () - за мен. Това означава, че чрез извършване на няколко пъти SELECT криптиране ( "тест") ще получим същата стойност, както и изпълнението на SELECT конвертиране (VARBINARY (16), pwdencrypt ( "тест")) дава всички часови различни стойности. Стойност криптирани използване pwdencrypt () може да се сравни с единствената проверими чрез pwdcompare (функцията). и шифрова с Шифроване () - сравнявайки го с криптирана проверите стойността. Но pwdcompare () функция има един голям недостатък - ако са инсталирани на сървъра, са съдебната независим код на страницата, тестът ще бъде нечувствителен. пароли именно "тест" и "тест", ще бъдат признати като идентични. Ние трябва да избирам - или да използвате функцията мощен алгоритъм условие pwdencrypt (). или да използвате по-малко сигурен криптиране (). Примери за използване на тези функции:
/ * Създайте таблица dbo.TestTable, в която да се имена и пароли съхраняване на потребителските * /
Създаване на таблица dbo.TestTable
(Потребителско име VARCHAR (30),
парола VARBINARY (16))
/ * Добавяне на потребител "User1" с парола "user1", криптиран функция криптиране () * /
Вмъкнете в dbo.TestTable
(Потребителско име, парола)
СТОЙНОСТИ ( "User 1", криптиране ( "user1"))
/ * ... държава "User1_1" с "user1" парола, криптиране () функция * /
Вмъкнете в dbo.TestTable
(Потребителско име, парола)
СТОЙНОСТИ ( "User1_1", криптиране ( "user1"))
/ * "User1_2", парола "User1", криптиране () функция * /
Вмъкнете в dbo.TestTable
(Потребителско име, парола)
СТОЙНОСТИ ( "User1_2", криптиране ( "User 1"))
/ * "User 2", парола "user2", pwdencrypt () * /
Вмъкнете в dbo.TestTable
(Потребителско име, парола)
СТОЙНОСТИ ( "User 2", конвертирате (VARBINARY, pwdencrypt ( "user2")))
/ * "User2_1", парола "user2", pwdencrypt () * /
Вмъкнете в dbo.TestTable
(Потребителско име, парола)
СТОЙНОСТИ ( "User2_1", конвертирате (VARBINARY, pwdencrypt ( "user2")))
/ * "User2_2", парола "User 2", pwdencrypt () * /
Вмъкнете в dbo.TestTable
(Потребителско име, парола)
СТОЙНОСТИ ( "User2_2", конвертирате (VARBINARY, pwdencrypt ( "User 2")))
/ * И сега - погледнете резултата. * /
SELECT * FROM dbo.TestTable
Човек може да се види, че User1 и User1_1 имат една и съща парола, а User 2, и User2_1 - различно. Сега експериментирам с pwdcompare () функция, а също и погледнете резултатите:
SELECT потребителско име ОТ КЪДЕ dbo.TestTable pwdcompare ( "user2", парола) = 1
SELECT потребителско име ОТ КЪДЕ dbo.TestTable pwdcompare ( "User 2", парола) = 1
SELECT потребителско име ОТ КЪДЕ dbo.TestTable pwdcompare ( "user1", парола) = 1
По същия начин, за да кодира функция ():
SELECT потребителско име ОТ КЪДЕ dbo.TestTable криптиране ( "user1 ') = парола
SELECT потребителско име ОТ КЪДЕ dbo.TestTable криптиране ( "User 1 ') = парола