数据库原理及应用10.ppt

5.3一些通用的嵌入式SQL l1、select 语句 l 不使用游标在程序中执行select语句，那么该查 询最多检索出一行记录。如果该查询检索到多于 一行的记录，系统将返回一个运行期错误。 l exec sql select all | distinct expr ,expr l into host-variable,host-variable l from tablename ,tablename . l where search_condition l2、declare cursor语句 l exec sql declare cursor_name cursor for l subquery l order by columnasc | desc,columnasc | desc l for read only | updateof colname,colname. l 子查询格式允许使用子句 group by 和 having l在最后一行的可选格式用于指明游标是read only 或update ，若缺省，当使用了order by，游标是 read only ，否则为update 。 l 注：在oracle中允许同时使用这两个格式，但改 为with readonly 在order by子句前面。 l故，若程序逻辑想通过游标更新或删除一条记 录，在游标声明中必须包括for update，在 lfor update 没有指定列，则被选到的列都被认 为是可更新的，若游标使用了for read only， 则不能通过该游标来更新或删除记录。 l 注：这里定义的游标只能在一个行集合中向 前移动，在SQL-99中新增了一个可滚动游标 。但oracle 和DB2 UDB不支持。这意味着要第 二次抽取某行记录，只能关闭后再打开游标。 通常情况下，游标在被再打开前必需先关闭。 l3、delete语句 l 有两种格式的delete语句，一种是定位删除：它 删除游标所在的当前记录；另一种是查找删除： 格式与交互式SQL中相同。 l exec sql delete from tablename name l where condition | where current of cursor_name l定位删除使用特殊语法：current of cursor_name l两种where条件是可选的。 l 长整型变量sqlca.sqlerrd2包含了删除操作所 影响到的行数。 l 在定位删除中，当删除执行后，游标指针加1 下移一行，若执行删除时，游标没有指向任意一 行，则whenever语句的not found 就成立。 l特别注意的是：在删除中约定的游标必须已经打 开并且指向一个实际的行，删除中的from必须同 选择游标时的from子句指向同一个数据源。此外 ，使用的游标必须是一个可更新的游标，不能被 声明为read only。 l 例5.3.1 从customers表中删除所有住在Duluth并 且没有订单的顾客记录。 使用查找删除： exec sql delete from customers c where c.city=Duluth and not exists (select * from orders o where o.cid=c.cid) l使用定位删除： l exec sql declare delcust cursor for l select cid from customers c where c.city=Duluth l and not exists(select * from orders o l where o.cid=c.cid ) l for update of cid l whenever not found goto skip; l exec sql open delcust; l while(true) l l exec sql fetch delcust into : cust_id; l exec sql delete from customers where current of delcust l l注：我们当然也可以创建一个游标检索所有没有 下过订单的顾客的city和cid，然后在程序逻辑中 测试if (strcmp(city,”Duluth”)=0)，来判断该行是 否删除，但是，由于性能的原因，SQL在执行一 个检测时能够不带额外开销是最好的，（在宿主 程序与SQL中的切换是比较大的一个开销），我 们通常将尽量多的任务交给SQL去做。 l 故：大家思考，在例5.3.1中的定位删除与查找 删除，哪种效率更高？ l4、update语句 l 与delete一样，有两种形式的update语句 l 查找更新和定位更新。 l exec sql update tablename name l set coluname= expr, coluname= expr l where condition | where current of cursor_name l长整型变量sqlca.sqlerrd2包含了更新操作所影响到 的行数。若没有行被影响到则whenever语句的not found 就成立。 l同delete一样，在定位更新中，约定的游标必须已经 打开并且指向一个实际的行，from必须同选择游标时 的from子句指向同一个数据源。此外，使用的游标必 须是一个可更新的游标，不能被声明为read only。 l5、insert语句 l insert语句与交互式的insert格式相同。 l exec sql insert into tablename(colum,colum) l values(expr,expr) | subquery ; l注：插入操作没有定位插入，新插入的行通常不 能被放在表中指定的位置，而是由表的磁盘结构 来决定位置。 l6、游标的打开、抽取、关闭 l exec sql open cursor_name; l exec sql fetch cursor_name into variable,variable l exec sql close cursor_name; l注：一般来说，若游标被打开了，就不能再次 打开，但oracle却可以例外处理。同样，关闭 一个没有打开的游标也会出错。当fetch语句执 行时遇到了一个空的活动行集或游标位于最后 一个活动行后面时，whenever语句的not found 条件成立。 l7、其他嵌入式SQL操作 l exec sql create table l exec sql drop table l exec sql connect l exec sql disconnect 5.4事务编程 l 大多数的数据库系统允许多用户同时访问同 一数据库的表。数据库系统也使用用户进程的概 念，通过允许多个进程同时访问数据。称为：“ 并发访问”。 l 显然，若不对并发访问进行控制，必然会导致 严重问题。 考虑数据的不一致性问题。 l 例5.4.1 假设一个存款人在表A中有两个银行帐 号ID，分别为A1和A2，来代表不同的行。帐户 基本信息的帐号用aid表示，该帐户现存的金额 用balance ，A1帐户上$900,A2 帐户上$100 ，若 进程p1想从帐号A1转帐$400到A2帐户上，该转 帐过程分三种状态： l l S1：A1.balance= = $900 ，A2.balance= =$100 , S2：A1.balance= = $500， A2.balance= =$100， S3：A1.balance= =$500, A2,balance= =$500, l现在我们看另外一个进程P2，它与进程P1同时运 行，该进程对存款人的存款额进行检查。若该储 户的存款总额大于$900，那么允许该储户办理信 用卡。那么，当两个进程并发执行，可能出现以 下情况： 进程P1进程P2 Update A set balance=balance-400 where A.aid=A1; update A set balance=balance+400 where A.aid=A2; int bal, sum=0; select A.balance into :bal from A where A.aid=A1; sum=sum+bal; select A.balance into :bal from A where A.aid=A2; sum=sum+bal; l进程P2在状态S2时，查询到该储户的存款总额 $600，称此为“不一致视图”。 l 为了避免访问这些不一致状态和由于并发访问 带来其他问题，数据库系统提供了一个特征，称为 ：“事务”。 l 每个进程将一系列的数据库操作结合在一起， 这些操作构成了一个状态的一致性改变。这种数据 库操作包称为事务。支持事务的数据库系统给程序 员提供一些事务特性，保证程序的正确访问。 l l 1、隔离性保证 l 2、原子性保证 l 3、持久性保证 l1、隔离性保证 l 隔离性保证了即使是事务T1与事务T2同时执行 ，对于每个事务而言，各个事务是相互隔离的运 行在数据库系统上。即：在数据库系统中，T2在 T1进行转帐前进行，或T2在T1转帐完成后进行 ，T2看到的状态是S1或S3，不会看到S2，因而 不可能导致不一致视图。 l2、如何在程序中指定事务 l 成功连接到一个数据库后，在进程中并没有活 动的事务。通过执行任何对数据库行进行操作的 SQL语句（select、open cursor、update、insert、 delete）产生一个活动的事务。 在执行这些语句时，事务保持活动状态，直到程 序显示的执行如下两种嵌入式SQL语句之一来终 止事务： exec sql commit work;事务成功终止； exec sql rollback work；事务失败回滚； 关键字work在SQL-92中是可选的。若在程序结 束前对于一个事务既没有执行commit也没有执行 Rollback语句，那么将执行一个缺省的终止操作， 具体操作（commit或rollback）与产品有关。 l #include l #include ”prompt.h” lint main( ) l exec sql begin declare section ; l char acctfrom11,acctto11; l double dollars; l exec sql end declare section ; l char dollarstr20; l exec sql connect to default; l exec sql set transaction isolation level serializable; l l while (1) l while(prompt (“enter account and dollars :n”,3 l acctfrom,10,acctto,10,dollarstr,10)=0; l exec sql commit work; l break; lunitsgone: l exec sql rollback work; l l游标和事务 l 游标在一个作为原子的事务单元所执行的循环 中做一些细节