简答题1. 动态SQL是什么?
在PL/SQL开发过程中,使用SQL或PL/SQL可以实现大部分的需求,但是,在某些特殊的情况下,在PL/SQL中使用标准的SQL语句或DML语句不能实现自己的需求,例如需要动态建表或执行某个不确定的操作的时候,就需要动态执行,此外,DDL语句及系统控制语句也不能在PL/SQL中直接使用,这就需要使用动态SQL来实现。因此,在Oracle数据库开发PL/SQL块中,可以把SQL分为静态SQL和动态SQL。所谓静态SQL指的是在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象。动态SQL是指在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。动态SQL允许在SQL客户模块或嵌入式宿主程序的执行过程中执行动态生成的SQL语句,动态SQL语句在程序编译时尚未确定。其中,有些部分需要程序在执行过程中临时生成的SQL语句,SQL标准引入动态SQL的原因是由于静态SQL不能提供足够的编程灵活性。
动态SQL是使用EXECUTE IMMEDIATE语句来实现的。
2. 如何使用批量动态SQL(FORALL及BULK子句的使用)?
批量动态SQL即在动态SQL中使用BULK子句,或使用游标变量时在FETCH中使用BULK,或在FORALL子句中使用BULK子句来实现。
如果一个循环内执行了INSERT、DELETE或UPDATE等语句引用了集合元素,那么可以将其移动到一个FORALL子句中。如果SELECT INTO、FETCH INTO或RETURNING INTO子句引用了一个集合,那么应该使用BULK COLLECT子句进行合并,从而来提高程序的性能。
(1)动态SQL中使用BULK子句的语法
使用BULK COLLECT INTO子句处理动态SQL中的多行查询可以加快处理速度,从而提高应用程序的性能。当使用BULK子句时,集合类型可以是PL/SQL所支持的索引表、嵌套表和VARRY,但集合元素必须使用SQL数据类型。常用的三种语句支持BULK子句,分别为EXECUTEIMMEDIATE,FETCH和FORALL。
(2)使用EXECUTE IMMEDIATE结合BULK子句处理DML语句返回子句下面的例子,首先定义了两个索引表类型以及其变量,接下来使用动态SQL语句来更新T_20170104_LHR的薪水,使用EXECUTE IMMEDIATE配合BULKCOLLECT INTO来处理结果集。
运行以上程序输出结果如下:
(3)使用EXECUTE IMMEDIATE结合BULK子句处理多行查询下面示例中,与前一个示例相同,只不过其动态SQL由查询语句组成,且返回多个结果集,同样使用了BULK COLLECT INTO来传递结果。
运行以上程序输出结果如下:
(4)使用FETCH子句结合BULK子句处理多行结果集 下面的示例中首先定义了游标类型、游标变量以及复合类型和复合变量,接下来从动态SQL中OPEN游标,然后使用FETCH将结果存放到复合变量中。即使用OPEN,FETCH代替了EXECUTE IMMEDIATE来完成动态SQL的执行。
运行以上程序输出结果如下:
(5)在FORALL子句中使用BULK子句下面是FORALL子句的语法:
FORALL子句允许为动态SQL输入变量,但FORALL子句仅支持DML(INSERT、DELETE、UPDATE)语句,不支持动态的SELECT语句。
在下面的示例中,首先声明了两个复合类型以及复合变量,接下来为复合变量ENAME_TABLE赋值,以形成动态SQL语句。紧接着使用FORALL子句结合EXECUTE IMMEDIATE来提取结果集。
运行以上程序输出结果如下:
3. 存储过程或函数如何返回集合类型?
TABLE()函数可接受查询语句或游标作为输入参数,并可输出多行数据,称为表函数。所以,存储过程或函数返回集合类型主要采用的是表函数和PIPELINED函数(管道化表函数)及数组结合的方式。当然,也可以采用存储过程返回系统游标SYS_REFCURSOR或自定义游标的方式。
有关存储过程或函数返回集合类型的写法有多种,作者把这多种方式写成了一个包,己发布到博客和公众号上,读者可白行下载阅读。
4. 假定SERV表有A、B、C三个字段:SERV(A NUMBER(10),B NUMBER(10),C NUMBER(10))。表SERV的内容如下:
A | B | C |
1 | 10 | 70 |
2 | 30 | 50 |
1 | 10 | 80 |
以下两段PL/SQL的功能是根据A列的值,查找出对应B列的值赋予变量X,请分别判断这两段PL/SQL是否能正常执行,若不能正常执行,请指出错误的原因并修改。
(1)
(2)
这两段程序除了WHERE语句后的值不同以外,其他均一样。对于程序(1),当A=1时,返回了2行记录,对于程序(2),当A=2时,返回了1行记录。对于变量X而言,只能接受一个值,所以,程序2执行不报错,程序(1)执行报错:ORA-01422:exact fetch returns more than requested number of rows。
对于程序(1)有两种修改方法,第一种就是将“SELECT B INTO X FROM SERV WHERE A=1;”修改为“SELECT DISTINCT B INTO X FROM SERV WHERE A=1;”。第二种方法就是返回集合类型,修改后的程序块如下:
5. 行列互换有哪些方法?
行列转换包括以下6种情况:①列转行。②行转列。③多列转换成字符串。④多行转换成字符串。⑤字符串转换成多列。⑥字符串转换成多行。其中,重点是行转列和字符串转换成多行。下面将对这几种情况做简要说明,详细转换过程请参考随书pdf文档。
(1)列转行 列转行就是将原表中的列名作为转换后的表的内容。列转行主要采用UNION ALL来完成。
(2)行转列 行转列就是将行数据内容作为列名。主要采用MAX和DECODE函数来完成。
(3)多列转换成字符串 使用||或CONCAT函数实现。
(4)多行转换成字符串 可以采用SYS_CONNECT_BY_PATH来完成。
(5)字符串转换成多列 实际上就是一个字符串拆分的问题。主要采用SUBSTR和INSTR来完成。
(6)字符串转换成多行 主要采用UNION ALL、SUBSTR和INSTR来完成,对于其他类型的转换请参考随书pdf文档。还有几类特殊的转换如下:
结果:
若是字符串类型,则如下:
结果:
6. 数据库中有一张如下所示的表,表名为SALES。
年 | 季度 | 销售量 |
1991 | 1 | 11 |
1991 | 2 | 12 |
1991 | 3 | 13 |
1991 | 4 | 14 |
1992 | 1 | 21 |
1992 | 2 | 22 |
1992 | 3 | 23 |
1992 | 4 | 24 |
要求:写一个SQL语句查询出如下所示的结果。
年 | 一季度 | 二季度 | 三季度 | 四季度 |
1991 | 11 | 12 | 13 | 14 |
1992 | 21 | 22 | 23 | 24 |
这是一道行转列的题目,首先建立表SALES:
此题若使用聚合函数+DECODE或CASE来回答,如下:
此题若使用PIVOT函数,如下:
此题若使用临时表的方式,如下:
7. 如何删除表中重复的记录?
在平时工作中可能会遇到这种情况,当试图对表中的某一列或几列创建唯一索引时,系统提示ORA-01452:不能创建唯一索引,发现重复记录。这个时候只能创建普通索引或者删除重复记录后再创建唯一索引。
重复的数据可能有这样两种情况:第一种是表中只有某些字段一样,第二种是两行记录完全一样。删除重复记录后的结果也分为两种,第一种是重复的记录全部删除,第二种是重复的记录只保留最新的一条记录,在一般业务中,第二种的情况出现较多。
(1)删除重复记录的方法原理 在Oracle中,每一条记录都有一个ROWID,ROWID在整个数据库中是唯一的,ROWID确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中,可能所有列上的内容都相同,但ROWID不会相同,所以,只要保留重复记录中那些具有最大的ROWID的记录,其余的全部删除。
(2)删除重复记录的方法若想要删除部分字段重复的数据,则使用下面语句进行删除,下面的语句是删除表中字段1和字段2重复的数据:
也可以利用临时表的方式,先将查询到的重复的数据插入到一个临时表中,然后进行删除,这样,执行删除的时候就不用再进行一次查询了。如下:
上面这句话的功能是建立临时表,并将查询到的数据插入其中。有了上面的执行结果,下面就可以进行删除操作了:
假如想保留重复数据中最新的一条记录,应该怎么做呢?可以利用ROWID,保留重复数据中ROWID最大的一条记录即可,如下:
重复数据删除技术可以提供更大的备份容量,实现更长时间的数据保留,还能实现备份数据的持续验证,提高数据恢复服务水平,方便实现数据容灾等。
8. 分区表有什么优点?分区表有哪几类?如何选择用哪种类型的分区表?
当表中的数据量不断增大时,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。当对表进行分区后,在逻辑上,表仍然是一张完整的表,只是将表中的数据在物理上可能存放到多个表空间或物理文件上。当查询数据时,不至于每次都扫描整张表。Oracle可以将大表或索引分成若干个更小、更方便管理的部分,每一部分称为一个分区,这样的表称为分区表。SQL语句使用分区表比全表能提供更好的数据处理与访问的性能。即使某些分区不可用,其他分区仍然可用,这称为分区独立性。
分区表的一些限制条件:①簇表不能进行分区。②不能分割含有LONG或LONG RAW列的表。③索引组织表不能进行范围分区。
(1)何时考虑分区?对大表进行分区,将有益于大表操作的性能和大表的数据的维护。官方文档说通常当表的大小超过2GB,或对于OLTP系统,当表的记录超过1000万条时,都应考虑对表进行分区。
(2)分区表有什么优点?分区表有如下的优点:
1)增强可用性:如果表的一个分区由于系统故障而不能使用,那么表的其余好的分区仍可以使用。
2)减少关闭时间:如果系统故障只影响表的一部分分区,那么只有这部分分区需要修复,可能比整个大表修复花的时间更少。
3)维护轻松:单独管理每个分区比管理单个大表要轻松得多。
4)均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O,改善性能。
5)改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快。
6)分区对用户透明,最终用户感觉不到分区的存在。
(3)有哪些类型的分区?如何选择用哪种类型的分区表?Oracle的分区可以分为:
1)范围分区(RANGE PARTITION)。
2)哈希分区(HASH PARTITION)。
3)列表分区(LIST PARTITION)。
4)复合分区(组合分区)。
5)INTERVAL分区(间隔分区)。
6)系统分区。
INTERVAL分区和系统分区是Oracle 11g的新特性,由于篇幅原因,本书中不讲解这2个分区,读者可自行查阅官方文档进行学习。尤其对于INTERVAL分区在生产环境中还是比较实用的。下面作者将对范围、哈希、列表和复合分区分别进行讲解。
(1)RAN(讵(范围)分区在如下几种情况下会使用到范围分区:
1)频繁地被一个时间范围谓词扫描。
2)维护一个时间滚动的数据窗口(Rolling Window Of Data)。
3)保存历史数据的表。
(2)HASH(哈希)分区HASH分区有如下的优点:
1)提高了大表的高可用性和可管理性。
2)可以避免数据倾斜,将数据均匀地分布在多个物理设备上,最大化I/O吞吐量。
3)分区修剪和分区智能连接。
4)要求分区键是高基数列。
5)分区修剪不支持基于分区字段的范围查询,只支持等值查询或IN-LIST查询。
HASH分区能够很容易对数据进行分区,因为语法很简单,很容易实现。在下面这几种情况下,使用HASH分区比RANGE分区更好:
1)事先不知道需要将多少数据映射到给定范围的时候。
2)分区的范围大小很难确定,或者很难平衡的时候。
3)RANGE分区使数据得到不期望的聚集时。
4)性能特性,如并行DML、分区修剪和分区连接很重要的时候。
(3)LIST(列表)分区列表分区(LIST PARTITION)提供了一种按照字段的值来进行分区的方法,这种方法非常适合于有高重复率字段值的表。通过这种方法,可以非常方便地控制将某些特定的数值存放到一个分区。
列表分区有如下特点:
1)列表值是离散的。
2)列表值是无序的,例如:PARTITION PART_01 VALUES('A','E','F')等。
3)分区键仅能包含一个列。
4)列表值是低基数的。
(4)复合分区如果某表按照某列分区之后,仍然较大,或者是有一些其他的需求,那么还可以通过在分区内再建子分区的方式将分区冉分区,即复合分区的方式。
复合分区在Oracle 11g之前有两种:RANGE-HASH与RANGE-LIST。需要注意的是其顺序,根分区只能是RANGE分区,子分区可以是HASH分区或LIST分区,而Oracle 11g在复合分区功能这块有所增强,又推出了RANGE-RANGE、LIST-RANGE、LIST-LIST和LIST-HASH,这就相当于除HASH外3种分区方式的笛卡尔形式都有了,即目前一共有6种分区,但根分区只能是RANGE分区或LIST分区。
在某些时候按照业务要求,上面的几种分区也可以按照一定的目的创建复合分区,或者称为子分区。
对于海量数据的数据库设计,分区的设计非常重要。例如,对于一个大表,应该采用哪种类型的分区,对于以后数据库的性能和管理至关重要。
其实,范围分区、HASH分区和列表分区这3种分区的特点都非常明显,如下:
1)如果需要进行数据的过期化处理,那么范围分区基本上是唯一的选择。
2)如果需要数据的均匀分布,那么可以考虑使用HASH分区。
3)如果数据的值可以很好地对应于某个分区,那么就可以考虑使用列表分区。
在上面的原则基础上,再结合性能的影响因素,来最终确定使用哪种类型的分区。
如果选择的分区不能确保各分区内数据量的基本平均,那么这种分区方式有可能是不恰当的。比如对于RANGE分区,假设分了10个分区,而其中一个分区中的记录数占总记录数的90%,其他9个分区只占总记录数的10%,则这个分区方式就起不到数据平衡的作用。
9. Oracle之INTERVAL分区的STORE IN属性存储在哪张表中?
存储在SYS.INSERT_TSN_LIST$中。可以通过10046事件对SQL语句“alter table TB_NAME set store in(TS1,TS2,TS3);”进行跟踪即可找到该表。所以,其查询SQL如下:
10. 分区表常用数据字典视图有哪些?
Oracle分区表相关数据字典视图如下:
1)显示数据库所有分区表的信息:DBA_PART_TABLES。
2)显示表分区信息,显示数据库所有分区表的详细分区信息:DBA_TAB_PARTITIONS。
3)显示子分区信息,显示数据库所有复合分区表的子分区信息:DBA_TAB_SUBPARTITIONS。
4)显示分区列,显示数据库所有分区表的分区列信息:DBA_PART_KEY_COLUMNS。
5)显示子分区列,显示数据库所有分区表的子分区列信息:DBA_SUBPART_KEY_COLUMNS。
11. 普通表转换为分区表有哪些办法?
将普通表转换成分区表有以下4种方法:
1)导出/导入方法(Export/Import Method)。
2)子查询插入方法(Insert With a Subquery Method)。
3)分区交换方法(Partition Exchange Method)。
4)在线重定义方法(DBMS_REDEFINITION Method)。
下面介绍一下这几种方法的主要过程:
(1)导出/导入方法(Export/Import Method) 采用逻辑导出/导入很简单,首先在源库建立分区表,然后将数据导出,导入到新建的分区表即可。
1)导出表:exp usr/pswd tables=T_TEST_LHR file=exp_lhr.dmp。
2)删除表:DROP TABLE T_TEST_LHR。
3)重建分区表的定义:
4)利用ignore=y来导入分区表:imp usr/pswd file=exp_lhr.dmp ignore=y。
(2)子查询插入方法(Insert With a Subquery Method)主要过程如下,其中,T表是非分区表:
然后改变表名:
(3)分区交换方法(Partition Exchange Method)主要过程有如下几个步骤:
创建分区表:
交换数据:
改变表名:
(4)在线重定义方法(DBMS_REDEFINITION Method)主要过程如下:
关于这几种方法的优缺点及适用情况见表。
12. 分区表性能注意事项
在使用分区表的时候需要注意以下几方面的内容:
1)在查询分区表时尽量带上分区键过滤条件,否则可能引起全分区扫描。
2)在设计分区表时,避免数据都进入默认分区,从而导致出现默认分区超大或各个分区大小严重不均衡的情况,失去分区表的意义。
3)需要特别注意分区表性能比普通表性能差的情况。这种情况的本质原因是,虽然分区表的分区索引比全局索引要小很多,但是由于没有扫描指定的分区,而是扫描了很多个小的索引,这些小索引的高度累计起来一般都比全局索引要高。索引的范围检索性能是由索引的高度(BLEVEL)决定的,而不是由索引的大小决定。所以,性能差异很明显。
13. DELETE、DROP和TRUNCATE的区别是什么?
DELETE、DROP和TRUNCATE的区别见表。