安徽理工大学oracle编程.doc

1.表空间]是Oracle数据库内部数据的逻辑组织结构。一般情况下，建立表空间由特权用户或DBA完成。建立数据库时，Oracle会自动建立。系统表空间SYSTEM和SYSAUX临时表空间TEMP撤销表空间UNDOTBS1
创建表空间命令语法：CREATE [SMALLFILE | BIGFILE] [PERMANENT | TEMPORARY | UNDO] TABLESPACE tablespace
[DATAFILE|TEMPFILE] datafile_tempfile_spec [, datafile_tempfile_spec ... ]
[{ MINIMUM EXTENT integer [ K | M ]
| BLOCKSIZE integer [ K | M ] | EXTENT MANAGEMENT LOCAL [AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]] | SEGMENT SPACE MANAGEMENT { MANUAL | AUTO } | { ONLINE | OFFLINE }
| { LOGGING | NOLOGGING }}];
2.Oracle用户表5种类型：关系表：用来存储用户的永久数据。临时表：供系统使用，如查询结果等。按索引组织的表：用来存储数据和索引。外部表：数据库外部数据文件，只读。嵌套表：一个表被另外一个表引用。其中关系表是用来存储和管理用户数据的对象。在Oracle中，还可对关系表创建分区表和蔟表，大大加快了表的访问速度。
3.创建数据表结构的方法SQL命令创建表结构，方便、快捷，修改容易，可重复性好。OEM创建，可视化程度高，修改更加方便。
4.使用SQL命令创建表结构：

5.使用查询子句创建数据表：利用查询子句建立新表，不但能备份原表使其不被破坏，还能选择其部分列创建新表。创建新表时，以查询条件为源，可以快速在新表中得到满足查询条件的数据。
SQL语法是：CREATE TABLE 表名[(列名,列名…)] AS 子句；(1) 原表中的long型字段不会复制到新表中。(2) 原表的完整性约束不会被复制到新表中，需要另外增加完整性约束。
6.表结构的修改：Oracle数据表创建之后如果需要修改其表结构，同样可以通过SQL命令修改或者用企业管理器OEM进行修改。1. 用SQL命令修改表结构：ALTER TABLE 表名 MODIFY (字段名1 类型宽度)，…MODIFY (字段名n 类型宽度) STORAGE 子句；
【例3-4】修改表emp_new1中员工名字段宽度，增大2字符。
ALTER TABLE emp_new1 MODIFY (ename varchar2(12)); --原来是10,改为12
7.用SQL命令删除列:SQL命令删除列，一次只能删除一列。当需要删除多列时，只能多次使用删除命令。SQL删除列命令语法是：ALTER TABLE 表名 DROP(列名);
【例3-7】删除例3-6表emp_new3中增加新列email字段。
SQL>ALTER TABLE emp_new3 DROP(email); --屏幕显示：表已经改变，删除列成功
8.表的删除:：DROP TABLE 表名 [CASCADE CONSTRAINTS]; --括号中是级联约束子句
没有被外键关联的表，直接删除。存在外键关联的表，删除时需要使用级联约束子句。
9. 表约束:Oracle数据库通过约束规则实现数据完整性控制，这些约束规则可以定义在数据列中，也可以在列定义之外。 约束规则的种类:1) NOT NULL：非空约束 2) Primary key：主键约束3) Unique：唯一性约束4) Foreign key：外键约束5) Check：检查约束6) REF：定义列对象的参照关系
10. 完整性约束应用特点:1) Primary key与Unique约束默认会成为索引。当主键和Unique有多个字段时，在where语句中的条件必须有主键或者unique的第一个字段，否则不会使用索引。组合关键字只能在表级定义。2) 一个表只能指定一组关键字，但可以在不同列上建立多个数据唯一性约束。3) 可以在定义表时定义完整性，也可以在定义表之后使用Alter table语句定义完整性.
11. 表约束的维护:表约束的维护有增加约束、禁用与启用约束、重命名约束和删除约束。
1) 增加约束:增加(add)主键约束和检查(check)约束。2) 禁用与启用约束: 3) 重命名约束
4) 删除约束例】禁用emp_new5表主键约束，禁用检查约束。Alter table emp_new5 modify primary key disable; --禁用主键约束Alter table emp_new5 modify constraint check_sal disable； --禁用检查约束 例】启用emp_new5表主键约束，启用检查约束。Alter table emp_new5 modify primary key enable; --启用主键约束Alter table emp_new5 modify constraint check_sal enable; --启用检查约束 例】重命名emp_new5表检查约束为check_sal_comm。Alter table emp_new5 rename constraint check_sal to check_sal_comm;--重命名检查约束 例】删除emp_new5表检查约束check_sal_comm。Alter table emp_new5 drop constraint check_sal_comm; --删除检查约束
13. 索引的定义：索引是建立在表或视图上的一种独立的物理数据库结构，是一种可选的数据对象，创建索引的目的是为了加快表中数据的查询速度。索引和表一样，也具有独立的存储结构，当数据表中数据宏大时，索引也占有较大的存储空间。为了进一步优化索引的查询，建立索引时可以设置索引文件存储的表空间和存储参数。索引类型：B树索引：这是默认的索引类型。B树蔟索引：为簇定义的索引。位图索引：适用于字段值重复较多的场合。函数索引：能够在索引中包含预先计算的函数值。全局与本地索引：在分区表上定义的索引。
15：簇表的定义：簇表是由多个共享相同数据块的表组成的，把这些表合在一起组成簇表可以提高数据访问的效率例如EMP表和DEPT表共享DEPTNO列，如果把EMP表和DEPT表中组成簇表时，两个表中每个部门的所有行都会存储到相同的数据块中。建立簇表带来的好处是，不仅减少了磁盘I/O,加速了数据访问速度，还由于在簇表中存储相关表的簇索引要比非簇表索引数据占有的空间更少。
16.分区表的定义：为了加快对宏大数据的访问速度，Oracle采用了分区表的方式组织和管理数据表，同时还采用了分区索引，使各个分区和分区索引可以按一定的分区规则提前组织、单独使用甚至可以非常方便地存储在不同的表空间中。这样不仅减少了数据的I/O操作，极大地提高了数据访问的效率，还能单独备份和恢复每个分区数据，使数据更加安全。
Oracle提供的分区表管理4种类型：范围分区、散列分区、列表分区和组合分区。
1. ）范围分区表：范围分区就是把表中某数值字段值或日期字段，按照一定的范围来进行分区）散列分区表：散列分区可以把数据表中的数据按均等地、随机的原则划分到用户设定的若干分区中，提供给用户一种灵活的放置数据的方法，提高了数据访问的性能。）列表分区表：如果分区字段的值不能进行范围分区，但可以按值确定出几种归属，则可以使用枚举的方式列出分区字段依据的归属条件，把表的数据分类映射到列表分区。）组合分区表： 组合分区是在分区中使用范围分区方法、在子分区中使用散列分区或列表分区方法。组合分区适用于历史数据和条块数据，它改善了范围分区及其数据放置的管理型，并提供了散列分区的并行机制的优点。

17.序列（大题）：使用SQL语句创建序列：在SQL*Plus中创建序列的语法是：
CREATE SEQUENCE 序列生成器名
[INCREMENT BY n] --时间间隔(也就是序列数的间隔)
[START WITH n] --起始序列值
[MAXVALUE n | NOMAXVALUE] --最大序列值
[MINVALUE n | NOMINVALUE] --最小序列值(反序用)
[CYCLE | NO CYCLE] --达到最大值后变为最小值，可构成循环数。
[CACHE n | NO CACHE] --开辟高速缓存，以便高速访问多个序列值
[ORDER | NO ORDER] --产生时间戳时可选择ORDER
读取序列值，需要使用到两个伪列：
CURRVAL伪列：用以读取序列的当前值，调用后序列值不改变。
NEXTVAL伪列：用以读取序列生成的下一个值，调用后序列值改变为新值。
修改序列：序列仅有起始值不能修改，其他参数均可修改，否则必须删除序列后重建。
修改序列生成器的语法是：ALTER SEQUENCE 序列名
删除序列的语法是：DROP SEQUENCE 序列名
【例3-44】删除序列my_sequ。SQL> DROP SEQUENCE my_sequ;
18：视图的概念：视图是一个基于基表和其他视图的逻辑表，视图由语句组成。根据用户的需要，视图从相应的数据表中抽出数据以组成相应的“表”。
使用视图的好处是：1) 隐藏逻辑数据的复杂性，使复杂的查询简单化；2) 为应用提供数据独立性，系统安全性更好；3) 可以为相同数据提供不同的视角。
19.数据库链接：Oracle提供了数据库链接的功能，目的是在分布式环境下，从本地数据库访问远程数据库中的数据。应用数据库链接，可以直接访问远程数据库的表。使用方法：通过数据库链接对远程数据库的表进行读写，可以直接访问，也可以在本地建立远程数据库的视图和创建同义词进行访问。
21.实体化视图:Oracle提供了实体化视图，实体化视图也是一个表，用以在分布式环境下通过数据库链接查询远程数据库的表或视图。被查询的表或视图称为主表，被查询的数据库称为主数据库。建立实体化视图分为两步：1) 建立实体化视图日志(表)，用以提供刷新实体化视图的数据2) 建立实体化视图。
22.SQL语言概述:SQL语言四大类：数据定义语言DDL)：用于定义、修改、删除数据库对象，进行权限管理，在第3章已有介绍。数据操纵语言DML)：用于插入、更新、删除、合并数据库中的数据，在本章中介绍。数据控制语言DCL)：用于控制数据库服务器的启动和关闭等操作.事务控制)：用于多用户环境下，保证数据库中数据的完整性、一致性.
23. DML: Oracle数据库操纵语言(DML)用于查询和操纵模式对象中的数据，包含INSERT、UPDATE、DELETE和MERGE等命令。
24. 插入语句INSERT:INSERT语句用于在数据表中增加数据，例如插入一行记录或多条记录。INSERT语句的基本语法是：INSERT INTO 表名(列名1，列名2，…) values(数据1，数据2，…)使用子查询语句从其他表中提取数据插入到指定的表中更为方便，因为这种方法可以一次插入多个记录，其语法如下：INSERT INTO 表名(<列名1>，<列名2>，…) SELECT <列名1>，<列名2>，… （换行）FROM <表名>；
25. 更新语句UPDATE: UPDATE语句用于修改表中的数据。 UPDATE两种语句格式。
1. 简单格式:简单格式用于直接修改某个列值，例如：UPDATE 表名 SET 列名=’新值’ WHERE 更新条件;2. 使用子查询的UPDATE语句:PDATE 表名1 SET(列名1，列名2，…)=
(SELECT 列名1，列名2，…FROM 表名2 WHERE 条件);
26.删除语句DELETE:DELETE语句用于删除表中的一行或多行数据。其格式如下：
DELETE [FROM] 表名 WHERE 条件; --“[ ]”中是可选项。
27. IF条件语句:PL/SQL的IF语句和CASE语句实现程序的选择分支执行功能。当选择分支较多时，CASE语句有更好的可读性。
28. CASE分支语句:CASE语句是将某个表达式的值与一组常量进行比较，如表达式与某个常量的值相等，则执行对应的语句。
CASE
WHEN 条件表达式1 THEN
语句序列1；
WHEN 条件表达式2 THEN
语句序列2；
[ELSE 语句序列3]
END CASE;
29.循环语句: 循环语句是指重复执行一组操作的语句，直到完成预定的循环条件后结束。PL/SQL中循环语句有3种格式，分别是LOOP循环、WHILE循环和FOR循环，以适应不同的应用场合。LOOP循环:LOOP循环的特点是先进入循环体执行语句序列，然后判断是否满足退出循环体的条件：
LOOP
语句序列;
EXIT WHEN 布尔表达式;
END LOOP;
WHILE循环在条件满足时，保持循环体中语句的执行，直到条件不满足时退出循环。
FOR循环使用计数器来控制循环次数，计数器从低界开始递增到高界，也可选择REVERSE从高界递减到低界，当循环计数器超出指定的范围时，循环结束。
30. 游标的概念:当PL/SQL中的SELECT语句返回一个记录集，用户需要对该记录集中每一条记录逐个进行处理时，使用游标来管理SELECT语句是一个有效的方法。游标对应的是一条SELECT语句，当打开一个游标，就执行了该SELECT语句。通过对游标控制，用户可以完成对表中记录逐条处理。游标分为两类：隐式游标和显式游标。当执行一个SELECT语句时，Oracle自动打开的游标为隐式游标。显式游标是需要用户先定义、后使用的，显式游标适用于需要单独处理每一条记录的场合。游标定义格式：CURSOR 游标名[(参数)] IS SELECT 子句; --参数与括号是任选项,同时舍取 游标的使用方法:：1) OPEN语句打开游标。2) FETCH语句取出一行数据。3) 处理取出的数据。4) 返回到步骤(2)继续取下一行数据。5) CLOSE语句关闭游标。
FETCH语句的语法是：FETCH游标名[(参数)] INTO 变量名表;具体使用游标是通过PL/SQL循环语句反复执行第(2)~第(4)步的。
32.显式游标的属性:：%ISOPEN：布尔型。如果游标已经打开，值为TRUE。%NOTFOUND：布尔型。如果最近一次FETCH操作没有返回结果，则值为TRUE。%FOUND：布尔型。如果最近一次FETCH操作有返回结果，则值为TRUE。%ROWCOUNT：数值型。取值为到当前记录为止返回的记录数。
游标属性的应用方法是将属性直接写在游标名的后面。如下代码判断游标emp_cur是否打开，如果尚未打开，则打开它。
IF NOT (emp_cur%ISOPEN) THEN
OPEN emp_cur;
END IF;
33. 在emp表中插入一条新记录。
UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno;
IF SQL%NOTFOUND THEN
INSERT INTO emp VALUES (my_empno, my_ename, …);
END IF;
34. 存储过程：PL/SQL过程又称为存储过程，其表现形式有两种：一种是匿名程序块，匿名程序块每次执行时，需要先编译后运行，执行完毕后就不再存在于数据库系统中。另一种是命名程序块，它可以保存在数据库系统中反复使用，因此有很高的效率。
35. 过程的概念和创建格式：过程就是一段应用程序，可用来完成数据库对象的操作和控制及数据处理任务。为了方便系统调用，必须给过程命名。创建过程的格式是：
CREATE [OR REPLACE] PROCEDURE 过程名
[参数1 [{IN | OUT | IN OUT}]] 类型，
[参数2 [{IN | OUT | IN OUT}]] 类型 ...]
{ IS | AS }
变量说明部分;
BEGIN
执行部分;
EXCEPTION
异常处理部分;
END;
36.数据库触发器应用： 数据库触发器(Database Triggers)是一种特殊类型的PL/SQL程序块，其结构与过程和函数类似，也包括声明部分、执行部分和异常处理部分。但触发器执行的方法与过程有2点不同：1) 过程是显式地通过调用执行，而触发器在事件的触发下隐式地运行。2) 过程调用可以传递参数，而触发器不能接收参数。触发器的分类：Oracle触发器主要分为3种：DML触发器、DDL触发器和系统触发器。
37. 触发器程序的编写：
创建触发器的语法格式是：
CREATE [OR REPLACE] TRIGGER [模式.]触发器名 {BEFORE|AFTER|INSTEAD OF}
{DML事件|DDL事件|DATABASE事件}
ON {[模式.]表|[模式.]视图|DATABASE}
[FOR EACH ROW [WHEN 触发条件]]
BEGIN
触发器程序语句;
[EXCEPTION 异常处理程序语句;]
END [[模式.]触发器名];
38. 触发器应用：Oracle在触发器中还提供了两种特殊的表：new表和old表。需要注意，对INSERT操作来说，不存在“旧”值对应记录，对DELETE操作来说，不存在“新”值对应记录。1) DELETE触发器：Delete触发器是在表中的行被删除前或删除后自动触发的触发器。
2) INSERT触发器：INSERT触发器是当表中插入新的数据行时被触发的触发器。3) UPDATE触发器：UPDATE触发器是当表中更新数据行时响应的触发器。4) 替换触发器：替换触发器，亦称为INSTEAD OF触发器，是应用在复杂视图上的触发器。复杂视图是指具有多数据操作特征的视图。
创建替换触发器需要注意的是：视图不能指定WITH CHECK OPTION；触发器不能指定BEFORE/AFTER选项；触发器必须指定FOR EACH ROW；
39. 异常处理程序：异常又称为例外，是PL/SQL执行期间引发的一个错误或警告，任何异常都将终止当前PL/SQL程序块的执行。PL/SQL中包括3种类型的异常：(1) 预定义异常(2) 非预定义异常(3) 用户自定义异常。
PL/SQL程序块的异常处理是在EXCEPTION程序块内中使用WHEN子句捕获并处理异常的，并且EXCEPTION程序块必须放在所在PL/SQL程序块的最后部分，其语法格式是：