合工大数据库课件

1、第八讲 数据库编程沈明玉数据库编程一、嵌入式SQL二、PL/SQL与Transact-SQL三、存储过程四、事务的概念与使用数据库编程一、嵌入式SQL嵌入式SQL什么是嵌入式SQL？ 交互式SQL和嵌入式SQL是SQL的两种使用方法。当一个程序既要访问数据库，又要处理数据时，将SQL语言嵌入到程序设计语言（宿主语言）中，将SQL语言访问数据库的功能和宿主语言的数据处理功能相结合，这种使用SQL的方法称为嵌入式SQL。嵌入式SQL（续）如何实现嵌入式SQL？宿主语言的编译器不能识别SQL，如何将嵌有SQL语句的宿主语言编译成可执行代码？宿主语言与DBMS之间如何传递数据和信息？如何解决元组集合数

2、据对宿主语言变量的赋值？在宿主语言中标注SQL语句的方法用EXEC SQL前缀来标识SQL语句，以区分宿主语言与SQL语言。嵌入式SQL（续）宿主语言与DBMS之间如何通信？SQL通信区（SQLCA）SQLCA是一个数据结构SQLCA的用途描述系统当前工作状态 描述运行环境SQLCA使用方法定义SQLCA：Exec SQL Include SQLCA使用SQLCA：变量SQLCODE，为0-执行成功，否则表示出错。嵌入式SQL（续）共享变量（主变量）宿主语言与SQL语言之间通过共享变量进行数据传送；共享变量由宿主语言程序定义，用SQL的Declare说明后可在SQL语句中引用（引用时需加:号，

3、以区分数据库自身的变量，如：属性）。嵌入式SQL（续）执行部分：各种SQL语句的执行Exec SQL Insert into Students (sno,sname,sage) values (:sno, :sname, :sage);Exec SQL Update Students SET sname=:sname, sage=:sage Where sno=:sno;Exec SQL Delete from SC Where sno = ( Select sno from Student Where sno=:sno); Exec SQL Select sname, sage into :

4、sname, :sage from Students Where sno=:sno;嵌入式SQL（续）使用游标（cursor）的SQL编程解决多对一的问题！说明游标语句：Exec SQL Declare Cursor for Select from Where ；打开游标语句：Exec SQL Open ；推进游标语句：Exec SQL Fetch into ;关闭游标语句：Exec SQL Close ;嵌入式SQL（续）建立数据库连接 Exec SQL Connect TO target AS connection-name User user-name; target是要连接的数据库服务

5、器：常见的服务器标识串，如: connect-name是可选的连接名，连接必须是一个有效的标识符 在整个程序内只有一个连接时可以不指定连接名。嵌入式SQL（续）例 依次检查某个系的学生记录，交互式更新某些学生年龄。 EXEC SQL BEGIN DEC LARE SECTION; /*主变量说明开始*/ char deptname64; char HSno64; char HSname64; char HSsex64; int HSage; intNEWAGE;EXEC SQL END DECLARE SECTION; /*主变量说明结束*/long SQLCODE;EXEC SQL INCL

6、UDE sqlca; /*定义SQL通信区*/嵌入式SQL（续）int main(void) /*C语言主程序开始*/int count = 0;char yn; /*变量yn代表yes或no*/printf(Please choose the department name(CS/MA/IS): ); scanf(%s, deptname); /*为主变量deptname赋值*/EXEC SQL CONNECT TO TESTlocalhost:54321 USER SYSTEM /MANAGER; /*连接数据库TEST*/EXEC SQL DECLARE SX CURSOR FOR /*

7、定义游标*/SELECT Sno, Sname, Ssex, Sage /*SX对应语句的执行结果*/FROM StudentWHERE SDept = :deptname;EXEC SQL OPEN SX; /*打开游标SX便指向查询结果的第一行*/嵌入式SQL（续） if (yn = y | yn = Y) /*如果选择更新操作*/ printf(INPUT NEW AGE:); scanf(%d,&NEWAGE); /*用户输入新年龄到主变量中*/ EXEC SQL UPDATE Student /*嵌入式SQL*/SET Sage = :NEWAGE WHERE CURRENT OF

8、SX ; /*对当前游标指向的学生年龄进行更新*/ EXEC SQL CLOSE SX; /*关闭游标SX不再和查询结果对应*/EXEC SQL COMMIT WORK; /*提交更新*/EXEC SQL DISCONNECT TEST; /*断开数据库连接*/数据库编程二、PL/SQL与Transact-SQLPL/SQL与Transact-SQLPL/SQLPL/SQL是Oracle对标准SQL的过程化扩充，它将数据库技术和过程化程序设计语言联系起来，可使用循环、分支和嵌套，将SQL的数据操纵功能与过程化语言数据处理功能相结合。PL/SQL与Transact-SQL（续）PL/SQL的作用

9、能够使一组SQL语句的功能更具模块化程序特点；采用了过程性语言控制程序的结构；可以对程序中的错误进行自动处理，使程序能够在遇到错误的时候不会被中断；具有较好的可移植性，可以移植到另一个Oracle数据库中；集成在数据库中，调用更快；减少了网络的交互，有助于提高程序性能。PL/SQL与Transact-SQL（续）PL/SQL程序的基本结构DECLARE可选部分变量、常量、游标、用户定义异常的声明BEGIN必要部分SQL语句和PL/SQL语句构成的执行程序EXCEPTION可选部分程序出现异常时，捕捉异常并处理异常END；必须部分PL/SQL与Transact-SQL（续）PL/SQL的变量简单

10、变量格式：变量名 constant 变量类型 not null default 值 | :=值v_name varchar2(10); v_age constant number:=20;v_sex char(2) default 男;PL/SQL与Transact-SQL（续）PL/SQL的语句赋值语句：变量名称:=表达式 v_sno:=20090080;条件语句： IF-THEN，IF-THEN-ELSE和嵌套的IF语句 IF (new.Job=讲师) AND (new.Sal y print x y else if y z print y z else print z yPL/SQL与T

11、ransact-SQL（续）分支语句Case when 条件表达式1 then when 条件表达式n then else EndPL/SQL与Transact-SQL（续）循环语句 while begin break continue endPL/SQL与Transact-SQL（续）异常处理 Transact-SQL 代码中的错误可使用 TRYCATCH 构造处理。TRYCATCH 构造包括两部分：一个 TRY 块和一个 CATCH 块BEGIN TRYSELECT 1/0;END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, E

12、RROR_PROCEDURE() AS ErrorProcedure, ERROR_MESSAGE() AS ErrorMessage;END CATCH;数据库编程三、存储过程存储过程存储过程：由PL/SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。 存储过程的优点：运行效率高；降低了客户机和服务器之间的通信量；方便实施企业规则。存储过程（续）PL/SQL创建存储过程Create or replace procedure 过程名 (argument1 in|out|in out type1 , argument2 in|out|in out type2, )IS

13、 | AS Begin Exception end;存储过程（续）PL/SQL创建函数Create or replace function 函数名 (argment in|out|in out type,) Return return_type IS |AS Begin Exception End;存储过程（续）创建PL/SQL存储过程示例create or replace procedure set_grade(sid sc.sno%type, cid o%type, newgrade sc.grade%type, st out varchar2) isbegin update sc set

14、 grade=newgrade where sno=sid and cno=cid; st:=OK！; commit; exception when no_data_found then st:=数据不存在！; when others then st:=操作失败！;end;存储过程（续）存储过程的使用declare mm varchar2(20);begin set_grade(20090001,1001,90,mm); dbms_output.put_line(操作结果： | mm);end;存储过程（续）创建PL/SQL函数create or replace function get_gr

15、ade(s_no sc.sno%type,c_no o%type)return numberis r_grade number;begin select grade into r_grade from sc where sno=s_no and cno=c_no; if r_grade is null then r_grade:=0; end if; return r_grade; 存储过程（续） exception when no_data_found then begin dbms_output.put_line(指定的数据不存在！); return -1; end; when other

16、s then begin dbms_output.put_line(sqlcode | - | sqlerrm); return -10; end;end get_grade;存储过程（续）PL/SQL的函数调用declare vv number;begin vv := shen.get_grade(20090001, 2010); dbms_output.put_line(成绩= | vv);end;存储过程（续）创建T-SQL存储过程create procedure sc_avgsno char(10),avg int outputASbegin select avg=avg(grade)

17、 from sc where sno=sno print(ok ! 学号： + sno+ 的数据已处理！)end存储过程（续）T-SQL函数的使用declare tmp char(10),avg_out intset tmp=20090001exec sc_avg tmp,avg_out output数据库编程四、事务的概念与使用事务的概念与使用事务(Transaction)的定义一个数据库操作序列；一个不可分割的工作单位；恢复和并发控制的基本单位。一个事务就是将一系列的数据操纵SQL语句作为一个逻辑单元，逻辑单元里面的单个操作要么全做，要么全部不做，以保证数据的完整性。事务的概念与使用（续）事务的ACID特性：原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持续性（Durability ）事务的概念与使用（续）PL/SQL事务支持的相关语句 Commit, rollback, savepoint, rollback to savepoint, PL/SQL未提供事务开始的显式定义语句事务的提交与回滚 Commit, rollback基于保存点的事务回滚savepoint sp1;rollback to sp1;事务的概念与使用（续）T-SQL的事务 begin transaction trans_name，c