数据库原理课件第7章.ppt

第7章 数据库编程 本章目录 n 7.1 t-sql语言 n 7.2 嵌入式sql n 7.3 sql与odbc/ ado 3 n理解t-sql程序设计的基本概念； n熟练掌握t-sql各种流程控制语句的用法； n掌握t-sql提供的常见函数的意义； n理解游标的功能及使用方法； n熟练掌握存储过程的创建及应用； n掌握嵌入t-sql的应用方法； n理解odbc和ado的体系结构及其访问数据库 的方法； 学习目标 n7.1.1 t-sql程序设计 n7.1.2 t-sql提供的函数 n7.1.3 游标 n7.1.4 存储过程 7.1 t-sql语言 1注释语句 在t-sql的编程中，提供有如下两种注释方式 : （1）单行注释：使用两个连在一起的减号- 作为注释符。 （2）多行注释：使用“/* */”作为注释符。 7.1.1 t-sql程序设计 2批处理 n所谓批处理是从客户机传递到服务器上的一组完整的 数据和sql指令。 n在一个批处理中可以包含一条或多条t-sql语句，成 为一个语句组。 n服务器将批处理编译成一个可执行单元，称为执行计 划。 n所有的批处理命令都使用go作为结束的标志。 7.1.1 t-sql程序设计 n执行一个批时，若其中有一个语句出现编译错误， 则sql server将取消该批内所有语句的执行。 n但若一个批通过了编译而在运行时出现错误，则批 内发生错误之前的语句将被执行，发生错误的语句 不被执行，发生错误之后的语句视具体情况可能被 执行或不被执行。 批处理(续) 例7-1选择数据库，创建视图，接着对这个视图进行查 询 use 高校教务信息管理系统 go create view student_view as select * from student go select * from student_view go 7.1.1 t-sql程序设计 3变量 在t-sql语句中变量有局部变量和全局变量 两种。 7.1.1 t-sql程序设计 局部变量是用户自定义的变量。使用范围是定义 它的批、存储过程或触发器。首字母为单个 全局变量由系统定义并维护，通过在名称前面加 符号 3.变量（续） （1）局部变量。 局部变量定义的语法如下： declare 变量名 数据类型， 例7.2 局部变量定义示例。 declare st_age int declare st_name char(10)，type int 局部变量赋值通常用select或set。 赋值方式有直接赋值和间接赋值两种。 直接赋值方式如下： 1. select 变量名=值， 2. set 变量名=值 注：set一次只能为一个变量赋值，而select 可一次为多个变量赋值。 7.1.1 t-sql程序设计 间接赋值是从表中取值赋值给变量，应保证类型一致。 如从表中返回的是多个值时，则取最后一个值赋给变量 。 例7.4 局部变量间接赋值示例。 select st_age=st_age from student where st_no=070302 或 set st_age=(select st_age from student where st_no=070302) 查看变量值，可用如下方法： select 变量名 7.1.1 t-sql程序设计 declare a varchar(8) set a=计算机% select 课程号,授课教师,学分 from kc where 课程名 like a 例如：从kc表中查找课程名中包含计算机的 课程的课程号,授课教师,学分 利用update为局部变量赋值 例 将sell_order表中的transporter_id列值为 “t001”、goods_id列值为“g00003”的 order_num列的值赋给局部变量order_num declare order_num float update sell_order set order_num=order_num*2 where transporter_id=t001 and goods_id=g00003 declare double_age int update 学生表 set double_age=2*(select 年龄 from 学生表 where 姓名=刘进 ) select double_age 将学生表中的刘进的年龄的2倍赋值给 double_age set double_age= 2*年龄 where 姓名=刘进 注意： nselect赋值语句中自动执行许多隐式的数据 转换，但在update中不会自动执行转换。 declare order_num int update sell_order set order_num=order_num*2 where transporter_id=t001 and goods_id=g00003 在sell_order中的order_num列是float类型的 ， order_num是 int类型的，所以会报错 （2）全局变量。全局变量是由系统提供且预先 声明的变量，返回一些系统信息、配置设定值和统 计数据。在使用全局变量时应该注意以下几点： n全局变量是在服务器级定义的。 n用户只能使用预先定义的全局变量。 n引用全局变量时，必须以标记符“”开头。 n全局变量对用户来说是只读的。 n局部变量的名称不能与全局变量的名称相同。 7.1.1 t-sql程序设计 rowcount nrowcount存储前一条命令影响到的记录总 数，除了declare语句之外，其他任何语句都 可以影响rowcount的值。 n例如 select * from student declare rows int select rows= rowcount print rows 9.1.2 常量与变量 error n如果error为非0值，则表明执行过 程中产生了错误，此时应当在程序中采取 相应的措施加以处理。 nerror的值与rowcount一样，会 随着每一条sql server语句的变化而改变 。 例使服务器产生服务，并显示错误号。 raiserror(miscellaneous error message,16,1) if error0 select my_error as last error 运行结果： 服务器: 消息 50000，级别 16，状态 1，行 2 miscellaneous error message last error - 50000 （所影响的行数为 1 行） trancount 记录当前的事务数量，当某个事务当前并 没有结束会话过程时，trancount的值大 于0。 version version的值代表服务器的当前版本和当 前操作系统版本 spid spid返回当前用户进程的服务器进程 id 4流程控制语句 表7-1 常用的流程控制语句 流程控制语句说 明 ifelse条件分支语句 beginend封装sql语句 case多重选择语 句 whilebreak/continue循环控制语句 return无条件返回 waitfor为语句的执行设置延迟 ifnot exists判断是否存在 7.1.1 t-sql程序设计 （1）ifelse语句 例7.6 选课关系中，评价课程号为012”的课程成绩 ，如果平均分在80分以上则在屏幕上打印优秀，否则 打印良好。 if (select avg(st_score) from score where sub_no=012)80 print 优秀 else print 良好 4流程控制语句 9.3.2 选择控制 例 嵌套if.else语句的使用。 if (select sum(order_num) from sell_order )50 print 他们是最佳的客户 else if (select sum(order_num) from sell_order)30 print 必须与他们保持联络 else print 再想想办法吧! （2）beginend语句 用beginend可以将一组sql语句 封装成一个完整的sql语句块，而且允许 嵌套。 4流程控制语句 例7.7 如果课程号“012”的课程平均成绩在80分以上 ，则显示该课程成绩大于平均分的学生学号。 if (select avg(st_score) from score where sub_no=012)80 begin print 优秀学生学号 select st_no from score where st_score ( select avg(st_score) from score where sub_no=012) and sub_no=012 end （4）case分支 case函数可以很方便地实现多重选择的情况 ncase函数用于简化sql表达式，它可以 用在任何允许使用表达式的地方。 n注意：case函数不是语句，它不能单独 执行，而只能作为语句的一部分来使用。 1、简单case函数：将一个测试表达式与一组简单 表达式进行比较，如果某个简单表达式与测试表达式的值 相等，则返回相应结果表达式的值 n命令格式： case 表达式 when 可能的取值 then 相应的结 果 when 可能的取值 then 相应的 结果 else 相应的结果语句 end （4）case函数 9.3.2 选择控制 例 使用简单case函数将goods表中的商品分类重 命名，以使之更易理解。 select case classification_id when p001 then 笔记本计算机 when p002 then 激光打印机 when p003 then 喷墨打印机 when p004 then 交换机 else 没有这种品牌 end as classification, goods_name as goods name, unit_price as price from goods where unit_price is not null 例查询readers表中读者的姓名和该读者的借阅量 select 姓名， 借阅量= case 读者类型 when 3 then 可以借8本书！ when 2 then 可以借20本书！ when 1 then 可以借50本书！ else 无规定！ end from readers n从学生表student中，选取sno，ssex，如果 ssex为m，则显示男，如果为f，则显示女 case函数 select sno, case ssex when mthen 男 when f then 女 end as 性别 from student 命令格式： case when 逻辑表达式 then 相应 的结果 else 相应的结果语句 end 命令说明： when 后面给出用于判断的逻辑表达式 2、case 搜索函数 select goods_name as 商品名称, case when stock_quantity-order_quantity3 and stock_quantity-order_quantity10 then 货物充足 end as 进货判断 from goods 例 根据goods表中库存货物数量与订货量之差， 使用case搜索函数判断该商品是否进货。 从成绩表中查询学号,课程号,成绩,成 绩等级，其中成绩为空，成绩等级为 缺考；成绩小于60，成绩等级为不及 格；6070分，成绩等级为及格；依 次为中等，良好，优秀等 use 成绩管理 select 学号,课程号,成绩,成绩等级= case when 成绩 is null then 缺考 when 成绩=60 then 及格 when 成绩=70 then 中等 when 成绩=80 then 良好 when 成绩=90 then 优秀 end from 成绩 （3）whilebreak/continue语句 例7.8 在选课关系中，如果课程平均成绩小于80，则 将成绩循环自动加2，直到平均成绩大于80，跳出循环 。 while (select avg(st_score) from score)80 break -退出循环体 end 7.1.1 t-sql程序设计 while (select avg(stock_quantity) from goods)25 begin print 库存太多了 break end else continue end break或continue语句 9.3 程序控制流语句 declare s int,n int,t int,c int select s=0,n=1 while n , .n as begin 函数体 return 标量表达式 end 例7.13计算长方体体积 create function cube (cubel decimal(4,1), cubew decimal(4,1), cubeh decimal(4,1) ) returns decimal(12,3) as begin return (cubel * cubew * cubeh) end 查询结果的方法如下: select cube(2,3,5) 删除cube函数语法: drop function cube 7.1.2 t-sql提供的函数 9.2.2 用户定义函数 内嵌表值函数的语法格式： create function 所有者.函数名 ( 参数名 as 数据类型 =default ,.n ) returns table with , .n as return ( select 语句 ) 例创建用户定义函数goodsq，返回输入商品编 号的商品名称和库存量。 create function goodsq(goods_id varchar(30) returns table as return ( select goods_name,stock_quantity from goods where goods_id =goods_id) 自定义函数 创建一个用户自定义函数，从readers表中返回某读者 （根据读者编号）借书的数量（已借数量）。 create function books_num( bh char(10) ) returns int begin declare num int select num=已借数量 from readers where 读者编号=bh return num end 该函数可以用以下语句来调用： select dbo.books_num(2004060003) 7.1.3 游标 1.游标的作用 n游标提供了一种对从表中检索出的数据进行操作的灵 活手段 n游标实际上是一种能从包括多条数据记录的结果集中 每次提取一条记录的机制。 n游标总是与一条sql选择语句相关联。 n游标允许应用程序对查询语句select返回的行结果 集中每一行进行相同或不同的操作，而不是一次对整 个结果集进行同一种操作。 表一 originsalary 表二 addsalary 要求：o_salary=o_salary+a_salary 游标的使用，必须按其生命周期进行。其生命 周期包括定义游标、打开游标、存取游标数据、 关闭游标和释放游标。与c语言中的文件极为相 似： （1）定义游标 创建文件。 （2）打开游标打开文件。 （3）存取游标数据存取文件。 （4）关闭游标关闭文件。 （5）释放游标删除文件。 sql-92游标定义格式 2.声明游标: declare 游标名 insensitive scroll cursor for select 语句 for read only |update of 字段名1 ， ninsensitive:游标不会随着基本表内 容的改变而改变，同时也无法通过游标 来更新基本表。 n游标的移动只能是next 。 参数说明： 参数说明： nscroll:表示可以使用相应的关键字指定游标 的移动位置，否则游标的移动只能是next nread only：禁止通过该游标进行更新 nupdate:定义游标中可更新的列 声明游标 9.4.2 声明游标 scroll的取值 scroll选项含 义 first提取游标中的第一行数据 last提取游标中的最后一行数据 prior提取游标当前位置的上一行数据 next提取游标当前位置的下一行数据 relative n提取游标当前位置之前或之后的第n行 数据(n为正表示向后，n为负表示向 前) absulute n 提取游标中的第n行数据 9.4.2 声明游标 例 使用sql-92标准的游标声明语句声明一个游 标，用于访问sales数据库中的goods表的信息 。 use sales go declare goods_cursor cursor for select * from goods for read only 声明游标 7.1.3 游标 3打开游标 open 游标名 4读取游标 fetch next |prior|first|last |absolute n |relative n from 游标名 | 游标变量名 into 变量名 ， 表 fetch_status变量 返回值描 述 0fetch命令已成功执行 -1fetch命令失败或者行数据己 超出了结果集 -2所读取的数据已经不存在。 fetch_status:返回最后一条fetch语句的 状态 读取游标 9.4.3 使用游标 open cur_customer fetch next from cur_customer while fetch_status = 0 begin fetch next from cur_customer end 例 打开声明的游标，读取游标中的数据。 /*取第一个数据行*/ /* 检查fetch_status是否还有数据可取*/ 5.关闭游标 close 游标名 n注：游标在关闭后，仍可用open语句打开继续读 取数据行。 6. 删除游标 deallocate cursor 游标名 n注：一旦某个游标被删除，在重新打开之前，必 需再次对其进行声明。 游标的应用 1. 用游标修改和删除表数据 n定位修改游标数据的语法格式为： update table_name set column_name=expression | default | null , . n where current of 游标名 n删除游标数据的语法格式为： delete from table_name where current of 游 标名 游标的应用 9.4.4 游标的应用例-37 定义游标cur_customer，通过cur_customer更新 customer表中的customer_name和linkman_name列。 declare cur_customer cursor for select * from customer for update of customer_name,linkman_name open cur_customer fetch next from cur_customer update customer set customer_name=南方体育用品公司 ,linkman_name=李强 where current of cur_customer close cur_customer 9.4.4 游标的应用 n若要删除customer表的一行数据，则使 用以下命令替换上例中的update语句， 就可以删除通过游标读入的一行数据。 delete from customer where current of cur_customer declare xs cursor for select * from 学生表 for update of 年龄 open xs fetch absulute 4 from xs update 学生表 set 年龄=年龄-1 where current of xs close xs deallocate xs 阅读程序 表一 originsalary 表二 addsalary 要求：o_salary=o_salary+a_salary create procedure pk_salaryadd as declare o_id nvarchar(20),a_salary float declare mycursor cursor for select o_id,a_salary from addsalary open mycursor fetch next from mycursor into o_id,a_salary while(fetch_status = 0) begin update originsalary set o_salary=o_salary+a_salary where o_id=o_id fetch next from mycursor into o_id,a_salary end close mycursor deallocate mycursor 要求：o_salary=o_salary+a_salary 7.1.4 存储过程 存储过程（stored procedure）是一组经编译后 存储在数据库内为了完成特定功能的sql语句集。 1存储过程的优点 （1）提高运行速度。 （2）增强了sql的功能和灵活性。 （3）有助于模块化。 （4）提高代码的优化率和可读性。 （5）减少网络流量。 （6）有助于提高安全性。 7.1.4 存储过程 2存储过程的类型 （1）系统存储过程。以sp_开头，用来进行系统 各项设定。 （2）扩展存储过程。以xp_开头，用来调用操作 系统提供的功能。 （3）用户自定义的存储过程。是由用户创建并 能完成某一特定功能的存储过程。它处于用户创建 的数据库中，存储过程名前没有前缀sp_。 create procedure procedure_name 参数变量 类型，n -输入参数 参数变量 类型 output -输出参数 with recompile | encryption | recompile, encryption as sql_statement ,.n 1. 创建存储过程的语法格式如下： 3.创建存储过程 注：输出参数需要用关键定output标明，输入参数 可以定义多个，但输出参数只能一个。 7.1.4 存储过程 例7.16 创建一存储过程实现输入系号时显示该系人 数。 create proc stucount_proc departid varchar(2) stucount int output as select stucount=count(*) from student where st_depid=departid 执行存储过程 n执行存储过程的语法格式： execute return_status= procedure_name 参数变量 = value| variable output| default ,.n with recompile 执行存储过程： exec borrowed_books 王飞 或 borrowed_books 王飞 或 declare a varchar(10) set a =王飞 execute borrowed_books a 执行存储过程命令如下： declare total int exec stucount_proc02, total output 4存储过程的修改 当存储过程需要修改时，可以使用以下语句进行修改 ： alter proc过程名 参数 as sql语句 5存储过程的删除 如果不再使用一个存储过程时，就要把它从数据库中 删除。在此之前，必须确认该存储过程没有任何依赖 关系。 存储过程的删除语法如下： drop proc 过程名 7.1.4 存储过程 7.2 嵌入式sql 在实际的应用中，使用编程语言访问数据库仍是必要的。在 编写访问数据库的程序时，需要将sql语言直接嵌入到程序的源 代码中，与其他程序设计语言语句混合。这种方式下使用的sql 语言称为嵌入式sql（embedded sql），而嵌入sql的程序语 言称为宿主语言。 n 7.2.1 嵌入式sql的实现方法 n7.2.2 嵌入式sql的使用规定 n7.2.3 嵌入sql语句 n7.2.4 嵌入式sql语句连接数据库 n7.2.5 嵌入式sql的应用举例 7.2.1 嵌入式sql的实现方法 sql语言提供了以下2种不同的使用方式： n（1）在终端交互式方式下使用，前面章节 介绍的就是作为独立语言由用户在交互环境 下使用的sql语言，简称为交互式sql。 n（2）将sql嵌入到宿主语言中混合编程， sql语句负责操纵数据库，宿主语言负责控 制程序流程和处理数据。嵌入式sql的实现 有两种处理方式：一种是扩充宿主语言的编 译程序，使之能处理sql语句；另一种采用 预处理方式。目前多数系统采用后一种方式 。 在嵌入式sql中，为了能够区分sql语句与主语言语句，所 有sql语句都必须加前缀exec sql。 以c或pl/sql作为主语言的嵌入式sql语句的语法如下： exec sql； 嵌入式sql与宿主语言之间的信息传递是通过宿主语言程序 变量（简称主变量host variable）来实现的。 将sql嵌入到宿主语言中混合编程，而程序中含有两种不同 计算模型的语句，一种是描述性的面向集合的sql语句，一种 是过程性的宿主语言语句。两种语言的分工是：sql语句负责 操纵数据库，宿主语言语句负责控制程序流程。 7.2.2 嵌入式sql的使用规定 7.2.3 嵌入sql语句 表7-6 嵌入式sql语句 begin declare sectionprepare close*select into* connect toset ansi_defaults declare cursor*set concurrency delete (positioned)*set connection delete (searched)*set cursor_close_on_commit describeset cursortype disconnectset fetchbuffer end declare sectionset option execute*set scrolloption execute immediateupdate (positioned)* fetch*update (searched)* get connectionwhenever open* 7.2.4 嵌入式sql语句连接数据库 在嵌入式sql程序中，使用“connect to”语句来连接数据库。 该语句的完整语法如下： connect to server_name.database_name as connection_name user login.password | $integrated 其中，server_name为服务器名；database_name为数据库名； connection_name为连接名，可省略（如果仅仅使用一个连接，那 么无需指定连接名，可以使用set connection来使用不同的连 接）；login为登录名；password为登录密码。 7.2.5 嵌入式sql的应用举例 例7.19 查询lastname为“white”的firstname的信息。 main() exec sql begin declare section； char first_name50； char last_name = “white”； exec sql end declare section； exec sql connect to teacher.pubs user sa.password； exec sql select au_fname into :first_name from authors where au_lname = :last_name； printf(“first name: %sn”,first_name)； return (0)； odbc/ ado是为各种高级程序或应用系统 提供 标准sql数据访问的中间件。 n7.3.1 odbc n7.3.2 ado 7.3 sql与odbc/ ado odbc，即开放式数据库连接（open database connectivity）， 是为用户提供简单、标准、透明的数据库连接的公共编程接口。 odbc数据库应用程序 驱动程序管理器 sql server 驱动程序 sql server 数据源 oracle 驱动程序 oracle 数据源 db2 驱动程序 db2 数据源 sybase 驱动程序 sybase 数据源 dbdbdbdb 1. odbc分层体系结构 7.3.1 odbc odbc的主要功能 （1）odbc数据库应用程序。用宿主语言和odbc函数编写的应用程序用于访问数据 。 包括调用odbc函数，递交sql语句给dbms，检索结果并进行处理。 （2）驱动程序管理器。它是一个动态链接库（dll），为应用程序加载、调用和卸载 db驱动程序。 （3）db驱动程序。应用程序通过驱动程序实现对数据源的各种操作，包括处理 odbc函数，向数据源提交用户请求执行的sql语句。 （4）数据源。db驱动程序与dbs之间特殊连接的命名，可代表用户名、服务器名和 所连接的数据库名等，可看成是与一个具体数据库建立的连接。 概括起来，odbc具有以下灵活的特点： （1）使用户程序有很高的互操作性，相同的目标代码适用于不同的dbms； （2）由于odbc的开放性，它为程序集成提供了便利，为客户机/服务器结构提供了技 术支持； （3）由于应用与底层网络环境和dbms分开，简化了开发维护上的困难。 7.3.1 odbc 2odbc接口 odbc接口由一系列的调用函数组成。应用程序在数据请求时一般分3个阶段来使用 odbc，即初始化阶段、sql处理阶段和终止阶段。每一阶段都要使用一些函数。 7.3.1 odbc 3odbc的应用程序实例 例7.20 使用odbc函数进行student表的查询实例。 #include #include #include #include #include main() henv henv; -定义环境句柄 hdbc hdbc; -定义连接句柄 hstmt hstmt; -定义语句句柄 retcode retcode; -返回变量 sqlallocenv( -分配环境句柄 sqlallconnect(henv, -数据源dns名 char* szuid=“sa”; -用户名 char* szauthstr=“sa”; -密码 sqlconnect(hdbc,(sqlchar*)szdns,(sword)strle n(szdns),(sqlchar*)szuid,(sword)strlen(szuid),( sqlchar*szauthstr,(sword)strlen(szauthstr); -连 接数据源 sqlallocstmt(henv, -直接执行sql while(retcode_is_successful(retcode) retcode=sqlfetch(hstmt); -移动游标