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，否则返回 1oracle 时间转换
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 unt
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 unt loop
dbms_output.put_line(emp_table(i).emp);
end loop;
forall i unt
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 ame%type;
ename_table ename_table_type;
begin
delete emp where deptno=&no
returning ename bulk collect into ename_table;
for i unt 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也称为

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。