从零开始学Oracle（六至八）

--以下内容转载于http://tech.ddvip.com

---从零开始学Oracle—性能对象(六)
Oracle性能对象

　　要点：分区必须是在创建表的结构时指定，分区方式一旦指定是不能改变的。

　　分区方式：

　　1 范围分区

　　2 散列分区

　　3复合分区

　　3列表分区

　　表分区的优点：

　　一、改善表的查询性能

　　二、表更容易管理，表的数据存储在多个部分，按分区加载和删除数据更快

　　三、便于备份和恢复

　　四、提高数据安全性

　　-----------注意：要分区的表不能具有Long和Long raw数据类型

　　范围分区：根据表的某个列或一组列的值范围，决定将数据存储在那个分区

create table niegc_part
　(
　part_id integer primary key,
　part_date date,
　part_dec varchar2(100)
　)
　partition by range(part_date)--根据日期分区
(
　 partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dw1,

　partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) tablespace dw2,

　 partition part_03 values less than(maxvalue) tablespace dw1

　);

　　散列分区

　　 散列分区通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区

　　，使行这些分区大小一致。如将part_id的数据根据自身的情况散列地存放在指定的二个表空间中：

create table niegc_part
(
part_id integer primary key,

part_date date,

part_dec varchar2(100)

)

partition by hash(part_id)

(

partition part_01　 tablespace dw1,

partition part_02　 tablespace dw2

);
--系统将按part_id将记录散列地插入二个分区中，这里也就是二个不同的表空间中。

　　复合分区：范围分区和散列分区的结合

　　--语法

　　 partition by range(列1）
　　 subpartition by hash(列2）
　　 subpartition 分区数目 [tore in (表空间1，表空间2）]
　　 （
　　　　　 partition　 part1 values less than (值1）,
　　　　 partition　 part2 values less than (maxvalue)
　　　 ）
--例：对emp表的复合分区,先按薪资分再按部门分
　　　 partition　 by range(sal)
　　 subpartition by hash(empno)
　　 (
　　　　 partition p1 values less than('1501')
　　　　　　 (
　　　　　　　　　 subpartition sp1 tablespace a1,
　　　　　　　　　 subpartition sp2 tablespace a2
　　　　　　 )
　　　　 partition p2 values less than('3001')
　　　　　　 (
　　　　　　　　　 subpartition sp3 tablespace a1,
　　　　　　　　　 subpartition sp4 tablespace a2
　　　　　　 )

　　 )

　　--查看表分区

　　　　　　　　　　　 select * from emp partition(p1)
　　　　　　　　　　　 select * from emp subpartition(sp1)

　　列表分区：允许用户明确地控制行到分区的映射

--语法
　　　 partition　 by list(列名）
　　 （
　　　　　　 partition part1 values(值1），
　　　　　　 partition part2 values（值2），
　　　　　　 partition part3 values（default)
　 --default关键字用于存储前面的分区不能存储的记录
　 )
 分区的维护操作

　　1.添加分区

 alter　 table Table add partition 分区名 values less than (值）
　　 如果边界是maxvalue 使用split partition语句
　　　 alter table Table split　 partition 分区名 at （值）

　　2.删除分区

　　　 alter table Table drop partition 分区名

　　3.截断分区 （只删除记录，不删除结构)

　　　 alter table Table truncate partition 分区名

　　4.合并分区　　

　　　 alter table Table merge partitions p1,p2 into partition p2

　　5.拆分分区

　　　 alter table Table split partition 分区1 at （值） into（partition 分区1，partition 分区2）

　　6.移动分区

　　　 alter table Table move partition 分区 tablespace（表空间名）

-----从零开始学Oracle—PL/SQL(七)
PL/SQL简介

　　 PL/SQL(Procedural Language Sql,过程语言|SQL)是结合了Oracle过程语言和结构化查询语言（SQL)的一种扩展语言.

　　 PL/SQL块是一段后台服务程序，它负责将数据从数据库中取出来，在PL/SQL块中进行处理，将处理的结果送到数据库.

　　优点:

　　 1,支持SQL

　　 2，支持面向对象编程(OOP)

　　 3,更好的性能

　　 4，可移置性

　　 5,与SQL集成 --支持所有SQL数据类型和Null值 ，另%type　 %rowType属性类型更加强了这种集成

　　 6,安全性

　　PL/SQL块包括3个部分:

　　1,声明部分　　　 --变量，游标，自定义异常, 局部子程序

　　2,可执行部分　　 --执行命令，可嵌套子块

　　3,异常处理部分

PL/SQL块
　 declare
　 begin
　　 --SQL语句
　　 --直接写的SQL语句(DML/TCL)
　　 --间接写execute immediate <DDL/DCL命令字符串>
　 --select 语句
　　　　　　　 <1>必须带有into子句
　　　　　　　　　　　　 select empno into eno from emp
　　　　　　　　　　 where empno =7369;
　　　　　　　 <2>只能查到一行**********
　　　　　　　 <3>字段个数必须和变量的个数一致
　 exception　 --异常
　　　 when <异常名字> then --特定异常
　　　　　　　 <处理语句>
　　　 when others then　 --所有异常都可捕获
　　　　　　　 <处理语句>　　
　 end;

语言特征：

　　<1>,大小写不敏感

　　<2>,复合符号的含义

　　 :=　　 赋值操作符　　 ||连接操作符　 ..范围操作符　 **求幂操作符 《，》标签分隔符　　　 --单行注释　 /*，*/多行注释

　　<3>声明变量　　 --变量名　 数据类型 :=值　 例： varA　 varchar2(10):='声明变量';

　　<4>声明常量　　 --变量名 constant 数据类型 := 值　　

　　---------------- 技巧：初始化变量和常量时，保留字default可替换：=赋值操作符--------------

　　<5>属性类型

　　 %type　　 --引用某个变量或数据库列的数据类型来声明变量
　　 %rowType　 --提供表示表中一行的记录类型

　　<6>控制结构

　　 a,条件控制　 if　 then　 或　　　　 if　 then　 else　　 或　　　 if　 then　 elsif　　 或　　　　 case
　　 b,循环控制 loop
　　　　　　　　　　　　　　　　　 while　　　　　　　　　　　　　 exit或exit when退出
　　　　　　　　　　　　　　　　　 for
　　 c,顺序控制　 Goto　　　　　　　　　　　　 Null语句

　　语法示例：

　 1--PL/SQL 简单语法

Begin
　 null;　
End;
Declare
　 varA number(4);
Begin
　 varA := 1234;
　 DBMS_output.put_line(varA);
End;
Declare
　 varA number(4);
Begin
　 varA := 1234;
　 DBMS_output.put_line(varA);
　 Declare
　　　 varB number(4):=2234;
　 Begin
　　　 DBMS_output.put_line(varA);
　　　 DBMS_output.put_line(varB);
　 End;
　 DBMS_output.put_line(varB);
End;
Declare
　 varA number(4);
　 varB number(4):=2234;
Begin
　 varA := 1234;
　 DBMS_output.put_line(varA||'　 '||varB);
End;
Begin块 注意事项：
-- 数据定义语言不能直接执行
Begin
　 drop table depta;
End;
-- select不能直接执行
select　 --必须使用select into 形式，只能且必须返回一行
Declare
　 varE empa%rowType;　 --与表的一行类型一样
　 vno empa.empno%type;　 -- 与表的某列类型一样
Begin
　 vno := 7902;
　 select * into varE from empa where empno = vno;
　 DBMS_output.put_line( varE.ename||'　 '||varE.job);
End;

inset　 --可以直接执行
Declare
　 vno empa.empno%type;
　 vna empa.ename%type;
　 vjob empa.job%type;
Begin
　 vno := 1236;
　 vna := 'Wing';
　 vjob := 'MANAGER';
　 insert into empa (empno,ename,job) values(vno,vna,vjob);
End;

delete --可以直接执行
Declare
　 vno empa.empno%type;
Begin
　 vno := 5555;
　 delete from empa where empno = vno;
End;
update --可以直接执行
Declare
　 vno empa.empno%type;
　 rsal empa.sal%type;
Begin
　 vno := 1236;
　 rsal := 100;
　 update empa set sal = nvl(sal,0) + rsal where empno = vno;
End;

 --事物控制语言可以直接执行
Declare
　 vno empa.empno%type;
　 rsal empa.sal%type;
Begin
　 vno := 1236;
　 rsal := 100;
　 update empa set sal = nvl(sal,0) + rsal where empno = vno;
　 commit;
End;
 -- 数据安全语言(DCL)不能直接执行
Begin
　 grant connect to scott;
End;

 --实例
 题：编写程序 向DEPT表中插入一条记录，
　　　 从键盘输入数据，如果
　　　 数据类型输入错误要有提示
　　　 无法插入记录 也要有提示
　　　 只能输入正数,如果有负数提示
　　 declare
　　　 n number;
　　　 no dept.deptno%type;
　　　 nm dept.dname%type;
　　　 lc dept.loc%type;
　　　 exp exception;　　 --用户定义的变量
　　　 exp1 exception;　　 --用户定义的变量
　　　 num number:=0;　　 --计数器
　　　　 pragma exception_init(exp,-1); --预定义语句
　　　 --捆绑Oracle内置异常(-1错误和异常变量关联),-1 主键冲突
　　　 pragma exception_init(exp1,-1476);除零异常
　　　 e1 exception; --用户定义的变量
　　 begin
　　　 --输入值
　　　　　 no := '&编号';
　　　　 num := num + 1;
　　　　 if no < 0 then
　　　　　　　 raise e1;　　　 --自定义异常的引发
　　　　　 end if;

　　　　 nm := '&名称';
　　　　 num := num +1;

　　　　 lc := '&地址';　
　　　　 num := num +1;

　　　　 n := 10 /0;　　　　

　　　　 insert into dept values (no,nm,lc);
　　　　 num := num +1;
　　　　 commit;

　　 exception
　　　　 --自定义异常
　　　　 when e1 then
　　　　　　　 dbms_output.put_line('编号不能为负数');
　　　　 --数据类型不对
　　　　 when value_error then
　　　　　　　 if num =0 then　
　　　　　　　　 dbms_output.put_line('编号数据类型不对');
　　　　　　　 elsif num = 1 then
　　　　　　　　 dbms_output.put_line('名称数据类型不对');
　　　　　　　 elsif num =2 then
　　　　　　　　 dbms_output.put_line('地址数据类型不对');
　　　　　　　 end if;
　　　　　　　 rollback;
　　　　 --主键冲突
　　　　 when exp then
　　　　　　　　 --sqlcode全局变量 异常错误号
　　　　　　　　 --sqlerrm全局变量 异常的文字信息　
　　　　　　　　 --dbms_output.put_line('异常的编号:'||sqlcode);
　　　　　　　　 --dbms_output.put_line('异常的内容:'||sqlerrm);
　　　　　　　　 --dbms_output.put_line('编号已存在') ;
　　　　　　　　 rollback;
　　　　 --非预定义异常(关联错误号)
　　　　 when exp1 then
　　　　　　　 dbms_output.put_line('0做了除数') ;
　　　　　　　　 raise_application_error(-20001,'0做了除数');
　　　　　　　　 --引起一个自定义的错误
　　　　　　　　　　 --预先保留-20001 到 -29999编号
　　　　　　　　 rollback;
　　　　 --其他的异常
　　　　 when others then
　　　　　　　　 dbms_output.put_line('异常的编号:'||sqlcode);
　　　　　　　　 dbms_output.put_line('异常的内容:'||sqlerrm);
　　　　　　　 -- dbms_output.put_line('出现错误');
　　　　　　　　 rollback;
　　 end;　
---从零开始学Oracle—异常处理(八)

一、异常

　　 (1) 程序错误

　　 - 编译时

　　 - 运行时

　　 (2)异常

　　 - 处理运行时错误

　　.PL/SQL是如何处理异常的?

　　 -　 错误引发异常

　　 -　 每当引发异常时，都将控制权传递给异常处理程序

　　 -　 异常处理程序处理异常

　　.异常中涉及的步骤

　　 -　 声明异常

　　 -　 引发异常

　　 -　 处理异常

　　.异常的类型

　　 -　 预定义的异常　　 --由Oracle服务器维护异常

　　 -　 非预定义的异常 又称'用户定义异常'

　　1、预定义异常

　　 由Oracle为常见错误预定义

　　 在DBMS_Standard程序包中提供了这些定义

　　 不需要显式声明

declare
　　 empJob emp.job%type;
begin
　 select job into empjob from emp where empno=0000;
　 DBMS_output.put_line('Employee job is '||emjob);
Exception
　 when No_data_found then
　　　　 DBMS_output.put_line('错误了，查询没有返回行');
　 when too_many_rows then
　　　　 DBMS_output.put_line('错误了，查询返回了多条数据');
　 when zero_divide　 then
　　　　 DBMS_output.put_line('错误了，除数不能为零');
　 when others then　 --一定是最后一个处理的代码
　　　　　 DBMS_output.put_line('错误了,未知的错误信息');
end;

　　2、非预定义异常

　　 无法预知的错误,如网络不通、服务没有启动、或其他错误，这样的异常需要用户要定义

declare
　　 PK_conflict Exception;　 --用户定义异常
　　 iCount int:=0;
begin
　　 select count(*) into iCount from emp where empno='7369'
　　 if iCount>0 then
　　　　　　 raise　　 Dup_Value;　 --引发异常
　　 end if;
Exception
　　　 when PK_conflict　 then　　 --处理异常
　　　　　 DBMS_output.put_line('表中已经有这条记录了');
end;
/
以上代码的问题是，只有条件满足 才会引发异常，否则永远都不会执行此句.

　　并且当有多个语句都有可能会引发此异常时，代码就过于冗余.并且都要显示

　　的引发异常，能不能让系统帮我们维护自定义异常了，当有异常引发时 自动

　　通知Oracle服务器处理异常

　　pragma指令

declare
　　 PK_conflict Exception;　 --用户定义异常
　　 pragma　 Exception_init(PK_conflict -1);　 --编译指令 可以把异常捆绑到Oracle 并覆盖预定义的异常
　　 iCount int:=0;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 这里的-1 代表主键冲突
begin
　　 insert into emp(empno,ename) values('7369','FeiYang');
Exception
　　　 when PK_conflict then　　 --处理异常
　　　　　 DBMS_output.put_line('表中已经有这条记录了,主键冲突');
end;

　　为了给用户友好的错误提示.用此函数可以定义友好的提示。

DBMS_output.put_line()函数只有sqlPlus 客户端支持.
Raise_Application_Error
　 .用于创建用户定义的错误消息的过程
　 .可以再执行部分和异常部分使用
　 .必须介于 -20000 和 -20999之间
　 .可以存储2M的错误信息
declare
　　 PK_conflict Exception;　 --用户定义异常
　　 pragma　 Exception_init(PK_conflict -1);　 --编译指令
　　 iCount int:=0;
begin
　　 insert into emp(empno,ename) values('7369','FeiYang');
Exception
　　　 when PK_conflict then　　 --处理异常
　　　　　 Raise_Application_Error(-20001,'表中已经有这条记录了'); --注意参数的前后顺序
end;
--常见系统异常参考表:

　1命名的系统异常　　　　　　　　　　　　　　　　　 产生原因
　2 access_into_null　　　　　　　　　　 未定义对象
　3 CASE_NOT_FOUND　　　　　　　　　　　　 CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
　4 COLLECTION_IS_NULL　　　　　　　　 集合元素未初始化
　5 CURSER_ALREADY_OPEN　　　　　　　 游标已经打开
　6 DUP_VAL_ON_INDEX　　　　　　　　　　 唯一索引对应的列上有重复的值
　7 INVALID_CURSOR　　　　　　　　　　　　 在不合法的游标上进行操作
　8 INVALID_NUMBER　　　　　　　　　　　　 内嵌的 SQL 语句不能将字符转换为数字
　9 NO_DATA_FOUND　　　　　　　　　　　　　 使用 select into 未返回行，或应用索引表未初始化的元素时
10 TOO_MANY_ROWS　　　　　　　　　　　　　 执行 select into 时，结果集超过一行
11 ZERO_DIVIDE　　　　　　　　　　　　　　　 除数为 0
12 SUBSCRIPT_BEYOND_COUNT　　　　 元素下标超过嵌套表或 VARRAY 的最大值
13 SUBSCRIPT_OUTSIDE_LIMIT　　　 使用嵌套表或 VARRAY 时，将下标指定为负数
14 VALUE_ERROR　　　　　　　　　　　　　　　 赋值时，变量长度不足以容纳实际数据
15 LOGIN_DENIED　　　　　　　　　　　　　　 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
16 NOT_LOGGED_ON　　　　　　　　　　　　　 PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
17 PROGRAM_ERROR　　　　　　　　　　　　　 PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
18 ROWTYPE_MISMATCH　　　　　　　　　　 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
19 SELF_IS_NULL　　　　　　　　　　　　　　 使用对象类型时，在 null 对象上调用对象方法
20 STORAGE_ERROR　　　　　　　　　　　　　 运行 PL/SQL 时，超出内存空间
21 SYS_INVALID_ID　　　　　　　　　　　　 无效的 ROWID 字符串
22 TIMEOUT_ON_RESOURCE　　　　　　　 Oracle 在等待资源时超时