数据库基础(六):T-SQL语言程序设计、游标定义与使用及存储过程创建与信息查找

简介: 数据库基础(六):T-SQL语言程序设计、游标定义与使用及存储过程创建与信息查找


  系列文章中示例数据来源于《SQL Server实验指导(2005版)》一书。尊重版权,因此遗憾不能将相关示例数据一并提供给大家;但是依据本系列文章的思想与对操作步骤、代码的详细解释,大家用自己手头的数据,可以将相关操作与分析过程加以完整重现。

1 计算1-100间所有可被3整除的数的个数与总和

(1) 启动Microsoft SQL Server 2008 R2软件;

(2) 在“对象资源管理器”窗格中,在“数据库”处右键,在弹出的菜单中选择“附加”选项;

(3) 选择需要加以附加的jxsk数据库物理文件,选择定位文件夹“G:\sql\chutianjia sql”并选择对应数据库jxsk的物理文件并选择“确定”按钮,再次选择“确定”即可;

(4) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

DECLARE @SUM SMALLINT, @I SMALLINT,@NUMS SMALLINT
SET @SUM=0
SET @I=1
SET @NUMS=0
WHILE (@I<=100)
BEGIN
IF (@I% 3=0)
BEGIN 
SET @SUM=@SUM+@I
SET @NUMS=@NUMS+1
END
SET @I=@I+1
END
PRINT'总和是'+STR(@SUM)
PRINT'个数是'+STR(@NUMS)

(5) 单击“工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

2 从学生表S中选取SNO、SN、SEX,若为“男”输出M,为“女”输出F

(1) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

USE jxsk
GO
SELECT SNO AS 学号, SN AS 姓名,
性别=
CASE SEX
WHEN '男' THEN 'M'
WHEN '女' THEN 'F'
END
FROM S
GO

(2) 单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(3) 首次运行后发现结果中“性别”一栏均为“NULL”,与预期将达到的结果不一致。通过检查发现自己的T-SQL语句中出现错误,更改后效果如下图;

3 面向复杂应用的T-SQL程序设计方法——查询所有同学选课信息:姓名、课程名、成绩

(1) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

USE jxsk
GO
SELECT SN AS 姓名, CN AS 课程名,
成绩=
CASE
WHEN SCORE IS NULL THEN '未考'
WHEN SCORE<60 THEN '不及格'
WHEN SCORE>=60 AND SCORE<70 THEN '及格'
WHEN SCORE>=70 AND SCORE<80 THEN '中'
WHEN SCORE>=80 AND SCORE<90 THEN '良好'
WHEN SCORE>=90 THEN '优'
END 
FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO
ORDER BY S.SNO,C.CNO,SCORE DESC
GO

(2) 单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(3) 首次运行后发现系统报错。通过检查发现自己的T-SQL语句中出现错误,即丢失了CASE,更改后效果如下图;

4 面向复杂应用的T-SQL程序设计方法——为教师增加工资

(1) 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.T”,右击并在弹出的窗口中选择“编辑前200行”;查看各教师的工资情况;

(2) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

USE jxsk
UPDATE T SET SAL=SAL+
CASE
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (SAL+COMM)>=4000
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 300
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (SAL+COMM)>=3000 AND (SAL+COMM)<4000
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 200
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (T.SAL+T.COMM<3000)
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 100
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO
GROUP BY TC.TNO HAVING COUNT(*)=1) THEN 50
ELSE 0
END
GO

(3) 单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(4) 首次运行后发现系统报错。通过系统内部提示,考虑到该错误应为上节实验课所设立的触发器对该数据库表修改加以限制,使得语句无法执行;

(5) 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.T”→“触发器”,右键选中已存在的触发器,在弹出的对话框中选择“禁用”或“删除”;考虑到今后实验可能仍然会使用到这一触发器,我选择了“禁用”按钮,如下图;

(6) 更改完毕后单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(7) 在“对象资源管理器”中选择“数据库”→“jxsk”→“表”→“dbo.T”,右击并在弹出的窗口中选择“编辑前200行”;查看各教师的工资情况已发生变化,如下图;

5 使用游标——定义游标Cursor_Famale

(1) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

DECLARE @SNO CHAR(6),@SNAME CHAR(8),@SEX CHAR(2),
@AGE TINYINT,@DEPT CHAR(10)
DECLARE CURSOR_FAMALE CURSOR
FOR SELECT SNO,SN,SEX,AGE,DEPT FROM S
OPEN CURSOR_FAMALE
FETCH NEXT FROM CURSOR_FAMALE
INTO @SNO,@SNAME,@SEX,@AGE,@DEPT
WHILE @@FETCH_STATUS=0
BEGIN
IF @SNAME='牛莉'
BEGIN
PRINT '找到牛莉的信息如下'
PRINT @SNO+''+@SNAME+''+@SEX+''+
CONVERT (CHAR(2),@AGE)+''+@DEPT
BREAK
END
FETCH NEXT FROM CURSOR_FAMALE
INTO @SNO,@SNAME,@SEX,@AGE,@DEPT
END
IF @@FETCH_STATUS !=0
PRINT '很抱歉,没有找到牛莉的信息!'
CLOSE CURSOR_FAMALE
DEALLOCATE CURSOR_FAMALE

(2) 单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(3) 由于我的数据库表S中并没有学生“牛莉”的信息,因此在执行上述语言后系统提示“很抱歉,没有找到牛莉的信息!”;

(4) 对数据库表S中信息加以修改,增添学生“牛莉”的信息后,单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

6 使用游标——创建存储过程Pro_C查找信息

(1) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

CREATE PROCEDURE PRO_C @C_CURSOR CURSOR VARYING OUTPUT
AS
SET @C_CURSOR = CURSOR
FOR
SELECT SNAME,SCORE FROM STUDENT,SC,COURSE
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库'
OPEN @C_CURSOR

(2) 单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(3) 首次运行后发现系统报错。通过系统内部提示,考虑到该错误应为@符号后的空格导致,修改后单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(4) 单击屏幕上方 “工具栏”菜单中的“新建查询”按钮,打开“查询编辑器”窗口,并在“查询编辑器”窗口中输入以下T-SQL语句:

DECLARE @MYCURSOR CURSOR
DECLARE @NAME VARCHAR(30)
DECLARE @IN_NAME CHAR(8)
DECLARE @SCORE INT
SELECT @IN_NAME = '王一山'
EXECUTE PRO_C @C_CURSOR = @MYCURSOR OUTPUT
FETCH NEXT FROM @MYCURSOR INTO @NAME,@SCORE
WHILE (@@FETCH_STATUS=0)
BEGIN
IF @NAME=@IN_NAME
BEGIN
PRINT @NAME+'选修了数据库课程,成绩是:'+CONVERT(CHAR(2), @SCORE)
BREAK
END
FETCH NEXT FROM @MYCURSOR INTO @NAME, @SCORE
END
IF (@@FETCH_STATUS!=0)
PRINT @IN_NAME+'没有选修数据库课程。'
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR

(5) 单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(6) 首次运行后发现系统报错。在语句处搜寻、更改但仍有错误,且错误甚至越来越多,如下图;

(7) 此时利用系统错误提示,考虑到可能是上述存储过程Pro_C构建出现错误;返回并对这一步骤加以检查,发现其T-SQL语言中数据库表与我个人数据库表名称、列名有不一致的地方,对其加以修改并单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;其中,修改之后的T-SQL语言为:

CREATE PROCEDURE PRO_C @C_CURSOR CURSOR VARYING OUTPUT
AS
SET @C_CURSOR = CURSOR
FOR
SELECT SN,SCORE FROM S,SC,C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CN='数据库'
OPEN @C_CURSOR

(8) 更改错误后发现仍有错误——存储过程Pro_C本已在上述步骤中建立完成,不可重复建立。因此在“对象资源管理器”中选择“数据库”→“jxsk”→“可编程性”→“存储过程”中选择Pro_C并右键,在弹出的窗口中选择“删除”;

(9) 删除后单击 “工具栏”中的“执行(x)”按钮,即可执行上述T-SQL语句,如下图;

(10) 此时再对“王一山”的信息加以查询,即可成功实现,如下图;

(11) 由于我的数据库表S中并没有学生“牛莉”的信息,因此在执行上述语言后系统提示“没有选修数据库课程。”;我在S表、SC表增加了王一山及其选课数据,如下图;再次查询实现如下结果,如下下图;

欢迎关注CSDN/公众号/知乎/微博:疯狂学习GIS


相关文章
|
12天前
|
SQL 存储 监控
|
18天前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(下)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
25 1
|
10天前
|
监控 关系型数据库 Java
|
19天前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(中)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
29 1
|
6天前
|
关系型数据库 MySQL Go
Go语言介绍以及如何在Go语言中操作MySQL数据库
Go语言介绍以及如何在Go语言中操作MySQL数据库
21 3
|
10天前
|
SQL 运维 关系型数据库
|
15天前
|
Oracle 关系型数据库 MySQL
深入了解 Linux 命令 `db_stat`:数据库统计信息的获取
本文模拟了 Linux 环境下使用 `db_stat` 命令获取数据库统计信息的场景,实际上`db_stat`是特定数据库系统的自定义工具。文章通过示例展示了如何针对Oracle、PostgreSQL和MySQL使用各自内置命令收集统计信息,强调了权限、性能影响和数据实时性等因素,并指出这些信息对优化数据库管理至关重要。
|
12天前
|
SQL 缓存 关系型数据库
✅分析SQL执行计划,我们需要关注哪些重要信息
SQL执行计划解析:12个关键字段详解,包括id(操作标识)、select_type(操作类型)、table(涉及表)、partitions(分区)、type(索引类型)、possible_keys(可能的索引)、key(使用索引)、key_len(索引长度)、ref(比较对象)、rows(扫描行数)、filtered(过滤比例)和Extra(额外信息)。类型从优至劣:system&gt;const&gt;eq_ref&gt;ref&gt;range&gt;index&gt;ALL。
|
12天前
|
JavaScript 关系型数据库 MySQL
Python实战:从猎聘网获取职位信息并存入数据库
Python实战:从猎聘网获取职位信息并存入数据库
|
17天前
|
存储 NoSQL Go
轻松上手,使用Go语言操作Redis数据库
轻松上手,使用Go语言操作Redis数据库

热门文章

最新文章