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子程序序三：：自主主事务务处理理crea