sql存儲(chǔ)過程及視圖創(chuàng)建實(shí)例及語(yǔ)法

1、sql server存儲(chǔ)過程transact-sql屮的存儲(chǔ)過租，非常類似于java語(yǔ)言屮的方法，它可以重復(fù)調(diào)用。當(dāng)存儲(chǔ)過程執(zhí)行一次后，可以將語(yǔ) 句緩存屮，這樣下次執(zhí)行的時(shí)候直接使用緩存屮的語(yǔ)句。這樣就可以提萵存儲(chǔ)過程的性能。0辦儲(chǔ)過程的概念存儲(chǔ)過程procedure是一組為了完成特定功能的sql語(yǔ)句集合，經(jīng)編譯話存儲(chǔ)在數(shù)裾庫(kù)屮，川戶通過指定存儲(chǔ)過 程的名稱并給出參數(shù)來執(zhí)行。存儲(chǔ)過程中可以包含邏機(jī)控制語(yǔ)句和數(shù)裾操縱語(yǔ)句，它可以接受參數(shù)、輸出參數(shù)、返冋牮個(gè)或多個(gè)結(jié)果集以及返冋 值。山于存儲(chǔ)過程在創(chuàng)建時(shí)即在數(shù)據(jù)庫(kù)服務(wù)器.h進(jìn)行了編譯并存儲(chǔ)在數(shù)據(jù)厙中，所以存儲(chǔ)過程運(yùn)行要比單個(gè)的sql語(yǔ)句 塊?？?。同

2、時(shí)屮于在調(diào)用時(shí)只需用提供存儲(chǔ)過程名和必耍的參數(shù)位息，所以在一定程度上也以減少m絡(luò)流墩、簡(jiǎn)單m 絡(luò)負(fù)擔(dān)。1、存儲(chǔ)過程的優(yōu)點(diǎn)a、存儲(chǔ)過程允許標(biāo)準(zhǔn)飢件式編程存儲(chǔ)過程創(chuàng)建后可以在程序中被多次調(diào)用執(zhí)行,而不必朮新編寫該存儲(chǔ)過程的sql語(yǔ)句。而且數(shù)據(jù)庫(kù)專業(yè)人員 可以隨吋對(duì)存儲(chǔ)過程進(jìn)行修改，但對(duì)應(yīng)用程序源代碼卻亳無影響，從而極人的提商了程序的可移梢性。b、存儲(chǔ)過稈能夠?qū)崿F(xiàn)較快的執(zhí)行速度如果某一操作乜含大量的t-sql語(yǔ)句代碼，分別被多次執(zhí)行，那么存儲(chǔ)過程要比批處理的執(zhí)行速度快得多。因 為存儲(chǔ)過程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過程時(shí)，查詢優(yōu)化器對(duì)典進(jìn)行分析、優(yōu)化，并給出坡終被存在系統(tǒng)表中的 存儲(chǔ)計(jì)劃。而批處理

3、的t-sql語(yǔ)句毎次運(yùn)行都需耍預(yù)編譯和優(yōu)化，所以速度就要慢-些=c、存儲(chǔ)過程減輕網(wǎng)絡(luò)流跫對(duì)于同一個(gè)針對(duì)數(shù)據(jù)庫(kù)對(duì)象的操作，如采這一揀作所涉及到的t-sql語(yǔ)句被組織成存儲(chǔ)過程，那么當(dāng)在客戶 機(jī)卜.調(diào)用該存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳遞的只足該調(diào)用語(yǔ)句，否則將會(huì)足多條sql語(yǔ)句。從而減輪了網(wǎng)絡(luò)流泉，降低了網(wǎng) 絡(luò)負(fù)載。d、存儲(chǔ)過程可被作為-種安全機(jī)制來充分利用系統(tǒng)管理w可以對(duì)執(zhí)行的某個(gè)存儲(chǔ)過程進(jìn)行權(quán)限限制，從而能夠?qū)崿F(xiàn)對(duì)某些數(shù)據(jù)訪問的限制，避免非授權(quán)用戶 對(duì)數(shù)據(jù)的汸問，保證數(shù)據(jù)的安全。0系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程足系統(tǒng)創(chuàng)迷的存儲(chǔ)過程,h的在于能夠方便的從系統(tǒng)表中査洵信息或完成與更新數(shù)據(jù)痄表和關(guān)的矜理 任務(wù)或其他

4、的系統(tǒng)管理任務(wù)。系統(tǒng)存儲(chǔ)過程主耍存儲(chǔ)在master數(shù)據(jù)庫(kù)中，以“sp”下劃線開頭的存儲(chǔ)過程。盡管這些系 統(tǒng)存儲(chǔ)過程在master數(shù)裾庫(kù)屮，但我們?cè)谄渌麛?shù)裾庫(kù)還是m以調(diào)用系統(tǒng)存儲(chǔ)過程。宵一些系統(tǒng)存儲(chǔ)過程會(huì)花創(chuàng)逑新的 數(shù)據(jù)庫(kù)的時(shí)候被自動(dòng)創(chuàng)迚在當(dāng)前數(shù)據(jù)痄中。常用系統(tǒng)存儲(chǔ)過程有：exec sp_databases;-查看數(shù)掘庫(kù)exec sp_tables;-色看表exec sp_columns student;-查看夕!jexec sp_helplndex student;-杏看索弓iexec sp_helpconstraint student; -約束exec sp_stored_procedure

5、s;exec sp_helptext 1 sp_stored_procedures *;-查看存儲(chǔ)過程創(chuàng)建、記義語(yǔ)句exec sp_rename student, stulnfo;-修改表、索弓i、歹!j的名稱exec sp_renamedb mytempdb, mydb;-更改數(shù)據(jù)庫(kù)名稱exec sp_defaultdb master1, mydb ； -改登錄名的默認(rèn)數(shù)捕牟exec sp_helpdb;-數(shù)據(jù)庫(kù)精助，査詢數(shù)掘庫(kù)信息exec sp_helpdb master;系統(tǒng)存儲(chǔ)過程示例：-表重命名exec sp_rename 1stu1, stud；select from stud;-

6、列重命名exec sp_rename 1 1,1sname1,1 column 1;exec sp_help 1 stud1;-重命名索引exec sp一rename n1 student.idx_cid1, n1idx_cidd1, n1 index 1;exec sp_help 1 student1;-查詢所有存儲(chǔ)過程select * from sys.objects where type = 1p1;select * from sys.objects where type_desc like f %pro% f and name like f sp% 1 ;0川戶u定

7、義存儲(chǔ)過程1、創(chuàng)建語(yǔ)法create proc | procedure proname參數(shù)數(shù)據(jù)類型=默認(rèn)值output,參數(shù)數(shù)裾類型=默認(rèn)值output,< < <assql_statements2、創(chuàng)建不帶參數(shù)存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程if (exists (select * from sys.objects where name = 1 proc_get_student1) drop proc proc一get一studentgocreate proc proc_get_studentselect * from student;-調(diào)用、執(zhí)行存儲(chǔ)過程exec proc_get_st

8、udent;3、修改存儲(chǔ)過程-修改存儲(chǔ)過程alter proc proc_getstudent asselect from student;4、帶參存儲(chǔ)過程-帶參存儲(chǔ)過程if (object_id(1proc_find_stu1,1p1) is not null)drop proc proc_find_stugocreate proc proc_find_stu (qstartld int, qendlci int)asselect * from student where id between startld and oendld goexec proc一find_stu 2, 4;5、帶通

9、妃符參數(shù)存儲(chǔ)過程-帶通配符參數(shù)存儲(chǔ)過程if (object_id(1proc_findstudentbyname1,1p1) is not null)drop proc proc_findstudentbynamegocreate proc proc_findstudentbyname(name varchar(20) = 1 %j%1, nextname varchar(20) = '%')asselect * from student where name like name and name like nextname; goexec proc_findstudentby

10、name;exec proc_findstudentbyname 1%o%1,11% f ;6、帶輸出參數(shù)存儲(chǔ)過程if (object一id(1proc_getstudentrecord1,丨p 丨)is not null) drop proc proc_getstudentrecordgocreate proc proc_getstudentrecord(id int,-默認(rèn)輸入?yún)?shù)name varchar (20) out,-輸出參數(shù)age varchar (20) output-輸入輸出參數(shù))asselect name = name, age = age from student whe

11、re id = id and sex=age;go declare id int,qname varchar(20),temp varchar(20);set id = 7;set temp = 1;exec proc_getstudentrecord id, name out, temp output; select name, temp; print name + 1# * + temp;7、不緩桴稃儲(chǔ)過程-with recompile 不緩存if (object一id(proc一temp，p) is not null)drop proc proc_tempgocreate proc pr

12、oc一tempwith recompileasselect * from student; goexec proc_temp;8、加密存儲(chǔ)過程加密 with encryptionif (object_id(1proc一temp_encryption，p) is not null)drop proc proctemp一encrypt iongocreate proc proc_temp_encryptionwith encryptionselect * from student;goexec proc一temp一encryption;exec sp_helptext 1proc_temp1;ex

13、ec sp_helptext 1proc_temp_encryption f;9、帶游標(biāo)參數(shù)存儲(chǔ)過程if (objectid(丨proc_cursor丨，1p1) is not null) drop proc proccursorgocreate proc proc_cursorcur cursor varying outputasset cur = cursor forward_only static for select id, name, age from student; open cur;go-調(diào)用declare exec_cur cursor;declare id int,qnam

14、e varchar(20),age int;exec proccursor cur = execcur output;-調(diào)川存儲(chǔ)過kfetch next from exec_cur into id, name, age;whilefetch_status = 0)beginfetch next from exec一cur into id, name, age;print 1 id: 1 + convert (varchar, id) + 丨，name:1 + name + , age + convert(char, age);endclose exec_cur;deallocate exec_

15、cur;-刪除游標(biāo)10、分頁(yè)存儲(chǔ)過程存儲(chǔ)過程、row_number a成分頁(yè)if (object_id(1pro_page1,1p1) is not null)drop proc proccursorgocreate proc pro_page startindex int,endlnclex intselect count (*) from productselect * from (select row_number() over(order by pid) as rowld, * from product )tempwhere temp.row工d between startindex

16、and endlndex godrop proc pro_pageexec pro_page 1, 4-分頁(yè)存儲(chǔ)過程if (objectid(pro_page，1p1) is not null)drop proc pro_stugocreate procedure pro_stu (pageindex int,pagesize int)asdeclare qstartrow int, qendrow int set startrow = (pagelndex - 1)* pagesize +1set endrow = startrow + pagesize -1 select * from (

17、select *, row_number () over (order by id asc) as number from student)twhere t.number between qstartrow and qendrow;exec pro_stu 2, 2;0 raiserrorraiserror返網(wǎng)用戶定義的錯(cuò)誤信息，可以指定嚴(yán)寬級(jí)別，沒置系統(tǒng)變雖記錄所發(fā)生的錯(cuò)誤。詔法如f:raiserror(msg_id i msg_str | local一variable, severity, state,argument , .nwith option , .n)井msgd:在sysmess

18、ages系統(tǒng)表屮指定的用戶龍義錯(cuò)誤位息# msg_stn川戶定義的信息，信息圾大長(zhǎng)度在2047個(gè)宇符。# severity：川戶定義與該消息x朕的嚴(yán)承級(jí)別。當(dāng)使川msgjd引發(fā)使川sp_addmessage創(chuàng)建的川戶記義消息 時(shí)，raiserror卜.指定嚴(yán)重性將覆蓋sp_addmessage中定義的嚴(yán)重性。任何用戶可以指定0-18直接的嚴(yán)蜇級(jí)別。只冇sysadmin冏定服務(wù)器角色常用或具有alter trace權(quán)限的用戶才能 指定19-25直接的嚴(yán)甫級(jí)別。19-25之間的安全級(jí)別忠耍使用with log選項(xiàng)。林state：介于1至127直接的茌何幣數(shù)。state默汄値是1。raiserror

19、(1 is error 1, 16, 1);select * from sys.messages;-使相sysmessages中定義的消息raiserror (33003, 16, 1);raiserror(33006, 16, 1);sqi存儲(chǔ)足數(shù)裾痄操作過稈屮比較重耍的一個(gè)環(huán)節(jié)，對(duì)r一些初學(xué)芯來說也足比較抽象難理解的，本文我將 通過兒個(gè)實(shí)例來解析數(shù)據(jù)庫(kù)中的sql存儲(chǔ)過程，這樣就將抽象的事物形象化，比較容易理解。例1:create proc proc_stu snamc varchar(20), pwd varchar(20) asselect * from ren where snamc=

20、snamc and pwd=pwdgo查看結(jié) proc_stu admin，admin例2:不面的存儲(chǔ)過程實(shí)現(xiàn)用戶驗(yàn)證的功能，如果不成功，返冋0,成功則返回1.create procedure validate ©username char(20), ©password char(20), ©legal bit outputih exists (select * i-rom ren where sname = ishrname and pwd = password)select ©legal = 1elseselect legal = 0在程序屮調(diào)用該存

21、儲(chǔ)過程，并根裾legal參數(shù)的值判斷用戶足否合法。例3: 個(gè)髙效的數(shù)據(jù)分頁(yè)的存儲(chǔ)過程可以輕松應(yīng)付百萬數(shù)據(jù)create procedure pagetest 一用丁翻頁(yè)的測(cè)試 需要把排序字段放在第一列(firstid nvarchar (20) =nu 11，當(dāng)前貞面m的策一條記錄的排序字段的值 既astll) nvarchar(20) =null,當(dāng)前頁(yè)jilf里的嚴(yán)后一條kls；的排序字段的值isnext bit=null, true 1 : f頁(yè)；false 0:上一頁(yè) a 11 count int output, 返回總記錄數(shù) pagesize int output,返回一似的記錄數(shù) c

22、urpage int 頁(yè)號(hào)(弟幾頁(yè))0:第一頁(yè)；-1秘后一頁(yè)。 )asif curpago=0表示第一頁(yè) begin-統(tǒng)計(jì)總記錄數(shù)select allcount=count(productid) from product test set pagesize"10-返回第一頁(yè)的數(shù)據(jù)select top 10productid,productname,introductionfrom product_test order by productldendelse if curpage=l表示最后一頁(yè)select * from (select top 10 productld,product

23、name,introductionfrom product test order by productld desc ) as aa order by productld elsebeginif isnext=l-翻到f頁(yè)select top 10 productld,productname,introductionfrom product_tost where productld > lastid order by productldelse-翻到上一-頁(yè)select * from(select top 10 productld,productnamc,introduction fro

24、m product_tost where product id < eirstid order by product id dcsc) as bb order by product id end上文中講到的這三個(gè)例子都足sql存儲(chǔ)過程比較典型的例子，希柴大家好好學(xué)，都能夠?qū)W到大家各£1需® 的東兩。判斷是否存在存儲(chǔ)過程if exists( select name from sysobjects where name='proc_name,) drop proc proc_name創(chuàng)建存儲(chǔ)過程create proc proc_name parameter var

25、char(20)， parameter2 int as主體語(yǔ)句go if exists：判斷是否存在drop proc : 刪除存儲(chǔ)過程，后面接存儲(chǔ)過程名稱，名稱不用引起來create proc：創(chuàng)建存儲(chǔ)過程，后面接存儲(chǔ)過程名稱，名稱不用引起來parameter varchar(20)：參數(shù)，在執(zhí)行存儲(chǔ)過稈的時(shí)候需要傳入的參數(shù)，這爪足字符類艱，如果冇多個(gè)參數(shù) 的話，除了敁后一個(gè)參數(shù)，k他的參數(shù)后而需要加逗號(hào)(英文狀態(tài))，如果不需耍傳入，tt接去掉就行了。create proc proc_nameas主體語(yǔ)句go as :關(guān)鍵字主體語(yǔ)句：就是你要做的操作的語(yǔ)句，和平時(shí)的完全一樣，存儲(chǔ)過程就相當(dāng)把

26、平時(shí)的sql語(yǔ)句在外逝加了個(gè)殼的 感覺。比如這里你需要查詢一個(gè)表select * from tablenamel那么這hi就這樣寫 create proc proc_nameasselect * from tablenamel go傳參數(shù)：create proc proc_nameparameter varchar(20)asselect * from tablename where name=parameter go執(zhí)行的時(shí)候這樣exec proc_name 小明創(chuàng)建-個(gè)存儲(chǔ)過程(insertresult)添加學(xué)生成績(jī)，耍求輸入(學(xué)號(hào)，課程名，分?jǐn)?shù))如果輸入的學(xué)號(hào)不存在，則提示“學(xué)生基本信息

27、表中不存在此學(xué)號(hào)，請(qǐng)檢查后重新輸入”如果輸入的課程不存在，則提示“還沒有這門課程，請(qǐng)檢査后重新輸入”如果該學(xué)生的專業(yè)中沒有輸入的課程，則提示“該學(xué)生的專業(yè)里面沒有這門課"判斷輸入的成績(jī)是否是大于0,如果小于0則提示“你太狠了吧，竟然打負(fù)分！”判斷該學(xué)生的課程成績(jī)足否已經(jīng)存在，如果存在則提示"此學(xué)生的這門課程成績(jī)己經(jīng)錄入了，諳不要重復(fù)朵入 最后添加成功則提示“成績(jī)添加成功”create procedure dbo.insertresultnstudentno int，學(xué)號(hào)vchcourse varchar(32)，課稅nachievement int成績(jī))asset noco

28、unt on-table_student_base_info 為7:zl:坫木位總衣，列 student_no 為學(xué) y，student_specialty 為專業(yè)課名稱，student_achievement 為專業(yè)課成績(jī)-table_course為深程表，列course_name為課程名declare ncount int set ncount = 0declare nspecialty set nspecialty = 0declare ncourse set ncourse = 0declare (5)nexistachievement set blsmark = 0判斷學(xué)號(hào)是否存在s

29、electncountno = count(*)fromtable_student_base_infowheretable student base info.student no = nstudentnoif ncount < 1beginprint 1學(xué)生基本信息表屮不存在此學(xué)號(hào)，請(qǐng)檢査后重新輸入* return end判斷專業(yè)課及成績(jī)selectnspecialty = count(*),nexistachievement = studentachievement fromtable student base info wheretable 一 student_base_info.

30、student一specialty = vchcourseif nspecialty < 1beginprint *該學(xué)牛.的專業(yè)里面沒有這門課 return endif nexistachievement >= 0beginprint *此學(xué)生的這fj課程成績(jī)己經(jīng)錄入了，請(qǐng)不要重s澩入 return end判斷指定課程selectncourse = course_name fromtable 一 course wherecourse_name = vchcourseif ncourse < 1beginprint 還沒冇這門課秤，請(qǐng)檢色后®新輸入| return

31、end成績(jī)?yōu)樨?fù)if nachievement < 0beginprint 你太狠了吧，竟然打負(fù)分！ * return end錄入成績(jī)updatetable_student_basejnfosetstudentachievement = nachievement wheretablestudent_basenfo.studentno = nstudentnoprint *成績(jī)添加成功如何用sql語(yǔ)句創(chuàng)建一個(gè)視圖create view viewnameasselect字段列表from表1inner join 表 2 on表1.字段=表2.字段where條件sql視圖的創(chuàng)建和使用視圖這個(gè)東西在其他的軟件中，我們也經(jīng)?？吹玫?，例如word中就存視亂我們不難發(fā)現(xiàn)，視圖似乎是-種u示方式， 就像word樣，有幾種兄示版而，這就是視閣，那么sql中的視閣又該怎么定義呢？今兒我耍學(xué)習(xí)一下，做個(gè)筆記。 這足在繼sql數(shù)裾庫(kù)商級(jí)齊洵和f齊洵之后的又一個(gè)市要內(nèi)容。1. 什么是視圖?視圖是一個(gè)虛擬的表，是一個(gè)表屮的數(shù)據(jù)經(jīng)過某種篩選后的.顯示方式，視圖由一個(gè)預(yù)定義的査詢select語(yǔ)句飢成:2. 視圖的特點(diǎn)。視圖屮的數(shù)據(jù)并不誠(chéng)f視圖本:身，而足屈f基本的表，對(duì)視圖可以像衣一樣進(jìn)行insert, upd