存储过程写法优质文档.docx
《存储过程写法优质文档.docx》由会员分享，可在线阅读，更多相关《存储过程写法优质文档.docx（16页珍藏版）》请在冰豆网上搜索。
存储过程写法优质文档
存储过程的定义和使用
一、启动SQL查询分析器
通过菜单[新建查询]，启动SQL查询分析器。
系统将打开“连接到服务器”窗口
⏹在服务器名称（指定SQLServer服务器）栏中，可选择需要连接的SQLServer服务器。
⏹在身份验证中，可选择登录SQLServer服务器的验证方式。
如果实验用机的SQLServer安装时设定了混合验证方式，则“SQLServer身份验证”方式可选。
选择该方式，其登录名默认为：
sa，密码默认是：
空。
点击按钮“确认”，SQL查询分析器将连接到指定的SQLServer服务器，并打开SQL查询分析器的界面
SQL查询分析器的界面：
⏹左侧窗口是“对象浏览器”窗口；
⏹右侧窗口是SQL语句的输入和调试窗口，可在该窗口中直接输入SQL语句或打开一个SQL脚本文件（后缀名为*.sql的文件）；
⏹上方的数据库列表显示了当前SQL语句作用的数据库。
二、创建数据库
使用SQL语句创建实验数据库SCMIS，并创建3张表：
STUDENT、COURSE和SC，输入数据。
以下内容均使用SQL语句在SQL查询分析器中执行运行。
（a）在SQLSERVER2012中，在查询分析器中使用T-SQL语句：
CREATEDATABASESCMIS
ON（NAME='SCMIS',
FILENAME='E:
\SCMIS.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5%）
LOGON
（NAME='SCMIS_Log',
FILENAME='e:
\SCMIS_Log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB）
（b）在SQLSERVER中查询分析器的编辑窗口中用下列SQL语句:
/*建表*/
useSCMIS
CREATETABLE[dbo].[COURSE]（
[CNO][char]
（2）PRIMARYKEY,
[CNAME][varchar]（16）NOTNULL,
[CREDIT][int],
[C_TOTAL][int],
[CPNO][char]
（2）
）ON[PRIMARY]
GO
CREATETABLE[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）
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->可编程性->“存储过程”,在其上单击右键->选择“新建存储过程…”，打开“存储过程属性”对话框，输入存储过程正文。
方法二：
在查询分析器中输入程序代码如下，然后运行。
USESCMIS
GO
CREATEPROCdbo.course_proc1
AS
SELECTS.Sname,S.Sdept,C.Cname,SC.Score
FROMStudentS,CourseC,SC
WHERES.Sno=SC.SnoANDC.Cno=SC.Cno
ORDERBYC.CnameDESC
GO
执行存储过程：
用T-SQL语句方式，EXEC可省略。
EXECcourse_proc1
2.创建带默认参数的存储过程，查看指定系别的学生信息。
系别默认为“IS”。
USESCMIS
GO
CREATEPROCdbo.stu_proc1@s_depvarchar（10）=’IS’
AS
SELECT*FROMStudentWHEREsdept=@S_dep
GO
执行存储过程的另一种方法：
用界面方式。
右击要执行的存储过程stu_proc1，选择“执行存储过程…”命令，在弹出的【执行过程】窗口中，在【值】输入参数值：
CS，点击“确定”。
该存储过程在调用时即使不指定参数值，也可以返回一个默认的结果。
小练习：
该存储过程在调用时即使不指定参数值，也可以返回一个默认的结果。
试着不指定参数执行存储过程，观察运行结果。
练习：
用T-SQL语句执行存储过程dbo.stu_proc1，查看’CS’系的学生信息。
3.向sc表添加一条记录的存储过程sc_add：
在查询分析器中输入程序代码如下，然后运行。
USESCMIS
GO
CREATEPROCsc_add（@ssnochar（5）,@ccnochar
（2）,@sscoreint）
AS
BEGIN
INSERTINTOscVALUES（@ssno,@ccno,@sscore）
END
RETURN
GO
4.修改sc表中一条记录的存储过程sc_update：
在查询分析器中输入程序代码如下，然后运行。
USESCMIS
GO
CREATEPROCsc_update（@ssno_oldchar（5）,@ssno_newchar（5）,@ccno_oldchar
（2）,@ccno_newchar
（2）,@sscoreint）
AS
BEGIN
UPDATEsc
SETsno=@ssno_new,cno=@ccno_new,score=@sscore
WHEREsno=@ssno_oldandcno=@ccno_old
END
RETURN
GO
5.删除sc表中一条记录的存储过程sc_delete：
在查询分析器中输入程序代码如下，然后运行。
USESCMIS
GO
CREATEPROCsc_delete（@ssnochar（5）,@ccnochar
（2））
AS
BEGIN
DELETEFROMscWHEREsno=@ssnoandcno=@ccno
END
RETURN
GO
6.存储过程的调用
USESCMIS
SELECT*FROMSC--执行前
EXECsc_add‘14003’,’C2’,86
SELECT*FROMSC--执行后
Go
EXECsc_update‘14003’,’14004’,’C2’,’C4’,78
SELECT*FROMSC--执行后
Go
EXECsc_delete‘14004’,’C4’
SELECT*FROMSC--执行后
Go
试用不同的参数传递方式执行上述存储过程。
7.创建一个存储过程course_proc2,如果指定学生有选课，则返回1;否则，返回0。
在查询分析器中输入程序代码如下，然后运行。
USESCMIS
GO
CREATEPROCdbo.course_proc2
（@ins_snochar（5）,@sc_countintOUTPUT）--@sc_count保存该学生的选课数。
AS
IFEXISTS
（SELECT*FROMSCWHERESno=@ins_sno）
BEGIN
SELECT@sc_count=COUNT（*）
FROMSC
WHERESno=@ins_sno
RETURN
（1）
END
ELSE
RETURN（0）
GO
执行，
USESCMIS
GO
DECLARE@stu_nochar（5）,
@msgvarchar（60）,
@return_statusint,
@numcourseint
SET@stu_no='14004'
EXEC@return_status=course_proc2@stu_no,@numcourseOUTPUT
IF@return_status=1
BEGIN
SELECT@msg='指定的学号'+@stu_no+'一共选修了'+STR（@numcourse,2）+'门课程'
PRINT@msg
END
ELSE
BEGIN
SELECT@msg='指定的学号'+@stu_no+'没有选修课程!
'
PRINT@msg
END
GO
点击“存盘”按钮，将执行语句保存为“成绩查询.sql”文件。
8.扩展的存储过程：
执行xp_msver，查看系统版本信息。
在查询分析器中输入程序代码如下，然后运行。
EXECxp_msver
小练习：
执行xp_logininfo扩展存储过程，了解账户、账户类型、权限级别、访问路径等系统信息。
升级会员 