搜索
张兴东279版权所有常问的面试题汇总：1、怎么在存储过程中使用临时表？在Oracle中，临时表分为SESSION、TRANSACTION两种，SESSION级的临时表数据在整个SESSION都存在，直到结束此次SESSION；而TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。1、ONCOMMITDELETEROWS说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）2、ONCOMMITPRESERVEROWS说明临时表是会话指定，当中断会话时ORACLE将截断表。3、临时表（无论会话级还是事务级）中的数据都是会话隔离的，不同session之间不会共享数据。4、在存储中使用事务级临时表时，注意commit前删除掉本事务的数据，否则可能会出现数据不断增加的情况（原因尚未搞明白）。5、两种临时表的语法：createglobaltemporarytable临时表名oncommitpreserve|deleterows;用preserve时就是SESSION级的临时表，用delete就是TRANSACTION级的临时表。6、特性和性能(与普通表和视图的比较)临时表只在当前连接内有效;临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用;数据处理比较复杂的时候时表快,反之视图快点;在仅仅查询数据的时候建议用游标:opencursorfor'sqlclause';2、口述一下存储过程的写法基本结构+存储名+输出变量+is+begin开始+操作语句+end结束CREATEORREPLACEPROCEDUREfun_xsreMobile(paR_intInterID_cnumber,paR_lngMobile_cnumber,Date_cnumber)isbeginselect*from(SelectID,CpID,MobState,Settled,SendDate,ReceCount,IsLocked,GateID,ExtData,ReceTsFromtb_prd_ofrwhereInterID=paR_intInterID_candMobile=paR_lngMobile_candSendDate=Date_corderbyIDdesc)whererownum=1;end;3、你了解oracle表分区吗？它有什么优缺点分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在张兴东279版权所有物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。表分区的具体作用Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用SQLDML命令访问分区后的表时，无需任何修改。什么时候使用分区表：1、表的大小超过2GB。2、表中包含历史数据，新的数据被增加都新的分区中。必须要建表之前就创建表分区有以下优点：1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。缺点：分区表相关：已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能。4、oracle表分区的分区类型有哪几种？它们的操作方法你知道吗？..Oracle分区表分为四类：范围分区表；列表分区表；哈希分区表；组合分区表5、谈谈你对执行计划的理解，你主要看执行计划的哪部分？6、你对sql语句优化有何看法，能说出几种优化方法吗？说到优化，主讲会索引和分区，引导到讲索引上去，索引要好好熟悉和使用。索引优化规则：1.like件中不要以通配符(WILDCARD)开始,否则索引将不被采用.2.避免在索引列上使用计算或改变索引列的类型或使用‘!=’及3.避免在索引列上使用NOT.4.用=替代.高效:SELECT*FROMEMPWHEREDEPTNO=4低效:SELECT*FROMEMPWHEREDEPTNO3张兴东279版权所有两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.5.用UNION替换OR(适用于索引列)通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择O规则R而降低.6.避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．(建议：可以给null值的字段设置一个默认值))7.如果索引是建立在多个列上,索引时段需要放在where条件的第一个条件(Oracle8i之前),Oracle8i之后允许跳跃式索引.8.(可能的话)用UNION-ALL替换UNION.UNION-ALL就是做简单的合并，不会进行排序，UNION先做简单的合并，然后做进行排序，最后去除重复的记录。9.避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎.执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUPBY子句,GROUPBY会触发嵌入排序(NESTEDSORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写.7、oracle优化器内部处理的表连接方式知道吗？有哪几种？表的连接是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个，那么连接的顺序如何呢？ORACLE首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。嵌套循环连接（NESTEDLOOPJOIN）群集连接(CLUSTERJOIN)张兴东279版权所有排序合并连接（SORTMERGEJOIN）笛卡尔连接(CARTESIANJOIN)哈希连接(HASHJOIN)。索引连接(INDEXJOIN)。8、会使用开窗函数吗？说一下你对开窗函数、聚合函数、分析函数的理解分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变9、游标的属性有哪几种？显式游标和隐式游标的使用方式分别是什么？属性SQL%ISOPEN返回的类型为布尔型,判断游标是否被打开，如果打开%ISOPEN等于true,否则等于false,即执行过程中为真,结束后为假。SQL%NOTFOUND返回值为布尔型,判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false,即与%FOUND属性返回值相反。SQL%FOUND返回值的类型为布尔型，值为TRUE代表插入删除更新或单行查询操作成功。SQL%ROWCOUNT返回值类型为整型，返回当前位置为止游标读取的记录行数,即成功执行的数据行数。显示游标：声明游标，打开游标，提取数据，关闭游标隐式游标,当查询开始时隐式游标打开,查询结束时隐式游标自动关闭10、plsql块怎么捕捉到异常？你能说出几个常见的预定义异常吗?定义异常抛出异常捕获及处理异常Oracle中出现错误的情形通常分为编译时错误（compile-timeerror）和运行时错误(run-timeerror)，异常是在PL/SQL执行过程中出现的警告或错误。异常名称ORACODESQLCODE触发时机LOGIN_DENIED01017-1017非法用户名称或者密码登录时候NO_DATA_FOUND01403100SELECTINTO没有返回行；或者代码指向嵌套表中被删除的元素；或者代码指向索引张兴东279版权所有表（数组）的未初始化的元素。NOT_LOGGED_ON01012没有登录，却意图执行一些调用PROGRAM_ERROR06501PL/SQL存在内部的错误ROWTYPE_MISMATCH06504发生在一个游标给另外一个游标赋值的情况下，尤其是调用含有游标参数的子过程时候。如果二者返回类型不一致，会出现。SELF_IS_NULL30625试图调用一个没有初始化的对象的方法成员，因为关键字SELF表示的是对象的自生（是内建参数），在调用时候总是第一个传递给方法成员。STORAGE_ERROR06500PL/SQL耗尽了内存，或者内存发生泄露。SUBSCRIPT_BEYOND_COUNT06533简单而言，就是访问数组对象（或者嵌套表）时候，越界了。SUBSCRIPT_OUTSIDE_LIMIT06532和上面一个类似，不通的是这里的索引号是非法的，例如-1SYS_INVALID_ROWID01410把一个不符合ROWID格式的字符串转换为ROWID时候发生的异常。TIMEOUT_ON_RESOURCE00051等待一个资源的时候，发生超时。TOO_MANY_ROWS01422SELECTINTO语句返回多余一行结果VALUE_ERROR06502数字，转换，截取，或者大小约束发生的错误。例如用一个变量的长度比列的长度小，如果要保存列值就会发生异常。在过程语句中，如果字符串转换为数字失败，会触发这个。在SQL语句中，则是触发INVALID_NUMBER异常。ZERO_DIVIDE01476试图除以0.ACCESS_INTO_NULL06530试图访问一个没有初始化对象的属性，和self_is_null类似CASE_NOT_FOUND06592“情况没有发现”在一个casewhen子句中，没有一个选择是满足的，但是又不存在else子句。COLLECTION_IS_NULL06531试图实用exists之外的集合方法访问未初始化的数组或者嵌套表，或者是试图给它们赋值。CURSOR_ALREADY_OPEN06511试图重新打开已经打开的游标。DUP_VAL_ON_INDEX00001试图在唯一索引列上存入重复的值。INVALID_CURSOR01001试图对游标做一些非法的操作，例如试图张兴东279版权所有关闭一个本来就没有打开的游标。INVALID_NUMBER01722和value_error类似，不过只适合