SQL SERVER设计与高级查询 第六章 学习笔记(完结)

SQL SERVER设计与高级查询

第六章  存储过程

【系统存储过程】

常用的系统存储过程:

EXEC SP_DATABASES --列出服务器上的所有数据库

EXEC SP_HELPDB --报告有关指定数据库或所有数据库信息

EXEC SP_RENAMEDB  dbName,New_dbName--更改数据库名称，一般SQL不能修改数据库的名称，但可以通过查询中调用SP_RENAMEDB过程来修改

EXEC SP_TABLES  --返回当前环境下可查询的对象的列表

EXEC SP_COLUMNS TableName --返回某个表列的信息

EXEC SP_HELP TableName --查看某个表的所有信息

EXEC SP_HELPCONSTRAINT TableName --查看某表的约束

EXEC SP_HELPINDEX TableName --查看某个表的索引

EXEC SP_STORED_PROCEDURES  --列出当前环境中的所有存储过程

EXEC SP_PASSWORD --添加或修改登陆帐户的密码

EXEC SP_HELPTEXT --显示默认值，未加密的存储过程，用户定义的存储过程、触发器或视图的实际文本

--常用扩展存储过程XP_CMDSHELL

EXEC XP_CMDSHELL DOS命令[NO_OUTPUT] --NO_OUTPUT  设置执行DOS命令后是否输出返回信息

 

/*-系统存储过程实例-*/

USE MASTER

GO

--创建数据库bankDB,要求保存在D:/bank

EXEC XP_CMDSHELL 'mkdir d:/bank',NO_OUTPUT  --创建文件夹D:/bank

--创建数据库bankDB

IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME = 'bankDB')

DROP DATABASE bankDB

GO

CREATE DATABASE bankDB

ON

(

NAME = 'bankDB',

FILENAME = 'd:/bank/bankDB.mdf',

SIZE = 5MB,

FILEGROWTH = 10%

)

LOG ON

(

NAME = 'bankDB_LOG',

FILENAME = 'd:/bank/bankDB_log.ldf',

SIZE = 1MB,

MAXSIZE = 100MB,

FILEGROWTH = 10%

)

GO

 

 

 

【自定义存储过程】

/*-语法-*/

CREATE PROCEDURE procedureName

{@参数数据类型} [=默认值] [output], --output关键字表示此参数为输出参数，否则视为普通输入参数

{@参数数据类型} [=默认值] [output]

AS

SQL语句

 

/*-不带参数存储过程实例-*/

USE student

GO

--查看本次考试的平均分以及未通过考试的学员名单

IF EXISTS(SELECT * FROM sysobjects WHERE NAME = 'proc_stu')

DROP PROCEDURE proc_stu

GO

--创建存储过程

CREATE PROCEDURE proc_stu

AS

DECLARE @writtenAvg float,@labAvg float  --笔试和机试平均分变量

SELECT @writtenAvg = AVG(writtenExam),@labAvg = AVG(labExam)

FROM stuMarks

 

PRINT '笔试平均分：'+CONVERT(VARCHAR(5),@writtenAvg)

PRINT '机试平均分：'+CONVERT(VARCHAR(5),@labAvg)

 

IF @writtenAvg>70 AND @labAvg>70

       PRINT '本班考试成绩：优秀'

ELSE

       PRINT '本班考试成绩：较差'

PRINT '-------------------------------------------------------------------------------'

PRINT '                                参加考试未通过的学员名单                                   '

 

SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks on stuInfo.stuNo = stuMarks.stuNo

WHERE writtenExam < 60 OR labExam < 60

GO

--调用存储过程语法：EXEC 过程名[参数]

EXEC proc_stu

 

/*-创建带输入参数的存储过程实例-*/

USE student

GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'proc_stu')

DROP PROCEDURE proc_stu

GO

 

CREATE PROCEDURE proc_stu

@writtenPass int = 60, --输入机试及格线

@labPass int = 60 --输入笔试及格线

AS

PRINT '笔试及格分数线：'+CONVERT(VARCHAR(5),@writtenPass)+'，'

                     +'机试及格分数线：'+CONVERT(VARCHAR(5),@labPass)

PRINT '-----------------------------------------------------------------------'

PRINT '                         参加本次考试没有通过的学员名单                        '

SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks

ON stuInfo.stuNo = stuMarks.stuNo

WHERE writtenExam < @writtenPass  OR labExam < @labPass

GO

 

--调用存储过程，根据情况调整分数线，观察变化

EXEC proc_stu 70,75

EXEC proc_stu

EXEC proc_stu 80,95

 

/*-处理错误信息-*/

--语法

RAISERROR (msg_id | msg_str , severity,state with option)

--msg_id 在sysmessages系统表中指定的用户错误信息

--msg_str 用户定义的特定信息，最长个字符

--severity 与特定信息相关联，表示用户定义的严重级别-18之间，-25为sysadmin预留

--state 表示错误的状态0-127

--option 指示是否将错误记录到服务器日志中

 

/*-创建带输出参数的存储过程实例-*/

/*-处理错误信息实例-*/

USE STUDENT

GO

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'PROC_STU')

DROP PROCEDURE PROC_STU

GO

CREATE PROCEDURE PROC_STU

@COUNT INT OUTPUT,

@WRITTENPASS INT=60,

@LABPASS INT = 60

AS

SET NOCOUNT ON --不显示受影响的行数

--错误处理

IF (NOT @WRITTENPASS BETWEEN 0 AND 100) OR (NOT @LABPASS BETWEEN 0 AND 100)

       BEGIN

              RAISERROR ('及格线错误，请指定范围在-100之间',16,1)

              RETURN

       END

--错误处理结束

PRINT '笔试及格线：'+CONVERT(VARCHAR(5),@WRITTENPASS)+'   '+'机试及格线：'+CONVERT(VARCHAR(5),@LABPASS)

PRINT '---------------------------------------------------------------------'

PRINT '                            参加本次考试没有通过的学员                          '

SELECT STUNAME,STUINFO.STUNO,WRITTENEXAM,LABEXAM

FROM STUINFO INNER JOIN STUMARKS ON STUINFO.STUNO = STUMARKS.STUNO

WHERE WRITTENEXAM < 60 OR LABEXAM < 60

--统计并返回没有通过测试的学员数量

SELECT @COUNT = COUNT(*) FROM STUMARKS WHERE WRITTENEXAM < 60 OR LABEXAM < 60

GO

--调用存储过程

DECLARE @SUM INT--定义一个变量，存放存储过程的返回结果

EXEC PROC_STU @SUM OUTPUT

PRINT '---------------------------------------------------------------------'

PRINT '未通过本次测试的人数：'+CONVERT(VARCHAR(5),@SUM) +'人'

 

 

(如果有不对的或需要补充的地方,还请老师和同学们帮忙指出来,谢谢!)

 

2010/3/21 整理