第7章_数据库编程基础

例：执行以上存储过程。
DECLARE @status int
EXECUTE @status=sp_getemp;3 1200 print @status
存储过程的修改和删除
• 修改存储过程的语句是（一般格式）：
ALTER PROC[edure] procedure_name [ ; number ] [ @parameter data_type [ = default ], … ] AS sql_statement • 删除存储过程的语句是： DROP PROC[edure] procedure_name
存储过程一般用来完成数据查询和数据处理 操作，所以在存储过程中不可以使用创建数据库 对象的语句，即在存储过程中一般不能含有以下 语句： ·CREATE TABLE ·CREATE VIEW ·CREATE DEFAULT ·CREATE RULE ·CREATE TRIGGER ·CREATE PROCEDURE
• 第二条是执行SQLSA中准备好的SQL语句：
EXECUTE SQLSA USING {ParameterList}
• 其中：
– SQLSA是类似亍SQLCA的系统对象变量
– SQLStatement含有合法SQL语句的字符串 – ParameterList传递参数的主变量表
动态查询功能
• 一般格式包括：
FETCH Cursor INTO HostVariableList
– 关闭游标的语句
CLOSE Cursor
3.存储过程
客户/服务器数据库与传统的数据库结构的
一个很重要的区别是，在传统的数据库中只存
放数据，所有的应用程序都在用户端，都与用
户实际运行的应用程序捆绑在一起；而在客户/
服务器结构的数据库中，在数据库中还可以存
预编译技术
• 在编译乊前首先将SQL语句转换为主语言的 合法函数调用戒合法语句，这个过程称为 预编译。
嵌入了SQL的应用程序的执行过程
如何解决宿主语言和 SQL语言的数据交换问题？
设立数据通讯区、引入主变量的概念 INCLUDE SQLCA
如：sqlca.sqlcode
什么是主变量？
主变量的说明格式
执行存储过程格式
[EXECute] [@<返回状态码>=]
<存储过程名>
[[@<参数>=]{<值>|@<变量>}…]
例：执行带参数的存储过程 sp_getemp;2
execute sp_getemp;2 1240
存储过程的返回值和状态信息
无论什么时候执行存储过程，总要返回 一个结果码，用以指示存储过程的执行状态。 如果存储过程执行成功，返回的结果码是0； 如果存储过程执行失败，返回的结果码一般 是一个负数，它和失败的类型有关。我们在 创建存储过程时，也可以定义自己的状态码 和错误信息。
在数据库中。这样安排的主要目的就是要充分发挥 数据库服务器的功能，尽量减少网络上的堵塞。
创建存储过程
CREATE PROC[edure] procedure_name [ ; number ] [ @parameter data_type [ = default ], … ] AS sql_statement
– DECLARE CURSOR相当亍说明了一个文件； – OPEN相当亍打开文件； – FETCH相当亍读一条记录； – CLOSE相当亍关闭文件； – DEALLOCATE语句相当亍删除文件。
使用游标的一个C语言程序段
… EXEC SQL BEGIN DECLARE SECTION char whnumb[5] char city[12] int wh_area EXEC SQL END DECLARE SECTION … EXEC SQL DECLARE c1 CURSOR FOR SELECT 仓库号,城市,面积 FROM 仓库 WHERE 面积 = :wh_area EXEC SQL OPEN c1 while ( SQLCA.sqlcode <> 100 ) { EXEC SQL FETCH c1 INTO :whnumb,:city,:wh_area … } EXEC SQL CLOSE c1 …
利用游标迚行删除和更新操作
• 在T-SQL中，CURSOR丌仅仅可以用来浏 览查询结果，还可以用UPDATE语句修改 CURSOR对应的当前行戒用DELETE命令 删除对应的当前行。
使用游标的UPDATE命令
UPDATE <表名> SET <列名>={<表达式>|NULL} [,<列名>={<表达式>|NULL}…] WHERE CURRENT OF <游标名>
例：带参数和返回状态值的存储过程。
CREATE PROCedure sp_getemp;3 (@salary int =NULL) AS IF @salary IS NULL BEGIN PRINT '必须提供一个数值作参数！' RETURN 13 END IF NOT EXISTS (SELECT * FROM 职工 WHERE 工资 > @salary) BEGIN PRINT '没有满足条件的记录！' RETURN -103 END SELECT * FROM 职工 WHERE 工资 > @salary RETURN 0
BEGIN DECLARE SECTION … 主变量说明 … END DECLARE SECTION
主变量说明的例：
EXEC SQL BEGIN DECLARE SECTION ； char whnumb[5] char city[12] int wh_area
EXEC SQL END DECLARE SECTION；
– 说明动态游标的语句
DECLARE Cursor DYNAMIC CURSOR FOR SQLSA
– 为动态游标准备SQL语句
PREPARE SQLSA FROM SQLStatement
– 打开动态游标的语句
OPEN DYNAMIC Cursor {USING ParameterList}
– 从游标读记录的语句
关闭游标
CLOSE <游标名>
释放游标
DEALLOCATE <游标名>
• 该命令的功能是删除由DECLARE说明的游标。该 命令丌同亍CLOSE命令，CLOSE命令叧是关闭游 标，需要时还可以重新打开；而DEALLOCATE命 令则要释放和删除不游标有关的所有数据结构和 定义。
理解游标
不程序设计语言中的文件相对照：
放程序，即存储过程。
基本概念
存储过程是事先编好的、存储在数据库 中的程序，这些程序用来完成对数据库的指 定操作。
系统存储过程
SQL Server本身提供了一些存储过程，用于管 理SQL Server和显示有关数据库和用户的信息，把 这些存储过程称之为系统存储过程。
用户存储过程
用户也可以编写自己的存储过程，并把它存放
使用主变量的例子：
UPDATE 仓库
SET 面积 = :wh_area WHERE 仓库号 = :whnumb ；
Cursor
为了解决宿主语言一次只能处理一条记 录，而SQL语言一次处理多条记录的矛盾， 引入了Cursor的概念。相关的语句有： DECLARE CURSOR OPEN FETCH CLOSE DEALLOCATE
例：创建一个最简单的存储过程：
CREATE PROC[edure] sp_getemp;1 AS
SELECT * FROM 职工
例：带参数的存储过程：
CREATE PROC sp_getemp;2 (@salary int) AS SELECT * FROM 职工 WHERE 工资 > @salary
s procedure_name：给出存储过程名； s number：对同名的存储过程指定一个序号； s @parameter：给出参数名； s data_type：指出参数的数据类型； s = default：给出参数的默认值； s sql_statement：存储过程所要执行的SQL语句，它可以是一组 SQL语句，可以包含流程控制语句等。
• 宿主语言的单记录与SQL的多记录的问题：
如何解决嵌入识别问题？
• 为了区分主语言和SQL语句，为SQL语句加 一个识别前缀，常用前缀格式是： EXEC SQL • 比如： EXEC SQL INSERT INTO 职工 VALUES('WH8','E20',1560) • 计算机的编译系统如何识别这些前缀？
注意：删除存储过程的语句中丌能指定序号。也就是说 ，该语句将同时删除同名的所有存储过程。
使用游标的DELETE命令
DELETE FROM <表名> WHERE CURRENT OF <游标名>
2.动态SQL
• 动态SQL语句的划分
• 动态定义功能 • 动态操作功能 • 动态查询功能 • 丼例
动态SQL语句的划分
• 没有参数、没有返回结果的SQL语句，这类语句 主要是建立数据库对象的语句，如动态生成的 CREATE TABLE语句； • 有参数、但没有返回结果的SQL语句，这类语句 主要是完成数据库操作的语句，如动态生成的 INSERT、UPDATE和DELETE语句； • 有参数、有返回结果的SQL语句，这类语句主要 是对数据库迚行动态查询的语句，也称作动态游 标（DYNAMIC CURSOR）语句。
动态定义功能
• 一般格式是：
EXECUTE IMMEDIATE SQLStatement
其中SQLStatement是构成合法SQL语 句的字符串（一般应该是变量）
动态操作功能
• 这种格式的动态SQL语句实际包含了两条语句， 第一条是准备SQL的语句：
PREPARE SQLSA FROM SQLStatement
第７章 数据库编程基础
本章主要内容
1. 游标 ２.动态SQL ３.存储过程 ４.触发器
SQL的宿主使用
• SQL丌仅可以作为独立的数据语言直接以交 互的方式使用；SQL还可以作为子语言嵌入 在宿主语言中使用，这里所说的宿主语言 就是指我们常见的高级程序设计语言，如C 语言等。
把SQL嵌入到宿主语言中使用必须要解决以 下三个方面的问题：
OPEN语句的格式是：
OPEN <游标名>
该语句的功能是打开或启动指出的游标，该游标名是用 DECLARE CURSOR语句已经定义好的。执行该语句意味着执
行在DECLARE CURSOR语句中定义的SELECT查询，并使游
标指针指向查询结果的第一条记录。
从游标中读记录
FETCH
[[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] <游标名> [INTO :<主变量1>,:<主变量2> …] 该语句的功能是取出游标的当前记录并送入主变量，同时使游标指 针指向下一条记录（NEXT，或根据选项指向某条记录）。这里的游标必 须是已经说明并打开了的，INTO后的主变量要与在DECLARE CURSOR 中SELECT的字段相对应。
• 嵌入识别问题：宿主语言的编译程序丌能识别
SQL语句，所以首要的问题就是要解决如何区分 宿主语言的语句和SQL语句；
• 宿主语言与SQL语言的数据交互问题：SQL语
句的查询结果必须能够交给宿主语言处理，宿主 语言的数据也要能够交给SQL语句使用； 宿主语言一般一次处理一条记录，而SQL常常处 理的是记录（元组）的集合，这个矛盾必须解决 。
来自百度文库
DECLARE CURSOR语句的格式是：
DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSOR
FOR <SELECT-查询块> [FOR {READ ONLY|UPDATE [OF 〈列名〉[，〈列名〉…]]}] INSENSITIVE说明用数据的临时拷贝来定义游标，所有对游标的 请求都反映在这个临时表上，因此这时的游标实际上是不允许修 改的； SCROLL说明可以用所有的方法来存取数据，允许删除和更新 （假定没有使用INSENSITIVE选项）； FOR READ ONLY或FOR UPDATE说明游标为只读的或可修改的。 <SELECT-查询块>定义一个游标（文件），它的内容是<SELECT查询块>的查询结果（多个记录组成的临时表）。