项目8 CJGL系统编程

项目8CJGL系统编程

SQLServer2008项目实训教程技能目标

学会使用Transact-SQL语言对CJGL数据库进行应用编程，以提高数据库应用系统的开发能力

SQLServer2008项目实训教程知识目标正确理解和掌握使用SQLServer变量；掌握SQLServer函数的使用；掌握流程控制语句；掌握编写顺序结构、选择结构和循环结构的程序学会创建、删除、修改存储过程；学会根据实际需要设计CJGL数据库中的存储过程；掌握创建、执行、修改和删除触发器的方法；学会根据实际开发CJGL数据库中的触发器；熟悉游标的工作原理、定义和使用

SQLServer2008项目实训教程Transact-SQL语言T-SQL是Microsoft公司在关系型数据库管理系统SQLServer中的SQL-3标准的实现,是微软对SQL（结构化查询语言）的扩展,具有SQL的主要特点,同时增加了变量,运算符,函数,流程控制和注释等语言元素,使得其功能更加强大。T-SQL对SQLServer十分重要,SQLServer中使用图形界面能够完成的所有功能,都可以利用T-SQL来实现.

SQLServer2008项目实训教程Transact-SQL语言的分类（1）数据定义语言DDL：用来创建数据库和数据库对象的命令，绝大部分以CREATE开头，如CREATETABLE等。（2）数据操作语言DML：用来操作数据库中各种对象，对数据进行修改和检索。DML语言主要有四种：SELECT（查询）、INSERT（插入）、UPDATE（更新）和DELETE（删除）。（3）数据控制语言DCL：用来控制数据库组件的存取许可、权限等命令，如GRANT、REVOKE等。（4）事务管理语言TML：用于管理数据库中的事务的命令，如COMMIT、ROLLBACK等。（5）其他语言元素：如标识符、数据类型、流程控制和函数等。

SQLServer2008项目实训教程Transact-SQL语法约定Transact-SQL语法格式约定标识符续行注释批处理

SQLServer2008项目实训教程Transact-SQL语法格式约定语法约定说明大写Transact-SQL关键字。

斜体用户提供的Transact-SQL语法的参数。粗体数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样键入的文本。下划线当语句中省略了包含带下划线的值的子句时应用的默认值。|（竖线）分隔括号或大括号中的语法项。只能选择其中一项。[]（方括号）可选语法项。不要键入方括号。{}（大括号）必选语法项。不要键入大括号。[,...n]指示前面的项可以重复n次。每一项由逗号分隔。[...n]指示前面的项可以重复n次。每一项由空格分隔。[;]可选的

Transact-SQL语句终止符。不要键入方括号。<标签>::=语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。

SQLServer2008项目实训教程语法格式约定标识符：用来定义服务器、数据库、数据库对象和变量等的名称。可分为常规标识符和分隔标识符。要求：第一个字符必须是字母、下划线、at符号（@）和数字标记（#）；标识符不能是T-SQL的保留字；包含的字符数必须在1-128之间。

续行：直接将一条语句分几行编写。

SQLServer2008项目实训教程语法格式约定注释：使用--（双减号）或/*…*/两种表示方法例1：单行文本注释--选择学生信息Select*fromstudent例2：块注释语句Selectsno,sNameFromstudent/*wheresnoin(selectsnofromscore)*/其中，where子句被注释，不再起作用。

SQLServer2008项目实训教程语法格式约定批处理：由一条或多条SQL语句构成，可从批中读取所有语句，编译成可执行的单元（执行计划），然后一次执行计划中的所有语句，用GO结束批处理。例：打印教师姓名Declare@namevarchar(10)Select@name=‘范新刚’Print‘教师姓名:’+@nameGo

SQLServer2008项目实训教程数据库对象命名方法

所有数据库对象名可以是由四部分组成的名称，格式如下。[server_name．[database_name]．[schema_name]．

|database_name．[schema_name]．|schema_name．]object_name

各部分说明如下。server_name：连接的服务器名称或远程服务器名称。database_name：SQLServer数据库的名称schema_name：指定包含对象的架构的名称。object_name：对象的名称。

SQLServer2008项目实训教程常量

根据常量值的不同类型，常量分为字符串常量、二进制常量、整型常量、实数常量、日期时间常量、货币常量和惟一标识常量。

SQLServer2008项目实训教程变量

变量是指在程序运行过程中，其值会改变的量。主要用来存储临时性的数据。

SQLServer2005提供两种变量：用户自己定义的局部变量和系统提供的全局变量。

SQLServer2008项目实训教程局部变量

用户自己定义的变量称为局部变量。局部变量用于保存特定类型的单个数据值的对象，必须先定义，再使用。变量名必须以@开头。定义格式：

DECLARE局部变量名数据类型[,...n]【例】定义一个整型变量。Declare@numberint【例】定义三个varchar类型变量和一个整型变量Declare@namevarchar(8),@sexvarchar(2),@ageint,@addressvarchar(50)局部变量的赋值一次为一个变量赋值：SET@局部变量名=表达式[,…n]一次为多个变量赋值：SELECT@局部变量名=表达式[,…n][FROM子句][WHERE子句]

SQLServer2008项目实训教程局部变量子任务1：声明2个变量，分别为字符型和整型，为其赋值为学生表中学号为001的学生姓名和年龄，并以消息方式输出以下结果：Declare@namevarchar(20),@ageintSelect@name=姓名,@age=datediff(yy,出生日期,getdate())From学生表Where学号='001'Print@name+’的年龄是：’+cast(@ageasvarchar(10))

SQLServer2008项目实训教程全局变量

全局变量由系统定义和维护的变量，用于记录服务器活动状态的一组数据。全局变量名由@@符号开始。用户不能建立全局变量，也不能使用SET语句去修改全局变量的值。在SQLServer中，全局变量以系统函数的形式在使用。

SQLServer2008项目实训教程表达式和运算符

表达式是标识符、值和运算符的组合。可将表达式用在查询中作为检索数据的一部分，也可用作查找满足一组条件的数据时的搜索条件。

SQLServer2008项目实训教程算术运算符运算符含义+（加）加-（减）减*（乘）乘/（除）除%（取模）返回一个除法运算的整数余数。例如，12%5=2，这是因为12除以5，余数为2。

SQLServer2008项目实训教程位运算符运算符含义&（位与）逻辑与运算（两个操作数）|（位或）位或（两个操作数）^（位异或）位异或（两个操作数）

SQLServer2008项目实训教程比较运算符运算符含义=（等于）等于>（大于）大于<（小于）小于>=（大于等于）大于或等于<=（小于等于）小于或等于<>（不等于）不等于!=（不等于）不等于（非SQL-92标准）!<（不小于）不小于（非SQL-92标准）!>（不大于）不大于（非SQL-92标准）

SQLServer2008项目实训教程逻辑运算符运算符含义ALL如果一组的比较都为TRUE，那么就为TRUE。AND如果两个布尔表达式都为TRUE，那么就为TRUE。ANY如果一组的比较中任何一个为TRUE，那么就为TRUE。BETWEEN如果操作数在某个范围之内，那么就为TRUE。EXISTS如果子查询包含一些行，那么就为TRUE。IN如果操作数等于表达式列表中的一个，那么就为TRUE。LIKE如果操作数与一种模式相匹配，那么就为TRUE。NOT对任何其他布尔运算符的值取反。OR如果两个布尔表达式中的一个为TRUE，那么就为TRUE。SOME如果在一组比较中，有些为TRUE，那么就为TRUE。

SQLServer2008项目实训教程字符串串联运算符

加号(+)是字符串串联运算符，将字符串串联起来。例如，‘教师姓名:’+‘范新刚'的结果就是'教师姓名:范新刚'。

SQLServer2008项目实训教程一元运算符运算符含义+（正）数值为正。-（负）数值为负。~（位非）返回数字的非。

SQLServer2008项目实训教程运算符优先级级别运算符1~（位非）2*（乘）、/（除）、%（取模）3+（正）、-（负）、+（加）、（+连接）、-（减）、&（位与）4=,

>、<、>=、<=、<>、!=、!>、!<（比较运算符）5^（位异或）、|（位或）6NOT7AND8ALL、ANY、BETWEEN、IN、LIKE、OR、SOME9=（赋值）

SQLServer2008项目实训教程函数

在Transact-SQL编程语言中提供丰富的函数。函数可分系统定义函数和用户定义函数。本节介绍的是系统定义函数中最常用的数学函数、字符串函数、日期时间函数、聚合函数、类型转换函数的最常用的部分。

SQLServer2008项目实训教程聚合函数常用的聚合函数（只用在select、compute、computeby或having子句中）

函数名功能AVG返回一组值的平均值。COUNT返回一组值中项目的数量。（返回值为int类型）。COUNT_BIG返回一组值中项目的数量。（返回值为bigint类型）。MAX返回表达式或者项目中的最大值。MIN返回表达式或者项目中的最小值。SUM返回表达式中所有项的和，或者只返回DISTINCT值。SUM只能用于数字列。

SQLServer2008项目实训教程数学函数

函数名功能ABS()返回该数的绝对值。Pi()返回圆周率。POWER(N,M)返回N的M次幂。RAND([N])返回0~1之间的随机数。ROUND(X,N)返回四舍五入值，精度为保留小数点N位。SQRT()返回平方根。

SQLServer2008项目实训教程字符串函数函数功能ASCII(字符表达式)返回最左侧的字符的ASCII码值CHAR（整型表达式）将intASCII代码转换为字符LEFT(字符表达式,整数)返回从左边开始指定个数的字符串RIGHT(字符表达式,整数)截取从右边开始指定个数字符串SUBSTRING(字符表达式,起始点,n)截取从起始点开始n个字符串LEN(字符串表达式)返回字符串的长度LTRIM(字符表达式)剪去左空格RTRIM(字符表达式)剪去右空格REPLICATE(字符表达式,n)重复字串CHARINDEX(字符C，字符表达式,[P])返回指定字符C在字符串中第一次出现的位置LOWER(字符常量)/UPPER(字符常量)/返回字符的小写/大写

SQLServer2008项目实训教程日期和时间函数函数功能DATEADD(datepart,数值,日期)返回增加一个时间间隔后的日期结果DATEDIFF(datepart,日期1,日期2)返回两个日期之间的时间间隔，格式为datepart参数指定的格式DATENAME(datepart,日期)返回日期的文本表示，格式为datepart指定格式DATEPART(datepart,日期)返回某日期的datepart代表的整数值GETDATE（）返回当前系统日期和时间DAY（日期）返回某日期的日datepart所代表的整数值MONTH（日期）返回某日期的月datepart所代表的整数值YEAR（日期）返回某日期的年datepart所代表的整数值

SQLServer2008项目实训教程类型转换函数函数功能CAST(@varASDatatype)将变量或常量数据类型转换为指定类型。CONVERT(Datatype,@var,[日期格式])将变量或常量数据类型转换为指定类型。如转换为日期类型，可指定日期格式。

SQLServer2008项目实训教程输出显示1.以网格方式输出使用select语句输出，如select@var1,@var2……2.以消息方式输出使用print语句输出，如print@var1+@var2+……注意：使用print输出的只能是字符类型，如其中包含非字符类型数据，则要进行类型转换。

SQLServer2008项目实训教程任务实施子任务2：一个电子邮箱地址为student_www@126.com，请使用函数得到其账户名，并以消息方式输出。Declare@emailvarchar(30),@usernamevarchar(20)Set@email='student_www@126.com'Set@username=substring(@email,1,charindex('@',@email)-1)Print@username子任务3：声明一个字符变量，并为其赋值（任意单词），然后将首字符变为大写后输出。Declare@wordvarchar(10),@new_wordvarchar(10)Set@word='student'Set@new_word=upper(left(@word,1))set@new_word=@new_word+right(@word,len(@word)-1)Print@new_word

SQLServer2008项目实训教程任务实施子任务4：查询80年代出生的学生信息，包括姓名、性别和出生日期。Select姓名,性别,出生日期From学生表Whereconvert(char(4),year(出生日期),102)like'198[0-9]‘其中，102表示日期采用ANSI标准的yy.mm.dd格式。

SQLServer2008项目实训教程流程控制语句1、BEGIN…END语句

BEGIN…END语句用于将多个Transact-SQL语句组合为一个逻辑块，相当于一个单一语句，达到一起执行的目的。它的语法格式如下。

BEGIN{

语句1

语句2…}ENDSQLServer允许BEGIN…END语句嵌套使用。

SQLServer2008项目实训教程流程控制语句（续）2、IF…ELSE语句

IF…ELSE语句实现程序选择结构。它的语法格式如下。

IF逻辑表达式

{语句块1}[ELSE{语句块2}]

SQLServer2008项目实训教程流程控制语句（续）【例】在CJGL数据库中，查询学生的平均成绩是否超过60分，并显示相关信息。

declare@avg_sintselect@avg_s=avg(成绩)from成绩表

if@avg_s>60print'学生的平均成绩超过60分'elseprint'学生的平均成绩不超过60分‘

SQLServer2008项目实训教程流程控制语句（续）【例】在CJGL数据库中，查询课程编号为“X001”的平均分是否超过80，如果是，列出所有该课程分数超过平均分的学生信息：学号、姓名、成绩；否则列出所有该课程分数低于平均分的学生信息。declare@avg_sfloatselect@avg_s=avg(成绩)from成绩表where课程编号=‘X001’if@avg_s>80selectS1.学号,姓名,成绩from学生表S1,成绩表S2whereS1.学号=S2.学号and成绩>@avg_sand课程编号=‘X001’elseselects1.学号,姓名,成绩from学生表S1,成绩表S2whereS1.学号=S2.学号and成绩<=@avg_sand课程编号=‘X001’

SQLServer2008项目实训教程流程控制语句（续）3、CASE语句：多分支结构。格式有如下两种：（1）简单case表达式计算条件列表并返回多个可能结果表达式之一。Case输入表达式

when表达式1then结果表达式1when表达式2then结果表达式2[,…n][else其它结果表达式]end

SQLServer2008项目实训教程流程控制语句（续）【例】查询所有学生的姓名、性别、出生日期和所在班级信息。select姓名,性别,出生日期,班级名称=case班级编号when0900401001then'11网络技术'when0900402002then'11软件技术'when0900403003then’12软件技术‘else'其它班级'endfrom学生表

SQLServer2008项目实训教程流程控制语句（续）

（2）搜索类型case表达式

Casewhen逻辑表达式1then结果表达式1when逻辑表达式2then结果表达式2[,…n][else其它结果表达式]End

SQLServer2008项目实训教程流程控制语句（续）【例】教师信息，并增加一列“等级”：如果职称为“教授”、“副教授”和“高级工程师”则等级为“高级”，如果职称为“讲师”和“工程师”的则等级为“中级”，其它为“初级”。select教师编号,教师姓名,出生日期,等级=casewhen职称in('教授','副教授','高级工程师')then'高级'when职称in('讲师','工程师')then'中级'else'初级'endfrom教师表

SQLServer2008项目实训教程流程控制语句（续）4、WHILE语句

WHILE语句实现循环结构。如果指定的条件为真，就重复执行语句块，直到逻辑表达式为假。它的语法格式如下。WHILE逻辑表达式

BEGIN

语句块1[CONTINUE][BREAK]

语句块2END

SQLServer2008项目实训教程流程控制语句（续）【例】编写程序，求1-100之间的和。declare@iint,@sumbigintselect@i=1,@sum=0while@i<=100beginset@sum=@sum+@iset@i=@i+1endprint@sum【例】编写程序，求1-200之间所有能被7整除的整数。declare@iintset@i=1while@i<=200beginif@i%7=0print@iSet@i=@i+1end

SQLServer2008项目实训教程流程控制语句（续）5、RETURN语句：无条件退出。格式如下：RETURN[整数表达式]6、WAITFOR语句

WAITFOR语句实现语句延缓一段时间或延迟到某特定的时间执行。它的语法格式如下。WAITFOR{DELAY'time'|TIME'time'}其中，Delay表示一直等到指定的时间过去，最长24小时。

Time则表示等待到指定时间。【例】等待10秒后执行select语句。

Waitfordelay'00:00:10’Select*from学生表【例】等到11点10分后才执行select语句。

Waitfortime’11:10:00’Select*from学生表

SQLServer2008项目实训教程用户自定义函数函数是由一个或多个T-SQL语句组成的子程序，可用于封装代码以便重新使用。SQLserver中除了可以使用系统函数外，还允许用户创建自己的函数。T-SQL中用户自定义函数分为3类：标量函数、内嵌表值函数和多语句表值函数。

SQLServer2008项目实训教程用户自定义函数1.标量函数返回一个标量值。语法格式如下：Createfunction函数名([参数名1数据类型,…])returns数据类型–定义函数返回的数据类型[withencryption]asbeginSQL_statementreturn表达式语句–标量函数必须具有返回值end

SQLServer2008项目实训教程用户自定义函数【例】创建标量函数UF_Credits，该函数根据输入的学号，返回该学生取得的总学分数，并利用该函数查询“11网络技术”班所有学生的学号、姓名、总学分数。--首先创建函数createfunctionUF_Credits(@snovarchar(10))returnsintasbegindeclare@creditintselect@credit=sum(学分)from成绩表Sjoin课程表ConS.课程编号=C.课程编号where成绩>=60and学号=@snoreturn@creditend

SQLServer2008项目实训教程用户自定义函数【例】创建标量函数UF_Credits，该函数根据输入的学号，返回该学生取得的总学分数，并利用该函数查询“11网络技术”班所有学生的学号、姓名、总学分数。--应用函数select学号,姓名,dbo.UF_Credits(学号)总学分数from学生表Sjoin班级表ConS.班级编号=C.班级编号where班级名称='11网络技术'

SQLServer2008项目实训教程用户自定义函数2.内嵌表值函数函数体本身是一个查询语句，结果返回一个表。语法格式如下：Createfunction函数名[(参数名1数据类型,…)]returnstable–返回的数据类型是表asreturnselect语句

SQLServer2008项目实训教程用户自定义函数【例】创建内嵌表值函数UF_Classlist，该函数根据输入的班级名称，给出该班所有学生的详细信息，并利用该函数查询“11网络技术”班所有学生信息。--首先创建函数createfunctionUF_Classlist(@classnamevarchar(20))returnstableasreturnselectS.*from学生表Sjoin班级表ConS.班级编号=C.班级编号where班级名称=@classname--应用函数select*fromdbo.UF_Classlist('11网络技术')

SQLServer2008项目实训教程用户自定义函数1.多语句表值函数返回的也是一个表，但此表是在该函数中新定义的。语法格式如下：Createfunction函数名[(参数名1数据类型,…)]returns@table_nametable–返回的数据类型是表变量{table_define_statement}–定义表结构asbeginSQL_statement–一般是select…into…语句return–后面不需要任何表达式end

SQLServer2008项目实训教程用户自定义函数【例】创建多语句表值函数UF_Class_score，根据输入的班级名称和课程名称返回该班所有学生的学号、姓名、性别、课程名称和成绩，并利用该函数查询“11网络技术”班“数据库技术与应用”课程的成绩。--首先创建函数createfunctionUF_Class_score(@classnamevarchar(20),@coursenamevarchar(20))returns@ttable(学号char(10),姓名varchar(20),性别char(2),课程名称varchar(20),成绩int)asbegininsertinto@tselectS1.学号,姓名,性别,课程名称,成绩from学生表S1,班级表C1,课程表C2,成绩表S2WhereS1.班级编号=C1.班级编号andS1.学号=S2.学号andC2.课程编号=S2.课程编号and班级名称=@classnameand课程名称=@coursenamereturnend

SQLServer2008项目实训教程用户自定义函数【例】创建多语句表值函数UF_Class_score，根据输入的班级名称和课程名称返回该班所有学生的学号、姓名、性别、课程名称和成绩，并利用该函数查询“11网络技术”班“数据库技术与应用”课程的成绩。--应用函数select*fromUF_Class_score('11网络技术','数据库技术与应用')

SQLServer2008项目实训教程存储过程

存储过程（StoredProcedure）是一组完成特定功能的Transact-SQL语句集，经编译后存储在数据库中，用户调用过程名和给出参数来调用它们。

SQLServer2008项目实训教程存储过程的特点允许模块化程序设计，提高代码的重用性执行速度快有效降低网络流量提高数据库的安全性

SQLServer2008项目实训教程存储过程的分类系统存储过程用户自定义存储过程扩展存储过程

SQLServer2008项目实训教程存储过程的分类(续)系统存储过程

SQLServer2008项目实训教程存储过程的分类（续）用户自定义存储过程用户自定义存储过程是指封装的由用户创建，能完成某一特定的功能的可重用代码的模块或例程。扩展存储过程扩展存储过程是指使用编程语言（例如C）创建自己的外部例程，是指MicrosoftSQLServer的实例可以动态加载和运行的DLL，以“XP_”为前缀。

SQLServer2008项目实训教程

存储过程语法格式语法格式：

CREATE[PROC|PROCEDURE]存储过程名[;number][{@参数名称参数数据类型}[VARYING][=参数的默认值][OUTPUT]][,...n][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement

参数：Number:用来对同名的过程分组，以便用一条dropproc语句即可将同组的过程一起除去。@参数名称：存储过程可以没有参数。也可以声明一个或多个参数，参数名称必须@作为第一个字符。VARYING表示指定作为输出参数支持的结果集，仅适用于游标参数。参数可设置默认值，如后面带OUTPUT，表示为输出参数。WITHENCRYPTION：对存储过程加密，其他用户无法查看存储过程的定义。WITHRECOMPILE：每次执行该存储过程都重新进行编译。sql_statemen：该存储过程中定义的编程语句。

SQLServer2008项目实训教程存储过程的组成存储过程的定义中包含如下的两个主要组成部分。（1）过程名称及其参数的说明：包括所有的输入参数以及传给调用者的输出参数。（2）过程的主体：也称为过程体，针对数据库的操作语句（T-SQL语句），包括调用其它存储过程的语句。

SQLServer2008项目实训教程不带参数的存储过程1、创建不带参数的存储过程语法格式：CREATE[PROC|PROCEDURE]存储过程名

[WITHENCRYPTION][WITHRECOMPILE]ASsql_statement【例】创建一个名为s_inf的存储过程，用于查询学生的信息。createprocedures_infasselect*from学生表

SQLServer2008项目实训教程不带参数的存储过程【例】创建一个名为s_lili_score存储过程，用于查询“李丽”的学号、姓名、性别、出生日期以及所有课程的成绩。createprocedures_lili_scoreasselectS1.学号,姓名,性别,出生日期,成绩from学生表S1,成绩表S2whereS1.学号=S2.学号and姓名=‘李丽'

SQLServer2008项目实训教程不带参数的存储过程（续）

执行不带参数的存储过程语法结构如下。

EXEC[UTE]存储过程名【例】执行创建的s_inf和s_lili_score存储过程。

EXECs_infEXECs_lili_score

SQLServer2008项目实训教程带输入参数的存储过程创建带输入参数的存储过程CREATE[PROC|PROCEDURE]存储过程名

[{@参数名称参数数据类型}[=参数的默认值][,...n][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement【例】创建存储过程stu_count，实现根据输入的系部名称输出给定系部的学生人数。createprocstu_count@dnamevarchar(20)asselectcount(学号)学生人数from部门表D,班级表C,学生表SwhereD.系部编号=C.系部编号andC.班级编号=S.班级编号and系部名称=@dname

SQLServer2008项目实训教程带输入参数的存储过程（续）执行输入参数的存储过程两种方法：（1）使用参数名传递参数值执行的语法结构如下。EXEC存储过程名[@参数名=参数值][DEFAULT][，…n]execstu_count@dname='信息工程系'（2）按位置传递参数值EXEC存储过程名[参数值1，参数值2，…]execstu_count'信息工程系'

SQLServer2008项目实训教程带输出参数的存储过程1、创建带输出参数的存储过程语法如下。@参数名数据类型[=默认值]OUTPUT【例】创建存储过程stuname，实现根据输入学生的学号，输出该学生的姓名和政治面貌。并调用该存储过程返回“001”号学生的姓名和政治面貌。createprocstuname@学号char(8),@姓名varchar(20)output,@政治面貌varchar(10)outputasselect@姓名=姓名,@政治面貌=政治面貌from学生表where学号=@学号

SQLServer2008项目实训教程带输出参数的存储过程2、执行带输出参数的存储过程语法如下。EXEC[UTE]存储过程名[[@参数名=]{参数值|@变量[OUTPUT]|[默认值]}][,...n]declare@stunamevarchar(20),@stuzvarchar(10)execstuname‘001’,@stunameoutput,@stuzoutputselect@stuname姓名,@stuz政治面貌

SQLServer2008项目实训教程查看存储过程（1）sp_help用于显示存储过程的参数及其数据类型。语法格式如下。sp_help[[@objname=]存储过程名]（2）sp_helptext用于显示存储过程的源代码。语法格式如下。sp_helptext[[@objname=]存储过程]

SQLServer2008项目实训教程删除用户存储过程

删除用户存储过程可以使用DROP命令。语法格式如下。DROP{PROC|PROCEDURE}存储过程名[,...n]

SQLServer2008项目实训教程修改存储过程ALTERPROCEDURE语句的语法格式如下。ALTER[PROC|PROCEDURE]存储过程名

[{@参数名称参数数据类型}[=参数的默认值][OUTPUT]][,...n][WITHENCRYPTION][WITHRECOMPILE]ASsql_statement

SQLServer2008项目实训教程系统存储过程常用系统存储过程：sp_tables：返回可在当前环境中查询的对象列表。这代表可在FROM子句中出现的任何对象。sp_stored_procedures：返回当前环境中的存储过程列表。sp_rename：在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型。sp_renamedb：更改数据库的名称。sp_help：报告有关数据库对象（sys.sysobjects兼容视图中列出的所有对象）、用户定义数据类型或SQLServer2005提供的数据类型的信息。sp_helptext：示用户定义规则的定义、默认值、未加密的Transact-SQL存储过程、用户定义Transact-SQL函数、触发器、计算列、CHECK约束、视图或系统对象（如系统存储过程）。sp_who：提供有关MicrosoftSQLServerDatabaseEngine实例中的当前用户和进程的信息。sp_password：为MicrosoftSQLServer登录名添加或更改密码。

SQLServer2008项目实训教程创建存储过程需要考虑的因素：（1）一个存储过程完成一个任务。（2）不要使用sp_来命名用户存储过程。（3）可以使用WITHENCRYPTION加密存储过程，以免存储过程的源代码被人查阅。（4）在服务器上创建、测试存储过程。

SQLServer2008项目实训教程触发器

触发器通常可以完成一定的业务规则，用于SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能的限制。

SQLServer2008项目实训教程触发器的分类DML触发器当数据库中发生数据操作语言(insert、update、delete)事件时将调用DML触发器。主要用来实现表之间的级联更新或删除，以及防止恶意或错误的插入、更新和删除操作。DDL触发器在CREATE、ALTER、DROP和其他DDL语句上操作时发生的触发器称为DDL触发器。主要用来记录数据库的修改过程，以及限制程序员对数据库的修改。

SQLServer2008项目实训教程DML触发器的分类根据触发器触发方式的不同，可分为：AFTER触发器：在数据修改完成后，触发器被激活。执行顺序如下。数据表约束检查→修改表中的数据→激活触发器INSTEADOF触发器：这类触发器会取代原来要进行的操作，在数据更改之前发生，数据的如何更改完全取决于触发器的内容，执行顺序如下。激活触发器→若触发器涉及数据更改，则检查表约束CLR触发器：CLR触发器将执行在托管代码中编写的方法，而不用执行T-SQL存储过程。

SQLServer2008项目实训教程DML触发器创建触发器的语法格式如下。CREATETRIGGER触发器名

ON表名或视图名

[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}//选择触发器的类型{INSERT[，]|UPDATE[，]|DELETE}//用来指明哪种数据操作将激活触发器[NOTFORREPLICATION]AS[IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)[…n]//用来确定对某一确定列进行插入操作还是更新操作，但不与删除操作用在一起

sql_statements

SQLServer2008项目实训教程INSERTED表和DELETED表操作类型INSERTED表DELETED表INSERT插入的记录不创建DELETE不创建删除的记录UPDATE修改后的记录修改前的记录DML触发器都会创建两个临时的用于记录更改前后变化的表：INSERTED和DELETED，表的结构和创建触发器的表的结构相同。在执行DML操作时，两个表的记录如下：对具有触发器的表进行insert、delete和update操作时，过程分别如下：1.Insert操作：插入到表中的新行被复制到inserted表中。2.Delete操作：从表中删除的行转移到了deleted表中。3.Update操作：先从表中删除旧行，然后向表中插入新行。其中，删除后的旧行被转移到deleted表中，插入到表中的新行被复制到inserted表中。

SQLServer2008项目实训教程创建DML触发器1、INSERT触发器INSERT触发器通常被用来验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。【例】创建名为reminder的触发器，当用户向部门表中插入一条系部记录时，向客户端发送一条提示消息“插入一条记录！”。

--创建触发器

createtriggerreminderon部门表forinsertasprint'插入一条记录！’

--测试触发器insert部门表values(14,‘管理系’,‘王明阳')

SQLServer2008项目实训教程创建DML触发器2、DELETE触发器当激发delete触发器时，被删除的记录转移到deleted表中，数据表中将不再存在该记录。

【例】在学生表上创建一个触发器，其功能为：当对学生表进行删除操作时，首先检查要删除的学生是否为11网络技术班的学生，如果不是的话，可以删除该学生；否则撤销此删除，并显示无法删除的信息。--创建触发器

createtriggerstu_delon学生表fordeleteasdeclare@cnamevarchar(20)select@cname=班级名称from班级表joindeletedon班级表.班级编号=deleted.班级编号if(@cname='11网络技术')beginprint'此为11网络技术班学生，无法删除记录'rollbackend

--测试触发器delete学生表where学号=’005’

SQLServer2008项目实训教程创建DML触发器（续）3、UPDATE触发器

当在定义有触发器的表上执行UPDATE语句时，原始行被移入到DELETED表，更新行被移入到INSERTED表。【例】创建了一个修改触发器，禁止用户更新学生表的学号字段值--创建触发器

createtriggers_update

on学生表

afterupdate

as

ifupdate(学号)

begin

raiserror('不允许修改主键列！',16,1)

rollbacktran

end

--测试触发器

update学生表set学号='012'where学号='004'

SQLServer2008项目实训教程创建DML触发器（续）【例】在成绩表上创建触发器trscore，检查插入或更新的成绩是否在0~100之间。--创建触发器

ifexists(selectnamefromsysobjectswherename='trscore'andtype='tr')droptriggertrscoregocreatetriggertrscoreon成绩表forinsert,updateasdeclare@scoreintselect@score=成绩frominsertedif@score<0or@score>100beginprint'成绩必须在0到100之间！'rollbackendgo--测试触发器insert成绩表values('004','x004',-50)goupdate成绩表set成绩=120where编号=3go

SQLServer2008项目实训教程创建DML触发器（续）【例】将上例改成使用insteadof触发器来实现。--修改触发器altertriggertrscoreon成绩表insteadofinsert,updateassetnocountondeclare@scoredecimal(4,1),@snochar(8),@coursenochar(4)set@sno=(select学号frominserted)set@courseno=(select课程编号frominserted)select@score=成绩frominsertedIf@score<0or@score>100beginprint'成绩必须在0到100之间！'rollbackendelsebegininsert成绩表values(@sno,@courseno,@score)endgo

SQLServer2008项目实训教程创建DML触发器（续）--测试触发器insert成绩表values('004','x004',-50)goupdate成绩表set成绩=120where编号=3go

SQLServer2008项目实训教程修改触发器使用ManagementStudio使用ALTERTRIGGER语句语法格式如下。ALTERTRIGGER要修改的触发器名

ON表名或视图名

[WITHENCRYPTION](FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}ASsql_statements

SQLServer2008项目实训教程DDL触发器DDL触发器常用于如下的情况。防止对数据库架构进行某些更改。响应数据库架构中的更改。记录数据库架构中的更改或事件创建DDL触发器的语法格式如下。CREATETRIGGER触发器名ON{ALLSERVER|DATABASE}[WITHENCRYPTION]{FOR|AFTER}{DDL事件}[,...n]ASsql_statement

SQLServer2008项目实训教程创建DDL触发器【例】创建DDL触发器trig_ddl，该触发器禁止对所有表的结构修改及删除。--创建触发器

createtriggertrig_ddl

ondatabaseforcreate_table,alter_table,drop_table

asbeginraiserror(’该数据库不允许进行架构更改！’,16,9)rollbackend--测试触发器altertable学生表addAint

SQLServer2008项目实训教程查看触发器使用系统存储过程sp_help、sp_helptext和sp_depents分别查看触发器的不同信息。sp_help：显示触发器的所有者和创建时间sp_helptext：显示触发器的源代码sp_depends：显示该触发器参考的对象清单

SQLServer2008项目实训教程删除触发器使用ManagementStudio使用DROPTRIGGER命令。语法格式如下。DROPTRIGGERtrigger_name[,...n]ON{DATABASE|ALLSERVER}[;]

SQLServer2008项目实训教程禁止或启用触发器禁用触发器当不再需要某个触发器时，可将其禁用或删除。语法格式如下。DISABLETRIGGER{ALL|触发器名[,...n]}ON{object_name|DATABASE|ALLSERVER}

启用触发器语法格式如下。ENABLETRIGGER{ALL|触发器名[,...n]}ON{object_name|DATABASE|ALLSERVER}

SQLServer2008项目实训教程游标

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。即通过游标可对结果集逐行处理。

SQLServer2008项目实训教程游标

游标总是与一条SQL选择语句相关联。游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当对结果集进行处理时，必须声明一个指向该结果集的游标。

SQLServer2008项目实训教程游标的特点允许程序对查询语句返回的记录集中的每一行执行相应的操作，而不是对整个记录集操作；提高对基于游标位置的表中记录的删除和更新；作为数据库管理系统面向行的应用程序之间的桥梁。

SQLServer2008项目实训教程游标的分类

游标分为：T-SQL游标，API服务器游标和客户游标。（1）T-SQL游标由SQLServer服务器实现的游标，主要通过T-SQL脚本、存储过程和触发器将游标传给服务器执行。（2）API游标

API游标支持在OLEDB，ODBC以及DB_library中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API游标函数，MSSQLSEVER的OLEDB提供者、ODBC驱动器或DB_library的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。（3）客户游标客户游标主要是当在客户机上缓存结果集时才使用，是前台游标。在一般情况下，服务器游标能支持绝大多数的游标操作。而客户游标常常被用作服务器游标的辅助。

SQLServer2008项目实训教程游标创建游标的语法格式如下。DECLARE游标名[INSENSITIVE]|SCROLLCURSORFORselect_statement

其中，INSENSITIVE：表示不敏感游标，即游标不会随基本表内容的改变而改变，也无法通过游标更新数据。SCROLL：表示滑动游标，可指定游标的移动方向，包括first、last、prior、next、relative、absolute

SQLServer2008项目实训教程游标打开游标OPEN游标名游标定义后，如要读取数据，则必须先打开游标。提取游标数据

FETCH[next|prior|first|last|absolute{n|@var}|relative{n|@var}]FROM游标名INTO@var注意：执行FETCH语句后，可通过@@FETCH_STATUS全局变量返回游标当前的状态。其有3个返回值：

0——FETCH语句执行成功。

1——FETCH语句执行失败或行数据超过游标结果集范围。

2——表示提取的数据不存在。关闭/释放游标

CLOSE/DEALLOCATE游标名

SQLServer2008项目实训教程游标【例】现需要为“11网络技术”班学生打印所有课程的成绩通知单，样式如下：