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

存储过程开发规范与技巧开发规范1.书写规范1）：程序头书写规范程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。示例如下：-----------------------------------------------------------------------------/*名称及实现功能:版本:(版本号标示：新建V1.0.0小的修改变为V1.0.1大的修改V1.1.0重构V2.0.0)Createby***CreateDate2006-06-29Updateby***updateDate2006-06-30修改原因:Updateby***updateDate2006-06-31修改原因:涉及的表或视图：dump_init辅助表(DM)：记录存储过程中使用的物化视图日志序号mlog$_acrcusmrsecindex源表(ODS)：客户第一索引物化视图日志,使用同义词ft_gld_customerdata目标表(DM)：客户事实表*/CREATEORREPLACEPROCEDURE*******------------------------------------------------------------------------------2）：代码书写规范1.语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、Sql保留字大写。2.连接符or、in、and、以及＝、=、=等前后加上一个空格。3.where子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。4.查询的WHERE过滤，原则应使过滤记录数最多的条件放在最前面。5.多表连接时，使用表的别名来引用列。6.查找数据库表或视图时，只能取出确实需要的那些字段，不要使用*来代替所有列名。7.功能相似的过程和函数，尽量写到同一个包中，加强管理。示例如下：BEGIN--查询员工及对应的部门名称SELECTemp.name,dept.nameFROMl_deptdept,l_employeeempWHEREemp.dept_id=dept.dept_id;END;3）注释书写规范为了提高可读性，应该使用一定数量的注释。注释大约占总行数的1/5。1：注释风格：注释单独成行、放在语句前面。2：应对不易理解的分支条件表达式加注释；3：对重要的计算应说明其功能；4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明；5：每条SQL语句均应有注释说明6：对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（--或/**/方式）2.命名规范命名对象规则样例存储过程、包、方法1业务相关以模块代码开头gld_assist_check_p2如果区分全量和增量，在最后加标识gld_load_to_etlgld_load_to_etl_full3全局使用，以global开头global_procedure_check变量以v开头v_updatemode1游标以c开头c_tablist内存表以m开头m_table1临时表以t开头t_tmpTable存储过程技术1．存储过程样例CREATEORREPLACEPROCEDUREexample(v_inputINNUMBER,--输入参数v_outputOUTNUMBER--输出参数)ISPRAGMAAUTONOMOUS_TRANSACTION;CURSORc1--定义一个游标，在begin之前ISSELECTb.tablenamemlogtable,MAX(remarks)KEEP(DENSE_RANKLASTORDERBYstarttime)remarksFROMproc_loga,table_procbWHERETO_CHAR(starttime,'yyyy-mm-dd')=--转换时间并做比较TO_CHAR(SYSDATE-TO_DSINTERVAL(TO_CHAR(intervaldays)||'00:00:00'),'yyyy-mm-dd')ANDa.remarksLIKE'SUCCEEDED:%'ANDa.procedurename=b.procedurenameGROUPBYb.tablename);--定义结束c1_recc1%ROWTYPE;--定义接受游标数据行的ROWTYPEv_mlogtableVARCHAR(30);v_postperiodCHAR(2);v_acctbalbeginseqNUMBER;v_systimeDATE;BEGINv_input:=0;--变量赋值v_systime:=SYSDATE;OPENc1;--打开游标LOOP--循环FETCHc1INTOc1_rec;--从当前游标行赋值c1_recEXITWHENc1%NOTFOUND;--游标没有数据退出v_mlogtable:=c1_rec.mlogtable;--从行取出具体数据赋给变量CASETRIM(LOWER(v_mlogtable))--CASE起始WHEN'String1'--当条件一THEN--做条件一工作BEGINv_remarks:=REPLACE(v_remarks,'AA');END;WHEN'String2'--当条件二THENBEGINEND;ELSE--其他条件NULL;ENDCASE;--CASE结束IF(LOWER(SUBSTR(v_mlogtable,1,5))'mlog$')THENSELECTlog_tableINTOv_mlogtableFROMuser_snapshot_logsWHERELOWER(MASTER)=LOWER(v_mlogtable);ENDIF;EXECUTEIMMEDIATE'deletefrom'||v_mlogtable||'wheresequence$$='||TO_CHAR(v_lognum);EXITWHEN12;--循环跳出条件ENDLOOP;--循环结束CLOSEc1;--关闭游标EXCEPTIONWHENOTHERSTHENROLLBACK;global_procedure_check.check_end('checkdataerror01',v_systime,1,SQLCODE||''||SQLERRM);RAISE;RETURN;END;ENDexample;2．基本知识1)基本结构--------------------------------------------------------CREATEORREPLACEPROCEDUREexample（parameters）--过程声明区IS--------------------------------------------------------v_1NUMBER;--过程中变量声明区--------------------------------------------------------BEGINv_1:=0;--过程内容区ENDexample;--------------------------------------------------------2)基本类型CHAR固定长度字符类型VARCHAR2可变长字符类型VARCHAR可变长字符类型(不建议使用)NUMBER一切数值类型DATE一切日期类型3)参数三种：IN输入参数，OUT输出参数，INOUT输入输出参数。4)变量的声明在变量声明区声明变量的名称和类型例：v_postperiodCHAR(2);可赋初值v_postperiodCHAR(2):=’01’;（这里叫变量声明区可能并不恰当，因为游标、自定义类型等，一切需要事先声明的都应在这里声明。）5)变量的赋值使用‘：=’为变量赋值1．直接使用基本类型赋值例：v_number:=1;2.使用函数赋值例：v_date:=sysdate;3．使用SQL语句为变量赋值1〉通过sql直接赋值SELECTCOUNT(*)INTOv_tmpnumberFROMetl_ods_masterdata_tablist;2〉通过构造SQL赋值：v_tmpsql:='SELECTlog_tableFROMuser_snapshot_logs'||v_dblink||'WHEREUPPER(MASTER)=UPPER('''||v_singletab||''')';EXECUTEIMMEDIATEv_tmpsqlINTOv_tmpvarchar;6)循环1.无限或简单循环LOOPEXITWHEN(退出循环条件);ENDLOOP;2.while循环WHILEconditionLOOPexecutable_statements;ENDLOOP;3.for循环基于数字的for循环：FORfor_indexINlow_value..high_valueLOOPexecutable_statements;ENDLOOP;基于游标的for循环：FORrecord_indexINmy_cursorLOOPexecutable_statements;ENDLOOP;7)调用其他过程或方法1．如果单独定义，直接使用例：v_retval0:=f_dump_init(v_updatemode,v_systime,'mlog$_glddocheader',v_procname,v_docheaderbeginseq,v_docheaderendseq);2．如果定义在包下，使用包名+过程名例：global_procedure_check.check_run(v_procname)；3．固定用法和函数标识作用用法或类型固定用法：SYSDATE当前系统时间DATESQLCODE异常代码VARCHAR2SQLERRM异常描述VARCHAR2NO_DATA_FOUND未找到数据异常与when搭配OTHERS其他所有异常与when搭配RAISE抛出当前异常RAISE;DENSE_RANK非选取字段排序MIN(B)KEEP(DENSE_RANKFIRSTORDERBYA)MAX(B)KEEP(DENSE_RANKLASTORDERBYA)PRAGMAAUTONOMOUS_TRANSACTIONBULKCOLLECTINTOSQL%ROWCOUNT使用自治事务，可以使该过程被调用时单独提交Begin之前使用PRAGMAAUTONOMOUS_TRANSACTION;将前面执行结果大批放入后面的集合中BULKCOLLECTINTOcolumntab;前一个DML语句执行影响行数作为NUMBER型使用v_number:=SQL%ROWCOUNTDBMS_OUTPUT.put_line（）输出信息函数TO_CHAR转换NCHAR、NVARCHAR2、CLOB、NCLOBTO_CHAR（A）转换DATE型为指定格式TO_CHAR(time,'yyyy-mm-dd')转换NUMBER型为指定格式TO_CHAR(564.70,'$999.9')TO_DATE转换字符串为指定日期to_date('1900-01-01','YYYY-MM-DD')INSTR(string,substring(,postion)(,occurrence))返回目标字符串中子字符串的位置。（起始位置和出现次数为可选）INSTR('bug-archie','archie')INSTR('haracter?archie','a',1,2)LENGTH获得指定字符串长度LENGTH('CANDIDE')LOWER将指定字符串转换成小写LOWER('LETTERS')UPPER将指定字符串转换成大写UPPER('letters')LPAD(str1,n,str2)将str1用str2左补齐至n位LPAD('55',10,'0')RPAD(str1,n,str2)将str1用str2右补齐至n位RPAD('55',10,'0')LTRIM去