第09章__PLSQL

1、第第9章章 PL/SQL教学目标教学目标w 理解理解PL/SQL程序块的结构和其基本结构程序块的结构和其基本结构w 怎样捕获和控制用户代码中的异常（异常）怎样捕获和控制用户代码中的异常（异常）w 掌握使用游标技术在用户代码中将掌握使用游标技术在用户代码中将SQL与与PL/SQL集成集成w 理解过程、函数的基本概念和特点，掌握存理解过程、函数的基本概念和特点，掌握存储过程、函数的具体应用储过程、函数的具体应用qPL/SQL是基于是基于Ada编程语言的结构化编程语言，是编程语言的结构化编程语言，是由由Oracle公司从版本公司从版本6开始提供的专用于开始提供的专用于Oracle产品的产品的数据库编

2、程语言。数据库编程语言。qPL/SQL为为Oracle数据库提供的过程化编程语言数据库提供的过程化编程语言q用户可以使用用户可以使用PL/SQL语言编写过程、函数、程序包语言编写过程、函数、程序包、触发器等、触发器等PL/SQL代码，并且把这些代码存储起来，代码，并且把这些代码存储起来，以便由具有适当权限的数据库用户重新使用。以便由具有适当权限的数据库用户重新使用。9.1 PL/SQL基础基础示例程序块示例程序块9.1.1PL/SQL的特点的特点q与与SQL语言紧密集成。语言紧密集成。q减小网络流量，提高应用程序的运行性能。减小网络流量，提高应用程序的运行性能。q模块化的程序设计功能，提高了系

3、统可靠性。模块化的程序设计功能，提高了系统可靠性。q服务器端程序设计，可移植性好。服务器端程序设计，可移植性好。9.1.2 PL/SQL执行过程执行过程PL/SQL块块SQL语句语句客户端应用程序客户端应用程序PL/SQL引擎引擎数据库服务器数据库服务器过程化语句执行器过程化语句执行器SQL执行器执行器块中块中SQL语句语句9.1.3 PL/SQL块的组成块的组成qPL/SQL是一种块结构的语言，组成是一种块结构的语言，组成PL/SQL程序程序的单元是逻辑块，一个的单元是逻辑块，一个PL/SQL 程序包含了一个或程序包含了一个或多个逻辑块，每个块都可以划分为三个部分。多个逻辑块，每个块都可以划

4、分为三个部分。 DECLARE 声明部分，定义变量、数据类型、异常、局部子程序等声明部分，定义变量、数据类型、异常、局部子程序等 BEGIN 执行部分，实现块的功能执行部分，实现块的功能 EXCEPTION 例外（例外（异常异常）处理部分，处理程序执行过程中产生的异常处理部分，处理程序执行过程中产生的异常 END; begin dbms_output.put_line(hello world!); end;输入输入/来执行来执行显示显示 hello world注意：注意：q执行部分是必须的，而声明部分和异常（异常）部分是执行部分是必须的，而声明部分和异常（异常）部分是可选的可选的q可以在一个块

5、的执行部分或异常处理部分嵌套其他的可以在一个块的执行部分或异常处理部分嵌套其他的PL/SQL块；块；q所有的所有的PL/SQL块都是以块都是以“END;”结束。结束。q若要在若要在SQL*Plus环境中看到环境中看到DBMS_OUTPUT.PUT_LINE方法的输出结果，必须将环境变量方法的输出结果，必须将环境变量SERVEROUTPUT设设置为置为ON。q PL/SQL中中标识符标识符以字母开头，后面可以是字母、数以字母开头，后面可以是字母、数字、字、$、下划线和、下划线和#q 标识符最长不超过标识符最长不超过30个字符个字符q 标识符的作用范围：视具体情况而定标识符的作用范围：视具体情况而

6、定程序块、子程序、包等程序块、子程序、包等9.1.4 PL/SQL标识符标识符q PL/SQL运算符运算符q 逻辑运算：逻辑运算：AND、OR、NOTq 算术运算：算术运算：+、-、*、/、*(幂幂) q 关系运算：关系运算：=、!=、=、=、LIKE、BETWEEN x AND y、IS NULLq 集合运算：集合运算：IN(属于属于) q 字符串运算：字符串运算：+、-、|(连接连接) 9.1.5 PL/SQL运算符运算符PL/SQL中运算符的优先级中运算符的优先级9.2 PL/SQL变量、常量和数据类型变量、常量和数据类型 用户使用的所有变量和常量都必须在程序块中用户使用的所有变量和常量

7、都必须在程序块中的声明部分定义。的声明部分定义。 对于每一个变量，用户都必须规定名称和数据对于每一个变量，用户都必须规定名称和数据类型，以便在可执行部分为其赋值。类型，以便在可执行部分为其赋值。 变量就是指可以由程序读取或赋值的存储单元。变量就是指可以由程序读取或赋值的存储单元。变量用于临时存放数据，变量中的数据随着程序的变量用于临时存放数据，变量中的数据随着程序的运行而变化。运行而变化。变量定义的基本格式为：变量定义的基本格式为： (宽度宽度):=；9.2.1变量变量例如：定义一个长度为例如：定义一个长度为10B的变量的变量count，其初始值为，其初始值为1，是，是varchar2类型。类

8、型。count varchar2(10) := 1;1.变量命名规则变量命名规则 变量名必须以变量名必须以字母开头字母开头，由字母、数字、下划线、美，由字母、数字、下划线、美元和英镑符号等特殊符号组成，但最好不要用中文。元和英镑符号等特殊符号组成，但最好不要用中文。 变量名不区分大小写。变量名不区分大小写。 变量名最长为变量名最长为30个字符。个字符。 变量名中不能包括任何形式的空白变量名中不能包括任何形式的空白(空格或制表符等空格或制表符等). 不能使用不能使用SQL或或PL/SQL的保留字为变量名。的保留字为变量名。可以在声明变量的同时给变量强制性的加上可以在声明变量的同时给变量强制性的加

9、上NOT NULLNOT NULL约束条件，此时变量在初始化时必须赋值。约束条件，此时变量在初始化时必须赋值。declare v_string varchar2(20); begin v_string:=hello world!; dbms_output.put_line(输入的字符内容是：输入的字符内容是：|v_string); end;显示显示 hello world9.2.2 常量常量 常量的值在程序内部不能改变，常量的值在定义常量的值在程序内部不能改变，常量的值在定义时赋予，声明方式与变量相似，但必须包括关键字时赋予，声明方式与变量相似，但必须包括关键字CONSTANTCONSTANT

10、。变量定义的基本格式为：变量定义的基本格式为： CONSTANT :=；例如：声明一个常量例如：声明一个常量PI：PI CONSTANT NUMBER := 3.14159;q部分常用部分常用 PL/SQL标量标量数据类型数据类型9.2.3 常用数据类型常用数据类型qPL/SQL中的变量和常量必须先定义然后才能使用中的变量和常量必须先定义然后才能使用qv_bonus NUMBER(8,2);qv_name VARCHAR2(30) := SCOTT;qv_hiredate DATE := 13-4月月-09;qv_valid BOOLEAN := TRUE;提问？提问？w 如果想要对某一张表中

11、的某一个数据列进如果想要对某一张表中的某一个数据列进行操作，怎么知道对应列的类型是什么？行操作，怎么知道对应列的类型是什么？9.2.4 PL/SQL记录记录 在在PL/SQL程序中，除了可以应用程序中，除了可以应用SQL中可以运中可以运用的各种类型外，还可以用户自定义数据类型以及通用的各种类型外，还可以用户自定义数据类型以及通过过%TYPE和和%ROWTYPE等引用表中的列和行数据等引用表中的列和行数据类型。类型。1 使用使用%TYPE v定义一个类型与某个变量的数据类型或数据库表定义一个类型与某个变量的数据类型或数据库表中某个列的数据类型一致，利用中某个列的数据类型一致，利用%TYPE。v一

12、旦表中指定列的数据类型被修改，则在程序执一旦表中指定列的数据类型被修改，则在程序执行时，将自动引用新的对应数据类型。行时，将自动引用新的对应数据类型。语法格式：语法格式： 变量名变量名 表名表名. .列表列表%TYPE;%TYPE; 例：声明变量例：声明变量v_dname与与dept表的表的dname列具有相列具有相同的数据类型：同的数据类型：w v_dname dept.dname%TYPE; %TYPE类型描述符的优势：类型描述符的优势：q简化变量的定义简化变量的定义q自动保持某些变量在数据类型上的一致性自动保持某些变量在数据类型上的一致性减少程序维护工作减少程序维护工作2 记录类型记录类

13、型 如果需要一次把一行记录中的多列的数据读取如果需要一次把一行记录中的多列的数据读取出来，并用于处理时，此时需要应用记录类型。出来，并用于处理时，此时需要应用记录类型。q定义记录类型的语法定义记录类型的语法TYPE record_type_name IS RECORD( field1 data_type, field2 data_type, );q引用记录类型变量的域引用记录类型变量的域记录变量名记录变量名.域名域名DECLARE TYPE worker_record_type IS RECORD ( id NUMBER(3), name VARCHAR2(20) ); worker_reco

14、rd worker_record_type;BEGIN worker_record.id:=10; worker_:=Jack; DBMS_OUTPUT.PUT_LINE(worker_record.id |:|worker_);END;例：记录类型应用例：记录类型应用 提问？提问？w 如果想要对某一张表中的某行进行操作，如果想要对某一张表中的某行进行操作，怎么知道行中不同列的类型是什么？怎么知道行中不同列的类型是什么？3 使用使用%ROWTYPE 当需要一次性的读到所有列数据到变量中时，当需要一次性的读到所有列数据到变量中时，可以应用可以应用PL/

15、SQLPL/SQL提供的提供的%ROWTYPE%ROWTYPE技术来自动提取表技术来自动提取表中行的结构信息，并自动生成对应的行数据类型。中行的结构信息，并自动生成对应的行数据类型。 语法格式：语法格式：变量名变量名 表名表名%ROWTYPE; %ROWTYPE; 例：声明变量例：声明变量v_dept_rec为为dept表的表结构表的表结构类型：类型：w v_dept_rec dept%ROWTYPE; 编程时使用编程时使用%TYPE%TYPE、%ROWTYPE%ROWTYPE方式声明变量，使变方式声明变量，使变量声明的类型与表中的保持同步，随表的变化而变化，量声明的类型与表中的保持同步，随表

16、的变化而变化，这样的程序在一定程度上具有更强的通用性这样的程序在一定程度上具有更强的通用性引用记录类型变量引用记录类型变量v_dept_rec的域的域v_dept_rec. dnamePL/SQL中的命名规范中的命名规范q从数据库中查询从数据库中查询/提取出数据，并赋值给相关变量提取出数据，并赋值给相关变量qSELECT INTO语法语法q SELECT DISTINCT | ALL * | select_item , select_item.q INTO variable_name , variable_name.| record_nameq FROM table_reference | T

17、HE (subquery) alias q , table_reference | THE (subquery) alias.q rest_of_statement rest_of_statement.;9.3 PL/SQL中的中的SELECT语句语句DECLARE v_bonus NUMBER(8,2); v_name VARCHAR2(30) := SCOTT;BEGIN SELECT sal * 0.10 INTO v_bonus FROM emp WHERE ename = v_name;END;例：例： SELECT INTO简单应用简单应用注意：注意：w SELECTINTO语句只

18、能查询一个记录的信息，如果语句只能查询一个记录的信息，如果没有查询到任何数据没有查询到任何数据,会产生会产生NO_DATA_FOUND异常；异常；w 如果查询到多个记录，则会产生如果查询到多个记录，则会产生TOO_MANY_ROWS异常。异常。w INTO句子后的变量用于接收查询的结果，变量的个数句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。型的变量。提问？以下代码有什么错误提问？以下代码有什么错误用用SELECTINTO语句查询语句查询10号部门所有员工信息。号部门所有员工信息。DECLAR

19、E v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE deptno=10; END; /*第第 1 行出现错误行出现错误:ORA-01422: 实际返回的行数超出请求的行数实际返回的行数超出请求的行数ORA-06512: 在在line 4显示运行结果显示运行结果DECLARE v_bonus NUMBER(8,2); v_name VARCHAR2(30) := SCOTT;BEGIN SELECT sal * 0.10 INTO v_bonus FROM emp WHERE ename = v_name; DBMS_OUT

20、PUT.PUT_LINE(v_bonus);END;例：例： SELECT INTO(%ROWTYPE)例：查询例：查询DEPT表中表中10号部门的具体信息号部门的具体信息提问提问查的结果放到哪里？查的结果放到哪里？如何显示结果？如何显示结果？需要一个和需要一个和DEPT表结构相同的记录。表结构相同的记录。显示时取出记录中各个字段的值。显示时取出记录中各个字段的值。DECLARE v_dept_rec dept%ROWTYPE; BEGIN SELECT * INTO v_dept_rec FROM dept WHERE deptno=10; DBMS_OUTPUT.PUT_LINE(10号部

21、门的名称是：号部门的名称是： | v_dept_rec.dname); DBMS_OUTPUT.PUT_LINE(10号部门的信息如下：号部门的信息如下：); DBMS_OUTPUT.PUT_LINE(编编 号：号： | v_dept_rec.deptno); DBMS_OUTPUT.PUT_LINE(名名 称：称： | v_dept_rec.dname); DBMS_OUTPUT.PUT_LINE(驻在地：驻在地： | v_dept_rec.loc);END;例：例： SELECT INTO(%TYPE)例：查询雇员（例：查询雇员（EMP）表中雇员编号为）表中雇员编号为7369的雇的雇员的姓

22、名和职位具体信息员的姓名和职位具体信息提问？提问？姓名和职位两个字段的类型是什么？姓名和职位两个字段的类型是什么？查的结果放到哪里？查的结果放到哪里？需要两个和需要两个和EMP表的表的empno,job列类型相同的变量列类型相同的变量DECLARE v_ename emp.ename%TYPE; v_str emp.job%TYPE; BEGIN SELECT ename,job INTO v_ename,v_str FROM emp WHERE empno=7369; DBMS_OUTPUT.PUT_LINE(employee 7369| is |v_ename | work is |v_s

23、tr);END;w 提问：如果想要查询不同的员工的信息怎么办？提问：如果想要查询不同的员工的信息怎么办？格式化输出结果格式化输出结果 在在SQL*Plus环境中，可以使用替换变量环境中，可以使用替换变量来临时存储有关的数据。来临时存储有关的数据。q替换变量替换变量 替换变量没有类型，只是一个代码替换操作替换变量没有类型，只是一个代码替换操作 替换变量可不事先定义就直接使用替换变量可不事先定义就直接使用 在在SQL*Plus中使用中使用DEFINE定义替换变量定义替换变量 环境变量环境变量VERIFY用于设定是否显示替换后的语句用于设定是否显示替换后的语句行行1. &替换变量替换变量 在在SEL

24、ECT语句中，如果某个变量前面使用语句中，如果某个变量前面使用了了&符号，那么表示该变量是一个替换变量。符号，那么表示该变量是一个替换变量。 在执行在执行SELECT语句时，系统会提示用户为语句时，系统会提示用户为该变量提供一个具体的值。该变量提供一个具体的值。 替换变量不仅仅可以用在替换变量不仅仅可以用在WHERE子句子句中，而且还可以用在下列部分：中，而且还可以用在下列部分：(1)ORDER BY子句。子句。(2)列表达式。列表达式。(3)表名。表名。(4)整个整个SELECT语句语句 SQL SELECT ename,job 2 FROM emp 3 WHERE empno=&p_eno

25、;输入输入 p_eno 的值的值: 7369原值原值 3: WHERE empno=&p_eno新值新值 3: WHERE empno=7369ENAME JOB- -SMITH CLERK注意：注意：SQLPLUS中进行替换时对用户的输入只检查数据类型。中进行替换时对用户的输入只检查数据类型。SELECTUPDATE、DELETE、INSERT例题：将雇员表中某雇员的薪水增加例题：将雇员表中某雇员的薪水增加100.DECLARE v_empno NUMBER(4);BEGIN v_empno:=&x; UPDATE emp SET sal=sal+100 WHERE empno=v_empn

26、o;END; w 输入输入 x 的值的值: 7844w 原值原值 4: v_empno:=&x;w 新值新值 4: v_empno:=7844;提问？提问？w 如果有多个地方都要使用同一个替代变量如果有多个地方都要使用同一个替代变量怎么办？怎么办？2. &替换变量替换变量 在在SELECT语句中，如果希望重新使用语句中，如果希望重新使用某个变量并且不希望重新提示输入该值，某个变量并且不希望重新提示输入该值，可以使用可以使用&替换变量。替换变量。SQL SELECT &p_eno,ename,job 2 FROM emp 3 WHERE empno=&p_eno;输入输入 p_eno 的值的值:

27、 7499原值原值 1: SELECT &p_eno,ename,job新值新值 1: SELECT 7499,ename,job原值原值 3: WHERE empno=&p_eno新值新值 3: WHERE empno=7499 7499 ENAME JOB- - - 7499 ALLEN SALESMAN3.DEFINE和和ACCEPT命令命令w DEFINE命令用来创建一个数据类型为命令用来创建一个数据类型为CHAR用户定义的变量。用户定义的变量。w UNDEFINE命令可以清除定义的变量。命令可以清除定义的变量。DEFINE命令命令语法格式：语法格式：DEFINE variable=v

28、aluew 不带任何参数，则显示所有用户定义的变量。不带任何参数，则显示所有用户定义的变量。w variable是变量名，是变量名，value是变量的值。是变量的值。w DEFINE value是显示指定变量的值和数据类型是显示指定变量的值和数据类型.w DEFINE variable=value是创建一个是创建一个CHAR类型类型的用户变量，且为该变量赋初值。的用户变量，且为该变量赋初值。 例：查询雇员（例：查询雇员（EMP）表中某雇员的雇员的姓名和职）表中某雇员的雇员的姓名和职位具体信息位具体信息定义一个变量定义一个变量p_empno ，并为它赋值，并为它赋值“7499”。然后。然后，显示

29、该变量信息。，显示该变量信息。 DEFINE p_empno = 7499DEFINE p_empno 显示结果为：显示结果为： DEFINE P_EMPNO = 7499 (CHAR)DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_str emp.job%TYPE; BEGIN SELECT empno,ename,job INTO v_empno,v_ename,v_str FROM emp WHERE empno=&p_empno; DBMS_OUTPUT.PUT_LINE(employee |to_char(v_emp

30、no )| is |v_ename | work is |v_str);END; 如果我想在输入信息之前如果我想在输入信息之前就知道参数的含义怎么办？就知道参数的含义怎么办？ACCEPT命令命令 使用使用ACCEPT命令可以定制一个用户提示，命令可以定制一个用户提示，用来提示用户收入指定的数据。用来提示用户收入指定的数据。w 使用使用ACCEPT定义变量时，可以明确地指定该变定义变量时，可以明确地指定该变量是量是NUMBER数据类型还是数据类型还是DATE数据类型。数据类型。w 为了安全性，还可以把用户的输入隐藏。为了安全性，还可以把用户的输入隐藏。语法格式：语法格式： ACCEPT vari

31、able datatype FORMAT formatPROMPT text HIDEwvariable：指定接收值的变量。：指定接收值的变量。wdatatype为变量数据类型，可以是为变量数据类型，可以是NUMBER、CHAR和和DATE。默认的数据类型为。默认的数据类型为CHAR。wFORMAT：定义由：定义由fromat指定的格式模式。指定的格式模式。wPROMPT：指定由：指定由text确定的在用户输入数据之前确定的在用户输入数据之前显示的提示文本。显示的提示文本。wHIDE指定是否隐藏用户的输入。指定是否隐藏用户的输入。例：查询雇员（例：查询雇员（EMP）表中某雇员的雇员的姓名）表中

32、某雇员的雇员的姓名和职位具体信息和职位具体信息定义一个变量定义一个变量p_empno ，并为它赋值，并为它赋值“7499”。然后，显示该变量信息。然后，显示该变量信息。 ACCEPT p_empno PROMPT 请输入员工号：请输入员工号：显示结果为：显示结果为： 请输入员工号：请输入员工号：7499DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_str emp.job%TYPE; BEGIN SELECT empno,ename,job INTO v_empno,v_ename,v_str FROM emp WHERE e

33、mpno=&p_empno; DBMS_OUTPUT.PUT_LINE(employee |to_char(v_empno )| is |v_ename | work is |v_str);END;q绑定变量绑定变量 使用使用VARIABLE定义定义 使用使用PRINT语句输出绑定变量的值语句输出绑定变量的值 在在PL/SQL块中使用绑定变量方法块中使用绑定变量方法:绑定变量名绑定变量名4.绑定变量绑定变量DEFINE p_empno = 7934VARIABLE g_salary NUMBERBEGIN SELECT sal INTO :g_salary FROM emp WHERE emp

34、no=&p_empno;DBMS_OUTPUT.PUT_LINE(工资值已取出至绑定变量工资值已取出至绑定变量g_salary);END;PRINT g_salary例：查询雇员（例：查询雇员（EMP）表中某雇员的雇员的姓名和职位具薪水信息）表中某雇员的雇员的姓名和职位具薪水信息9.4 流程控制语句流程控制语句 PL/SQL的基本逻辑结构包括顺序结构、条件的基本逻辑结构包括顺序结构、条件结构和循环结构。结构和循环结构。 控制结构是所有程序设计语言的核心。检测控制结构是所有程序设计语言的核心。检测不同条件并加以处理是程序控制的主要部分。不同条件并加以处理是程序控制的主要部分。q分支语句分支语句I

35、FIF 条件条件 THEN 语句序列语句序列;END IF;IF 条件条件 THEN 语句序列语句序列1;ELSE 语句序列语句序列2;END IF;IF 条件条件l THEN 语句序列语句序列1;ELSIF 条件条件2 THEN 语句序列语句序列2;ELSE 语句序列语句序列3;END IF;q分支语句分支语句CASECASE 表达式表达式 WHEN 常量常量1 THEN 语句语句1; ELSE 常量常量x THEN 语句语句x;END CASE;9.4.1 条件结构条件结构例：查询不同等级成绩的评论例：查询不同等级成绩的评论DECLARE grade char:=B;BEGIN IF gr

36、ade = A THEN DBMS_OUTPUT.PUT_LINE(Excellent); ELSIF grade = B THEN DBMS_OUTPUT.PUT_LINE(Very Good); ELSIF grade = C THEN DBMS_OUTPUT.PUT_LINE(Good); ELSIF grade = D THEN DBMS_OUTPUT. PUT_LINE(Fair); ELSIF grade = F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT_LINE(No such grade); END IF;END

37、;DECLARE grade char:=B;BEGINCASE grade WHEN A THEN DBMS_OUTPUT.PUT_LINE(Excellent); WHEN B THEN DBMS_OUTPUT.PUT_LINE(Very Good); WHEN C THEN DBMS_OUTPUT.PUT_LINE(Good); WHEN D THEN DBMS_OUTPUT.PUT_LINE(Fair); WHEN F THEN DBMS_OUTPUT.PUT_LINE(Poor); ELSE DBMS_OUTPUT.PUT_LINE(No such grade); END CASE;

38、END;例：查询不同等级成绩的评论例：查询不同等级成绩的评论q 基本循环语句基本循环语句qLOOPq 语句序列语句序列;q EXIT WHEN 布尔表达式布尔表达式;qEND LOOP;q 数值数值FOR循环语句循环语句qFOR 计数器变量计数器变量 IN REVERSE 下界下界上界上界 LOOPq 语句序列语句序列;qEND LOOP;q WHILE循环语句循环语句qWHILE 条件条件 LOOPq 语句序列语句序列;qEND LOOP;9.4.2 循环控制语句循环控制语句DECLARE n NUMBER:=1;count1 NUMBER:=2;BEGIN LOOPn:=n*count1;

39、count1:=count1+1;EXIT WHEN count1=11; END LOOP; dbms_output.put_line(10的阶乘的阶乘: | to_char(n);END;例：求例：求10的阶乘的阶乘(LOOP-EXIT-WHEN-END)DECLAREn NUMBER:=1;count1 NUMBER:=2;BEGINWHILE count1=3000 THEN RAISE e_user_1; END IF;EXCEPTION WHEN e_user_1 THEN DBMS_OUTPUT.PUT_LINE(发生用户自定义例外发生用户自定义例外e_user_1); INSE

40、RT INTO test_msg(empno,message) VALUES(7788,挣的还可以挣的还可以); COMMIT; WHEN OTHERS THEN ROLLBACK WORK;END;例：错误信息表（自定义异常处理）例：错误信息表（自定义异常处理）定义错误信息表定义错误信息表CREATE TABLE test_msg( empno NUMBER(4), message VARCHAR2(20);当员工工资当员工工资=3000抛出异常提示抛出异常提示9.6 游标游标 游标（游标（cursor）用来管理）用来管理SQL的的SELECT语句。游语句。游标是为处理这些语句而分配的一大块

41、内存。标是为处理这些语句而分配的一大块内存。 Oracle游标是一种用于轻松的处理多行数据的机制游标是一种用于轻松的处理多行数据的机制，游标可看作一种特殊的指针，它与某个查询结果相游标可看作一种特殊的指针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定位置联系，可以指向结果集的任意位置，以便对指定位置的数据进行处理。的数据进行处理。 使用游标可以在查询数据的同时对数据进行处理。使用游标可以在查询数据的同时对数据进行处理。游标的类型游标的类型w 显式游标显式游标w由用户定义、操作，用于处理返回多行数据的由用户定义、操作，用于处理返回多行数据的SELECT查询。查询。w一般用于需要对

42、多条记录按顺序一条一条进行一般用于需要对多条记录按顺序一条一条进行处理的场合，被处理的数据集称为活动数据处理的场合，被处理的数据集称为活动数据w 隐式游标隐式游标w由系统自动进行操作，用于处理由系统自动进行操作，用于处理DML语句和返语句和返回单行数据的回单行数据的SELECT查询。查询。 在使用显式游标时，必须编写四部分代码：在使用显式游标时，必须编写四部分代码：(1) 在在PL/SQL块的块的DECLARE段中定义游标。段中定义游标。(2) 在在PL/SQL块中初始块中初始BEGIN后打开游标。后打开游标。(3) 取游标到一个或多个变量中，在接收游标的取游标到一个或多个变量中，在接收游标的

43、FETCH语句中，接收变量的数目必须与游标的语句中，接收变量的数目必须与游标的SELECT列表列表中的表列数目一致。中的表列数目一致。(4) 使用完后要关闭游标。使用完后要关闭游标。 9.6.1 显式游标显式游标w 在在SCOTT用户下的雇员表中，根据输入的用户下的雇员表中，根据输入的部门号查询某个部门的员工信息，部门号部门号查询某个部门的员工信息，部门号在程序运行时指定。在程序运行时指定。 提问？显示游标应用提问？显示游标应用查询的结果集是单条数据还是多条数据？查询的结果集是单条数据还是多条数据？如何使用游标？如何使用游标？(1)声明游标声明游标 显式游标是作为声明段的一部分进行定义的：显式

44、游标是作为声明段的一部分进行定义的：DECLARE CURSOR cursor_nameISselect_statementw cursor_name是游标名是游标名w Select_statement是是SELECT语句，由该查询产生与所语句，由该查询产生与所声明的游标相关联的结果集。声明的游标相关联的结果集。1 显式游标的定义和使用显式游标的定义和使用说明说明w 游标必须在游标必须在PL/SQL块的声明部分进行定义；块的声明部分进行定义；w 游标定义时可以引用游标定义时可以引用PL/SQLPL/SQL变量，但变量必须在游变量，但变量必须在游标定义之前定义；标定义之前定义；w 定义游标时并没

45、有生成数据，只是将定义信息保存定义游标时并没有生成数据，只是将定义信息保存到数据字典中；到数据字典中；w 游标定义后，可以使用游标定义后，可以使用cursor_name%ROWTYPE定定义游标类型变量。义游标类型变量。w 在在SCOTT用户下的雇员表中，根据输入的部门号查用户下的雇员表中，根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。询某个部门的员工信息，部门号在程序运行时指定。 例：显示游标应用例：显示游标应用定义游标，定义变量定义游标，定义变量DECLARE v_deptno emp.deptno%TYPE; CURSOR c_emp IS SELECT * FROM

46、emp WHERE deptno=v_deptno; v_emp c_emp%ROWTYPE; 声明游标后，要使用游标从中提取数据，就必声明游标后，要使用游标从中提取数据，就必须先打开游标。须先打开游标。格式为：格式为： OPEN cursor_namew 说明说明 检查变量的值检查变量的值 执行游标定义时对应的执行游标定义时对应的SELECTSELECT语句，将查询结果检索语句，将查询结果检索到工作区中。到工作区中。 游标指针指向第一个元组游标指针指向第一个元组 一旦游标打开，就无法再次打开，除非先关闭一旦游标打开，就无法再次打开，除非先关闭 如果游标定义中的变量值发生变化，则只能在下次打如

47、果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。开游标时才起作用。(2) 打开游标打开游标w 在在SCOTT用户下的雇员表中，根据输入的部门号查用户下的雇员表中，根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。询某个部门的员工信息，部门号在程序运行时指定。 例：显示游标应用例：显示游标应用定义游标，定义变量，打开游标定义游标，定义变量，打开游标DECLARE v_deptno emp.deptno%TYPE; CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno; v_emp c_emp%ROWTYPE;B

48、EGIN v_deptno:=&x; OPEN c_emp;(3) 读取数据读取数据 游标打开后，就可以使用游标打开后，就可以使用FETCH语句从中读取数据。语句从中读取数据。FETCH语句的格式为：语句的格式为：FETCH cursor_name INTO variable_name,nw Cursor_name为从中提取数据的游标名，为从中提取数据的游标名，w INTO表示将读取的游标数据存放到指定的变量表示将读取的游标数据存放到指定的变量variable_name中。中。说明说明w 在使用在使用FETCH语句之前必须先打开游标语句之前必须先打开游标w 对游标第一次使用对游标第一次使用FE

49、TCH语句时，游标指针指向语句时，游标指针指向第一条记录，因此第一条记录，因此操作的对象是第一条记录操作的对象是第一条记录，使，使用后，游标指针指向下一条记录。用后，游标指针指向下一条记录。w 游标指针只能向下移动，不能回退游标指针只能向下移动，不能回退w INTO子句中的变量个数、顺序、数据类型必须与子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型工作区中每行记录的字段数、顺序以及数据类型一一对应。一一对应。w 在在SCOTT用户下的雇员表中，根据输入的部门号查用户下的雇员表中，根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。询某个部门的员工信

50、息，部门号在程序运行时指定。 例：显示游标应用例：显示游标应用定义游标，定义变量，打开游标定义游标，定义变量，打开游标,读取数据读取数据DECLARE v_deptno emp.deptno%TYPE; CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno; v_emp c_emp%ROWTYPE;BEGIN v_deptno:=&x; OPEN c_emp;LOOP FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.empno| |

51、 v_emp.ename| | v_emp.sal | | v_deptno); END LOOP; (4)关闭游标关闭游标格式为：格式为：CLOSE cursor_name;关闭游标即关闭关闭游标即关闭SELECT操作，释放所占的内存区。操作，释放所占的内存区。q关闭的游标可以使用关闭的游标可以使用OPEN语句再次打开，由此语句再次打开，由此得到新状态的数据集得到新状态的数据集q可以打开的游标数由参数可以打开的游标数由参数OPEN_CURSOR限定，限定，默认值为默认值为300。w 在在SCOTT用户下的雇员表中，根据输入的部门号查用户下的雇员表中，根据输入的部门号查询某个部门的员工信息，部

52、门号在程序运行时指定。询某个部门的员工信息，部门号在程序运行时指定。 例：显示游标应用例：显示游标应用定义游标，定义变量，打开游标，关闭游标定义游标，定义变量，打开游标，关闭游标DECLARE v_deptno emp.deptno%TYPE; CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno; v_emp c_emp%ROWTYPE;BEGIN v_deptno:=&x; OPEN c_emp; LOOP FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.P

53、UT_LINE(v_emp.empno| | v_emp.ename| | v_emp.sal | | v_deptno); END LOOP; CLOSE c_emp;END; 2 显示游标的属性显示游标的属性q游标的属性可用于游标的属性可用于IF、EXIT等语句，不能直接在等语句，不能直接在SQL语句中使用语句中使用属属 性性 名名说说 明明%ISOPEN逻辑值，判断游标是否已打开，如游标未打开其值逻辑值，判断游标是否已打开，如游标未打开其值为为false，否则为，否则为true%FOUND逻辑值，判断游标是否指向数据行。如游标当前指逻辑值，判断游标是否指向数据行。如游标当前指向一行数据则

54、返回向一行数据则返回true，否则为，否则为false。本属性常用。本属性常用于控制游标循环的结束。于控制游标循环的结束。%NOTFOUND逻辑值，判断游标是否没指向数据行。其值是逻辑值，判断游标是否没指向数据行。其值是%FOUND属性值的非。本属性常用于控制游标循环属性值的非。本属性常用于控制游标循环的结束。的结束。%ROWCOUNT返回游标当前已提取的记录的行数，每成功提取一返回游标当前已提取的记录的行数，每成功提取一次数据行其值加次数据行其值加1q 判断是否有满足条件的被修改记录，如没有，则插入新记录判断是否有满足条件的被修改记录，如没有，则插入新记录UPDATE emp SET sal

55、=sal*1.05 WHERE empno=v_empno;IF SQL%NOTFOUND THEN INSERT INTO emp VALUES (v_empno, v_ename, .);END IF;q 提取并处理提取并处理10条记录条记录LOOP FETCH emp_cursor INTO v_empno, v_ename; EXIT WHEN emp_cursor%ROWCOUNT10 OR emp_cursor%NOTFOUND; END LOOP;q系统隐含地定义一个系统隐含地定义一个 “游标名游标名%ROWTYPE”的的记录类型变量作为循环计数器记录类型变量作为循环计数器q 自

56、动打开游标自动打开游标q 重复地从游标工作区中取出数据重复地从游标工作区中取出数据q 所有记录都被取走后，自动关闭游标所有记录都被取走后，自动关闭游标q游标游标FOR循环语法循环语法FOR 记录型变量名记录型变量名 IN 游标名游标名 LOOP 语句语句; END LOOP;3. 用于游标的用于游标的FOR循环循环w 对SCOTT用户下的雇员表中的雇员，计算其佣金信息：w 佣金=薪水*0.05+奖金*0.25w 如果该雇员没有奖金，以0计算。并将计算结果插入w 到BONUS表（插入3列信息 ename,sal,comm）。提问？游标提问？游标FOR循环应用循环应用w DECLAREw v_bo

57、nus NUMBER;w CURSOR emp_cursor IS SELECT ename,sal,comm FROM emp;w BEGINw DELETE FROM bonus;w FOR emp_rec IN emp_cursor LOOP w v_bonus:= (emp_rec.sal*0.05)+(nvl(emp_m,0)*0.25);w INSERT INTO BONUS(ename,sal,comm)w VALUES(emp_rec.ename,emp_rec.sal,v_bonus);w END LOOP; w COMMIT;w END;q用游标锁定记录用游标锁定记录在定义

58、游标的在定义游标的SELECT语句中带语句中带FOR UPDATE子句子句q在在UPDATE语句中使用游标限定要修改的记录语句中使用游标限定要修改的记录UPDATE table SET column_name = sql_expression WHERE CURRENT OF cursor_name;4.用于游标的用于游标的SELECT和和UPDATE语句语句例例 ： 按年度基本工资额用游标修改按年度基本工资额用游标修改emp表中记录的表中记录的sal字段值，如年基本工资低于字段值，如年基本工资低于30000，sal增加增加20%，否则，否则sal增加增加15%。定义游标定义游标DECLARE

59、 v_sal NUMBER(7,2); CURSOR emp_cur IS SELECT 12*sal FROM emp FOR UPDATE;例：游标修改数据应用例：游标修改数据应用DECLARE v_sal NUMBER(7,2); CURSOR emp_cur IS SELECT 12*sal FROM emp FOR UPDATE;BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_sal; EXIT WHEN emp_cur%NOTFOUND; IF v_sal30000 THEN UPDATE emp SET sal=sal*1.2 WHER

60、E CURRENT OF emp_cur; ELSE UPDATE emp SET sal=sal*1.15 WHERE CURRENT OF emp_cur; END IF; END LOOP; CLOSE emp_cur; COMMIT;END;显示游标的优势显示游标的优势(1) 通过检查通过检查PL/SQL的系统变量的系统变量“%found”或或“%notfound”确认成功或失败。确认成功或失败。(2) 显示游标是在显示游标是在DECLARE段中由用户自己定义的，这段中由用户自己定义的，这样样PL/SQL块的结构化程度更高块的结构化程度更高(定义和使用分离定义和使用分离)。(3) 游标