异常和游标管理

 

游标：

用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。

分类：

静态游标：

分为显式游标和隐式游标。

REF游标：

       是一种引用类型，类似于指针。

显式游标：

       CURSOR 游标名 ( 参数 ) [返回值类型] IS

              Select 语句

生命周期：

1．打开游标（OPEN）：

解析，绑定。。。不会从数据库检索数据

2．从游标中获取记录(FETCH INTO)：

执行查询，返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。

3．关闭游标(CLOSE)

完成游标处理，用户不能从游标中获取行。还可以重新打开。

选项：参数和返回类型

set serveroutput on

declare

       cursor emp_cur ( p_deptid in number) is

select * from employees where department_id = p_deptid;

l_emp employees%rowtype;

begin

       dbms_output.put_line(‘Getting employees from department 30’);

open emp_cur(30);

       loop

              fetch emp_cur into l_emp;

              exit when emp_cur%notfound;

              dbms_output.put_line(‘Employee id ‘|| l_emp.employee_id || ‘ is ‘);

              dbms_output.put_line(l_emp.first_name || ‘ ‘ || l_emp.last_name);

       end loop;

       close emp_cur;

       dbms_output.put_line(‘Getting employees from department 90’);

open emp_cur(90);

       loop

              fetch emp_cur into l_emp;

              exit when emp_cur%notfound;

              dbms_output.put_line(‘Employee id ‘|| l_emp.employee_id || ‘ is ‘);

              dbms_output.put_line(l_emp.first_name || ‘ ‘ || l_emp.last_name);

       end loop;

       close emp_cur;

end;

/

隐式游标：

不用明确建立游标变量，分两种：

1．在PL/SQL中使用DML语言，使用ORACLE提供的名为SQL的隐示游标

2．CURSOR FOR LOOP，用于for loop 语句。

1举例：

declare

begin

       update departments set department_name=department_name;

       --where 1=2;

      

       dbms_output.put_line(‘update ‘|| sql%rowcount ||’ records’);

end;

/

2举例：

declare

begin

       for my_dept_rec in ( select department_name, department_id from departments)

       loop

              dbms_output.put_line(my_dept_rec.department_id || ‘ : ’ || my_dept_rec.department_name);

       end loop;

end;

/

游标属性：

%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT：当前时刻已经从游标中获取的记录数量。

%ISOPEN：是否打开。

Declare

       Cursor emps is

       Select * from employees where rownum<6 order by 1;

      

       Emp employees%rowtype;

       Row number :=1;

Begin

       Open emps;

       Fetch emps into emp;

      

       Loop

              If emps%found then

                     Dbms_output.put_line(‘Looping over record ‘||row|| ‘ of ‘ || emps%rowcount);

                     Fetch emps into emp;

                     Row := row + 1;

              Elsif emps%notfound then

                     Exit; ---exit loop, not IF

              End if;

       End loop;

      

       If emps%isopen then

              Close emps;

       End if;

End;

/

显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

REF CURSOR游标:

动态游标，在运行的时候才能确定游标使用的查询。分类：

强类型（限制）REF CURSOR，规定返回类型

弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。

TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]

Declare

       Type refcur_t is ref cursor;

      

       Type emp_refcur_t is ref cursor return employee%rowtype;

Begin

       Null;

End;

/

强类型举例：

declare

       --声明记录类型

       type emp_job_rec is record(

              employee_id number,

              employee_name varchar2(50),

              job_title varchar2(30)

       );

       --声明REF CURSOR，返回值为该记录类型

       type emp_job_refcur_type is ref cursor

              return emp_job_rec;

       --定义REF CURSOR游标的变量

       emp_refcur emp_job_refcur_type;

       emp_job emp_job_rec;

begin

       open emp_refcur for

              select e.employee_id,

                            e.first_name || ‘ ’ ||e.last_name “employee_name”,

                            j.job_title

              from employees e, jobs j

              where e.job_id = j.job_id and rownum < 11 order by 1;

       fetch emp_refcur into emp_job;

       while emp_refcur%found loop

              dbms_output.put_line(emp_job.employee_name || ‘’’s job is ’);

              dbms_output.put_line(emp_job.job_title);

              fetch emp_refcur into emp_job;

       end loop;

end;

/

单独select

declare

       l_empno emp.EMPLOYEE_ID%type;

--     l_ename emp.ename%type;

begin

       select EMPLOYEE_ID   

              into l_empno

       from emp;

       --where rownum =1;

       dbms_output.put_line(l_empno);

end;

/

使用INTO获取值，只能返回一行。

错误处理：

exception

       when <exception_expression> then

              …

       when <exception_expression> then

              …

end;

exception_expression包括：

1.      预定义表达式

2.      用户定义表达式

3.      PRAGMA EXCEPTION_INIT

预定义

declare

       l_dept departments%rowtype;

begin

       l_dept.department_id:=100;

       l_dept.department_name:=’HR’;

       insert into departments(department_id, department_name)

              values(l_dept.department_id, l_dept.department_name);

Exception

       When DUP_VAL_ON_INDEX then

              Dbms_output.put_line(‘heihei’);

end;

/

DUP_VAL_ON_INDEX 异常

Exception

       When DUP_VAL_ON_INDEX then

              Dbms_output.put_line…..

自定义：

declare

l_exc exception;

begin

       …

       raise l_exc;

exception

       when l_exc then

              …

end;

/

PRAGMA EXCEPTION_INIT

 

Declare

       L_update_text varchar2(100):=

              ‘update &table_name set &updated_column_name= ‘’:a’’

                     where &key_column_name=:a’;

begin

       execute immediate L_update_text using ‘&updated_column_value’, &key_column_value;

end;

/

Declare

       Invalid_column_name exception;

       Pragma exception_init(Invalid_column_name,-904);

       L_update_text varchar2(100):=

              ‘update &table_name set &updated_column_name= ‘’:a’’

                     where &key_column_name=:a’;

begin

       execute immediate L_update_text using ‘&updated_column_value’, &key_column_value;

exception

              when Invalid_column_name then

                     dbms_output.put_line(‘hehe’);

end;

/

异常传播：

begin

       begin

              begin

                     begin

                            begin

                                   declare

                                          fname employees.first_name%type;

                                   begin

                                          select first_name into fname from employees where 1=2;

                 &nb