编程规范与技巧Oracle SQL.pdf
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_05.gif)
《编程规范与技巧Oracle SQL.pdf》由会员分享,可在线阅读,更多相关《编程规范与技巧Oracle SQL.pdf(33页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、 商务智能部 1 编程规范与技巧编程规范与技巧 Oracle SQL 当前版本:当前版本:Ver 0.6 版本日期:版本日期:2009 年年 08 月月 15 日日 商务智能部 2 目录目录 编程规范与技巧 .1Oracle SQL .11.前言 .41.1 边界定义 .41.2 原则定义 .52.格式编排约定 .62.1 基本要求 .62.2 编写规范.62.2.1 字段排列要求 .62.2.2 字段分割符,点书写位置要求 .72.2.3 字段别名 AS 语句编写要求 .72.2.4 SELECT 子句排列要求 .72.2.5 运算符前后间隔要求 .82.2.6 CASE 语句的编写.92.
2、2.7 DECODE 语句:.92.2.8 子查询嵌套编写规范 .102.2.9 表别名定义约定 .103.语法及数据操作约定 .123.1 JOIN 操作语法要求 .123.2 INSERT 语句编写规范 .133.3 DELETE 语句编写规范 .133.4 NULL 值处理约定 .143.4.1 数字型字段 NULL 值的处理 .143.4.2 字符型字段 NULL 值的处理 .143.4.3 提取日期型字段的处理 .143.5 除法运算被 0 除的处理 .143.6 注释约定 .164.小技巧 .184.1 如何删除表中的重复记录 .184.2 横转竖表的转换 .194.3 窗口函数与
3、聚合函数比较 .204.4 关于 NOT IN 的使用 .204.5 关于统计信息的收集 .204.6 求 m 行到 n 行的数据 .214.7 一条记录根据条件 多表插入 .224.8 实现分组取前 N 条记录(TOP N 算法).235.知识点 .245.1 SQL 连接 .241.LEFT OUTER JOIN:左外关联 .26 商务智能部 3 2.RIGHT OUTER JOIN:右外关联 .265.2 ORACLE SQL 中的 UPDATE 用法 .275.3 存储过程模板 .29 商务智能部 4 1.前言前言 为了统一软件开发过程中关于 SQL(Structured Query
4、Language)编码的格式,使编码人员编写SQL 代码遵从特定的风格,并养成良好的开发习惯,从而增强 代码的可读性,便于交流和维护,特此收集、整理已经积累的技术资料,修订 和编制了本编码规范。本规范适用于已掌握 Oracle 数据库系统的基本知识及 T-SQL 编程技能者阅 读,对从事Oracle 数据库系统的代码开发、测试、检查人员起指导的作用,也即 对从事 Oracle 数据库系统技术工作人员必不可少的阅读材料。1.1 边界定义边界定义 数据库系统的开发更多的是对数据进行加载、整理、抽取的工作,在项目 实施过程中经常要用到对数据进行各种形式操作的开发工作,因此对数据操作 的代码编写进行规
5、范化的约定显的优为重要。本规范主要用于Oracle 数据库系统的结构化查询语言代码编写的标准化要 求,结合Oracle 的特点以及代码开发人员在日常编写 SQL 的 DML(Data Manipulation Language)经验,为规范 Oracle 数据库系统的 SQL 的 DML 开发建立 统一的标准。规范主要应用于 Oracle SQL 的 DML 以下几个语句编写约定 Select 语句;Insert 语句;Delete 语句;逻辑判断语句;类型转换语句;以及相关的 Oracle 函 数;Oracle 数据库项目实施过程中数据表现形态约定等内容。在 Oracle 数据库中不建议使用
6、 UPDATE 语句来进行数据的更新操作,可以 采用替代的中间过度表完成表中数据的重新组织,从而实现原表数据值的更新。商务智能部 5 1.2 原则定义原则定义 1、要求代码行清晰、整齐,具有一定的可观赏性;2、代码编写要充分考虑执行速度最优的原则;3、代码行整体层次分明、结构化强;4、代码中应有必要的注释以增强代码的可读性;5、规范要求非强制性约束代码开发人员的代码编写行为,在实际应用中在 不违反常规要求的前提下允许存在可理解的偏差。本规范在对日常的代码开发工作起到指导作用的同时也将得到不断 的完善和补充。商务智能部 6 2.格式编排约定格式编排约定 2.1 基本要求基本要求 代码段中应用到的
7、所有 Oracle 关键字、保留字都应大写 如 SELECT 、FROM、WHERE、AND、OR、UNION、INSERT、DELETE、GROUP、HAVING、COUNT 等;代码行中的关键字不允许使用简写的方式(如:SEL、DEL);表名、字段名、字段别名以首字大写加下划线连接符来命名,建议使用 规范命名(更完整的命名规则不在本规范中阐述),四个空格为一个缩进量,所有的缩进皆为一个缩进量的整数位 。对应的括号通常要求在同一列的位置上;每行宽度不超过 120 字符 (每个字符为 8 个点阵宽),超过行宽的代码可 折行与上行左对齐编排;2.2 编写规范编写规范 2.2.1 字段排列要求字段
8、排列要求 示例 1:SELECT 语句选择的字段按每行一个字段方式编排;SELECT 单字后面一个缩进量,后直接跟首个选择的字段,即字段离 商务智能部 7 首起二个缩进量;其它字段前导二个缩进量再跟一,点后放置字段名;2.2.2 字段分割符字段分割符,点书写位置要求点书写位置要求 两个字段之间的,点分割符紧跟在第二个字段的前面 2.2.3 字段别名字段别名 AS 语句编写要求语句编写要求 AS语句应与相应的字段在同一行;多个字段的AS建议尽量对齐在同一列上;字段别名在同一个查询例程中可以被引用 2.2.4 SELECT 子句排列要求子句排列要求 示例 2:商务智能部 8 SELECT 语句中所
9、用到的 FROM、WHERE、GROUP BY、HAVING、ORDER BY、JOIN、UNION 等子句:换行编写;与相应的 SELECT 语句对齐;子句后续的代码离子句首字母二个缩进量起编写;WHERE 子句下的逻辑判断符 AND 、OR 等与 WHERE 右对齐编排 超过两个缩进量长度的子句加一空格后编写后续代码,如:ORDER BY、GROUP BY 等;2.2.5 运算符前后间隔要求运算符前后间隔要求 示例 3:算术运算符、逻辑运算符的前后至少要保留一个空格 商务智能部 9 2.2.6 CASE 语句的编写语句的编写 示例 4:SELECT 语句中对字段值进行判断取值的操作将用到的
10、 CASE 语 句,正 确的编排 CASE 语句的写法对加强代码行的可阅读性也是很关键的一部分。我们对 CASE 语句编排作如下约定:CASE 语句从 CASE 开头到 END 结束要用括弧包括起来,并给结果 值赋别名字段;WHEN 子语在 CASE 语句的下一行并缩进二个缩进量后编写;每个 WHEN 子语一行编写,当然如果语句较长可换行编排;CASE 语句必须包含 ELSE 子语;2.2.7 DECODE 语句:语句:DECODE 语句是 ORACLE 独有的,在封装程度和使用上皆方便于 CASE 语句,但 是,咱们是需要面对多种的数据库编程的话,最好使用 CASE 语句,因为这才是 T-S
11、QL 中的标准语句。商务智能部 10 2.2.8 子查询嵌套编写规范子查询嵌套编写规范 用好子查询是提高代码执行速度的一个好方法,采用子查询来缩小结果集之 间的操作将使系统运算的开销更小,当然 Oracle 的优化器也会优化代码的处理过 程以最优的路径来完成用户的需求,但我们在编写代码时也要考虑性能的优化。子查询嵌套在 Oracle 数据库系统开发中是经常要用到的,因此代码的分 层编排就非常重要。示例 5:2.2.9 表别名定义约定表别名定义约定 因为一旦在 SELECT 语句中给操作表定义了别名,那么在整个语句中对此表 的引用都必须惯以别名替代,因此我们考虑到编写代码的方便性,我们约定别名
12、尽量简单、简洁,表别名可以用 a、b、c、a1、a2、sale、buy 等来定义,当然 要避免使用 Oracle 中的保留字如:order、partiton 等。表别名采用简单字符命名;多层次的嵌套子查询别名之前要体现层次关系;有需要的情况下对表别名加注释;商务智能部 11 示例 6:商务智能部 12 3.语法及数据操作约定语法及数据操作约定 3.1 JOIN 操作语法要操作语法要求求 示例 7:多个表 JOIN 取值在非 INNER JOIN 的情况下大多会取到空值,对这些 空值在程序代码中需要进行 处理,同时 JOIN 操作也要注意被 JOIN 表的关 键字段值是否唯一,因此对JOIN 语
13、句作如下约定:对有可能匹配不上而产生的空值要进行转换处理(各种类型数据的空值 转换见下文描述)在此不采纳使用 RIGHT JOIN 进行表的 JOIN 操作,因为 RIGHT JOIN 完全可以用 LEFT JOIN 来实现,同时 RIGHT JOIN 在代码的逻辑上看起来不太 容易理解。商务智能部 13 3.2 INSERT 语句编写规范语句编写规范 INSERT 语句在异构表之前的数据拷贝一定要带字段进行值的插入。如下所示:示例 8:3.3 DELETE 语句编写规范语句编写规范 DELETE 语句将会删除数据表的信息,所以对进行 DELETE 的操作要 格外小心,一定要检查 DELETE
14、 限定条件是否正确。通常我们在代码开发过程中对数据进行删除操作后再进行数据的插入操 作,对这种类型的操作我们在编写代码时是将两个例程作为一个事务提交以 保证整个事务的完整性。示例 9:商务智能部 14 3.4 NULL 值处理约定值处理约定 我们约定在提取数据的结束集中不允许存在 NULL 的值,因此对有可能 出现 NULL 值的各种类型字段需要进行特殊的值转换处理。3.4.1 数字型字段数字型字段 NULL 值的处理值的处理 NVL (数字型字段,0)说明:通过以上函数处理后,如果字段值为 NULL 将返回 0 值。3.4.2 字符型字段字符型字段 NULL 值的处理值的处理 NVL(字符型
15、字段,)3.4.3 提取日期型字段的处理提取日期型字段的处理 NVL(日期型字段,YYYY-MM-DD HH24:MI:SS)3.5 除法运算被除法运算被 0 除的处理除的处理 为不避免在进行除法运算时被 0 除出错,我们需要对除法运算有可能出 现被 0 除的情况作特殊处理。商务智能部 15 示例 10:说明:对有可能出现 0 的除数如果是 0 值时要把它转换为 NULL,任何 数与 NULL 相除等于 NULL,因此不会使程序出错,同时对除法运算返回值 如果是 NULL 时要把它转换为0.商务智能部 16 3.6 注释约定注释约定 1、该程序开发的相关环境,也就是在程序开始之前的注释,必须要
16、有。2、开发程序当中每句话都注释,按照常理是越清楚越好,但是这会牵扯 太多的开发精力,所以适当即可。但是程序是可以分成很多模块的,在每个 模块开始务必写清楚该模块的含义。3、在程序的后面,不管程序运行正确与否,尽量都要记录正确或者错误 的日志。对于较为复杂的数据操作例程应有充分的注释,注明实现的功能,业务逻 辑关系输入输出关系等内容多行注释可用 /*/来标识单行注释可用-来标识 示例 11:商务智能部 17 对于较为复杂的过程必须注明代码实现的功能以及相关的创建、修改 记录;主体上有分割的代码行建议加一空白行以示区分。商务智能部 18 4.小技巧小技巧 这个章节的内容其实就是一些 SQL 的使
17、用技巧,一些疑难的 SQL 问题解决。4.1 如何删除表中的重复记录如何删除表中的重复记录 删除重复记录,是一个很有代表性的技术问题,有很多种方法,这里的实 现方法采用了 rowid 的唯一性的特性。示例如下:DELETE FROM table_name a WHERE rowid (SELECT min(rowid)FROM table_name b WHERE b.pk_column_1=a.pk_column_1 AND b.pk_column_2=a.pk_column_2);商务智能部 19 4.2 横转竖表的转换横转竖表的转换 横表和竖表的很不好处理,现在简单的举个例子,看看原理:
18、固定列数的行列转换 如 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)
19、英语 FROM table GROUP BY student 不定列行列转换 这一类型的转换必须借助于 PL/SQL 来完成,实现起来比较复杂,不再这里阐述。商务智能部 20 4.3 窗口函数与聚合函数比较窗口函数与聚合函数比较 Oracle 的窗口函数比常规聚合函数要慢很多,所以今后的 SQL 中要尽可能 的向外层推窗口函数,因为越向外,结果集越小,窗口函数的代价就会降低。4.4 关于关于 NOT IN 的使用的使用 在 NOT IN 表达式中,如果 NOT IN 的子查询记录的重复系数比较高,适于 在子查询中加上 GROUP BY ,显式过滤掉重复记录。4.5 关于统计信息的收集关于统计信
20、息的收集 1、统计信息要收集就就尽量收集全面,包括所有可能用在条件中的字段 以及连接用的组合字段;2 、统计信息可以灌,只要保证上次的统计信息是好的情况下。尽量避免 部分统计信息在不同时间段重灌,一旦需要重灌,建议先将所有现有信息全 DROP 掉,在一个全初始的情况下灌入。3 、统计信息可以手工改写,但改写时要格外谨慎,避免造成与实际情况 差距太大。灌入改写后的信息后,最好对以前查询的 SQL 进行一下 EXPLAIN,以 确保不会对其他查询造成影响。4 、尽管说完全没有统计信息时,查询计划也不会很坏,但要注意统计信 息对 INDEX 的影响,因为有些 INDEX 在没有统计信息时是不起作用的
21、。如果只 灌入 INDEX 字段的统计信息,需格外小心,用 EXPLAIN 结果证明不会出现对其 他查询执行路径的影响。5 、看起来对 INDEX 字段的信息收集与对表相应字段的信息收集是一样的,只对 表收集或只对 INDEX 收集即可,无需重复进行。商务智能部 21 4.6 求求 m 行到行到 n 行的数据行的数据 使用 Oracle8i 开始支持的分析函数 这种方法取出来的数据准确性相当高,查询效率比较稳定.建议使用这种方法.SELECT col1,col2,.,coln FROM(select ROW_NUMBER()OVER(ORDER BY colx desc/asc)rn,col1
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 编程规范与技巧Oracle SQL 编程 规范 技巧 Oracle
![提示](https://www.deliwenku.com/images/bang_tan.gif)
限制150内