存儲(chǔ)過(guò)程的程序設(shè)計(jì)

1、數(shù)據(jù)庫(kù)地存儲(chǔ)過(guò)程地程序設(shè)計(jì)（ 68 個(gè)）1、登陸管理員CREATE procedure mngdebarkationd_name varchar(50),p_word varchar(50)用戶名密碼AsDeclare num intmnginfoSet num =(select count(*) from where mngname=d_name and mngpsw=p_word) beginif num=1print 您登陸成功！ ！elseprint 您不能登陸！ ！用戶密碼 付給地權(quán)限 返回值end GO 2、添加管理員 CREATE procedure adding_mng d_n

2、ame varchar(50), p_word varchar(50), powerid varchar(10), result char(1) outputAs declare i int declare name varchar(20) Declare curlist cursor local scroll for( Select mngname from mnginfo) open curlist set i=cursor_rows fetch first from curlist into name while i=1 begin if name=d_name begin set re

3、sult=0 break end else set result=1 fetch next from curlist into name set i=i-1endclose curlistif result=1Insert into mn gi nfo values(d _n ame,p_word,powerid) GO3、修改管理員地密碼和權(quán)限CREATE procedure update_ mngd_n ame varchar(50),用戶名p_word1 varchar(50),舊密碼p_word varchar(50),新地密碼powerid varchar(10)新地權(quán)限asdecl

4、are i intset i=(selectcoun t(*)from mnginfop word=p word1 )beginif i0beginupdate mnginfoSetmn gpsw=p_word ,powerID=poweridwhere mngn ame=d_ nameprint您修改信息成功！ ！endwhere mngn ame=d_ nameandElseprint 您不能修改用戶名！endGO4、刪除管理員CREATE proc delete_ mngd_n ame varchar(50)用戶名asdelete from mn gi nfowhere mngn ame

5、=d_ nameGO5、查找權(quán)限Create procedure Manage_power_selete1/* 查找權(quán)限號(hào) */mn gName varchar(50)用戶名AsSelect addi ng,modif,delet,brower ,selec from legalpowerON legalpower.powerlD=mngin fo.powerIDwheremn gName= mn gNameGO6登陸客戶Create procedure customer _ debarkatio nLEFT OUTER JOIN mn gi nfocustomerlD varchar(20)

6、 d_n ame varchar(50) p_word varchar(50) AsSet n um =(selectcoun t(*)客戶端ID 用戶名密碼formcustomeri nfo)wherecustomerlD=customerlDuser name=d_ nameand passkey=p wordbegin ifprint您登陸成功！ ！ elseprint您不能登陸！ !endnum=1go7、增加客戶CREATE procedure addi ng_cust customerlD varchar(20), d_n ame varchar(50), p_word varch

7、ar(50), power int As客戶端ID用戶名密碼權(quán)限declare i intfromcustomeri nfoset i=(select coun t(*)where user name=d_ name) beginif i=0 beginInsert into customeri nfoValues(customerlD , d_n ame,p_word ,power) print您添加成功！ ！endelseprint您不能添加用戶！endGO8、修改客戶客戶ID、密碼和權(quán)限CREATE procedure update_cust customerID varchar(20)

8、,客戶端 IDd_n ame varchar(50),用戶名p_word1 varchar(50),舊密碼新密碼權(quán)限from customerinfowhere Username=d_name andp_word varchar(50),power intasdeclare i intset i=(select coun t(*)p_word=p_word1)beginif i0beginupdate customeri nfoSet passkey=p_word,power=power customer ID= customer ID where Usern ame=d_ nameprint

9、 您修改信息成功！ ！EndElsebeginprint 您不能修改用戶名!endendGO9、刪除客戶CREATE procedure delete_custd_n ame varchar(50)用戶名asdelete from customeri nfowhere user name=d_ nameGO 10、添加試題分類CREATE procedure add_testi nfoClassifylD varchar(20),分類 IDtest name varchar(50),分類名稱explai n varchar(50),說(shuō)明builuptime datetime,建立時(shí)間super

10、iorID varchar(20),上級(jí) IDpath varchar(50)路徑asdeclare i intset i=(select coun t(*)from testpaperi nfowhere classifylD=classifylD) begin if i=0beginInsert into testpaperI nfoValues(ClassifyID,testname,explain,builuptime,superiorID,path) print 您添加成功 !endElsePrint 你不能輸入試題分類 !End11、修改試題分類GO CREATE proc upd

11、ate_testinfoClassifyID varchar(20), testname varchar(50),explain varchar(50),builuptime datetime, superiorID varchar(20), path varchar(50)分類 ID 分類名稱 說(shuō)明 建立時(shí)間 上級(jí) ID 路徑asdeclare i intset i = (select count(*) from testpaperInfo where classifyID=ClassifyID)beginif i=0print 您不能修改試題分類！ elsebeginupdate test

12、paperInfoset testname=testname,explain=explain, builuptime=builuptime,superiorid=superiorID ,path=pathwhere classifyID=ClassifyIDprint 您修改成功 endendGO12、添加試卷分類CREATE procedure add_examinfoClassifyID varchar(20),testname varchar(50), explain varchar(50), builuptime datetime,superiorID varchar(20),path

13、 varchar(50) as分類 ID 分類名稱說(shuō)明 建立時(shí)間 上級(jí) ID 路徑declare i intset i=(select count(*) from exampaperinfo where classifyID=classifyID) begin if i=0beginInsert into exampaperInfo Values(ClassifyID,testname,explain,builuptime,superiorID,path) print 您添加成功 ! endElsePrint 你不能輸入試卷分類 !end GO13、修改試卷分類CREATE proc upda

14、te_examInfo ClassifyID varchar(20),分類 IDtestname varchar(50),分類名稱explain varchar(50), 說(shuō)明 builuptime datetime,建立時(shí)間superiorID varchar(20),上級(jí) IDpath varchar(50)路徑asdeclare i intset i = (select count(*) from exampaperInfo where classifyID=ClassifyID) begin if(i=0 ) print 您不能修改試卷分類！ else begin update exa

15、mpaperInfo set examname=examname,explain=explain, builuptime=builuptime,superiorid=superiorID , path=path,你可以自己添加題型）where classifyID=ClassifyID print 您修改成功 ! end end return GO14、增加題型(如果你需要地題型不在我們給提供不范圍之內(nèi) create procedure add_comptypetopictype varchar(20), 題型prototype int 對(duì)應(yīng)原型AsDeclare i intSet i=(se

16、lect count(*) from comptype where topictype=topictype) beginIf i=0beginInsert into comptypeValues(topictype, prototype)print 您添加題型成功 !endElsePrint 您不能添加題型 !EndGO15、修改題型(如果你認(rèn)為那個(gè)題型用詞不當(dāng)可以修改,只有管理員有這個(gè)權(quán)限)CREATE procedure update_comptypetopictype varchar(20), 原題型topictype1 varchar(20), 修改地題型prototype int對(duì)應(yīng)

17、原型as declare i intset i=(select typeid from comptype where topictype =topictype ) beginif i0beginupdate comptypeSet topictype =topictype1,prototype=prototypewhere typeid=iprint 您修改信息成功！ ！EndElsebeginprint 您不能修改用戶名！ endendGO16、刪除題型(誤添加 ,只能刪除自己添加地) create procedure delete_comptype topictype varchar(50

18、) 題型 asdelete from comptypewhere topictype=topictypeGO17、添加附加文件地址CREATE procedure add_manyfilesavefile_addreseID varchar(20) , ShowAddress varchar(50) , DirManCon varchar(50), Documenttype varchar(50) , picture image,Explain varchar(50)附加文件地址 ID 顯示地址 直接顯示內(nèi)容 文件類型 圖片 說(shuō)明asdeclare file_count intset file

19、_count = (select count(*) from manyfile_save where file_addreseID=file_addreseID) beginif(file_count=0 )beginInsert into manyfile_saveValues(file_addreseID, ShowAddress,DirManCon, Documenttype,picture,Explain ) print 添加成功 !end elseprint 您不能添加多文件！ endGO18、修改附加文件地址CREATE procedure update_manyfilesavef

20、ile_addreseID varchar(20) , ShowAddress varchar(50) , DirManCon varchar(50), Documenttype varchar(50) , picture image, Explain varchar(50) as declare file_count int set file_count = (select file_addreseID=file_addreseID) begin if(file_count0 ) begin update manyfile_save附加文件地址 ID顯示地址直接顯示內(nèi)容文件類型圖片說(shuō)明cou

21、nt(*) from manyfile_save wheresetShowAddress=ShowAddress,DirManCon=DirManCon,Documenttype=Documenttype, picture=picture,Explain=Explain where file_addreseID=file_addreseIDprint 您修改信息成功！ ！ endElseprint 您不能修改！ ！ endGO19、添加答案表CREATE procedure add_answer answerID varchar(20), Answer1 varchar(50), righta

22、nswer varchar(50)AsDeclare I intSet I=(select count(*) from answer beginIf I=0答案 ID 答案 正確答案where answerID=answerID)Insert into answerValues(answerID, Answer1, rightanswer )ElsePrint 不能添加答案 EndGO20、修改答案表CREATE procedure update_answer answerID varchar(20), Answer1 varchar(50), rightanswer varchar(50)A

23、sDeclare I intSet I=(select count(*) from answer答案 ID 答案 正確答案where answerID=answerID)beginIf I0beginupdate answerset Answer1=Answer1, rightanswer=rightanswerWhere answerID=answerIDprint 您修改信息成功！ ！ endElseprint 您不能修改！ ！ endGO21、增加所屬知識(shí)點(diǎn)表CREATE procedure add_Belongkonwpoint belong_konwpointID varchar(2

24、0),所屬知識(shí)點(diǎn) IDkonwpoint1 varchar(50),知識(shí)點(diǎn)asDeclare I intSet I=(select count(*) from Belong_konwpoint where belong_konwpointID=belong_konwpointID ) beginIf I=0beginInsert into Belong_konwpointValues(belong_konwpointID, konwpoint1, powerh)print 您添加信息成功 !EndElsePrint 您不能添加所屬知識(shí)點(diǎn) EndGO22、修改所屬知識(shí)點(diǎn) CREATE proce

25、dure update_Belongkonwpoint belong_konwpointID varchar(20),所屬知識(shí)點(diǎn) IDkonwpoint1 varchar(50),知識(shí)點(diǎn)powerh real權(quán)限asDeclare I intSet I=(select count(*) from Belong_konwpoint where belong_konwpointID=belong_konwpointID ) beginIf I0beginupdate Belong_konwpointset konwpoint1=konwpoint1,powerh=powerhwhere belon

26、g_konwpointID=belong_konwpointIDprint 您修改信息成功 !EndElsePrint 您不能修改 End,但在認(rèn)GO23、增加認(rèn)知分類(如果一道題屬于兩種認(rèn)知分類,例如,1 題即屬于綜合也屬于評(píng)價(jià)知分類表中不存在這樣地情況 ,你可以添加)CREATE procedure add_recogclassify recogclassify1 varchar(50) 認(rèn)知分類 as declare i intSet i=(select count(*) from recogclassifywhere Recogclassify1=recogclassify1 ) be

27、ginIf i=0beginInsert into recogclassifyValues(recogclassify1)Print 您添加成功 !EndElsePrint 您不能添加認(rèn)知分類 EndGO24、修改認(rèn)知分類(只能修改組卷添加地)CREATE procedure update_recogclassify recogclassifyid int, 認(rèn)知分類 ID recogclassify1 varchar(50) 認(rèn)知分類 asdeclare i intSet i=(select recogclassifyid from recogclassify where Recogclas

28、sifyid=recogclassifyid) beginif i0beginupdate recogclassifyset recogclassify1= recogclassify1 where Recogclassifyid=iprint 您修改信息成功 ! EndElsePrint 您不能修改EndGO25、添加試題 （外面只調(diào)用這一個(gè)存儲(chǔ)過(guò)程來(lái)添加試題所有地屬性）CREATE procedure add_topicment classifyID varchar(20), Trname varchar(50), customerID varchar(20), topicID varch

29、ar(20),typeID int,topic varchar(50), file_addreseID varchar(20) , ShowAddress varchar(50) ,分類 ID 題目錄入人 客戶端 ID 題目 ID 題型 ID 題干 附加文件地址 ID 顯示地址DirManCon varchar(50), Documenttype varchar(50) , picture image, Explain varchar(50) Answerpiece int,answerID varchar(20), Answer1 varchar(50),rightanswer varcha

30、r(50), belong_konwpointID varchar(20), konwpoint1 varchar(50), powerh real difficultyquotiety real, classifyquotiety real, RecogclassifyID int, Answeranaly varchar(50), Adddate datetime, Answertime real, Usemark int, Remarks text, result char(2) output as exec add_answer answerID , exec直接顯示內(nèi)容 文件類型 圖

31、片 說(shuō)明 答案?jìng)€(gè)數(shù) 答案 ID答案 正確答案 所屬知識(shí)點(diǎn) ID 知識(shí)點(diǎn) 權(quán)限 難度 區(qū)分度 認(rèn)知分類 答案分析添加時(shí)間 建議答題時(shí)間 使用狀態(tài)備注Answer1 , rightanswerfile_addreseID ,ShowAddress ,DirManCon ,Documenttype exec add_Belongkonwpoint belong_konwpointID, konwpoint1 declare i intadd_manyfilesave ,picture,Explain ,powerhSet i=(select count(*) from topicmentwhere

32、topicID= topicID) beginIf i=0beginInsert into topicmentValues(classifyID,Trname, customerID, topicID , typeID,topic,file_addreseID, Answerpiece,answerID ,belong_konwpointID, difficultyquotiety ,classifyquotiety , RecogclassifyID,Answeranaly,Adddate ,Answertime, Usemark, Remarks)set result =1分類 ID 題目

33、錄入人 客戶端 ID 題目 ID 題型 ID 題干 附加文件地址 ID 顯示地址 直接顯示內(nèi)容 文件類型 圖片 說(shuō)明 答案?jìng)€(gè)數(shù) 答案 ID 答案 正確答案 所屬知識(shí)點(diǎn) ID 知識(shí)點(diǎn) 權(quán)限 難度 區(qū)分度 認(rèn)知分類 答案分析 添加時(shí)間 建議答題時(shí)間 使用狀態(tài)備注print resultEndElseset result =0EndGO26、修改試題CREATE procedure update_topicment classifyID varchar(20), Trname varchar(50), customerID varchar(20), topicID varchar(20), type

34、ID int,topic varchar(50), file_addreseID varchar(20) , ShowAddress varchar(50) , DirManCon varchar(50), Documenttype varchar(50) , picture image, Explain varchar(50) Answerpiece int,answerID varchar(20), Answer1 varchar(50),rightanswer varchar(50), belong_konwpointID varchar(20), konwpoint1 varchar(

35、50), powerh real difficultyquotiety real, classifyquotiety real, RecogclassifyID int, Answeranaly varchar(50), Adddate datetime, Answertime real, Usemark int, Remarks text, result char(2) output asexec update _answer answerID , Answer1 , rightanswerexec update_manyfilesave file_addreseID ,ShowAddres

36、s ,DirManCon ,Documenttype ,picture,Explain exec update _Belongkonwpoint belong_konwpointID, konwpoint1 ,powerh declare i intSet i=(select count(*) from topicmentwhere topicID= topicID)beginIf i0beginupdate topicmentset classifyID=classifyID ,trname=Trname , customerID=customerID, typeID=typeID, top

37、ic=topic, file_addreseID=file_addreseID, Answerpiece=Answerpiece, answerID=answerID, belong_konwpointID=belong_konwpointID, difficultyquotiety=difficultyquotiety,classifyquotiety=classifyquotiety,RecogclassifyID=RecogclassifyID,Answeranaly=Answeranaly, Adddate=Adddate,Answertime=Answertime, Usemark=

38、Usemark, Remarks=Remarkswhere topicID= topicIDset result =1print 您修改成功 !EndElsebeginset result =0print 您不能修改信息 !endEndGO27、添加組卷算法CREATE procedure add_combmethodContents varchar(50), 組卷策略內(nèi)容地簡(jiǎn)介result char(2) outputAsinsert into combmethodValues( Contents)set result=1print 您添加成功！ ！ GO28、添加組卷地基本信息CREATE

39、 procedure add_composerefertestpaperID varchar(20),試卷 IDexamname varchar(20),試卷分類testName varchar(50),試卷名稱combmethodID int,組卷 IDaveragedifficulty real,平均難度averageclassify real,平均區(qū)分度Belong_konwpoint varchar(20),試卷中包含地所有地知識(shí)點(diǎn)Recogclassify varchar(20),試卷中包含地所有地認(rèn)知分類resultchar(2) outputAsdeclare I int,cla

40、ssifyID varchar(20)set classifyID=(select classifyID from exampaperinfo whereexamname=examname)Set i=(select count(*)from composerefer where testpaperID=testpaperID)BeginIf i=0BeginInsert into composereferValues(testpaperID,ClassifyID ,testName ,combmethodID , averagedifficulty,averageclassify ,Belo

41、ng_konwpoint ,Recogclassify )Set result=1print 您添加成功 !EndElseBeginSet result=0Print 您不能添加組卷地基本信息 EndEnd GO29、添加試卷地基本信息CREATE procedure add_tpinfo testpaperID varchar(20), examname varchar(20), testName varchar(50), combtime datetime, combmethodID varchar(20), combname varchar(50), fullscore int, tes

42、ttime real, averagedifficulty real, avergeclassify real, usemark int,Examinemark int, Remarks text, resultchar(2) outputAs試卷 ID 試卷分類名稱 試卷名稱 組卷時(shí)間 組卷 ID 組卷人 滿分值 考試用地時(shí)間 平均難度平均區(qū)分度使用狀態(tài)標(biāo)識(shí)（ 2 為禁用 0 為考試 1 為練習(xí)） 審核標(biāo)識(shí)（ 1 為審核 2 為未審核） 備注declare I int,totaltopic int,classifyID varchar(20)set classifyID=(select cl

43、assifyID from exampaperinfo whereexamname=examname)Set i=(select count(*) from tpinfo where testpaperID=testpaperID)set totaltopic=(select sum(piece) from typesp where testpaperID=testpaperID) BeginIf i=0BeginInsert into tpinfoValues(testpaperID, ClassifyID, testName ,combtime, combmethodID , combna

44、me , totaltopic, fullscore, testtime ,averagedifficulty ,avergeclassify, usemark, Examinemark,Remarks)Set result=1print 您添加成功 !EndElseBeginSet result=0Print 您不能添加試卷地基本信息EndEndGO30、添加試題使用信息題目 ID 題型 ID 抽取時(shí)間 試卷 ID 實(shí)際使用地難度 實(shí)際使用地區(qū)分度平均得分CREATE procedure add_topicuseinfo TopicID varchar(20),TypeID varchar(

45、20), Sampletime datetime, testpaperID varchar(20), averagedifficulty real , averageclassify real, avergescore real,result char(2)outputAsInsert into topicuseinfoValues (TopicID,TypeID ,Sampletime,testpaperID, averagedifficultyaverageclassify,avergescore)Set result=1GO31.添加試卷地基本信息CREATE procedure add

46、_testpaperuseinfotestpaperID varchar(20),試卷 IDClassifyID varchar(20),分類 IDtestName varchar(50),試卷名稱testtime real,考試時(shí)間averagescore real,平均分值factdifficulty real,實(shí)際難度f(wàn)actclassify real,實(shí)際區(qū)分度Usecir int , 使用情況( 1普通考試 ,2 網(wǎng)絡(luò)隨機(jī)抽題) Remarks text, 備注result char(2) outputAsInsert into testpaperuseinfoValues(test

47、paperID,ClassifyID,testName ,testtime ,averagescore ,factdifficulty,factclassify,Usecir ,Remarks)Set result=1GO31.查找組卷策略地內(nèi)容介紹CREATE procedure select_combmethodcombmethodID int 組卷策略簡(jiǎn)單介紹asdeclare i intset i=(select count(*) from combmethod where combmethodID=combmethodID) beginif i0beginselect content

48、s from combmethod where combmethodID=combmethodIDprint 輸入正確 !return(1)end elsebeginprint 輸入有誤 ! return(0) end endGO33.按分類名查找 (子存儲(chǔ)過(guò)程) CREATE procedure select_topic1 testName varchar(50) asdeclare i intset i=(select count(*) from beginif i0beginselect topicID from試題分類名稱testpaperinfo where testName=tes

49、tName)topicment inner join testpaperinfoontopicment.ClassifyID=testpaperinfo.ClassifyIDwhere testpaperinfo.testName=testName print 輸入正確 ! return(1) end else beginprint 輸入有誤 !return(0)end endGO34. 按題型查找CREATE procedure select_topic2topictype varchar(50) asdeclare i intset i=(select count(*) from begi

50、nif i0beginselect topicID from topicment.typeID=comptype.typeID題型comptype where topictype=topictype)topicment inner join comptypeonwhere comptype.topictype=topictype print 輸入正確 !return(1) end elsebeginprint 輸入有誤 !return(0)endendGO35. 按錄入查找CREATE procedure select_topic3trname varchar(50) 錄入人asdeclare

51、 i intset i=(select count(*) from topicment where TRName=TRName) begin if i0 beginselect topicID from topicment where TRName=TRName print 輸入正確 !whereonwherereturn(1)endelsebeginprint 輸入有誤 !return(0)endendGO36. 按認(rèn)知分類查找CREATE procedure select_topic4Recogclassify1 varchar(50) 認(rèn)知分類asdeclare i intset i=(select count(*) from RecogclassifyRecogclassify1=Recogclassify1)beginif i0beginselect topicID from topicment inner join Recogclassify topicment.RecogclassifyID=Recogclassify.RecogclassifyIDwhere Recogclassify.Recogclassify1=Recogclass