ORACLE深入浅出中级篇

ORACL深入浅出主讲人：邹振兴1第一章事务与锁什么是事务？如何Oracle使用事务。在Java中使用事务。什么是锁？演示更新丢失的问题。锁的分类。阻塞与死锁。2什么是事务什么是事务？事务是一种机制，这种机制能保证在事务内的DML(数据操作语句)做为一个不可分割的单元进行执行。举例说明事务。演示sqlservert2005事务处理。演示Oracle事务处理。事务四大特性(ACID)原子性(A)一致性(C)隔离性(I)永久性(D)3什么是事务事务处理的两种方式显示事务：用户通过编码方式开启事务隐式事务：数据库默认认为所有操作都是事务比较sqlserver2005与Oracle事务处理方式的区别。4如何Oracle使用事务Oracel事务关键字Savepointa;设置事务保存点Rollbacktoa;回滚到事务保存点a,事务不结束Commit;提交事务，事务结束Rollback;回滚所有事务，事务结束讲解Oracel程序员一天5在JavaJdbc中使用事务演示如何在JavaJdbc中如何使用事务驱动包位置：oracle安装路径->oracle->jdbc->lib->classes12.jar演示案例：DBUtil.java6什么是锁生活中的锁：为什么会有锁的出现。锁的作用就是保护私有的空间。Oracle中的锁：演示Oracle中更新丢失的问题。讲解此问题的两种思路：一、在第一个用户修改之前不允许其它用户修改。二、在修改之前，查看此数据的版本号。7锁的分类悲观锁定此锁持悲观态度，认为在用户修改数据期间，一定会有用户再对其修改。那么此锁会在用户事务内一直保持锁定状态，其它用户无法进行修改。在查询语句后接forupdatenowait此可加锁。查询多少数据，锁定多少数据。乐观锁定此锁持乐观态度，认为在用户修改数据期间，其它用户不会再对其修改，直到最后提交数据时才判断数据是否已更改。8锁的分类乐观锁定的实现方式：增加标识列来区分是否已修改通过OracleORA_ROWSCN机制来实现乐观锁定9阻塞与死锁如果你有两个会话，每个会话都持有另一个会话想要的资源，此时就会出现死锁（deadlock）。例如，如果我的数据库中有两个表A和B，每个表中都只有一行，就可以很容易地展示什么是死锁。我要做的只是打开两个会话（例如，两个SQL*Plus会话）。在会话A中更新表A，并在会话B中更新表B。现在，如果我想在会话B中更新表A，就会阻塞。会话A已经锁定了这一行。这不是死锁；只是阻塞而已。我还没有遇到过死锁，因为会话A还有机会提交或回滚，这样会话B就能继续。10阻塞与死死锁如果我再再回到会会话A，，试图更更新表B，这就就会导致致一个死死锁。要要在这两两个会话话中选择择一个作作为“牺牺牲品””，让它它的语句句回滚。。例如，，会话B中对对表A的的更新新可能回回滚，得得到以下下错误：：想要更更新表B的会会话A还还阻塞塞着，Oracle不不会回回滚整个个事务。。只会回回滚与死死锁有关关的某条条语句。。会话B仍然然锁定着着表B中中的行行，而会会话A还还在耐耐心地等等待这一一行可用用。收到到死锁消消息后，，会话B必须须决定将将表B上上未执执行的工工作提交交还是回回滚，或或者继续续走另一一条路，，以后再再提交。。一旦这这个会话话执行提提交或回回滚，另另一个阻阻塞的会会话就会会继续，，好像什什么也没没有发生生过一样样。11总结开发多用用户、数数据库驱驱动的应应用时，，最大的的难点之之一是：：一方面面要力争争取得最最大限度度的并发发访问，，与此同同时还要要确保每每个用户户能以一一致的方方式读取取和修改改数据。。为此就就有了锁锁定（locking）机制制，这也也是所有有数据库库都具有有的一个个关键特特性，Oracle在在这方方面更是是技高一一筹。不不过，Oracle的的这些些特性的的实现是是Oracle所特有有的，就就像SQLServer的的实现现只是SQLServer特有有的一样样，应用用执行数数据处理理时，要要正确地地使用这这些机制制，而这这一点要要由你（（应用的的开发人人员）来来保证。。12第五章使使用PL/SQLPL/SQL简简介变量、常常量数据类型型流程控制制异常处理理13PL/SQL简简介PL/SQL是是Oracle在sql标准准上的加加强，使使用Oracle能够够基于对对象编程程T-SQL是基基于事务务的结构构化查询询语言两者都是是对SQL的扩扩展14PL/SQL块块简介PL/SQL块块是构构成PL/SQL程程序的的基本组组织单元元，可以以编写存存储过程程、函数数、包、、触发器器等。PL/SQL分分为三三个部分分：声明明部分、、可执行行部分和和异常处处理部分分15PL/SQL块块简介演示：PL/SQL打印HelloWorld。演示：如何在在PL/SQL块中向向表中增增加一条条记录。。演示：如何在在PL/SQL块中修修改表中中数据。。演示：查询学学员'张张秋丽'的年龄龄。16PL/SQL块块小结结每一个PL/SQL块块将会自自动开启启一个事事务，在在PL/SQL块中必必须结束束事务，，否则，，将会阻阻塞。PL/SQL块块每条语语句后，，必须带带上’;’，表表示语句句结束。。PL/SQL块块中不能能执行select语语句进行行显示。。在命令行行中执行行pl-sql块需加加/。在命令行行中打印印变量需需setserverouton;17变量与常常量在PL/SQL块的声声明部分分声明变变量或常常量语法法：：declaresalnumber;piconstantnumber:=3.14;赋值值1、、使使用用赋赋值值运运算算符符““:=””2、使用SELECTINTO语句3、接收用户户输入18变量与常量示示例Declaremynamevarchar2(20)notnull:=‘张三’;myagenumber;mypiconstantnumber:=3.14;Beginmyage:=26;selectenameintomynamefromempwhereempno=7369;--输出End;19日期时间日期时间类型型存储日期和时时间数据常用的两种日日期时间类型型DATETIMESTAMPdeclaremydatedate;mytimetimestamp;beginselectsysdateintomydatefromdual;selectsysdateintomytimefromdual;dbms_output.put_line(mydate);dbms_output.put_line(mytime);end;20布尔类型（boolean）用于存储逻辑辑值(TRUE、FALSE和NULL)不能向数据库库中插入BOOLEAN数据不能将列值保保存到BOOLEAN变变量中只能对BOOLEAN变变量执行逻辑辑操作21属性类型用于引用数据据库列的数据据类型，以及及表示表中一一行的记录类类型列类型%type，语法法：表名.列名%type行类型%rowtype，语法：表表名%rowtype优点不需要知道被被引用的表列列的具体类型型如果被引用对对象的数据类类型发生改变变，PL/SQL变量量的数据类型型也随之改变变22属性类型示例例问题1：假设设现在不知道道emp表Job列的类类型，但要保保存员工7369的岗位位到变量中，，如何定义？？declaremyjobemp.Job%type;beginselectJobintomyjobfromempwhereempno=7369;dbms_output.put_line(myjob);end;23属性类型示例例问题2：假设设要输出emp表员工7369的所所有信息，怎怎么办？定义义8个列类型型变量，依次次输出？declaremyempemp%rowtype;beginselect*intomyempfromempwhereempno=7369;dbms_output.put_line(myemp.ename||myemp.job);end;24异常处处理在运行行程序序时出出现的的错误误叫做做异常常发生异异常后后，语语句将将停止止执行行，控控制权权转移移到PL/SQL块块的异异常处处理部部分异常有有两种种类型型：预定义义异常常-当当PL/SQL程程序违违反Oracle规规则或或超越越系统统限制制时隐隐式引引发用户定定义异异常-用用户户可以以在PL/SQL块块的声声明部部分定定义异异常，，自定定义的的异常常通过过RAISE语语句显显式引引发25预定义义异常常Others处处理理所有有异常常在用户户试图图将重重复的的值存存储在在使用用唯一一索引引的数数据库库列中中时出出现DUP_VAL_ON_INDEX在将字字符串串转换换为数数字时时出现现INVALID_NUMBER在表中中不存存在请请求的的行时时出现现NO_DATA_FOUND在执行SELECTINTO语句句后返回回多行时时出现TOO_MANY_ROWS在以零作作为除数数时出现现ZERO_DIVIDE26预定义异异常declaremynameemp.ename%type;beginselectenameintomynamefromemp;Exceptionwhentoo_many_rowsthendbms_output.put_line('值过多多');end;27预定义异常常declaremynameemp.ename%type;beginselectenameintomynamefromempwhereempno=1;Exceptionwhenno_data_foundthendbms_output.put_line('没有数数据');end;28用户自定义义异常declaremyExceptionException;beginif...thenraisemyException;endif;ExceptionwhenmyExceptionthen...end;29流程控制条件结构If语句Case语语句循环结构Loop循循环While循环For循环环30条件结构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%。31接收用用户输输入的的两个个数，，通过过条件件结构构判断断两个个数的的关系系declarenum1number;num2number;beginnum1:=‘‘&请请输入入一个个数’’;num2:='&num2';--接接收用用户输输入'&name'ifnum1<num2thendbms_output.put_line('小小于');elsifnum1>num2thendbms_output.put_line('大大于');elsedbms_output.put_line('等等于');endif;end;32declaregradevarchar2(10);begingrade:='&grade';casegradewhen'A'thendbms_output.put_line('优优秀');when'B'thendbms_output.put_line('良良好');when'C'thendbms_output.put_line('一一般');when'D'thendbms_output.put_line('差差');elsedbms_output.put_line('没这这个');endcase;end;33循环结结构使用三三种循循环实实现输输出100次我我爱你你341)无无条件件（无无限））循环环--跳跳出循循环exit,exitwhen...declarecounternumber:=0;beginloopcounter:=counter+1;dbms_output.put_line(counter||'：我我爱你你');--ifcounter=100then--exit;--退退出循循环--endif;exitwhencounter=100;endloop;end;352)有有条件件循环环declarecounternumber:=0;beginwhile(counter<100)loopcounter:=counter+1;dbms_output.put_line(counter||':我我爱你你');endloop;end;363)循循环固固定次次数:数字字区间间：下下限..上上限beginfornumin1..100loopdbms_output.put_line(num||':我我爱你你');endloop;end;37动态SQL应用：：1、DDL语语句命命令和和会话话控制制语句句不能能在PL/SQL中中直接接使用用，但但是可可以通通过动动态SQL来来执执行2、在在运行行时，，根据据需要要动态态构建建SQL语语句执执行38动态SQL动态SQL是是指指在PL/SQL程程序执执行时时生成成的SQL语语句句编译程程序对对动态态SQL不不做处处理，，而是是在程程序运运行时时动态态构造造语句句、对对语句句进行行语法法分析析并执执行执行语语法：：executeimmediate'sql语语句'[into变变量列列表]--将查查询的的值赋赋值给给变量量[using参参数数列表表]--使使用参参数填填补占占位符符（:name）39Eg：：利用用动态态SQL建建表beginexecuteimmediate'createtabletest(tidnumber)';end;问题：能能否建表表后执行行插入数数据操作作？40Eg：根根据员工工编号获获得员工工姓名declaremynameemp.ename%type;beginexecuteimmediate'selectenamefromempwhereempno=:no'intomynameusing'&no';dbms_output.put_line(myname);end;问题：如如果Sql语句句有多个个条件怎怎么写？？41总结1、:=赋值运运算符2、||连接操操作符3、'&name'接接受用户户输入参参数值4、:name动态态SQL中占位位符，使使用using填补42第六章游游标管管理什么是游游标显示游标标的使用用游标类型型的使用用隐式游标标的使用用43什么是游游标提问：请请编写pl-sql块块，打印印出所有有的参加加考试的的学员信信息。分析问题题：pl-sql块中中无法使使用select语句句，使用用变量也也无法保保存多行行数据。。解决问题题：要是是Oracle提供类类似于Java集合的的结构就就能轻松松解决问问题。44显示游标标1显式游标标在PL/SQL块块的声声明部分分定义查查询，该该查询可可以返回多行行显式游标标的操作作过程：：数据库打开游标标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量关闭游标标45显示游标标2定义：CURSOR<游标标名>IS<SELECT语语句>[FORUPDATE|FORUPDATEOF字字段];操作：OPEN<游游标名>;--打开开游标FETCH<游标名名>INTO变量量1,变变量2,变量3,....变变量n;或或者FETCH<游标名名>INTO行对对象;--取出出游标当当前位置置的值CLOSE<游标名名>;--关关闭游标标46显示游标标属性%NOTFOUND--如如果FETCH语句失失败，则则该属性性为"TRUE"，否否则为"FALSE";%FOUND--如如果FETCH语句成成功，则则该属性性为"TRUE"，否否则为"FALSE";%ROWCOUNT--返返回游标标当前的的行数;%ISOPEN--如果游游标是开开的则返返回"TRUE"，否否则为"FALSE";47输出所有有员工编编号与姓姓名declarerowempemp%rowtype;--行行变量cursorempcurisselect*fromemp;--定义义游标beginopenempcur;--打开开游标loopfetchempcurintorowemp;dbms_output.put_line(rowemp.empno||rowemp.ename);exitwhenempcur%notfound;endloop;closeempcur;--关关闭游标标end;48For循循环游标标循环游标标用于简简化游标标处理代代码自动打开开游标，，提取记记录，关关闭游标标当用户需需要从游游标中提提取所有有记录时时使用循环游标标的语法法如下：：FOR<record_index>IN<cursor_name>LOOP<executablestatements>ENDLOOP;<record_index>是类类型为%rowtype的记记录变量量使用游标标for循环的的时候不不能使用用open语句句，fetch语句和和close语语句，否则会产生生错误。49输出所有员员工编号与与姓名declarecursorempcurisselect*fromemp;--定定义游标beginforrowempinempcurloopdbms_output.put_line(rowemp.empno||rowemp.ename);endloop;end;50带参数的游游标提高游标的的灵活性语法：CURSOR<游游标名>(参数列表表)IS<SELECT语句>;51根据部门编编号输出该该部门员工工姓名declarecursorempcur(dtnoemp.deptno%type)isselect*fromempwheredeptno=dtno;--定义游游标beginforrowempinempcur(20)loopdbms_output.put_line(rowemp.ename);endloop;end;52游标加锁[FORUPDATE|FORUPDATEOF字字段]即在程中有有“UPDATE””,“INSERT”,“DELETE”语句句对数据库库操作时，，游标自动动给指定的的表或者字字段加锁，，防止同时时有别的程程序对指定定的表或字字段进行““UPDATE”,“INSERT””,“DELETE”操作。。在使用“DELETE”,““UPDATE”后后还可以在在程序中使使用CURRENTOF<游标名名>子句句引用当前前行进行更更新操作。53使用游标更更新工资，，翻倍declarecursormycursorisselectsalfromempforupdate;beginforrinmycursorloopupdateempsetsal=sal*2wherecurrentofmycursor;endloop;end;54REF游标标与游标变变量REF游游标和游标标变量用于于处理运行行时动态执执行的SQL查查询创建游标变变量需要两两个步骤：：声明REF游标标类型声明REF游标标类型的变变量用于声明REF游游标类型型的语法为为：TYPE<ref_cursor_name>ISREFCURSOR[RETURN<return_type>];打开游标变变量的语法法如下：OPENcursor_nameFORselect_statement;55获取部门10的工资资declareTypesalcursorisrefcursor;--声明明游标类型型salcurvarsalcursor;--定义游游标变量mysalnumber;beginopensalcurvarforselectsalfrommyempwheredeptno=10;loopfetchsalcurvarintomysal;dbms_output.put_line(mysal);exitwhensalcurvar%notfound;endloop;end;56游标变量优点点游标变量的功功能强大，可可以简化数据据处理。游标变量的优优点有：可从不同的SELECT语句中中提取结果集集可以作为过程程的参数进行行传递可以引用游标标的所有属性性可以进行赋值值运算57使用游标变量量执行动态SqldeclareTypesalcursorisrefcursor;--声明游标类类型salcurvarsalcursor;--定义游标标变量mysalnumber;beginopensalcurvarfor'selectsalfrommyempwheredeptno=:dno'--动态SQL字符串串using‘‘&no’’;--填补补占位符的参参数loopfetchsalcurvarintomysal;dbms_output.put_line(mysal);exitwhensalcurvar%notfound;endloop;end;58隐式游标在PL/SQL中使用DML语句时自动创建隐式式游标隐式游标自动动声明、打开开和关闭，其其游标名称为为SQL通过检查隐式式游标的属性性可以获得最最近执行的DML语句句的信息59隐式游游标属属性%NOTFOUND--如如果DML语句句没有有影响响到任任何行行时，，则该该属性性为"TRUE"，，否则则为"FALSE";%FOUND--如果果DML语语句影影响到到一行行或多多行时时，则则该属属性为为"TRUE"，否否则为为"FALSE";%ROWCOUNT--返返回DML语句句影响响的行行数;%ISOPEN-游游标标是否否打开开，隐隐式游游标始始终为为FALSE使用：：游标标名称称+属属性SQL%NOTFOUND60隐式游游标属属性beginupdateempsetcomm=100whereempno=7369;ifSQL%Found=truethendbms_output.put_line('更更新成成功');dbms_output.put_line('受受影响响的行行数'||SQl%rowcount);endif;IfSQL%NotFound=truethendbms_output.put_line(‘不不存在在');endif;end;61总结游标用用于处处理查查询结结果集集中的的数据据游标类类型有有：隐隐式游游标、、显式式游标标和REF游游标标隐式游游标由由PL/SQL自自动动定义义、打打开和和关闭闭显式游游标用用于处处理返返回多多行的的查询询在声明明REF游游标时时，不不需要要将SELECT语语句与与其其关联联62第七章章子子程序序与程程序包包什么是是子程程序子程序序一：：存储储过程程子程序序二：：函数数子程序序三：：自主主事务务处理理组织子子程序序：程程序包包63子程序序1命名的的PL/SQL块块，，编译译并存存储在在数据据库中中。子程序序的各各个部部分：：声明部部分可执行行部分分异常处处理部部分(可选选)子程序序的分分类：：过程－－执执行行某些些操作作函数－－执执行行操作作并返返回值值64子程序序2子程序序的优优点：：模块化化将程序序分解解为逻逻辑模模块可重用用性可以被被任意意数目目的程程序调调用可维护护性简化维维护操操作安全性性通过设设置权权限，，使数数据更更安全全65子程序序一：：存储储过程程过程是是用于于完成成特定定任务务的子子程序序例如：前往售票厅厅询问关于车车票的信息息排队等候在柜台购买买车票66子程序一：：存储过程程创建简单过过程createorreplaceprocedureSayHiasbegindbms_output.put_line('hi');end;67子程序一：：存储过程程调用--1、在在命令窗口口下executeSayHi;--2、在在SQLWindow下beginSayHi();end;68子程序一：：存储过程程带参数过程程,参数不不用指定长长度createorreplaceprocedureSayHi(strvarchar2)asbegindbms_output.put_line(str);end;69子程序一：：存储过程程根据员工编编号查询员员工姓名，，并打印，，没找到数数据则异常常处理createorreplaceprocedureSearchName(emp_nonumber)isempnamevarchar(20);beginselectenameintoempnamefromempwhereempno=emp_no;dbms_output.put_line(empname);end;70子程序一：存存储过程过程参数的三三种模式：IN用于接受调用用程序的值默认的参数模模式OUT用于向调用程程序返回值INOUT用于接受调用用程序的值，，并向调用程程序返回更新新的值71子程序一：：存储过程程根据员工编编号返回员员工姓名给给另一程序序createorreplaceprocedureSearchName(emp_noinnumber,empnameoutvarchar2)isbeginselectenameintoempnamefromempwhereempno=emp_no;end;72子程序一：：存储过程程declareemp_nonumber;empnamevarchar2(20);beginemp_no:='&no';SearchName(emp_no,empname);dbms_output.put_line(empname);end;73子程序一：：存储过程程交换两个数数createorreplaceprocedureSwap(num1inoutnumber,num2inoutnumber)istempnumber;begintemp:=num1;num1:=num2;num2:=temp;end;74子程序一：：存储过程程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;75子程序一：：存储过程程--返返回回游游标标的的存存储储过过程程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;76子程程序序一一：：存存储储过过程程创建建过过程程的的语语法法：：create[orreplace]procedure过程程名名称称[(参参数数列列表表)]is|as[变变量量]begin可执执行行部部分分;[Exception]end;77子程程序序一一：：存存储储过过程程使用用Java程程序序调调用用几几种种存存储储过过程程。。Test.java78子程程序序二二：：函函数数(function)函数数是是可可以以返返回回值值的的命命名名的的PL/SQL子子程程序序。。参数数与与返返回回值值类类型型不不用用指指定定长长度度79子程程序序二二：：函函数数简单单函函数数createorreplacefunctionFSayHireturnvarchar2isbeginreturn'Hi';end;80子程序二二：函数数调用函数数1、结合合查询selectFSayHifromdual;2、返回回值给变变量declarestrvarchar2(20);beginstr:=FSayHi;dbms_output.put_line(str);end;81子程序二二：函数数带参函数数createorreplacefunctionFSayHi(strvarchar2)returnvarchar2isbeginreturnstr;end;selectFSayHi('大大家好')fromdual;82子程序二二：函数数--通过过员工编编号返回回员工姓姓名createorreplacefunctionFSearchName(emp_nonumber)returnvarchar2isempnamevarchar2(20);beginselectenameintoempnamefromempwhereempno=emp_no;returnempname;end;selectFSearchName(7369)fromdual;83子程序二二：函数数创建函数数语法create[orreplace]function函数名称称[(参参数列表表)]returnis|asbegin可执行部部分;[Exception]end;84子程序二二：函数数删除过程程与函数数dropprocedure过过程名;dropfunction函数数名;授予其他他用户执执行子程程序的权权限grantexecuteon子程程序名to用用户名名;85过程与函函数比较较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN

语句86子程序三三：自主主事务处处理自主事务务处理主事务处处理启动动独立事事务处理理然后主事事务处理理被暂停停自主事务务处理子子程序内内的SQL操操作然后终止止自主事事务处理理恢复主事事务处理理PRAGMAAUTONOMOUS_TRANSACTION用用于标标记子程程序为自自主事务务处理87子程序三三：自主主事务处处理自主事务务处理测测试：创建过程1，执行删删除员工7369操操作，并回回滚创建过程2，执行删删除员工7499操操作，并调调用过程1创建程序调调用过程2问哪个删除除操作会回回滚？88子程序三：：自主事务务处理createorreplaceproceduretestp1is--pragmaautonomous_transaction;--标识子子程序为自自主事务处处理begindelete