ORACLE深入浅出-中级篇.ppt

ORACL深入浅出 第一章事务与锁 什么是事务 如何Oracle使用事务 在Java中使用事务 什么是锁 演示更新丢失的问题 锁的分类 阻塞与死锁 什么是事务 什么是事务 事务是一种机制 这种机制能保证在事务内的DML 数据操作语句 做为一个不可分割的单元进行执行 举例说明事务 演示sqlservert2005事务处理 演示Oracle事务处理 事务四大特性 ACID 原子性 A 一致性 C 隔离性 I 永久性 D 什么是事务 事务处理的两种方式显示事务 用户通过编码方式开启事务隐式事务 数据库默认认为所有操作都是事务比较sqlserver2005与Oracle事务处理方式的区别 如何Oracle使用事务 Oracel事务关键字Savepointa 设置事务保存点Rollbacktoa 回滚到事务保存点a 事务不结束Commit 提交事务 事务结束Rollback 回滚所有事务 事务结束讲解Oracel程序员一天 在JavaJdbc中使用事务 演示如何在JavaJdbc中如何使用事务驱动包位置 oracle安装路径 oracle jdbc lib classes12 jar演示案例 DBUtil java 什么是锁 生活中的锁 为什么会有锁的出现 锁的作用就是保护私有的空间 Oracle中的锁 演示Oracle中更新丢失的问题 讲解此问题的两种思路 一 在第一个用户修改之前不允许其它用户修改 二 在修改之前 查看此数据的版本号 锁的分类 悲观锁定此锁持悲观态度 认为在用户修改数据期间 一定会有用户再对其修改 那么此锁会在用户事务内一直保持锁定状态 其它用户无法进行修改 在查询语句后接forupdatenowait此可加锁 查询多少数据 锁定多少数据 乐观锁定此锁持乐观态度 认为在用户修改数据期间 其它用户不会再对其修改 直到最后提交数据时才判断数据是否已更改 锁的分类 乐观锁定的实现方式 增加标识列来区分是否已修改通过OracleORA ROWSCN机制来实现乐观锁定 阻塞与死锁 如果你有两个会话 每个会话都持有另一个会话想要的资源 此时就会出现死锁 deadlock 例如 如果我的数据库中有两个表A和B 每个表中都只有一行 就可以很容易地展示什么是死锁 我要做的只是打开两个会话 例如 两个SQL Plus会话 在会话A中更新表A 并在会话B中更新表B 现在 如果我想在会话B中更新表A 就会阻塞 会话A已经锁定了这一行 这不是死锁 只是阻塞而已 我还没有遇到过死锁 因为会话A还有机会提交或回滚 这样会话B就能继续 阻塞与死锁 如果我再回到会话A 试图更新表B 这就会导致一个死锁 要在这两个会话中选择一个作为 牺牲品 让它的语句回滚 例如 会话B中对表A的更新可能回滚 得到以下错误 想要更新表B的会话A还阻塞着 Oracle不会回滚整个事务 只会回滚与死锁有关的某条语句 会话B仍然锁定着表B中的行 而会话A还在耐心地等待这一行可用 收到死锁消息后 会话B必须决定将表B上未执行的工作提交还是回滚 或者继续走另一条路 以后再提交 一旦这个会话执行提交或回滚 另一个阻塞的会话就会继续 好像什么也没有发生过一样 总结 开发多用户 数据库驱动的应用时 最大的难点之一是 一方面要力争取得最大限度的并发访问 与此同时还要确保每个用户能以一致的方式读取和修改数据 为此就有了锁定 locking 机制 这也是所有数据库都具有的一个关键特性 Oracle在这方面更是技高一筹 不过 Oracle的这些特性的实现是Oracle所特有的 就像SQLServer的实现只是SQLServer特有的一样 应用执行数据处理时 要正确地使用这些机制 而这一点要由你 应用的开发人员 来保证 第五章使用PL SQL PL SQL简介变量 常量数据类型流程控制异常处理 PL SQL简介 PL SQL是Oracle在sql标准上的加强 使用Oracle能够基于对象编程T SQL是基于事务的结构化查询语言两者都是对SQL的扩展 PL SQL块简介 PL SQL块是构成PL SQL程序的基本组织单元 可以编写存储过程 函数 包 触发器等 PL SQL分为三个部分 声明部分 可执行部分和异常处理部分 PL SQL块简介 演示 PL SQL打印HelloWorld 演示 如何在PL SQL块中向表中增加一条记录 演示 如何在PL SQL块中修改表中数据 演示 查询学员 张秋丽 的年龄 PL SQL块小结 每一个PL SQL块将会自动开启一个事务 在PL SQL块中必须结束事务 否则 将会阻塞 PL SQL块每条语句后 必须带上 表示语句结束 PL SQL块中不能执行select语句进行显示 在命令行中执行pl sql块需加 在命令行中打印变量需setserverouton 变量与常量 在PL SQL块的声明部分声明变量或常量语法 declaresalnumber piconstantnumber 3 14 赋值1 使用赋值运算符 2 使用SELECTINTO语句3 接收用户输入 变量与常量示例 Declaremynamevarchar2 20 notnull 张三 myagenumber mypiconstantnumber 3 14 Beginmyage 26 selectenameintomynamefromempwhereempno 7369 输出End 日期时间 日期时间类型存储日期和时间数据常用的两种日期时间类型DATETIMESTAMPdeclaremydatedate mytimetimestamp beginselectsysdateintomydatefromdual selectsysdateintomytimefromdual dbms output put line mydate dbms output put line mytime end 布尔类型 boolean 用于存储逻辑值 TRUE FALSE和NULL 不能向数据库中插入BOOLEAN数据不能将列值保存到BOOLEAN变量中只能对BOOLEAN变量执行逻辑操作 属性类型 用于引用数据库列的数据类型 以及表示表中一行的记录类型列类型 type 语法 表名 列名 type行类型 rowtype 语法 表名 rowtype优点不需要知道被引用的表列的具体类型如果被引用对象的数据类型发生改变 PL SQL变量的数据类型也随之改变 属性类型示例 问题1 假设现在不知道emp表Job列的类型 但要保存员工7369的岗位到变量中 如何定义 declaremyjobemp Job type beginselectJobintomyjobfromempwhereempno 7369 dbms output put line myjob end 属性类型示例 问题2 假设要输出emp表员工7369的所有信息 怎么办 定义8个列类型变量 依次输出 declaremyempemp rowtype beginselect intomyempfromempwhereempno 7369 dbms output put line myemp ename myemp job end 异常处理 在运行程序时出现的错误叫做异常发生异常后 语句将停止执行 控制权转移到PL SQL块的异常处理部分异常有两种类型 预定义异常 当PL SQL程序违反Oracle规则或超越系统限制时隐式引发用户定义异常 用户可以在PL SQL块的声明部分定义异常 自定义的异常通过RAISE语句显式引发 预定义异常 Others处理所有异常在用户试图将重复的值存储在使用唯一索引的数据库列中时出现DUP VAL ON INDEX在将字符串转换为数字时出现INVALID NUMBER在表中不存在请求的行时出现NO DATA FOUND在执行SELECTINTO语句后返回多行时出现TOO MANY ROWS在以零作为除数时出现ZERO DIVIDE 预定义异常 declaremynameemp ename type beginselectenameintomynamefromemp Exceptionwhentoo many rowsthendbms output put line 值过多 end 预定义异常 declaremynameemp ename type beginselectenameintomynamefromempwhereempno 1 Exceptionwhenno data foundthendbms output put line 没有数据 end 用户自定义异常 declaremyExceptionException beginif thenraisemyException endif ExceptionwhenmyExceptionthen end 流程控制 条件结构If语句Case语句循环结构Loop循环While循环For循环 条件结构 1 if then endif 2 if then elseif endif endif 嵌套if3 if then elsif then else endif 多重if4 case when endcase 编写一个PL SQL块 用户输入一个员工名 如果该员工的工资低于2000 则将该员工工资增长10 接收用户输入的两个数 通过条件结构判断两个数的关系declarenum1number num2number beginnum1 declaregradevarchar2 10 begingrade 循环结构 使用三种循环实现输出100次我爱你 1 无条件 无限 循环 跳出循环exit exitwhen declarecounternumber 0 beginloopcounter counter 1 dbms output put line counter 我爱你 ifcounter 100then exit 退出循环 endif exitwhencounter 100 endloop end 2 有条件循环declarecounternumber 0 beginwhile counter 100 loopcounter counter 1 dbms output put line counter 我爱你 endloop end 3 循环固定次数 数字区间 下限 上限beginfornumin1 100loopdbms output put line num 我爱你 endloop end 动态SQL 应用 1 DDL语句命令和会话控制语句不能在PL SQL中直接使用 但是可以通过动态SQL来执行2 在运行时 根据需要动态构建SQL语句执行 动态SQL 动态SQL是指在PL SQL程序执行时生成的SQL语句编译程序对动态SQL不做处理 而是在程序运行时动态构造语句 对语句进行语法分析并执行执行语法 executeimmediate sql语句 into变量列表 将查询的值赋值给变量 using参数列表 使用参数填补占位符 name Eg 利用动态SQL建表 beginexecuteimmediate createtabletest tidnumber end 问题 能否建表后执行插入数据操作 Eg 根据员工编号获得员工姓名 declaremynameemp ename type beginexecuteimmediate selectenamefromempwhereempno no intomynameusing 问题 如果Sql语句有多个条件怎么写 总结 1 赋值运算符2 连接操作符3 name 接受用户输入参数值4 name动态SQL中占位符 使用using填补 第六章游标管理 什么是游标显示游标的使用游标类型的使用隐式游标的使用 什么是游标 提问 请编写pl sql块 打印出所有的参加考试的学员信息 分析问题 pl sql块中无法使用select语句 使用变量也无法保存多行数据 解决问题 要是Oracle提供类似于Java集合的结构就能轻松解决问题 显示游标1 显式游标在PL SQL块的声明部分定义查询 该查询可以返回多行显式游标的操作过程 数据库 打开游标 提取行 变量 关闭游标 显示游标2 定义 CURSORIS FORUPDATE FORUPDATEOF字段 操作 OPEN 打开游标FETCHINTO变量1 变量2 变量3 变量n 或者FETCHINTO行对象 取出游标当前位置的值CLOSE 关闭游标 显示游标属性 NOTFOUND 如果FETCH语句失败 则该属性为 TRUE 否则为 FALSE FOUND 如果FETCH语句成功 则该属性为 TRUE 否则为 FALSE ROWCOUNT 返回游标当前的行数 ISOPEN 如果游标是开的则返回 TRUE 否则为 FALSE 输出所有员工编号与姓名 declarerowempemp rowtype 行变量cursorempcurisselect fromemp 定义游标beginopenempcur 打开游标loopfetchempcurintorowemp dbms output put line rowemp empno rowemp ename exitwhenempcur notfound endloop closeempcur 关闭游标end For循环游标 循环游标用于简化游标处理代码自动打开游标 提取记录 关闭游标当用户需要从游标中提取所有记录时使用循环游标的语法如下 FORINLOOPENDLOOP 是类型为 rowtype的记录变量使用游标for循环的时候不能使用open语句 fetch语句和close语句 否则会产生错误 输出所有员工编号与姓名 declarecursorempcurisselect fromemp 定义游标beginforrowempinempcurloopdbms output put line rowemp empno rowemp ename endloop end 带参数的游标 提高游标的灵活性语法 CURSOR 参数列表 IS 根据部门编号输出该部门员工姓名 declarecursorempcur dtnoemp deptno type isselect fromempwheredeptno dtno 定义游标beginforrowempinempcur 20 loopdbms output put line rowemp ename endloop end 游标加锁 FORUPDATE FORUPDATEOF字段 即在程中有 UPDATE INSERT DELETE 语句对数据库操作时 游标自动给指定的表或者字段加锁 防止同时有别的程序对指定的表或字段进行 UPDATE INSERT DELETE 操作 在使用 DELETE UPDATE 后还可以在程序中使用CURRENTOF子句引用当前行进行更新操作 使用游标更新工资 翻倍 declarecursormycursorisselectsalfromempforupdate beginforrinmycursorloopupdateempsetsal sal 2wherecurrentofmycursor endloop end REF游标与游标变量 REF游标和游标变量用于处理运行时动态执行的SQL查询创建游标变量需要两个步骤 声明REF游标类型声明REF游标类型的变量用于声明REF游标类型的语法为 TYPEISREFCURSOR RETURN 打开游标变量的语法如下 OPENcursor nameFORselect statement 获取部门10的工资 declareTypesalcursorisrefcursor 声明游标类型salcurvarsalcursor 定义游标变量mysalnumber beginopensalcurvarforselectsalfrommyempwheredeptno 10 loopfetchsalcurvarintomysal dbms output put line mysal exitwhensalcurvar notfound endloop end 游标变量优点 游标变量的功能强大 可以简化数据处理 游标变量的优点有 可从不同的SELECT语句中提取结果集可以作为过程的参数进行传递可以引用游标的所有属性可以进行赋值运算 使用游标变量执行动态Sql declareTypesalcursorisrefcursor 声明游标类型salcurvarsalcursor 定义游标变量mysalnumber beginopensalcurvarfor selectsalfrommyempwheredeptno dno 动态SQL字符串using 隐式游标 在PL SQL中使用DML语句时自动创建隐式游标隐式游标自动声明 打开和关闭 其游标名称为SQL通过检查隐式游标的属性可以获得最近执行的DML语句的信息 隐式游标属性 NOTFOUND 如果DML语句没有影响到任何行时 则该属性为 TRUE 否则为 FALSE FOUND 如果DML语句影响到一行或多行时 则该属性为 TRUE 否则为 FALSE ROWCOUNT 返回DML语句影响的行数 ISOPEN 游标是否打开 隐式游标始终为FALSE使用 游标名称 属性SQL NOTFOUND 隐式游标属性 beginupdateempsetcomm 100whereempno 7369 ifSQL Found truethendbms output put line 更新成功 dbms output put line 受影响的行数 SQl rowcount endif IfSQL NotFound truethendbms output put line 不存在 endif end 总结 游标用于处理查询结果集中的数据游标类型有 隐式游标 显式游标和REF游标隐式游标由PL SQL自动定义 打开和关闭显式游标用于处理返回多行的查询在声明REF游标时 不需要将SELECT语句与其关联 第七章子程序与程序包 什么是子程序子程序一 存储过程子程序二 函数子程序三 自主事务处理组织子程序 程序包 子程序1 命名的PL SQL块 编译并存储在数据库中 子程序的各个部分 声明部分可执行部分异常处理部分 可选 子程序的分类 过程 执行某些操作函数 执行操作并返回值 子程序2 子程序的优点 模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限 使数据更安全 子程序一 存储过程 过程是用于完成特定任务的子程序例如 前往售票厅 询问关于车票的信息 排队等候 在柜台购买车票 子程序一 存储过程 创建简单过程createorreplaceprocedureSayHiasbegindbms output put line hi end 子程序一 存储过程 调用 1 在命令窗口下executeSayHi 2 在SQLWindow下beginSayHi end 子程序一 存储过程 带参数过程 参数不用指定长度createorreplaceprocedureSayHi strvarchar2 asbegindbms output put line str end 子程序一 存储过程 根据员工编号查询员工姓名 并打印 没找到数据则异常处理createorreplaceprocedureSearchName emp nonumber isempnamevarchar 20 beginselectenameintoempnamefromempwhereempno emp no dbms output put line empname end 子程序一 存储过程 过程参数的三种模式 IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值 并向调用程序返回更新的值 子程序一 存储过程 根据员工编号返回员工姓名给另一程序createorreplaceprocedureSearchName emp noinnumber empnameoutvarchar2 isbeginselectenameintoempnamefromempwhereempno emp no end 子程序一 存储过程 declareemp nonumber empnamevarchar2 20 beginemp no 子程序一 存储过程 交换两个数createorreplaceprocedureSwap num1inoutnumber num2inoutnumber istempnumber begintemp num1 num1 num2 num2 temp end 子程序一 存储过程 declarenum1number 10 num2number 20 begindbms output put line num1 num1 dbms output put line num2 num2 Swap num1 num2 dbms output put line 调用过程后 dbms output put line num1 num1 dbms output put line num2 num2 end 子程序一 存储过程 返回游标的存储过程createorreplaceproceduregetAllEmp returnCursoroutsys refcursor asbeginopenreturnCursorforselect fromemp end 使用存储过程返回的游标declaretypeclasscursorisrefcursor mycursorclasscursor var empemp rowtype begingetAllEmp returnCursor mycursor loopfetchmycursorintovar emp dbms output put line var emp ename exitwhenmycursor notfound endloop end 子程序一 存储过程 创建过程的语法 create orreplace procedure过程名称 参数列表 is as 变量 begin可执行部分 Exception end 子程序一 存储过程 使用Java程序调用几种存储过程 Test java 子程序二 函数 function 函数是可以返回值的命名的PL SQL子程序 参数与返回值类型不用指定长度 子程序二 函数 简单函数createorreplacefunctionFSayHireturnvarchar2isbeginreturn Hi end 子程序二 函数 调用函数1 结合查询selectFSayHifromdual 2 返回值给变量declarestrvarchar2 20 beginstr FSayHi dbms output put line str end 子程序二 函数 带参函数createorreplacefunctionFSayHi strvarchar2 returnvarchar2isbeginreturnstr end selectFSayHi 大家好 fromdual 子程序二 函数 通过员工编号返回员工姓名createorreplacefunctionFSearchName emp nonumber returnvarchar2isempnamevarchar2 20 beginselectenameintoempnamefromempwhereempno emp no returnempname end selectFSearchName 7369 fromdual 子程序二 函数 创建函数语法create orreplace function函数名称 参数列表 returnis asbegin可执行部分 Exception end 子程序二 函数 删除过程与函数dropprocedure过程名 dropfunction函数名 授予其他用户执行子程序的权限grantexecuteon子程序名to用户名 过程与函数比较 子程序三 自主事务处理 自主事务处理主事务处理启动独立事务处理然后主事务处理被暂停自主事务处理子程序内的SQL操作然后终止自主事务处理恢复主事务处理PRAGMAAUTONOMOUS TRANSACTION用于标记子程序为自主事务处理 子程序三 自主事务处理 自主事务处理测试 创建过程1 执行删除员工7369操作 并回滚创建过程2 执行删除员工7499操作 并调用过程1创建程序调用过程2问哪个删除操作会回滚 子程序三 自主事务处理 createorreplaceproceduretestp1is pragmaautonomous transaction 标识子程序为自主事务处理begindeletefrommyempwhereempno 7369 rollback end 子程序三 自主事务处理 createorrep