Oracle_SQL学习笔记

SQL、PL/SQL学习笔记1．SQL并行查询alter session enable parallel dmlexecute immediate 'alter session enable parallel dml'; --修改会话并行DMLselect /*+parallel(a,4)*/ * from table_name aselect /*+parallel(a,8)*/ * from table_name aselect /*+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 tinsert /*+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 partition mk_user_mon_'||trim(iv_month) 删除指定表分区数据3．minus(差集)与intersect(交集)minus 指令是运用在两个SQL 语句上。

它先找出第一个SQL 语句所产生的结果，然后看这些结果有没有在第二个SQL 语句的结果中,如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现; 如果第二个SQL 语句所产生的结果并没有存在于第一个SQL 语句所产生的结果内，那这笔资料就被抛弃。

intersect 指令是运用在两个SQL语句上，如果两个SQL语句的记录完全相同则显示相应记录，否则将不在结果中出现4．Order by 中的 nulls lastorder 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 ,否则返回a6．怎样确保最终用户在数据库中只有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 bwhere x=:x’ using x;3)pl/sql中的批量绑定变量（forall）a)forall i in 1..x.countdml;--只能有一条语句(update,insert,delete)sql%bulk_rowcount(i):用于取得在执行批量绑定操作时的第i个元素作用的行数b)bulk collect 子句：用于取得批量数据，它只适用于select into、fetch into和DML返回子句语法：…BULK COLLECT INTO collection_name…i.select 中使用bulk collectdeclaretype emp_table_type is table emp%rowtype index by binary_integer;emp_table emp_table_type;beginselect * 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.countupdate 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 collectdeclaretype ename_table_type is table of emp.ename%type;ename_table ename_table_type;begindelete emp where deptno=&noreturning 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 tableb．共享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 modeb．通过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: 资源正忙，要求指定NOW AIT；如果不选择nowait选项则会一直处理等待状态。

wait [n]：等待n秒，如果另有会话正在修改该记录会报告错误：ORA-30006: 资源已被占用;执行操作时出现W AIT 超时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 Processing3)联机分析处理(OLAP)：On-Line Analytical Processing也称为在线分析处理。