Kas ir sql saglabātā procedūra. Piemēru avota dati. Risinājumu apkopošana no tīmekļa vietnes "Kāda ir atšķirība starp saglabāto procedūru un skatu?"
Saglabātā procedūra - datu bāzes objekts, kas ir SQL instrukciju kopa, kas tiek apkopota vienreiz un saglabāta serverī. Saglabātās procedūras ir ļoti līdzīgas parastajām augsta līmeņa valodas procedūrām, tām var būt ievades un izvades parametri un lokālie mainīgie, tās var veikt skaitliskus aprēķinus un darbības ar rakstzīmju datiem, kuru rezultātus var piešķirt mainīgajiem un parametriem. Saglabātās procedūras var veikt standarta datu bāzes darbības (gan DDL, gan DML). Turklāt saglabātās procedūras pieļauj cilpas un zarus, tas ir, tās var izmantot instrukcijas, lai kontrolētu izpildes procesu.
Saglabātās procedūras ir līdzīgas lietotāja definētajām funkcijām (UDF). Galvenā atšķirība ir tā, ka lietotāja definētas funkcijas var izmantot tāpat kā jebkuru citu izteiksmi SQL priekšrakstā, savukārt saglabātās procedūras ir jāizsauc, izmantojot funkciju CALL:
ZVANA procedūra (…)
IZPILDĪT procedūru (…)
Saglabātās procedūras var atgriezt vairākus rezultātus, tas ir, SELECT vaicājuma rezultātus. Šādas rezultātu kopas var apstrādāt, izmantojot kursorus, citas saglabātās procedūras, kas atgriež rezultātu kopas rādītāju, vai lietojumprogrammas. Saglabātajās procedūrās var būt arī deklarēti mainīgie datu un kursoru apstrādei, kas ļauj cilpot vairākās tabulas rindās. SQL standarts nodrošina IF, LOOP, REPEAT, CASE un daudzus citus, ar kuriem strādāt. Saglabātās procedūras var pieņemt mainīgos, atgriezt rezultātus vai modificēt mainīgos un atgriezt tos atkarībā no tā, kur mainīgais ir deklarēts.
Saglabāto procedūru ieviešana dažādās DBVS atšķiras. Lielākā daļa lielāko datu bāzu pārdevēju tos atbalsta vienā vai otrā veidā. Atkarībā no DBVS, saglabātās procedūras var tikt realizētas dažādās programmēšanas valodās, piemēram, SQL, Java, C vai C++. Saglabātās procedūras, kas nav rakstītas SQL, var vai nevar izpildīt SQL vaicājumus pašas.
Aiz muguras
Loģikas koplietošana ar citām lietojumprogrammām. Saglabātās procedūras iekapsulē funkcionalitāti; tas nodrošina savienojamību datu piekļuvei un pārvaldībai dažādās lietojumprogrammās.
Lietotāju izolēšana no datu bāzes tabulām. Tas ļauj jums piešķirt piekļuvi saglabātajām procedūrām, bet ne pašiem tabulas datiem.
Nodrošina aizsardzības mehānismu. Saskaņā ar iepriekšējo punktu, ja varat piekļūt datiem, izmantojot tikai saglabātās procedūras, neviens cits nevar izdzēst jūsu datus, izmantojot komandu SQL DELETE.
Uzlabota izpilde samazinātas tīkla trafika dēļ. Izmantojot saglabātās procedūras, var apvienot vairākus vaicājumus.
Pret
Palielināta datu bāzes servera slodze, jo lielākā daļa darba tiek veikta servera pusē, bet mazāka - klienta pusē.
Jums būs daudz jāmācās. Lai rakstītu saglabātās procedūras, jums būs jāapgūst MySQL izteiksmes sintakse.
Jūs dublējat savu pieteikuma loģiku divās vietās: servera kods un kods saglabātajām procedūrām, tādējādi sarežģījot datu apstrādes procesu.
Migrācija no vienas DBVS uz citu (DB2, SQL serveris utt.) var radīt problēmas.
Uzglabāto procedūru mērķis un priekšrocības
Saglabātās procedūras uzlabo veiktspēju, uzlabo programmēšanas iespējas un atbalsta datu drošības līdzekļus.
Tā vietā, lai saglabātu bieži lietotu vaicājumu, klienti var atsaukties uz atbilstošo saglabāto procedūru. Kad tiek izsaukta saglabātā procedūra, serveris nekavējoties apstrādā tās saturu.
Papildus reālai vaicājuma izpildei saglabātās procedūras ļauj veikt arī aprēķinus un manipulēt ar datiem – mainīt, dzēst, izpildīt DDL paziņojumus (ne visās DBVS!) un izsaukt citas saglabātās procedūras, kā arī veikt sarežģītu transakciju loģiku. Viens paziņojums ļauj izsaukt sarežģītu skriptu, kas ietverts saglabātā procedūrā, izvairoties no simtiem komandu nosūtīšanas tīklā un jo īpaši no nepieciešamības pārsūtīt lielus datu apjomus no klienta uz serveri.
Lielākajā daļā DBVS pirmo reizi palaižot saglabāto procedūru, tā tiek kompilēta (parsēta un tiek ģenerēts datu piekļuves plāns). Nākotnē tā apstrāde būs ātrāka. Oracle DBMS interpretē datu vārdnīcā saglabāto procesuālo kodu. Sākot ar Oracle 10g, tiek atbalstīta tā sauktā saglabātā procesuālā koda sākotnējā kompilācija C valodā un pēc tam mērķa mašīnas mašīnkodā, pēc kuras, izsaucot saglabāto procedūru, tās kompilētais objekta kods tiek tieši izpildīts.
Programmēšanas iespējas
Izveidoto saglabāto procedūru var izsaukt jebkurā laikā, kas nodrošina modularitāti un veicina koda atkārtotu izmantošanu. Pēdējais padara datubāzi vieglāk uzturējamu, jo tā kļūst izolēta no mainīgajiem uzņēmējdarbības noteikumiem. Jebkurā laikā varat mainīt saglabāto procedūru saskaņā ar jaunajiem noteikumiem. Pēc tam visas lietojumprogrammas, kas to izmanto, automātiski atbildīs jaunajiem uzņēmējdarbības noteikumiem bez tiešām izmaiņām.
Drošība
Saglabāto procedūru izmantošana ļauj ierobežot vai pilnībā likvidēt tiešu lietotāju piekļuvi datu bāzes tabulām, atstājot lietotājiem tikai atļaujas izpildīt saglabātās procedūras, kas nodrošina netiešu un stingri regulētu piekļuvi datiem. Turklāt dažas DBVS atbalsta saglabātās procedūras teksta šifrēšanu (iesaiņošanu).
Šie drošības līdzekļi ļauj datu bāzes struktūru izolēt no lietotāja, nodrošinot datu bāzes integritāti un uzticamību.
Tādu darbību iespējamība kā SQL injekcija ir samazināta, jo labi uzrakstītas saglabātās procedūras papildus pārbauda ievades parametrus pirms vaicājuma nodošanas DBVS.
Saglabāto procedūru ieviešana
Saglabātās procedūras parasti tiek veidotas, izmantojot SQL valodu vai tās īpašo ieviešanu izvēlētajā DBVS. Piemēram, šiem nolūkiem Microsoft SQL Server DBVS ir Transact-SQL valoda, Oracle - PL/SQL, InterBase un Firebird - PSQL, PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL/PL (angļu valodā), Informix valodā - SPL. MySQL diezgan cieši ievēro SQL:2003 standartu, tā valoda ir līdzīga SQL/PL.
Dažas DBVS ļauj izmantot saglabātās procedūras, kas rakstītas jebkurā programmēšanas valodā, kas var izveidot neatkarīgus izpildāmos failus, piemēram, C++ vai Delphi. Microsoft SQL Server terminoloģijā šādas procedūras sauc par paplašinātām saglabātajām procedūrām, un tās ir vienkārši funkcijas, kas ietvertas Win32 DLL. Un, piemēram, programmās Interbase un Firebird no DLL/SO izsauktajām funkcijām ir cits nosaukums – UDF (User Defined Function). MS SQL 2005 ieviesa iespēju rakstīt saglabātās procedūras jebkurā .NET valodā, un nākotnē plānots atteikties no paplašinātām glabājamām procedūrām. Oracle DBVS savukārt ļauj ierakstīt saglabātās procedūras Java valoda. IBM DB2 saglabāto procedūru un funkciju rakstīšana parastajās programmēšanas valodās ir tradicionāls veids, kas tiek atbalstīts no paša sākuma, un SQL procesuālais paplašinājums šai DBVS tika pievienots tikai diezgan vēlīnās versijās pēc tā iekļaušanas ANSI standartā. Informix atbalsta arī procedūras Java un C.
Oracle DBVS saglabātās procedūras var apvienot tā sauktajās paketēs. Pakete sastāv no divām daļām – pakotnes specifikācijas, kas nosaka glabājamās procedūras definīciju, un pakotnes pamatteksta, kas satur tās realizāciju. Tādējādi Oracle ļauj nodalīt programmas koda interfeisu no tā ieviešanas.
IBM DB2 DBVS saglabātās procedūras var apvienot moduļos.
Sintakse
IZVEIDOT PROCEDŪRU `p2`()
SQL DROŠĪBAS DEFINĒJS
KOMENTĀRS "Procedūra"
IZVĒLIES "Sveika pasaule!";
Pirmā koda daļa izveido saglabātu procedūru. Nākamajā ir iekļauti izvēles parametri. Tad nāk nosaukums un, visbeidzot, pašas procedūras pamatteksts.
4 saglabātās procedūras īpašības:
Valoda: pārnesamības nolūkos noklusējuma vērtība ir SQL.
Deterministisks: ja procedūra vienmēr atgriež vienu un to pašu rezultātu un izmanto tos pašus ievades parametrus. Tas ir paredzēts replikācijas un reģistrācijas procesam. Noklusējuma vērtība NAV DETERMINISTIKA.
SQL drošība: zvana laikā tiek pārbaudītas lietotāja tiesības. INVOKER ir lietotājs, kas izsauc saglabāto procedūru. DEFINER ir procedūras “radītājs”. Noklusējuma vērtība ir DEFINER.
Komentārs: dokumentācijas nolūkos noklusējuma vērtība ir ""
Saglabātās procedūras izsaukšana
CALL saglabātās_procedūras_nosaukums (param1, param2, ....)
CALL procedūra1(10 , "virknes parametrs" , @parameter_var);
Saglabātās procedūras modificēšana
MySQL ir ALTER PROCEDURE priekšraksts procedūru maiņai, taču tas ir piemērots tikai noteiktu raksturlielumu maiņai. Ja jāmaina procedūras parametri vai pamatteksts, tas jāizdzēš un jāizveido no jauna.
Noņemšanaglabājasprocedūras
NOTEIKŠANAS PROCEDŪRA, JA IR p2;
Šī ir vienkārša komanda. Paziņojums IF EXISTS uztver kļūdu, ja šādas procedūras nav.
Iespējas
IZVEIDOT PROCEDŪRU proc1(): tukšs parametru saraksts
IZVEIDOT PROCEDŪRU proc1 (IN varname DATA-TYPE): viens ievades parametrs. Vārds IN nav obligāts, jo noklusējuma parametri ir IN (in).
IZVEIDOT PROCEDŪRU proc1 (OUT varname DATA-TYPE): atgriezts viens parametrs.
IZVEIDOT PROCEDŪRU proc1 (INOUT varname DATA-TYPE): viens parametrs, gan ievade, gan atgriešana.
Mainīgā deklarācijas sintakse izskatās šādi:
DEKLARĒT varname DATA-TYPE DEFAULT noklusējuma vērtība;
Strādājot ar SQL Server, lietotāji var izveidot savas procedūras, kas īsteno noteiktas darbības. Saglabātās procedūras ir pilnvērtīgi datu bāzes objekti, un tāpēc katra no tām tiek glabāta noteiktā datu bāzē. Tieša izsaukšana uz saglabāto procedūru ir iespējama tikai tad, ja tā tiek veikta saistībā ar datubāzi, kurā atrodas procedūra.
Saglabāto procedūru veidi
SQL Server ir vairāku veidu saglabātās procedūras.
Sistēmā saglabātās procedūras ir paredzētas dažādu administratīvo darbību veikšanai. Ar viņu palīdzību tiek veiktas gandrīz visas servera administrēšanas darbības. Var teikt, ka sistēmā glabātās procedūras ir saskarne, kas nodrošina darbu ar sistēmas tabulām, kas galu galā nozīmē datu mainīšanu, pievienošanu, dzēšanu un izgūšanu gan lietotāju, gan sistēmas datu bāzu sistēmas tabulās. Sistēmā saglabātajām procedūrām ir prefikss sp_, tās tiek glabātas sistēmas datu bāzē, un tās var izsaukt jebkuras citas datu bāzes kontekstā.
Pielāgotas saglabātās procedūras īsteno noteiktas darbības. Saglabātās procedūras ir pilnvērtīgs datu bāzes objekts. Rezultātā katra saglabātā procedūra atrodas noteiktā datu bāzē, kur tā tiek izpildīta.
Pagaidu saglabātās procedūras pastāv tikai īsu laiku, pēc tam tās automātiski iznīcina serveris. Tie ir sadalīti vietējā un globālā. Vietējās pagaidu saglabātās procedūras var izsaukt tikai no savienojuma, kurā tās ir izveidotas. Veidojot šādu procedūru, tai ir jāpiešķir nosaukums, kas sākas ar vienu # rakstzīmi. Tāpat kā visi pagaidu objekti, šāda veida saglabātās procedūras tiek automātiski izdzēstas, kad lietotājs atvienojas vai serveris tiek restartēts vai apturēts. Globālās pagaidu saglabātās procedūras ir pieejamas jebkuram savienojumam no servera, kuram ir tāda pati procedūra. Lai to definētu, vienkārši piešķiriet tai nosaukumu, kas sākas ar rakstzīmēm ##. Šīs procedūras tiek dzēstas, kad serveris tiek restartēts vai apturēts, vai kad tiek aizvērts savienojums kontekstā, kurā tās tika izveidotas.
Trigeri
Trigeri ir saglabātas procedūras veids. Tie tiek izpildīti, kad tabulā tiek izpildīts datu manipulācijas valodas (DML) operators. Trigeri tiek izmantoti, lai pārbaudītu datu integritāti un arī atsauktu darījumus.
Sprūda ir kompilēta SQL procedūra, kuras izpildi nosaka noteiktu notikumu rašanās relāciju datu bāzē. Trigeru izmantošana lielākoties ir ļoti ērta datu bāzes lietotājiem. Tomēr to izmantošana bieži vien ir saistīta ar papildu resursu izmaksām I/O operācijām. Ja tādus pašus rezultātus (ar daudz mazākām izmaksām) var sasniegt, izmantojot saglabātās procedūras vai lietojumprogrammas, trigeru izmantošana nav praktiska.
Trigeri ir īpašs SQL servera rīks, ko izmanto datu integritātes uzturēšanai datu bāzē. Integritātes ierobežojumi, noteikumi un noklusējuma iestatījumi ne vienmēr var sasniegt vēlamo funkcionalitātes līmeni. Bieži vien ir nepieciešams ieviest sarežģītus datu pārbaudes algoritmus, lai nodrošinātu to uzticamību un realitāti. Turklāt dažreiz jums ir jāuzrauga tabulas vērtību izmaiņas, lai saistītos datus varētu mainīt pēc vajadzības. Trigerus var uzskatīt par sava veida filtriem, kas stājas spēkā pēc tam, kad visas darbības ir pabeigtas saskaņā ar noteikumiem, standarta vērtībām utt.
Sprūda ir īpaša veida glabāšanas procedūra, ko serveris palaiž automātiski, kad tiek mēģināts mainīt datus tabulās, kurām ir saistīti trigeri. Katrs Sprūda ir piesaistīts noteiktai tabulai. Visas tā veiktās datu modifikācijas tiek uzskatītas par vienu darījumu. Ja tiek atklāta kļūda vai datu integritātes pārkāpums, darījums tiek atcelts. Tāpēc izmaiņas ir aizliegtas. Tiek atsauktas arī visas trigera veiktās izmaiņas.
Izveido sprūda tikai datu bāzes īpašnieks. Šis ierobežojums ļauj izvairīties no nejaušām izmaiņām tabulu struktūrā, citu objektu savienošanas veidiem ar tām utt.
Sprūda Tas ir ļoti noderīgs un tajā pašā laikā bīstams līdzeklis. Tātad, ja tās darbības loģika ir nepareiza, jūs varat viegli iznīcināt visu datu bāzi, tāpēc trigeri ir ļoti rūpīgi jāatkļūdo.
Atšķirībā no parastās apakšprogrammas, sprūda tiek izpildīts netieši ikreiz, kad notiek trigera notikums, un tam nav argumentu. Tā aktivizēšanu dažreiz sauc par sprūda iedarbināšanu. Izmantojot trigerus, tiek sasniegti šādi mērķi:
Ievadīto datu pareizības apstiprināšana un sarežģītu datu integritātes ierobežojumu ieviešana, kurus ir grūti vai pat neiespējami uzturēt, izmantojot tabulā iestatītos integritātes ierobežojumus;
izdodot brīdinājumus, kas atgādina veikt noteiktas darbības, atjauninot noteiktā veidā ieviestu tabulu;
audita informācijas uzkrāšana, fiksējot informāciju par veiktajām izmaiņām un personām, kuras tās veikušas;
replikācijas atbalsts.
Komandas CREATE TRIGGER pamata formāts ir parādīts zemāk:
<Определение_триггера>::=
IZVEIDOT TRIGGERI trigera_nosaukums
PIRMS | PĒC<триггерное_событие>
IESLĒGTS<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Aktivizēšanas notikumi sastāv no rindu ievietošanas, dzēšanas un atjaunināšanas tabulā. Pēdējā gadījumā trigera notikumam varat norādīt konkrētus tabulas kolonnu nosaukumus. Aktivizēšanas laiks tiek noteikts, izmantojot atslēgvārdus BEFORE ( Sprūda darbojas pirms ar to saistīto notikumu izpildes) vai PĒC (pēc to izpildes).
Aktivizētāja veiktās darbības ir norādītas katrai rindai (KATRAI RINDAI), uz kuru attiecas notikums, vai tikai vienu reizi katram notikumam (KATRA PAZIŅOJUMAM).
Nepareizi uzrakstīti trigeri var radīt nopietnas problēmas, piemēram, bloķētas slēdzenes. Trigeri var bloķēt daudzus resursus uz ilgu laiku, tāpēc jums vajadzētu pievērst uzmanību Īpaša uzmanība lai samazinātu piekļuves konfliktus.
Sprūda var izveidot tikai pašreizējā datu bāzē, bet trigera ietvaros ir iespējams piekļūt citām datu bāzēm, tostarp tām, kas atrodas attālajā serverī.
1. Procedūrās iekļaujiet rindiņu — IESTATĪT NOCOUNT ON: Ar katru DML izteiksmi SQL serveris rūpīgi atgriež mums ziņojumu ar apstrādāto ierakstu skaitu. Šī informācija Tas var mums noderēt koda atkļūdošanas laikā, bet pēc tam tas būs pilnīgi bezjēdzīgi. Ierakstot SET NOCOUNT ON, mēs atspējojam šo funkciju. Saglabātajām procedūrām, kas satur vairākas izteiksmes un/vai cilpas, šī darbība var ievērojami palielināt veiktspēju, jo tiks ievērojami samazināts trafika apjoms.
IZVEIDOT PROC dbo.ProcName
AS
IESTATĪT NOCOUNT ON;
--Šeit ir procedūras kods
ATLASĪT kolonnu1 NO dbo.TblTable1
-- Pārslēdziet SET NOCOUNT uz sākotnējo stāvokli
SET NOCOUNT OFF;
AIZIET
2. Izmantojiet shēmas nosaukumu kopā ar objekta nosaukumu: Nu, manuprāt, tas ir skaidrs. Šī operācija norāda serverim, kur meklēt objektus, un tā vietā, lai nejauši rakņātos pa tvertnēm, tas nekavējoties zinās, kur tam jāiet un kas jāņem. Ar lielu datu bāzu, tabulu un saglabāto procedūru skaitu tas var ievērojami ietaupīt mūsu laiku un nervus.
SELECT * FROM dbo.MyTable — tas ir labi
-- Tā vietā
SELECT * FROM MyTable -- Un to darīt ir slikti
-- Procedūras izsaukums
EXEC dbo.MyProc — Atkal labs
--Tā vietā
EXEC MyProc — slikti!
3. Neizmantojiet prefiksu “sp_” saglabāto procedūru nosaukumā: Ja mūsu procedūras nosaukums sākas ar "sp_", SQL Server vispirms meklēs savā galvenajā datubāzē. Fakts ir tāds, ka šis prefikss tiek izmantots personīgām servera iekšējām procedūrām. Tāpēc tās izmantošana var radīt papildu izmaksas un pat nepareizus rezultātus, ja tās datubāzē tiek atrasta procedūra ar tādu pašu nosaukumu kā jums.
4. Izmantojiet JA EXISTS (SELECT 1), nevis JA EXISTS (SELECT *): Lai pārbaudītu ieraksta esamību citā tabulā, mēs izmantojam paziņojumu IF EXISTS. Šī izteiksme atgriež patiesu, ja no iekšējās izteiksmes tiek atgriezta vismaz viena vērtība, nav nozīmes “1”, visām kolonnām vai tabulai. Atgrieztie dati būtībā netiek izmantoti nekādā veidā. Tādējādi, lai datu pārraides laikā saspiestu trafiku, loģiskāk ir izmantot “1”, kā parādīts zemāk:
JA PASTĀV (IZVĒLIES 1 NO sysobjects
WHERE nosaukums = "MyTable" UN veids = "U")
5. Izmantojiet TRY-Catch, lai uztvertu kļūdas: Pirms 2005. gada serveriem pēc katra pieprasījuma procedūrā tika ierakstīts milzīgs kļūdu pārbaužu skaits. Vairāk koda vienmēr patērē vairāk resursu un vairāk laika. Izmantojot 2005. gada SQL Server, pareizāka un ērts veidsšīs problēmas risinājumi:
SĀC MĒĢINĀT
--kods
BEIDZIET MĒĢINĀJUMU
SĀC NOZVEJU
-- kļūda tverot kodu
BEIGAS NOZVEJAS
Secinājums
Būtībā tas ir viss, kas man šodien ir. Es vēlreiz atkārtoju, ka šeit ir tikai tās metodes, kuras es personīgi izmantoju savā praksē, un es varu galvot par to efektivitāti.
P.S.
Mans pirmais raksts, nevērtējiet pārāk skarbi.
SQL saglabātās procedūras ir izpildāmi programmu moduļi, kurus var uzglabāt dažādu objektu veidā. Citiem vārdiem sakot, tas ir objekts, kas satur SQL paziņojumus. Šīs saglabātās procedūras var izpildīt klientā lietojumprogrammas lai iegūtu labu sniegumu. Turklāt šādi objekti bieži tiek izsaukti no citiem skriptiem vai pat no kādas citas sadaļas.
Ievads
Daudzi cilvēki uzskata, ka tie ir līdzīgi dažādām procedūrām (attiecīgi, izņemot MS SQL). Varbūt tā ir taisnība. Tiem ir līdzīgi parametri un tie var radīt līdzīgas vērtības. Turklāt dažos gadījumos tie pieskaras. Piemēram, tās ir apvienotas ar DDL un DML datu bāzēm, kā arī lietotāja funkcijām (ar koda nosaukumu UDF).
Patiesībā SQL saglabātajām procedūrām ir plašs priekšrocību klāsts, kas tās atšķir no līdzīgiem procesiem. Drošība, programmēšanas elastība, produktivitāte – tas viss piesaista arvien vairāk lietotāju, kuri strādā ar datu bāzēm. Procedūru popularitātes maksimums bija 2005.–2010. gadā, kad tika izlaista Microsoft programma ar nosaukumu “SQL Server Management Studio”. Ar tās palīdzību darbs ar datu bāzēm ir kļuvis daudz vienkāršāks, praktiskāks un ērtāks. Gadu no gada šis ieguva popularitāti programmētāju vidū. Mūsdienās tā ir absolūti pazīstama programma, kas lietotājiem, kuri “sazinās” ar datu bāzēm, ir līdzvērtīga Excel.
Kad tiek izsaukta procedūra, to uzreiz apstrādā pats serveris bez liekiem procesiem vai lietotāja iejaukšanās. Pēc tam jūs varat veikt jebkādu dzēšanu, izpildi vai modifikāciju. Par to visu atbild DDL operators, kurš viens pats veic vissarežģītākās darbības objektu apstrādei. Turklāt tas viss notiek ļoti ātri, un serveris faktiski netiek ielādēts. Šāds ātrums un veiktspēja ļauj ļoti ātri pārsūtīt lielus informācijas apjomus no lietotāja uz serveri un otrādi.
Lai ieviestu šo tehnoloģiju darbam ar informāciju, ir vairākas programmēšanas valodas. Tie ietver, piemēram, PL/SQL no Oracle, PSQL InterBase un Firebird sistēmās, kā arī klasisko Microsoft Transact-SQL. Visi no tiem ir paredzēti saglabāto procedūru izveidei un izpildei, kas ļauj lielu datu bāzu procesoriem izmantot savus algoritmus. Tas nepieciešams arī tādēļ, lai personas, kas pārvalda šādu informāciju, varētu aizsargāt visus objektus no trešo personu nesankcionētas piekļuves un attiecīgi noteiktu datu izveidošanas, grozīšanas vai dzēšanas.
Produktivitāte
Šos datu bāzes objektus var ieprogrammēt dažādos veidos. Tas ļauj lietotājiem izvēlēties vispiemērotāko izmantotās metodes veidu, ietaupot pūles un laiku. Turklāt procedūra tiek apstrādāta pati, kas ļauj izvairīties no milzīgā laika, kas pavadīts saziņai starp serveri un lietotāju. Tāpat moduli var pārprogrammēt un mainīt vēlamajā virzienā absolūti jebkurā laikā. Īpaši vērts atzīmēt ātrumu, ar kādu tiek palaista SQL saglabātā procedūra: šis process notiek ātrāk nekā citi tam līdzīgi, kas padara to ērtu un universālu.
Drošība
Šis informācijas apstrādes veids atšķiras no līdzīgiem procesiem ar to, ka garantē paaugstinātu drošību. To nodrošina fakts, ka var pilnībā izslēgt citu lietotāju piekļuvi procedūrām. Tas ļaus administratoram veikt darbības ar tiem neatkarīgi, nebaidoties no informācijas pārtveršanas vai nesankcionētas piekļuves datubāzei.
Datu pārsūtīšana
Saistība starp SQL saglabāto procedūru un klienta lietojumprogrammu ir parametru un atgriešanas vērtību izmantošana. Pēdējam nav jānodod dati saglabātajai procedūrai, bet šī informācija (galvenokārt pēc lietotāja pieprasījuma) tiek apstrādāta SQL. Kad saglabātā procedūra ir pabeigusi savu darbu, tā nosūta datu paketes atpakaļ (bet atkal pēc izvēles) lietojumprogrammai, kas to izsauca, izmantojot dažādas metodes, ar kuras palīdzību var veikt gan saglabātas SQL procedūras izsaukumu, gan atgriešanos, piemēram:
Datu pārsūtīšana, izmantojot parametru Output type;
Datu nodošana, izmantojot atgriešanas operatoru;
Datu nodošana, izmantojot atlases operatoru.
Tagad izdomāsim, kā šis process izskatās no iekšpuses.
1. Izveidojiet EXEC saglabātu procedūru SQL
Jūs varat izveidot procedūru MS SQL (Managment Studio). Pēc procedūras izveides tā tiks uzskaitīta datu bāzes programmējamajā mezglā, kurā izveidošanas procedūru izpilda operators. Lai izpildītu, SQL saglabātās procedūras izmanto EXEC procesu, kas satur paša objekta nosaukumu.
Veidojot procedūru, vispirms tiek parādīts tās nosaukums, kam seko viens vai vairāki tai piešķirtie parametri. Parametri var būt neobligāti. Pēc tam, kad parametrs(-i), tas ir, procedūras pamatteksts, ir uzrakstīti, ir jāveic dažas nepieciešamās darbības.
Lieta ir tāda, ka ķermenī var būt lokāli mainīgie, un šie mainīgie ir arī lokāli attiecībā uz procedūrām. Citiem vārdiem sakot, tos var skatīt tikai Microsoft SQL Server procedūras pamattekstā. Uzglabātās procedūras šajā gadījumā tiek uzskatītas par vietējām.
Tātad, lai izveidotu procedūru, mums ir nepieciešams procedūras nosaukums un vismaz viens parametrs kā procedūras pamatteksts. Ņemiet vērā, ka šajā gadījumā lieliska iespēja ir izveidot un izpildīt procedūru ar shēmas nosaukumu klasifikatorā.
Procedūras pamatteksts var būt jebkāda veida, piemēram, izveidot tabulu, ievietot vienu vai vairākas tabulas rindas, noteikt datu bāzes veidu un raksturu utt. Tomēr procedūras iestāde ierobežo noteiktu darbību veikšanu tās ietvaros. Daži no svarīgiem ierobežojumiem ir uzskaitīti zemāk:
Ķermenim nevajadzētu radīt nekādu citu uzglabātu procedūru;
Ķermenis nedrīkst radīt nepatiesu priekšstatu par objektu;
Ķermenim nevajadzētu radīt nekādus trigerus.
2. Mainīgā iestatīšana procedūras pamattekstā
Mainīgos varat iestatīt lokālus procedūras pamattekstā, un tad tie atradīsies tikai procedūras pamattekstā. Laba prakse ir izveidot mainīgos lielumus saglabātās procedūras pamatteksta sākumā. Bet jūs varat arī iestatīt mainīgos jebkurā konkrētā objekta pamattekstā.
Dažreiz jūs varat pamanīt, ka vienā rindā ir iestatīti vairāki mainīgie, un katrs mainīgā parametrs ir atdalīts ar komatu. Ņemiet vērā arī to, ka mainīgā lieluma prefikss ir @. Procedūras pamattekstā varat iestatīt mainīgo, kur vien vēlaties. Piemēram, @NAME1 mainīgais var tikt deklarēts procedūras pamatteksta beigās. Lai deklarētajam mainīgajam piešķirtu vērtību, tiek izmantota personas datu kopa. Atšķirībā no situācijas, kad vienā rindā tiek deklarēts vairāk nekā viens mainīgais, šajā situācijā tiek izmantota tikai viena personas datu kopa.
Lietotāji bieži uzdod jautājumu: "Kā procedūras pamattekstā vienā paziņojumā piešķirt vairākas vērtības?" Nu. Tas ir interesants jautājums, taču to izdarīt ir daudz vieglāk, nekā jūs domājat. Atbilde: izmantojot tādus pārus kā "Select Var = vērtība". Varat izmantot šos pārus, atdalot tos ar komatu.
Dažādi piemēri parāda, kā cilvēki izveido vienkāršu saglabātu procedūru un izpilda to. Tomēr procedūra var pieņemt tādus parametrus, ka procesam, kas to izsauc, būs tai tuvas vērtības (bet ne vienmēr). Ja tie sakrīt, tad organismā sākas attiecīgi procesi. Piemēram, ja izveidojat procedūru, kas pieņems pilsētu un reģionu no zvanītāja un atgriezīs datus par to, cik autoru pieder attiecīgajai pilsētai un reģionam. Procedūra prasīs datu bāzes autoru tabulas, piemēram, Pubs, lai veiktu šo autoru skaitīšanu. Piemēram, lai iegūtu šīs datu bāzes, Google lejupielādē SQL skriptu no SQL2005 lapas.
Iepriekšējā piemērā procedūrai tiek ņemti divi parametri, kas angļu valoda nosacīti sauksies @State un @City. Datu tips atbilst lietojumprogrammā definētajam veidam. Procedūras pamattekstam ir iekšējie mainīgie @TotalAuthors, un šis mainīgais tiek izmantots, lai parādītu autoru skaitu. Tālāk seko vaicājumu atlases sadaļa, kurā tiek aprēķināts viss. Visbeidzot, aprēķinātā vērtība tiek izdrukāta izvades logā, izmantojot drukas paziņojumu.
Kā izpildīt saglabātu procedūru SQL
Ir divi veidi, kā veikt procedūru. Pirmais veids, nododot parametrus, parāda, kā pēc procedūras nosaukuma tiek izpildīts ar komatu atdalīts saraksts. Pieņemsim, ka mums ir divas vērtības (kā iepriekšējā piemērā). Šīs vērtības tiek apkopotas, izmantojot @State un @City procedūru parametru mainīgos. Šajā parametru nodošanas metodē kārtība ir svarīga. Šo metodi sauc par kārtas argumentu nodošanu. Otrajā metodē parametri jau ir tieši piešķirti, un šajā gadījumā secība nav svarīga. Šī otrā metode ir pazīstama kā nosauktu argumentu nodošana.
Procedūra var nedaudz atšķirties no parastās. Viss ir tāds pats kā iepriekšējā piemērā, bet tikai šeit parametri ir nobīdīti. Tas nozīmē, ka parametrs @City tiek saglabāts vispirms un @State tiek saglabāts blakus noklusējuma vērtībai. Noklusējuma parametrs parasti tiek izcelts atsevišķi. SQL saglabātās procedūras tiek nodotas tikai kā parametri. Šajā gadījumā ar nosacījumu, ka parametrs "UT" aizstāj noklusējuma vērtību "CA". Otrajā izpildē parametram @City tiek nodota tikai viena argumenta vērtība, un parametrs @State iegūst noklusējuma vērtību “CA”. Pieredzējuši programmētāji iesaka visiem mainīgajiem pēc noklusējuma atrasties parametru saraksta beigās. Pretējā gadījumā izpilde nav iespējama, un tad jums ir jāstrādā ar nosaukto argumentu nodošanu, kas ir ilgāks un sarežģītāks.
4. SQL Server saglabātās procedūras: atgriešanas metodes
Ir trīs svarīgi veidi, kā nosūtīt datus izsauktajā saglabātajā procedūrā. Tie ir uzskaitīti zemāk:
Atgriezt saglabātās procedūras vērtību;
Saglabāto procedūru parametru izvade;
Izvēloties vienu no saglabātajām procedūrām.
4.1 Vērtību atgriešana no SQL saglabātajām procedūrām
Šajā tehnikā procedūra piešķir vērtību vietējam mainīgajam un atgriež to. Procedūra var arī tieši atgriezt nemainīgu vērtību. Nākamajā piemērā esam izveidojuši procedūru, kas atgriež kopējais skaits autori. Ja salīdzina šo procedūru ar iepriekšējām, var redzēt, ka drukas vērtība ir apgriezta.
Tagad apskatīsim, kā izpildīt procedūru un izdrukāt tās atgriešanas vērtību. Lai veiktu procedūru, ir jāiestata mainīgais un jādrukā, kas tiek veikta pēc visa šī procesa. Ņemiet vērā, ka drukas priekšraksta vietā varat izmantot atlases priekšrakstu, piemēram, Select @RetValue, kā arī OutputValue.
4.2. SQL saglabāto procedūru parametru izvade
Atbildes vērtību var izmantot, lai atgrieztu vienu mainīgo, ko mēs redzējām iepriekšējā piemērā. Izvades parametra izmantošana ļauj zvanītājam nosūtīt vienu vai vairākas mainīgās vērtības. Izvades parametrs tiek precīzi apzīmēts ar šo atslēgvārdu “Izvade”, veidojot procedūru. Ja parametrs ir dots kā izejas parametrs, tad procedūras objektam tam ir jāpiešķir vērtība. SQL saglabātās procedūras, kuru piemērus var redzēt zemāk, šajā gadījumā atgriezieties ar kopsavilkuma informāciju.
Mūsu piemērā būs divi izvades nosaukumi: @TotalAuthors un @TotalNoContract. Tie ir norādīti parametru sarakstā. Šie mainīgie piešķir vērtības procedūras pamattekstā. Kad mēs izmantojam izvades parametrus, zvanītājs var redzēt vērtību, kas iestatīta procedūras pamattekstā.
Arī iepriekšējā scenārijā tiek deklarēti divi mainīgie, lai redzētu vērtības, kuras MS SQL Server saglabāja procedūras, kas iestatītas izvades parametrā. Pēc tam procedūra tiek veikta, ievadot parametra “CA” normālo vērtību. Tālāk norādītās opcijas tiek izvadīti, un tāpēc deklarētie mainīgie tiek nodoti noteiktajā secībā. Ņemiet vērā, ka, izejot cauri mainīgajiem, izvade atslēgvārds arī šeit jautāja. Kad procedūra ir veiksmīgi pabeigta, ziņojuma logā tiek parādītas izvades parametru atgrieztās vērtības.
4.3. Izvēloties vienu no SQL saglabātajām procedūrām
Šo paņēmienu izmanto, lai atgrieztu vērtību kopu kā datu tabulu (RecordSet) izsaucējai saglabātajai procedūrai. Šajā piemērā SQL saglabātā procedūra ar @AuthID parametriem vaicā tabulu Autori, filtrējot atgrieztos ierakstus, izmantojot šo @AuthId parametru. Paziņojums Select nosaka, kas ir jāatdod saglabātās procedūras izsaucējam. Kad tiek izpildīta saglabātā procedūra, AuthId tiek nosūtīts atpakaļ. Šī procedūra šeit vienmēr atgriež tikai vienu ierakstu vai neatgriež vispār. Taču saglabātajai procedūrai nav ierobežojumu vairāk nekā viena ieraksta atgriešanai. Nereti tiek redzēti piemēri, kad dati tiek atgriezti, izmantojot atlasītus parametrus, kas ietver aprēķinātos mainīgos, sniedzot vairākas kopsummas.
Beidzot
Saglabātā procedūra ir diezgan nopietns programmas modulis, kas atgriežas vai iziet, kā arī iestata nepieciešamos mainīgos, pateicoties klienta lietojumprogrammai. Tā kā saglabātā procedūra pati darbojas serverī, var izvairīties no milzīga datu apmaiņas starp serveri un klienta lietojumprogrammu (dažiem aprēķiniem). Tas ļauj samazināt slodzi SQL serveris, kas, protams, nāk par labu to turētājiem. Viens no apakštipiem ir T SQL saglabātās procedūras, taču to izpēte ir nepieciešama tiem, kas veido iespaidīgas datu bāzes. Ir arī liels, pat milzīgs daudzums nianšu, kas var noderēt, pētot saglabātās procedūras, taču tas vairāk vajadzīgs tiem, kas plāno iesaistīties programmēšanā, tostarp profesionāli.
Procedūras deklarācija
IZVEIDOT PROCEDŪRU [({IN|OUT|INOUT} [,…])]
[DINAMISKO REZULTĀTU IESTATĪJUMS ]
SĀKT [ATOMIKA]
BEIGAS
Atslēgvārdi
. IN (Input) – ievades parametrs
. OUT (Output) – izejas parametrs
. INOUT – ievade un izvade, kā arī lauks (bez parametriem)
. DINAMISKO REZULTĀTU IESTATĪJUMS norāda, ka procedūra var atvērt noteiktu skaitu kursoru, kas paliks atvērti pēc procedūras atgriešanās
Piezīmes
Tīkla un steku pārslodzes dēļ nav ieteicams izmantot daudzus parametrus saglabātajās procedūrās (galvenokārt lielus skaitļus un rakstzīmju virknes). Praksē esošajiem Transact-SQL, PL/SQL un Informix dialektiem ir būtiskas atšķirības no standarta gan parametru deklarācijā un lietošanā, gan mainīgo deklarācijā, gan apakšprogrammu izsaukšanā. Korporācija Microsoft iesaka izmantot šādu aptuveno informāciju, lai aprēķinātu saglabāto procedūru kešatmiņas lielumu:
=(maksimālais vienlaicīgo lietotāju skaits)*(lielākā izpildes plāna lielums)*1,25. Izpildes plāna lieluma noteikšanu lapās var veikt, izmantojot komandu: DBCC MEMUSAGE.
Procedūras izsaukšana
Daudzās esošajās DBVS saglabātās procedūras tiek izsauktas, izmantojot operatoru:
IZPILDĪT PROCEDŪRU [(][)]
Piezīme: izsaukumus uz saglabātajām procedūrām var veikt no lietojumprogrammas, citas saglabātas procedūras vai interaktīvi.
Procedūras deklarācijas piemērs
IZVEIDOT PROCEDŪRU Proc1 AS //deklarē procedūru
DECLARE Cur1 CURSOR FOR SELECT SNname, City FROM SalesPeople WHERE Vērtējums>200 //kursora deklarēšana
OPEN Cur1 //atver kursoru
FETCH NEXT FROM Cur1 //nolasa datus no kursora
WHILE @@Fetch_Status=0
SĀKT
IEŅEMT TĀLĀK NO Cur1
BEIGAS
CLOSE Cur1 //aizveriet kursoru
ATZĪT Cur1
IZPILDĪT Proc1 //palaist procedūru
Polimorfisms
Divas apakšprogrammas ar vienādu nosaukumu var izveidot vienā shēmā, ja abu apakšprogrammu parametri ir pietiekami atšķirīgi viens no otra, lai tos varētu atšķirt. Lai atšķirtu divas rutīnas ar vienādu nosaukumu vienā shēmā, katrai no tām tiek piešķirts alternatīvs un unikāls nosaukums (konkrēts nosaukums). Šāds nosaukums var būt skaidri norādīts, kad ir definēta apakšprogramma. Izsaucot apakšprogrammas ar vairākiem identiskiem nosaukumiem, nepieciešamās apakšprogrammas noteikšana tiek veikta vairākos posmos:
. Sākotnēji tiek definētas visas procedūras ar norādīto nosaukumu, un, ja tādu nav, tad visas funkcijas ar doto nosaukumu.
. Tālākai analīzei tikai tās apakšprogrammas, attiecībā uz kurām šis lietotājs ir izpildes privilēģija (EXECUTE).
. Tiem tiek atlasīti tie, kuru parametru skaits atbilst izsaukuma argumentu skaitam. Tiek pārbaudīti parametru norādītie datu tipi un to pozīcijas.
. Ja ir atlikušas vairākas apakšprogrammas, tiek atlasīta tā, kuras kvalificējošais nosaukums ir īsāks.
Praksē Oracle polimorfisms tiek atbalstīts funkcijām, kas deklarētas tikai pakotnē, DB@ - dažādās shēmās, un Sybase un MS SQL Server pārslodze ir aizliegta.
Procedūru dzēšana un mainīšana
Lai noņemtu procedūru, izmantojiet operatoru:
Lai mainītu procedūru, izmantojiet operatoru:
MAINĪT PROCEDŪRU [([{IN|OUT|INOUT}])]
SĀKT [ATOMIKA]
BEIGAS
Privilēģijas veikt procedūras
GRANT IZPILDĪT UZ |PUBLISKĀ [AR DOTĀCIJAS IESPĒJU]
Sistēmas procedūras
Daudzām DBVS (tostarp SQL Server) ir īpašs iebūvētu sistēmā saglabāto procedūru kopums, ko varat izmantot saviem mērķiem.