Proceduri stocate SQL Server. Procedură stocată

scopul de a lucra - Aflați cum să creați și să utilizați proceduri stocate pe serverul de baze de date.

1. întinderea tuturor exemplelor, analiza rezultatelor execuției lor în utilitate SQL Server Studio de management. Verificarea prezenței proceselor create în baza de date curentă.

2. Efectuați toate exemplele și sarcinile în cursul lucrărilor de laborator.

3. Efectuați sarcini individuale prin opțiuni.

Explicarea muncii

Pentru a stăpâni procedurile stocate de programare, utilizați baza de date când baza de date a numelui Db_books.care a fost creat în numărul de muncă de laborator 1. Când efectuați exemple și sarcini, plătiți pentru respectarea numelor bazei de date, a tabelelor și a altor obiecte de proiect.

Proceduri stocate Există un set de comenzi constând din unul sau mai multe operatori SQL sau funcții și stocate în baza de date în formularul de compilare.

Tipuri de proceduri stocate

Procedurile stocate de sistem sunt destinate să efectueze diverse acțiuni administrative. Aproape toate acțiunile de administrare a serverului sunt efectuate cu ajutorul lor. Se poate spune că procedurile stocate de sistem sunt o interfață care oferă lucrări cu tabelele de sistem. Procedurile stocate de sistem au prefixul SP_, sunt stocate în baza de date a sistemului și pot fi cauzate în contextul oricărei alte baze de date.

Proceduri stocate personalizate implementează anumite acțiuni. Proceduri stocate - un obiect de bază de date cu drepturi depline. Ca rezultat, fiecare procedură stocată este localizată într-o bază de date specifică, unde se efectuează.

Procedurile stocate temporare există doar un timp, după care sunt distruse automat de server. Ele sunt împărțite în LOSOSAL și GLOBAL. Procedurile locale stocate temporare pot fi cauzate numai de acel compus în care este creat. La crearea unei astfel de proceduri, trebuie să dea un nume pornind de la un simbol #. Ca toate obiectele temporare, procedurile stocate de acest tip sunt șterse automat când utilizatorul este deconectat, reporniți sau opriți serverul. Procedurile stocate temporare globale sunt disponibile pentru orice conexiuni de server pe care există aceeași procedură. Pentru ao determina, este suficient să-i dați un nume care începe cu ## caractere. Aceste proceduri sunt șterse la repornirea sau oprirea serverului, precum și la închiderea conexiunii, în contextul căruia au fost create.

Crearea, schimbarea procedurilor stocate

Crearea procedurii stocate implică decizia următoarelor rate: Planificarea drepturilor de acces. La crearea unei proceduri stocate, ar trebui să se țină cont de faptul că va avea aceleași drepturi de acces la obiectele bazei de date, care și-au creat utilizatorul; Determinarea parametrilor procedurilor de stocare, procedurile stocate pot avea parametri de intrare și ieșire; Dezvoltarea procedurii stocate de cod. Codul de procedură poate conține o secvență de comenzi SQL, inclusiv apelarea altor proceduri stocate.

Sintaxa operatorului de creare a unei noi sau modificări a procedurii stocate disponibile în denumirile serverului MS SQL:

(Creați | alter) proc [edure] nume_name_name [(număr] [parameter_name tip_data) [Varit] [\u003d implicit] [ieșire]] [, ... N] [cu (recompil | criptare | Recompilare, criptare)] [Pentru replicare] ca operator SQL_ [... N]

Luați în considerare parametrii acestei comenzi.

Folosind SP_, #, ## Prefixele create de procedură pot fi definite ca fiind sistematice sau temporare. Așa cum se poate observa din sintaxa de comandă, nu este permisă specificarea numelui proprietarului la care procedura creată, precum și numele bazei de date, unde trebuie plasate. Astfel, pentru a plasa procedura stocată creată într-o bază de date specifică, trebuie să rulați comanda Creare procedură în contextul acestei baze de date. Când contactați corpul procedurii stocate la obiectele din aceeași bază de date, puteți utiliza nume scurte, adică fără a specifica numele bazei de date. Când trebuie să vă referiți la obiecte situate în alte baze de date, specificând numele bazei de date.

Pentru a transfera datele de intrare și ieșire în procedura stocată creată, numele parametrilor trebuie să înceapă cu simbolul @. Într-o procedură stocată, puteți seta mulți parametri separați prin virgule. În corpul procedurii, variabilele locale nu trebuie aplicate, ale căror nume au coincis cu numele parametrilor acestei proceduri. Pentru a determina tipul acestor parametri ai procedurii stocate, orice tip de date SQL sunt adecvate, inclusiv definite de utilizator. Cu toate acestea, tipul de date cursor poate fi utilizat numai ca parametru de ieșire al procedurii stocate, adică Cu ieșirea cu cuvânt cheie.

Prezența cuvântului cheie de ieșire înseamnă că parametrul corespunzător este conceput pentru a returna datele din procedura stocată. Cu toate acestea, acest lucru nu înseamnă că parametrul nu este adecvat pentru transmiterea valorilor în procedura stocată. Specificarea cuvântului cheie de ieșire instruiește serverul atunci când iese din procedura stocată pentru a atribui valoarea curentă a parametrului variabil local, care a fost specificat când procedura este numită valoarea parametrului. Rețineți că atunci când se precizează ieșirea cuvântului cheie, valoarea parametrului corespunzător la apelarea procedurii poate fi specificată numai utilizând o variabilă locală. Nu este permisă utilizarea unor expresii sau constante admise pentru parametrii convenționali. Varitoarea cuvântului cheie este utilizat împreună cu parametrul de ieșire având un tip de cursor. Aceasta determină că parametrul de ieșire va fi setul rezultat.

Cuvântul cheie implicit este o valoare care va accepta parametrul implicit corespunzător. Astfel, când se numește procedura, nu puteți specifica valoarea parametrului corespunzător.

Deoarece serverul lade planul de execuție a interogării și a compilat codul, la apelul ulterior, procedurile vor fi utilizate deja. Cu toate acestea, în unele cazuri, este încă necesar să se recomigureze codul procedurii. Specificarea cuvântului cheie recompilul prescrie sistemul pentru a crea un plan de procedură stocat pentru fiecare apel.

Parametrul de replicare este în cerere atunci când replicați datele și includerea procedurii stocate create ca articol în publicare. Cuvântul cheie de criptare instruiește serverul să cripteze codul procedurii stocate, care poate oferi protecție împotriva utilizării algoritmilor drepturilor de autor care implementează activitatea procedurii stocate. Cuvântul cheie este situat la începutul corpului procedurii stocate. În corpul procedurii, aproape toate comenzile SQL pot fi aplicate, tranzacții declarate, blocați blocarea și apelați alte proceduri stocate. Ieșirea procedurii stocate se poate face utilizând comanda de retur.

Eliminarea procedurii stocate

Procedura de picătură (nume) [, ... N]

Efectuarea procedurilor stocate

Pentru a efectua procedura stocată, se utilizează comanda: [exec [ute] name_name [; număr] [[@ parameter_name \u003d] (valoare | @_name_name) [ieșire] | [Implicit]] [, ... N]

Dacă apelul la procedura stocată nu este singura comandă din pachet, este necesară prezența comenzii Execute. Mai mult decât atât, această comandă este necesară pentru a apela procedura de la corpul unei alte proceduri sau declanșare.

Utilizarea cuvântului cheie de ieșire la apelarea procedurii este rezolvată numai pentru parametrii care au fost anunțați atunci când creați o procedură cu o ieșire a cuvântului cheie.

Când apelați la o procedură, cuvântul cheie implicit este specificat pentru parametru, va fi utilizată valoarea implicită. În esență, cuvântul specificat este permis numai pentru acești parametri pentru care este definită valoarea implicită.

Din sintaxa comenzii executate, este clar că numele parametrilor pot fi omise atunci când procedura este apelată. Cu toate acestea, în acest caz, utilizatorul trebuie să precizeze valorile pentru parametrii din aceeași ordine, în care au fost enumerați la crearea unei proceduri. Atribuiți valoarea implicită parametrului prin simpla trecere la listate, este imposibil. Dacă parametrii sunt necesari pentru care este definită valoarea implicită, o indicație de nume de parametru suficient de explicită la apelarea procedurii stocate. Mai mult, în așa fel încât să puteți enumera parametrii și valorile lor într-o ordine arbitrară.

Rețineți că atunci când sunați la procedură, sunt indicate numele parametrilor cu valori sau numai valorile fără numele parametrului. Combinația lor nu este permisă.

Utilizați rentabilitatea procedurii stocate

Vă permite să ieșiți din procedură în orice punct din starea specificată și vă permite, de asemenea, să transferați rezultatul procedurii prin numărul prin care puteți evalua calitatea și corectitudinea procedurii. Un exemplu de creare a unei proceduri fără parametri:

Creați proceduri Count_books ca număr de selecție (cod_book) din cărți Go

Exercitiul 1.

Exec Count_books.

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametrul de intrare:

Creați proceduri Count_Books_pages @Cout_Pages As INT ca număr de selectare (cod_book) din cărțile în care paginile\u003e \u003d @Count_pages Go

Sarcina 2.. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați-l folosind comanda

Exec Count_books_pages 100.

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametrii de intrare:

Creați proceduri Count_books_TITLE @Count_pages ca int, @sitle ca char (10) ca număr de selecție (cod_book) din cărți în care paginile\u003e \u003d @Count_pages și titlu_book cum ar fi @title Go

Sarcina 3. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați-l folosind comanda

Exec Count_books_TITLE 100, "P%"

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametrii de intrare și parametrul de ieșire:

Creare proceduri Count_books_itogo @count_pages Int, @sitle Char (10), @itogo Int Ieșire ca selecte @itogo \u003d Count (Code_book) din cărți în care paginile\u003e \u003d @count_pages și titlu_book cum ar fi @title Go

Sarcina 4. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați folosind setul de comandă:

SQL\u003e Declară @q ca int exear numă_books_itogo 100, "P%", @ @que Ieșire Selectați @q

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametrii de intrare și returnarea:

Crearea procedurii de verificare @param Int ca (selectați NAME_AUTHOR de la autori în cazul în care Code_Author \u003d @param) \u003d "Pushkin A.s. RETURN 1 altceva retur 2

Sarcina 5. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați-l folosind comenzi:

Declară @return_Status Int Exec @return_Status \u003d Checkname 1 Selectați "Starea returnării" \u003d @return_Status

Un exemplu de a crea o procedură fără parametri pentru a crește valoarea câmpului cheie în tabelul de achiziții de 2 ori:

Creați Proc Update_Proc ca achiziții de actualizare Setați codul_purchase \u003d code_purchase * 2

Sarcina 6. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați-l folosind comanda

Exec Update_proc.

Un exemplu de procedură cu un parametru de intrare pentru toate informațiile despre un anumit autori:

Creați PROC SELECT_AUTHOR @K Char (30) ca selectat * de la autori unde name_author \u003d @k

Sarcina 7.

Exec Select_Author "Pușkin A.s." sau SELECT_AUTHOR @ K \u003d "Pușkin A.s." sau Exec Select_Author @ k \u003d "Pușkin A.s."

Un exemplu de a crea o procedură cu o parametru de intrare și o valoare implicită pentru a mări valoarea câmpului cheie în tabelul de achiziții la un anumit număr de ori (implicit de 2 ori):

Creați Proc Update_proc @p Int \u003d 2 ca achiziții de actualizare setați cod_purchase \u003d code_purchase * @p

Procedura nu returnează niciun fel de date.

Sarcina 8. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați-l folosind comenzi:

Exec Update_proc 4 sau Exec Update_proc @p \u003d 4 sau Exec Update_proc - Valoarea implicită este utilizată.

Un exemplu de creare a unei proceduri cu parametrii de intrare și ieșire. Creați o procedură pentru a determina numărul de comenzi efectuate în perioada specificată:

Creați Proc Count_Purchases @ D1 SmalLDateTime, @ D2 smalLDADETime, @C Int ieșire ca selecte @ C \u003d Count (Code_purchase) de la achiziții în cazul în care DATE_Order între @ d1 și @ D2 set @C \u003d Isnull (@C, 0)

Sarcina 9. Creați această procedură în secțiunea Proceduri stocate a datelor DB_Books prin intermediul utilitarului SQL Server Management Studio. Rulați-l folosind comenzi:

Declarați @ C2 Int Exec Count_Purchaze '01 - Jun- 2006 ', '01 - Jul- 2006', @ Ieșire C2 Selectați @ C2

Opțiuni de K. lucrări de laborator №4

General. În utilitatea SQL Server Management Studio pentru a crea pagina noua Pentru cod ("Creați o interogare" buton). Programat, efectuați o bază de date activă DB_Books utilizând operatorul de utilizare. Creați proceduri stocate utilizând producătorii de proceduri Creare și determină independent numele procedurilor. Fiecare procedură va efectua de o interogare SQL care a fost efectuată în a doua activitate de laborator. Mai mult, codul de interogare SQL trebuie modificat, astfel încât acestea să poată fi transmise valorilor câmpului pentru care se efectuează căutarea.

De exemplu, sarcina inițială și cererea de laborator numărul 2:

/ * Alegeți dintre furnizorii de cărți de referință (Tabelul livrărilor) Numele, telefoanele și Inn (NAME_Company, Telefon și Inn), a cărui nume de companie (nume_company) "OAO World".

Selectați NAME_COMPANY, Telefon, Inn din livrări unde NAME_Company \u003d "OAO Lumea"

* / - Această procedură de lucru va fi creată:

Creați PROC SELECT_NAME_COMPANY @comp Char (30) ca selectați NAME_Company, telefon, Inn de la livrări în cazul în care_company \u003d @comp

- Ordinea procedurii este utilizată de comandă:

Exec Select_Name_Company "Ojsc World"

Lista de sarcini

În utilitarul SQL Server Management Studio, creați un nou program. Programat, efectuați o bază de date individuală activă creată în numărul de muncă de laborator 1, utilizând operatorul de utilizare. Creați proceduri stocate utilizând producătorii de proceduri Creare și determină independent numele procedurilor. Fiecare procedură va efectua de o interogare SQL care sunt prezentate sub formă de sarcini separate prin opțiuni.

Opțiunea 1

1. Pentru a retrage o listă de angajați care au cel puțin un copil.

2. să retragă o listă de copii care au dat cadouri în perioada specificată.

3. să retragă o listă de părinți care au copii minori.

4. Pentru a retrage informații despre cadouri cu un cost mai mare decât numărul specificat sortat după dată.

Opțiunea 2.

1. Afișați lista de instrumente cu tipul specificat.

2. Îndepărtați numărul de dispozitive reparate și costul total al reparațiilor la expertul specificat.

3. Aducerea listei proprietarilor de instrumente și a numărului de apeluri, sortate după numărul de apeluri descrescătoare.

4. Afișează informații despre maeștri cu o descărcare mai mare decât numărul specificat sau cu o dată de recepție mai mică decât data specificată.

Opțiunea 3.

2. Pentru a afișa o listă de coduri de vânzări pentru care culorile vândute sunt mai mult decât numărul specificat.

3. Îndepărtați vânzarea, suma, vânzătorul și floarea la codul de vânzare specificat.

4. Afișați o listă de culori și grad pentru flori cu o înălțime a numărului specificat sau înflorire.

Opțiunea 4.

1. Pentru a retrage o listă de medicamente cu indicarea specificată pentru utilizare.

2. Pentru a afișa o listă de livrări, care au vândut mai mult decât numărul specificat de același medicament.

3. Pentru a retrage data livrării, suma, titlul capului de la furnizor și numele medicamentelor pe codul de primire mai mult decât numărul specificat.

Opțiunea 5.

2. Afișați o listă de echipamente scrise pentru motivul indicat.

3. să retragă data primirii, numele echipamentului, numele responsabilului și data de depunere a echipamentelor scrise în perioada specificată.

4. Afișați lista de echipamente cu tipul specificat sau cu data de primire mai mult decât o anumită valoare.

Opțiunea 6.

1. Lista de feluri de mâncare cu greutate mai mare decât numărul specificat.

2. să retragă o listă de produse, în titlul căruia se găsește fragmentul specificat al cuvântului.

3. Scoateți volumul produsului, numele vasului, numele produsului cu un cod de mâncare de la valoarea inițială specificată printr-o valoare specifică specifică.

4. Afișați procesul de gătit și numele vasului cu cantitatea de carbohidrați este mai mare decât o anumită valoare sau cantitatea de calorii este mai mare decât valoarea specificată.

Opțiunea 7.

1. Pentru a retrage o listă de angajați cu acest post.

3. să retragă data înregistrării, tipul de document, numele registratorului și numele organizației pentru documentele înregistrate în perioada specificată.

4. Afișează o listă de documente înregistrate cu un anumit tip de document sau data înregistrării este mai mare decât valoarea specificată.

Opțiunea 8.

1. să retragă o listă de angajați cu motivul specificat pentru concediere.

3. să retragă data înregistrării, motivul concedierii, numele angajatului pentru documentele înregistrate în perioada specificată.

Opțiunea 9.

1. să retragă o listă de angajați care au curajos vacanța tipului specificat.

2. Sarma o listă de documente la data înregistrării în perioada specificată.

3. să retragă data înregistrării, tipul de vacanță, FIO al angajatului pentru documentele înregistrate în perioada specificată.

4. Afișați o listă cu documentele înregistrate cu un cod de documente în intervalul specificat.

Opțiunea 10.

1. Pentru a retrage o listă de angajați cu acest post.

2. Pentru a retrage o listă de documente în care se găsește fragmentul specificat al cuvântului.

3. să retragă data înregistrării, tipul de document, FIO al expeditorului și numele organizației pentru documentele înregistrate în perioada specificată.

4. Afișați o listă de documente înregistrate cu tipul de document specificat sau cu codul de document mai mic decât o anumită valoare.

Opțiunea 11.

1. să retragă o listă de angajați desemnați în poziția specificată.

2. Sarma o listă de documente la data înregistrării în perioada specificată.

3. să retragă data de înregistrare, poziția, numele complet al documentelor înregistrate în perioada specificată.

4. Afișați o listă cu documentele înregistrate cu un cod de documente în intervalul specificat.

Opțiunea 12.

3. să retragă o listă de persoane care să închirieze echipamente și numărul de apeluri, sortate după numărul de apeluri descrescătoare.

Opțiunea 13.

1. Afișați lista de echipamente cu tipul specificat. 2. Afișați lista de echipamente care a scris un anumit angajat.

3. Afișați cantitatea de echipamente scrise grupate pe echipament.

4. Afișați informații despre angajați cu data de angajare mai mult decât o anumită dată.

Opțiunea 14.

1. Afișați lista de flori cu tipul de fișă specificat.

2. Sarma o listă de coduri de venit pentru care culorile vândute sunt mai mult decât o anumită valoare.

3. Îndepărtați data primirii, suma, numele furnizorului și culorilor pe un anumit cod al furnizorului.

4. Lista culorilor și a soiurilor pentru flori cu o înălțime mai mare decât un anumit număr sau înflorire.

Opțiunea 15.

1. Pentru a retrage o listă de clienți care au ajuns la numere în perioada specificată.

2. Pentru a retrage o sumă totală de plăți pentru fiecare client.

3. Îndepărtați data sosirii, tipul de cameră, numele clienților înregistrați în perioada specificată.

4. Sarma o listă de clienți înregistrați într-un anumit tip.

Opțiunea 16.

1. Afișați lista de echipamente cu tipul specificat.

2. Postați o listă de echipamente care a luat un anumit client de închiriat.

3. Pentru a retrage o listă de persoane care deține echipamentul de angajare și numărul de apeluri lor sortate după numărul de apeluri descrescătoare.

4. Distribuiți informațiile sortate de adrese.

Opțiunea 17.

1. Pentru a afișa o listă de valori cu costul achizițiilor mai mult decât o anumită valoare sau o perioadă de garanție mai mult decât numărul specificat.

2. Pentru a aduce o listă de locații de valori materiale, în titlul despre care se găsește cuvântul specificat.

3. Scoateți valoarea valorii valorilor cu codul din intervalul specificat.

4. Afișați o listă de persoane responsabile din punct de vedere financiar cu o dată de angajare în intervalul specificat.

Opțiunea 18.

1. LISTA. lucrări de reparațiiefectuată de un maestru specific.

2. Pentru a retrage o listă de etape de lucru incluse în lucrare, în titlul despre care se găsește cuvântul specificat.

3. Pentru a obține cantitatea de cost al pașilor de reparare a lucrărilor de lucru cu codul în intervalul specificat.

4. Sarma o listă de maeștri de la data angajării în intervalul specificat.

Opțiunea 19.

1. Pentru a retrage o listă de medicamente cu o anumită indicație.

2. Sarma o listă de cecuri de cecuri pentru care mai mult decât un anumit număr de medicamente vândute.

3. Pentru a retrage data vânzării, suma, numele complet și tratamentul pentru un control cu \u200b\u200bnumărul specificat.

4. Afișați o listă de medicamente și unități de măsurare pentru medicamente cu un pachet în ambalaje mai mult decât numărul sau codul specificat al medicamentului este mai mic decât o anumită valoare.

Opțiunea 20.

1. Pentru a retrage o listă de angajați cu acest post.

2. Pentru a retrage o listă de documente în care se găsește fragmentul specificat al cuvântului.

3. să retragă data înregistrării, tipul de document, numele complet și de execuția pentru documentele înregistrate în perioada specificată.

4. Afișați o listă de documente înregistrate cu tipul de document specificat sau cu codul documentului într-o anumită interval.

22 răspunsuri

În experiența mea în scris, în principal, Winforms Aplicațiile client / server sunt concluzii simple la care am venit:

Utilizați procedurile stocate:

• Pentru orice lucrare dificilă cu datele. Dacă veți face ceva cu adevărat necesită o masă cursor sau o temperatură, este de obicei cea mai mare drumul rapid Fă-o în SQL Server.
• Dacă aveți nevoie să blocați accesul la date. Dacă nu oferiți acces la tabel utilizatorilor (sau rolul sau altceva), puteți fi siguri că singura cale Interacțiunea cu datele - prin intermediul joint-venturei pe care îl creați.

Utilizați solicitări speciale:

• Pentru crud când nu aveți nevoie să restricționați accesul la date (sau să faceți altfel).
• Pentru căutare simplă. Crearea SP pentru o varietate de criterii de căutare este durerea și întreținerea dificilă. Dacă puteți crea un mod destul de rapid search Query.Foloseste-l.

În majoritatea aplicațiilor mele, am folosit atât SP, cât și ad-hoc sql, deși cred că folosesc spitalul mai puțin și mai puțin deoarece sunt în cele din urmă codul ca C #, este doar mai dificil de controlat, testat și întreținere. Aș recomanda utilizarea ad-hoc sql dacă nu găsiți un motiv specific.

Procedurile stocate sunt un contract pentru software.care încapsulează acțiunile întreprinse împotriva bazei de date. Codul în proceduri și chiar și diagrama bazei de date în sine poate fi modificată fără a afecta codul detaliat, astfel încât intrările și ieșirile procedurii rămân neschimbate.

Încorporarea solicitărilor în aplicația dvs., vă conectați îndeaproape cu modelul dvs. de date.

Din același motiv, nu este, de asemenea, o bună practică de a crea pur și simplu proceduri stocate care sunt cereri de crud pentru fiecare tabel din baza dvs. de date, deoarece este încă strâns legată. În schimb, procedurile ar trebui să fie greoaie, granulate grosiere.

Cred că acesta este principalul conflict între persoanele care trebuie să susțină baza de date și persoanele care dezvoltă interfețe de utilizator.

Ca persoană cu date, nu aș lua în considerare lucrul cu baza de date la care sunt abordate prin cereri ADHOC, deoarece acestea sunt dificil de configurat sau de a le gestiona. Cum pot afla ce va afecta schimbarea în schemă? În plus, nu cred că utilizatorii ar trebui să furnizeze acces direct la tabelele de bază de date din motive de securitate (și vreau să spun că nu numai atacuri de injecție SQL, ci și pentru că este un control intern de bază care nu permite drepturi directe și impune tuturor utilizatorilor. Utilizați numai procsurile destinate aplicației pentru a preveni o posibilă fraudă. Orice sistem financiar care vă permite să introduceți direct, să actualizați sau să ștergeți drepturile la tabele, are un risc imens de fraudă. Este rău.).

Bazele de date nu sunt orientate pe obiecte, iar codul care pare bun din punct de vedere orientat pe obiecte poate fi extrem de sărac din punctul de vedere al bazei de date.

Dezvoltatorii noștri ne informează că sunt mulțumiți de faptul că toate accesul nostru la baze de date sunt efectuate prin procs, deoarece accelerează semnificativ eroarea dependentă de erori și apoi lansează PROC în mediul de lucru și nu creează o nouă ramură de codul și recompilarea și repornirea producției. Cerem ca toate procesele noastre să fie în activități subversive, astfel încât controlul sursei nu este deloc o problemă. Dacă nu este în subversiune, va elimina periodic DBAS, deci nu există nici o rezistență la utilizarea controlului sursei.

Procedurile stocate sunt cu siguranță potrivite ... acestea sunt compilate, au un plan de împlinire înainte de a începe munca și puteți să le gestionați drepturile.

Nu înțeleg această problemă cu codul sursă al procedurii stocate. Le puteți controla cu siguranță, dacă sunteți doar puțin disciplinați.

Începeți întotdeauna cu un fișier.sql, care este sursa procedurii stocate. Puneți-l în controlul versiunii după ce ați scris codul. Data viitoare când doriți să editați procedura stocată, veți ieși din dvs. elementul sursă Control decât baza de date. Dacă urmați acest lucru, veți avea aceeași bună sursă de management, cum ar fi codul dvs.

Aș dori să citez lui Tom Kyte de la Oracle aici ... Aici este regula ei despre unde să scrie codul ... Deși puțin fără legătură, dar știu bine, cred.

Aplicația noastră are un strat de cod care oferă conținutul interogării (și, uneori, provocarea procedurii stocate). Acest lucru ne permite să:

• ușor de primit toate solicitările atunci când gestionați versiunile
• pentru a face toate modificările pentru fiecare interogare pentru diferite servere de baze de date
• elimină repetarea aceluiași cod de interogare prin codul nostru

Controlul accesului este implementat pe stratul de mijloc și nu în baza de date, deci nu avem nevoie de proceduri stocate. Acesta este un fel de drum mediu dintre cererile speciale și procedurile stocate.

Există argumente convingătoare pentru ambele proceduri stocate care se află în depozitul central, dar (potențial) este dificil de amânat, iar cererile speciale sunt mai ușor de depanat, deoarece acestea sunt cu codul tău, dar pot fi, de asemenea, mai greu de găsit în cod .

Argumentul că procedurile stocate sunt mai eficiente, nu mai conține apă. Link text.

Execuția Google pentru procedura stocată vs interogarea dinamică va afișa oricum argumente decente și probabil mai bine pentru a vă lua propria soluție ...

Unele lucruri trebuie să se gândească: cine are nevoie de proceduri stocate, oricum?

Este clar că aceasta este o chestiune a nevoilor și preferințelor dvs., dar este foarte important să ne gândim la acest lucru atunci când utilizați cereri speciale într-un mediu orientat public, există siguranță. Faceți întotdeauna parametrizarea și urmați vulnerabilități tipice, cum ar fi injecții SQL.

Procedurile salvate sunt excelente deoarece pot fi modificate fără recompilare. Voi încerca să le folosesc cât mai des posibil.

Folosesc ad-hoc pentru interogări care sunt generate dinamic bazate pe intrarea utilizatorului.

Procs din motivele menționate de alții, precum și mai ușor de configurat PROC utilizând un profiler sau PROC părți. Deci, nu este nevoie să spuneți unei persoane despre rularea aplicației dvs. pentru a afla ce este trimis la serverul SQL

Dacă utilizați cereri ad-hoc, asigurați-vă că sunt parametrizate

Paramed SQL sau SPROC ... nu contează din punct de vedere al performanței ... Puteți solicita optimizarea uneia dintre ele.

Pentru mine, ultimul avantaj rămas al SPROC este că pot exclude o mulțime de drepturi de a gestiona drepturile SQL, oferind doar drepturile dvs. de autentificare pentru a rula sproci ... Dacă utilizați SQL parametizat, autentificarea asociată conexiunii dvs. Șirul are mult mai multe drepturi (înregistrarea oricărui tip de operator de selecție pe unul dintre tabelele la care au acces, de exemplu).

Încă mai prefer un SQL parametrizat, deși ...

Argumentul performanței SPRIC este controversat - 3 top RDBMS utilizează cache-ul de interogare și pentru o vreme. El a fost documentat ... sau altul 1995?

Cu toate acestea, încorporarea SQL în aplicația dvs. este, de asemenea, un design teribil - serviciul de cod pare a fi conceptul lipsă pentru mulți.

Dacă aplicația poate porni de la zero folosind orm (aplicațiile cu câmp verde sunt departe de mai multe!), Este o alegere excelentă deoarece modelul dvs. de clasă gestionează modelul dvs. de bază de date și economisește timp.

Dacă structura ORM nu este disponibilă, am folosit o abordare hibridă a creării Fișier XML. SQL Resurse pentru a căuta rânduri SQL după cum este necesar (acestea sunt apoi infrastructuri de resurse cache). Dacă SQL are nevoie de manipulări minore efectuate în cod, dacă este necesară o manipulare mare a șirului SQL, regâncăm această abordare.

Această abordare hibridă facilitează gestionarea dezvoltatorilor (poate că suntem o minoritate, deoarece echipa mea este destul de strălucitoare pentru a citi planul de interogare), iar desfășurarea este un control simplu de la SVN. În plus, simplifică comutarea RDBM - doar înlocuiți fișierul de resurse SQL (nu la fel de simplu ca instrumentul ORM, desigur, dar funcționează cu sisteme învechite sau nu sunt acceptate de baza de date)

Experiența mea este că 90% din cererile și / sau procedurile stocate nu ar trebui înregistrate deloc (cel puțin manual).

Accesul la date trebuie să fie generat într-un fel automat. Puteți decide dacă doriți să generați în mod static în timpul compilării sau dinamic în timpul executării, dar dacă doriți să adăugați o coloană la masă (obiect de proprietate), trebuie să modificați numai un singur fișier.

Prefer să stochez toate datele acces Într-un cod de program, în care nivelul de acces la date efectuează interogări directe SQL. Pe de altă parte, logica Controlpe care am plasat-o în baza de date sub formă de declanșatoare, proceduri stocate, funcții ale utilizatorilor și altceva. Un exemplu de ceea ce consider că o bază de date decentă este de a genera date - să presupunem că clientul nostru are numele de nume și nume de familie. Acum interfața cu utilizatorul necesită afișare, care este derivată dintr-o logică non-trivială. Pentru această generație, creez o procedură stocată, care este apoi pornită de un declanșator ori de câte ori este actualizată șirul (sau alte date sursă).

Se pare că aceasta este o neînțelegere ușor răspândită pe care nivelul de acces la date este o bază de date și tot ceea ce privește accesul la date și datele apar acolo. Este doar incorect, dar văd multe proiecte care curg de la această idee. Poate că acesta este un fenomonon local.

În articolul precedent din acest ciclu, am analizat modul în care datele pot fi recuperate din tabele, modifică structura, crearea, modificarea și ștergerea bazelor de date și obiecte care conțin. În acest articol vom vorbi mai detaliat despre obiectele caracteristice serverului DBMS: idei, declanșatoare și proceduri stocate.

În primul articol al acestui ciclu, publicat în nr. 3'2000 din revista noastră, am observat că majoritatea trimiterilor de asistență DBMS moderne, declanșatoare și proceduri stocate. Reprezentările sunt, de asemenea, susținute de mai multe DBM-uri desktop, cum ar fi accesul, dbaza, Clipper.

Trebuie remarcat faptul că declanșatoarele și procedurile stocate sunt scrise în mod obișnuit în limbi de programare, care sunt extensii procedurale ale limbajului SQL. Aceste extensii conțin operatori pentru a descrie algoritmii, de exemplu, fac ... în timp ce ... atunci ... altcineva, lipsesc în limba SQL însuși (dacă vă amintiți, SQL este un limbaj nepromisiv și puteți formula o sarcină , dar este imposibil să descriem algoritmii de execuție). Spre deosebire de limba SQL, subordonată standardului, extensiile sale procedurale nu sunt standardizate, iar DBM-urile diferite utilizează diferite structuri sintactice pentru a implementa aceleași modele algoritmice, dar discuția despre diferențele în sintaxa extensiei SQL pentru DBMSS diferită este dincolo de domeniul de aplicare al acestui articol .

Pentru a ilustra modul în care puteți utiliza vizualizări, declanșatoare și proceduri stocate, am ales Microsoft SQL Server 7.0 și baza de date de NordWind, care este inclusă în livrarea acestui DBMS.

Înainte de a efectua exemple, acordați atenție faptului că implementarea și metoda de depozitare a declanșatorilor și a procedurilor stocate în DBMS pe care le utilizați pot diferi de cele prezentate în acest articol. În plus, crearea de obiecte de server ar trebui să aibă permisiuni adecvate furnizate de administratorul bazei de date.

Rețineți, de asemenea, că unii drivere ODBC nu acceptă apelul de proceduri stocate din aplicațiile client, chiar dacă acestea sunt susținute de DBMS în sine. Cu toate acestea, în acest caz, procedurile stocate pot fi cauzate de declanșatoare.

Să începem cu ideile, apoi să discutăm procedurile stocate și să terminăm capitolul de prezentare a declanșatorului.

Reprezentare

Prezentarea este o masă virtuală care conține, de obicei, un set de coloane de unul sau mai multe tabele. De fapt, vizualizarea nu conține date, ci doar un tip de interogare SQL Selectați indicând datele și din care trebuie luate tabele la accesarea acestei reprezentări. Din acest punct de vedere, prezentarea este o cerere de stocare.

În majoritatea cazurilor, depunerea este utilizată pentru a asigura securitatea datelor. De exemplu, unele categorii de utilizatori pot avea acces la prezentare, dar nu la tabelele ale căror date sunt formate; În plus, interogarea SQL poate conține parametrul utilizatorului (numele în care înregistrarea utilizatorului) și, în acest caz, datele disponibile atunci când se referă la vizualizare vor depinde de numele de utilizator.

Mai jos sunt principalele caracteristici ale reprezentanțelor:

• prezentările se comportă ca mese;
• viziunile nu conțin date;
• prezentările pot utiliza date de la mai mult de un tabel.

Pentru a crea o vizualizare, putem folosi propunerea Create View SQL, pentru modificarea acestuia - propunerea Alter View și pentru al elimina - oferta de vizualizare.

Vom începe cu operatorul de creație pentru a crea o vizualizare pentru baza de date curentă.

Creați oferta de vizualizare

Propunerea de sintaxă pentru a crea o vizualizare seamănă cu propunerea SQL Selectați cu mai multe cuvinte cheie suplimentare. Mai jos este sintaxa sa simplificată:

Creați Vizualizare Vizualizare_name ca select_statement

Argumentul View_name indică numele prezentării. Cuvântul cheie utilizat în Microsoft SQL Server vă permite să ascundeți textul sursă de afișare Creare în tabelul Syscomments.

Cuvântul cheie indică faptul că interogarea selectată va fi efectuată de fapt atunci când contactați vizualizarea. Rețineți că această interogare nu poate conține comanda cu cuvintele cheie, calculați sau calculați de, în și nu se poate referi la o masă temporară.

Pentru a modifica vizualizarea creată anterior, ar trebui să utilizați propunerea Alter View, care descrie pe scurt în următoarea secțiune.

OFERTA PRODUCERE VIZUALĂ.

Această ofertă este utilizată pentru a șterge vizualizările din baza de date. Rețineți că atunci când ștergeți o masă din baza de date, toate vederile care se leagă de acestea sunt eliminate. Folosind această ofertă, trebuie să specificăm numele vizualizării la distanță. După ștergerea vizualizării, toate informațiile despre acesta sunt eliminate din tabelele de sistem.

Un alt caz în care vederea trebuie îndepărtată, poate apărea, cu condiția ca structura tabelelor pe care să se bazeze, sa schimbat după crearea unei vizualizări. În acest caz, puteți șterge vizualizarea și apoi o creați din nou utilizând oferta Creare View.

Crearea și utilizarea ideilor

Crearea ofertei de vizualizare este utilizată pentru a crea reprezentări care vă permit să extrageți date care îndeplinesc anumite cerințe. Prezentarea este creată în baza de date curentă și stocată ca obiect separat.

Cea mai bună modalitate de a crea o vizualizare este de a crea o interogare selectată și, verificarea acesteia, adăugați partea lipsă a propunerii Create View. Să ne uităm la codul sursă pentru prezentarea produselor pe categorii în baza de date Northwind (listare 1).

Primul șir evidențiat cu caractere aldine este ceva pe care propunerea SQL este diferită de a crea o viziune din solicitarea de selecție obișnuită care efectuează datele de selectare a datelor. Propunerea selectată conținută în această vizualizare selectează câmpurile a două tabele - câmpul CategorieName din tabelul Categorii și câmpul NAMEDNAME, COMANDERURINȚĂ, INSTRUMENTEINTOCK, întreruptă din tabelul de produse. După aceasta, datele celor două tabele sunt asociate prin câmpul CateriDID și numai acele produse care există încă în depozit (a se vedea criteriul după cuvântul cheie unde) sunt incluse în setul de date rezultat. Rezultatul apelului la această reprezentare este prezentat în fig. unu .

Acum, să creăm o viziune care să arate toate teritoriile regiunii estice. Această vizualizare se bazează pe următoarea interogare (listarea 2).

Asigurați-vă că propunerea selectată returnează rezultatele de care avem nevoie, adăugăm instrucțiunea Creare View și atribuie numele Eastterr creat de prezentare.

În loc să creați un text de prezentare manuală, puteți utiliza instrumente vizuale care sunt de obicei incluse în DBMS. În fig. 2 arată cum poate fi creată aceeași viziune utilizând instrumentul de designer View, care este o parte integrantă a managerului de întreprinderi inclusă în Microsoft SQL Server.

Partea superioară a designerului de vizualizare vă permite să specificați modul în care sunt asociate tabelele și ce câmpuri vor fi afișate în vizualizare. Mai jos puteți specifica aliasurile de tabele și câmpuri, restricții asupra valorilor acestora, metoda de afișare. Următoarele sunt textul sursă al prezentării și rezultatele execuției sale.

Înainte de a termina revizuire scurtă Reprezentări, să vorbim puțin despre cum să obținem informații suplimentare despre ele. În Microsoft SQL Server 7.0, putem folosi următoarele proceduri stocate de sistem:

• pentru a obține informații despre vizualizare, puteți utiliza procedura stocată de sistemul SP_HEP. De exemplu, Sp_Help Eastterr va returna informații despre noul ansamblu creat;
• pentru a obține textul sursă al vizualizării, puteți utiliza procedura stocată sp_HelpText;
• pentru a găsi o listă de tabele din care depinde punctul de vedere, puteți utiliza procedura stocată de sistemul SP_DEPENS;
• puteți utiliza procedura stocată de sistemul SP_RENAME pentru redenumirea vizualizării.

În această secțiune, am revizuit modul de utilizare a depunerilor pentru a obține date care îndeplinesc unul sau altul criterii. Cu toate acestea, înapoi la ultimul exemplu. În baza de date nord-care există patru regiuni, iar pentru lista teritoriilor din toate regiunile, avem nevoie de patru opinii diferite. Această sarcină ar putea fi simplificată dacă am putea trece valoarea regiunii ca parametru. Acest lucru se poate face folosind procedura stocată, despre care vom vorbi în secțiunea următoare.

Proceduri stocate

Procedura stocată este un set de propunere compilat, salvat în baza de date ca obiect numit și executat ca un singur fragment de cod. Procedurile stocate pot primi și returna parametrii. Când utilizatorul creează o procedură stocată, serverul îl compilează și îl plasează în cache-ul partajat, după care codul compilat poate fi aplicat de mai mulți utilizatori. Când aplicația utilizează procedura stocată, transmite parametrii dacă este necesar, iar serverul efectuează procedura fără a recompila.

Procedurile stocate vă permit să îmbunătățiți performanța aplicațiilor. În primul rând, în comparație cu cererile convenționale SQL trimise de la aplicația client, acestea necesită mai puțin timp pentru a se pregăti pentru execuție, deoarece acestea sunt deja compilate și salvate. În al doilea rând, traficul de rețea în acest caz este, de asemenea, mai mic decât dacă este transmisă interogarea SQL, deoarece mai puține date sunt transmise în rețea. Smochin. 3 ilustrează apelul la procedura stocată de către aplicația client.

Procedurile stocate sunt recompuse automat dacă cu obiecte la care afectează, se fac modificări; Cu alte cuvinte, ele sunt întotdeauna relevante. După cum sa menționat mai sus, procedurile stocate pot lua parametri, ceea ce permite aplicații multiple Utilizați aceeași procedură utilizând diferite seturi de intrare.

Procedurile stocate sunt utilizate în mod obișnuit pentru a sprijini integritatea de referință a datelor și punerea în aplicare a normelor de afaceri. În acest din urmă caz, se realizează o flexibilitate suplimentară, deoarece dacă regulile de afaceri se schimbă, puteți modifica textul procedurii, fără a schimba aplicațiile clientului.

Pentru a crea, modifica și elimina procedurile, există o ofertă specială SQL - Creare procedură, modifică procedura și procedura de scădere. Ne vom uita la ele în secțiunea următoare.

Creați oferta de procedură

Crearea ofertei de procedură este utilizată pentru a crea o procedură stocată. Are următoarea sintaxă simplificată:

Creați Proc Proc_Name [(@Parameter Data_type) [\u003d Implicit]] [...] ca sql_statements

Argumentul Proc_name stabilește numele procedurii stocate, care trebuie să fie unic în baza de date curentă. Argumentul @Parameter determină parametrul de procedură. În clauza de procedură Creare, puteți defini unul sau mai mulți parametri. Dacă nu există o valoare implicită pentru parametru, acesta trebuie transferat de către utilizator (sau aplicația client) la apelarea procedurii. În Microsoft SQL Server 7.0, numărul parametrilor procedurii stocate nu trebuie să depășească 1024; În mod implicit, acestea pot avea valori nulă.

Rețineți, totuși, că unele mecanisme universale de acces la date pot impune restricții suplimentare asupra numărului de parametri ai procedurilor stocate. De exemplu, driverul BDE pentru Oracle 8 este capabil să funcționeze numai cu proceduri, numărul de parametri care nu depășesc 10.

Argumentul Data_type indică tipul de date pentru parametru. Cuvântul cheie implicit poate fi utilizat pentru a seta valorile implicite - poate fi o constantă sau nulă. Dacă valoarea implicită este specificată, procedura poate fi apelată fără a specifica valoarea parametrului. Dacă procedura utilizează un parametru cu un cuvânt cheie similar, valoarea sa implicită poate conține caractere de grup (%, _ și [^]).

Cuvântul cheie de ieșire arată că acesta este parametrul returnat.

Cuvântul cheie indică acțiunea pe care procedura trebuie să o realizeze sub forma oricărui număr de propuneri și sugestii SQL privind caracteristica extensiei procedurale SQL a acestui server.

Procedura creată de oferta de procedură Creare va fi salvată în baza de date curentă. În Microsoft SQL Server, numele procedurilor sunt conținute în tabelul SysObjects System, iar textul sursă este în tabelul de syscomments.

Pentru a modifica procedura stocată anterior creată, ar trebui să utilizați propunerea de procedură Alter, care descrie pe scurt în secțiunea următoare.

Oferă procedură de scădere

Această ofertă este utilizată pentru a elimina procedurile stocate din baza de date. Oferta de procedură de scădere primește un argument - numele procedurii la distanță.

Când ștergeți procedura stocată, acesta este șters de la tabelele SysObjects și Syscomments System.

Crearea și utilizarea procedurilor stocate

În secțiunea privind ideile, am acordat atenție la ceea ce ar fi convenabil dacă am putea trece parametrul pentru a reprezenta parametrul care conține valoarea regiunii pentru a selecta una dintre cele patru regiuni din baza de date nord-care. Să luăm în considerare cererea care returnează lista teritoriilor din regiune:

Selectați Teritoriile.terioryDecription, Regiune.RegionDecription de la teritorii Inner Alăturați regiunea pe teritorii.regionaliD \u003d regiune.reonid unde teritorii

Pentru a selecta o altă regiune, trebuie să schimbăm starea în clauza unde în ultimul rând al cererii. Prin urmare, dacă folosim o variabilă (apelați-o regiz), putem alege una dintre cele patru regiuni fără a schimba alte părți ale interogării.

În baza de date de NordWind, patru regiuni cu numere de la 1 la 4. Aceasta înseamnă că variabila REGID trebuie să fie un tip întreg. Codul procedurii stocate este mai jos:

Creare Procedură Showregiune @regid Int ca selecteriile.terioryDescrieri, regiune.RegionalDeriftion din teritorii Inner Alăturați regiunea pe teritorii.regionale \u003d regiune.renique unde teritorii.regionale \u003d @regid

Rețineți că am lăsat aproape toate textele de interogare Select Neverdent (ISTIC) și a adăugat doar propunerea de procedură de creație pentru numele procedurii stocate nou create (în prima linie), declarația parametrilor (în a doua linie) și cuvânt cheie. Așa cum indică începutul propunerilor care efectuează acțiuni.

Rezultatul executării procedurii create în Analizor de interogare SQL Server pentru REGID \u003d 2 este prezentat în fig. 3.

Evident, putem aplica proceduri stocate nu numai pentru a implementa versiuni avansate de observații sau solicitări selectate "inteligente". Procedurile stocate oferă mecanisme pentru automatizarea multor sarcini de rutină.

În Microsoft SQL Server 7.0, putem utiliza, de asemenea, proceduri stocate sistemice pentru a lucra cu procedurile convenționale stocate:

• sp_stored_proceduri - prezintă o listă de proceduri stocate;
• sp_HelpText - arată textul sursă al procedurii stocate;
• sp_depens - prezintă informații despre dependența procedurilor stocate;
• sp_procoption - stabilește opțiunile procedurilor stocate sau le stabilește;
• sp_recompile - recompilați procedura în momentul următorului său apel;
• sp_rename - modifică numele procedurii.

Proceduri stocate de sistem

Din moment ce vorbim despre Microsoft SQL Server, trebuie remarcat un număr mare de proceduri stocate sistemice implementate în acesta. Numele procedurilor stocate de sistem încep cu SP_ sau XP_ și stocate în baza de date Master. Mai sus, am descris deja unele dintre procedurile stocate frecvent utilizate.

Vă rugăm să rețineți că declanșatoarele nu ar trebui să revină la datele utilizatorului.

În clauza de declanșare Creare puteți folosi două mese speciale. De exemplu, tabelele șterse și inserate au aceeași structură ca și tabelul pentru care este definit declanșatorul și conține valorile vechi și noi ale înregistrărilor modificate de utilizator. De exemplu, putem folosi următoarea propunere SQL pentru a căuta înregistrări la distanță:

Selectați * De la ștergerea

În fila. 3 prezintă conținutul tabelelor șterse și inserate pentru toate modificările posibile de date.

Pentru a schimba declanșatorul existent, ar trebui să utilizați propunerea Alter Trigger. Vom vorbi despre asta în secțiunea următoare.

În primul rând, trebuie să adăugăm două noi câmpuri la masă în care aceste informații vor fi conținute. Să le numim actualizat (numele managerului care a actualizat ultima înregistrare) și actualizat (timpul în care înregistrarea a fost modificată). Apoi creați un declanșator numit KeekTrack. Iată codul său:

Creați Trigger KeekTrack pe clienți pentru Insert, Actualizare ca actualizare Clienți setați Clienți.Updatedby \u003d user_name (), clienți.updatedwhen \u003d getdate () de la inserat, clienți unde este introdus.Customererid \u003d Clienți.Customererid

După cum se poate observa din textul sursă al declanșatorului, se efectuează după fiecare operație de inserare și actualizare din tabelul clienților. Acest declanșator va salva numele managerului (utilizatorul bazei de date) în câmpul Clienți.Updatedby și data și ora schimbării - în câmpul Clienți.Updatedwhen. Aceste date sunt extrase din tabelul de timp introdus.

După cum puteți vedea, acest declanșator vă permite să monitorizați modificările și introducerea de noi intrări în tabel.

Înainte de a finaliza o scurtă trecere în revistă a declanșatorilor, trebuie să raportați unde puteți găsi informații despre declanșatoarele disponibile. Tabelul SysObjects stochează informații despre declanșatoare și tipurile lor, iar tabelul Syscomments conține textul sursă.

Concluzie

În această parte, am analizat mai multe tipuri de obiecte de bază de date - proceduri stocate, vizualizări și declanșatori. Am învățat următoarele:

• Reprezentarea este o masă virtuală, de obicei creată ca un subset de coloane de unul sau mai multe mese. Pentru a crea o vizualizare, se aplică propunerea Create View, pentru modificare - propunerea Alter View și pentru a șterge oferta de vizualizare.
• Procedura stocată este un set de propunere compilat, salvat în baza de date ca obiect numit și executat ca un singur fragment de cod. Pentru a crea o procedură stocată, oferta de procedură Creare se aplică procedurii de modificare și pentru eliminarea procedurii.
• Triggerul este un tip special de procedură stocată, care se numește automat când datele dintr-un tabel specific sunt adăugate, șterse sau modificări utilizând Inserare, Ștergere sau Actualizare oferte SQL. Declanșatoarele sunt create folosind oferta Creare declanșator. Pentru a schimba declanșatorul, se utilizează propunerea Alter declanșator, iar propunerea de declanșare a scăderii este utilizată.

Computer Press 12 "2000

Procedură stocată - Acesta este un tip special de pachet de instrucțiuni Transact-SQL, creat folosind limba SQL. și extensiile procedurale. Principala diferență dintre pachet și procedura stocată este că acesta din urmă este salvat ca obiect de bază de date. Cu alte cuvinte, procedurile stocate sunt salvate pe partea serverului pentru a îmbunătăți performanța și constanța executării sarcinilor repetate.

Componenta motorului bazei de date suportă procedurile stocate și procedurile de sistem. Procedurile stocate sunt create în același mod ca toate celelalte obiecte de bază de date, adică. Folosind limba DDL. Proceduri de sistem O componentă este furnizată de componenta motorului bazei de date și poate fi utilizată pentru a accesa informațiile din directorul de sistem și a modificărilor acestuia.

Când creați o procedură stocată, puteți defini o listă opțională de parametri. Astfel, procedura va lua argumentele corespunzătoare de fiecare dată la apelul său. Procedurile stocate pot returna o valoare care conține un anumit utilizator Informații sau, în cazul unei erori, mesajul de eroare corespunzător.

Procedura stocată este pre-compilată înainte de a fi salvată ca obiect din baza de date. Forma de procedură pre-compilată este stocată în baza de date și este utilizată pentru fiecare apel. Această proprietate a procedurilor stocate oferă un beneficiu important format în eliminarea (aproape în toate cazurile) de compilare repetată a procedurii și obținerea îmbunătățirii relevante a productivității. Această proprietate a procedurilor stocate are, de asemenea, un efect pozitiv asupra valorii datelor implicate în schimbul dintre sistemul de bază de date și aplicații. În special, pentru a apela procedura stocată, mai multe mii de octeți pot necesita mai puțin de 50 octeți. Atunci când mai mulți utilizatori efectuează sarcini repetate care utilizează proceduri stocate, efectul acumulativ al unor astfel de economii poate fi destul de semnificativ.

Procedurile stocate pot fi utilizate și în următoarele scopuri:

pentru a crea jurnale de jurnal despre acțiunile cu tabelele bazei de date.

Utilizarea procedurilor stocate oferă capacitatea de a gestiona securitatea la un nivel, depășind semnificativ nivelul de securitate furnizat prin utilizarea instrucțiunilor de finanțare și revocare, cu care utilizatorii beneficiază de diferite privilegii de acces. Acest lucru este posibil datorită faptului că autorizația privind executarea procedurii stocate nu depinde de autorizația privind modificarea obiectelor conținute în această procedură stocată, așa cum este descris în următoarea secțiune.

Procedurile stocate care creează înregistrări și / sau jurnale de citire sunt furnizate. o oportunitate suplimentară Asigurarea securității bazelor de date. Folosind proceduri, administratorul bazei de date poate urmări modificările aduse bazei de date de către utilizatori sau programe de aplicații.

Crearea și executarea procedurilor stocate

Procedurile stocate sunt create de instrucțiuni. Creați procedură.care are următoarea sintaxă:

Creați Proc Proc_Name [((@ param1) Type1 [Varing] [\u003d implicit1])] (, ...) ca lot | Numele extern Metodal_Name Sintax Acord

Parametrul Schema_name definește numele schemei atribuite de proprietarul procedurii stocate create. Parametrul Proc_Name definește numele procedurii stocate. Parametrul @ param1 este un parametru al procedurii (argument formal), al cărui tip de date este determinat de parametrul Type1. Parametrii procedurii sunt locali în cadrul procedurii, la fel cum variabilele locale sunt locale în cadrul pachetului. Procedura Parametrii sunt valori care sunt transmise prin procedura de obiect apelare pentru utilizare în acesta. Parametrul implicit1 determină valoarea implicită pentru parametrul corespunzător al procedurii. (Valoarea implicită poate fi, de asemenea, nulă.)

Opțiunea de ieșire. Indică faptul că parametrul de procedură este returnat și este posibil să returnați valoarea din procedura stocată care necesită procedura sau sistemul.

Așa cum am menționat mai devreme, forma de procedură pre-compilată este stocată în baza de date și este utilizată în fiecare apel. Dacă dintr-un anumit motiv procedura stocată este necesară pentru a compila de fiecare dată când se numește, atunci când se utilizează procedura cu opțiunea de recompilare. Utilizarea opțiunii cu recompilul reduce unul dintre cele mai importante avantaje ale procedurilor stocate: îmbunătățirea performanței datorită unei compilații. Prin urmare, opțiunea cu recompilare trebuie utilizată numai cu modificări frecvente ale obiectelor de bază de date utilizate de procedura stocată.

Executați ca ofertă Determină contextul de securitate în care procedura stocată trebuie executată după ce se numește. Prin stabilirea acestui context, utilizând motorul bazei de date puteți gestiona alegerea conturilor de utilizator pentru verificarea accesului la obiecte menționate de această procedură stocată.

Utilizarea implicită a declarației de procedură Creare poate fi membri ai serverului Sysadmin predefinit și rolul predefinit DB_OWNER sau DB_DDDDADMINM. Dar membrii acestor roluri pot atribui acest drept altor utilizatori utilizând instrucțiunile. Grant Creation Procedură.

Exemplul de mai jos prezintă crearea unei proceduri simple stocate pentru a lucra cu tabelul proiectului:

Utilizați Sampledb; Du-te Creare Procedură de creștereBudget (@percent Int \u003d 5) ca actualizare set de proiecte Buget \u003d buget + buget * @ procent / 100;

Așa cum am menționat mai devreme, se utilizează separarea a două pachete instrucțiunea merge.. Instrucțiunea Creare procedură nu poate fi combinată cu alte instrucțiuni tranzacționate-SQL într-un singur pachet. Procedura de crescătorieBudget crește bugetele pentru toate proiectele la un anumit număr de dobânzi, determinat de parametrul @Pencent. Procedura definește, de asemenea, valoarea numărului procentual implicit (5), care este utilizat dacă acest argument lipsește în timpul procedurii.

Procedurile stocate se pot referi la tabelele inexistente. Această proprietate vă permite să executați depanarea codului de procedură, fără a crea mai întâi tabelele corespunzătoare și fără conectarea la serverul final.

Spre deosebire de procedurile stocate principale, care sunt întotdeauna salvate în baza de date curentă, este posibil să se creeze proceduri stocate temporare care sunt întotdeauna plasate în baza de date temporală a sistemului tempdb. Una dintre ocazii de a crea proceduri stocate temporare poate fi dorința de a evita executarea recurentă a unui anumit grup de instrucțiuni atunci când este conectat la baza de date. Puteți crea proceduri temporare locale sau globale. Pentru a face acest lucru, numele procedurii locale este setat cu un singur simbol # (#proc_name), iar numele procedurii globale este dublu (# Proc_Name).

O procedură stocată temporară locală poate fi executată numai de utilizator și numai în timpul unei conexiuni la baza de date în care a fost creată. Toți utilizatorii pot efectua o procedură temporară globală, dar numai până la finalizarea ultimei conexiuni în care este executată (de obicei, acesta este un compus al creatorului procedurii).

Ciclul de viață al procedurii stocate constă din două etape: crearea și executarea acesteia. Fiecare procedură este creată o singură dată și se efectuează de mai multe ori. Procedura stocată este efectuată de executați instrucțiunile Utilizatorul care este proprietarul procedurii sau are dreptul de a accesa această procedură. Executarea instrucțiunii are următoarea sintaxă:

[] [@return_status \u003d] (proc_name | @proc_name_var) ([[@ parameter1 \u003d] valoare | [@ parametru1 \u003d] @variable] | implicit) .. Acorduri de sintaxă

Cu excepția parametrului RETURN_STATUS, toate parametrii de instrucțiuni Execute au aceeași valoare logică ca parametrii de instrucțiuni de procedură Creare cu același nume. Parametrul RETURN_STATUS definește o variabilă întregi în care starea de returnare a procedurii este salvată. Valoarea parametrului poate fi atribuită folosind sau o constantă (valoare) sau o variabilă locală (@variable). Ordinea valorilor parametrilor numiți nu este importantă, dar valorile parametrilor anonim trebuie să fie furnizați în ordinea în care acestea sunt definite în instrucțiunile de procedură Creare.

Mod implicit Oferă valori implicite pentru parametrul de procedură, care a fost indicat în definiția procedurii. Când procedura așteaptă o valoare pentru un parametru pentru care a fost definită valoarea implicită și parametrul lipsește sau este indicat cuvântul cheie implicit, atunci apare o eroare.

Când instrucțiunea Execute este prima instrucțiune a pachetului, cuvântul cheie Execute poate fi omis. Cu toate acestea, va fi mai fiabilă pentru a include acest cuvânt în fiecare pachet. Utilizarea instrucțiunii Execute este afișată în exemplul de mai jos:

Utilizați Sampledb; Executați-vă CreștereBudget 10;

Instruirea execută în acest exemplu efectuează procedura stocată de creștereBudget, care crește bugetul tuturor proiectelor cu 10%.

Exemplul de mai jos prezintă crearea unei proceduri stocate pentru prelucrarea datelor în tabelele angajaților și lucrărilor:

Procedura modificată în exemplul ilustrează utilizarea procedurilor stocate ca parte a procesului de integritate de referință (în acest caz între mesele angajatului și de lucru). O procedură stocată similară poate fi utilizată în definiția unui declanșator, care oferă de fapt integritate de referință.

Exemplul de mai jos prezintă utilizarea propunerii de ieșire în procedura stocată:

Această procedură stocată poate fi rulată prin următoarele instrucțiuni:

Declară @quantitydeleeemployee Int; Executați Delegeejloyee @ EMPID \u003d 18316, @ [E-mail protejat] Ieșire; Imprimare n "Angajați eliminați:" + Convert (NVARCHAR (30), @quantitydeleeemployee);

Această procedură calculează numărul de proiecte pe care angajatul este ocupat cu numărul tabelului @Empid și atribuie valoarea primită a parametrului. © Counter. După ștergerea tuturor rândurilor pentru acest număr de tabletă din mesele angajatului și de lucru_on, valoarea calculată este atribuită variabila @quantitydeleejloyee.

Valoarea parametrului este returnată de procedura de apelare numai dacă este specificată opțiunea de ieșire. În exemplul de mai sus, procedura DELETejloyeee transmite procedura de apel parametrului @Counter, prin urmare, procedura stocată returnează valoarea sistemului. Prin urmare, parametrul @Counter trebuie specificat atât în \u200b\u200bopțiunea de ieșire atunci când se declară procedura și în instrucțiunile executate atunci când se numește.

Oferta cu seturi de rezultate Executați instrucțiuni

În SQL Server 2012, este introdusă instrucțiunea Execute oferta cu seturi de rezultatePrin care atunci când efectuați anumite condiții, puteți schimba formularul setului rezultat al procedurii stocate.

Următoarele două exemple vor ajuta la explicarea acestei propuneri. Primul exemplu este un exemplu introductiv care arată modul în care rezultatul poate să pară când oferta cu seturile de rezultate este omisă:

Procedura AngajațiIINTEPT este o procedură simplă care afișează camerele tabletei și numele tuturor angajaților care lucrează într-un departament specific. Numărul departamentului este parametrul de procedură și trebuie să specificați când este numit. Executarea acestei proceduri afișează un tabel cu două coloane, a cărei anteturi coincid cu numele coloanelor corespunzătoare din tabelul de bază de date, adică. ID-ul și numele de familie. Pentru a modifica titlurile coloanei rezultate (precum și tipul lor de date), SQL Server 2012 aplică o nouă ofertă cu seturi de rezultate. Aplicarea acestei propuneri este prezentată în exemplul de mai jos:

Utilizați Sampledb; Exec AngajațiInDept "D1" cu seturi de rezultate ((int nul, [prenume] Char (20) Nu NULL));

Rezultatul procedurii stocate cauzate de această metodă va fi după cum urmează:

După cum puteți vedea, pornirea procedurii stocate utilizând propunerea cu seturi de rezultate în instrucțiunea Execute vă permite să modificați numele și tipul de date ale coloanelor setului rezultat din această procedură. Astfel, această nouă funcționalitate oferă o mai mare flexibilitate în executarea procedurilor stocate și plasându-și rezultatele într-un tabel nou.

Schimbarea structurii procedurilor stocate

Motorul bazei de date acceptă, de asemenea, instrucțiuni Modificați procedura. Pentru a modifica structura procedurilor stocate. ALTER Manualul de procedură este de obicei aplicat pentru a schimba instrucțiunile tranzacționate-SQL în cadrul procedurii. Toți parametrii instrucțiunii de procedură Alter au aceeași valoare ca parametrii declarației de procedură de creație. Scopul principal al utilizării acestei instrucțiuni este de a evita suprascrierea drepturilor existente ale procedurii stocate.

Componenta motorului bazei de date acceptă tipul de date cursor.. Acest tip de date este utilizat pentru a declara cursoare în procedurile stocate. Cursor - Acesta este un design de programare folosit pentru a stoca rezultatele interogării (de obicei o apelare a rândului) și pentru a oferi utilizatorilor posibilitatea de a afișa această linie de rezultate.

Pentru a elimina unul sau grupul de proceduri stocate utilizate drop Instrucțiuni de procedură. Ștergerea procedurii stocate poate doar proprietarul sau membrii rolurilor predefinite DB_OWNER și SYSADMIN.

Proceduri stocate și miercuri CLR

SQL Server acceptă mediul comun CLR (Runtime de limba comună), care vă permite să dezvoltați diferite obiecte de bază de date (proceduri stocate definite de utilizator, funcții statistice declanșatoare definite de utilizator și tipuri de date de utilizator), aplicând limbi de bază C # și vizuale. Mediul CLR vă permite, de asemenea, să efectuați aceste obiecte utilizând sistemul mediului general de implementare.

Mediul CLR este permis și interzis prin opțiune clr_enabled. Procedura de sistem sp_configure.care rulează pentru a executa instrucțiuni Reconfigurați.. În exemplul de mai jos, se arată cum puteți utiliza procedura SP_Configure System pentru a permite utilizarea mediului CLR:

Utilizați Sampledb; Exec sp_configure "clr_enabled", 1 reconfigurare

Pentru a crea, compila și salva procedura utilizând mediul CLR, trebuie să efectuați următoarea secvență de pași în ordinea specificată:

Creați o procedură stocată în C # sau Visual Basic, apoi compilați-o utilizând compilatorul corespunzător.

Utilizarea instrucțiunilor Creați asamblare., Creați un fișier executabil corespunzător.

Efectuați procedura utilizând instrucțiunea Execute.

Figura de mai jos prezintă schema grafică a etapelor prezentate anterior. Următoarea este o descriere mai detaliată a acestui proces.

Creați mai întâi un program dorit în orice mediu de dezvoltare, de exemplu Studio vizual.. Compilați programul finit în codul obiect utilizând compilatorul C # sau Visual Basic. Acest cod este stocat în fișierul bibliotecii dinamice (.dll), care servește ca o sursă pentru instrucțiunea Creare asamblare, creând un cod executiv intermediar. Apoi, executați instrucțiunea Creare procedură pentru a salva codul executabil ca obiect baza de date. În cele din urmă, executați procedura de execuție utilizând instrucțiunea Execute deja familiarizată cu noi.

Exemplul de mai jos prezintă codul sursă al procedurii stocate în limba C #:

Folosind sistemul.Data.sqlcient; folosind Microsoft.sqlserver.Server; Clasa parțială publică (int a conglamilor () (int rânduri; conexiune SQLConnection \u003d nou sqlconnection ("conexiune conexiune \u003d true"); conexiune.open (); sqlommand cmd \u003d conexiune.Createcommand \u003d "CMD.comMandText \u003d" Select Count ( *) Ca "Număr de angajați" + "de la angajat"; rânduri \u003d (int) cmd.executescalar (); conectare.close (); rânduri de întoarcere;)))

Această procedură implementează o cerere de numărare a numărului de rânduri din tabelul angajatului. În utilizarea directivelor la începutul programului, se specifică spațiile de nume necesare pentru executarea acestuia. Utilizarea acestor directive vă permite să specificați în cod sursa Nume de clasă fără specificarea în mod explicit a spațiilor de nume corespunzătoare. Următorul este determinat de clasa stocată pentru care se aplică. atributul sqlprocedure.Ceea ce informează compilatorul că această clasă este o procedură stocată. În interiorul codului de clasă, este definită metoda Contemployeees (). Conectarea la sistemul de baze de date este stabilită de o instanță de clasă. Sqlconnection.. Pentru a deschide conexiunea, se aplică metoda Open () () din această instanță. DAR creațiCOMMAND () Metodă vă permite să vă întoarceți la instanța de clasă Sqlcommnd.pe care este trecut comanda SQL dorită.

În următorul cod de fragment:

Cmd.comMandText \u003d "Selectați numărul (*) ca" Număr de angajați "+" de la angajat ";

selectați instrucțiunile sunt utilizate pentru a număra numărul de rânduri din tabelul angajatului și a afișa rezultatul. Textul comenzii este specificat prin atribuirea unei proprietăți CMD CMD variabilă returnată de metoda CreateCommand (). Următorul se numește executescalar () metoda Sqlcommand instanță. Această metodă returnează o valoare scalară care este transformată într-un tip întreg de date int și este atribuită variabilei rândurilor.

Acum puteți compila acest cod utilizând mediul de studio vizual. Am adăugat această clasă la proiectul numit Crstoredoredress, astfel încât studioul vizual compilează ansamblul la același nume cu extensia * .dll. Exemplul de mai jos prezintă următorul pas în crearea unei proceduri stocate: crearea unui cod executat. Înainte de a efectua codul în acest exemplu, trebuie să aflați locația fișierului DLL compilat (de obicei localizat în dosarul proiectului Debug).

Utilizați Sampledb; Du-te de asamblare CLPStoreduri de la "D: \\ proiecte \\ clștiredprocedres \\ bin \\ Debug \\ clproceds.dll" cu permision_set \u003d Sigur

Instrucțiunea Creare asamblare ia codul controlat ca intrare și creează obiectul corespunzător pentru care puteți crea proceduri media stocate CLR definite de funcția de utilizator și declanșatoare. Această instrucțiune are următoarea sintaxă:

Creați ansamblul ansamblului_name [proprietar de autorizare_name] din acordurile de sintaxă (dll_file)

Parametrul Ansamblu_name indică numele ansamblului. În autorizația opțională de sugestie, este indicată numele rolului ca proprietar al acestui ansamblu. În clauza de la, calea este indicată în cazul în care sunteți descărcat.

Oferta cu permision_set. Este o sugestie foarte importantă a instrucțiunii Creare asamblare și ar trebui să fie indicată întotdeauna. Acesta definește un set de drepturi de acces furnizate de Codul Adunării. Sigiliul este cel mai limitativ. Codul de asamblare care are aceste drepturi nu poate accesa resursele sistemului extern, cum ar fi fișierele. Seturile de drepturi externe_access permite codului de asamblare să acceseze anumite resurse externe ale sistemului, iar Brectssetul nesigur oferă acces nelimitat la resurse, atât în \u200b\u200binteriorul, cât și în afara sistemului de baze de date.

Pentru a salva informațiile despre codul de asamblare, utilizatorul trebuie să poată executa instrucțiunile de asamblare Creare. Proprietarul de asamblare este utilizatorul (sau rolul) care efectuează această instrucțiune. Proprietarul de asamblare poate face un alt utilizator folosind instrucțiunile de autorizare de autorizare Crearea schemei.

Componenta motorului bazei de date acceptă, de asemenea, instrucțiunile de asamblare a ansamblului Alter. Instrucțiuni alter asamblare Folosit pentru a actualiza ansamblul la ultima versiune. Această instrucțiune adaugă sau șterge fișierele asociate cu ansamblul corespunzător. Scoateți instrucțiunile Asamblare Îndepărtează ansamblul specificat și toate fișierele asociate din baza de date curentă.

În exemplul de mai jos, se arată că creează o procedură stocată bazată pe un cod gestionat, implementat anterior:

Utilizați Sampledb; Du-te Crearea de regimuri de procedură ca nume extern CLSTOREDROCEDURES.STREDEDROCEDURES.COUNTECHOYEES

Instrucțiunea Creare procedură din exemplu este diferită de aceleași instrucțiuni din exemple înainte de a conține numele extern Pame.. Acest parametru indică faptul că codul este creat de mediul CLR. Numele din această teză constă din trei părți:

asamblare_name.class_name.method_name.

asamblare_name - indică numele ansamblului;

cLASS_NAME - indică numele clasei totale;

metoda_name este o parte opțională, indică numele metodei specificate în clasă.

Procedura Contemployeses este prezentată în exemplul de mai jos:

Utilizați Sampledb; Declarați @count Int Executees @count \u003d Contemployees Print @count - se va întoarce 7

Instrucțiunea de imprimare returnează numărul curent de rânduri din tabelul angajatului.

Includeți în procedurile dvs. o linie - setată la:

Cu fiecare expresie DML, SQL Server ne returnează cu atenție un mesaj care conține numărul de înregistrări procesate. Aceasta informatie Poate fi util pentru noi în timpul depunerii codului, dar după ce va fi complet inutil. Prescrierea setați Nocount, dezactivăm această caracteristică. Pentru procedurile stocate care conțin mai multe expresii sau \\ și cicluri, această acțiune poate oferi o creștere semnificativă a performanței, deoarece numărul de trafic va fi redus semnificativ.

Transact-SQL.

Utilizați numele schemei numit Obiect:

Ei bine, cred că este clar. Această operație spune serverului unde să caute obiecte și în loc să se agită aleatoriu în coșul său, el va ști imediat unde trebuie să meargă și ce să ia. Cu un număr mare de baze de date, tabele și proceduri stocate, se poate salva în mod semnificativ timpul și nervii.

Transact-SQL.

Selectați * de la dbo.mytable - Așa este să o faceți bine - în loc să selectați * de la mytable - și așa face rău - exec dbo.Myproc procedura este bun - unul este bun - mai ales Exec Myproc.

Nu utilizați prefixul "SP_" în numele procedurilor sale stocate:

Dacă numele procedurii noastre începe cu SP_, SQL Server va căuta mai întâi în baza de date principală. Faptul este că acest prefix este utilizat pentru procedurile stocate de servere interne personale. Prin urmare, utilizarea sa poate duce la costuri suplimentare și chiar rezultat incorect, dacă procedura cu același nume cum ați găsit în baza sa.

Utilizați dacă există (selectați 1) în loc de dacă există (selectați *):

Pentru a verifica disponibilitatea unei intrări într-un alt tabel, folosim o expresie dacă există. Această expresie Returnează adevărat dacă una dintre exprimarea internă este returnată cel puțin o propoziție, nu contează "1", toate difuzoarele sau mesele. Datele acuate, în principiu, nu sunt utilizate. Astfel, pentru a comprima traficul în timpul transferului de date, este mai logic să se utilizeze "1", după cum se arată mai jos.