数据仓库设计编码规范3.0

数据仓库设计编码规范(3.0)上海****通信技术有限公司金刚(seniordba@)2011-02

1培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志2目的和范围目的：好的命名规范，可以明显的增强程序的可读性和可维护性：

(1).保障程序一致性(2).增强程序的可读性(3).增强程序可维护性

(4).降低程序的BUG概率

(5).提升程序可扩展性和性能范围：

本规范主要适用于所有基于Oracle数据仓库设计开发的项目。

3培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志4数据仓库基本概念(一)数据仓库：数据仓库是一个面向主题的，集成的，时变的和非易失的数据集合，支持管理部门的决策过程。

面向主题的：数据仓库围绕一些主题如顾客、供应商、产品和销售来组织。数据仓库关注决策者的数据建模与分析，而不是组织机构的日常操作和事务处理。因此，数据仓库排除对于决策支持过程无用的数据，提供特定主题的简明视图。

集成的：通常，构造数据仓库是将多个异构数据源，如关系数据库，平面文件和联机事务记录集成在一起。使用数据清理和数据集成技术确保命名约定、编码结构、属性度量等的一致性。

时变的：数据存储从历史的角度（例如过去3~5年）提供信息。数据仓库中的关键结构都隐式或显式的包含时间元素。

非易失的：数据仓库总是物理的分别存放数据；这些数据源于操作环境下的应用数据。由于这种分离，数据仓库不需要事务处理、恢复和并发控制机制。通常，它只需两种数据访问操作：数据的初始化装入和数据访问。5数据仓库基本概念(二)数据仓库的三种模型：(一)、企业数据仓库(EDW):

数据仓库收集了整个组织关于主题的所有信息，它提供企业范围内的数据集成，通常来自一个或多个操作数据库系统或外部信息提供者，并且是跨功能的。通常，它包含细节数据和汇总数据，它需要广泛的商业建模，可能需要多年设计和建设。

(二)、数据集市数据集市包含企业范围的一个子集，针对特定的用户群和特定的主题。例如，销售数据集市可能限定其主题为顾客、商品和销售。包括在数据集市中的数据通常是汇总的。(三)、虚拟仓库虚拟仓库是操作数据库视图的集合。虚拟仓库易于建立，但需要操作数据库服务器具有剩余能力。6数据仓库基本概念(三)ODS：

ODS是企业数据架构中最为复杂的一种形态，既要满足数据事务操作要求，又要满足数据分析要求，从技术构建角度考察，难度相当之大。其主要功能是给数据仓库提供数据，作为EDW的数据源。

ODS与数据仓库的重要区别如下：

(一)、ODS只存储明细数据。

(二)、ODS中存储的数据一般不超过一个月。

(三)、ODS支持事务更新操作。

(四)、ODS是应用系统数据库的一个延迟快照。根据延迟时间的长度，分为：

I类ODS,实时或近似实时，与应用系统的数据延迟为1~2秒。

II类ODS,与应用系统的数据延迟为2~4小时。

III类ODS,与应用系统的数据延迟为12~24小时。7数据仓库体系架构图8数据仓库基本层次结构(一)系统的信息模型从存储的内容方面可以分为：

(一)、STAGE接口信息模型

(二)、ODS/DWD信息模型

(三)、MID信息模型

(四)、DM(DataMart)信息模型

(五)、元数据信息模型9数据仓库基本层次结构(二)在各个信息模型中存储的内容如下描述：

1)

STAGE层信息模型：提供业务系统数据文件的临时存储，数据稽核，数据质量保证，屏蔽对业务系统的干扰，对于主动数据采集方式，以文件的方式描述系统与各个专业子系统之间数据接口的内容、格式等信息。与该模型对应的数据是各个专业系统按照该模型的定义传送来的数据文件。STAGE是生产系统数据源的直接拷贝，由ETL过程对数据源进行直接抽取，在格式和数据定义上不作任何改变。10数据仓库基本层次结构(三)STAGE层存在的意义在于两点：(1).对数据源做统一的一次获取，数据仓库中其它部分都依赖STAGE层的数据，不再重复进行抽取，也不在生产系统上做运算，减少生产系统的压力。(2).在生产系统数据已经刷新的情况下，保存一定量的生产系统的历史数据，以便在运算出错的情况下可以进行回溯。11数据仓库基本层次结构(四)2)

ODS/DWD层信息模型，简称DWD层是数据仓库的细节数据层，是对STAGE层数据进行沉淀，减少了抽取的复杂性，同时ODS/DWD的信息模型组织主要遵循企业业务事务处理的形式，将各个专业数据进行集中。为企业进行经营数据的分析，系统将数据按分析的主题的形式存放，跟STAGE层的粒度一致，属于分析的公共资源。12数据仓库基本层次结构(五)3)MID数据模型轻度综合层是新模型增加的数据仓库中DWD层和DM层之间的一个过渡层次，是对DWD层的生产数据进行轻度综合和汇总统计。轻度综合层与DWD的主要区别在于二者的应用领域不同，DWD的数据来源于生产型系统，并为满足一些不可预见的需求而进行沉淀；轻度综合层则面向分析型应用进行细粒度的统计和沉淀。13数据仓库基本层次结构(六)4)DM信息模型为专题经营分析服务，系统将数据按分析的专题组织成多维库表的形式存放，属于分析目标范畴的数据组织和汇总，属于分析的专有资源。其信息主要来源于DWD和MID层汇总，反映实时的经营状况，时间维度为天。而历史经营状况的分析，时间维度一般为周，月，同时也具有季度，年这样的维度。14数据仓库基本层次结构(七)5)MDW元数据信息模型描述数据及其环境的数据，即是对数据资源的描述，是信息共享和交换的基础和前提，用于描述数据集的内容、质量、表示方式、空间参考、管理方式以及数据集的其它特征。一般来说，它有两方面的用途。首先，元数据能提供基于用户的信息，如记录数据项的业务描述信息的元数据能帮助用户使用数据。其次，元数据能支持系统对数据的管理和维护，元数据机制主要支持以下五类系统管理功能：

(1).描述哪些数据在数据仓库中

(2).定义要进入数据仓库中的数据和从数据仓库中产生的数据

(3).记录根据业务事件发生而随之进行的数据抽取工作时间安排

(4).记录并检测系统数据一致性的要求和执行情况

(5).衡量数据质量15培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志16分级存储（一）概念：分级存储是根据数据的重要性、访问频率、保留时间、容量、性能等指标，将数据采取不同的存储方式存储在不同性能的存储设备上，通过分级存储管理实现数据客体在存储设备之间的自动迁移。数据分级存储的工作原理是基于数据访问的局部性。通过将不经常访问的数据自动移到存储层次中较低的层次，释放出较高成本的存储空间给更频繁访问的数据，可以获得更好的性价比。这样，一方面可大大减少非重要性数据在一级本地磁盘所占用的空间，还可加快整个系统的存储性能。17分级存储（二）在线存储近线存储离线存储(核心存储)(二级存储)(三级存储)在分级存储体系架构中，一般分为:

在线(On-line)存储

近线(Near-line)存储

离线(Off-line)存储三级存储方式。按照数据的不同生命周期制定相应存储机制，降低成本18分级存储（三）

在线存储：在线存储是指将数据存放在高速的磁盘系统（如闪存存储介质、FC磁盘或SCSI磁盘阵列)等存储设备上，适合存储那些需要经常和快速访问的程序和文件，其存取速度快，性能好，存储价格相对昂贵。在线存储是工作级存储，其最大特征是存储设备和所存储的数据时刻保持“在线”状态，可以随时读取和修改，以满足前端应用服务器和数据库对数据访问的速度要求。19分级存储(四)

近线存储近线存储是指将数据存放在低速的磁盘系统上，一般是一些存取速度和价格介于高速磁盘与磁带之间的低端磁盘设备。近线存储外延相对比较广泛，主要定位于客户在线存储和离线存储之间的应用。就是指将那些并不是经常用到（例如一些长期保存的不常用的文件归档），或者说访问量并不大的数据存放在性能较低的存储设备上。但对这些设备的要求是寻址迅速、传输率高。因此，近线存储对性能要求相对来说并不高，但又要求相对较好的访问性能。同时多数情况下由于不常用的数据要占总数据量的较大比重，这也就要求近线存储设备在需要容量上相对较大。近线设备主要有SATA磁盘阵列、DVD-RAM光盘塔和光盘库等设备。20分级存储(五)

离线存储离线存储则指将数据备份到磁带或磁带库上。大多数情况下主要用于对在线存储或近线存储的数据进行备份，以防范可能发生的数据灾难，因此又称备份级存储。离线存储通常采用磁带作为存储介质，其访问速度低，但价格低廉的海量存储。21培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志22数据库对象命名规范（总纲）

命名规范总纲:

(一)、所有名称的字符范围为:A-Z,a-z,0-9和_(下划线)，不允许使用其它字符作为名称。

(二)、采用英文单词或英文短语(包括缩写)作为名称，不能使用无意义的字符或汉语拼音。

(三)、名称应该清晰明了，能够准确表达事务的含义，最好可读，遵循”顾名思义”的原则。

(四)、所有标识符（包括表名，字段名，存储过程名，参数名，变量名等）要求简洁，不要用特殊字符，更不要使用保留字。(五)、所有标识符必须以字母开头，而不要以数字开头。(六)、所有数据库对象标识符不要用双引号扩起来。23数据库对象命名规范（表）表命名规范：(一)、不使用TAB,TBL,T作为前缀，本来就是一个表，为什么还要说明(二)、表名以代表表内内容的一个或多个名词组成，以下划线分隔(三)、表命名包括三部分：XXX_YYY_ZZZ，总长度不超过26个字符。XXX代表层次域，YYY代表内容域，ZZZ代表功能模块域(四)、使用表的层次分类作为表名的前缀：如，与事实表相关的表使用前缀FCT_,与短信搜索相关的使用_SMS后缀，后缀表示按功能划分。相同功能模块使用相同的后缀。(五)、一些表作为多对多连接的表，可以使用两个表的内容做为表名，后缀以_REL结尾，主要应用于OLTP数据库。(六)、当系统中有一些少量的，重复出现的值时，使用字典表来节约存储空间和优化查询。如地区，系统中的用户类型等。在OLTP中，以DIC_开头，在数据仓库中以DMS_开头。24数据库表分区设计规范：表分区设计规范:(一)、分区只应用于大表。(原则上规定记录数>1,000,000)(二)、关于时间范围的分区一律使用NUMBER数据类型，不要使用VARCHAR2或DATE数据类型,分区类型选择范围分区。之所有不选择VARCHAR2或DATE,一方面是基于存储和运算效率考虑，另一方面是是在分区列同时存储日和月维度数据时，难以分离。(三)、关于时间分区的命名，统一要求日分区：PART_YYYYMMDD，分区字段名称为DAY_ID

月分区：PART_YYYYMM，分区字段名称为MON_ID

周分区：PART_YYYYWW,分区字段名称为WEEK_ID(四)、

尽量少使用复合分区。(五)、由于目前绝大部分存储都是采用RAID或集中存储，要求同一个表放在同一个表空间中，减少维护和方便管理。25数据库对象命名规范（字段名）字段名命名规范：(一)、字段不使用任何前缀，表名代表了一个名称空间，字段前面再加前缀显得罗嗦。(二)、字段名也避免采用过于简单的名称：例如，用户表中，用户名的字段名为user_name要比name更好。(三)、布尔型的字段，以一些助动词开头，更加直接生动：如，用户是否留言has_message,用户是否通过检查is_checked等(四)、入库接口缓冲表，如果接口文件是采用固定列宽分隔的，特别注意字段类型及长度同接口保持一致。(五)、如果接口文件某字段是日期时间类型，数据库也采用相应的数据类型，不要使用字符或数字数据类型。我们要把脏数据拒绝在系统之外，同时也可以发现脏数据，作为数据质量的一部分反馈出来。26数据库对象命名前／后缀（一）前／后缀说明DMS_维表IF_接口表DW_明细数据表DW_M_轻量汇总数据（汇总中间层)FCT_事实表LOG_日志表BUF_中间计算缓冲表_HIS历史数据表_USR用户信息及权限相关信息表_EXP数据导出DM_挖掘相关结果表27数据库对象命名前／后缀（二）PK_主键FK_外键IXU_唯一索引CK_检查约束IX_索引IXBMP_位图索引SEQ_序列VW_视图MV_物化视图FUN_函数28数据库帐户管理类别帐户用途描述管理PERFDB管理数据库管理和监控（取代SYS用户）应用BackGroundUser后台处理ETL处理ForeGroundUser前台应用应用访问,存放维表和事实表DataMining数据挖掘数据挖掘相关对象及过程TempREQUIREPROCESS临时需求处理临时工单，非正式发版工单需求MetaData元数据ETL调度，数据质量等29数据库表空间设计规范表空间设计规范：(一)、每个应用用户对应独立的表空间。每个用户可以使用多个表空间，但每个表空间只能被一个用户使用。(二)、每个应用用户对应独立的临时表空间，必要时可以使用临时表空间组。(三)、在数据仓库中，按层次划分表空间。比方说：

DW层，DWM层，FCT层，DMS层,BUF层，入库接口缓冲表IF层(四)、在超大型数据仓库中，在层次划分表空间的基础上，按功能划分表空间。(五)、按数据规模划分表空间，将个别超大型表划分到单独的表空间中，便于维护和管理。(六)、单个表空间最大空间限制在200G~500G。30培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志31SQL编码规范(一）SQL编码规范：(一)、SQL语句应正确、规范、高效和最优。(二)、同一项目的SQL书写格式应该统一。(三)、应避免写非常复杂的SQL语句。

SQL语句尽可能避免多表联合复杂查询。(四)、SQL语句不应在客户端组织，而应在服务器端组织。(五)、SQL语句的语法应与所使用的数据库相适应(六)、应确保变量和参数的类型和大小与数据库中表数据库列相匹配。(七)、使用SELECT语句时，应指出列名，不应使用列的序号或者用”*”替代所有列名。(八)、使用INSERT语句时，应指定插入的字段名，不应不指定字段名,而直接插入values.32SQL编码规范(二）SQL编码规范：

(九)、如果SQL语句连接多表时，应使用表的别名来引用列。示例：SELECTlist.manifest_no,list.bill_no,stat.list_stat

FROMmft_list

list,list_statstat

WHERElist.manifest_no=stat.manifest_noANDlist.bill_no=stat.list_no;(十)、SQL语句应避免对大表做全表扫描操作，对大表的操作应尽量使用索引或建立分区表。(十一)、SQL语句应避免不必要的排序操作。

(十二)、SQL语句应避免删除全表的操作，例如:DELETEFROMEMP;33SQL编码规范(三)SQL编码规范：

(十三)、应使用绑定变量实现SQL语句共享，避免使用硬编码。

示例：//红色字体表示书写错误；蓝色字体表示书写正确正确编码：SELECTemp_nameFROMempWHEREemp_no=:B1;//Bindvalue:123

错误编码：SELECTemp_nameFROMempWHEREemp_no=123;34SQL编码规范(四)SQL编码规范：(十四)、SQL语句的大小写(1).SQL语句中出现的所有表名，表别名，字段名，序列等数据库对象都应小写。示例：//红色字体表示书写错误；蓝色字体表示书写正确SELECTlist.manifest_no,list.bill_no,STAT.list_stat

FROMmft_listlist,list_statSTAT

WHERElist.manifest_no=STAT.manifest_noANDlist.BILL_NO=STAT.list_no;ALTERSEQUENCEMANIFEST_NO

CACHE200;SELECTmanifest_no.nextvalFROMDUAL;35SQL编码规范(五)(十四)、SQL语句的大小写(2).SQL语句中出现的系统保留字，SQL保留字都应大写//红色字体表示书写错误；蓝色字体表示书写正确SELECTlist.manifest_no,list.list_no,stat.list_stat

from

mft_listlist,list_statstat

wherelist.manifest_no=stat.manifest_noANDlist.bill_no=stat.list_no;INSERTINTOemployees(employee_name,date,employee_age)VALUES(employees_seq.nextval,‘John’,SYSDATE,30）；36SQL编码规范(六)(十五)、SQL语句的换行

(I).SELECT/FROM/WHERE/ORDERBY/GROUPBY等子句独占一行。

(2).SELECT子句内容如果只有一项，应与SELECT同占一行

(3).SELECT子句内容如果多于一项，若一行可以全部显示，则同占一行，否则后续的项同上一行的第一项对齐。行总长度在100到130内为宜（包括缩进字符在内)

示例：//SELECT语句书写的正确示例SELECTbill_noFROMmft_list

WHEREmanifest_no=‘000000000000000007’;SELECTlist.manifest_no,list.list_no,stat.list_statFROMmft_listlist,list_statstatWHERElist.manifest_no=stat.manifest_noANDstat.stat!=2;37SQL编码规范(七)SQL编码规范：(十六)、应尽量减少对数据库的访问次数，减少交互，降低服务器资源开销。(十七)、.应使用DECODE函数避免重复扫描相同记录或者重复连接相同的表示例：//一般情况下，效率较低。

SELECTCOUNT(*),SUM(salary)

FROMemp

WHEREdept_no=‘0020’;

***************************************************************************************

SELECTCOUNT(*),SUM(salary)

FROMemp

WHEREdept_no=‘0030’;

***************************************************************************************

SELECT

COUNT(DECODE(dept_no,‘0020’,‘X’,NULL))

d0020_count,

COUNT(DECODE(dept_no,‘0030’,‘X’,NULL))

d0030_count,

SUM(DECODE(dept_no,‘0020’,salary,NULL))

d0020_sal,

SUM(DECODE(dept_no,‘0030’,salary,NULL))

d0030_sal

FROMemp;

其中：X表示任何字符或字符串。类似的，DECODE还可用于GROUPBY和ORDERBY子句中。38SQL编码规范(八)SQL编码规范：(十八)、SQL语句中变量的命名。

(1).SQL语句中出现的变量参数命名应反映变量的实际意义。

(2).SQL语句中的表别名应简短明了，应反映表名的实际意义。特别是在表比较多的SQL语句中。但单个SQL语句中表数目<=5,则使用a,b,c,d,e别名简化命名。(十九)、SQL语句的缩进

(1).如果一行有多列并超过120个字符，基于对齐规则，应采用下行缩进。

(2).缩进应为3个字符。

(3).同层次的SQL语句缩进应保持一致（纵向对齐）示例：

SELECTfirst_name,last_name,salary,birthday,hire_date,depart_ment,job,sex

FROMemployees

WHERE

empid=12;39SQL编码规范(九)SQL编码规范：(二十)、INSERT/VALUES缩进规范。示例：

INSERTINTOlist_stat(list_no,list_stat,parent,

manifest_no,div_flag)VALUES('bill020','1','0','000000000000007807','0');(二十一)、单条SQL文中不应出现空行或注释

SELECTDMS_TIME.DAY_NAME,DMS_AREA.ALL_NAME,

SUM(FCT_ACCESS.LOGIN_COUNT),SUM(FCT_ACCESS.ACCESS_COUNT),

中间出现空行

SUM(FCT_ACCESS.ACCESS_TIMES)

FROMDMS_TIME,DMS_AREA,FCT_ACCESS,DMS_SERVICE_TYPE_V

WHERE(DMS_TIME.DAY_ID=FCT_ACCESS.DMS_TIME_ID)AND(FCT_ACCESS.DMS_AREA_ID40SQL编码规范(十)SQL编码规范：(二十二)、在SQL中，对表的访问次数越多，带来的性能成本越高，产生的磁盘IO越大。因此，减少IO访问是提升数据库性能的关键手段之一。如下例：

低效SQL:UPDATEEMPSETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;

高效SQL:UPDATEEMPSET(EMP_CAT,SAL_RANGE)=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;41SQL编码规范(十一)SQL编码规范：(二十三)、Having的使用规范在SQL中，会需要使用having来做是否判断，但应该避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销相关规则如下：Having的语句只应用在需要判断count汇总的值是否满足条件。不能用来代替where条件作为判断，例如：

低效SQL:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGION!=‘SYDNEY’ANDREGION!=‘PERTH’

高效SQL:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’GROUPBYREGION42SQL编码规范(十二)SQL编码规范：(二十四)、truncate/delete

对数据库表数据进行删除操作，如果没有任何条件的全部删除，使用delete的话，数据库会把被删除的数据库放入rollbacksegment中，这样既占用Rollback空间，也降低了性能。而Truncatetable的操作，不需要对数据进行丢失保护，从而具有更好的性能，因此：在删除全部表数据的情况下，使用truncate来代替delete

如果这种操作在一个大的流程中，需要确保其他的流程处理都正常结束之后再做这个操作,因为truncate是不需要commit的一种自动提交操作，同样也无法rollback；因此要把握好使用的时机。43SQL编码规范(十三)SQL编码规范：(二十五)、in/notinexists/notexists使用规范在SQL中，会用的IN/NOTIN的操作，用来判断相关数据是否存在于其他表中，需要遵循规则如下：除了whereemployee_namein(‘abc’,’cde’)这样的指明的常量（或者传入参数的变量）以外，都不要使用in的操作，而应该使用exists来代替in。例如：whereemployee_idin(seleetemployee_idfromps_payment_history

pphwherepph.payment_month=‘200912’);

应该修改为：whereexists(selct

pph.employee_idfromps_payment_history

pphwherepph.employee_id=emp.employee_idandpph.payment_month=‘200912’);

同样的原理，使用notexists代替notin的操作44SQL编码规范(十四)SQL编码规范：(二十六)、应将SQL语句中的数据库函数、计算表达式等放置在等号右边。(二十七)、应按照业务需要使用事务，同时应保持事务简短，避免大事务。(二十八)、在事务完整性的基础上，SQL语句应在程序中显示使用COMMIT,ROLLBACK,

尽快提交事务，释放系统资源。(二十九)、

SQL语句应避免频繁引起数据库事务回滚。(三十)、

SQL语句内的算术运算符、逻辑运算符（AND、OR、NOT)、比较运算符(=、<=、>、>=、>、<、<>、BETWEENAND)、IN、LIKE等运算符前后都应加一空格。(三十一)、不等于应统一使用符号”<>”，不要使用”!=“(三十二)、如果业务规则允许结果集不需要唯一确定，应使用UNIONALL替代UNION45培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志46PL/SQL编码规范(一)PL/SQL编码规范：(一)、存储过程名称标识：前缀_名称_后缀前缀表示功能模块名称偏向输出后缀表明动作：INS(插入),UPD(更新),EXP(导出)，PROC(处理)(二)、所有外部ETL调度接口以PROC接尾，其它过程不得使用_PROC结尾。(三)、存储过程的参数命名

(1)、形参统一以p_开头

(2)、过程内变量以v_开头

(3)、参数类型请尽量不要使用引用类型，减少不必要的伪依赖。比方说emp%rowtype,

emp.dept_id%type

(4)、带默认值的参数，要求放在参数列表最后。47PL/SQL编码规范(二)PL/SQL编码规范：(四)、空格、缩进和空行规范花要求

(1)、空格比较操作符左右各一个空格赋值操作符左右各一个空格嵌套结构，换行后缩进三个空格

(2)、缩进嵌套结构，换行后缩进三个空格同一层级的语句要纵向对齐（BEGIN,EXCEPTION,END;IF,ELSIF,ELSE,ENDIF;)

(3)、空行相对独立的逻辑块间加空行包中各子程序间加空行48PL/SQL编码规范(三)PL/SQL编码规范：(五)、PLSQL程序规范：代码对齐规范使用良好的对齐规范，可以明显的增强程序的可读性、以及程序的美观程度，同时可以减少程序出错的概率，增强可维护性。总体来说，对齐要基于程序层次情况，使用首尾对齐原则，如下例所示：FUNCTIONGET_EMPLOYEE_SALARY(p_employee_idNUMBER,p_monthNUMBER)RETURNNUMVERISV_SALARYNUMBER;CURSORCUR_EMPLOYEE_PAYROLL_ELEMENTISSELECTpayroll_element_code,base_salaryFROMps_payroll_elements

ppe

WHEREppe.EMPLOYEE_id=p_EMPLOYEE_idANDppe.payment_month=p_month;BEGINV_SALARY:=0;FORC1INCUR_EMPLOYEE_PAYMENT_ELEMENTLOOPIFC1.base_salaryisnotnullthenV_SALARY:=V_SALARY+C1.base_salary;

ENDIF;

ENDLOOP;RETURNV_SALARY_AMOUNT;END;49PL/SQL编码规范(四)PL/SQL编码规范：(六)、优良的程序结构

良好的程序结构，可以使程序有一个好的性能基础，另外也能让后续的变更更加灵活。基本的结构概念如下：

(1)、小步快跑原则。尽量不要使用一个单一的超级大超级复杂的SQL代码去实现复杂的功能。

(2)、结构简单清晰。尽量是程序的结构看起来易懂，清晰，简单。不要设计只有自己才能看得懂的程序。

(3)、变量定义不要过多，不要太多重复，尽量使用可以重用的变量(绑定变量可以提高性能)。

(4)、程序结构严谨，过程有输入参数就需要有输出参数返回状态，有正确完善的异常处理机制。

(5)、增加程序的重用性，对于使用次数多的Procedure,Function，尽量做到能够共享，且有好的运行性能。

(6)、对于大批量数据处理的程序，良好的COMMIT/ROLLBACK及时提交机制。

(7)、对于取出的大批量数据，尽量保存在内存中，不要多次重复读取大批量数据。

(8)、适当使用临时表作为数据中转池。50PL/SQL编码规范(五)PL/SQL编码规范：(七)、若无特别需要，不要或少使用游标。(八)、若无特别需要，不要使用或少使用动态SQL.(九)、开发阶段以功能实现和满足业务需求为目标，要求快速编码，高效完成开发功能目标。(十)、每一存储过程均应记录执行存储过程的日志信息。必须调用专用写日志的存储过程，同时有Exception的异常处理机制。(十一)、存储过程修改时，应注释清楚修改人，修改日期，修改原因及修改内容。51PL/SQL编码规范(六)

PL/SQL编码规范：(十二)、SQL语句中出现的常量函数，如果不影响业务规则，要求用变量替换，减少不必要的函数计算。

DECLAREv_dateDATE;BEGINv_date:=sysdate;

INSERTINTOemployees(employee_name,date,employee_age)

SELECTname,

sysdate,ageFROMemp；

END;使用v_date替换掉不确定函数sysdate52PL/SQL编码规范(七)PL/SQL编码规范：(十三)、内部块和子程序

(1)、过程很长时，推荐写内部块来提高可读性

(2)、用很多重复的代码时，推荐写成子程序

(3)、动态sql避免使用拼sql的方式。推荐使用绑定变量

(4)、要清楚明白地使用列名，而不能使用列的序号53PL/SQL编码规范(八)PL/SQL编码规范：(十四)、注释编码规则(1)、对较为复杂的SQL语句应注释，并说明算法和功能。(2)、注释应单独成行，并放在语句前面(3)、应对不易理解的分支条件表达式加注释(4)、对重要的计算应说明其功能(5)、过长的函数实现，应将其语句按实现的功能分段加以概括性说明对常量或变量注释时，应注释被保存值的含义，宜包括合法取值的范围。54PL/SQL编码规范(九)PL/SQL编码规范：(十五)、开发过程中，尽量保证设计文档和数据库物理表结构一致变化。资源允许的情况下，安排专人负责数据库设计、文档管理和数据库同步更新。(十六)、尽量不要使用触发器，不便于调试，跟踪和监控。(十七)、相邻两层要求遵循最基本的守恒原则。比方说：STAGE1:FTP文件下载,下载前后文件大小相同或误差在10%以内。STAGE2:数据装载，接口文件总记录数＝入库记录数+丢弃记录数+错误记录数

STAGE3:调度守恒，每天所有的调度过程都被调度，没有某个过程调度缺失55PL/SQL编码规范(十)PL/SQL编码规范：(十八)、每个过程代码行控制在200到250行以内，不要太长。(十九)、避免每个字段单独占一行。(二十)、每个过程需要注释，包括：作者，开发日期，功能简单说明，调用示例等。(二十一)、所有过程都可以重复调度，在原始数据没有变化的情况下，应该产生同样的输出。(二十二)、入库接口表在该接口处理过程成功调度后，方可清除。其它任何情况下不可清除。(二十三)、在数据仓库中，DW到DW_M层次结构中，若非必要，不要增加保留或自增序列字段。56培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志57索引优化规则(一)索引优化规则：(一)、索引的建立应慎重考虑，不是越多越好，索引可以提高相应的SELECT的效率，但同时也降低了INSERT、UPDATE和DELETE的效率。(二)、被查询列有大量重复数据时，如状态标志，可考虑建立位图索引（仅限于DW环境).(三)、在业务允许的情况下，查询列、排序列与索引列的次序保持一致。(四)、应避免在WHERE子句索引列上使用函数或者表达式。示例如下：

CREATEINDEXix_emp_hire_dateONemp(hire_date);select*fromemp

whereto_char(hire_date,’yyyymmdd’)=‘20110101’;where子句应修改为：wherehire_date>=to_date(‘20110101’,’yyyymmdd’)andhire_date<to_date(‘20110102’,’yyyymmdd’)58索引优化规则(二)索引优化规则：(五)、索引列的比较应尽量避免使用<>,NOT(六)、应尽量避免在WHERE子句中对索引列使用LIKE‘%xxx%’,’%xxx’(七)、可在WHERE子句中对索引列使用LIKE‘xxx%’(八)、应尽量使用与索引列数据类型一致的比较值。避免索引列值进行数据类型隐式转换，容易导致不必要的运行时错误和错误的执行计划。在WHERE子句中应注意比较值与索引列数据类型的一致性，应显示转换比较值使其与索引列数据类型保持一致。(九)、对于复合索引，WHERE子句中必须包含索引的前导列，才能保证使用到索引。例如：

CREATEINDEXix_emp_idONemp(emp_id,hire_date,dept_id);

其主导列是(1).emp_id(2).emp_id,hire_date(3).emp_id,hire_date,dept_id59索引优化规则(三)索引优化规则：(十)、排序应尽量建立在索引列上

(十一)、关于区间比较的列放在所有列的最后。例如：wheredept_no=10andhire_datebetween:d1and:d2;

错误的索引：

createindexIX_empONemp(hire_date,dept_no);

正确的索引：

createindexIX_empONemp(dept_no,hire_date);

60索引优化规则(四)索引优化规则(十二)、对于已经存在Index的表，如果程序语句写法不对,会使得可以使用的Index无法使用，具体如下：

(1)、在Where条件中不要对Index字段进行类型转换处理，例如：

to_char(employee_number)=‘12345’,

这样的写法会让Index无效。如果一定需要做转换，应该转换为：employee_number=to_number(‘12345’)

类似的转换还有：to_number,to_date,trunc

(2)、在Where条件中，不要对Index字段进行数据附加操作，例如：whereaction_date+1=to_date(‘2009-01-01’,’yyyy-mm-dd’)

而应该修改为：whereaction_date=to_date(‘2009-01-01’,’yyyy-mm-dd’)-1

61索引优化规则(五)索引优化规则(十二)、对于已经存在Index的表，如果程序语句写法不对,会使得可以使用的Index无法使用，具体如下：

(3)、在where条件中，不要对Index字段进行!=操作（不等于操作），因为Index只记录了有什么，而没有记录没有什么，例如：wheresalary_amount!=0

应该修改为：wheresalary_amount>0

(4)、在where条件中，不要对两个index字段做拼合操作，例如：whereemployee_id||employee_no=‘123’||’C001’

应该修改为：whereemployee_id=123andemployee_no=‘C001’62索引优化规则(六)索引优化规则：(十三)、禁用不想使用的索引如果出现SQL会使用到多个INDEX，而被选择的INDEX不是最优选择的时候，可以使用强制禁用不需要的INDEX的办法来迫使系统选择你希望的INDEX。例如：Select*fromps_employee

whereaction_date>sysdate-365anddept_id=111;

如果发现使用action_date这个index的性能没有dept_id这个index好，那么可以修改成select*fromps_employeewhereaction_date+0>sysdate–365anddept_id=111

通过强制屏蔽action_date这个index被使用，就可以迫使系统选择dept_id作为index来运作。

关于索引的更多信息，我们将在数据库中级培训（执行计划)第８讲中专题详细讲述。63培训大纲一、目的和范围二、数据仓库体系架构三、分级存储（数据生命周期）四、数据库对象命名规范五、SQL编码规范六、PL/SQL编码规范七、代码优化规则八、日志64日志接口设计(一)日志价值及分类：

大家知道，系统出问题了，我们第一步干什么，检查系统日志。由此可见，日志对于我们有多重要。在整个数据仓库体系架构中，我们又需要输出哪些日志呢？数据仓库日志主要按照数据仓库体系层次架构分为以下几类：

(1)、上游FTP文件的到达状态，已成功上传完成文件数，已成功下载文件数，文件大小，文件下载开始时间,文件下载结束时间，FTP传输速率，文件到达时间，文件记录数等信息。

(2)、数据加载日志可以告诉我们，加载开始时间，加载结束时间，加载用时，成功入库记录数，丢弃记录数，错误记录数，入库后使用空间等信息.(3)、ETL作业调度日志可以告诉我们：当前运行状态，作业执行状态，成功／失败、错误消息，开始时间，结束时间，执行用时，估计用时，客户端IP，当前已完成哪些任务，还有哪些任务没有完成，估计所有任务完成时间等信息.65日志接口设计(二)日志价值及分类：

数据仓库日志主要按照数据仓库体系层次架构分为以下几类：

(4)、过程处理日志，包括处理过程名称，输入参数及值，影响记录数，执行开始时间，结束时间，运行状态（成功/失败/警告），错误消息，执行节点，客户端IP,客户端应用程序等信息。(5).数据导出监控日志，确保导出成功并正确分发，包括导出开始时间，结束时间，导出文件名，导出记录数，导