IDS(OL)_T_STD_ETL编程规范_ORACLESQL分册

1、在线经营分析系统在线经营分析系统ETL编程规范编程规范Oracle SQL分册分册当前版本：当前版本： V0.1版本日期：版本日期： 2013年年04月月07日日文档信息文档信息项目名称：项目名称：在线经营分析系统项目项目经理：项目经理：文档版本编号：文档版本编号：0.1项目管理阶段：项目管理阶段：设计分析阶段文档版本日期：文档版本日期：2013-04-07质量复审方法：质量复审方法：项目经理和架构师复审起草人起草人：卢卫平起草日期起草日期：2013-04-07复审人：复审人：复审日期：复审日期：分发名单分发名单From 自自日期日期部门部门职务职务Email / 电话电话 给给 To 部门部

2、门职务职务行动行动*截止日期截止日期Email / 电话电话*行动类别：批准，复审，通知，存档，所需行动，参加会议，其它（请指明）版本历史版本历史版本编号版本编号 版本日期版本日期修改人修改人描述描述文件名文件名0.12013-04-07卢卫平目目 录录1 前言.41.1 边界定义.41.2 原则定义.42格式编排约定格式编排约定.52.1 基本要求.52.2 编写规范.52.2.1字段排列要求.52.2.2 字段分割符,点书写位置要求.52.2.3字段别名 AS 语句编写要求.52.2.4 SELECT 子句排列要求.52.2.5运算符前后间隔要求.62.2.6 CASE 语句的编写.62.

3、2.7 DECODE 语句：.72.2.8子查询嵌套编写规范.72.2.9表别名定义约定.83语法及数据操作约定语法及数据操作约定.93.1 JOIN 操作语法要求.93.2 INSERT 语句编写规范 .93.3 DELETE 语句编写规范 .93.4 NULL 值处理约定.103.4.1 数字型字段NULL 值的处理.103.4.2 字符型字段NULL 值的处理.103.4.3提取日期型字段的处理.103.5 除法运算被 0 除的处理.103.6 注释约定.114 小技巧小技巧.114.1 如何删除表中的重复记录.124.2 横转竖表的转换.124.3 窗口函数与聚合函数比较.124.4

4、关于 NOT IN 的使用.134.5 关于统计信息的收集.134.6 求M行到N行的数据.134.7 一条记录根据条件多表插入.134.8 实现分组取前 N 条记录（TOP N 算法）.145 知识点知识点.145.1 SQL 连接.145.2 ORACLE SQL 中的 UPDATE 用法.166 存储过程模板存储过程模板.171 前言前言为了统一软件开发过程中关于SQL(Structured Query Language)编码的格式，使编码人员编写SQL 代码遵从特定的风格，并养成良好的开发习惯，从而增强代码的可读性，便于交流和维护，特此收集、整理已经积累的技术资料，修订和编制了本编码规

5、范。 本规范适用于已掌握Oracle数据库系统的基本知识及T-SQL 编程技能者阅读，对从事Oracle数据库系统的代码开发、测试、检查人员起指导的作用，也即对从事Oracle 数据库系统技术工作人员必不可少的阅读材料。1.1 边界定义边界定义数据库系统的开发更多的是对数据进行加载、整理、抽取的工作，在项目实施过程中经常要用到对数据进行各种形式操作的开发工作，因此对数据操作的代码编写进行规范化的约定显的优为重要。本规范主要用于Oracle 数据库系统的结构化查询语言代码编写的标准化要求，结合Oracle 的特点以及代码开发人员在日常编写SQL 的DML(Data Manipulation La

6、nguage)经验，为规范Oracle数据库系统的SQL 的DML 开发建立统一的标准。规范主要应用于Oracle SQL 的DML 以下几个语句编写约定Select 语句；Insert 语句；Delete 语句；逻辑判断语句；类型转换语句；以及相关的Oracle 函数；Oracle数据库项目实施过程中数据表现形态约定等内容。在Oracle数据库中不建议使用UPDATE 语句来进行数据的更新操作，可以采用替代的中间过度表完成表中数据的重新组织，从而实现原表数据值的更新。1.2 原则定义原则定义1、要求代码行清晰、整齐，具有一定的可观赏性；2、代码编写要充分考虑执行速度最优的原则；3、代码行整体

7、层次分明、结构化强；4、代码中应有必要的注释以增强代码的可读性；5、规范要求非强制性约束代码开发人员的代码编写行为，在实际应用中在不违反常规要求的前提下允许存在可理解的偏差。 本规范在对日常的代码开发工作起到指导作用的同时也将得到不断的完善和补充。2格式编排约定格式编排约定2.1 基本要求基本要求1 代码段中应用到的所有Oracle 关键字、保留字都应大写大写 如 SELECT 、FROM、WHERE、AND、OR、UNION、INSERT、DELETE、GROUP、HAVING、COUNT 等；代码行中的关键字不允许使用简写的方式（如：SEL、DEL）； 1 表名、字段名、字段别名以首字大写

8、加下划线连接符来命名，建议使用规范命名(更完整的命名规则不在本规范中阐述)，1 四个空格四个空格为一个缩进量，所有的缩进皆为一个缩进量的整数位所有的缩进皆为一个缩进量的整数位 。1 对应的括号通常要求在同一列的位置上；1 每行宽度不超过120120字符字符 (每个字符为8 个点阵宽)，超过行宽的代码可折行与上行左对齐编排；2.2 编写规范编写规范2.2.1 字段排列要求字段排列要求示例1： 1 SELECT 语句选择的字段按每行一个字段方式编排；2 SELECT 单字后面一个缩进量一个缩进量，后直接跟首个选择的字段，即字段离首起二个缩二个缩进量进量； 3 其它字段前导二个缩进量再跟一二个缩进量

9、再跟一, ,点点后放置字段名；2.2.2 字段分割符字段分割符,点书写位置要求点书写位置要求 两个字段之间的,点分割符紧跟在第二个字段的前面2.2.3 字段别名字段别名 AS 语句编写要求语句编写要求 AS语句应与相应的字段在同一行； 多个字段的AS建议尽量对齐在同一列上； 字段别名在同一个查询例程中可以被引用2.2.4 SELECT 子句排列要求子句排列要求示例3： SELECT 语句中所用到的FROM、WHERE、GROUP BY、HAVING、ORDER BY、JOIN、UNION 等子句：1 换行编写；2 与相应的SELECT 语句对齐；3 子句后续的代码离子句首字母二个缩进量二个缩进

10、量起编写；4 WHERE 子句下的逻辑判断符 AND 、OR 等与WHERE 右对齐编排5 超过两个缩进量长度的子句加一空格后编写后续代码，如：ORDER BY、GROUP BY 等；2.2.5 运算符前后间隔要求运算符前后间隔要求示例4： 算术运算符、逻辑运算符的前后至少要保留一个空格2.2.6 CASE 语句的编写语句的编写示例5： SELECT 语句中对字段值进行判断取值的操作将用到的CASE 语句，正确的编排CASE 语句的写法对加强代码行的可阅读性也是很关键的一部分。我们对CASE 语句编排作如下约定：1 CASE 语句从CASE 开头到END 结束要用括弧包括起来，并给结果 值赋别

11、名字段；2 WHEN 子语在CASE 语句的下一行并缩进二个缩进量二个缩进量后编写；3 每个WHEN 子语一行编写，当然如果语句较长可换行编排；4 CASE 语句必须包含ELSE 子语；2.2.7 DECODE 语句：语句： DECODE语句是ORACLE独有的，在封装程度和使用上皆方便于CASE语句，但是，咱们是需要面对多种的数据库编程的话，最好使用CASE语句，因为这才是T-SQL中的标准语句。2.2.8 子查询嵌套编写规范子查询嵌套编写规范用好子查询是提高代码执行速度的一个好方法，采用子查询来缩小结果集之间的操作将使系统运算的开销更小，当然Oracle的优化器也会优化代码的处理过程以最优

12、的路径来完成用户的需求，但我们在编写代码时也要考虑性能的优化。子查询嵌套在Oracle 数据库系统开发中是经常要用到的，因此代码的分层编排就非常重要。示例6： 2.2.9 表别名定义约定表别名定义约定因为一旦在SELECT 语句中给操作表定义了别名，那么在整个语句中对此表的引用都必须惯以别名替代，因此我们考虑到编写代码的方便性，我们约定别名尽量简单、简洁，表别名可以用 a、b、c、a1、a2、sale、buy 等来定义，当然要避免使用Oracle中的保留字如：order、partiton 等。 表别名采用简单字符命名； 多层次的嵌套子查询别名之前要体现层次关系； 有需要的情况下对表别名加注释；

13、示例7： 3语法及数据操作约定语法及数据操作约定3.1 JOIN 操作语法要求操作语法要求示例8： 多个表JOIN 取值在非INNER JOIN 的情况下大多会取到空值，对这些空值在程序代码中需要进行处理，同时JOIN 操作也要注意被JOIN 表的关键字段值是否唯一，因此对JOIN 语句作如下约定：1 对有可能匹配不上而产生的空值要进行转换处理（各种类型数据的空值转换见下文描述）2 在此不采纳使用 RIGHT JOIN 进行表的JOIN 操作，因为RIGHT JOIN 完全可以用LEFT JOIN 来实现，同时RIGHT JOIN 在代码的逻辑上看起来不太容易理解。3.2 INSERT 语句编

14、写规范语句编写规范INSERT 语句在异构表之前的数据拷贝一定要带字段进行值的插入。如下所示：示例9： 3.3 DELETE 语句编写规范语句编写规范DELETE 语句将会删除数据表的信息，所以对进行DELETE 的操作要格外小心，一定要检查DELETE 限定条件是否正确。通常我们在代码开发过程中对数据进行删除操作后再进行数据的插入操作，对这种类型的操作我们在编写代码时是将两个例程作为一个事务提交以保证整个事务的完整性。示例10： 3.4 NULL 值处理约定值处理约定我们约定在提取数据的结束集中不允许存在NULL 的值，因此对有可能出现NULL 值的各种类型字段需要进行特殊的值转换处理。3.

15、4.1 数字型字段数字型字段 NULL 值的处理值的处理 NVL （数字型字段,0）说明：通过以上函数处理后，如果字段值为NULL 将返回 0 值。3.4.2 字符型字段字符型字段 NULL 值的处理值的处理NVL(字符型字段,)3.4.3 提取日期型字段的处理提取日期型字段的处理NVL(日期型字段,YYYY-MM-DD HH24:MI:SS)3.5 除法运算被除法运算被 0 除的处理除的处理为不避免在进行除法运算时被 0 除出错，我们需要对除法运算有可能出现被 0 除的情况作特殊处理。示例11： 说明：对有可能出现0 的除数如果是0 值时要把它转换为NULL，任何数与NULL 相除等于NUL

16、L，因此不会使程序出错，同时对除法运算返回值如果是NULL 时要把它转换为0. 3.6 注释约定注释约定 1、该程序开发的相关环境，也就是在程序开始之前的注释，必须要有。2、开发程序当中每句话都注释，按照常理是越清楚越好，但是这会牵扯太多的开发精力，所以适当即可。但是程序是可以分成很多模块的，在每个模块开始务必写清楚该模块的含义。3、在程序的后面，不管程序运行正确与否，尽量都要记录正确或者错误的日志。对于较为复杂的数据操作例程应有充分的注释，注明实现的功能，业务逻辑关系输入输出关系等内容多行注释可用 /* */ 来标识单行注释可用 -来标识示例14： 对于较为复杂的过程必须注明代码实现的功能以

17、及相关的创建、修改记录； 主体上有分割的代码行建议加一空白行以示区分。4 小技巧小技巧 这个章节的内容其实就是一些 SQL 的使用技巧，一些疑难的 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

18、.pk_column_2 );4.2 横转竖表的转换横转竖表的转换 横表和竖表的很不好处理，现在简单的举个例子，看看原理： 固定列数的行列转换固定列数的行列转换如student subject grade-student1 语文 80student1 数学 70student1 英语 60student2 语文 90student2 数学 80student2 英语 100转换为 语文 数学 英语student1 80 70 60student2 90 80 100语句如下：SELECT student,SUM(DECODE(subject,语文语文, grade,null) 语文语文, SU

19、M(DECODE(subject,数学数学, grade,null) 数学数学, SUM(DECODE(subject,英语英语, grade,null) 英语英语FROM tableGROUP BY student不定列行列转换不定列行列转换这一类型的转换必须借助于 PL/SQL 来完成，实现起来比较复杂，不再这里阐述。4.3 窗口函数与聚合函数比较窗口函数与聚合函数比较 Oracle的窗口函数比常规聚合函数要慢很多，所以今后的SQL 中要尽可能的向外层推窗口函数，因为越向外，结果集越小，窗口函数的代价就会降低。4.4 关于关于 NOT IN 的使用的使用在 NOT IN 表达式中，如果 N

20、OT IN 的子查询记录的重复系数比较高，适于在子查询中加上 GROUP BY ，显式过滤掉重复记录。4.5 关于统计信息的收集关于统计信息的收集1、 统计信息要收集就就尽量收集全面，包括所有可能用在条件中的字段以及连接用的组合字段；2 、统计信息可以灌，只要保证上次的统计信息是好的情况下。尽量避免部分统计信息在不同时间段重灌，一旦需要重灌，建议先将所有现有信息全 DROP 掉，在一个全初始的情况下灌入。3 、统计信息可以手工改写，但改写时要格外谨慎，避免造成与实际情况差距太大。灌入改写后的信息后，最好对以前查询的 SQL 进行一下 EXPLAIN，以确保不会对其他查询造成影响。4 、尽管说完

21、全没有统计信息时，查询计划也不会很坏，但要注意统计信息对 INDEX 的影响，因为有些 INDEX 在没有统计信息时是不起作用的。如果只灌入 INDEX 字段的统计信息，需格外小心，用 EXPLAIN 结果证明不会出现对其他查询执行路径的影响。5 、看起来对 INDEX 字段的信息收集与对表相应字段的信息收集是一样的，只对表收集或只对 INDEX 收集即可，无需重复进行。4.6 求求 m 行到行到 n 行的数据行的数据 使用 Oracle8i 开始支持的分析函数 这种方法取出来的数据准确性相当高, 查询效率比较稳定. 建议使用这种方法.SELECT col1,col2,.,colnFROM (

22、 select ROW_NUMBER() OVER(ORDER BY colx desc /asc ) rn, col1,col2,.,coln FROM table_name WHERE .)WHERE rn BETWEEN m AND n 4.7 一条记录根据条件多表插入一条记录根据条件多表插入9i 以上可以通过 Insert all 语句完成，仅仅是一个语句，如：INSERT ALLWHEN (id=1) THENINTO table_1 (id, name) values(id,name)WHEN (id=2) THENINTO table_2 (id, name) values(id

23、,name)ELSEINTO table_other(id, name) values(id, name)SELECT id,nameFROM a;如果没有条件的话，则完成每个表的插入，如INSERT ALLINTO 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,nameFROM a;4.8 实现分组取前实现分组取前 N 条记录（条记录（TOP N 算法）算法）TOP N 算法有很多种，而

24、且是数据分析中很经典的一种分析方法，大致分为两种：静态 TOP N、动态 TOP N。我们这里讲讲简单的静态 TOP N 实现方法：如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。SELECT * from (SELECT depno,ename,sal, ROW_NUMBER() OVER (PARTITION BY depno ORDER BY sal desc) rn FROM emp) WHERE rn=35 知识点知识点5.1 SQL 连接连接 连接体现了关系型数据库的灵魂，大家看到连接自然而然的就会想到“=”，无非就是A 表的某个字段等于 B 表的某个字段。其实连接的含义比

25、这广泛的多，请看详细的阐述。 连接问题分为：内连接，外联结，交叉连接连接问题分为：内连接，外联结，交叉连接.内连接内连接 分为 等值连接等值连接，不等值连接不等值连接，自然连接自然连接，等值连接与不等值连接都好理解,自然连接开始有一点费解。很简单就是最基本的连接语句SELECT a.st_id,,b.skill FROM students a , student_skill b WHERE a.st_id = b.st_id等价于SELECT a.st_id,,b.skill FROM students a INNER JOIN student_skill b自然连接的

26、定义自然连接的定义：自然连接自然连接：在连接条件中使用等于(=)运算符比较被连接列 的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。交叉连接交叉连接(CROSS JOIN)没有 WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles 表中有 6 类图书，而 publishers 表中有 8 家出版社，则下列交叉连接检索到的记录数将等于 6*8=48 行。SELECT type,pub_nameFROM titles CROSS JOIN publishe

27、rsORDER BY type现在是连接的重点：外连接现在是连接的重点：外连接 外连接操作与内连接操作的区别：外连接在查询两个表时要把某一个表中的数据全部列出来，而内连接呢，只是把两个表中符合条件的部分数据列出来。外连接分为左外连接，右外连接，全外连接（外连接分为左外连接，右外连接，全外连接（Oracle 不支持）不支持）外连接的操作关键字 left outer join , right outer join , 在 Oracle 中一般用(+)表示,根据放置位置不同来确定是内连接还是外连接，就像内连接用“=”,等来表示一样。SELECT a.st_id, ,b.skill FROM students a, student_skill b WHERE a.st_id = b.st_id(+);可以这么理解(+)放在哪边,就不会以这个表为主,如果没有相应的数据以空补齐对