Oracle笔记SQL

Oracle 笔记

SQL、PL/SQL学习笔记1．SQL并⾏查询

alter session enable parallel dml

execute immediate 'alter session enable parallel dml'; --修改会话并⾏DMLselect /*+parallel(a,4)*/ * from table_name aselect /*+parallel(a,8)*/ * from table_name a

select /*+parallel(a,4) parallel(b,4) parallel(c,4)*/ a.*,b.*,c.* from table_name1 a,table_name2 b,table_name cinsert /*+parallel(t,4)*/ into table_name t insert /*+parallel(t,8)*/ into table_name t

/*+parallel(t,8)*/ 并⾏处理，⼀般为CPU的倍数如：4，8等,在执⾏类型SQL 必须先运⾏:alter session enable parallel dml2．删除表分区数据

alter table masamk.tb_mk_sc_user_mon truncate partitionmk_user_mon_'||trim(iv_month) 删除指定表分区数据3．minus(差集)与intersect(交集)

minus 指令是运⽤在两个 SQL 语句上。它先找出第⼀个 SQL 语句所产⽣的结果，然后看这些结果有没有在第⼆个 SQL 语句的结果中,如果有的话，那这⼀笔资料就被去除，⽽不会在最后的结果中出现; 如果第⼆个 SQL 语句所产⽣的结果并没有存在于第⼀个 SQL 语句所产⽣的结果内，那这笔资料就被抛弃。

intersect 指令是运⽤在两个SQL语句上，如果两个SQL语句的记录完全相同则显⽰相应记录，否则将不在结果中出现4．Order by 中的 nulls last

order by area_code,bill_month nulls last --nulls last 将排序字段为null 记录放在最后⾯5．nvl的⼏个不同函数

nvl(a,1) 如果 a 为 null 返回 1,否则返回 anvl2(a,1,0) 如果 a 为 null 返回 0,否则返回 1nullif(a,b) 如果 a = b 返回 null ,否则返回 a

6．怎样确保最终⽤户在数据库中只有N个会话(如果N 为1则只有1个会话)

create profile one_session limit sessions_per_user N; --创建参数⽂件(N 为任意整数)alter user <⽤户> profile one_session; --设置⽤户的参数⽂件alter system set resource_limit=true; --设置资源限定7．表的字段参照另外表的字段

create table resources ( resource_name varchar2(10) primary key,,,,); create table schedules (resource_name refere ncesresources,….);8．绑定变量的使⽤1) sql中的绑定变量

定义绑定变量：variable emplno varchar2(10);

给绑定变量赋值：execute :emplno := ‘12345670’;

sql/plus中使⽤绑定变量：select * from emp where empno = :emplno;pl/sql中使⽤绑定变量：execute immediate ‘insert into t values(:x)’ using x;

游标中使⽤绑定变量：open c1 for ‘select * from emp where empno=:empno’ using empno;

2) DDL语句中不允许使⽤绑定变量,如:execute immediate ‘create table a as selct * from b where x=:x’ using x;3) pl/sql中的批量绑定变量（forall）a) forall i in 1..x.count

dml;--只能有⼀条语句(update,insert,delete)

sql%bulk_rowcount(i):⽤于取得在执⾏批量绑定操作时的第i个元素作⽤的⾏数

b) bulk collect ⼦句：⽤于取得批量数据，它只适⽤于select into、fetch into和DML返回⼦句语法：…BULK COLLECT INTO collection_name…i. select 中使⽤bulk collect declare

type emp_table_type is table emp%rowtype index by binary_integer; emp_table emp_table_type;begin

select * bulk collect into emp_table from emp where deptno=&no; for i in 1..emp_table.count loopdbms_output.put_line(emp_table(i).emp);end loop;

forall i in 1..emp.table.count

update sal set deptno = emp_table(i).deptnowhere empno = emp_table(i).empno;

dbms_output.put_line('第2个元素更新的⾏数为：'||sql%bulk_rowcount(2));end;

ii. dml的返回⼦句中使⽤bulk collect declare

type ename_table_type is table of emp.ename%type;ename_table ename_table_type;begin

delete emp where deptno=&no

returning ename bulk collect into ename_table;for i in 1..ename_table.count loopdbms_output.put_line(ename_table(i));end loop;end;

c) fetch c1 bulk collect into collect1,collect2,…[limit rows] 9．在SQL中锁定记录锁(lock)机制⽤于管理对共享资料的并发访问，并提供数据完整性和⼀致性

锁的类型：DML锁、DDL锁、内部锁和闩1) DML锁

a．事务锁(TX锁)：事务发起第⼀个修改时会得到TX锁，直到事务提交或回滚b． DML Enqueue锁(TM锁)：⽤于确保在修改表的内容时，表的结构不会改变2) DDL锁

a．排他DDL锁(Exclusive DDL Lock)：这会防⽌其他会话得到它们⾃⼰的DDL 锁或TM(DML)锁(即其他会话只能对该表执⾏select )。如：alter table

b．共享DDL锁(Share DDL Lock)：这些锁会保护所引⽤对象的结构，使之不会被其他会话修改，但是允许修改数据。如在创建VIEW时，对原始表就会加共享锁，此时原始可以修改数据，但不能修改表结构

c．可中断解析锁(Breakable parse locks)：这些锁允许⼀个对象向另外某个对象注册其依赖性

3) 闩(latch)：是轻量级的串⾏化设备，⽤于协调对共享数据结构、对象和⽂件的多⽤户访问；闩⽤于保护某些内存结构，如数据库块缓冲区缓存或共享池中的库缓存4) ⼿动锁定和⽤户定义锁

a．通过⼀条SQL语句⼿动地锁定数据。i. select … for update [nowait/wait [n]]ii. select … for update of

table_name --多表关联时锁定指定表的数据⾏

iii. lock table in exclusive mode b．通过DBMS_LOCK包创建我们⾃⼰的锁

5) select … for update [nowait/wait [n]] [skip locked] 详解select * from resources where resource_name=’abc’ for update[nowait/wait [n]] [skip locked];

nowait：⽴即执⾏，如果另有会话正在修改该记录会⽴即报告错误：ORA-000: 资源正忙，要求指定 NOWAIT；如果不选择nowait选项则会⼀直处理等待状态。wait [n]：等待n秒，如果另有会话正在修改该记录会报告错误：ORA-30006: 资源已被占⽤; 执⾏操作时出现 WAIT 超时

skip locked：跳过已被别的会话锁定的记录

6) set transaction read only(只读事务)：使会话取得特定时间点的数据，即使其它会话已经修改并提交新数据，当前会话也只能看到锁定时的数据，同时当前会话不能执⾏DML.

7) set transaction isolation level { serializable | read committed }(顺序事务)：同只读事务，但允许执⾏DML语句。10．数据库与实例的关系

数据库(Database)：物理操作系统⽂件或磁盘的集合。(数据库是磁盘上存储的数据⽂件集合)

实例(instance)：⼀组Oracle后台进程/线程以及⼀个共享内存区，这些内存由同⼀个计算机上运⾏的统⼀线程/进和所共享。(实例就是⼀组后进程和共享内存)

实例与数据库之间的关系是：数据库可以由多个实例装载和打开，⽽实例可以在任何时间点装载和打开⼀个数据库。11．Oralce数据库所包含的⽂件类型

1) 与实例相关的⽂件：参数⽂件(parameter file)、跟踪⽂件(trace file)、警告⽂件(alert file)

2) 构成数据库的⽂件：数据⽂件(data file)、临时⽂件(temp file)、控制⽂件(control file)、重做⽇志⽂件(redo log file)、密码⽂件(password file)

3) Oracle 10g新增⽂件：修改跟踪⽂件(change tracking file)、闪回⽇志⽂件(flashback log file)

4) 其他类型⽂件：转储⽂件(DMP file)、数据泵⽂件(Data Pumn file)、平⾯⽂件(flat file)12．表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系

1) 表空间(tablespace)：是Oracle中的⼀个逻辑存储容器，位于存储层次体系的顶层，包含⼀个或多个数据⽂件2) 段(segment)：占⽤存储空间的数据为对象，如表、索引、回滚段等；段由⼀个或多个区段组成3) 区段(extent)：是⽂件中⼀个逻辑上连续分配的空间；区段由块组成

4) 块(block)：是Oracle中最⼩的空间分配单位；数据⾏、索引条⽬或临时排序结果就存储在块中；Oracle中常见的块⼤⼩：2K、4K、8K、16K(最⼤不能超过32K)

5) 它们之间的关系：数据库由⼀个或多个表空间组成，表空间由⼀个或多个数据⽂件组成，表空间包含段，段由⼀个或多个区段组成，区段则由连续的块组成13．名称解释

1) 决策⽀持系统(DSS)：Decision Support System2) 联机事务处理(OLTP)：On-line Transaction Processing

3) 联机分析处理(OLAP)：On-Line Analytical Processing也称为在线分析处理。

4) ETL(Extraction-Transformation-Loading)：抽取(Extraction)、转换(Transformation)、载⼊(Loading) ETL负责将分布的、异构数据源中的数据

如关系数据、平⾯数据⽂件等抽取到临时中间层后进⾏清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。 ETL是数据仓库中的⾮常重要的⼀环。

5) 关系数据库管理系统(RDBMS)：Relational Database Management System

6) 表的三种联接⽅式:nested loop(嵌套循环连接)、sort merge join(排序合并连接)、hash join(哈希连接)7) 数据查询语⾔(Select):⽤于检索数据库数据

8) 数据定义语⾔(DDL)：Data Definition Language(如 create table、alter table、truncate table):⽤于建⽴、修改和删除数据为对象(采⽤先提交(commit)，再执⾏DDL，再COMMIT，所有如果有必须回滚的事务，DDL不会回滚⽽会直接提交(commit))9) 数据操纵语⾔(DML): Data Manipulation Language(包含:insert、update、delete):⽤于改变数据库数据

10) 数据控制语⾔(DCL): Data Control Language(包含:grant、revoke):⽤于执⾏权限授予和收回操作(同数据操纵语⾔DML会⾃动提交事务)

11) 事务控制语⾔(TCL):Transactional Control Language(Commit、Rollback、Savepoint)：⽤于维护数据的⼀致性12) Recursive Calls：Number of recursive calls generated at both the user and system level.（⽤户与系统造成的递归调⽤数）

13) DB Block Gets：请求的数据块在buffer能满⾜的个数(Number of times a CURRENT block was requested.)

14) Consistent(⼀致性) Gets：数据请求在回滚段Buffer中的总数 (Number of times a consistent read was requested for ablock.)

15) Physical Reads：从磁盘读到Buffer Cache数据块数量(Total number of data blocks read from disk. This number equalsthe value of \"physical reads direct\" plus all reads into buffer cache)

16) Sorts (disk)：Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quiteresource intensive. Try increasing the size of the initialization parameterSORT_AREA_SIZE.(排序运算需要的最⼩磁盘写)

17) PCTFREE：PCTFREE参数⽤于指定块中必须保留的最⼩空闲空间⽐例.之所以要为块保留⼀些空闲空间,是因为在对块中存储的数据进⾏修改时(UPDATE操作),有可能会需要更多的存储空间.这时如果块中存储空间不⾜,就必须分配新块,此时会产⽣指针,降低性能.⽽如果每块在最初填写数据时均不填满,保留⼀

部分可⽤空间,⽐如20%,则可以尽量避免上述问题. 当⼀些块在以后使⽤时,⽐如进⾏update操作时,则可以使⽤那20%的空间.⽽如果⼀些块中的数据后来⼜没有了或减少了,⽐如由原来的90%变为70%,因为已符合PCTFREE的规定,那么如果有INSERT操作的话,则该块⼜可以被使⽤了,但实际上这个块只有10%的空间可以给INSERT操作使⽤,所以这种情况应该避免.那就⽤到了下⾯的参数(PCTUSED)

18) PCTUSED：PCTUSED参数⽤于指定⼀个百分⽐,当块中已经使⽤的存储空间降低到这个百分⽐之下时,这个块才被标记为可⽤,否则按上⾯的即使块中已经有30%的可⽤空间,块依然不可⽤. 这是ORACLE为了防⽌出现太⼤的数据碎⽚导⾄降低数据库性能及防⽌浪费空间⽽导⾄磁盘利⽤率低的⼀个提供给专业⽤户使⽤的参数！

当⼀个块写到pctused所指定的值时（如：80%），这个块就被标记为已⽤，不可以再朝⾥边写数据，以为⽇后修改此块内的某条记录（主要是增加数据量）提供条件

当⼀个块因为修改及删除记录⽽使其占⽤率降低到pctfree所指定的值时（如：20%），在数据字典⾥这个块被标记为可⽤，新增加的记录就可以朝这个块⾥写数据

这个参数⾮常专业，⼀定要你⾮常熟悉磁盘调整及了解⾃⼰数据库的应⽤特点才可以调整，⽽且调整此参数⼀定要很有经验，建议不是很确定不要随意调整，因为会⼤⼤降低数据库效率的

19) INITRANS：参数确定为事务处理项预分配多少数据块头部的空间。当您预计有许多并发事务处理要涉及某个块时，可为相关的事务处理项预分配更多的空间，以避免动态分配该空间的开销。

20) MAXTRANS：参数并⾏使⽤某个数据块的事务处理的数量。当您预计有许多事务处理将并⾏访问某个⼩表时，则当创建表时，应设置该表的事务处理项预分配更多的块空间，较⾼的MAXTRANS 参数值允许许多事务处理并⾏访问该表INITRANS和MAXTRANS 参数的设置可能相应低⼀些（如分别为2和5）。14．数据库分析技术⽤analyze语句产⽣分析数据

分析表：analyze table zl_yhjbqk estimate statistics sample 20 percent 分析索引：analyze index⽤户资料表主键computestatistics

分析列：analyze table zl_yhjbqk compute statistics for columns hbs_bh 分析索引列：analyze table zl_yhjbqk computestatistics for all indexed columns⽤sys.dbms_utility包分析数据

分析数据库（包括所有的⽤户对象和系统对象）：analyze_database

分析⽤户所有的对象（包括⽤户⽅案内的表、索引、簇）：analyze_schema ⽤sys.dbms_stats包处理分析数据分析数据库（包括所有的⽤户对象和系统对象）：gather_database_stats 分析⽤户所有的对象（包括表、索引、簇）：gather_schema_stats分析表：gather_table_stats分析索引：gather_index_stats

删除数据库统计信息：delete_database_stats删除⽤户⽅案统计信息：delete_schema_stats删除表统计信息：delete_table_stats删除索引统计信息：delete_index_stats删除列统计信息：delete_column_stats设置表统计信息：set_table_stats设置索引统计信息：set_index_stats设置列统计信息：set_column_stats

ORACLE推荐⽤户采⽤sys.dbms_stats包体进⾏分析，因为在ORACLE9i及其以上的版本全⾯扩充的此包体的功能。sys.dbms_utility包体进⾏分析时会对所

有的信息全部分析⼀遍，时间⽐较长，⽽在9i中sys.dbms_stats可以利⽤表修改监控技术来判断需统计分析的表进⾏，节省了

⽤户的分析资源。

15．Oracle数据库中⼼后台进程

1) 进⾏监视器(PMON:Process Monitor)：负责在出现异常中⽌的连接之后完成清理、监视其他Oracle后台进程并在必要时重启这些后台进程、向Oracle TNS注册实例

2) 系统监视器(SMON:System Monitor SMON)：进⾏要完成所有”系统级”任务：清理临时空间、合并空闲空间、针对原来不可⽤的⽂件恢复活动的事务、执⾏RAC中失败节点的实例恢复、清理OBJ$(OBJ$是⼀个低级数据字典表，其中⼏乎对每个对象都包含⼀个条⽬)、收缩回滚段、“离线”回滚段

3) 分布式数据库恢复(RECO:Distributed Database Recovery)

4) 检查点进程(CKPT:Checkpoint Process)：更新数据⽂件的⽂件⾸部，以辅助真正建⽴检查点的进程(DBWn)5) 数据库导写⼊器(DBWn:Database Block Writer):负责将脏块写⼊磁盘的后台进程

6) ⽇志写⼊器(LGWR:Log Writer)：负责半SGA中重做⽇志缓冲区的内容刷新输出到磁盘。如果满⾜以下某个条件，就会做这个⼯作：

a.每3秒会刷新输出⼀次b.任何事务发出⼀个提交时

c.重做⽇志缓冲区1/3满，或者已经包含1MB的缓冲数据

7) 归档⾥程(ARCn:Archive Process)：当LGWR将在线重做⽇志⽂件填满时，就将其复制到另⼀个位置。8) 其他中⼼进程：取决于所⽤的Oracle特性，可能还会看到其他⼀些中⼼进程

a.⾃动存储管理后台(ASMB:Automatic Storage Management Background):在使⽤了ASM的数据库实例中运⾏，负责与管理存储的ASM实例通信、向ASM实例提供更新统计信息

b.重新平衡(RBAL:Rebalance):在使⽤了ASM的数据库实例中运⾏。向ASM磁盘组增加或去除磁盘时，RBAL进⾏负责处理重新平衡的请求

16．Oracle数据库⼯具后台进程

1) 作业队列(CJQ0:job queue coordinator,Jnnn)2) ⾼级队列(QMNC,Qnnn)

3) 事件监视器进程(EMNn:Event Monitor Process)4) 内存管理器(MMAN:Memory Manager)

5) 可管理性监视器(Manageability Monitor:MMON、MMNL、Mnnn)6) 修改跟踪进程(CTWR:Change Tracking Process)7) 恢复写⼊器(RVWR:Recover Writer)17．Oracle数据库从属进程

1) I/O从属进程：⽤于不⽀持异步I/O的系统或设备模拟异步I/O。DBWn和LGWR可以利⽤I/O从属进程来模拟异步I/O;另外RMAN写磁带进也可能利⽤

I/O从属进程。有两个参数控制I/O从属进程的使⽤：BACKUP_TAPE_IO_SLAVES、DBWR_IO_SLAVES

2) 并⾏查询从属进程：对SELECT、CREATE TABLE、CREATE INDEX、UPDATE 等SQL语句，创建⼀个执⾏计划，其中包含可以同时完成的多个(⼦)执⾏计划3)18．insert语句的⽤法

1) insert in to table_name(column_id…) values(values1…);2) insert /*+append */ into table_name(column_id…)

values(values1…);3) 多表插⼊数据:

insert all when deptno=01 then into dept01(column_id…) values(…) when deptno=02 then into dept01(column_id…)values(…)

else into dept(column_id…) values(…)select deptno from emp;

insert first when deptno=01 then into dept01(column_id…) values(…) when deptno=02 then into dept01(column_id…)values(…)

else into dept(column_id…) values(…)select deptno from emp;

说明:当⼤量数据插⼊时，使⽤2)将快于1),2)是直接插⼊，不写⽇志. 19．commit、rollback、savepoint的使⽤commit：⽤于提交事务

savepoint：设置保存点(如：savepoint a; dbms_transaction.savepoint(a)) rollback：回滚事务(如：rollback;--回滚所有事务rollback to a;--回滚保存点a后所有事务)20．PL/SQL中的复合数据类型

1) PL/SQL中的记录：type type_name isrecord(filed_declaretion…)：⽤于处理单⾏多列a) type t_record is record(emplno varchar2(10));e_record t_record;

b) e_record hrs101t0%rowtype;2) PL/SQL中的集合：⽤于处理多⾏单列a) 索引表：

type type_name is table of element_type [not null] index bybinary_integer/pls_integer;identifier type_name;

如：type t_emp is table of emp%rowtype index by binary_integer;type t_no is table of emp.empno%type index by binary_integer;

b) 嵌套表：当使⽤嵌套表元素时，必须先使⽤期构造⽅法初始化嵌套表type type_name is table of element_type;identifier type_name;c) 变长数组

type type_name is varray(size_limit) of element_type [not null]; identifier type_name;d) 记录表：⽤于处理多⾏多列

type emp_table_type is table of emp%type index by binary_integer; emp_table emp_table_type;e) 多级集合

i. 多级varray(变长数组)

type a1_varray_type is varray(10) of int;

type na1_varray_type is varray(10) of a1_varray_type;

na1 na1_varray_type;ii. 嵌套表

type a1_table_type is table of int;

type nal_table_type is table of a1_table_type;na1 na1_table_type;iii. 多级索引表

type a1_table_type is table of int index by binary_integer;

type na1_table_type is table of a1_table_type index by binary_integer; na1 na1_table_type;

f) 集合⽅法：是Oracle所提供的⽤于操纵集合变量的内置函数或过程，其中exists、 count、limit、first、next、prior、next是函数，extend、trim、delete是过程。

i. exists：⽤于确定集合元素是否存在，如果成在则返回TRUE，否则返回FLASE使⽤⽅法：if ename_table.exists(1) then….ii. count：⽤于返回当前集合变量的元素总个数使⽤⽅法：ename_table.count;

iii. limit：⽤于返回集合元素的最⼤个数使⽤⽅法：ename_table.limitiv. first、last：⽤于返回集合变量第⼀/最后元素的下标

使⽤⽅法：ename_table.first

使⽤⽅法：ename_http://www.doczj.com/doc/d2d2bb66f5335a8102d220f8.html stv. prior、next：⽤于返回集合元素的前⼀个/后⼀个元素的下标使⽤⽅法：ename_table.prior使⽤⽅法：ename_table.next

vi. extend：⽤于扩展集合变量的尺⼨，并为它们增加元素。该⽅法只适⽤于嵌套表和VARRAY。⽅法有：EXTEND、EXTEND(n)、EXTEND(n,i)

使⽤⽅法：ename_table.extend：添加⼀个null元素ename_table.extend(n)：添加n个null元素

ename_table.extend(n,i): 添加n个元素(值与i元素相同)

vii. trim：⽤于从集合尾部删除元素；该⽅法只适⽤于嵌套表和VARRAY使⽤⽅法：ename_table.trim：从集合尾部删除⼀个元素ename_table.trim(n)：从集合尾部删除n个元素

viii. delete：⽤于删除集合元素；该⽅法只适⽤于嵌套表和索引表使⽤⽅法：ename_table.delete：删除集合变量的所有元素使⽤⽅法：ename_table.delete(n)：删除集合变量的第n个元素g) 集合赋值21．游标的使⽤1) 显⽰游标

a) 定义游标：cursor c1 is select_statement;b) 打开游标：open c1;

c) 提取数据：fetch c1 into variable1,variable2,...;--提取1条数据fetch c1 bulk collect into collect1,collect2,…; 提取全部数据

fetch c1 bulk collect into collect1,collect2,…[limit n];--⼀次提取n条数据d) 关闭游标：close c1;2) 显⽰游标属性

a) %isopen：⽤于确定游标是否已经打开，如果已经打开返回true,否则为false使⽤⽅法：if c1%isopen then…else….end if;

b) %found：⽤于检查是否从结果集中提取到了数据，提取到数据为true, 否则为false使⽤⽅法：if c1%found then… else exit; en d if;c) %notfound：与%found相反

⼀般使⽤⽅法：exit when c1%notfound;

d) %rowcount：⽤于返回到当前为⽌已经提取的实际⾏数3) 参数游标：cursor c1(parameter_name datatype…) isselect_statement;

4) 使⽤游标更新或删除数据

a) update table_name set column=.. where current of c1;b) delete table_name set column = .. where current of c1;5) 游标for循环：

a) for r1 in c1 loop statement;…. end loop;b) for r1 in (select ….) loop statement;… end loop;6) 使⽤游标变量

a) 定义REF CURSOR类型和游标变量

i. TYPE ref_type_name IS REF CURSOR [RETURN return_type];ii. cursor_variable ref_type_name; b) 打开游标i. OPEN cursor_variable FORselect_statement;c) 提取游标数据

i. FETCH cursor_variable INTO variable1,variable2…;

ii. FETCH cursor_variable BULK COLLECT INTO collect1…[LIMIT n];d) 关闭游标变量

i. CLOSE cursor_variable;

7) 使⽤CURSOR表达式：是Oracle9i新增的特性，⽤于返回嵌套游标a) 语法：CURSOR(subquery)

b) 例⼦：TYPE recursor IS REF CURSORCURSOR dept_cursor(v_deptno varchar2) is

select a.deptno,a.deptname,cursor(select emplno,emplnm from empwhere deptno = a.deptno)

from dept where a.deptno=v_deptno;empcur refcursor;

v_deptno dept.deptno%type;v_deptname dept.deptname%type;v_emplno emp.emplno%type;v_emplnm emp.emplnm%type;begin

OPEN dept_cursor(v_deptno);loop

fetch dept_cursor into v_deptno,v_deptname,empcur; exit when dept_cursor%NOTFOUND;dbms_output.put_line(v_detpno||v_deptname);loop

fetch empcur into v_emplno,v_emplnm;exit when empcur%notfound;

dbms_output.put_line(v_emplno||v_emplnm);end loop;end loop;close dept_cursor;end;

22．异常处理1) 预定义异常

a) access_not_null：ora-06530 对象未初始化

b) case_not_found：ora-06592 给定条件未包含在CASE语句中c) collection_is_null：ora-06531 没有初始化集合元素d) cursor_already_open：ora-06511 重新找开已经找开的游标e) dup_val_on_index：ora-00001 在唯⼀键值插⼊重复值f) invalid_curosr：ora-010001 试图在不合法的游标上执⾏操作g) invalid_number：ora-01722 不能有效地将字符转为数字h) no_data_found：ora-1403 执⾏select into 未返回⾏i) too_many_rows：ora-01422执⾏select into 返回多⾏数据j) zero_divide：ora-01476 使⽤数字除以0

k) subscript_beyond_count：ora-06533 使⽤嵌套表或VARRAY元素时下标出界l) subscript_outside_limit：ora-06532使⽤嵌套表或VARRAY元素时下标为负值m) value_error：ora-06502 变量长度不够2) ⾃定义异常

a) 定义：excep EXCEPTION;

b) 初始化：PRAGMA EXCEPTION_INIT(excep,-2291) –2291为Oracle 错误代码c) 代号中激活异常：RAISE excep;d) 处理异常：when excep then3) 使⽤例外函数

a) SQLCODE 返回Oracle错误号

b) SQLERRM 返回错误号对应的错误消息

c) raise_application_error：⽤于⾃定义错误消息(⽤于程序段中) i. 语法：raise_application_error(error_number,message[,(TRUE | FLASE)]);ii. error_number取值：-20000到-2099923．触发器1) DML触发器a) 语句触发器

i. 语法：CREATE OR REPLACE TRIGGER trigger_nametiming event1 [or event2 or event3]ON table_name[DECLARE变量定义]BEGINEND;

ii. timing：BEFORE或AFTER

iii. event：INSERT、UPDATE、DELETEiv. 使⽤条件谓词

1. INSERTING：当触发事件是INSERT操作时，返回值为TRUE2. UPDATING：当触发事件是UPDATE操作时，返回值为TRUE3. DELETING：当触发事件是DELETE操作时，返回值为TRUE4. ⽤法：case when inserting/updating/deleting then ….b) ⾏及触发器

i. 语法：CREATE OR REPLACE TRIGGER trigger_nametiming event1 [OR event2 OR event3]

ON table_name [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW [WHEN condition][DECLARE变量定义]BEGIN…END;

ii. timing：BEFORE/AFTER

iii. event：INSERT/UPDATE/DELETE

iv. REFERENCING⼦名⽤于指定引⽤新、旧数据的⽅式，默认为old修饰符引⽤旧数据，new修饰符引⽤新数据v. FOR EACH ROW表⽰建⽴⾏触发器

vi. WHEN⼦句⽤于指定触发条件,如：WHEN (old.emplno= '12345678')2) INSTEAD OF 触发器

a) 语法：CREATE OR REPLACE TRIGGER trigger_nameINSTEAD OF INSERT[ OR DELETE OR UPDATE] ON view_nameFOR EACH ROW[DECLARE变量定义;]BEGINEND;b) 注意事项

i. INSTEAD OF触发器只适⽤于视图

ii. 当基于视图建⽴触发器时，不能指定BEFORE和AFTER选项iii. 在建⽴INSTEAD OF触发器时，必须指定FOR EACH ROW 选项3) 系统事件触发器

i. 建⽴例程启动触发器(只能使⽤AFTER关键字)语法:CREATE OR REPLACE TRIGGER tr_startupAFTER STARTUP ON DATABASEBEGINEND;

ii. 建⽴例程关闭触发器(只能使⽤BEFORE关键字)

语法:CREATE OR REPLACE TRIGGER tr_shutdownBEFORE SHUTDOWN ON DATABASE

BEGINEND;

c) 建⽴⽤户登录和退出触发器

i. 登录触发器(只能使⽤AFTER关键字) 语法：CRETAE OR REPLACE TRIGGER tr_logonAFTER LOGON ON DATABASEBEGINEND;

ii. 退出触发器(只能使⽤BEFORE关键字) 语法：CREATE OR REPLACE TRIGGER tr_logoffBEFORE LOGOFF ON DATABASEBEGINEND;

4) 建⽴DDL触发器(必须使⽤AFTER关键字)语法：CREATE OR REPLACE TRIGGER tr_ddlAFTER DDL ON table_nameBEGINEND;5) 管理触发器

a) 显⽰触发器：select * from user_triggers;b) 禁⽌触发器：alter trigger trigger_name disable;c) 激活触发器：alter trigger trigger_name enable;

d) 禁⽌或激活表的所有触发器：alter table t_name disable/enable all triggers;e) 重新编译触发器：alter trigger trigger_name compile;f) 删除触发器：drop trigger trigger_name;24．动态SQL

1) 使⽤EXECUTE IMMEDIATE语句

a) 语法：EXECUTE IMMEDIATE dynamic_string[INTO {define_variable1[,define+variable]…|record}][USING [IN | OUT | IN OUT] bind_argument][,[IN | OUT | IN OUT] bind_argument1]…][(RETURNING | RETURN) INTObind_argument[,bind_argument]…]

b) 处理DDL操作: EXECUTE IMMEDIATE 'drop table test';

c) 处理DCL操作：EXECUTE IMMEDIATE 'grant create table to scott'd) 处理DML操作

i. EXECUTE IMMEDIATE 'update emp set sal=sal*1.1 where deptno=30';

ii. EXECUTE IMMEDIATE 'update emp set sal=sal*:rate where deptno=:dept' using &1,&2;

iii. EXECUTE IMMEDIATE 'update emp set sal = sal*:rate where deptno=:dept RETURNING sal INTO :salary' USING &1,&2RETURNING INTO salary;

e) 处理单⾏查询：EXECUTE IMMEDIATE 'select * from emp where emplno=:eno' into emp_record USING &1;2) 处理多⾏查询：使⽤OPEN-FOR，FETCH和CLOSE语句a) 定义游标变量：

TYPE refcursore IS REF CURSOR;cursor_variable refcursor;b) 打开游标变量：

OPEN cursor_variable FOR dynamic_string[USING bind_argument[,bing_argument1]…]c) 循环提取数据：

FETCH cursor_variable INTO {var1[,var2]… | record_var}; d) 关闭游标变量CLOSE cursor_variable;

3) 使⽤批量动态SQL：在动态SQL中使⽤BULK⼦句a) 在EXECUTE IMMEDIATE语句中使⽤动态BULK⼦句EXECUTE IMMEDIATE dynamic_string

[BULK COLLECT INTO define_variable[,define_variable…]][USING bind_argument[,bind_argument…]]

[{RETURNING | RETURN} BULK COLLECT INTO v_return[,v_retrun…]]b) 在FETCH语句中使⽤BULK⼦句

FETCH dynam ic_cursor BULK COLLECT INTO define_variable[…];c) 在FORALL语句中使⽤BULK⼦句FORALL index in lower bound..upper bound

EXECUTE IMMEDIATE dynamic_string | dml_statement[USING bind_argument…]

[{RETURNING | RETURN} BULK COLLECT INTO bind_argument…] 25．Oracle系统包1) DBMS_OUTPUTa) 启⽤

i. dbms_output.enable(buffer_size in integer default 20000);ii. set serveroutput on;b) 禁⽤

i. dbms_output.disable;c) PUT和PUT_LINE

i. PUT：所有信息显⽰在同⼀⾏ii. PUT_LINE：信息显⽰后，⾃动换⾏

d) NEW_LINE：⽤于在⾏的尾部追加⾏结束符，⼀般⽤PUT同时使⽤e) GET_LINE和GET_LINES

i. DBMS_OUTPUT.GET_LINE(line OUT VARCHAR2,status OUT INTEGER)：⽤于取缓冲区的单⾏信息ii. DBMS_OUTPUT.GET_LINES(lines OUT chararr,numlies IN OUT INTEGER)：⽤于取得缓冲区的多⾏信息2) DBMS_JOB

a) SUBMIT:⽤于建⽴⼀个新作业语法：

DBMS_JOB.SUBMIT(job OUT BINARY_INTEGER,what IN VARCHAR2,

next_date IN DATE DEFATULT SYSDATE,interval IN VARCHAR2 DEFAULT 'NULL',no_parse IN BOOLEAN DEFAULT FALSE,

instance IN BINARY_INTEGER DEFAULT any_instance,force IN DEFAULT FALSE);例⼦：

VAR jobno NUMBER;BEGIN

DBMS_JOB.SUBMI(:jobno,

'pro_hrs101d0_ins_hrs101t0',sysdate,‘sysdate+1’);

b) REMOVE:⽤于删除作业队列中的特定作业

语法：DBMS_JOB.REMOVE(jov IN BINARY_INTEGER);例⼦：DBMS_JOB.REMOVE(10); --删除JOB号为10的JOB；c) CHANGE：⽤于改变与作业相关的所有信息语法：

DBMS_JOB.CHANGE(job IN BINARY_INTEGER,what IN VARCHAR2,next_date IN DATE,interval IN VARCHAR2,

instance IN BINARY_INTEGER DEFAULT NULL,force IN BOOLEAN DEFAULT FALSE);

例⼦：execute dbms_job.change(2,null,null,'sysdate+7') –修改2号job 的间隔时间为7天d) WHAT：⽤于改变作业要执⾏的操作

语法：DBMS_JOB.WHAT(job IN BINARY_INTEGER,what IN VARCHAR2);e) NEXT_DATE：⽤于改变作业的下次运⾏⽇期

语法：DBMS_JOB.NEXT_DATE(job in BINARY_INTEGER,next_date IN DATE);f) INSTANCE：⽤于改变运⾏作业的例程

语法：DBMS_JOB.INSTANCE(job IN BINARY_INTEGER,INSTANCE IN BINARY_INTEGER,force IN BOOLEAN DEFAULT FALSE);g) INTERVAL：⽤于改变作业的运⾏时间间隔

语法：DBMS_JOB.INTERVAL(job IN BINARY_INTEGER,interval IN VARCHAR2);h) BROKEN：⽤于设置作业的中断标记.当中断了作业之后，作业将不会被运⾏语法：DBMS_JOB.BROKEN(job IN BINARY_INTEGER,broken IN BOOLEAN,

next_date IN DATE DEFAULT SYSDATE);i) RUN：⽤于运⾏已存在的作业

语法：DBMS_JOB.RUN(job in BINARY_INTEGER,force IN BOOLEAN DEFAULT FALSE);3) DBMS_PIPE:在同⼀例程的不同会话之间的管道通信

a) CREATE_PIPE：⽤于建⽴公⽤或私有管道.如果参数private为TRUE则为私有管道语法：

DBMS_PIPE.CREATE_TYPE(

pipename IN VARCHAR2,--指定管道的名称

maxpipesize IN INTEGER DEFAULT 8192,--指定管道消息的最⼤尺⼨private IN BOOLEAN DEFAULT TRUE) --TRUE为私有，FALSE为公⽤REURN INTEGER;--如果返回为0，则管道建⽴成功，否则为建⽴管道失败b) PACK_MESSAGE：⽤于将消息写⼊到本地消息缓冲区。

语法：DBMS_PIPE.PACK_MESSAGE(item IN VARCHAR2/NCHAR/NUMBER/DATE);DBMS_PIPE.PACK_MESSAGE_RAW(item IN RAW);DBMS_PIPE.PACK_MESSAGE_ROWID(item IN ROWID);c) SEND_MESSAGE：⽤于将本地消息缓冲区的内容发送到管道语法：DBMS_PIPE.SEND_MESSAGE(pipename IN VARCHAR2,

timeout IN INTEGER DEFAULT MAXWAIT,--指定发送消息的超时时间maxpipesize IN INTEGER DEFAULT 8192)

RETURN INTEGER;--如果返回为0，则发送成功，1为超时，3为出现中断d) RECEIVE_MESSAGE：⽤于接收管道消息，并将消息写⼊本地消息缓冲区语法：DBMS_PIPE.RECEIVE_MESSAGE(pipename IN VARHCAR2,

timeout IN INTEGER DEFAULT MAXWAIT)

RETURN INTEGER;--同SEND_MESSAGE,2表⽰本地缓冲区不能容纳管道消息e) NEXT_ITEM_TYPE：确定本地消息缓冲区下项的数据类型，在调⽤RECEIVE_MESSAGE之后调⽤该函数

语法：DBMS_PIPE.NEXT_ITEM_TYPE RETURN INTEGER;--如果返回为0，表⽰管道没有任何消息，6-NUMBER，9-ARCHAR2，11-ROWID，12-DATE，23-RAW

f) UNPACK_MESSAGE：⽤于将消息缓冲区的内容写⼊到变量中，在使⽤函数RECEVIE_MESSAGE接收管道消息之后使⽤取得消息缓冲区的消息,每次取⼀条消息

语法：DBMS_PIPE.UNPACK_MESSAGE(item OUT VARCHAR2/NCHAR/NUMBER/DATE);DBMS_PIPE.UNPACK_MESSAGE_RAW(item OUT RAW);DBMS_PIPE.UNPACK_MESSAGE_ROWID(item OUT ROWID);