第第第第8 8 8 8讲讲讲讲: : : :( ( ( (第第第第5 5 5 5章章章章) ) ) ) 高级高级SQLSQL课程名称课程名称: : 数据库系统数据库系统 -JDBC(for Java)JDBC(for Java)p.89p.89 (ODBC for C+,C#,VB)(ODBC for C+,C#,VB)2一一一一 ( ( ( (讲解讲解讲解讲解) ) ) )动态动态动态动态SQLSQLSQLSQL简介简介简介简介讨论讨论1. 如何用动态如何用动态SQL操作数据库？操作数据库？ 许多应用的完成都需要采用某种程序语言和利用SQL1）程序语言如何利）程序语言如何利用用SQL完成基于数据完成基于数据库的数据处理库的数据处理？ 1 1）动态）动态SQLSQL 调用数据库厂商提供的通过函数/方法； SQL语句-以字符串方式在运行时动态构建和提交；2 2）嵌入式）嵌入式SQLSQL 将SQL语句嵌入到程序中，但与动态SQL不同的是： SQL语句-在程序编译时完全确定； 这样的应用程序在编译前，需要进行预编译和优化； （这种预编译器由提供数据库厂商） 预编译将程序中的SQL代码转换成相应的代码和函数； 数据库厂商提供的库函数供程序编译(链接)时使用；3Java(JDBC)Java(JDBC)Java(JDBC)Java(JDBC)应用示例应用示例应用示例应用示例一一 动态动态SQLSQL简介简介连接连接ORACLEORACLE数据库数据库( (指定协议、主机名、端口、数据库名、用户、口令指定协议、主机名、端口、数据库名、用户、口令) )载入载入Oracle for JDBCOracle for JDBC数据库驱动器数据库驱动器动态生成动态生成SQLSQL空语句句柄空语句句柄动态生成动态生成SQLSQL插入语句，并执行插入插入语句，并执行插入判定判定SQLSQL插入语句成功？否则输入错误信息插入语句成功？否则输入错误信息动态生成动态生成SQLSQL查看语句，并执行查看语句，并执行一次从查询结果中提取一次从查询结果中提取1 1个元组，并显示个元组，并显示查询输入完成时，释放资源查询输入完成时，释放资源动态动态SQLSQL语句可以包含语句可以包含(4(4个，四个？号个，四个？号) )多个参数变量多个参数变量为每个变量赋值为每个变量赋值将该元组插入库将该元组插入库输入新的变量值后再插入输入新的变量值后再插入1 1元组元组2) 如何理解如何理解该程序该程序？ 4二二二二 嵌入式嵌入式嵌入式嵌入式SQL SQL SQL SQL p.95p.95p.95p.95讨论讨论2. 如何用嵌入式如何用嵌入式SQL操作数据库？操作数据库？ Include files; EXEC SQL INCLUDE SQLCA;EXEC SQL BEGIN DECLARE SECTION; char branchname15 = ; char szServerDatabase100;char szLogin20;char szPassword20; int iassets= 0; EXEC SQL END DECLARE SECTION;(读取并为前4个宿主变量赋值)EXEC SQL CONNECT TO :szServerDatabase USER :szLogin using :szPassword; EXEC select assets into iassets from branch where branch_name = :branchname;应用于主语言：应用于主语言：C,C+,Cobol,Pascal,Java,PL/I,FortranC,C+,Cobol,Pascal,Java,PL/I,Fortran1）预编译器如何识）预编译器如何识别嵌入在主语言中的别嵌入在主语言中的SQL语句？语句？C语言等语言等: EXEC SQL ;Cobol: EXEC SQL END-EXEC;申明申明SQL语句中将使用的宿主变量语句中将使用的宿主变量（将专门用于（将专门用于SQL语句中）语句中）连接到数据库连接到数据库查看部门的资产查看部门的资产将该单个属性值存放到将该单个属性值存放到iassets 加加: :-表示宿主变量表示宿主变量SQL通信区通信区:SQLCASQLCODE分量分量:返回执行状态返回执行状态1.1.程序编写的基本要素程序编写的基本要素52.2.2.2.游标游标游标游标( ( ( (cursorcursorcursorcursor) ) ) )与返回结果集与返回结果集与返回结果集与返回结果集 p.98p.98p.98p.98(申明宿主变量)(读取和对宿主变量credit_amount赋值)EXEC SQL declare c cursor for select ID, name from student where tot_cred :credit_amount;EXEC SQL open open c; ; while . doEXEC SQL fetch fetch c into into :si,:sn; ; (打印/显示变量si和sn中的当前值)EXEC SQL close close c; ; 二二 嵌入式嵌入式SQLSQL1.1.定义游标定义游标2.2.打开游标打开游标( (执行语句执行语句) )3.3.提取当前元组提取当前元组 并移到下一元组并移到下一元组( (并放入变量并放入变量sisi和和sn)sn)4.4.关闭游标关闭游标( (释放资源释放资源) )由于由于selectselect查询结果为元组的集合查询结果为元组的集合; ;必须建立必须建立SQLSQL与主语言的数据交换区与主语言的数据交换区; ;主语言通过游标指针主语言通过游标指针, ,一次取一元组一次取一元组; ;62.2.2.2.游标与数据更新游标与数据更新游标与数据更新游标与数据更新 p.98p.98p.98p.98二二 嵌入式嵌入式SQLSQLEXEC SQL declare c cursor for select *from instructor where dept_name = Music for update;EXEC SQL update instructor set salary = salary + 100 where current of c;4）解释这两个语句）解释这两个语句的作用？的作用？为修改数据库中记录，该语句定义一个游标。该语句利用定义的游标，对当前记录进行修改。while . doEXEC SQL fetch c; EXEC SQL close c;需要的话，还可以添加语句：需要的话，还可以添加语句：EXEC SQL COMMIT-提交事务/EXEC SQL ROLLBACK-回滚事务7三三三三 SQLSQLSQLSQL函数与过程函数与过程函数与过程函数与过程1.SQL1.SQL函数函数p.98p.98讨论讨论3. SQL如何定义如何定义和使用函数与过程和使用函数与过程 ？1）解释这几个语句）解释这几个语句(各部分各部分)的作用？的作用？定义(声明)一个函数：有一个输入参数，有一个输出参数(返回值),begin-end为函数体.SQL查询语句中，可以使用用户定义的函数，就像使用系统固有函数一样。定义的函数甚至可返回一个表，需仔细描述返回值(各属性)类型。这里函数体仅一个SQL语句，故函数体不需用begin-end界定。 函数返回表可以用在SQL查询中允许表出现的位置！SQLSQL除了提供一些常用的内建函数除了提供一些常用的内建函数( (聚集、日期、字符串转换等聚集、日期、字符串转换等) )外，外，可编写存储过程可编写存储过程( (业务逻辑业务逻辑),),存于库中存于库中, , 可在可在SQL/SQL/应用代码中调用！应用代码中调用！作用：作用：给定系给定系名返回名返回系人数系人数使用该函数的参数( (统称存储过程统称存储过程) )82.SQL2.SQL2.SQL2.SQL过程过程过程过程p.98-99p.98-99p.98-99p.98-99三三 SQLSQL函数与过程函数与过程2）SQL函数与函数与SQL过程有何不同？过程有何不同？过程：一段过程：一段SQLSQL语句程序；语句程序；函数：有处理还有返回值函数：有处理还有返回值3）该）该SQL过程的作过程的作用，及使用方法？用，及使用方法？该过程的作用与前面的该过程的作用与前面的dept_countdept_count函数类似！函数类似！存储过程可在存储过程可在SQLSQL过程过程中或中或嵌入式嵌入式SQLSQL中通过中通过callcall命令命令调用，调用，还可在还可在动态动态SQLSQL中用中用create procedure dept_count_proc ( in dept_name varchar(20), out d_count integer) begin select count(*) into d_count from instructor where instructor.dept_name = dept_count_proc.dept_name enddeclare d_count integer;call dept_count_proc( Physics, d_count);begin enddeclare n integer default 0;while n 10 do set n = n + 1end whilerepeat set n = n 1 until n = 0end repeat4）编写存储过程可）编写存储过程可以使用这些常规控以使用这些常规控制语句吗？制语句吗？If n 0 then return(-1)else then return(1)endif可以使用可以使用! !93.3.3.3.存储过程示例存储过程示例存储过程示例存储过程示例*p.100p.100p.100p.100（三（三 SQLSQL函数与过程）函数与过程）输入课程信息参数输入课程信息参数输出错误参数输出错误参数统计课程人数统计课程人数, ,暂存于暂存于currEnrolcurrEnrol满足人数限制时，注册一个学生满足人数限制时，注册一个学生( (且注册成功时，返回且注册成功时，返回0)0)查看课程人数限制查看课程人数限制, ,暂存于暂存于limitlimit否则否则, ,输出错误信息到输出错误信息到errorMsgerrorMsg( (且注册不成功时，返回且注册不成功时，返回-1)-1)4）(讲解讲解)该该SQL函函数是如何完成课程数是如何完成课程注册业务逻辑？注册业务逻辑？1.1.定义触发器定义触发器10四四四四 触发器触发器触发器触发器(student(student表的属性表的属性) )讨论讨论4. 什么是什么是触发触发器，器，如何使用？如何使用？当当sectionsection中还有参照记录存在时，中还有参照记录存在时，拒绝删除拒绝删除time_slottime_slot中被参照记录。中被参照记录。撤销删除的记录！撤销删除的记录！当当time_slottime_slot中不存在时，中不存在时，拒绝在拒绝在sectionsection表上插入。表上插入。撤销新插入的记录！撤销新插入的记录！将对改变前后的记录比较将对改变前后的记录比较当属性值改变时当属性值改变时有新记录成绩且非有新记录成绩且非F F旧记录成绩为旧记录成绩为F F或空或空将根据将根据takestakes的课程成绩记录，的课程成绩记录，重新计算相应学生的总学分数重新计算相应学生的总学分数. .begin-endbegin-end可有多个语句可有多个语句atomic-atomic-表示为一个事务表示为一个事务