第5讲-2_Oracle_编程(二).ppt

1、第5讲 ORACLE编程基础（二）,主要内容,PL/SQL简介与运行环境,PL/SQL程序块的组成,事务控制命令,PL/SQL流程控制,游标的使用,PL/SQL的异常处理,存储过程、函数、触发器,事务的概念,事务控制命令,5.4 事务控制命令,并发控制,事务的概念,当用户建立与数据库的会话后，用户就可以对数据库进行操作，而用户对数据库的操作是一个个事务来进行的。 事务是指在相邻两条事务控制命令之间的一组sql语句，它是对数据库操作的逻辑单元，并且可以避免不一致的数据。 事务的开始位置是从前一个事务结束以后执行的第一条SQL语句，或者在连接到该数据库以后所执行的第一条SQL语句。 事务的结束是使

2、用COMMIT或ROLLBACK等显式语句标识的 。,事务的控制语句,事务的控制命令有如下三种形式： 5.4.1 事务提交(COMMIT) 5.4.2 事务回退(ROLLBACK) 5.4.3 设置保存点命令(SAVEPOINT),5.4.1 事务提交(COMMIT),提交命令用于提交自上次提交以后对数据库中数据所作的改动。提交命令会结束用户的事务处理，可以使对数据的改变永久化。 在执行提交命令之前，数据库中的数据（永久存储介质上的数据）并没有发生任何改变，用户本人可以通过查询命令查看对数据库操作的结果，但是整个网络上的其他用户并没有看到你对数据库所作的改动。 无论事务处理的规模如何，提交的响

3、应通常差别很小。因为在数据库中进行提交之前，已经完成了所有实际的工作，已经修改了数据库中的数据，完成了99.9%的工作。,5.4.1 事务提交(COMMIT),COMMIT命令的语法如下： COMMIT；,Oracle中设置了一个自动提交的开关，如果把它设置为ON,那么所有的改变数据库中数据的语句都会立即生效，影响数据库中的数据，缺省值为OFF。格式：SET AUTOCOMMIT ON|OFF。 用户主动断开与oracle的连接，这时用户当前的事务将被自动提交。,5.4.2 事务回退(ROLLBACK),在尚未对数据库提交的时候，可以用事务回退命令ROLLBACK，将数据库回退到上次COMMI

4、T后的状态。 事务完成的所有工作都被取消，好像没有执行这些命令一样。 -如果插入了一行，那么回滚就删除它；如果更新了一行，回滚就将其更新到原来的样子；如果删除了一行，就重新插入它。 事务所需要执行的所有加锁处理都被释放掉； 一旦事务已提交，就不能再使用事务回退命令进行回退了。 ROLLBACK语句的语法是： ROLLBACK;,5.4.2 事务回退(ROLLBACK),当程序检测出一个错误而不能继续工作时，通常都使用显式的ROLLBACK语句。 如果一个会话断开与数据库的连接，而没有使用COMMIT或ROLLBACK终止当前的事务时，那么该事务会自动被数据库撤销。 用户进程被意外中止，这是用户

5、当前的事务被回滚。 用户只有在必要时才可以进行回滚，它是开销很大的操作。,5.4.3 设置保存点命令(SAVEPOINT),如果只需回退事务的一部分时，可以使用保存点，将整个事务划分为若干部分，这样就可以回退部分事务了。 保存点的设置和使用格式： SAVEPOINT ； 设置了保存点后就可以回退到指定的保存点了，语法格式如下： ROLLBACK TO ;,5.4.4 事务处理示例,例：在数据库中有一个表，表名为score_table,表有五个域：学号、姓名、班级、数学、语文成绩。我们用Oracle的SQL*Plus工具来完成3个事务。 SQL select * from score_table

6、; NO NAME CLASS MATHS CHINESE - - - - - 1 陈静 3(1) 88 67 2 张华 3(1) 87 87 3 郑军 3(1) 81 99 4 邓超 3(1) 93 91 SQL delete from score_table where name=陈静; 已删除 1 行。 SQL commit; 提交完成。,5.4.4 事务处理示例,SQL select * from score_table; NO NAME CLASS MATHS CHINESE - - - - - 2 张华 3(1) 87 87 3 郑军 3(1) 81 99 4 邓超 3(1) 93

7、 91,5.4.4 事务处理示例,SQL delete from score_table; 已删除3行。 SQL select * from score_table; 未选定行 SQL rollback; 回退已完成。,5.4.4 事务处理示例,SQL select * from score_table; NO NAME CLASS MATHS CHINESE - - - - - 2 张华 3(1) 87 87 3 郑军 3(1) 81 99 4 邓超 3(1) 93 91,并发控制,Oracle是一个典型的多用户数据库系统，也就是说，多个用户会同时建立与数据库的会话。当一个用户建立与数据库会

8、话，也就会有多个事务对数据库进行操作，就会发生多个事务操作相互干扰的问题。因此，Oracle必须提供一种机制，以保证所有事务在执行时都能够得到正确的结果，这就是基于事务的并发控制。 为了维护事务的一致性，Oracle使用锁机制防止其他用户修改一个未完成事务中的数据。,锁,锁是由Oracle系统自动管理的，它对用户而言是透明的。锁的持续时间等于被提交事务的处理时间。 比如，当用户要更新数据表中的记录时，Oracle会自动完成对该项资源的加锁和解锁。用户也可以用lock table语句显式对要访问的资源加锁。,锁,Oracle中的锁分为三类： DDL锁：在使用create、truncate或者al

9、ter语句时，由Oracle自动对相关资源施加，以确保在语句执行过程中，没有其他事务访问。 DML锁：在事务处理的开始被施加，而且事务处理完成时被释放。 内部锁：由Oracle来管理，以保护内部数据库结构，例如数据文件等。,条件控制,循环控制,GOTO跳转控制语句,5.5 PL/SQL 流程控制,循环的嵌套,一、IF_THEN_ELSE语句语法: IF condition THEN Statements 1; Statements 2; . ELSE Statements 1; Statements 2; . END IF,5.5.1 条件控制,一、IF_THEN_ELSE语句语法示例如下:

10、declare a number; b number; begin a:=3; b:=4; if ab then dbms_output.put_line(a|大于|b); else dbms_output.put_line(a|小于|b); end if; end;,5.5.1 条件控制,下面的例子是查询是否有该学生的成绩，没有就添加进来，将所有科目成绩都置为0。 DECLARE counter number(2); math_score number(3,0); chinese_score number(3,0); BEGIN SELECT count(*) INTO counter FR

11、OM score_table WHERE name=陈静; IF counter0 THEN SELECT maths,chinese INTO math_score,chinese_score FROM score_table WHERE name=陈静; DBMS_OUTPUT.PUT_LINE(math_score); DBMS_OUTPUT.PUT_LINE (chinese_score); ELSE INSERT INTO score_table VALUES(1,陈静, 3(1), 0,0); END IF; END;,5.5.1 条件控制,IF 可以嵌套，可以在IF 或IF .E

12、LSE语句中使用IF或IF.ELSE语句。 程序中的DBMS_OUTPUT是Oracle内置包，该包允许使用SQL*Plus和SQLPlus工作单从PL/SQL中显示输出信息。 DBMS_OUTPUT.PUT_LINE允许显示一行文本 ，该过程接受要接受一个变量，该变量可以是字符串类型、数值类型或者日期类型。 它在调试程序中非常有用，在使用之前需要在SQLPlus中设置，用SET命令将SERVEROUTPUT置为ON即可。,5.5.1 条件控制,二、IF_THEN_ELSIF语句 IF condition1 THEN statement1; ELSIF condition2 THEN stat

13、ement2; ELSIF condition3 THEN statement3; ELSE statement4; END IF; statement5;,5.5.1 条件控制,下面的示例判断某一年是否为闰年，闰年判断条件：年号能被4整除但是不能被100整除，或者能被400整除。 declare year_date number; leap boolean; begin year_date:=2008; if mod(year_date,4)0 then leap:=false; elsif mod(year_date,100)0 then leap:=true; elsif mod(yea

14、r_date,400)0 then leap:=false; else leap:=true; end if; if leap then dbms_output.put_line(year_date|是闰年); else dbms_output.put_line(year_date|是平年); end if; end;,5.5.1 条件控制,下面的例子演示了判断某同学在表记录中是否存在，如果存在，再根据该同学的成绩增加学费或者减免学费，例子中IF_THEN_ELSIF语句嵌套在IF_THEN_ELSE语句中.,5.5.1 条件控制,DECLARE counter number(2); math

15、_score number(3,0); chinese_score number(3,0); BEGIN SELECT count(*) INTO counter FROM score_table WHERE name=邓超; IF counter0 THEN SELECT maths,chinese INTO math_score,chinese_score FROM score_table WHERE name=邓超; IF math_score95 AND chinese_score95 THEN UPDATE student_fee SET allfee=allfee-100 WHER

16、E name=邓超; ELSIF math_score90 AND chinese_score90 THEN UPDATE student_fee SET allfee=allfee-50 WHERE name=邓超; ELSIF math_score60 OR chinese_score60 THEN UPDATE student_fee SET allfee=allfee+100 WHERE name=邓超; ELSE DBMS_OUTPUT.PUT_LINE(math_score); DBMS_OUTPUT.PUT_LINE (chinese_score); END IF; ELSE D

17、BMS_OUTPUT.PUT_LINE(没有这位同学); END IF; COMMIT; END;,三、Case表达式 Case语句的基本格式如下： Case 变量 WHEN 表达式1 then值1 WHEN 表达式2 then值2 WHEN 表达式3 then值3 WHEN 表达式4 then值4 ELSE 值5 END; Case语句的功能：首先设定变量的值作为条件，然后顺序检查表达式，一旦从中找到与条件匹配的表达式值，就停止case语句的处理。,5.5.1 条件控制,三、CASE语句 set serveroutput on declare i number:=1; begin case

18、i when 0 then dbms_output.put_line(i is zero); when 1 then dbms_output.put_line(i is one); else dbms_output.put_line(i is more than one); end case; end;,5.5.1 条件控制,执行上面的程序块时，每次循环，执行的都是查找与i匹配的when常量的相关语句。所有其他语句都会被跳过。,5.5.1 条件控制,（1）直到型循环 （ loopexitend循环控制语句） 语法格式： LOOP EXIT WHEN ； END LOOP; 先执行循环体，然后判

19、断，如果条件为真，则结束循环，否则继续循环。直到型循环的循环体至少执行1次。,5.5.2 循环控制,示例用直到型循环求1100所有整数的和。 variable sum number DECLARE I number(3):=100; BEGIN :sum:=0; LOOP :sum:=:sum+I; I:=I-1; EXIT WHEN I = 0; END LOOP; END; / print sum; 全局变量的引用时，必须加上:。,5.5.2 循环控制,（2）FOR循环 FOR.LOOP语法： FOR counter IN REVERSE start_range.end_range LOO

20、Pstatements; END LOOP; LOOP和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的，counter是一个隐式声明的变量，初始值是start_range，第二个值是start_range+1，直到end_range，如果start_range等于end _range，那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。,5.5.2 循环控制,例：从键盘接受一个整数，计算它的阶乘，并在屏幕上打印出来。 variable fac number DECLARE NUM NUMBER(3):=5; BEGIN :fac:=1; IF NUM0

21、 THEN FOR I in 1.NUM LOOP :fac:=:fac * I; END LOOP; END IF; END; / PRINT fac; 如果要退出for循环可以使用EXIT语句。,5.5.2 循环控制,set serveroutput on begin for i in 0.5 loop case i when 0 then dbms_output.put_line(i is zero); when 1 then dbms_output.put_line(i is one); when 2 then dbms_output.put_line(i is two); when

22、3 then dbms_output.put_line(i is three); when 4 then dbms_output.put_line(i is four); else dbms_output.put_line(i is five); end case; end loop; end;,5.5.2 循环控制,使用reverse关键字 例：输出20到1之间能被3整除的数。 begin for i in reverse 1 . 20 loop if mod(i,3)=0 then dbms_output.put_line(i); end if; end loop; end; /,5.5.

23、2 循环控制,嵌套的for循环 例：打印一个由星号“*”组成的三角形。 begin for x in 1 . 5 loop for y in 1.2*x-1 loop dbms_output.put (*); end loop; dbms_output.put_line( ); end loop; end; /,5.5.2 循环控制,（3）WHILE循环 WHILE循环的语法是： WHILE condition LOOP Sequence_of_statement END LOOP; WHILE.LOOP中condition条件与循环相联系，如果条件为TRUE,则执行循环体内的语句，如果结果为

24、FALSE,则结束循环，当前循环的循环体可能一次也不执行。,5.5.2 循环控制,示例用WHILE循环求1100所有整数的和 DECLARE summ number :=0; i number(3):=100; BEGIN WHILE i0 LOOP summ:=summ+i; i:=i - 1; END LOOP; DBMS_OUTPUT.PUT_LINE(summ); END;,5.5.2 循环控制,循环结构有三种类型： 简单循环结构：一直运行，直到显式地结束循环为止。 for循环：重复运行指定的次数。 while循环：一直运行，直到满足指定的条件为止。 练习：编写循环结构，当计数器cou

25、nter的值大于20时，循环结束。 1.使用ifthen条件判断的简单循环结构。 2.使用exit when条件判断的简单循环结构。 3.使用for循环结构。 4.使用while循环结构。,5.5.2 循环控制,1.使用ifthen条件判断的简单循环结构。 counter:=0; loop counter:=counter+1; if counter0 then exit; end loop;,5.5.2 循环控制,程序块的内部可以有另一个程序块这种情况称为嵌套。 嵌套要注意的是变量，定义在最外部程序块中的变量可以在所有子块中使用，如果在子块中定义了与外部程序块变量相同的变量名，在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块