数据库第3次上机

实验6 T-SQL语言实验目的：1、掌握Transact-SQL语言中的变量、运算符、函数；2、掌握Transact-SQL语句编写批处理和流程控制程序。

3、掌握游标的创建，打开，数据读取，关闭，销毁的基本方法实验内容和步骤：首先完成数据库和表的创建（具体代码，见“建库.sql”，可用文本编辑器打开）。

具体操作：首先在D盘下新建文件夹T estDB，打开“查询分析器”->点击工具栏中的图标->找到“建库.sql”，打开 ->执行。

即可得到实验所需数据库和数据。

Transact-SQL语言是在SQL Server 2000 中使用的程序设计语言。

不但包含了标准SQL 语言部分，而且为了满足实际应用中的编程需要，在Transact-SQL语言中还另外增加了一些语言要素，它们包括注释、变量、运算符、函数和流程控制语句等。

所有的语句都在查询分析器中执行。

1. Transact-SQL语句实验内容1：返回在程序执行过程中上一条SQL语句影响的记录数（全局变量@@ROWCOUNT），注意观察结果窗口。

use teach--打开teach 数据库GO--查询学生表中的记录SELECT * FROM Student--查询上一条SELECT语句影响的记录数SELECT @@ROWCOUNT AS 第一次查询返回的记录数GO实验内容2：查询学生表的人数，将返回的记录数赋给局部变量@RowsUSE teachGODECLARE @Rows int--声明局部变量--给局部变量赋值SET @Rows=(SELECT COUNT(*) FROM Student)--显示局部变量的值SELECT @Rows AS SELECT返回的记录数GO实验内容3：设置一个局部变量为表，进行插入和查询操作-- 定义变量为临时表DECLARE @Table_Example TABLE(Col_num int PRIMARY KEY,Col_text char (50))-- 向该变量中添加两行内容INSERT INTO @Table_Example VALUES (1, 'this is a')INSERT INTO @Table_Example VALUES (2, 'table data type example')-- 从临时表中查询SELECT * FROM @Table_ExampleGO实验内容4：(练习，自己试着写一下)设置一个局部变量其值为选修了课程的人数，并显示提示：DECLARE@Rows int--声明局部变量--给局部变量赋值SET@Rows=(SELECT COUNT(distinct sno)FROM SC)--显示局部变量的值SELECT@Rows AS选课人数GO实验内容5：检查sysdatabases中的每一个数据库，使用数据库标识号来确定数据库名称。

USE master--打开master 数据库GO--DB_NAME 是一个函数，功能是根据数据库标识号，来返回数据库的名称SELECT dbid , DB_NAME(dbid) AS DB_NAMEFROM sysdatabases ORDER BY dbidGO实验内容6：从数据库中返回学生表的第2列的名称USE teachGO--定义局部变量，用于存放学生表的ID号DECLARE @学生表ID INT--OBJECT_ID是一个函数，功能是返回数据库对象的编号SET @学生表ID= OBJECT_ID('Student')--COL_NAME是一个函数，返回指定表中指定字段的名称，即列名SELECT COL_NAME(@学生表ID,2) AS 学生表中第2列的名称GO实验内容7：简单年、月、日函数SELECT MONTH('03/12/1998'), DAY('03/12/1998'),YEAR('03/12/1998')实验内容8：从系统当前日期中返回系统当前的年份数、月份数和天数。

-- GETDATE()函数返回系统当前日期SELECT YEAR(GETDATE()) AS 当前年份,MONTH(GETDATE()) AS 当前月份,DAY(GETDATE()) AS 当前天数GO实验内容9：自己编写函数。

编写一个函数，名称为“函数_学生选课数”，要求根据输入的学生学号，求此学生的选课数。

USE teachGO--通过CREATE FUNCTION语句创建用户的自定义函数“函数_学生选课数”CREATE FUNCTION 函数_学生选课数(@xuehao AS char(9) --函数的输入参数)RETURNS int --返回结果类型BEGIN --函数体DECLARE @选课数 AS intSELECT @选课数 =COUNT(cno) FROM SC WHERE Sno=@xuehaoRETURN @选课数ENDGO--使用自定义函数，求选课表（SC）中每位学生的选课数SELECT DISTINCTStudent.Sno,Sname,选课数=dbo. 函数_学生选课数(SC.Sno) FROM Student,SCWHERE Student .Sno=SC.SnoGO实验内容10: (练习，自己试着写一下)练习自定义函数，求每门课程的选课人数提示：USE teachGOCREATE FUNCTION函数_选课人数(@cno AS char(9))RETURNS intBEGINDECLARE@选课人数AS intSELECT@选课人数=COUNT(sno)FROM SC WHERE cno=@cnoRETURN@选课人数ENDGO--使用自定义函数，查询每门课的选课人数（注：没有人选的课程，不会出现）SELECT DISTINCTo,Cname,选课人数=dbo.函数_选课人数(o) FROM Course,SCWHERE o=oGO--使用自定义函数，通过左外连接，保留了左边被舍弃的元组SELECT DISTINCTo,Cname,选课人数=dbo.函数_选课人数(o) FROM Course left join SC on o=oGO2. 游标的创建和使用游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。

我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

T-SQL游标：该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。

Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。

使用游标要遵循以下顺序：声明游标→打开游标→读取数据→关闭游标→删除游标。

1．声明游标实验内容1：以下是一个符合SQL-92标准的游标声明，此游标是只读的，只能从头到尾顺序提取数据。

DECLARE S_CUR1 CURSORFORSELECT * FROM STUDENTFOR READ ONLY实验内容2：以下是一个符合T-SQL扩展的游标声明，此游标是动态游标，可前后滚动，可对姓名列进行修改。

DECLARE S_CUR2 CURSORSCROLL DYNAMICFORSELECT * FROM STUDENTFOR UPDATE OF SName2. 打开游标声明游标之后，要使用游标提取数据，必须先打开游标。

使用OPEN语句可以打开游标。

打开游标后，可以使用全局变量@@CURSOR_ROWS查看游标中数据行的数目。

实验内容3：打开游标S_CUR1，输出其行数OPEN S_CUR1SELECT '游标s_cur1的数据行数'=@@CURSOR_ROWS注意：通过联机丛书来查询@@CURSOR_ROWS，来理解其值的含义3．读取数据游标打开后，就可以使用FETCH命令从中读取数据。

具体语句格式见联机丛书。

实验内容4：从游标S_CUR1中提取数据，只能用NEXT提取数据。

FETCH NEXT FROM S_CUR1提示：选中该语句，点击执行，查看结果。

再点击执行，查看结果。

重复执行。

实验内容5：从游标S_CUR2中提取数据：OPEN S_CUR2--读取游标第一行FETCH FIRST FROM S_CUR2--读取下一行FETCH NEXT FROM S_CUR2--读取上一行FETCH PRIOR FROM S_CUR2--读取最后一行FETCH LAST FROM S_CUR2--读取当前行的上二行FETCH RELATIVE-2 FROM S_CUR24．关闭游标游标使用完后，要及时关闭游标，以释放当前的结果集并解除定位在该游标记录行上的游标锁定。

实验内容6：关闭游标S_CUR1CLOSE S_CUR15．删除游标关闭一个游标后，其数据结构仍存储在系统中，需要的时候仍然可以再次使用OPEN 语句打开和使用该游标。

如果确定以后不再使用该游标，则可以删除游标，将游标占用的系统空间释放出来。

实验内容7：删除游标S_CUR1DEALLOCATE S_CUR1实验7 存储过程实验目的：1、理解存储过程的概念，掌握各种存储过程的创建方法。

2、掌握查看、修改和删除存储过程的方法。

3、掌握执行存储过程的方法。

实验内容：存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句。

在Sql Server2000中，可以使用三种方法创建存储过程。

1）使用创建存储过程向导创建存储过程。

2）使用企业管理器创建存储过程。

3）使用T-SQL语句中的CREA TE PRODCECURE命令创建存储过程。

具体步骤：打开查询分析器⏹首先创建数据库和表⏹创建存储过程⏹执行存储过程，查看结果我们在这里掌握第三种方法，其他方法自行掌握。

实验内容1：简单的存储过程。

功能为：查询student表中的内容Create procedure query1_sAsSelect * from studentgo执行语句为：exec query1_s实验内容2：带参数的存储过程。

带了一个参数@s_sno，功能为：查询指定学生的信息Create procedure query2_s@s_sno char(9)AsSelect * from student where sno=@s_snogo执行该存储过程的2种不同方法：1）exec query2_s'201010001'2）exec query2_s @s_sno ='201010001'实验内容3：带输入和输出参数的存储过程既有输入参数也有输出参数。