oracle开发总结

打造中国金融IT服务业第一品牌1ORACLE研发总结（设计架构篇）(1).数据库分区一般情况下，数据仓库或者数据集市可根据数据用途不同在逻辑上分为若干个区，即多个不同的用户。如信贷风险管理系统(CRMIS)就分为CRMIS_SDW、CRMIS_BDW、CRMIS_MDW和CRMIS_ADW等四个区，分别存放源数据，基础区数据，多维区数据和应用区数据；中行对账系统（CASS）则把数据分为CASS1和CASS2分别存放后台和前台数据。(2).表空间设计为了清晰地划分数据，我们通常会为每个数据库区分别建立一个或若干个数据表空间和索引表空间，对数据库的管理和扩展大有裨益，对提高数据扫描效率也有帮助。(3).表分区与维护将大表（500万以上的记录数）的数据及其索引通过分区（patition）的形式分割为若干较小、可治理的分区（小块），并且每一分区可进一步划分为更小的子分区（subpartition）。而这种分区对于应用来说是透明的。通过对表进行分区，有以下的好处：1）减少数据损坏的可能性。2）各分区可以独立备份和恢复，增强了数据库的可治理性。3）可以控制分区在硬盘上的分布，以均衡IO，改善了数据库的性能一般分为范围分区，列表分区，哈希分区，复合分区（特别是范围列表分区）等--------附1:范围分区表和范围列表分区表自动维护分区参考代码:打造中国金融IT服务业第一品牌2ORACLE研发总结（表分析）（1）命令方式：analyzetabletable_namecomputestatistics;（2）工具包方式：execdbms_stats.gather_table_stats(ownname=‘user_name',tabname='table_name',estimate_percent='45',cascade=true,force=true);注意truncate表时会回收空闲空间，--------附2:自动做表分析参考代码:对经常做DELETE操作的表定期做表分析，以降低其HWM，可使用的两种方式：打造中国金融IT服务业第一品牌3ORACLE研发总结尽量避免行链接和行迁移行链接(Rowchaining)当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就会发生行链接。oracle会使用与该块链接的一块或多块数据块来容纳该行的数据。行连接经常在插入比较大的行时才会发生，如包含long,longrow,lob等类型的数据。在这些情况下行链接是不可避免的。行迁移(RowMigration)当修改不是行链接的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲空间已经比较小而不能完全容纳该行的数据时，就会发生行迁移。主要是由于设置的PCTFREE参数过小，导致没有给update操作留下足够的空闲空间引起。为了避免行迁移，所有被修改的表应该设置合适的PCTFREE值，以便在每个数据块内为数据修改保留足够的空间。可以通过增加PCTFREE值的办法来避免行迁移，但这种解决办法是以牺牲更多的空间为代价的，这也就是我们通常所说的以空间换效率。而且通过增加PCTFREE值的办法只能缓解行迁移现象，而不能完全解决行迁移，所以较好的办法是在设置了合适的PCTFREE值的后，在发现行迁移现象比较严重时，对表的数据进行重组。打造中国金融IT服务业第一品牌4ORACLE研发总结EXECUTEIMMEDIATE的用法1.EXECUTEIMMEDIATE不会提交一个DML事务执行，应该显式提交，如果通过EXECUTEIMMEDIATE处理DML命令，那么在完成以前需要显式提交或者作为EXECUTEIMMEDIATE自己的一部分；如果通过EXECUTEIMMEDIATE处理DDL命令,它提交所有以前改变的数据2.不支持返回多行的查询,这种交互将用临时表来存储记录或者用REFcursors.3.当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.4.给动态语句传值(USING子句)5.从动态语句检索值(INTO子句)6.动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定7.将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量8.传递并检索值.INTO子句用在USING子句前9.多行查询选项.对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REFcursors纠正此缺憾.打造中国金融IT服务业第一品牌5ORACLE研发总结关于批量重跑由于数据、程序或者调度配置有误等原因，批量跑错，往往要求重跑，一个良好的系统应该具备容错纠错能力，根据程序类型指定如下重跑方案：1.历史拉链数据根据begin_date和end_date支持重跑；首先删除begin_date大于等于要重跑批量日期的数据，然后更新end_date小于等于要重跑批量日期的end_date为‘99991231’；注：begin_date和end_date即该笔数据的起始日期和结束日期，在这两个日期内数据是有效的。2.merge数据根据add_date和mod_date支持重跑.若要重跑需删除add_date大于等于要重跑批量日期的数据。这样才能保证不会有多余的数据。注：add_date和mod_date即该笔数据的最初添加日期和最近修改日期。打造中国金融IT服务业第一品牌6ORACLE研发总结(函数)常用函数nvl(),nvl2(),--空值判断函数substr(),sunstrb(),--字符串截取函数length(),lengthb(),--字符串长度函数instr(c1,c2[,n1[,n2]]),instrb(),--字符串定位函数trim([[leading||trailing||both]c2from]c1),--去掉前后指定的字符，默认为空格ltrim(),rtrim()，--去掉前或后指定的字符，默认为空格replace(c1,c2[,c3]),translate(c1,c2,c3)--替换函数，绝对替换ceil(),floor(),round(),trunc()，--取上、取下函数，四舍五入函数，截取函数，power(),exp(),mod(),--数学函数greatest(),least(),--取序列中的最大值，最小值函数add_months(),last_day(),next_day()--有用的日期函数nullif(c1,c2)--逻辑等价于：CASEWHENc1=c2THENNULLELSEc1END打造中国金融IT服务业第一品牌7ORACLE研发总结(函数)LPAD(c1,n[,c2])返回指定长度=n的字符串，需要注意的有几点：如果nc1.length则从右到左截取指定长度返回；如果nc1.lengthandc2isnull，以空格从左向右补充字符长度至n并返回；如果nc1.lengthandc2isnotnull，以指定字符c2从左向右补充c1长度至n并返回；RPAD(c1,n[,c2])返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；DECODE(exp,s1,r1,s2,r2..s,r[,def])SYS_CONNECT_BY_PATH(col,c)--该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connectby子句共同使用。WM_CONCAT()--这个函数很有用啊，可用于展示不在分组列中的字段。SYS_CONTEXT(c1,c2[,n])--将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。其拥有CURRENT_SCHEMA，CURRENT_USER，IP_ADDRESS，OS_USER等内置参数打造中国金融IT服务业第一品牌8ORACLE研发总结(函数)示例：返回某个日期所在月的天数：to_char(last_day(to_date('20110212','yyyymmdd')),'dd')返回上月最后一天：to_char(add_months(last_day(sysdate),-1),‘yyyymmdd')返回上月第一天：add_months(trunc(SYSDATE,'MM'),-1)返回当年所有的星期六、星期日的日期SELECTTO_CHAR(B.A,'day')the_DAY,TO_CHAR(B.A,'YYYY-MM-DD')ASthe_DATEFROM(SELECTTRUNC(sysdate,'y')+ROWNUM-1AFROMDBA_OBJECTSWHEREROWNUM=add_months(TRUNC(sysdate,'y'),12)-TRUNC(sysdate,'y'))BWHERETO_CHAR(B.A,'d')IN('1','7');打造中国金融IT服务业第一品牌9ORACLE研发总结(分析函数)排序分析函数：row_number()over(partitionby...orderby...)--从1到N排序自然，不管是否重复rank()over(partitionby…orderby...)--从1到N自然排序，重复的具有相同排序，会空出排序位置dense_rank()over(partitionby...orderby...)--从1到N自然排序，重复的具有相同排序，不会空出排序位置（注：空值处理可用排序关键字：NULLSLAST/FIRST进行处理，告诉空值排序的位置）first/last排名查询示例：（求薪水总额最高和最低的部门）selectmin(t.deptno)keep(dense_rankfirstorderbysum(t.sal)desc)ASthe_first,min(t.deptno)keep(dense_ranklastorderbysum(t.sal)desc)ASthe_lastfromscott.emptgroupbyt.deptno;Ntile(n)层次排名函数，把数据分为n个等级，示例：按薪水高低把员工分为5个等级SELECTntile(5)over(ORDERBYt.salDESC)ASntile,t.*FROMscott.empt打造中国金融IT服务业第一品牌10ORACLE研发总结(分析函数)自动汇总分析函数rollup()示例：SELECTnvl(t.job,'总计'),sum(t.sal)ASsalfromscott.emptgroupbyrollup(t.job);--对不同工作类别汇总其薪水cube()多个字段分组的汇总，相当于rollup的改进版打造中国金融IT服务业第一品牌11ORACLE研发总结(分析函数)lead()和lag()函数Lead(col,n)over(partitionby…orderby…)lag(col,n)over(partitionby…orderby…)功能：在同一行中显示前n行或后n行的数据的数据。ratio_to_report()函数ratio_to_report(col)over(partitionby…)功能：各项在总计中所占的比例算术统计分析函数sum(col)over(partitionby..)avg(colover(partitionby..)count(*)over(partitionby..)max(col)over(partitionby..)min(col)over(partitionby..)功能：在指定组中求和，求平均，求总数，最大值，最小值打造中国金融IT服务业第一品牌12ORACLE研发总结(分析函数)first_value()和last_value()函数first_value(col)over(partitionby…orderby..)last_value(col)over(partitionby…orderby..)功能：用于在窗口记录集中查找第一条记录和最后一条记录假设我们的报表需