ch03 oracle异常异常与动态sql

--------------------------------------oracle异常------------------------
预定义异常:数据库给定的异常
declare v_job varchar2(200);
begin
    select job  into v_job from emp;
exception
    when too_many_rows then raise_application_error(-20001,'行太多');
    when no_data_found then raise_application_error(-20001,'没有数据');
    when others then raise_application_error(-20001,'其他错误');
end;

自定义异常
declare myException Exception;
begin
    raise myException;
exception
    when myException then raise_application_error(-20001,'我的消息');
    --when myException then dbms_output.put_line('我的消息');
end;

 

--------------------------------------oracle动态sql------------------------
动态sql
格式为：execute immediate sql [into 变量] [using 参数值]
declare
    v_sql varchar2(200);
    v_emp emp%rowtype;
    v_job emp.job%type;
begin
    v_sql:='select * from emp where job =:1 and rownum=1';
    v_job := '&CLERK';
execute immediate v_sql into v_emp using v_job;
dbms_output.put_line(v_emp.empno);
exception
    when too_many_rows then raise_application_error(-20001,'行太多');
    when no_data_found then raise_application_error(-20001,'没有数据');
    when others then raise_application_error(-20001,'其他错误');
end;

 

--------------------------------------oracle游标管理------------------------ 
作用:一批行，可以一行一行的处理

属性:%rowcount,%found,%notfound,%isopen

类型:隐式,显式,REF

例:不带参数
declare
    cursor v_cursor return emp%rowtype is select * from emp where rownum<10;
    v_emp emp%rowtype;
begin
    open v_cursor;
    fetch v_cursor into v_emp;
    while(v_cursor%found) loop
         fetch v_cursor into v_emp;
         dbms_output.put_line(v_emp.empno);
    end loop;
    close v_cursor;
end;

动态(指针)：先定义类型，再定义变量
--普通的使用方式
declare
  type c_type is ref cursor return emp%rowtype;
  c1 c_type;
begin
  open c1 for select * from emp where rownum=10;
end;

--动态的指针游标
declare
    type c_type is ref cursor;
    aa c_type;
    v_sql varchar2(2000):='select * from emp where job =:1 and rownum=10';
    v_job emp.job%type;
begin
    v_job:='&job';
    open aa for v_sql using v_job;
end;

 

--------------------------------------oracle优化基础------------------------ 
SQL> explan plan for select * from dept;

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   105 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     7 |   105 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

已选择8行。

判断当前数据库使用何种优化器：

  主要是由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows | all_rows | choose | rule。
具体解释如下：
  RULE 为使用 RBO 优化器。
  CHOOSE 则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引
用的对象已经被分析，则就使用 CBO 优化器，否则为 RBO 优化器。

  ALL_ROWS为 CBO 优化器使用的第一种具体的优化方法，是以数据的吞吐量为
主要目标，以便可以使用最少的资源完成语句。

  FIRST_ROWS 为优化器使用的第二种具体的优化方法，是以数据的响应时间为
主要目标，以便快速查询出开始的几行数据。
  FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法，让
优化器选择一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果
的前 n 行。该参数为 ORACLE 9I 新引入的。

  从 ORACLE V7以来，optimizer_mode 参数的缺省设置应是"choose"，即如果对已分
析的表查询的话选择 CBO，否则选择 RBO。在此种设置中，如果采用了 CBO，则缺省为
CBO 中的 all_rows 模式。

  注意：即使指定数据库使用 RBO 优化器，但有时 ORACLE 数据库还是会采用 CBO
优化器，这并不是 ORACLE 的 BUG，主要是由于从 ORACLE 8I 后引入的许多新特性都必
须在 CBO 下才能使用，而你的 SQL 语句可能正好使用了这些新特性，此时数据库会自动
转为使用 CBO 优化器执行这些语句。