pl初学者总结（下）

五、动态sql和异常

1.动态 SQL

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行

DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行

执行动态 SQL 的语法：

   EXECUTE IMMEDIATE 字符串类型的命令

   [INTO  define_variable_list]

   [USING bind_argument_list];

execute immediate 字符串参数 [into] 变量 using 参数

immediate后面跟的参数：它是一个字符串类型的sql语句，或者字符串类型的plsql代码

into 变量:是把sql语句的执行结果保存到变量中

using 参数：传给sql语句的参数

如：

--按照员工编号查询员工的信息

declare

  --声名一个变量保存员工信息

  v_emp emp%rowtype;

  --声名一个字符串类型变量

  v_sql varchar2(255);

begin

  execute immediate 'select * from emp where empno=7369' into v_emp;

  dbms_output.put_line(v_emp.ename||','||v_emp.job);

  v_sql:='select * from emp where empno=7369';

  execute immediate v_sql into v_emp;

  dbms_output.put_line(v_emp.ename||','||v_emp.job);

  v_sql:='select * from emp where empno=:a and deptno=:b';

  execute immediate v_sql into v_emp using 7369,20;

  dbms_output.put_line(v_emp.ename||','||v_emp.job);

end;

declare

  --声名一个变量保存员工信息

  v_emp emp%rowtype;

  --声名一个字符串类型变量

  v_sql varchar2(255);

  --

  n number(10):=1000;

begin

 -- execute immediate 'update emp set sal=:1 where empno=:2' using n,7369 ;

  --execute immediate 'delete from emp where empno=:1' using 7369;

  execute immediate 'create table t(id number(11),name varchar2(20))';

end;

select * from emp where empno=7369;

select * from t;

2.异常

1.异常概述

在运行程序时出现的错误叫做异常

发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

declare

   声明部分;

begin

   plsql代码块;

   exception

      异常处理部分;

end;

ORACLE异常分为两种类型：系统异常、自定义异常。其中系统异常又分为：预定义异常和非预定义异常。

2.预定义异常

ORACLE定义了它们的错误编号和异常名字，常见的预定义异常处理如下:

ACCESS_INTO_NULL:对应ORA-06530为了引用对象属性，必须首先初始化对象。直接引用未初始化的对象属性时，会发生异常

CASE_NOT_FOUND:对应ORA-06592，当CASE语句的WHEN子句没有包含必须条件分支或者ELSE子句时，会触发

COLLECTION_IS_NULL:对应ORA-06531,在给嵌套表变量或者varrary变量赋值之前，必须首先初始化集合

CURSOR_ALREADY_OPEN:ORA-06511，当已打开游标上执行OPEN操作时会触发

INVALID_CURSOR:ORA-01001,当视图从未打开游标，提取数据，或者关闭未打开游标时会触发

INVALID_NUMBER:ORA-01722,当内嵌SQL语句不能将字符转变成数字时会触发

LOGIN_DENIED:ORA-01017,连接Oracle数据库时，如果提供了不正解的用户名和口令时会触发

NO_DATA_FOUND:ORA-01403执行SELECT INTO 未返回行或者引用了未初始化的PL/SQL表元素时会触发

NOT_LOGGED_ON:ORA-01012没有连接数据库执行SQL时会触发

PROGRAM_ERROR:ORA-06501存在PL/SQL内部问题，在这种情况下需要重新安装数据字典视图和PL/SQL包

ROWTYPE_MISMATCH:ORA-016504当执行赋值操作时，如果宿主变量和游标变量不兼容的返回类型时，会触发

SELF_IS_NULL: ORA-30625,当使用对象类型时，如果在null实例上调用成员方法时，会触发

STORAGE_ERROR:ORA-06500当执行PL/SQL块时，如果超出内存空间或者内存被破坏，会触发

SUBSCRIPT_BEYOND_COUNT:ORA-06533当使用嵌套或者varray元素的范围进会触发

SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532，使用嵌套表或者varray元素时，如果元素下标为负值时，会触发

SYS-INVALID_ROWID:ORA-01410当字符串转变为ROWID时如果使用了无效字符串，会触发

TIMEOUT_ON_RESOURCE:ORA-00051当等待资源时如果出现超时会触发

TOO_MANY_ROWS:ORA-01422当执行SELECT INTO时，如果返回超过一行、会触发

VALUE_ERROR:ORA-06502,执行赋值时，如果变量长度不足，会触发

ZERO_DIVIDE：ORA-01476如果用数字值除0，会触发

如：

declare

   v emp.ename%type;

begin

   select ename into v from emp where 1=0;

end;

语法：

begin

  exception

    when 异常名称 then

       异常处理代码;

    when 异常名称 then

       异常处理代码;

    when 异常名称 then

       异常处理代码;

    when others then

       异常处理代码;  

end;

exception:出现在end前面，在exception后不能有除 异常处理代码 外的其它代码

others:others其他所有的异常

注：

SQLCODE与SQLERRM为异常处理函数。

函数SQLCODE用于取得Oracle错误号，

函数SQLERRM用于取得与错误号对应的相关错误消息

3.非预定义异常

ORACLE为它定义了错误编号，但没有定义异常名字。我们使用的时候，先声名一个异常名，通过伪过程PRAGMA EXCEPTION_INIT，将异常名与错误号关联起来。

如：

pragma exception_init(自定义的异常,编号);

insert into emp(empno,deptno) values(7369,10);

declare

   --声名异常变量

   myexc exception;

   --绑定异常

   pragma exception_init(myexc,-00001);

begin

   begin

      insert into emp(empno,deptno) values(7369,10);

    -- commit;

      exception

          when myexc then

     --    rollback;

            dbms_output.put_line('违反唯一约束');

   end;

   dbms_output.put_line('-----');  

end;

4.自定义异常

程序员从业务角度出发，制定的一些规则和限制。

异常名称 exception;

抛出异常：

raise 异常名称;

exception 捕获异常 处理异常

----------------------------------------

declare

   --定义一个自己的异常

   myexcp exception;

   --声名两个变量接收两个数

   m number(10):=&m;

   n number(10):=&n;

begin

   if n=0 then

      dbms_output.put_line('除数不能为零');

      raise myexcp;--抛出异常 需要处理异常

   end if;

   dbms_output.put_line(m/n);

end;

5.引发应用程序错误