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

《Oracle存储过程开发规范与技巧6579.docx》由会员分享，可在线阅读，更多相关《Oracle存储过程开发规范与技巧6579.docx（65页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、或视图图：dump_innit 辅助表(DMM)：记录存存储过程中使使用的物化视视图日志序号号mlog$_aacrcussmrseccindexx 源表(ODSS)：客户第第一索引物化化视图日志,使用同义词词ft_gld_custoomerdaata 目标表(DMM)：客户事事实表*/CREATE OR REPLAACE PROCEEDURE *-2）：代码书写写规范1. 语句中出现的所所有表名、字字段名全部小小写，系统保保留字、内置置函数名、SSql保留字字大写。 2. 连接符or、iin、andd、以及、=等等前后加上一一个空格。3. where子句句书写时，每每个条件占一一行，语句令令起

3、一行时，以以保留字或者者连接符开始始，连接符右右对齐。4. 查询的WHERRE过滤，原原则应使过滤滤记录数最多多的条件放在在最前面。 5. 多表连接时，使使用表的别名名来引用列。6. 查找数据库表或或视图时，只只能取出确实实需要的那些些字段，不要要使用*来代代替所有列名名。7. 功能相似的过程程和函数，尽尽量写到同一一个包中，加加强管理。 示例如下下：BEGIN -查询员工工及对应的部部门名称 SSELECTT emp.name, deptt.name FROMM l_deppt deppt, l_empployeee emp WHEREE emp.dept_id = deptt.dept_i

4、d;END;3）注释书写规规范为了提高可读性性，应该使用用一定数量的的注释。注释释大约占总行行数的1/55。1：注释风格：注释单独成成行、放在语语句前面。2：应对不易理理解的分支条条件表达式加加注释；3：对重要的计计算应说明其其功能；4：过长的函数数实现，应将将其语句按实实现的功能分分段加以概括括性说明；5：每条SQLL语句均应有有注释说明6：对于程序的的整体功能，应应在程序开始始部分说明，可可采用单行/多行注释。（- 或 /* */ 方式）2.命名规范命名对象规则样例存储过程、包、方方法1 业务相关以以模块代码开开头gld_asssist_ccheck_p2 如果区分全全量和增量，在在最后加

5、标识识gld_loaad_to_etlgld_loaad_to_etl_ffull3 全局使用，以以globaal开头global_proceedure_checkk变量以 v 开头v_updattemodee1游标以 c 开头c_tabliist内存表以 m 开头m_tablee1临时表以 t 开头t_tmpTaable存储过程技术1存储过程样样例CREATE OR REPLAACE PROCEEDURE exampple ( v_innput IN NUUMBER,-输入参参数 v_ouutput OUT NUUMBER -输输出参数)ISPRAGMA AUTONNOMOUSS_TRANN

6、SACTIION; CURSSOR c11-定义一一个游标，在在beginn之前 IS SSELECTT b.tableename mlogttable, MAAX (remarkks)KEEEP (DENSSE_RANNK LAST ORDERR BY staarttimme) remmarks FFROM proc_log aa, tablee_procc b WWHERE TO_CHHAR (startttime, yyyyy-mm-ddd) = -转换时时间并做比较较 TO_CCHAR ( SYSSDATE - TO_DSSINTERRVAL ( TO_CHARR (interrval

7、daays) | 00:000:00), yyyyy-mm-ddd ) AAND a.remarrks LIIKE SUCCCEEDEDD:% AAND a.proceedurenname = b.proceedurenname GGROUP BY b.tableename); -定义结结束c1_rec c1%ROWTYYPE; -定义接接受游标数据据行的ROWTYYPE v_mllogtabble VARCHHAR (30); v_poostperriod CHHAR (2); v_accctballbeginnseq NUUMBER; v_syystimee DAATE;BEGIN v_i

8、nnput := 0;-变量赋赋值 v_syystimee := SYSSDATE; OPENN c1;-打开游游标 LOOPP-循环 FFETCH c1 INTO cc1_recc;-从当前前游标行赋值值c1_reec EEXIT WHEN cc1%NOTTFOUNDD;-游标没没有数据退出出 vv_mloggtablee := c1_rec.mlogttable;-从行取取出具体数据据赋给变量 CCASE TTRIM (LOWERR (v_mloogtablle)-CASSE起始 WHEEN Strinng1-当条件件一 THEEN-做条件件一工作 BEGINN v_remarrks :=

9、 REPLAACE (v_remmarks, AA); END; WHEEN Strinng2-当条件件二 THEEN BEGINN END; ELSSE-其他条条件 NULL; EEND CASE;-CASSE结束 IIF (LOWERR (SUBSTTR (v_mloogtablle, 1, 5) mlogg$) TTHEN SELLECT llog_taable IINTO vv_mloggtablee FFROM uuser_ssnapshhot_loogs WHHERE LLOWER (MASTTER) = LOWEER (v_mloogtablle); EEND IF; EEXEC

10、UTTE IMMEDDIATE ddeletee fromm | v_mloogtablle | wheere seequencce$ 2;-循环跳跳出条件 END LOOP;-循环结结束 CLOSSE c1;-关闭游游标 EXCEEPTIONN WWHEN OTHERRS TTHEN ROLLLBACKK; gloobal_pproceddure_ccheck.checkk_end (checckdataaerrorr01, v_syystimee, 1, SQLCCODE | | SQQLERRMM ); RAIISE; RETTURN; END;END exaample;2基本知识1)

11、基本结构-CREATE OR REPLAACE PROCEEDURE exampple（paarametters）-过程声声明区IS-v_1 NUMBBER;-过程中变变量声明区-BEGIN v_1 := 0;-过程内容容区END exaample;-2) 基本类型CHAR固定长度字符符类型VARCHARR2可变长字符符类型VARCHARR可变长字符符类型(不建建议使用)NUMBER一切数值类类型DATE一切日期类类型3) 参数三种：IN 输输入参数，OOUT输出参参数，IN OUT 输输入输出参数数。4) 变量的声明在变量声明区声声明变量的名名称和类型例：v_posstperiiod CHA

12、AR (2);可赋初值v_postpperiodd CHAR (2):=01;（这里叫变量声声明区可能并并不恰当，因因为游标、自自定义类型等等，一切需要要事先声明的的都应在这里里声明。）5) 变量的赋值使用：=为为变量赋值1直接使用基基本类型赋值值例：v_nummber := 1;2.使用函数赋赋值例：v_datte := sysdaate;3使用SQLL语句为变量量赋值1通过sqll直接赋值 SSELECTT COUNNT (*) INTOO v_tmppnumbeer FROMM etl_oods_maasterddata_ttablisst;2通过构造SSQL赋值： v_ttmpsqll

13、 := SSELECTT log_tablee FROOM useer_snaapshott_logss | v_dblinnk | WHEREE UPPEER (MAASTER) = UPPPER ( | v_singlletab | ); EXEECUTE IMMEDDIATE v_tmppsql INTO vv_tmpvvarchaar;6) 循环1. 无限或简单循环环LOOPEXIT WHHEN (退出循环条条件);END LOOOP;2. while循环环WHILE ccondittionLOOP execcutablle_staatemennts;END LOOOP;3. for循

14、环基于数字的foor循环：FOR forr_indeex IN low_vvalue . hiigh_vaalueLOOP execcutablle_staatemennts;END LOOOP; 基于游标的foor循环：FOR reccord_iindex IN myy_curssorLOOP execcutablle_staatemennts;END LOOOP;7) 调用其他过程或或方法1如果单独定定义，直接使使用例：v_rettval0 := f_dummp_iniit (v_upddatemoode, v_systiime, mmlog$_glddoocheadder, v_proc

15、nname, v_docheeaderbbeginsseq, v_docheeadereendseqq );2如果定义在在包下，使用用包名+过程程名例： globbal_prroceduure_chheck.ccheck_run (v_proocnamee)；3固定用法和和函数标识作用用法或类型固定用法：SYSDATEE当前系统时间DATESQLCODEE异常代码VARCHARR2SQLERRMM异常描述VARCHARR2NO_DATAA_FOUNND未找到数据异常常与 when 搭配OTHERS其他所有异常与 when 搭配RAISE抛出当前异常RAISE;DENSE_RRANK非选取字段排

16、序序MIN(B) KEEP (DENSSE_RANNK FIRST ORDEER BY A)MAX(B) KEEP (DENSE_RANK LAST ORDERR BY AA)PRAGMA AUTONNOMOUSS_TRANNSACTIIONBULK COOLLECTT INTOSQL%ROWWCOUNTT使用自治事务，可可以使该过程程被调用时单单独提交Begin之前前使用 PRRAGMA AUTONNOMOUSS_TRANNSACTIION;将前面执行结果果大批放入后后面的集合中中BULK COOLLECTT INTO ccolumnntab;前一个DML语语句执行影响响行数作为NUMBEE

17、R型使用v_numbeer:= SQL%ROOWCOUNNTDBMS_OUUTPUT.put_lline（）输出信息函数TO_CHARR转换NCHARR、NVARCCHAR2、CLOB、NCLOBBTO_CHARR（A）转换DATE型型为指定格式式TO_CHARR (time, yyyyy-mm-ddd)转换NUMBEER型为指定定格式TO_CHARR (5644.70, $9999.9)TO_DATEE转换字符串为指指定日期to_datee(1900-01-011,YYYYY-MM-DDD)INSTR(sstringg,subsstringg(,posstion)(, occurrrence

18、e)返回目标字符串串中子字符串串的位置。（起起始位置和出出现次数为可可选）INSTR (bug- archhie, archhie)INSTR (haraacter?archiie, a, 1, 2)LENGTH获得指定字符串串长度LENGTH(CANDDIDE)LOWER将指定字符串转转换成小写LOWER (LETTTERS)UPPER将指定字符串转转换成大写UPPER (letteers)LPAD(sttr1,n,str2)将str1用sstr2左补齐至n位LPAD (55, 10, 0)RPAD(sttr1,n,str2)将str1用sstr2右补齐至n位位RPAD (55, 10, 0

19、)LTRIM去掉指定字符串串左侧的指定定字符或字符符集合，默认认为空格LTRIM ( Way)LTRIM (1231123Wayy,123)RTRIM去掉指定字符串串右侧的指定定字符或字符符集合，默认认为空格RTRIM (Way xyXxyyxy,xy)POWER（mm,n）计算m的n次方方POWER(22,3)Extractt (yeaar froom datte)取出date的的年4ROWTYYPE的使用用可以使用%tyype 和% rowttype属性性实现使用其其他变量、数数据库列或表表的数据类型型的引用。%type属属性提供了所所需要的变量量的类型及长长度。% rowttype属性性

20、允许人们定定义一个记录录变量，它的的成员变量拥拥有表中每一一列正确的类类型及长度,使用点符号号引用记录中中的每个成员员变量。这种种动态赋值方方法是非常有有用的，比如如变量引用的的列的数据类类型和大小改改变了，如果果使用了%TTYPE,那那么用户就不不必修改代码码，否则就必必须修改代码码。 CREATE TABLEE EMPLOOYEE ( EMP_IID NUUMBER NOTT NULL, EMP_NNAME CHHAR (20), CREATTE_DATTE DAATE）DECLAREE v_sttudenttrecorrd eemployyee%ROOWTYPEE; n eemployy

21、ee.creatte_datte%TYPPE;BEGIN SELEECT * INNTO v_studeentreccord FRROM emplooyee WHEERE emmp_id = 1; n := v_sttudenttrecorrd.creatte_datte; DBMSS_OUTPPUT.put_lline (n);END;5内存表的使使用内存表主要作为为数组用。1）：一个字段段：PROCEDUURE t11IS TYPEE t_c IS TABLEE OF testaa.a1%TYPPE IINDEX BY BINARRY_INTTEGER; aa t_c;BEGIN aa (

22、0) := aaa; DBMSS_OUTPPUT.put_lline (aa (0);END;2）：定义多个个字段：PROCEDUURE t11IS TYPEE t_r IS RECORRD ( tt1 VVARCHAAR (10), tt2 VVARCHAAR (10) ); TYPEE t_t IS TABLEE OF t_rr IINDEX BY BINARRY_INTTEGER; aa t_t;BEGIN aa (0).t1 := aaa; aa (0).t2 := bbb; DBMSS_OUTPPUT.put_lline (aa (0).t1); DBMSS_OUTPPUT.put_

23、lline (aa (0).t2);END;6游标的使用用游标是用来处理理使用SELLECT语句句从数据库中中检索到的多多行记录的工工具。借助于于游标的功能能，数据库应应用程序可以以对一组记录录逐个进行处处理，每次处处理一行。DECLAREE n NUMBEER; CURSSOR c IS SSELECTT * FROMM emplooyee;BEGIN FOR v_c IIN c LOOPP nn := v_cc.emp_iid; DDBMS_OOUTPUTT.put_lline (n); END LOOP;EXCEPTIION WHENN OTHERRS THENN DDBMS_OOUTP

24、UTT.put_lline (erroor);END;7跟踪调试根踪调试主要是是检查程序运运行的情况，可可以在需要检检查程序是否否执行正确作作为输出的依依据：DBMMS_OUTTPUT.PPUT_LIINE(G_USERIID(-2);执行时设置：sset seerverooutputt on8临时表临时表用于保存存事务或者会会话的中间结结果，临时表表中保存的数数据只有对当当时的会话是是可见的，任任何会话都不不能看见其他他会话的数据据。即使COOMMIT之之后也是不可可见的。对于于临时表并行行不是问题，即即使锁定也不不能阻止其他他程序的访问问。每个数据据库创建临时时表一次，（OORACLEE

25、的DDLL语句是一种种消耗较大的的动作）并不不用每个程序序创建一次，并并且临时表总总保持为空。下面这个例子可可以说明临时时表的运行过过程：CREATE GLOBAAL TEMPOORARY TABLEE REPDDB.L_EMPP_DEPTT_TEMPP(EMP_IDDVARCHHAR(5),EMP_NAAMEVARCHHAR(20),DEPT_IIDVARCHHAR(5),DEPT_NNAMEVARCHHAR(20) 1 DDECLARRE 2 DD L_EMMP_DEPPT_TEMMP%ROWWTYPE; 3 CCURSORR C ISS 4 SSELECTT E.EMMP_ID AA ,

26、EE.EMP_NAME BB ,DD.DEPTT_ID CCC ,D.NAME DD 5 FFROM LL_EMPLLOYEE E,L_DDEPT DD 6 WWHERE E.DEPP_ID=DD.DEPTT_ID; 7 BBEGIN 8 FFOR VV_C INN C LOOOP 9 IINSERTT INTOO L_EEMP_DEEPT_TEEMP 10 VVALUESS (V_CC.AA,VV_C.BBB,V_C.CC,V_C.DD); 11 EEND LOOOP; 12* EEND ;SQL /PL/SQL 过程已成功功完成。SQL SEELECT COUNTT(*) 2 FRROM

27、L_EMP_DDEPT_TTEMP 3 / COUNTT(*)- 3SQL COOMMIT 2 /提交完成。SQL SEELECT COUNTT(*) 2 FRROM L_EMP_DDEPT_TTEMP 3 / COUNTT(*)- 09异常处理例外是一个非致致命事件，它它立即中断程程序的正常执执行并引起一一个非条件转转移，跳转到到当前程序块的例外外处理部分。一一些例外，像像NO_DAATE_FOOUND或TTO_MANNY_ROWWS，属于预预定义例外用用于处理常见见的oraccle错误，可可以被认为是是正常的处理理部分。部分分ERRORR这样的例外外表明一个程程序错误或一一些意料之外外的事

28、件。如如下所示：1）：正常处理理的部分 1 DEECLAREE 2 N CHAR; 3 BEEGIN 4 SEELECT EMP_NNAME 5 INNTO N 6 FRROM EMMPLOYEEE; 7 DBBMS_OUUTPUT.PUT_LLINE(N); 8* ENND;SQL /DECLAREE*第 1 行出现现错误:ORA-014422: 实实际返回的行行数超出请求求的行数ORA-065512: 在在 linee 4 1 DEECLAREE 2 N CHAR; 3 BEEGIN 4 SEELECT EMP_NNAME 5 INNTO N 6 FRROM EMMPLOYEEE; 7 D

29、BBMS_OUUTPUT.PUT_LLINE(NN); 8 EXXCEPTIION WHHEN TOOO_MANNY_ROWWS THEEN 9 DBBMS_OUUTPUT.PUT_LLINE(TOO MMANY RROWS RRETURNN); 10* ENND;PL/SQL 过程已成功功完成。 输输出结果为：TOO MMANY RROWS RRETURNN2）：非正常处处理的部分，自自定义异常SQL innsert into l_empployeee 2 vaalues (4,dd,3,ssysdatte,20000) 3 /insert into l_empployeee*第 1 行出现

30、现错误:ORA-022291: 违违反完整约束束条件 (RREPDB.FK_EMMP_DEPPT) - 未找到父项项关键字处理方法：自定定义异常 1 deeclaree 2 e excepption ; 3 prragma excepption_init(e,-22291); 4 beegin 5 innsert into l_empployeee 6 vaalues (6,dd,3,ssysdatte,20000) ; 7 exxceptiion whhen e then 8 DBBMS_OUUTPUT.PUT_LLINE(违反完整约约束条件 (REPDBB.FK_EEMP_DEEPT);

31、9* ennd ;SQL /PL/SQL 过程已成功功完成。 输输出结果为： 违反完整整约束条件 (REPDDB.FK_EMP_DDEPT) 10嵌套程序块的内部可可以有另一个个程序块这种种情况称为嵌嵌套。嵌套要要注意的是变变量，定义在在最外部程序序块中的变量量可以在所有有子块中使用用，如果在子子块中定义了了与外部程序序块变量相同同的变量名，在在执行子块时时将使用子块块中定义的变变量。子块中中定义的变量量不能被父块块引用。如果字块需要单单独提交，应应使用自治事事务。11标签用户可以使用标标签使程序获获得更好的可可读性。程序序块或循环都都可以被标记记。标签的形形式是。要求使用标签。12记录转储储

32、开始时间和和结束时间1）在建立中间间表后，用脚脚本或手工在在数据表中建建立一条记录录，以后每次次都更新。对每个转储只只记录一条记记录，不保存存历史记录。2）在开始转储储时，读取上上次转储结束束时间，只转转储从上次转转储以来的新新增或修改的的记录。3）在转储的存存储过程中记记录开始时间间和结束时间间，是否成功功。如果失败败，记录失败败原因。4）可以用SSQL语句查查找失败的转转储，可以查查找转储时间间过长的转储储。表名：转储记录录（TRANNSLOG）字段：程序包名存储过程名中间表名开始时间结束时间成功标识失败原因13授权grant sselectt on cs_new.ACPSttorkFll

33、AssAnnaTab to reppdbnewwrevoke selecct on cs_new.BILInnvoiceeUseEnntityDData ffrom rrepdbnnew注意：不能为当当前用户授权权14建立同义义词CREATE SYNONNYM ACCPStorrkFlAsssAnaTTab FOOR jceerp.ACPSttorkFllAssAnnaTabdrop SYYNONYMM BILIInvoicceUseEEntityyData注：建立同义词词后用户可以以用seleect访问，但但不能建立视视图。15为表字段段加注释COMMENTT ON COLUMMN HAN_

34、22.BBBB IS B字段;16触发器create or replaace triggger qyytest11_triggger3 BEFORRE INSERRT ON mlog$_qyteest1FOR EACCH ROWbegin:new.snnaptimme$ := SYSSDATE;end;17自定义类类型的赋值自定义类型：CREATE OR REPPLACE TYPPE rrepdbnnew.INPARRAM AS VARRRAY(550) OF VARRCHAR22(25); 自定义类型的赋赋值： declaree iindataaname inpparam; beginn i

35、indataaname := iinparaam(2); iindataaname:= iinparaam(isCaalCounnt,NO_TTX); end; 18OBJEECT TYYPES簡單來說，Orracle Objecct Typess 就是 OOraclee 以 TYYPE 的方方式來實現物物件(Objeccts)的方法，宣宣告/定義的方法法，類似於 Packaage。Objecct Type 的的宣告/定義中包含含了它的 AAttribbutes/Propeertiess 與 Meethodss，也就是 Membeer Functtions/Proceeduress。本篇來介紹

36、利用用 Oraccle Obbject Typess 來做中介介暫存的實作作。建立 Oraccle Obbject:定義這個 Obbject 的內容，可可以把它想像像為所希望的的 Row Colummns 的定定義。view pllaincoopy too clippboarddprintt?CREATE TYPE ttype_oobj ASS OBJECCT ( col1 INTEEGER, col2 VARCCHAR2(60) ); / CREATE TYPE ttype_oobj ASS OBJECCT ( coll1 INNTEGERR, coll2 VAARCHARR2(60);/建立 Objeect Coollecttion:建立一個 Taable Type，這這個 Tabble 裝的的資料列內容容(欄位)就是之前所所建立的 OObjectt。view pllaincoopy too clippboarddprintt?CREATE OR REPLAACE TYPE tyype_taab IS TABLEE OF typpe_objj; / CREATE OR REPLAACE TYPE ttype_ttab ISS TABLEE OF typpe_objj;/在 PL/SQQL 中的應應