系统存储荼过程写法详细分析

系统存储荼过程写法详细分析

''存储过程的定义和使用一、启动SQL查询分析器通过菜单[新建查询]，启动SQL查询分析器。系统将打开“连接到服务器”窗口n在服务器名称（指定SQLServer服务器）栏中，可选择需要连接的SQLServer服务器。n在身份验证中，可选择登录SQLServer服务器的验证方式。如果实验用机的SQLServer安装时设定了混合验证方式，则“SQLServer身份验证”方式可选。选择该方式，其登录名默认为：sa，密码默认是：空。点击按钮“确认”，SQL查询分析器将连接到指定的SQLServer服务器，并打开SQL查询分析器的界面SQL查询分析器的界面：n''SQL语句作用的当前数据库对象浏览窗口SQL语句输入窗口n左侧窗口是“对象浏览器”窗口；n右侧窗口是SQL语句的输入和调试窗口，可在该窗口中直接输入SQL语句或打开一个SQL脚本文件（后缀名为*.sql的文件）；n上方的数据库列表显示了当前SQL语句作用的数据库。二、创建数据库使用SQL语句创建实验数据库SCMIS，并创建3张表：STUDENT、COURSE和SC，输入数据。以下内容均使用SQL语句在SQL查询分析器中执行运行。（a）在SQLSERVER2012中，在查询分析器中使用T-SQL语句：CREATEDATABASESCMISON(NAME='SCMIS',FILENAME='E:SCMIS.MDF',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)LOGON(NAME='SCMIS_Log',FILENAME='e:SCMISn''_Log.ldf',SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)(b)在SQLSERVER中查询分析器的编辑窗口中用下列SQL语句:/*建表*/useSCMISCREATETABLE[dbo].[COURSE]([CNO][char](2)PRIMARYKEY,[CNAME][varchar](16)NOTNULL,[CREDIT][int],[C_TOTAL][int],[CPNO][char](2))ON[PRIMARY]GOCREATETABLE[dbo].[STUDENT]([SNO][char](5)PRIMARYKEY,[SNAME][char](8)NOTNULL,[SSEX][char](2)CONSTRAINTchkSsexCHECK(ssexIN('男','女')),[SDEPT][varchar](10),[SMAJOR][varchar](10),[SAGE][int]CONSTRAINTchkSageCHECK(sage>=16andsage<=40),[NATIVE][varchar](30),[PHONE][char](7))ON[PRIMARY]CREATETABLE[dbo].[SC]([SNO][char](5)NOTNULL,[CNO][char](2)NOTNULL,[SCORE][int],CONSTRAINTpkSCPRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESstudent(sno),FOREIGNKEY(cno)REFERENCEScourse(cno))ON[PRIMARY]GO(c)在SQLSERVER中查询分析器的编辑窗口中使用下列SQL语句插入数据:/*数据输入*/INSERTINTOSTUDENT(Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native)VALUES('14001','李勇','男',20,'CS','软件工程',2681032,'福建')INSERTINTOSTUDENT(Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native)VALUES('14002','刘晨','女',19,'IS','信息管理',2682437,'福建')INSERTINTOSTUDENT(Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native)VALUES('14003','王名','女',18,'MA','计算数学',2680050,'山东')INSERTINTOSTUDENT(Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native)VALUES('14004','张立','男',19,'IS','电子商务',2681032,'湖北')INSERTINTOCOURSE(Cno,Cname,Cpno,Credit,C_Total)n''VALUES('C1','数据库','C5',3,3)INSERTINTOCOURSE(Cno,Cname,Credit,C_Total)VALUES('C2','数学',3,2)INSERTINTOCOURSE(Cno,Cname,Cpno,Credit,C_Total)VALUES('C3','信息系统','C1',4,2)INSERTINTOCOURSE(Cno,Cname,Credit,C_Total)VALUES('C4','操作系统',2,2)INSERTINTOCOURSE(Cno,Cname,Cpno,Credit,C_Total)VALUES('C5','数据结构','C6',3,1)INSERTINTOCOURSE(Cno,Cname,Credit,C_Total)VALUES('C6','C语言',2,1)INSERTINTOSC(Sno,Cno,Score)VALUES('14001','C1',92)INSERTINTOSC(Sno,Cno,Score)VALUES('14001','C2',85)INSERTINTOSC(Sno,Cno,Score)VALUES('14001','C3',88)INSERTINTOSC(Sno,Cno,Score)VALUES('14002','C2',90)INSERTINTOSC(Sno,Cno,Score)VALUES('14002','C3',80)INSERTINTOSC(Sno,Cno,Score)VALUES('14003','C1',76)INSERTINTOSC(Sno,Cno,Score)VALUES('14003','C4',82)INSERTINTOSC(Sno,Cno,Score)VALUES('14004','C5',75)INSERTINTOSC(Sno,Cno,Score)VALUES('14004','C6',77)INSERTINTOSC(Sno,Cno,Score)VALUES('14004','C1',55)GO三.存储过程的创建和使用1.创建一个存储过程course_proc1,显示每门课程选修学生信息及其成绩。方法一：使用ManagementStudio选择数据库SCMIS->可编程性->“存储过程”,在其上单击右键->选择“新建存储过程…”，打开“存储过程属性”对话框，输入存储过程正文。n''方法二：在查询分析器中输入程序代码如下，然后运行。USESCMISGOCREATEPROCdbo.course_proc1ASSELECTS.Sname,S.Sdept,C.Cname,SC.ScoreFROMStudentS,CourseC,SCWHERES.Sno=SC.SnoANDC.Cno=SC.CnoORDERBYC.CnameDESCGO执行存储过程：用T-SQL语句方式，EXEC可省略。EXECcourse_proc1n''1.创建带默认参数的存储过程，查看指定系别的学生信息。系别默认为“IS”。USESCMISGOCREATEPROCdbo.stu_proc1@s_depvarchar(10)=’IS’ASSELECT*FROMStudentWHEREsdept=@S_depGO执行存储过程的另一种方法：用界面方式。右击要执行的存储过程stu_proc1，选择“执行存储过程…”命令，在弹出的【执行过程】窗口中，在【值】输入参数值：CS，点击“确定”。该存储过程在调用时即使不指定参数值，也可以返回一个默认的结果。n''小练习：该存储过程在调用时即使不指定参数值，也可以返回一个默认的结果。试着不指定参数执行存储过程，观察运行结果。练习：用T-SQL语句执行存储过程dbo.stu_proc1，查看’CS’系的学生信息。1.向sc表添加一条记录的存储过程sc_add：在查询分析器中输入程序代码如下，然后运行。USESCMISGOCREATEPROCsc_add(@ssnochar(5),@ccnochar(2),@sscoreint)ASBEGINn''INSERTINTOscVALUES(@ssno,@ccno,@sscore)ENDRETURNGO1.修改sc表中一条记录的存储过程sc_update：在查询分析器中输入程序代码如下，然后运行。USESCMISGOCREATEPROCsc_update(@ssno_oldchar(5),@ssno_newchar(5),@ccno_oldchar(2),@ccno_newchar(2),@sscoreint)ASBEGINUPDATEscSETsno=@ssno_new,cno=@ccno_new,score=@sscoreWHEREsno=@ssno_oldandcno=@ccno_oldENDRETURNGOn''1.删除sc表中一条记录的存储过程sc_delete：在查询分析器中输入程序代码如下，然后运行。USESCMISGOCREATEPROCsc_delete(@ssnochar(5),@ccnochar(2))ASBEGINDELETEFROMscWHEREsno=@ssnoandcno=@ccnoENDRETURNGO2.存储过程的调用n''USESCMISSELECT*FROMSC--执行前EXECsc_add‘14003’,’C2’,86SELECT*FROMSC--执行后GoEXECsc_update‘14003’,’14004’,’C2’,’C4’,78SELECT*FROMSC--执行后GoEXECsc_delete‘14004’,’C4’SELECT*FROMSC--执行后Gon''试用不同的参数传递方式执行上述存储过程。1.创建一个存储过程course_proc2,如果指定学生有选课，则返回1;否则，返回0。在查询分析器中输入程序代码如下，然后运行。USESCMISGOCREATEPROCdbo.course_proc2(@ins_snochar(5),@sc_countintOUTPUT)--@sc_count保存该学生的选课数。ASIFEXISTS(SELECT*FROMSCWHERESno=@ins_sno)BEGINSELECT@sc_count=COUNT(*)FROMSCWHERESno=@ins_snoRETURN(1)ENDELSERETURN(0)GOn''执行，USESCMISGODECLARE@stu_nochar(5),@msgvarchar(60),@return_statusint,@numcourseintSET@stu_no='14004'EXEC@return_status=course_proc2@stu_no,@numcourseOUTPUTIF@return_status=1BEGINSELECT@msg='指定的学号'+@stu_no+'一共选修了'+STR(@numcourse,2)+'门课程'PRINT@msgENDELSEBEGINSELECT@msg='指定的学号'+@stu_no+'没有选修课程!'PRINT@msgENDGOn''点击“存盘”按钮，将执行语句保存为“成绩查询.sql”文件。8.扩展的存储过程：执行xp_msver，查看系统版本信息。在查询分析器中输入程序代码如下，然后运行。EXECxp_msver小练习：执行xp_logininfo扩展存储过程，了解账户、账户类型、权限级别、访问路径等系统信息。