存储过程写法.doc

存储过程的定义和使用存储过程的定义和使用一、启动SQL查询分析器通过菜单新建查询，启动SQL查询分析器。系统将打开“连接到服务器”窗口n 在服务器名称（指定SQL Server服务器）栏中，可选择需要连接的SQL Server服务器。n 在身份验证中，可选择登录SQL Server服务器的验证方式。如果实验用机的SQL Server安装时设定了混合验证方式，则“SQL Server身份验证”方式可选。选择该方式，其登录名默认为：sa，密码默认是：空。点击按钮“确认”，SQL查询分析器将连接到指定的SQL Server服务器，并打开SQL查询分析器的界面SQL查询分析器的界面：SQL语句作用的当前数据库对象浏览窗口SQL语句输入窗口n 左侧窗口是“对象浏览器”窗口；n 右侧窗口是SQL语句的输入和调试窗口，可在该窗口中直接输入SQL语句或打开一个SQL脚本文件（后缀名为*.sql的文件）；n 上方的数据库列表显示了当前SQL语句作用的数据库。二、创建数据库使用SQL语句创建实验数据库SCMIS，并创建3张表：STUDENT、COURSE和SC，输入数据。以下内容均使用SQL语句在SQL查询分析器中执行运行。（a） 在SQL SERVER2012中，在查询分析器中使用T-SQL语句：CREATE DATABASE SCMISON (NAME=SCMIS,FILENAME=E:SCMIS.MDF,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)LOG ON(NAME=SCMIS_Log,FILENAME=e:SCMIS_Log.ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)(b)在SQL SERVER中查询分析器的编辑窗口中用下列SQL语句:/* 建表 */use SCMISCREATE TABLE dbo.COURSE (CNO char (2) PRIMARY KEY ,CNAME varchar (16) NOT NULL ,CREDIT int ,C_TOTAL int ,CPNO char (2) ) ON PRIMARYGOCREATE TABLE dbo.STUDENT (SNO char (5) PRIMARY KEY ,SNAME char (8) NOT NULL ,SSEX char (2) CONSTRAINT chkSsex CHECK(ssex IN (男,女) ,SDEPT varchar (10) ,SMAJOR varchar (10) ,SAGE int CONSTRAINT chkSage CHECK(sage=16 and sage可编程性-“存储过程”,在其上单击右键-选择“新建存储过程”，打开“存储过程属性”对话框，输入存储过程正文。方法二：在查询分析器中输入程序代码如下，然后运行。USE SCMIS GO CREATE PROC dbo.course_proc1 AS SELECT S.Sname,S.Sdept,C.Cname,SC.Score FROM Student S,Course C,SC WHERE S.Sno=SC.Sno AND C.Cno=SC.Cno ORDER BY C.Cname DESC GO执行存储过程：用T-SQL语句方式，EXEC可省略。 EXEC course_proc12. 创建带默认参数的存储过程，查看指定系别的学生信息。系别默认为“IS”。USE SCMIS GO CREATE PROC dbo.stu_proc1 s_dep varchar(10)=IS AS SELECT * FROM Student WHERE sdept=S_depGO执行存储过程的另一种方法：用界面方式。右击要执行的存储过程stu_proc1，选择“执行存储过程”命令，在弹出的【执行过程】窗口中，在【值】输入参数值：CS，点击“确定”。该存储过程在调用时即使不指定参数值，也可以返回一个默认的结果。小练习：该存储过程在调用时即使不指定参数值，也可以返回一个默认的结果。试着不指定参数执行存储过程，观察运行结果。练习：用T-SQL语句执行存储过程dbo.stu_proc1，查看CS系的学生信息。3. 向sc表添加一条记录的存储过程 sc_add：在查询分析器中输入程序代码如下，然后运行。USE SCMISGOCREATE PROC sc_add(ssno char(5),ccno char(2),sscore int)AS BEGIN INSERT INTO sc VALUES(ssno,ccno,sscore) ENDRETURNGO4. 修改sc表中一条记录的存储过程 sc_update：在查询分析器中输入程序代码如下，然后运行。USE SCMISGOCREATE PROC sc_update(ssno_old char(5),ssno_new char(5),ccno_old char(2),ccno_new char(2),sscore int)AS BEGIN UPDATE sc SET sno=ssno_new,cno=ccno_new,score=sscoreWHERE sno=ssno_old and cno=ccno_old ENDRETURNGO5. 删除sc表中一条记录的存储过程 sc_delete：在查询分析器中输入程序代码如下，然后运行。USE SCMISGOCREATE PROC sc_delete(ssno char(5),ccno char(2)AS BEGIN DELETE FROM sc WHERE sno=ssno and cno=ccno ENDRETURNGO6. 存储过程的调用USE SCMISSELECT * FROM SC - 执行前EXEC sc_add 14003,C2,86SELECT * FROM SC - 执行后GoEXEC sc_update 14003,14004,C2,C4,78SELECT * FROM SC - 执行后GoEXEC sc_delete 14004,C4SELECT * FROM SC - 执行后Go试用不同的参数传递方式执行上述存储过程。7. 创建一个存储过程course_proc2,如果指定学生有选课，则返回1;否则，返回0。在查询分析器中输入程序代码如下，然后运行。USE SCMIS GO CREATE PROC dbo.course_proc2 (ins_sno char(5),sc_count int OUTPUT) -sc_count 保存该学生的选课数。 AS IF EXISTS (SELECT * FROM SC WHERE Sno=ins_sno) BEGIN SELECT sc_count=COUNT(*) FROM SC WHERE Sno=ins_sno RETURN(1) END ELSE RETURN(0)GO 执行，USE SCMIS GO DECLARE stu_no char(5), msg varchar(60), return_status int, numcourse int SET stu_no=14004 EXEC return_status=course_proc2 stu_no,numcourse OUTPUT IF return_status=1 BEGIN SELECT msg=指定的学号+stu_no+一共选修了+STR(numcourse,2)+门课程 PRINT msg END ELSE BEGIN SELECT msg=指定的学号+stu_no+没有选修课程