`

oracle 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常,

阅读更多

游标的概念:
    游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁 盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
游标有两种类型:显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句,一次只能从数据库中提取一行数据,对于这种 形式的查询和DML操作,系统都会使用一个隐式游标。但是如果要提取多行数据,就要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。显式游标 对应一个返回结果为多行多列的SELECT语句。
游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。
隐式游标
如前所述,DML操作和单行SELECT语句会使用隐式游标,它们是:
* 插入操作:INSERT。
* 更新操作:UPDATE。
* 删除操作:DELETE。
* 单行查询操作:SELECT ... INTO ...。
当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字SQL来访问,但要注意, 通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。游标 的属性有四种,如下所示。

Sql代码
  1. 隐式游标的属性 返回值类型   意    义  
  2. SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数  
  3. SQL%FOUND   布尔型 值为TRUE 代表插入、删除、更新或单行查询操作成功  
  4. SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反  
  5. SQL%ISOPEN  布尔型 DML执行过程中为真,结束后为假  
隐式游标的属性	返回值类型	意    义
SQL%ROWCOUNT	整型	代表DML语句成功执行的数据行数
SQL%FOUND	布尔型	值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND	布尔型	与SQL%FOUND属性返回值相反
SQL%ISOPEN	布尔型	DML执行过程中为真,结束后为假


【训练1】 使用隐式游标的属性,判断对雇员工资的修改是否成功。
步骤1:输入和运行以下程序:

Sql代码
  1. SET  SERVEROUTPUT  ON    
  2.         BEGIN   
  3.         UPDATE  emp  SET  sal=sal+100  WHERE  empno=1234;  
  4.          IF SQL%FOUND THEN    
  5.         DBMS_OUTPUT.PUT_LINE('成功修改雇员工资!' );  
  6.         COMMIT ;   
  7.         ELSE   
  8.         DBMS_OUTPUT.PUT_LINE('修改雇员工资失败!' );  
  9.          END  IF;   
  10.         END ;  
SET SERVEROUTPUT ON 
		BEGIN
  		UPDATE emp SET sal=sal+100 WHERE empno=1234;
 		 IF SQL%FOUND THEN 
   		DBMS_OUTPUT.PUT_LINE('成功修改雇员工资!');
   		COMMIT; 
  		ELSE
		DBMS_OUTPUT.PUT_LINE('修改雇员工资失败!');
 		 END IF; 
		END;


运行结果为:

Sql代码
  1. 修改雇员工资失败!  
  2.         PL/SQL 过程已成功完成。  
修改雇员工资失败!
		PL/SQL 过程已成功完成。


步骤2:将雇员编号1234改为7788,重新执行以上程序:
运行结果为:

Sql代码
  1. 成功修改雇员工资!  
  2.         PL/SQL 过程已成功完成。  
成功修改雇员工资!
		PL/SQL 过程已成功完成。


说明:本例中,通过SQL%FOUND属性判断修改是否成功,并给出相应信息。
显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1.声明游标
在DECLEAR部分按以下格式声明游标:
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])]
IS SELECT语句;
参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数,则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句,甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句,但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2.打开游标
在可执行部分,按以下格式打开游标:
OPEN 游标名[(实际参数1[,实际参数2...])];
打开游标时,SELECT语句的查询结果就被传送到了游标工作区。
3.提取数据
在可执行部分,按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[,变量名2...];

FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区,FETCH语句一次返回指针所指的一行数据,要返回多行需重复执行,可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明:
第一种格式中的变量名是用来从游标中接收数据的变量,需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中,需要使用%ROWTYPE事先定义记录变量,这种形式使用起来比较方便,不必分别定义和使用多个变量。
定义记录变量的方法如下:
变量名 表名|游标名%ROWTYPE;
其中的表必须存在,游标名也必须先定义。
4.关闭游标
CLOSE 游标名;
显式游标打开后,必须显式地关闭。游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
以下是使用显式游标的一个简单练习。
【训练1】  用游标提取emp表中7788雇员的名称和职务。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2.         DECLARE    
  3.          v_ename VARCHAR2(10);  
  4.          v_job VARCHAR2(10);  
  5.          CURSOR  emp_cursor  IS    
  6.          SELECT  ename,job  FROM  emp  WHERE  empno=7788;  
  7.          BEGIN   
  8.      OPEN  emp_cursor;  
  9.     FETCH  emp_cursor  INTO  v_ename,v_job;  
  10.         DBMS_OUTPUT.PUT_LINE(v_ename||',' ||v_job);  
  11.         CLOSE  emp_cursor;  
  12.         END ;  
SET SERVEROUTPUT ON
		DECLARE	
 		 v_ename VARCHAR2(10);
 		 v_job VARCHAR2(10);
 		 CURSOR emp_cursor IS 
 		 SELECT ename,job FROM emp WHERE empno=7788;
		 BEGIN
 	 OPEN emp_cursor;
  	FETCH emp_cursor INTO v_ename,v_job;
  		DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);
  		CLOSE emp_cursor;
		END;


执行结果为:

Sql代码
  1. SCOTT,ANALYST  
  2.         PL/SQL 过程已成功完成。   
SCOTT,ANALYST
		PL/SQL 过程已成功完成。 


说明:该程序通过定义游标emp_cursor,提取并显示雇员7788的名称和职务。
作为对以上例子的改进,在以下训练中采用了记录变量。
【训练2】  用游标提取emp表中7788雇员的姓名、职务和工资。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2.         DECLARE   
  3.          CURSOR  emp_cursor  IS    SELECT  ename,job,sal  FROM  emp  WHERE  empno=7788;  
  4.          emp_record emp_cursor%ROWTYPE;  
  5.         BEGIN   
  6. OPEN  emp_cursor;      
  7.         FETCH  emp_cursor  INTO  emp_record;  
  8.            DBMS_OUTPUT.PUT_LINE(emp_record.ename||',' || emp_record.job|| ',' || emp_record.sal);  
  9.          CLOSE  emp_cursor;  
  10.         END ;  
SET SERVEROUTPUT ON
		DECLARE
 		 CURSOR emp_cursor IS  SELECT ename,job,sal FROM emp WHERE empno=7788;
 		 emp_record emp_cursor%ROWTYPE;
		BEGIN
OPEN emp_cursor;	
   		FETCH emp_cursor INTO emp_record;
		   DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);
 		 CLOSE emp_cursor;
		END;


执行结果为:

Sql代码
  1. SCOTT,ANALYST,3000  
  2.         PL/SQL 过程已成功完成。   
SCOTT,ANALYST,3000
		PL/SQL 过程已成功完成。 


说明:实例中使用记录变量来接收数据,记录变量由游标变量定义,需要出现在游标定义之后。
注意:可通过以下形式获得记录变量的内容:
记录变量名.字段名。
【训练3】  显示工资最高的前3名雇员的名称和工资。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2.         DECLARE   
  3.          V_ename VARCHAR2(10);  
  4.         V_sal NUMBER(5);  
  5.         CURSOR  emp_cursor  IS    SELECT  ename,sal  FROM  emp  ORDER   BY  sal  DESC ;  
  6.         BEGIN   
  7.          OPEN  emp_cursor;  
  8.          FOR  I  IN  1..3 LOOP  
  9.            FETCH  emp_cursor  INTO  v_ename,v_sal;  
  10.          DBMS_OUTPUT.PUT_LINE(v_ename||',' ||v_sal);  
  11.           END  LOOP;  
  12.          CLOSE  emp_cursor;  
  13.          END ;  
SET SERVEROUTPUT ON
		DECLARE
 		 V_ename VARCHAR2(10);
  		V_sal NUMBER(5);
  		CURSOR emp_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;
		BEGIN
 		 OPEN emp_cursor;
 		 FOR I IN 1..3 LOOP
		   FETCH emp_cursor INTO v_ename,v_sal;
		 DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);
 		  END LOOP;
  		 CLOSE emp_cursor;
		 END;


执行结果为:

Sql代码
  1. KING,5000  
  2.      SCOTT,3000  
  3.      FORD,3000  
  4.      PL/SQL 过程已成功完成。  
 KING,5000
		 SCOTT,3000
		 FORD,3000
		 PL/SQL 过程已成功完成。


  说明:该程序在游标定义中使用了ORDER BY子句进行排序,并使用循环语句来提取多行数据。
游标循环
【训练1】  使用特殊的FOR循环形式显示全部雇员的编号和名称。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2. DECLARE   
  3.   CURSOR  emp_cursor  IS    
  4.   SELECT  empno, ename  FROM  emp;  
  5. BEGIN   
  6. FOR  Emp_record  IN  emp_cursor LOOP     
  7.     DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);  
  8.     END  LOOP;  
  9.     END ;  
SET SERVEROUTPUT ON
DECLARE
  CURSOR emp_cursor IS 
  SELECT empno, ename FROM emp;
BEGIN
FOR Emp_record IN emp_cursor LOOP   
    DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);
	END LOOP;
	END;


执行结果为:

Sql代码
  1. 7369SMITH  
  2. 7499ALLEN  
  3. 7521WARD  
  4. 7566JONES  
  5.          PL/SQL 过程已成功完成。  
7369SMITH
7499ALLEN
7521WARD
7566JONES
   		 PL/SQL 过程已成功完成。


  说明:可以看到该循环形式非常简单,隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量,循环的执行次数与游标取得的数据的行数相一致。
【训练2】  另一种形式的游标循环。

Sql代码
  1. SET  SERVEROUTPUT  ON    
  2. BEGIN   
  3.  FOR  re  IN  ( SELECT  ename  FROM  EMP)  LOOP  
  4.   DBMS_OUTPUT.PUT_LINE(re.ename)  
  5.  END  LOOP;  
  6. END ;  
SET SERVEROUTPUT ON 
BEGIN
 FOR re IN (SELECT ename FROM EMP)  LOOP
  DBMS_OUTPUT.PUT_LINE(re.ename)
 END LOOP;
END;


执行结果为:

Sql代码
  1. SMITH  
  2. ALLEN  
  3. WARD  
  4. JONES  
SMITH
ALLEN
WARD
JONES


    说明:该种形式更为简单,省略了游标的定义,游标的SELECT查询语句在循环中直接出现。
显式游标属性
虽然可以使用前面的形式获得游标数据,但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如下所示。

Sql代码
  1. 游标的属性   返回值类型   意    义  
  2. %ROWCOUNT   整型  获得FETCH 语句返回的数据行数  
  3. %FOUND  布尔型 最近的FETCH 语句返回一行数据则为真,否则为假  
  4. %NOTFOUND   布尔型 与%FOUND属性返回值相反  
  5. %ISOPEN 布尔型 游标已经打开时值为真,否则为假  
游标的属性	返回值类型	意    义
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真,否则为假


可按照以下形式取得游标的属性:
游标名%属性
要判断游标emp_cursor是否处于打开状态,可以使用属性emp_cursor%ISOPEN。如果游标已经打开,则返回值为“真”,否则为“假”。具体可参照以下的训练。
【训练1】  使用游标的属性练习。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2. DECLARE   
  3.   V_ename VARCHAR2(10);  
  4.   CURSOR  emp_cursor  IS    
  5.   SELECT  ename  FROM  emp;  
  6. BEGIN   
  7.  OPEN  emp_cursor;  
  8.  IF emp_cursor%ISOPEN THEN   
  9. LOOP  
  10.    FETCH  emp_cursor  INTO  v_ename;  
  11.    EXIT WHEN  emp_cursor%NOTFOUND;  
  12.    DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-' ||v_ename);  
  13.   END  LOOP;  
  14.  ELSE   
  15.   DBMS_OUTPUT.PUT_LINE('用户信息:游标没有打开!' );  
  16.  END  IF;  
  17.  CLOSE   emp_cursor;  
  18. END ;  
SET SERVEROUTPUT ON
DECLARE
  V_ename VARCHAR2(10);
  CURSOR emp_cursor IS 
  SELECT ename FROM emp;
BEGIN
 OPEN emp_cursor;
 IF emp_cursor%ISOPEN THEN
LOOP
   FETCH emp_cursor INTO v_ename;
   EXIT WHEN emp_cursor%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
  END LOOP;
 ELSE
  DBMS_OUTPUT.PUT_LINE('用户信息:游标没有打开!');
 END IF;
 CLOSE  emp_cursor;
END;


执行结果为:

Sql代码
  1. 1-SMITH  
  2. 2-ALLEN  
  3. 3-WARD  
  4.  PL/SQL 过程已成功完成。  
1-SMITH
2-ALLEN
3-WARD
 PL/SQL 过程已成功完成。


    说明:本例使用emp_cursor%ISOPEN判断游标是否打开;使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数 据行数并输出;使用循环来获取数据,在循环体中使用FETCH语句;使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行,当 FETCH语句失败时说明数据已经取完,退出循环。
【练习1】去掉OPEN emp_cursor;语句,重新执行以上程序。
游标参数的传递
 【训练1】  带参数的游标。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2.         DECLARE   
  3.             V_empno NUMBER(5);  
  4.             V_ename VARCHAR2(10);  
  5.             CURSOR   emp_cursor(p_deptno NUMBER,     p_job VARCHAR2)  IS   
  6.             SELECT   empno, ename  FROM  emp  
  7.             WHERE    deptno = p_deptno  AND  job = p_job;  
  8. BEGIN   
  9.      OPEN  emp_cursor(10,  'CLERK' );  
  10.     LOOP  
  11.      FETCH  emp_cursor  INTO  v_empno,v_ename;  
  12.      EXIT WHEN  emp_cursor%NOTFOUND;  
  13.      DBMS_OUTPUT.PUT_LINE(v_empno||',' ||v_ename);  
  14.       END  LOOP;  
  15.     END ;  
SET SERVEROUTPUT ON
		DECLARE
  			V_empno NUMBER(5);
  			V_ename VARCHAR2(10);
    		CURSOR 	emp_cursor(p_deptno NUMBER, 	p_job VARCHAR2) IS
		    SELECT	empno, ename FROM emp
    		WHERE	deptno = p_deptno AND job = p_job;
BEGIN
 	 OPEN emp_cursor(10, 'CLERK');
  	LOOP
  	 FETCH emp_cursor INTO v_empno,v_ename;
  	 EXIT WHEN emp_cursor%NOTFOUND;
  	 DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
	  END LOOP;
	END;


执行结果为:

Sql代码
  1. 7934,MILLER  
  2.         PL/SQL 过程已成功完成。  
7934,MILLER
		PL/SQL 过程已成功完成。


说明:游标emp_cursor定义了两个参数:p_deptno代表部门编号,p_job代表职务。语句OPEN emp_cursor(10, 'CLERK')传递了两个参数值给游标,即部门为10、职务为CLERK,所以游标查询的内容是部门10的职务为CLERK的雇员。循环部分用于显示查 询的内容。
【练习1】修改Open语句的参数:部门号为20、职务为ANALYST,并重新执行。
也可以通过变量向游标传递参数,但变量需要先于游标定义,并在游标打开之前赋值。对以上例子重新改动如下:
  【训练2】  通过变量传递参数给游标。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2.         DECLARE   
  3.         v_empno NUMBER(5);  
  4.         v_ename VARCHAR2(10);  
  5.         v_deptno NUMBER(5);  
  6. v_job VARCHAR2(10);  
  7.          CURSOR  emp_cursor  IS   
  8.             SELECT  empno, ename  FROM  emp  
  9.             WHERE    deptno = v_deptno  AND  job = v_job;  
  10.         BEGIN   
  11.          v_deptno:=10;  
  12.          v_job:='CLERK' ;  
  13.          OPEN  emp_cursor;  
  14.         LOOP  
  15.          FETCH  emp_cursor  INTO  v_empno,v_ename;  
  16.            EXIT WHEN  emp_cursor%NOTFOUND;  
  17. DBMS_OUTPUT.PUT_LINE(v_empno||',' ||v_ename);  
  18.          END  LOOP;  
  19.         END ;  
SET SERVEROUTPUT ON
		DECLARE
  		v_empno NUMBER(5);
  		v_ename VARCHAR2(10);
  		v_deptno NUMBER(5);
v_job VARCHAR2(10);
   		 CURSOR emp_cursor IS
    		SELECT empno, ename FROM emp
    		WHERE	deptno = v_deptno AND job = v_job;
		BEGIN
 		 v_deptno:=10;
 		 v_job:='CLERK';
 		 OPEN emp_cursor;
  		LOOP
  		 FETCH emp_cursor INTO v_empno,v_ename;
		   EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
 		 END LOOP;
		END;


执行结果为:

Sql代码
  1. 7934,MILLER  
  2.         PL/SQL 过程已成功完成。  
7934,MILLER
		PL/SQL 过程已成功完成。


说明:该程序与前一程序实现相同的功能。
动态SELECT语句和动态游标的用法
Oracle支持动态SELECT语句和动态游标,动态的方法大大扩展了程序设计的能力。
对于查询结果为一行的SELECT语句,可以用动态生成查询语句字符串的方法,在程序执行阶段临时地生成并执行,语法是:
execute immediate 查询语句字符串 into 变量1[,变量2...];
以下是一个动态生成SELECT语句的例子。
【训练1】  动态SELECT查询。

Sql代码
  1. SET  SERVEROUTPUT  ON    
  2.         DECLARE    
  3.         str varchar2(100);  
  4.         v_ename varchar2(10);  
  5.         begin   
  6.         str:='select ename from scott.emp where empno=7788' ;  
  7.         execute  immediate str  into  v_ename;   
  8.         dbms_output.put_line(v_ename);  
  9.         END ;   
SET SERVEROUTPUT ON 
		DECLARE 
		str varchar2(100);
		v_ename varchar2(10);
		begin
		str:='select ename from scott.emp where empno=7788';
		execute immediate str into v_ename; 
		dbms_output.put_line(v_ename);
		END; 


执行结果为:

Sql代码
  1. SCOTT  
  2.         PL/SQL 过程已成功完成。  
SCOTT
		PL/SQL 过程已成功完成。


说明:SELECT...INTO...语句存放在STR字符串中,通过EXECUTE语句执行。
在变量声明部分定义的游标是静态的,不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据,但还是有很大的局限性。通过采用动态游标,可以在 程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型,然后声明一个游标变量,游标对应的查询语句可以在程序的执行过程中动 态地说明。
定义游标类型 的语句如下:
TYPE 游标类型名 REF CURSOR;
声明游标变量的语句如下:
游标变量名 游标类型名;
在可执行部分可以如下形式打开一个动态游标:
OPEN 游标变量名 FOR 查询语句字符串;
【训练2】  按名字中包含的字母顺序分组显示雇员信息。
输入并运行以下程序:

Sql代码
  1. declare    
  2.  type cur_type is  ref  cursor ;  
  3.  cur cur_type;  
  4.  rec scott.emp%rowtype;  
  5.  str varchar2(50);  
  6.  letter char :=  'A' ;  
  7. begin   
  8.         loop          
  9.          str:= 'select ename from emp where ename like ' '%' ||letter|| '%' '' ;  
  10.          open  cur  for  str;  
  11.          dbms_output.put_line('包含字母' ||letter|| '的名字:' );  
  12.           loop  
  13.          fetch  cur  into  rec.ename;  
  14.          exit when  cur%notfound;  
  15.         dbms_output.put_line(rec.ename);  
  16. end  loop;  
  17.   exit when  letter= 'Z' ;  
  18.   letter:=chr(ascii(letter)+1);  
  19.  end  loop;  
  20. end ;  
declare 
 type cur_type is ref cursor;
 cur cur_type;
 rec scott.emp%rowtype;
 str varchar2(50);
 letter char:= 'A';
begin
 		loop  		
 		 str:= 'select ename from emp where ename like ''%'||letter||'%''';
 		 open cur for str;
 		 dbms_output.put_line('包含字母'||letter||'的名字:');
		  loop
  		 fetch cur into rec.ename;
  		 exit when cur%notfound;
   		dbms_output.put_line(rec.ename);
end loop;
  exit when letter='Z';
  letter:=chr(ascii(letter)+1);
 end loop;
end;


运行结果为:

Sql代码
  1. 包含字母A的名字:  
  2. ALLEN  
  3. WARD  
  4. MARTIN  
  5. BLAKE  
  6. CLARK  
  7. ADAMS  
  8. JAMES  
  9. 包含字母B的名字:  
  10. BLAKE  
  11. 包含字母C的名字:  
  12. CLARK  
  13. SCOTT  
包含字母A的名字:
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
包含字母B的名字:
BLAKE
包含字母C的名字:
CLARK
SCOTT


说明:使用了二重循环,在外循环体中,动态生成游标的SELECT语句,然后打开。通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。

异常处理
错误处理
错误处理部分位于程序的可执行部分之后,是由WHEN语句引导的多个分支构成的。错误处理的语法如下:
EXCEPTION
WHEN 错误1[OR 错误2] THEN
语句序列1;
WHEN 错误3[OR 错误4] THEN
语句序列2;
WHEN OTHERS
语句序列n;
END;
其中:
错误是在标准包中由系统预定义的标准错误,或是由用户在程序的说明部分自定义的错误,参见下一节系统预定义的错误类型。
语句序列就是不同分支的错误处理部分。
凡是出现在WHEN后面的错误都是可以捕捉到的错误,其他未被捕捉到的错误,将在WHEN OTHERS部分进行统一处理,OTHENS必须是EXCEPTION部分的最后一个错误处理分支。如要在该分支中进一步判断错误种类,可以通过使用预定 义函数SQLCODE( )和SQLERRM( )来获得系统错误号和错误信息。
如果在程序的子块中发生了错误,但子块没有错误处理部分,则错误会传递到主程序中。
下面是由于查询编号错误而引起系统预定义异常的例子。
【训练1】  查询编号为1234的雇员名字。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2. DECLARE   
  3. v_name VARCHAR2(10);  
  4. BEGIN   
  5.    SELECT    ename  
  6.    INTO      v_name  
  7.    FROM      emp  
  8.    WHERE     empno = 1234;  
  9. DBMS_OUTPUT.PUT_LINE('该雇员名字为:' || v_name);  
  10. EXCEPTION  
  11.   WHEN  NO_DATA_FOUND  THEN   
  12.     DBMS_OUTPUT.PUT_LINE('编号错误,没有找到相应雇员!' );  
  13.   WHEN  OTHERS  THEN   
  14.     DBMS_OUTPUT.PUT_LINE('发生其他错误!' );  
  15. END ;  
SET SERVEROUTPUT ON
DECLARE
v_name VARCHAR2(10);
BEGIN
   SELECT	ename
   INTO		v_name
   FROM		emp
   WHERE	empno = 1234;
DBMS_OUTPUT.PUT_LINE('该雇员名字为:'|| v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('编号错误,没有找到相应雇员!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生其他错误!');
END;


执行结果为:

Sql代码
  1. 编号错误,没有找到相应雇员!  
  2.         PL/SQL 过程已成功完成。  
编号错误,没有找到相应雇员!
		PL/SQL 过程已成功完成。


说明:在以上查询中,因为编号为1234的雇员不存在,所以将发生类型为“NO_DATA_
FOUND”的异常。“NO_DATA_FOUND”是系统预定义的错误类型,EXCEPTION部分下的WHEN语句将捕捉到该异常,并执行相应代码部 分。在本例中,输出用户自定义的错误信息“编号错误,没有找到相应雇员!”。如果发生其他类型的错误,将执行OTHERS条件下的代码部分,显示“发生其 他错误!”。
【训练2】  由程序代码显示系统错误。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2. DECLARE   
  3. v_temp NUMBER(5):=1;  
  4. BEGIN   
  5. v_temp:=v_temp/0;  
  6. EXCEPTION  
  7.   WHEN  OTHERS  THEN   
  8. DBMS_OUTPUT.PUT_LINE('发生系统错误!' );  
  9.     DBMS_OUTPUT.PUT_LINE('错误代码:' || SQLCODE( ));  
  10.     DBMS_OUTPUT.PUT_LINE('错误信息:'  ||SQLERRM( ));  
  11.         END ;  
SET SERVEROUTPUT ON
DECLARE
v_temp NUMBER(5):=1;
BEGIN
v_temp:=v_temp/0;
EXCEPTION
  WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生系统错误!');
    DBMS_OUTPUT.PUT_LINE('错误代码:'|| SQLCODE( ));
    DBMS_OUTPUT.PUT_LINE('错误信息:' ||SQLERRM( ));
		END;


执行结果为:

Sql代码
  1. 发生系统错误!  
  2.         错误代码:?1476  
  3.         错误信息:ORA-01476: 除数为 0  
  4.         PL/SQL 过程已成功完成。  
发生系统错误!
		错误代码:?1476
		错误信息:ORA-01476: 除数为 0
		PL/SQL 过程已成功完成。


说明:程序运行中发生除零错误,由WHEN OTHERS捕捉到,执行用户自己的输出语句显示错误信息,然后正常结束。在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。
预定义错误
Oracle的系统错误很多,但只有一部分常见错误在标准包中予以定义。定义的错误可以在EXCEPTION部分通过标准的错误名来进行判断,并进行异常处理。常见的系统预定义异常如下所示。

Sql代码
  1. 错 误 名 称 错误代码    错 误 含 义  
  2. CURSOR_ALREADY_OPEN ORA_06511   试图打开已经打开的游标  
  3. INVALID_CURSOR  ORA_01001   试图使用没有打开的游标  
  4. DUP_VAL_ON_INDEX    ORA_00001   保存重复值到惟一索引约束的列中  
  5. ZERO_DIVIDE ORA_01476   发生除数为零的除法错误  
  6. INVALID_NUMBER  ORA_01722   试图对无效字符进行数值转换  
  7. ROWTYPE_MISMATCH    ORA_06504   主变量和游标的类型不兼容  
  8. VALUE_ERROR ORA_06502   转换、截断或算术运算发生错误  
  9. TOO_MANY_ROWS   ORA_01422   SELECT INTO …语句返回多于一行的数据  
  10. NO_DATA_FOUND   ORA_01403   SELECT INTO …语句没有数据返回  
  11. TIMEOUT_ON_RESOURCE ORA_00051   等待资源时发生超时错误  
  12. TRANSACTION_BACKED_OUT  ORA_00060   由于死锁,提交失败  
  13. STORAGE_ERROR   ORA_06500   发生内存错误  
  14. PROGRAM_ERROR   ORA_06501   发生PL/SQL内部错误  
  15. NOT_LOGGED_ON   ORA_01012   试图操作未连接的数据库  
  16. LOGIN_DENIED    ORA_01017   在连接时提供了无效用户名或口令  
错 误 名 称	错误代码	错 误 含 义
CURSOR_ALREADY_OPEN	ORA_06511	试图打开已经打开的游标
INVALID_CURSOR	ORA_01001	试图使用没有打开的游标
DUP_VAL_ON_INDEX	ORA_00001	保存重复值到惟一索引约束的列中
ZERO_DIVIDE	ORA_01476	发生除数为零的除法错误
INVALID_NUMBER	ORA_01722	试图对无效字符进行数值转换
ROWTYPE_MISMATCH	ORA_06504	主变量和游标的类型不兼容
VALUE_ERROR	ORA_06502	转换、截断或算术运算发生错误
TOO_MANY_ROWS	ORA_01422	SELECT…INTO…语句返回多于一行的数据
NO_DATA_FOUND	ORA_01403	SELECT…INTO…语句没有数据返回
TIMEOUT_ON_RESOURCE	ORA_00051	等待资源时发生超时错误
TRANSACTION_BACKED_OUT	ORA_00060	由于死锁,提交失败
STORAGE_ERROR	ORA_06500	发生内存错误
PROGRAM_ERROR	ORA_06501	发生PL/SQL内部错误
NOT_LOGGED_ON	ORA_01012	试图操作未连接的数据库
LOGIN_DENIED	ORA_01017	在连接时提供了无效用户名或口令


比如,如果程序向表的主键列插入重复值,则将发生DUP_VAL_ON_INDEX错误。
如果一个系统错误没有在标准包中定义,则需要在说明部分定义,语法如下:
错误名 EXCEPTION;
定义后使用PRAGMA EXCEPTION_INIT来将一个定义的错误同一个特别的Oracle错误代码相关联,就可以同系统预定义的错误一样使用了。语法如下:
PRAGMA EXCEPTION_INIT(错误名,- 错误代码);
【训练1】  定义新的系统错误类型。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2.         DECLARE   
  3.         V_ENAME VARCHAR2(10);  
  4.         NULL_INSERT_ERROR EXCEPTION;  
  5.         PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);  
  6.         BEGIN   
  7.         INSERT   INTO  EMP(EMPNO)  VALUES ( NULL );  
  8. EXCEPTION  
  9. WHEN  NULL_INSERT_ERROR  THEN   
  10.     DBMS_OUTPUT.PUT_LINE('无法插入NULL值!' );  
  11.   WHEN  OTHERS   THEN   
  12.     DBMS_OUTPUT.PUT_LINE('发生其他系统错误!' );  
  13. END ;  
SET SERVEROUTPUT ON
		DECLARE
		V_ENAME VARCHAR2(10);
		NULL_INSERT_ERROR EXCEPTION;
		PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);
		BEGIN
		INSERT INTO EMP(EMPNO) VALUES(NULL);
EXCEPTION
WHEN NULL_INSERT_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('无法插入NULL值!');
  WHEN OTHERS  THEN
    DBMS_OUTPUT.PUT_LINE('发生其他系统错误!');
END;


执行结果为:

Sql代码
  1. 无法插入 NULL 值!  
  2.         PL/SQL 过程已成功完成。  
无法插入NULL值!
		PL/SQL 过程已成功完成。


  说明:NULL_INSERT_ERROR是自定义异常,同系统错误1400相关联。
自定义异常
程序设计者可以利用引发异常的机制来进行程序设计,自己定义异常类型。可以在声明部分定义新的异常类型,定义的语法是:
错误名 EXCEPTION;
用户定义的错误不能由系统来触发,必须由程序显式地触发,触发的语法是:
RAISE 错误名;
RAISE也可以用来引发模拟系统错误,比如,RAISE ZERO_DIVIDE将引发模拟的除零错误。
使用RAISE_APPLICATION_ERROR函数也可以引发异常。该函数要传递两个参数,第一个是用户自定义的错误编号,第二个参数是用户自定义的错误信息。使用该函数引发的异常的编号应该在20 000和20 999之间选择。
自定义异常处理错误的方式同前。
【训练1】  插入新雇员,限定插入雇员的编号在7000~8000之间。

Java代码
  1. SET SERVEROUTPUT ON  
  2. DECLARE  
  3. new_no NUMBER(10 );  
  4. new_excp1 EXCEPTION;  
  5. new_excp2 EXCEPTION;  
  6. BEGIN  
  7. new_no:=6789 ;  
  8. INSERT INTO emp(empno,ename)  
  9.   VALUES(new_no, '小郑' );  
  10.   IF new_no<7000  THEN  
  11.     RAISE new_excp1;  
  12.   END IF;  
  13.   IF new_no>8000  THEN  
  14.     RAISE new_excp2;  
  15.   END IF;  
  16.   COMMIT;  
  17. EXCEPTION  
  18. WHEN new_excp1  THEN  
  19.     ROLLBACK;  
  20.     DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限!' );  
  21.     WHEN new_excp2  THEN  
  22.     ROLLBACK;  
  23.     DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限!' );  
  24.     END;  
SET SERVEROUTPUT ON
DECLARE
new_no NUMBER(10);
new_excp1 EXCEPTION;
new_excp2 EXCEPTION;
BEGIN
new_no:=6789;
INSERT INTO	emp(empno,ename)
  VALUES(new_no, '小郑');
  IF new_no<7000 THEN
    RAISE new_excp1;
  END IF;
  IF new_no>8000 THEN
    RAISE new_excp2;
  END IF;
  COMMIT;
EXCEPTION
WHEN new_excp1  THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限!');
  	WHEN new_excp2  THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限!');
	END;


执行结果为:
雇员编号小于7000的下限!
PL/SQL 过程已成功完成。
说明:在此例中,自定义了两个异常:new_excp1和new_excp2,分别代表编号小于7000和编号大于8000的错误。在程序中通过判断编号大小,产生对应的异常,并在异常处理部分回退插入操作,然后显示相应的错误信息。
【训练2】  使用RAISE_APPLICATION_ERROR函数引发系统异常。

Sql代码
  1. SET  SERVEROUTPUT  ON   
  2. DECLARE   
  3. New_no NUMBER(10);  
  4. BEGIN   
  5.   New_no:=6789;  
  6.  INSERT   INTO     emp(empno,ename)  
  7.   VALUES (new_no,  'JAMES' );  
  8. IF new_no<7000 THEN   
  9.     ROLLBACK ;  
  10.     RAISE_APPLICATION_ERROR(-20001, '编号小于7000的下限!' );  
  11.   END  IF;  
  12.   IF new_no>8000 THEN   
  13.     ROLLBACK ;  
  14.     RAISE_APPLICATION_ERROR (-20002, '编号大于8000的下限!' );  
  15.   END  IF;  
  16. END ;  
SET SERVEROUTPUT ON
DECLARE
New_no NUMBER(10);
BEGIN
  New_no:=6789;
 INSERT INTO	emp(empno,ename)
  VALUES(new_no, 'JAMES');
IF new_no<7000 THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, '编号小于7000的下限!');
  END IF;
  IF new_no>8000 THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR (-20002, '编号大于8000的下限!');
  END IF;
END;


执行结果为:

Sql代码
  1. DECLARE   
  2.         *  
  3.         ERROR 位于第 1 行:  
  4.         ORA-20001: 编号小于7000的下限!  
  5.         ORA-06512: 在line 9  
DECLARE
		*
		ERROR 位于第 1 行:
		ORA-20001: 编号小于7000的下限!
		ORA-06512: 在line 9


  说明:在本训练中,使用RAISE_APPLICATION_ERROR引发自定义异常,并以系统错误的方式进行显示。错误编号为20001和20002。
注意:同上一个训练比较,此种方法不需要事先定义异常,可直接引发。
可以参考下面的程序片断将出错信息记录到表中,其中,errors为记录错误信息的表,SQLCODE为发生异常的错误编号,SQLERRM为发生异常的错误信息。
DECLARE
  v_error_code      NUMBER;
  v_error_message   VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
    v_error_code := SQLCODE ;
    v_error_message := SQLERRM ;
    INSERT INTO errors
    VALUES(v_error_code, v_error_message);
END;
  【练习1】修改雇员的工资,通过引发异常控制修改范围在600~6000之间。
阶段训练
【训练1】  将雇员从一个表复制到另一个表。
步骤1:创建一个结构同EMP表一样的新表EMP1:
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;
步骤2:通过指定雇员编号,将雇员由EMP表移动到EMP1表:

Sql代码
  1. SET  SERVEROUTPUT  ON    
  2. DECLARE   
  3. v_empno NUMBER(5):=7788;  
  4. emp_rec emp%ROWTYPE;  
  5. BEGIN   
  6.  SELECT  *  INTO  emp_rec  FROM  emp  WHERE  empno=v_empno;  
  7.  DELETE   FROM  emp  WHERE  empno=v_empno;  
  8. INSERT   INTO  emp1  VALUES  emp_rec;  
  9.  IF SQL%FOUND THEN   
  10.   COMMIT ;  
  11.   DBMS_OUTPUT.PUT_LINE('雇员复制成功!' );  
  12.  ELSE    
  13.   ROLLBACK ;  
  14.   DBMS_OUTPUT.PUT_LINE('雇员复制失败!' );  
  15.  END  IF;  
  16. END ;  
SET SERVEROUTPUT ON 
DECLARE
v_empno NUMBER(5):=7788;
emp_rec emp%ROWTYPE;
BEGIN
 SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;
 DELETE FROM emp WHERE empno=v_empno;
INSERT INTO emp1 VALUES emp_rec;
 IF SQL%FOUND THEN
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('雇员复制成功!');
 ELSE 
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE('雇员复制失败!');
 END IF;
END;


执行结果为:
雇员复制成功!
PL/SQL 过程已成功完成。
步骤2:显示复制结果:
SELECT empno,ename,job FROM emp1;
执行结果为:

 
Sql代码
  1. EMPNO ENAME      JOB  
  2. ------------- -------------- ----------------   
  3.     7788  SCOTT      ANALYST  
 EMPNO ENAME      JOB
-------------- -------------- ----------------
     7788  SCOTT      ANALYST


说明:emp_rec变量是根据emp表定义的记录变量,SELECT...INTO...语句将整个记录传给该变量。INSERT语句将整个记 录变量插入emp1表,如果插入成功(SQL%FOUND为真),则提交事务,否则回滚撤销事务。试修改雇员编号为7902,重新执行以上程序。
【训练2】  输出雇员工资,雇员工资用不同高度的*表示。
输入并执行以下程序:

Sql代码
  1. SET  SERVEROUTPUT  ON    
  2. BEGIN   
  3.  FOR  re  IN  ( SELECT  ename,sal  FROM  EMP)  LOOP  
  4.   DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ' )||rpad( '*'</spa
    分享到:
    评论

相关推荐

    oracle 隐式游标,显示游标,游标循环

    游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库...在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。

    详解Oracle隐式游标和显式游标

    1.Oracle隐式游标 1.1Oracle有常用的哪些隐式游标 1.2 Oracle隐式游标演示 -- 隐式游标 (使用的表为Oracle默认自带的emp表) -- sql%rowcount:影响记录条数 sql%found:是否有满足条件的记录 set serveroutput on...

    Oracle显式游标和隐式游标.doc

    Oracle显式游标和隐式游标.doc

    Oracle之隐式游标的使用方法.pdf

    Oracle之隐式游标的使用方法.pdf 学习资料 复习资料 教学资源

    Oracle面试题

    1. oracle中的游标分为显示游标和隐式游标 2. 显示游标是用cursor...is命令定义的游标,它可以对查询语句(select)返回的多条记录进行处理;隐式游标是在执行插入 (insert)、删除(delete)、修改(update)和返回单条...

    oracle游标练习答案

    关于游标使用问题的练习和答案。游标分隐式和显示游标;动态和ref游标。适用于初学者。

    快速练习ORACLE游标习题及答案

    关于游标使用问题的练习附答案。游标分隐式和显示游标;动态和ref游标。。。

    oracle游标学习资料

    主要描述oracle中游标的学习隐式游标和显示游标

    Oracle游标使用方法及语法大全

    让你了解游标的语法与如何使用游标. ...PL/SQL管理隐式游标,当查询开始时隐式游标打开,查询结束时隐式游标自动关闭。显式游标在PL/SQL块的声明部分声明,在执行部分或异常处理部分打开,取出数据,关闭

    Oracle 游标使用总结

    Oracle游标分为显示游标和隐式游标。 显示游标(Explicit Cursor):在PL/SQL程序中定义的、用于查询的游标称作显示游标。 隐式游标(Implicit Cursor):是指非PL/SQL程序中定义的、而且是在PL/SQL中使用UPDATE/...

    Oracle显示游标的使用及游标for循环

    本篇文章给大家介绍oracle显示游标的使用及游标for循环,当查询返回单行记录时使用隐式游标,查询返回多行记录并逐行进行处理时使用显式游标,对本文感兴趣的朋友一起学习吧

    oracle游标管理

    oracle游标管理:显示游标,隐式游标,REF游标

    ORACLE 游标学习笔记

    在PL/SQL中为所有SQL数据操纵语句(包括返回一行的select)隐式声明游标,称为隐式游标的原因是用户不能直接命名和控制此类游标.当用户在PL/SQL中使用数据操纵语言(DML)时,Oracle预先定义一个名为SQL的隐式游标

    第7章 游标和异常处理.ppt

    7.1 游标的概念 7.2 隐式游标 7.3 显式游标 7.4 异常处理

    oracle游标

    有关显示游标,隐式游标,动态游标的,如果没基础的话,可以参考这个看看哦

    oracle游标实例

    关于oracle数据库隐式游标、显式游标相关用法的详细实例,还不错

    MySql游标的使用实例

    1.创建游标 代码如下:DECLARE calc_bonus CURSOR FOR SELECT id, salary, commission FROM employees; 2.打开游标 代码如下:OPEN calc_bonus; 3.使用游标 代码如下:FETCH calc_bonus INTO re_id, re_salary, re_comm...

    mysql函数、存储过程、触发器、游标.doc

    mysql函数、存储过程、触发器、游标

Global site tag (gtag.js) - Google Analytics