实验4 T-SQL语言.doc

实验4 T-SQL语言实验目的：1、 掌握Transact-SQL语言中的变量、运算符、函数；2、 掌握Transact-SQL语句编写批处理和流程控制程序。3、 掌握游标的创建，打开，数据读取，关闭，销毁的基本方法实验内容和步骤：Transact-SQL语言是在SQL Server 2005 中使用的程序设计语言。不但包含了标准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 int SELECT 选课数 =COUNT(cno) FROM SC WHERE Sno=xuehao RETURN 选课数ENDGO-使用自定义函数，求选课表（SC）中每位学生的选课数SELECT DISTINCT Student.Sno,Sname,选课数=dbo. 函数_学生选课数(SC.Sno)FROM Student,SCWHERE Student .Sno=SC.SnoGO实验内容10: (练习，自己试着写一下)练习自定义函数，求每门课程的选课人数提示：USE teachGOCREATE FUNCTION 函数_选课人数(cno AS char(9) )RETURNS int BEGIN DECLARE 选课人数 AS int SELECT 选课人数 =COUNT(sno) FROM SC WHERE cno=cno RETURN 选课人数ENDGO-使用自定义函数，查询每门课的选课人数（注：没有人选的课程，不会出现）SELECT DISTINCT Course.Cno ,Cname ,选课人数=dbo. 函数_选课人数(SC.Cno)FROM Course,SCWHERE Co=SC.Cno GO-使用自定义函数，通过左外连接，保留了左边被舍弃的元组SELECT DISTINCT Course.Cno ,Cname ,选课人数=dbo. 函数_选课人数(SC.Cno)FROM Course left join SC on Co=SC.Cno GO2. 游标的创建和使用游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。T-SQL游标：该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。使用游标要遵循以下顺序：声明游标打开游标读取数据关闭游标删除游标。1声明游标实验内容1：以下是一个符合SQL-92标准的游标声明，此游标是只读的，只能从头到尾顺序提取数据。 DECLARE S_CUR1 CURSOR FOR SELECT * FROM STUDENT FOR READ ONLY实验内容2：以下是一个符合T-SQL扩展的游标声明，此游标是动态游标，可前后滚动，可对姓名列进行修改。 DECLARE S_CUR2 CURSOR SCROLL DYNAMIC FOR SELECT * FROM STUDENTFOR UPDATE OF SName2. 打开游标声明游标之后，要使用游标提取数据，必须先打开游标。使用OPEN语句可以打开游标。打开游标后，可以使用全局变量CURSOR_ROWS查看游标中数据行的数目。实验内容3：打开游标S_CUR1，输出其行数 OPEN S_CUR1 SELECT 游标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_CUR13. 收获体会此次实验与之前的几次试验都大有所不同，此次实验的内容主要是关于Transact-SQL语言中的变量、运算符、函数以及游标的相关操作。对于