PL/SQL开发五年工作经验精典实例(转)

. minus(差集)与intersect(交集) minus指令是运用在两个SQL语句上。它先找出第一个SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中,如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现; 如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃。 intersect指令是运用在两个SQL语句上，如果两个SQL语句的记录完全相同则显示相应记录，否则将不在结果中出现。 ------------------------------------------------------------------------------------------- 2. 表空间(tablespace)、段(segment)、区段(extent)、块(block)的关系 1)表空间：是Oracle中的一个逻辑存储容器，位于存储层次体系的顶层，包含一个或多个数据文件。 2)段：占用存储空间的数据为对象，如表、索引、回滚段等；段由一个或多个区段组成。 3)区段：是文件中一个逻辑上连续分配的空间；区段由块组成。 4)块：是Oracle中最小的空间分配单位；数据行、索引条目或临时排序结果就存储在块中；Oracle中常见的块大小：2K、4K、8K、16K(最大不能超过32K)。 5)它们之间的关系：数据库由一个或多个表空间组成，表空间由一个或多个数据文件组成，表空间包含段，段由一个或多个区段组成，区段则由连续的块组成。 ------------------------------------------------------------------------------------------- 3. ETL(Extraction-Transformation-Loading)名称解释 ETL：抽取(Extraction)、转换(Transformation)、载入(Loading) ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。 oracle中的裸设备指: 裸设备就是绕过文件系统直接访问的储存空间. ------------------------------------------------------------------------------------------- 4. 数据库分析技术语句分析表： analyze table UNIT_PRE_CLAIM compute statistics; 分析索引：analyze index PK_UNIT_PRE_CLAIM_RELA_ID compute statistics; 分析列： analyze table zl_yhjbqk compute statistics for columns hbs_bh; 分析索引列：analyze table zl_yhjbqk compute statistics for all indexed columns; ------------------------------------------------------------------------------------------- 5. ORACLE的优化器共有3种: a. RULE (基于规则的优化器) 。总是使用索引 。总是从驱动表开始（from子句最右边的表） 。只有在不可避免的情况下，才使用全表扫描 b. COST (基于成本的优化器) 。需要表、索引的统计资料 Analyze table customer compute statistics; Analyze table customer estimate statistics sample 5000 rows; 。表中设置并行度、表分区 c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS. 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer), 你必须经常运行analyze命令,以增加数据库中的对象统计信息(object statistics)的准确性.如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关,如果table已经被analyze过, 优化器模式将自动成为CBO, 反之,数据库将采用RULE形式的优化器. 缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan), 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.注意: 在排序合并连接中不使用索引,如果查询返回两个表中大部分的数据快,那么CBO会认为全表扫描比索引扫描执行更快。 ------------------------------------------------------------------------------------------- 6. Hint的常用用法(注意/*和+之间不能有空格) 1)/*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 2)/*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化. 3)/*+CHOOSE*/ 表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量; 如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法; 4)/*+ RULE*/ 表明对语句块选择基于规则的优化方法. 5)/*+ROWID(TABLE)*/ 提示明确表明对指定表根据ROWID进行访问. 例如: SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' 6)/*+INDEX(TABLE INDEX_NAME)*/ 表明对表选择索引的扫描方法. 7)/*+INDEX_JOIN(TABLE INDEX_NAME)*/ 提示明确命令优化器使用索引作为访问路径. 8)/*+APPEND*/ 直接插入到表的最后,可以提高速度. 例如: insert /*+append*/ into test1 select * from test4; 如用hint指定使用某个索引 select /*+ index(表名) */ col1 from 表名; select /*+ index(表名 索引名) */ col1 from 表名; select /*+ index(a 索引名) */ col1 from 表名 a; 其中 TABLE_NAME是必须要写的，且如果在查询中使用了表的别名，在hint也要用表的别名来代替表名; INDEX_NAME可以不必写，Oracle会根据统计值选一个索引; ------------------------------------------------------------------------------------------- 7. 位图索引位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段。位图索引在实际密集型OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量的删除、修改、新建操作，ORACLE每次进行操作都会对要操作的数据块加锁，所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP（数据分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。 ------------------------------------------------------------------------------------------- 8. 外部连接说明（左、右连接） 在where条件子句的等式谓词放置一个(+)来实现，例如：该语句返回所有emp表的记录； select a.ename,b.comm from emp a,bonus b where a.ename=b.ename(+); 在9i以前可以这么写: select a.id,a.name,b.address from a,b where a.id=b.id(+) --左联 select a.id,a.name,b.address from a,b where a.id(+)=b.id --右联外联 Select a.Id, a.Name, b.Address From a, b Where a.Id = b.Id(+) Union Select b.Id, '' Name, b.Address From b Where Not Exists (Select * From a Where a.Id = b.Id); ------------------------------------------------------------------------------------------- 9. Oracle为什么不使用索引 1)看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。 2)看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。 3)索引列如果是函数的参数。则索引在查询时用不上。 4)是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致上一种现象的发生。 5)是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句analyze table xxxx compute statistics for all indexes; ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。 6)如果索引列值是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空。 7)看是否有用到并行查询（PQO）。并行查询将不会用到索引。 8)如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加hint的方式强制ORACLE使用最优的“执行计划”。hint采用注释的方式，有行注释和段注释两种方式。如我们想要用到A表的IND_COL1索引的话，可采用以下方式：“SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 = XXX;" ------------------------------------------------------------------------------------------- 10. 如何屏蔽索引语句的执行计划中有不良索引时，可以人为地屏蔽该索引，方法：数值型：在索引字段上加0， 例如 select * from emp where emp_no+0 = v_emp_no; 字符型：在索引字段上加''，例如 select * from tg_cdr01 where msisdn||'' =v_msisdn; 怎么避免使用特定索引: 在很多时候，Oracle会错误的使用索引而导致效率的明显下降，我们可以使用一点点技巧而避免,如表test,有字段a,b,c,d，在a,b,c上建立联合索引inx_a(a,b,c)，在b上单独建立了一个索引Inx_b(b)。在正常情况下，where a=? and b=? and c=?会用到索引inx_a，where b=?会用到索引inx_b 但是，where a=? and b=? and c=? group by b会用到哪个索引呢？在分析数据不正确（很长时间没有分析）或根本没有分析数据的情况下，oracle往往会使用索引inx_b。通过执行计划的分析，这个索引的使用，将大大耗费查询时间。当然，我们可以通过如下的技巧避免使用inx_b，而使用inx_a。 where a=? and b=? and c=? group by b||'' --如果b是字符 where a=? and b=? and c=? group by b+0 --如果b是数字通过这样简单的改变，往往可以是查询时间提交很多倍. 当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法： select /*+ no_index(t,inx_b) */ * from test t where a=? and b=? and c=? group by b ------------------------------------------------------------------------------------------- 11. 如获得完整的时间格式 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select to_char(to_date('2008-06-24','yyyy-mm-dd'),'day') from dual; --可以转换成中文星期几 ------------------------------------------------------------------------------------------- 12. 随机抽取前N条记录的问题 select * from (select * from tablename order by sys_guid()) where rownum < N; 抽取从N行到M行的记录，如从20行到30行的记录 select * from (select rownum id,t.* from table where …… and rownum <= 30) where id > 20; 怎么查询(抽取)重复记录 select * from table t1 where t1.rowed !=(select max(rowed) from table t2 where t1.id=t2.id) 如果想删除重复记录，可以把第一个语句的select替换为delete Delete From serv_jz_sz a Where a.Rowid <> (Select Max(Rowid) From serv_jz_sz b Where a.Acc_Nbr = b.Acc_Nbr); ------------------------------------------------------------------------------------------- 13. 怎么样在ORACLE中定时运行存储过程可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业： VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;', SYSDATE, 'SYSDATE + 1'); commit; END; 之后，就可以用以下语句查询已经提交的作业 select * from user_jobs; ------------------------------------------------------------------------------------------- 14. 如果存在就更新，不存在就插入数据，可以用一个语句实现吗? 记住，一定要用MERGE INTO来实现哦，具体可参考我赋的例子! 类似的还有，实现一条记录根据条件多表插入。可以通过Insert all语句完成，仅仅是一个语句，如： INSERT ALL WHEN (id=1) THEN INTO table_1 (id, name) values(id,name) WHEN (id=2) THEN INTO table_2 (id, name) values(id,name) ELSE INTO table_other (id, name) values(id, name) SELECT id,name FROM a; 如果没有条件的话，则完成每个表的插入，如 INSERT ALL INTO table_1 (id, name) values(id,name) INTO table_2 (id, name) values(id,name) INTO table_other (id, name) values(id, name) SELECT id,name FROM a; ------------------------------------------------------------------------------------------- 15. 固定列数的行列转换 student subject grade -------- ------- ----- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 转换为: 语文 数学 英语 student1 80 70 60 student2 90 80 100 …… …… …… …… …… 语句如下： Select Student, Sum(Decode(Subject, '语文', Grade, Null)) "语文", Sum(Decode(Subject, '数学', Grade, Null)) "数学", Sum(Decode(Subject, '英语', Grade, Null)) "英语" From Table Group By Student ------------------------------------------------------------------------------------------- 16. 怎么样实现分组取前N条记录 (如获取每个部门薪水前三名的员工) Select * From (Select Depno, Ename, Sal, Row_Number() Over(Partition By Depno Order By Sal Desc) Rn From Emp) Where Rn <= 3