oracle forall sql%bulkrowcount,ORACLE_SQL学习笔记

ORACLE_SQL学习笔记

更新时间：2017/2/22 19:44:00  浏览量：584  手机版

ORACLE_SQL学习笔记

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