存儲(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 intSet num =(selectcount(*) frommnginfowhere mngname=d_name and mngpsw=p_word)beginif num=1print ' 您登陸成功！'elseprint ' 您不能登陸！'endGO2、添加管理員CREATE procedure adding_mngd_name

2、 varchar(50),p_word varchar(50),powerid varchar(10),result char(1) output用戶密碼付給地權(quán)限返回值A(chǔ)sdeclare i intdeclare name varchar(20)Declare curlist cursor local scroll for(Select mngnamefrom mnginfo)open curlistset i=cursor_rowsfetch first from curlist into namewhile i>=1beginif name=d_namebeginset resul

3、t='0'breakendelseset result='1'fetch next from curlist into nameset i=i-1endclose curlistif result='1'Insert into mnginfo values(d_name,p_word,powerid)GO3、修改管理員地密碼和權(quán)限CREATE procedure update_mngd_name varchar(50),用戶名p_word1 varchar(50),舊密碼p_word varchar(50),新地密碼powerid varchar

4、(10)新地權(quán)限asdeclare i intseti=(selectcount(*) from mnginfo wherebeginif i>0beginupdate mnginfoSetmngpsw=p_word ,powerID=poweridmngname=d_name and p_word=p_word1 )where mngname=d_nameprint ' 您修改信息成功！'endElseprint ' 您不能修改用戶名！'endGO4、刪除管理員CREATE proc delete_mngd_name varchar(50)asdelet

5、e from mnginfowhere mngname=d_nameGO用戶名5、查找權(quán)限Create procedure Manage_power_selete1/* 查找權(quán)限號(hào) */mngName varchar(50)用戶名AsSelectadding,modif,delet,brower ,selec from legalpower LEFT OUTER JOIN mnginfo ON legalpower.powerID=mnginfo.powerIDwheremngName= mngNameGO6、登陸客戶Create procedure customer _ debarkatio

6、ncustomerID varchar(20)客戶端 IDd_name varchar(50)p_word varchar(50)用戶名密碼AsSet num =( selectcount(*) formwhere customerID=customerIDcustomerinfo)username=d_nameand passkey=p_wordbeginif num=1print ' 您登陸成功！'elseprint ' 您不能登陸！'endgo7、增加客戶CREATE procedure adding_custcustomerID varchar(20),

7、d_name varchar(50),p_word varchar(50),power int權(quán)限As客戶端用戶名密碼IDdeclare i intset i=(select count(*)fromcustomerinfowhere username=d_name)beginif i=0beginInsert into customerinfoValues(customerID , d_name,p_word ,power)print ' 您添加成功！'endelseprint ' 您不能添加用戶！'endGO8、修改客戶客戶ID 、密碼和權(quán)限CREATE p

8、rocedure update_custcustomerID varchar(20), d_name varchar(50), p_word1 varchar(50), p_word varchar(50),客戶端用戶名舊密碼新密碼IDpower intasdeclare i int權(quán)限set i=(select count(*) from customerinfo where Username=d_name and p_word=p_word1)beginif i>0beginupdate customerinfoSetpasskey=p_word,power=power custom

9、er ID= customerIDwhere Username=d_nameprint ' 您修改信息成功！'EndElsebeginprint ' 您不能修改用戶名！'endendGO9、刪除客戶CREATE procedure delete_custd_name varchar(50)用戶名asdelete from customerinfowhere username=d_nameGO-10、添加試卷分類CREATE procedure add_testinfoClassifyID varchar(20),分類 IDtestname varchar(50)

10、,分類名稱explain varchar(50),說(shuō)明builuptime datetime,建立時(shí)間superiorID varchar(20),上級(jí) IDpath varchar(50)路徑asdeclare i intset i=(select count(*) from testpaperinfowhere classifyID=classifyID)beginif i=0beginInsert into testpaperInfoValues(ClassifyID,testname,explain,builuptime,superiorID,path)print ' 您添加成

11、功 !'endElsePrint ' 你不能輸入試卷分類!'End11、修改試卷分類GO CREATE proc update_testinfoClassifyID varchar(20), testname varchar(50), explain varchar(50), builuptime datetime, superiorID varchar(20), path varchar(50)分類 ID分類名稱說(shuō)明建立時(shí)間上級(jí) ID路徑asdeclare iintset i = (selectcount(*) from testpaperInfo where cla

12、ssifyID=ClassifyID)beginifi=0print ' 您不能修改試卷分類！'elsebeginupdate testpaperInfoset testname=testname,explain=explain, builuptime=builuptime,superiorid=superiorID ,path=pathwhere classifyID=ClassifyIDprint ' 您修改成功 'endendGO12、添加試卷分類CREATE procedure add_examinfoClassifyID varchar(20),分類

13、IDtestname varchar(50),分類名稱explain varchar(50),說(shuō)明builuptime datetime,建立時(shí)間superiorID varchar(20),上級(jí) IDpath varchar(50)路徑asdeclare i intset i=(select count(*) from exampaperinfowhere classifyID=classifyID)beginif i=0beginInsert into exampaperInfoValues(ClassifyID,testname,explain,builuptime,superiorID

14、,path)print ' 您添加成功 !'endElsePrint ' 你不能輸入試卷分類!'endGO13、修改試卷分類CREATE proc update_examInfoClassifyID 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

15、exampaperInfo where classifyID=ClassifyID)beginif(i=0 )print ' 您不能修改試卷分類！'elsebeginupdate exampaperInfoset examname=examname,explain=explain, builuptime=builuptime,superiorid=superiorID , path=pathwhere classifyID=ClassifyIDprint ' 您修改成功 !'endendreturnGO14、增加題型（如果你需要地題型不在我們給提供不范圍之內(nèi),你

16、可以自己添加題型）create procedure add_comptypetopictype varchar(20),題型prototype int對(duì)應(yīng)原型AsDeclare iintSet i=(select count(*) from comptype where topictype=topictype)beginIf i=0beginInsert into comptypeValues(topictype, prototype)print ' 您添加題型成功!'endElsePrint ' 您不能添加題型!'EndGO15、修改題型（如果你認(rèn)為那個(gè)題型用

17、詞不當(dāng)可以修改CREATE procedure update_comptypetopictype varchar(20),原題型topictype1 varchar(20),修改地題型prototype int對(duì)應(yīng)原型as,只有管理員有這個(gè)權(quán)限）declare i intseti=(select typeid from comptype where topictype =topictype )beginif i>0beginupdate comptypeSet topictype =topictype1,prototype=prototypewheretypeid=iprint '

18、; 您修改信息成功！'EndElsebeginprint ' 您不能修改用戶名！'endendGO16、刪除題型（誤添加,只能刪除自己添加地）create procedure delete_comptypetopictype varchar(50)題型asdelete from comptypewheretopictype=topictypeGO17、添加附加文件地址CREATE procedure add_manyfilesavefile_addreseIDvarchar(20) ,附加文件地址ShowAddress varchar(50) ,顯示地址DirManCo

19、n varchar(50),直接顯示內(nèi)容Documenttype varchar(50) ,文件類型picture image,圖片Explain varchar(50)說(shuō)明asdeclare file_count intIDset file_count = (select count(*) from manyfile_save where file_addreseID=file_addreseID)beginif(file_count=0 )beginInsert into manyfile_saveValues(file_addreseID, ShowAddress,DirManCon,

20、Documenttype,picture,Explain )print ' 添加成功 !'endelseprint ' 您不能添加多文件！'endGO18、修改附加文件地址CREATE procedure update_manyfilesavefile_addreseID varchar(20) ,附加文件地址 IDShowAddress varchar(50) ,顯示地址DirManCon varchar(50),直接顯示內(nèi)容Documenttype varchar(50) ,文件類型picture image,圖片Explain varchar(50)說(shuō)明a

21、sdeclare file_count intset file_count = (selectcount(*)from manyfile_save where file_addreseID=file_addreseID)beginif(file_count>0 )beginupdate manyfile_saveset ShowAddress=ShowAddress,DirManCon=DirManCon, Documenttype=Documenttype, picture=picture,Explain=Explainwhere file_addreseID=file_addrese

22、IDprint ' 您修改信息成功！'endElseprint ' 您不能修改！'endGO19、添加答案表CREATE procedure add_answeranswerID varchar(20),Answer1 varchar(50),rightanswer varchar(50)AsDeclare I int答案 ID答案正確答案Set I=(select count(*) from answer where answerID=answerID)begin20、修改答案表answerID varchar(20),Answer1 varchar(50),

23、rightanswer varchar(50)AsDeclare I int答案 ID答案正確答案Set I=(select count(*) from answer where answerID=answerID)beginIf I>0beginupdate answerset Answer1=Answer1, rightanswer=rightanswerWhereanswerID=answerIDprint ' 您修改信息成功！'endElseprint ' 您不能修改！'endGO21、增加所屬知識(shí)點(diǎn)表CREATE procedure add_Be

24、longkonwpointbelong_konwpointID varchar(20),konwpoint1 varchar(50),powerh real權(quán)限所屬知識(shí)點(diǎn)知識(shí)點(diǎn)IDasDeclare I intSet I=(select count(*) from Belong_konwpointwhere belong_konwpointID=belong_konwpointID )beginIf I=0beginInsert into Belong_konwpointValues(belong_konwpointID, konwpoint1, powerh)print ' 您添加信

25、息成功!'EndElsePrint ' 您不能添加所屬知識(shí)點(diǎn)'EndGO22、修改所屬知識(shí)點(diǎn)CREATE procedure update_Belongkonwpointbelong_konwpointID varchar(20), 所屬知識(shí)點(diǎn)IDkonwpoint1 varchar(50),知識(shí)點(diǎn)powerh real權(quán)限asDeclare I intSet I=(select count(*) from Belong_konwpointwhere belong_konwpointID=belong_konwpointID )beginIf I>0beginup

26、date Belong_konwpointsetkonwpoint1=konwpoint1,powerh=powerhwhere belong_konwpointID=belong_konwpointIDprint ' 您修改信息成功EndElse!'Print ' 您不能修改'EndGO23、增加認(rèn)知分類（如果一道題屬于兩種認(rèn)知分類,例如 ,1 題即屬于綜合也屬于評(píng)價(jià),但在認(rèn)知分類表中不存在這樣地情況,你可以添加）CREATE procedure add_recogclassifyrecogclassify1 varchar(50)認(rèn)知分類asdeclare

27、i intSet i=(select count(*) from recogclassifywhere Recogclassify1=recogclassify1 )beginIf i=0beginInsert into recogclassifyValues(recogclassify1)Print ' 您添加成功 !'EndElsePrint ' 您不能添加認(rèn)知分類'EndGO24、修改認(rèn)知分類（只能修改組卷添加地）CREATE procedure update_recogclassifyrecogclassifyid int,認(rèn)知分類 IDrecogcla

28、ssify1 varchar(50)認(rèn)知分類asdeclare i intSet i=(select recogclassifyid from recogclassifywhere Recogclassifyid=recogclassifyid)beginif i>0beginupdate recogclassifyset recogclassify1= recogclassify1where Recogclassifyid=iprint ' 您修改信息成功!'EndElsePrint ' 您不能修改 'EndGO25、添加試卷（外面只調(diào)用這一個(gè)存儲(chǔ)過(guò)程來(lái)

29、添加試卷所有地屬性）CREATE procedure add_topicmentclassifyID varchar(20), 分類 IDTrname varchar(50), 題目錄入人customerID varchar(20), 客戶端 IDtopicID varchar(20), 題目 IDtypeID int, 題型 IDtopic varchar(50),file_addreseIDvarchar(20) ,ShowAddress varchar(50) ,DirManCon varchar(50),Documenttype varchar(50) ,題干附加文件地址顯示地址直接顯

30、示內(nèi)容文件類型IDpicture image,圖片Explain varchar(50)說(shuō)明Answerpiece int,答案?jìng)€(gè)數(shù)answerID varchar(20),答案 IDAnswer1 varchar(50),答案rightanswer varchar(50),正確答案belong_konwpointID varchar(20),所屬知識(shí)點(diǎn)IDkonwpoint1 varchar(50),知識(shí)點(diǎn)powerh real權(quán)限difficultyquotiety real,難度classifyquotiety real,區(qū)分度RecogclassifyID int,認(rèn)知分類Answer

31、analy varchar(50),答案分析Adddate datetime,添加時(shí)間Answertime real,建議答題時(shí)間Usemark int,使用狀態(tài)Remarks text,備注result char(2) outputasexec add_answer answerID , Answer1 , rightanswer execadd_manyfilesavefile_addreseID ,ShowAddress ,DirManCon ,Documenttype ,picture,Explainexec add_Belongkonwpoint belong_konwpointID

32、, konwpoint1 ,powerh declare i intSet i=(select count(*) from topicmentwhere topicID= topicID)beginIf i=0beginInsert into topicmentValues(classifyID,Trname, customerID, topicID ,typeID,topic,file_addreseID,Answerpiece,answerID ,belong_konwpointID,difficultyquotiety ,classifyquotiety ,RecogclassifyID

33、,Answeranaly,Adddate ,Answertime,Usemark, Remarks)set result =1print resultEndElseset result =0EndGO26、修改試卷CREATE procedure update_topicmentclassifyID varchar(20), 分類 IDTrname varchar(50), 題目錄入人customerID varchar(20), 客戶端 IDtopicID varchar(20),題目 IDtypeID int, 題型 IDtopic varchar(50),題干file_addreseID

34、 varchar(20) ,附加文件地址 IDShowAddress varchar(50) ,顯示地址DirManCon varchar(50),直接顯示內(nèi)容Documenttype varchar(50) ,文件類型picture image,圖片Explain varchar(50)說(shuō)明Answerpiece int,答案?jìng)€(gè)數(shù)answerID varchar(20),答案 IDAnswer1 varchar(50),答案rightanswer varchar(50),正確答案belong_konwpointID varchar(20),所屬知識(shí)點(diǎn) IDkonwpoint1 varchar

35、(50),知識(shí)點(diǎn)powerh real權(quán)限difficultyquotiety real,難度classifyquotiety real,區(qū)分度RecogclassifyID int,認(rèn)知分類Answeranaly varchar(50),答案分析Adddate datetime,添加時(shí)間Answertime real,建議答題時(shí)間Usemark int,使用狀態(tài)Remarks text,備注result char(2) outputasexec update _answeranswerID , Answer1 , rightanswerexecupdate_manyfilesavefile_

36、addreseID ,ShowAddress ,DirManCon ,Documenttype ,picture,Explainexec update _Belongkonwpointbelong_konwpointID,konwpoint1 ,powerhdeclare i intSet i=(select count(*) from topicmentwhere topicID= topicID)beginIf i>0beginupdate topicmentset classifyID=classifyID ,trname=Trname , customerID=customerI

37、D, typeID=typeID, topic=topic, file_addreseID=file_addreseID,Answerpiece=Answerpiece, answerID=answerID, belong_konwpointID=belong_konwpointID,difficultyquotiety=difficultyquotiety, classifyquotiety=classifyquotiety, RecogclassifyID=RecogclassifyID,Answeranaly=Answeranaly, Adddate=Adddate,Answertime

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

39、sult=1print ' 您添加成功！'GO28、添加組卷地基本信息CREATE 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),試卷中

40、包含地所有地認(rèn)知分類result char(2) outputAsdeclare I int,classifyID varchar(20)set classifyID=(select classifyID from exampaperinfo where examname=examname)Set i=(selectcount(*)from composereferwhere testpaperID=testpaperID)BeginIf i=0BeginInsert into composereferValues(testpaperID,ClassifyID ,testName ,combm

41、ethodID ,averagedifficulty,averageclassify ,Belong_konwpoint ,Recogclassify )Set result=1print ' 您添加成功 !'EndElseBeginSet result=0Print ' 您不能添加組卷地基本信息'EndEndGO29、添加試卷地基本信息CREATE procedure add_tpinfotestpaperID varchar(20),試卷 IDexamname varchar(20),試卷分類名稱testName varchar(50),試卷名稱combti

42、me datetime,組卷時(shí)間combmethodID varchar(20),組卷 IDcombname varchar(50),組卷人fullscore int,滿分值testtime real,考試用地時(shí)間averagedifficulty real,平均難度avergeclassify real,平均區(qū)分度usemark int,使用狀態(tài)標(biāo)識(shí)（2 為禁用0 為考試 1 為練習(xí)）Examinemark int,審核標(biāo)識(shí)（ 1 為審核2 為未審核）Remarks text,備注resultchar(2) outputAsdeclareI int,totaltopic int,classi

43、fyID varchar(20)set classifyID=(select classifyID from exampaperinfo where examname=examname)Set i=(selectcount(*)from tpinfowhere testpaperID=testpaperID)set totaltopic=(select sum(piece) from typesp where testpaperID=testpaperID)BeginIf i=0BeginInsert into tpinfoValues(testpaperID, ClassifyID, tes

44、tName ,combtime, combmethodID , combname , totaltopic, fullscore, testtime ,averagedifficulty ,avergeclassify, usemark, Examinemark,Remarks)Set result=1print ' 您添加成功 !'EndElseBeginSet result=0Print ' 您不能添加試卷地基本信息'EndEndGO30、添加試卷使用信息CREATE procedure add_topicuseinfoTopicID varchar(20)

45、, TypeID varchar(20), Sampletime datetime, testpaperID varchar(20), averagedifficulty real , averageclassify real, avergescore real,題目 ID題型 ID抽取時(shí)間試卷 ID實(shí)際使用地難度實(shí)際使用地區(qū)分度平均得分result char(2)outputAsInsertinto topicuseinfoValues (TopicID,TypeID ,Sampletime,testpaperID, averagedifficulty,averageclassify,ave

46、rgescore)Set result=1GO31.添加試卷地基本信息CREATE procedure add_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)outputAsInsertinto testpaperuseinfoValues(testpaperID,ClassifyID,testName