日志

SQL、PL/SQL学习笔记转载

已有 985 次阅读2007/7/4 17:58 |个人分类:plsql

SQL、PL/SQL学习笔记

1．SQL并行查询

alter session enable parallel dml

execute immediate 'alter session enable parallel dml'; --修改会话并行DML

select /*+parallel(a,4)*/ * from table_name a

select /*+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 c

insert /*+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 dml

2．删除表分区数据

alter table masamk.tb_mk_sc_user_mon truncate partition mk_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,否则返回 a

nvl2(a,1,0) 如果 a 为 null 返回 0,否则返回 1

nullif(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 references resources,….);

8．绑定变量的使用

1) sql中的绑定变量

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

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

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 loop

dbms_output.put_line(emp_table(i).emp);

end loop;

forall i in 1..emp.table.count

update sal set deptno = emp_table(i).deptno

where 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 loop

dbms_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-00054: 资源正忙，要求指定 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 System

2) 联机事务处理(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 a block.)

15) Physical Reads：从磁盘读到Buffer Cache数据块数量(Total number of data blocks read from disk. This number equals the 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 quite resource intensive. Try increasing the size of the initialization parameter SORT_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用户资料表主键compute statistics

分析列：analyze table zl_yhjbqk compute statistics for columns hbs_bh

分析索引列：analyze table zl_yhjbqk compute statistics 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语句，创建一个执行计划，其中包含可以同时完成的多个(子)执行计划

18．insert语句的用法

1) insert into 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 is record(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 by binary_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.limit

iv. first、last：用于返回集合变量第一/最后元素的下标

使用方法：ename_table.first

使用方法：ename_table.last

v. 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; end if;

c) %notfound：与%found相反

一般使用方法：exit when c1%notfound;

d) %rowcount：用于返回到当前为止已经提取的实际行数

3) 参数游标：cursor c1(parameter_name datatype…) is select_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 FOR select_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 CURSOR

CURSOR dept_cursor(v_deptno varchar2) is

select a.deptno,a.deptname,cursor(select emplno,emplnm from emp

where 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;

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 then

3) 使用例外函数

a) SQLCODE 返回Oracle错误号

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

c) raise_application_error：用于自定义错误消息(用于程序段中)

i. 语法：raise_application_error(error_number,message[,(TRUE | FLASE)]);

ii. error_number取值：-20000到-20999

23．触发器

1) DML触发器

a) 语句触发器

i. 语法：CREATE OR REPLACE TRIGGER trigger_name

timing event1 [or event2 or event3]

ON table_name

[DECLARE

变量定义]

BEGIN

END;

ii. timing：BEFORE或AFTER

iii. event：INSERT、UPDATE、DELETE

iv. 使用条件谓词

1. INSERTING：当触发事件是INSERT操作时，返回值为TRUE

2. UPDATING：当触发事件是UPDATE操作时，返回值为TRUE

3. DELETING：当触发事件是DELETE操作时，返回值为TRUE

4. 用法：case when inserting/updating/deleting then ….

b) 行及触发器

i. 语法：CREATE OR REPLACE TRIGGER trigger_name

timing 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_name

INSTEAD OF INSERT[ OR DELETE OR UPDATE] ON view_name

FOR EACH ROW

[DECLARE

变量定义;]

BEGIN

END;

b) 注意事项

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

ii. 当基于视图建立触发器时，不能指定BEFORE和AFTER选项

iii. 在建立INSTEAD OF触发器时，必须指定FOR EACH ROW 选项

3) 系统事件触发器

a) 常用事件发生函数

函数名称	函数描述
ora_client_ip_address	用于返回客户端的IP地址
ora_database_name	用于返回当前数据库名
ora_des_encrypted_password	用于返回DES加密扣的用户口令
ora_dict_obj_name	用于返回DDL操作对应的数据为对象名
ora_dict_obj_name_list	用于返回在事件中被修改的对象名列表
ora_dict_obj_owner	用于返回DDL操作所对应对象的所有者
ora_dict_obj_owner_list(owner_list OUT ora_name_list_t)	用于返回在事件中被修改的对象的所有者列表
ora_dict_obj_type	返回DDL操作所对应的数据库对象类型
ora_grantee	用于返回授权事件的授权者
ora_instance_num	用于返回例程号
ora_is_alter_column(column in varchar2)	用于检测特定列是否被修改
ora_is_creating_nested_table	用于检测是否正在建立嵌套表
ora_is_drop_column(column in varchar2)	用于检测特定列是否被删除
ora_is_serverror(error_number)	用于检测是否返回了特定Oracle错误
ora_login_user	用于返回登录用户名
ora_sysevent	用于返回触发器的系统事件名

b) 建立例程启动和关闭触发器

i. 建立例程启动触发器(只能使用AFTER关键字)

语法:CREATE OR REPLACE TRIGGER tr_startup

AFTER STARTUP ON DATABASE

BEGIN

END;

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

语法:CREATE OR REPLACE TRIGGER tr_shutdown

BEFORE SHUTDOWN ON DATABASE

BEGIN

END;

c) 建立用户登录和退出触发器

i. 登录触发器(只能使用AFTER关键字)

语法：CRETAE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

END;

ii. 退出触发器(只能使用BEFORE关键字)

语法：CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

END;

4) 建立DDL触发器(必须使用AFTER关键字)

语法：CREATE OR REPLACE TRIGGER tr_ddl

AFTER DDL ON table_name

BEGIN

END;

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) INTO bind_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,&2 RETURNING 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 dynamic_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_OUTPUT

a) 启用

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);

g) REMOVE_PIPE：用于删除已经建立的管道

语法：DBMS_PIPE.REMOVE_PIPE(pipename IN VARCHAR2) RETURN INTEGER;

h) PURGE：用于清除管道中的内容

语法：DBMS_PIPE.PURGE(pipename IN VARCHAR2);

i) RESET_BUFFER：用于复位管道缓冲区。在使用新管道之前应该复位管道缓冲区

语法：DBMS_PIPE.REST_BUFFER;

j) UNIQUE_SESSION_NAME：用于为特定会话返回唯一的名称，名称最大长度为30字节

语法：DBMS_PIPE.UNIQUE_SESSION_NAME();

4) DBMS_ALERT：适用于生成并传递数据库预警信息。合理使用包和数据库触发器，可以使得在和生特定数据库事件时将信息传递给应用程序。

a) REGISTER：注册预警事件

语法：DBMS_ALERT.REGISTER(name IN VARCHAR2)

b) REMOVE：删除会话不需要的预警事件

语法：DBMS_ALERT.REMOVE(name IN VARCHAR2);

c) REMOVEALL：删除当前会话所有已经注册的预警事件

语法：DBMS_ALERT.REMOVEALL();

d) SET_DEFAULTS：设置检测预警事件的时间间隔，默认时间间隔为5秒

语法：DBMS_ALERT.SET_DEFAULTS(sensitivity IN NUMBER);

e) SIGNAL：指定预警所对应的预警消息事件

语法：DBMS_ALERT.SIGNAL(name IN VARCHAR2,message IN VARCHAR2);

f) WAITANY：等待当前会话的任何预警事件，并且在预警事件发生时输出相应信息

语法：DBMS_ALERT.WAITANY(

name OUT VARCHAR2,

message OUT VARCHAR2,

status OUT INTEGER,--返回0表示发生了预警事件，1表示超时

timeout IN NUMBER DEFAULT MAXWAIT);--设置等待预警事的超时时间

g) WAITONE：等待当前会话的特定预警事件，并且在发生预警事件时输出预警消息

语法：DBMS_ALERT.WAITONE(

name OUT VARCHAR2,

message OUT VARCHAR2,

status OUT INTEGER,--返回0表示发生了预警事件，1表示超时

timeout IN NUMBER DEFAULT MAXWAIT);--设置等待预警事的超时时间

5) DBMS_SESSION:提供使用PL/SQL实现ALTER SESSION命令,SET ROLE命令和其他会话住处的方法

a) SET_INENTIFIER:该过程用于设置会话的客户ID号

语法:DBMS_SESSION.SET_IDENTIFIER(client_id VARCHAR2);

b) SET_CONTEXT:该过程用于设置应用上下文属性

语法:DMBS_SESSION.SET_CONTEXT(

namespace VARCHAR2,--指定应用上下文的命名空间

attribute VARCHAR2,--指定应用上下文的属性

value varchar2);--指定属性值

DMBS_SESSION.SET_CONTEXT(

namespace VARCHAR2,

attribute VARCHAR2,

value varchar2,

username VARCHAR2,--指定应用上下文的用户名属性

client_id VARCHAR2);

c) CLEAR_CONTEXT:用于清除应用上下文的属性设置

d) CLEAR_IDENTIFIER:删除会话的set_client_id

e) SET_ROLE:激活或禁止会话角色，与SQL语句的SET ROLE作用相同

f) SET_SQL_TRACE:激活或禁止当前会话的SQL跟踪,同ALTER SESSION SET SQL_TRACE

g) SET_NLS:设置NLS特征,同ALTER SESSION SET <nls_param>=<value>

h) CLOSE_DATABASE_LINK:关闭已经打开的远程数据库链

i) SET_PACKAGE:复位当前会话的所有包,并且会释放包状态

j) MODIFY_PACKAGE_STATE:用于修改当前会话的PL/SQL程序单元的状态

k) UNIQUE_SESSION_ID:返回当前会话的唯一标识符

l) IS_ROLE_ENABLED:确定当前会话是否激活了特定角色

m) IS_SESSION_ALIVE:确定特定会话是否处于活动状态

n) SET_CLOSE_CACHED_OPEN_CURSORS:打开或关闭close_cached_open_cursors

o) FREE_UNUSED_USER_MEMORY:在执行了大内存操作之后回收未用内存

p) LIST_CONTEXT:返回当前会话的命名空间和上下文列表

q) SWITCH_CURRENT_CONSUMER_GROUP:改变当前会话的资源使用组

6) DBMS_RLS：用于实现清细访问控制(VPD：虚拟专用数据库)，并且精细访问控制是通过在SQL语句中动态增加谓词(where子句)来实现。

a) ADD_POLICY：用于为表、视图或同义词增加一个安全策略,当执行该操作结束时会自动提交事务

语法：

DBMS_RLS.ADD_POLICY(

object_schema IN VARCHAR2 NULL,--指定包含表、视图或同义词的方案/用户

object_name IN VARCHAR2，--指定要增加安全策略的表、视图或同义词

policy_name IN VARCHAR2,--指定要增加的安全策略名称

function_schema IN VARCHAR2 NULL,--指定策略函数的所在方案/用户

policy_function IN VARCHAR2,-- 指定生成安全策略谓词的函数名

statement_types IN VARCHAR2 NULL,--指定使用安全策略的SQL语句(默认值NULL表于适用于SELECT、INSERT、UDPATE以及DELETE语句)

update_check IN BOOLEAN FALSE,--指定在执行INSERT或UPDATE时是否检查安全策略

enable IN BOOLEAN TRUE,--指定是否激活安全策略

static_policy IN BOOLEAN FALSE);--指定是否要生成静态的安全策略

b) DROP_POLICY：用于删除定义在特定表、视图或同义词上的安全策略

语法：DBMS_RLS.DROP_POLICY(

object_schema IN VARCHAR2 NULL,

object_name IN VARCHAR2,

policy_name IN VARCHAR2);

c) REFRESH_POLICY：用于刷新与安全策略修改相关的所有SQL语句，并使得Oracle重新解析相关SQL语句

语法：DBMS_RLS.REFRESH_POLICY(

object_schema IN VARCHAR2 NULL,

收藏邀请使用道具 | 编辑| 删除|

的个人空间 https://www.erp100.com/?0 [收藏] [复制] [RSS]

日志

SQL、PL/SQL学习笔记转载

1．SQL并行查询

2．删除表分区数据

3．minus(差集)与intersect(交集)

4．Order by 中的 nulls last

5．nvl的几个不同函数

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

7．表的字段参照另外表的字段

8．绑定变量的使用

9．在SQL中锁定记录

10．数据库与实例的关系

11．Oralce数据库所包含的文件类型

12．表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系

13．名称解释

14．数据库分析技术

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

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

17．Oracle数据库从属进程

18．insert语句的用法

19．commit、rollback、savepoint的使用

20．PL/SQL中的复合数据类型

21．游标的使用

22．异常处理

23．触发器

24．动态SQL

25．Oracle系统包

评论 (0 个评论)

的个人空间 https://www.erp100.com/?0 [收藏] [复制] [RSS]

日志

SQL、PL/SQL学习笔记 转载

1．SQL并行查询

2．删除表分区数据

3．minus(差集)与intersect(交集)

4．Order by 中的 nulls last

5．nvl的几个不同函数

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

7．表的字段参照另外表的字段

8．绑定变量的使用

9．在SQL中锁定记录

10．数据库与实例的关系

11．Oralce数据库所包含的文件类型

12．表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系

13．名称解释

14．数据库分析技术

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

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

17．Oracle数据库从属进程

18．insert语句的用法

19．commit、rollback、savepoint的使用

20．PL/SQL中的复合数据类型

21．游标的使用

22．异常处理

23．触发器

24．动态SQL

25．Oracle系统包

评论 (0 个评论)

SQL、PL/SQL学习笔记转载