SQL⼊门（4）：嵌⼊式SQL语⾔本节讲述内容:1.嵌⼊式SQL 语⾔概述2.变量声明与数据库连接3.数据集与游标4.可滚动游标与数据库的增删改5.状态捕捉以及错误处理机制(⼀)嵌⼊式SQL语⾔之前我们所学的都是交互式SQL 语⾔: select .. from .. where..嵌⼊式SQL语⾔表⽰将SQL语⾔嵌⼊到某⼀种⾼级语⾔中使⽤, ⽐如C++ ,Java, powerbuilder等它们也称为宿主语⾔(host language).复杂的检索不能⽤⼀条SQL语句完成, 需要结合⾼级语⾔中的顺序\分⽀\循环结构帮助处理.if [conditon] then SQL_query else SQL_query end ifdo while [condition] SQL_query end do还有在SQL语句的检索结果基础上,再进⾏处理的SQL_query1for ... doprocess the recordnextSQL_query 2if .. thenelseend if交互式SQL: select sname, sage from student where sname='xy';嵌⼊式SQL: 以宿主语⾔C语⾔为例,exec sql select sname, sage into :vsname, :vsage from student where sname='xy';主要区别:(1) exex sql 是⼀个引导词, 它引导sql 语句, 将SQL语句预编译成C编译器可识别的语句.(2) 增加 into ⼦句, ⽤于把SQL 语句的检索结果赋给⾼级语⾔的程序变量(3) ⽤冒号开头表⽰⾼级语⾔的程序变量 :vsname , :vsage冒号很重要, ⽤于区分是程序变量还是表的字段!! .... 还有很多特点之后在详细介绍为啥要学嵌⼊式SQL , ⽤来解决啥问题?下⾯逐个解决上述8个问题(⼀) 数据库的连接(问题1)在嵌⼊式SQL 操作之前, ⾸先需要与数据库进⾏连接不同的DMBS 的语句是有差别的,在嵌⼊式SQL程序执⾏之后, 需要断开数据库SQL 执⾏的提交与撤销SQL语句在执⾏过程中, 必须要有提交, 撤销语句提交: exec sql commit work;撤销: exec sql rollback work;很多DBMS 都设计了捆绑提交\撤销与断开连接在⼀起的语句, 以保证在断开连接之前使⽤户确认提交或撤销先前的⼯作, Oracle 中就是这样:exec sql commit release;exec sql rollback release;为什么需要提交和撤销呢? 这个设计到数据库中的'' 事务 ''处理什么是事务? 从应⽤程序员⾓度来看, 事物是⼀个存取或者改变数据库内容的程序的⼀次执⾏,或者说是⼀条或者多条SQL 语句的⼀次执⾏被看做是⼀个事务事务⼀般由应⽤程序员提出, 因此有开始和结束, 结束前需要提交或者撤销begin transactionexec sql...exec sql...exec sql commit work|exec sql rollback work --提交或者撤销end transaction注意: 提交表⽰这⼀系列操作对数据库的更新是有效的, 撤销则表⽰⽆效其实从任何⼀个SQL语句执⾏就表⽰了⼀个事务的开始, 到了 commit 或 rollback 则结束⼀个事务,因此上述的 begin end 可以省略.事务的ACID 特性A : atomicity 原⼦性, DBMS保证表⽰事务中的⼀组操作是不可分的,要么全做,要么⼀条也不做C : consistency ⼀致性,例如两个⼈同时在买车票,会不会买到同⼀张车票I: isolation 隔离性两个事务操作互不⼲扰D: durability 已提交事务的影响是持久的, 被撤销的事务影响可以恢复事务处理技术是DBMS的核⼼处理技术!!(⼆) 变量声明(问题2)exec sql select sname, sage into :vsname, :vsage from student where sname=:specname;加了冒号表⽰⾼级语⾔的程序变量, 这些变量需要声明exec sql begin declare section; --开始声明char vsname[10], specname[10]='xy' ;int vsage;exec sql end declare section; -- 结束声明注: 宿主程序的字符串变量长度要⽐字符型字段多1, 因为宿主程序的字符串尾部多⼀个终⽌符'\0' . -- 变量的声明与使⽤exec sql begin declare section;char vsname[10], specname[10]='xy' ;int vsage;exec sql end declare section;-- ⽤户在此处可以基于键盘输⼊给specname 赋值exec sql select sname, sage into :vsname, :vsage from student where sname=:specname;实例: 数据库连接+变量定义#include<stdio.h>#include"prompt.h"exec sql include sqlca; --sqlca 表⽰SQL的通信区, communication areachar cid_prompt[]="please enter customer id:";int main(){exec sql begin declare section; --下⾯声明变量char cust_id[5], cust_name[14];float cust_discnt;exec sql end declare section;exec sql whenever sqlerror goto report_error;-- 错误捕获exec sql whenever not found goto notfound; -- 记录没有找到strcpy(user_name,"poneilsql");-- 字符串赋值strcpy(user_pwd,"123456");exec sql connect :user_name identified by :user_pwd; -- 连接数据库while((prompt(cid_prompt,1,cust_id,4))>=0){exec sql select cname,discnt into :cust_name,:cust_discntfrom customers where cid=:cust_id; -- 根据输⼊的客户id 找到名字和折扣exec sql commit work;-- 提交printf("customer's name is %s and discountis %.1f\n",cust_name, cust_discnt);continue; -- 接着循环,再输⼊客户idnotfound:printf("can't find customer %s, continuing\n", cust_id);}exec sql commit release; -- 断开数据库的连接return0;report_error: -- 前⾯报错的执⾏print_dberror();exec sql rollback release; -- 断开连接return1;}(三) 数据集与游标(问题3 4 5)问题3: SQL 语句如何执⾏?问题4: 如何将SQL 检索到的结果传递回宿主程序进⾏处理?问题5: 如何将静态SQL , SQL语句中的常量更换为变量?如何读取单⾏数据和多⾏数据, 单⾏结果处理与多⾏结果处理的差异: into ⼦句和游标 cursor 1. 检索单⾏结果, 可以将结果直接传送到宿主主程序的变量中, select ... into ...exec sql select sname, sage into :vsname, :vsage from studentwhere sname=:specname;2. 如果是多⾏结果, 则需要使⽤游标cursor游标是指向某个检索记录的指针, 通过这个指针, 每次读⼀⾏, 处理⼀⾏,接着再读⼀⾏...,直到全部处理完毕 fetch..into... (⼀次⼀⾏)需要先定义⼀个cursor-->再打开-->接着⼀条⼀条处理-->最后关闭exec sql delcare cur_student cursor for--游标名select sno, sname, sclass from studentwhere sclass='0315'; -- 定义游标exec sql open cur_student; --打开游标exec sql fetch cur_student into :vsno, :vsname, :vsclass; --取数据...exec sql close cur_student; --关闭游标具体实例:已知表orders(cid, aid, product, dollars) 客户id, 代理⼈id, 产品, ⾦额游标: 给定⼀个客户id, 选出该客户下的所有代理商和⾦额(多⾏数据)#define True 1#include<stdio.h>#include"prompt.h"exec sql include sqlca; --sqlca 表⽰SQL的通信区, communication areaexec sql begin declare section; --声明变量char cust_id[5], agent_id[14];double dollar_sum;exec sql end declare section;int main(){char cid_prompt[]="please enter customer id:"; -- 定义提⽰字符串exec sql declare agent_dollars cursor for-- 定义游标select aid,sum(dollars) from orderswhere cid=:cust_id group by aid;exec sql whenever sqlerror goto report_error;-- 错误捕获exec sql connect to testdbl; --连接数据库exec sql whenever not found goto finish; -- 记录没有找到while((prompt(cid_prompt,1,cust_id,4))>=0){exec sql open agent_dollars; -- 打开游标while(True){ -- 打印每⼀条记录exec sql fetch agent_dollars into :agent_id,:dollar_sum;printf("%s %11.2f\n",agent_id, dollar_sum)};finish:exec sql close agent_dollars; -- 关闭游标exec sql commit work; -- 提交exec sql disconnect current;--断开连接return0;report_error: -- 前⾯报错的执⾏print_dberror();exec sql rollback;-- 撤销exec sql disconnect current; --断开连接return1;}总结游标:exec sql delcare cur_student cursor for--游标名select sno, sname, sclass from studentwhere sclass=:vclass; -- 定义游标order by snofor read only; --只读, 不可更新cursor 数据读取 fetch : exec sql fetch cursor_name into host_variableexec sql delcare cur_student cursor for--游标名select sno, sname, sclass from studentwhere sclass=:vclass; -- 定义游标order by snofor read only; --只读, 不可更新exec sql open cur_student; -- 打开exec sql fetch cur_student into :vsno, :vsname, :vsage; -- 使⽤exec sql close cur_student; -- 关闭可滚动游标与数据库的增删改标注的游标始终是⾃开始到结束⽅向移动的, 每fetch ⼀次,向结束⽅向移动⼀次,每⼀条记录只能被访问⼀次, 再次访问该记录只能关闭游标后重新打开可不可以实现游标的向上移动呢? ODBC (open database connectivity) 是⼀种跨DBMS 的DB 操作平台, 它在应⽤程序与实际的DBMS之间提供了⼀种通⽤的接⼝,很多DBMS不⽀持可滚动游标, 但是通过ODBC 可实现该功能定义中增加了 scroll使⽤如下:可滚动游标移动时需要判断是否到了结束位置, 或者到了起始位置,EOF表⽰最后⼀条记录的后⾯位置BOF表⽰起始位置的前⾯如果不需要区分最上最下, 则可以⽤whenever not found 进⾏检测⽤游标进⾏数据库的增删改1. 查找删除(与交互式delete 语句相同)exec sql delete from customers c where c.city='harbin'andnot exists (select*from orders o where o.cid=c.cid)-- 删除城市是哈尔滨且在订单 orders表⾥⾯没有记录的.2. 定位删除exec sql declare delcust cursor forselect cid from customers c where c.city='harbin'andnot exists (select*from orders o where o.cid=c.cid)for update of cid;exec sql open delcustwhile(True){exec sql fetch delcust into :cust_id;exec sql delete from customers where current of delcust;}1. 查找更新exec sql update student sset scalss='0315'where s.sclass='0314';2.定位更新exec sql declare stud cursor forselect*from student s where s.sclass='0314'andfor update of sclass;exec sql open studwhile(True){exec sql fetch stud into :vsno, :vsname,:vsclass;exec sql update studentset sclass='0315'where current of stud;}插⼊语句exec sql insert into student(sno,sname,sclass)values ('031501','xy','0315');exec sql insert into master_stud(sno,sname,sclass)select sno,sname,sclass from student;综合实例: 求数据库中某⼀列位于中值的那⼀⾏--已知表 orders(cid,aid,product,dollars)-- 寻找数据库中某⼀列位于中值的那⼀⾏#include<stdio.h>#include"prompt.h"exec sql include sqlca; --sqlca 表⽰SQL的通信区, communication areachar cid_prompt[]="please enter customer id:"; -- 定义提⽰字符串int main(){exec sql begin declare section; --声明变量char cid[5], user_name[20], user_pwd[10];double dollars; int ocount;exec sql end declare section;exec sql declare dollars_cursor cursor for-- 定义游标select dollars from orderswhere cid=:cid and dollars is not null order by dollars;exec sql whenever sqlerror goto report_error;-- 错误捕获strcpy(user_name,"poneilsql");-- 字符串赋值strcpy(user_pwd,"123456");exec sql connect :user_name identified by :user_pwd; -- 连接数据库--exec sql whenever not found goto finish; -- 记录没有找到while((prompt(cid_prompt,1,cust_id,4))>=0){exec sql select count(dollars) into :ocount from orderswhere cid=:cid;if(ocount==0){printf("no record reviewed for cid value %s\n",cid);continue;}exec sql open dollars_cursor;for (i=0;i<(ocount+1)/2;i++)exec sql fetch dollars_cursor into :dollars ;exec sql close dollars_cursor;exec sql commit work; -- 提交printf("median dollar amount=%f\n",dollars); }。
