SQL egcinwe izinqubo: ukudala kanye nokusetshenziswa

SQL egciniwe inqubo elisebenzisekayo isofthiwe module ukuthi zingagcinwa in database ngesimo izinto ezihlukahlukene. Ngamanye amazwi, kuba into equkethe SQL-isitatimende. Lezi izinqubo egcinwe angasayindwa e isicelo iklayenti ukuze uthole ukusebenza okuhle. Ngaphezu kwalokho, izindawo ezifana bavame ngokuthi kusuka kwamanye izikripthi noma kunoma iyiphi enye ingxenye.

isingeniso

Abantu abaningi bakholelwa ukuthi zifana nezinqubo ahlukahlukene izilimi ephezulu ezingeni izinhlelo (ngokulandelana, ngaphandle MS SQL). Mhlawumbe lokhu kuyiqiniso. Sinenkosi imingcele ngendlela efanayo, angakhipha ukubaluleka efanayo. Ngaphezu kwalokho, kwezinye izimo, bona mbeni. Ngokwesibonelo, ehlangana yolwazi ka DDL nedatha DML, kanye nemisebenzi umsebenzisi (igama le khodi - UDF).

izinqubo SQL Empeleni egcinwe abe ezahlukene izinzuzo ukuthi uwahlukanise phakathi izinqubo ezinjalo. Ukuphepha, kokuhlukana izinhlelo umkhiqizo - konke lokhu edonsela abasebenzisi ukusebenza yolwazi, bayanda. Isiqongo ukuthandwa weza izinqubo iminyaka 2005-2010, lapho ngathola uhlelo kusukela "Microsoft" ngaphansi kwegama «SQL Ukuphathwa Server Studio». Ngosizo lwawo, umsebenzi nge yolwazi sekulula kakhulu, zama-practical futhi kuwusizo kakhudlwana. Unyaka nonyaka, lokhu indlela yokudlulisa ulwazi yatha- imvelo izinhlelo. Namuhla, MS SQL Server kuyinto uhlelo ngokuphelele evamile, okuyinto ukuze abasebenzisi "ukuxhumana" yolwazi, wema belingana "Excel".

Uma ushayele inqubo, it is kweso kusetshenzwe iseva ngaphandle izinqubo ezingadingekile futhi kokungenela komsebenzisi. Ungabese enze noma yisiphi isenzo ngolwazi: ukususwa, ukubulawa, ushintsho. Over zonke lokhu kungenjongo yokuba DDL-opharetha, ngubani olulodwa-handedly ukwenza izenzo eziyinkimbinkimbi kulawo izinto. Futhi konke lokhu kwenzeka ngokushesha kakhulu, futhi iseva empeleni ukulayishwa. Lokhu ijubane futhi umkhiqizo akuvumele ngokushesha uthumele esiningi ulwazi umsebenzisi kwiseva, noHezekeli.

Kute kwetfulwe umsebenzi ngolwazi, zikhona eziningana ngezilimi izinhlelo ubuchwepheshe. Lezi zihlanganisa, isibonelo, PL / SQL kusukela uhlelo lokuphathwa semininingwane Oracle, PSQL e InterBase futhi izinhlelo Firebird, kanye zakudala "maykrosoftovskih» transact-SQL. Bonke zenzelwe dala futhi uqalise izinqubo egcinwe, okukuvumela ukuthi usebenzise ama-algorithm zabo phezu bokudla esikhulu egciniwe. Kuyadingeka futhi ukuqinisekisa ukuthi labo afeze ukuphathwa kolwazi ezinjalo, ongavikela zonke izinto kusukela amaqembu okungagunyaziwe wesithathu, ngakho-ke, ukudala, ukuguqulwa noma ukusulwa idatha ethile.

umkhiqizo

Lezi izinto semininingwane lingacushwa ngezindlela ezahlukene. Lokhu kuvumela abasebenzisi ukukhetha uhlobo ngenqubo kungafaneleka kakhulu, okuyinto usindisa isikhathi namandla. Ngaphezu kwalokho, inqubo ngokwalo icutshungulwe, kanjalo sigweme isikhathi omkhulu esichithwa ukuxhumana phakathi iseva kanye umsebenzisi. I module kungenziwa reprogrammed futhi washintsha indlela okuyiyona yona e ngokuphelele noma nini. kuwufanele Ikakhulukazi siphawula nesivinini lapho kwethulwa inqubo SQL egcinwe kwenzeka: inqubo ishesha naleti kuso, okwenza kube lula futhi uyisithelo esiyisimanga.

ukuphepha

Lolu hlobo lwe-information processing iyahluka kwenye izinqubo ezifanayo ukuthi lunikeza ukulondeka ethuthukisiwe. Lokhu kuqinisekiswa iqiniso lokuthi abanye abasebenzisi kusuka izinqubo zokufinyelela luqedwe ngokuphelele. Lokhu kuzovumela Administrator ukuqhuba imisebenzi nabo ngokuzimela, ngaphandle kokwesaba ukucabhela noma ezifana nokufinyelela okungagunyaziwe kuya database.

ukudluliswa kwedatha

Ukuxhumana phakathi egcinwe inqubo SQL futhi isicelo iklayenti ukusebenzisa nemingcele bese ubuyela amanani. Lesi sakamuva akudingekile ukuba adlulisele idatha ku inqubo egciniwe, kodwa ulwazi (ikakhulukazi ngesicelo yomsebenzisi), kanye nezinqubo ukuze SQL. Inqubo Kanye egcinwe kuqede umsebenzi walo, it ithumela amaphakethe idatha emuva (kodwa, futhi, uma uthanda) ukuze isicelo ukushaya usebenzisa ezihlukahlukene izindlela kungenziwa njengoba ikholi egcinwe inqubo SQL futhi ukubuya, isibonelo:

- ukuthumela idatha nge lokukhipha ipharamitha uhlobo;

- ukuthumela idatha nge isitatimende ukubuya;

- ukudluliswa kwedatha nge Ukukhetha yenethiwekhi.

Futhi manje sibheke indlela le nqubo ibukeka ngaphakathi efanayo.

1. Yakha Exec-agcinwe kule nqubo SQL

Ungakha inqubo e MS SQL (Managment Studio). Emva inqubo kudalwa, it is ohlwini ehlelwe semininingwane node, lapho inqubo wenziwa opharetha wendalo. Ukuze bakhiphe SQL izinqubo egcinwe usebenzisa Exec-inqubo elinegama into.

Lapho edala gama livela inqubo yokuqala, bese wenza okukodwa noma ngaphezulu nemingcele babelwa kuye. Amapharamitha kungaba ozikhethela. Ngemva ipharamitha (s), okusho umzimba inqubo, letawubhalwa, kubalulekile ukuba afeze eminye imisebenzi edingekayo.

Iqiniso lokuthi umzimba ungaba eziguquguqukayo endawo, elise-ke, futhi lezi eziguquguqukayo kukhona zendawo maqondana nezinqubo. Ngamanye amazwi, bangakwazi kubhekwe kuphela ngaphakathi ukwelashwa umzimba Microsoft SQL Server. izinqubo egcinwe kulesi simo ezibhekwa wendawo.

Ngakho, ukwakha nqubo, sidinga igama inqubo kanye ipharamitha okungenani eyodwa njengoba umzimba inqubo. Sicela uqaphele ukuthi inketho esihle kulokhu ukudalwa kanye nokuqaliswa izinqubo ngagama schema ku somhleli.

Umzimba inqubo ungaba noma yiluphi uhlobo opharetha SQL, isibonelo, ezifana ekudaleni itafula, ufaka igama elilodwa noma ngaphezulu imigqa etafuleni, liqine uhlobo egciniwe kanye uhlamvu, nokunye. Noma kunjalo, umzimba inqubo kuvimbela abanye imisebenzi kuwo. Ezinye ukulinganiselwa ezibaluleke kakhulu tibalwe lapha ngentasi:

- umzimba akudali noma iyiphi enye inqubo egciniwe;

- umzimba akumelwe ukudala oyiphutha mayelana into;

- umzimba akudali noma iyiphi trigger.

2. Setha okuguquguqukayo emzimbeni inqubo

Ungenza eziguquguqukayo endawo inqubo emzimbeni, bese bayoba ngaphakathi emzimbeni inqubo. Ukuvivinya okuhle ukwakha variable ekuqaleni olulondoloziwe inqubo umzimba. Kodwa ungakwazi futhi usethe eziguquguqukayo kuphi emzimbeni into.

Ngezinye izikhathi ungase uphawule ukuthi ezinye eziguquguqukayo asethwe kumugqa ofanayo, futhi variable ngayinye ahlukaniswe ngekhoma. Phawula futhi ukuthi variable is prefixed nge @. Emzimbeni inqubo, ungasetha kwenombolo ofuna. Ngokwesibonelo, kwenombolo @ NAME1 kungenziwa yamemezela ngasekupheleni umzimba inqubo. Ukuze ukwabela Inani kwenombolo limenyezelwe usebenzisa iqoqo idatha yomuntu siqu. Ngokungafani isimo lapho abangaphezu koyedwa variable limenyezelwe kumugqa ofanayo, eyodwa kuphela iqoqo idatha yomuntu siqu esetshenziswa kulesi simo.

Abasebenzisi bavame ukubuza umbuzo: "Indlela nika amagugu amaningi isitatimende eyodwa yomzimba inqubo 'Awu?. Umbuzo othakazelisayo lowo, kodwa lokhu lula kakhulu kunalokho okucabangayo. Impendulo: ngosizo ngazimbili ezifana «Khetha Var = inani". Ungasebenzisa lawa ngazimbili, ahlukaniswe ikhefana.

3. Yakha inqubo SQL egciniwe

Ngo ezihlukahlukene izibonelo zabantu ukubonisa ngokudala inqubo elula agcinwe futhi akhiphe ke. Nokho, nqubo ingathatha imingcele yokuthi inqubo Ukushayela kwezindlela ke kuyodingeka value eduze ke (kodwa hhayi ngaso sonke isikhathi). Uma ukufanisa ke iqala izinqubo elihambisana ngaphakathi emzimbeni. Ngokwesibonelo, uma uhlela indlela inqubo ukuthi kuzothatha umuzi nesifunda koshayayo bese ubuyela idatha mayelana kangakanani ababhali ubheke umuzi nesifunda elihambisana. Inqubo ngeke ukhononda itafula semininingwane abalobi, isibonelo, Pubs, lapho efeza lo abalobi ukubala. Ukuze uthole lezi yolwazi, isibonelo, i-Google Amafayela SQL iskripthi nekhasi SQL2005.

Esikhathini esidlule, le nqubo ithatha nemingcele ezimbili, okuyinto ngesiNgisi yayiyobizwa ngokuthi kunemibandela @State futhi @City. Uhlobo lwedatha oluhambisana uhlobo ebalulwe isicelo. Umzimba inqubo has eziguquguqukayo yangaphakathi @TotalAuthors (bonke ababhali), futhi lokhu okuguquguqukayo lisetshenziselwa babonise inombolo yabo. Kube sekulandela isicelo Ukukhetha kwesigaba ukuthi kuzo zonke izici. Ekugcineni, ukubaluleka ibalwa uboniswa iwindi okukhipha usebenzisa isitatimende ukuphrinta.

Kanjani ukwenza inqubo SQL egciniwe

Kunezindlela ezimbili ukwenza inqubo. Indlela yokuqala kuboniswa odlulayo nemingcele njengoba uhlu oluhlukaniswe ngofeleba kwenziwa emva igama inqubo. Ake sithi ube amanani amabili (njengoba kulesibonelo odlule). Loku okuyigugu eqoqwe usebenzisa eziguquguqukayo futhi inqubo @State @City. Kule ndlela, imingcele ebalulekile oda ukudluliswa. Le nqubo ibizwa ngokuthi ukudluliswa ukulandelana agumenti. Ngo indlela yesibili, nemingcele sebevele babelwe ngqo, kuleso simo oda ayibalulekile. Le ndlela lwesibili ukudluliswa agumenti okuthiwa ezaziwayo.

Inqubo ingahluka kancane zehluke ejwayelekile. Noma kunjalo, njengoba esikhathini esidlule, kodwa imingcele udlulele lapha kuphela. Lokho @City ipharamitha lilondolozwe lokuqala, futhi @State egcinwe eduze inani elimisiwe. Ukulungiselelwa okuzenzakalelayo ngokuvamile zabelwe ngokwehlukile. SQL izinqubo egcinwe kukhona imingcele njengoba elula. Kulokhu, uma okukhethwa kukho okuthi "le UT 'ubuyisela inani elizenzakalelayo" CA ". Ku-samuntu yesibili lidlula kuphela agumenti nenani elilodwa ngoba @City, futhi @State inketho ishintshela "CA". program abanolwazi bayelulekwa ukuthi zonke eziguquguqukayo okuzenzakalelayo zitholakala eduze kokuphela uhlu ipharamitha. Kungenjalo, ukukhishwa akunakwenzeka, khona-ke kufanele isebenze nokudluliswa agumenti okuthiwa ukuthi zinde futhi zithinte izinto eziyinkimbinkimbi.

4. elondolozwe Izinqubo SQL Server: izindlela yokubuyisela

Kunezindlela ezintathu ezinkulu ukuthumela idatha egcinwe kule nqubo ngokuthi. Basuke ngezansi:

- ukubuya amagugu isixazululi nkinga yehadiwe nezinsiza;

- lokukhipha ipharamitha izinqubo egcinwe;

- Khetha okukodwa izinqubo egcinwe.

4.1 Ukubuya amagugu SQL egcinwe izinqubo

Kulesi inqubo, inqubo usetha ukubaluleka variable endawo silubuyisele. Inqubo Ungakwazi futhi ngokuqondile ukubuyela value njalo. Ngo isibonelo esilandelayo, sidale inqubo ebuyisela lonke inani abaxhasi. Uma siqhathanisa lokhu inqubo yangaphambilini, ungabona ukuthi ukubaluleka ukuphrinta esikhundleni reverse.

Manje ake sibheke indlela ukwenza inqubo kanye ukubonisa ukubaluleka, ukuyibuyisa. Buciko izinqubo letidzingeka ekusunguleni nowokunyathelisa variable, okuyinto eyenziwa ngemva inqubo. Phawula ukuthi kunokuba ukunyathelisa opharetha angasebenzisa Khetha-opharetha, isibonelo, Khetha @RetValue, futhi OutputValue.

4.2 Izinqubo lokukhipha sepharamitha SQL egciniwe

Inani impendulo ingasetshenziswa ukubuyela kwenombolo elilodwa sibonile esidlule. Ukusebenzisa lokukhipha ivumela uhlelo inqubo ukuthumela eyodwa noma ngaphezulu eziguquguqukayo ephathini ukushaya. Lokukhipha ipharamitha luchazwa ngokuthi isikhathi leligama ukhiye «lokukhipha» uma udala inqubo. Uma ipharamitha icaciswe njengenhlangano ipharamitha okukhipha, into inqubo okumelwe uyabele ke inani. Elondolozwe izinqubo SQL, izibonelo ezingasetshenziswa kubonakala ngezansi, Kulezo zimo kubuyela Imininingwane sokugcina.

Kulesi sibonelo, kuzoba ngezimpelasonto ezimbili igama: @TotalAuthors futhi @TotalNoContract. Basuke ecacisiwe ohlwini ipharamitha. Lezi eziguquguqukayo kuthiwa amagugu babelwe phakathi nomzimba inqubo. Lapho sisebenzisa nemingcele okukhipha, ucingo ungabona ukubaluleka lihlelwe emzimbeni inqubo.

Ngaphezu kwalokho, kule ndaba langaphambilini, eziguquguqukayo ezimbili kuthiwa ukubona amagugu ukuthi basuke efakwe izinqubo egcinwe, MS SQL Server njengenhlangano ipharamitha okukhiphayo. Khona-ke le nqubo wenziwa ngokusebenzisa ukubaluleka evamile «CA» ipharamitha. Amapharamitha abalandelayo okukhipha futhi ngenxa yalokho, kwathiwa eziguquguqukayo ziyadlula ngendlela yakhona. Sicela wazi ukuthi uma kwenombolo okukhipha elisemqoka futhi ecacisiwe lapha. Emva inqubo usuphothuliwe ngempumelelo, inani elibuyisiwe ngokusebenzisa nemingcele okukhipha ayaboniswa iwindi umyalezo.

4.3 Ukukhetha izinqubo SQL egciniwe

Le nqubo ibizwa esetshenziswa ukubuyela isethi ethebuleni ifomu amanani wedatha (ukufanisana) ngokubiza inqubo egciniwe. Kulesi sibonelo, SQL egciniwe inqubo ngezimiso @AuthID ucela ithebula "Ababhali" okufika amarekhodi esibuyiswe lokhu @AuthId ipharamitha. Khetha opharetha onquma lokho kufanele ibuyiselwe lidonsa isixazululi nkinga yehadiwe nezinsiza. Lapho isixazululi nkinga yehadiwe nezinsiza AuthId ngocansi emuva. Onjalo inqubo njalo ibuyisela irekhodi eyodwa kuphela noma akukho nhlobo. Nokho, inqubo egciniwe akanayo imikhawulo ukubuya okungaphezu kokukodwa. Ngokuvamile izibonelo zingatholakala lapho idatha ukubuya ezikhethiwe ngokusebenzisa imingcele kanye eziguquguqukayo ibalwa kwenziwa ngokunikeza sebuningini amagugu Imininingwane.

Ekuphetheni

Inqubo egcinwe wuhlelo kuhle sina, wabuya noma idluliselwe, kanye liqine eziguquguqukayo kudingekile ngenxa isicelo iklayenti. Njengoba inqubo egciniwe uyabulawa kuseva ngokwayo, ukushintshanisa kwe-data e omningi phakathi iseva kanye isicelo iklayenti (kwabanye izibalo) kungagwenywa. Lokhu kwenza kube nokwenzeka ukunciphisa umthwalo iseva SQL, okuyinto, Yebo, esandleni abanikazi babo. Omunye zenyathi zigcinwa izinqubo T SQL, kodwa ekutadisheni kwabo kuyadingeka ukuze ababehumusha ukudalwa database umxhwele. Nokho, Kukhona ezinkulu, ngisho omningi umehluko wemisindo nemiqondo yamagama ukuthi kungaba wusizo ekuhloleni izinqubo egcinwe, lesi sidingo kulabo uhlela ukwenzani izinhlelo ezihambisanayo, kuhlanganise efanele.