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

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

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

2、涉及的表或视图：dump_init协助表(DM)：记录储备过程中运用的物化视图日志序号 mlog$_acrcusmrsecindex 源表(ODS)：客户第一索引物化视图日志,运用同义词 ft_gld_customerdata目标表(DM)：客户事实表 */ CREATE OR REPLACE PROCEDURE* - 2 ：代码书写规范 1. 语句中显现的全部表名、字段名全部小写，系统保留字、内置函数名、Sql 保留字大写。2. 连接符 or、in、and、以及、lt;=、gt;=等前后加上一个空格。3. where 子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符起先，连接符

3、右对齐。4. 查询的 WHERE 过滤，原那么应使过滤记录数最多的条件放在最前面。5. 多表连接时，运用表的别名来引用列。6. 查找数据库表或视图时，只能取出的确须要的那些字段，不要运用*来代替全部列名。7. 功能相像的过程和函数，尽量写到同一个包中，加强治理。例如如下：BEGIN-查询职员及对应的部门名称 SELECT emp.name, dept.name FROM l_dept dept, l_employee empWHERE emp.dept_id = dept.dept_id; END; 3 注释书写规范 为了提高可读性，应当运用肯定数量的注释。注释大约占总行数的 1/5。1：注释

4、风格：注释单独成行、放在语句前面。2：应对不易明白得的分支条件表达式加注释； 3：对重要的运算应说明其功能； 4：过长的函数实现，应将其语句按实现的功能分段加以概括性说明； 5：每条 SQL 语句均应有注释说明 6：关于程序的整体功能，应在程序起先部分说明，可接受单行/多行注释。- 或 /* */ 方式 2. 命名规范 命名对象 规那么 样例 储备过程、包、方法 1 业务相关以模块代码开头 gld_assist_check_p2 假如区分全量和增量，在最终加标识 gld_load_to_etl gld_load_to_etl_full3 全局运用，以 global 开头 global_proc

5、edure_check变量 以 v 开头 v_updatemode1游标 以 c 开头 c_tablist内存表 以 m 开头 m_table1 临时表 以 t 开头 t_tmpTable储备过程技术 1 储备过程样例 CREATE OR REPLACE PROCEDURE example (v_inputIN NUMBER, -输入参数v_outputOUTNUMBER -输出参数 ) IS PRAGMA AUTONOMOUS_TRANSACTION;CURSOR c1-定义一个游标，在begin之前IS SELECTb.tablename mlogtable,MAX (remarks)KE

6、EP (DENSE_RANK LAST ORDER BY starttime) remarks FROM proc_log a, table_proc b WHERE TO_CHAR (starttime, yyyy-mm-dd) lt;= -转换时刻并做比较TO_CHAR (SYSDATE - TO_DSINTERVAL (TO_CHAR (intervaldays) | 00:00:00), yyyy-mm-dd )AND a.remarks LIKE SUCCEEDED:%AND a.procedurename = b.procedurename GROUP BY b.tablename

7、);-定义终止 c1_recc1%ROWTYPE;-定义同意游标数据行的ROWTYPEv_mlogtable VARCHAR (30);v_postperiodCHAR (2);v_acctbalbeginseq NUMBER;v_systime DATE;BEGINv_input := 0;-变量赋值v_systime := SYSDATE;OPEN c1;-打开游标LOOP -循环 FETCH c1 INTO c1_rec; -从当前游标行赋值c1_rec EXIT WHEN c1%NOTFOUND; -游标没有数据退出 v_mlogtable := c1_rec.mlogtable;-从

8、行取出详细数据赋给变量CASE TRIM (LOWER (v_mlogtable)-CASE起始WHENString1-当条件一THEN -做条件一工作 BEGINv_remarks := REPLACE (v_remarks, AA); END;WHEN String2 -当条件二THEN BEGIN END;ELSE -其他条件 NULL; END CASE; -CASE终止IF (LOWER (SUBSTR (v_mlogtable, 1, 5) lt;gt; mlog$) THENSELECT log_tableINTO v_mlogtableFROM user_snapshot_lo

9、gs WHERE LOWER (MASTER) = LOWER (v_mlogtable); END IF; EXECUTE IMMEDIATEdelete from | v_mlogtable | where sequence$ lt;= | TO_CHAR (v_lognum);EXIT WHEN 1gt;2;-循环跳出条件END LOOP; -循环终止 CLOSE c1; -关闭游标 EXCEPTION WHEN OTHERS THENROLLBACK;global_procedure_check.check_end (checkdataerror01,v_systime,1,SQLCO

10、DE | | SQLERRM );RAISE;RETURN;END; END example; 2 差不多学问 1) 差不多结构 - CREATE OR REPLACE PROCEDURE exampleparameters-过程声明区 IS - v_1 NUMBER;-过程中变量声明区- BEGINv_1 := 0; -过程内容区 END example; -2) 差不多类型 CHAR 固定长度字符类型VARCHAR2可变长字符类型 VARCHAR可变长字符类型(不建议运用) NUMBER一切数值类型 DATE 一切日期类型3) 参数 三种：IN 输入参数，OUT 输出参数，IN OUT 输

11、入输出参数。4) 变量的声明 在变量声明区声明变量的名称和类型 例：v_postperiodCHAR (2); 可赋初值 v_postperiodCHAR (2):=’01’ 那个地点叫变量声明区可能并不恰当，因为游标、自定义类型等，一切须要事先声明的都应在那个地点声明。 5) 变量的赋值 运用：=’为变量赋值 1 直截了当运用差不多类型赋值 例：v_number := 1; 2. 运用 函数 赋值 例：v_date := sysdate ; 3 运用 SQL 语句为变量赋值 1通过 sql 直截了当赋值 SELECT COUNT (*) INTO v_tm

12、pnumber FROM etl_ods_masterdata_tablist; 2通过构造 SQL 赋值：v_tmpsql :=SELECT log_tableFROM user_snapshot_logs | v_dblink | WHERE UPPER (MASTER) = UPPER ( | v_singletab | ); EXECUTE IMMEDIATE v_tmpsql INTO v_tmpvarchar;6) 循环 1. 无限或简洁循环 LOOP EXIT WHEN (退出循环条件); END LOOP; 2. while 循环 WHILE condition LOOPexe

13、cutable_statements; END LOOP; 3. for 循环 基于数字的 for 循环：FOR for_index IN low_value . high_value LOOPexecutable_statements; END LOOP;基于游标的 for 循环：FOR record_index IN my_cursor LOOPexecutable_statements; END LOOP;7) 调用其他过程或方法 1假如单独定义，直截了当运用 例：v_retval0 := f_dump_init (v_updatemode,v_systime,mlog$_glddoch

14、eader,v_procname,v_docheaderbeginseq,v_docheaderendseq );2假如定义在包下，运用包名+过程名 例：global_procedure_check.check_run (v_procname)；3 固定用法 和函数 标识 作用 用法或类型 固定用法：SYSDATE 当前系统时刻DATE SQLCODE 特地代码 VARCHAR2 SQLERRM 特地描述 VARCHAR2 NO_DATA_FOUND 未找到数据特地 与 when 搭配 OTHERS 其他全部特地 与 when 搭配 RAISE 抛出当前特地 RAISE; DENSE_RANK

15、 非选取字段排序 MIN(B) KEEP (DENSE_RANK FIRST ORDER BY A) MAX(B) KEEP (DENSE_RANK LAST ORDER BY A) PRAGMA AUTONOMOUS_TRANSACTION BULK COLLECT INTO SQL%ROWCOUNT 运用自治事务，能够使该过程被调用时单独提交 Begin 之 前 使 用PRAGMA AUTONOMOUS_TRANSACTION; 将前面执行结果大批放入后面的集合中 BULK COLLECT INTO columntab; 前一个 DML 语句执行阻碍行数 作为 NUMBER 型运用 v_n

16、umber:= SQL%ROWCOUNT DBMS_OUTPUT.put_line 输出信息函数TO_CHAR 转换 NCHAR、NVARCHAR2、CLOB、NCLOB TO_CHARA 转换 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) 返回目标字符串中子字符串的位置。起始位置和显现次数

17、为可选 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 去掉指定字符串左侧的指定

18、字符或字符集合，默认为空格 LTRIM (Way) LTRIM (123123Way,123) RTRIM 去掉指定字符串右侧的指定字符或字符集合，默认为空格 RTRIM (Way xyXxyxy,xy) POWERm,n 运算 m 的 n 次方 POWER(2,3) Extract (year from date) 取出 date 的年4 ROWTYPE 的运用 能够运用%type 和% rowtype属性实现运用其他变量、数据库列或表的数据类型的引用。%type属性供应了所须要的变量的类型及长度。% rowtype属性承诺人们定义一个记录变量，它的成员变量拥有表中每一列正确的类型及长度,运

19、用点符号引用记录中的每个成员变量。这种动态赋值方法是特地有用的，比如变量引用的列的数据类型和大小变更了，假如运用了%TYPE,那么用户就不必修改代码，否那么就必需修改代码。CREATE TABLE EMPLOYEE (EMP_ID NUMBERNOT NULL,EMP_NAME CHAR (20),CREATE_DATEDATE DECLAREv_studentrecord employee%ROWTYPE;n employee.create_date%TYPE; BEGINSELECT *INTO v_studentrecordFROM employee WHERE emp_id = 1;

20、n := v_studentrecord.create_date;DBMS_OUTPUT.put_line (n); END;5 内存表的运用 内存表要紧作为数组用。1：一个字段：PROCEDURE t1 IS TYPE t_c IS TABLE OF testa.a1%TYPE INDEX BY BINARY_INTEGER; aa t_c; BEGINaa (0) := aaa;DBMS_OUTPUT.put_line (aa (0); END; 2：定义多个字段：PROCEDURE t1 IS TYPE t_r IS RECORD ( t1 VARCHAR (10), t2 VARCHA

21、R (10); TYPE t_t IS TABLE OF t_r INDEX BY BINARY_INTEGER; aa t_t; BEGINaa (0).t1 := aaa;aa (0).t2 := bbb;DBMS_OUTPUT.put_line (aa (0).t1);DBMS_OUTPUT.put_line (aa (0).t2); END;6 游标的运用 游标是用来处理运用SELECT语句从数据库中检索到的多行记录的工具。借助于游标的功能，数据库应用程序能够对一组记录逐个进行处理，每次处理一行。DECLAREn NUMBER;CURSOR cIS SELECT * FROM employee; BEGINFOR v_c IN cLOOP n := v_c.emp_id; DBMS_OUTPUT.put_line (n);END LOOP; EXCEPTIONWHEN OTHERSTHEN DBMS_OUTPUT.put_line (error); END;7 跟踪调试 根踪调试要紧是检查程序运行的情形，能.