Oracle存储过程开发规范与技巧

1、1 1 / / 7171存储过程开发规范与技巧开发规范1.书写规范1)：程序头书写规范程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。示例如下：/*名称及实现功能 :版本: ( 版本号标示：新建 V1.0.0 小的修改变为 V1.0.1 大的修改V1.1.0 重构 V2.0.0)修改原因 :修改原因 :Create byCreateDate 2006- 06- 29Update byupdateDate 2006- 06- 30Update byupdateDate 2006- 06- 312 2 / / 7171涉及的表或视

2、图：dump_init辅助表(DM):记录存储过程中使用的物化视图日志序号mlog$_acrcusmrsecindex源表(ODS):客户第一索引物化视图日志, 使用同义词ft_gld_customerdata目标表(DM)：客户事实表*/CREATEORREPLACEPROCEDURE *2)：代码书写规范1.语句中出现的所有表名、 字段名全部小写， 系统保留字、 内置函数名、 Sql 保留字大写。2.连接符 or、in、and、以及=、=、=等前后加上一个 空格。3.where 子句书写时， 每个条件占一行， 语句令起一行时， 以保留字或者连接符开始，连接符右对齐。4.查询的 WHER 过

3、滤，原则应使过滤记录数最多的条件放在最前面。5.多表连接时，使用表的别名来引用列。6.查找数据库表或视图时，只能取出确实需要的那些字 段，不3 3 / / 7171要使用 * 来代替所有列名。7.功能相似的过程和函数， 尽量写到同一个包中， 加强管 理。示例如下：BEGIN- 查询员工及对应的部门名称SELECTemp. name, FROMl_dept dept , l_employee empWHEReEmp. dept_id = dept . dept_id ;END;3）注释书写规范为了提高可读性， 应该使用一定数量的注释。 注释大约占总行数的 1/51：注释风格：注

4、释单独成行、放在语句前面。2：应对不易理解的分支条件表达式加注释；3：对重要的计算应说明其功能；4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；5:每条 SQL 语句均应有注释说明4 4 / / 71716:对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（-或/* */ 方式）2.命名规范命名对象规则样例存储过程、包、方法1 1 业务相关以模块代码开头gld_assist_check_p2 2 如果区分全量和增量，在取后加标识gldo ad_to_etl gld load to etl full3 3 全局使用，以 globalglobal 开头global_p

5、rocedure_check变量以 V V 开头v_updatemode1游标以 c c 开头c_tablist内存表以 m m 开头m_table1临时表以 t t 开头t_tmpTable存储过程技术1 .存储过程样例CREATEORREPLAC 田 ROCEDUFeXample （v_inputINNUMBER-输入参数v_outputOUTNUMBER -输出参数）ISPRAGMAUTONOMOUS_TRANSACTIONCURSOR1-定义一个游标，在 begin 之前ISSELECTb . tablename mlogtableMAX( remarks )KEEP (DENSE_R

6、ANKLAST ORDERBY5 5 / / 7171starttime )remarksFROMproc_log a , table_proc bWHERTEO_CHAR( starttime , yyyy-mm-dd ) = - 转换时间并做比较TO_CHAR( SYSDATE - TO_DSINTERVAL(TO_CHAR( intervaldays ) | 00:00:00 ), yyyy-mm-dd )ANDa . remarks LIKE SUCCEEDED:%ANDa . procedurename = b . procedurenameGROUPBYb . tablename

7、); - 定义结束c1_rec c1 %ROWTYPE; - 定义接受游标数据行的ROWTYPEv_mlogtableVARCHAR( 30);v_postperiodCHAR( 2);v_acctbalbeginseq NUMBE;Rv_systimeDATE;6 6 / / 7171BEGINv_input := 0; v_systime := SYSDATE;OPENc1 ; - 打开游标LOOP - 循环FETCHc1 INTOc1_rec ; - 从当前游标行赋值 c1_recEXIT WHENc1 %NOTFOUND-;- 游标没有数据退出 v_mlogtable :=c1_rec

8、 . mlogtable ; - 从行取出具体数 据赋给变量CASETRIM (LOWER v_mlogtable )-CASE 起始THEN- 变量赋值WHENString1THENBEGINv_remarksEND;WHENString2- 当条件一- 做条件一工作:= REPLACE(v_remarks , AA );- 当条件二7 7 / / 7171BEGINEND;ELSE- 其他条件NULL;ENDCASE;-CASE 结束IF ( LOWER( SUBSTR( v_mlogtable , 1, 5) mlog$ )THENSELECTlog_tableINTOv_mlogtableFROMuser_snapshot_logsWHERLEOWER(MASTER)= LOWER( v_mlogtable );ENDIF;EXECUTEIMMEDIATEdelete from | v_mlogtable- 循环结束| where sequence$ =| TO_CHAR( v_lognum);- 循环跳出条件EXITWHENDLOOP;8 8 / / 7171CLOSEc1 ;- 关闭游标EXCEPTION