数据库开发规范

1、.项目一部数据库开发规范版本号日期修改者说 明1.02012-03-06注：对该文件内容增加、删除或修改均需填写此变更记录，详细记载变更信息，以保证其可追溯性。1. 命名规范1.1. 对象新建的表，存储过程，包等要遵循以下规则对象名称前缀备注表见下表，根据功能分视图v_主键pk_索引idx_序列seq_存储过程pr_包pk_函数f_同义词s_数据库连接(dblink)dl_1.2. 表表名不得超过30个字母，全部采用大写字母，表的命名可以如下：模块缩写名_表的名称，如RES_NODE代表资源模块的节点; 值班： DUTY_CALENDAR。1.3. 字段名称字段名不得超过30个字母，必须以英文

2、单词构成，每个单词之间以下划线隔开，全部采用大写字母。对复杂的大型应用系统而言，必须建立表名和字段名的数据字典，并附于开发规范附录中，在命名时必须严格遵守数据字典。2. 数据库对象管理2.1. 一般规定数据库所有对象，包括表、视图、主键、索引、序列、存储过程、包等必须在数据库建模工具中进行管理并保持与数据库完全同步。2.2. 大小写在数据库模型、数据库脚本中，所有对象，包括表、视图、主键、索引、序列、存储过程、包等名称必须大写。3. 语句书写规范3.1. 尽量不使用某种数据库的特有功能为了保持可移植性，尽量不使用某种数据库的特有功能，如SQL Server专用的Unique ID, Oracl

3、e专用的Sequence的功能；3.2. 查询sql语句尽量使用绑定变量3.3. 尽最大可能不使用通配符在SQL语句中，LIKE关键字支持通配符匹配，但这种匹配特别耗费时间。如：SELECT A FROM ABC WHERE A LIKE M% 。在A字段上建立了索引。把语句改为SELECT A FROM ABC WHERE A M AND A 50000 AND emp_type = MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE emp_mgr = E.emp_no);SELECT * FROM emp E WHERE 25 50000 AND

4、 emp_type = MANAGER;使用OR语句时，行数多的放在后面SELECT * FROM emp E WHERE 25 50000 AND emp_type = MANAGER);SELECT * FROM emp E WHERE (emp_sal 50000 AND emp_type = MANAGER) OR 25 0; （3）TRUNCSELECT acc_name, trans_date, amount FROM transaction WHERE TRUNC(trans_date) = TRUNC(SYSDATE);SELECT acc_name, trans_date,

5、amount FROM transaction WHERE trans_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + .99999; （4）|SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name | acc_type = AMEXA; SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name = AMEX AND acc_type = A;（5）运算SELECT acc_name, t

6、rans_date, amount FROM transaction WHERE amount + 3000 5000; SELECT acc_name, trans_date, amount FROM transaction WHERE amount ( SELECT MIN(X.rowid) FROM emp X WHERE X.emp_no = E.emp_no );4.2.4 表的行计数SELECT COUNT(有索引的列) FROM TRANS;SELECT COUNT(*) FROM TRANS;SELECT COUNT(1) FROM TRANS;4.2.5 用WHERE语句替换

7、HAVING语句的使用SELECT region, AVG(loc_size) FROM location GROUP BY region HAVING region != SYDNEY AND region != PERTH;SELECT region, AVG(loc_size) FROM location WHERE region != SYDNEY AND region != PERTH; GROUP BY region4.2.6 使用表连接替代EXISTS使用SELECT emp_name FROM emp WHERE (emp_cat, sal_range) = (SELECT M

8、AX(category), MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;SELECT emp_name FROM emp WHERE emp_cat = (SELECT MAX(category) FROM emp_categories) AND sal_range = (SELECT MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;4.2.7 使用EXISTS替代表连接SELECT . FROM dept D, emp E WHERE E.dept_no = D

9、.dept_no AND E.emp_type = MANAGER AND D.dept_cat = A;SELECT . FROM emp E WHERE EXISTS ( SELECT X FROM deptWHERE dept_no = E.dept_no AND dept_cat = A) AND E.emp_type = MANAGER;SELECT . FROM dept D, emp EWHERE E.dept_no = D.dept_no AND (E.emp_type = MANAGER OR D.dept_cat = A);SELECT . FROM emp E WHERE

10、 E.emp_type = MANAGER;OR EXISTS ( SELECT X FROM dept WHERE dept_no = E.dept_no AND dept_cat = A);4.2.8 使用EXISTS代替DISTINCT语句SELECT DISTINCT dept_no, dept_name FROM dept D, emp E WHERE D.dept_no = E.dept_no;SELECT dept_no, dept_name FROM dept D WHERE EXISTS ( SELECT X FROM emp E WHERE E.dept_no = D.de

11、pt_no);4.2.9 使用NOT EXISTS代替NOT IN语句 SELECT . FROM empWHERE dept_no NOT IN ( SELECT dept_no FROM dept WHERE dept_cat = A);SELECT . FROM emp E WHERE NOT EXISTS ( SELECT X FROM dept WHERE dept_no = E.dept_noAND dept_cat = A);4.2.10 使用union all 代替union 语句Union - 进行排序Union All - 不排序4.2.11 使用Union和IN代替OR语

12、句Loc_no, region上有索引 SELECT . FROM location WHERE loc_id = 10 OR region = MELBOURNE; SELECT . FROM location WHERE loc_id = 10UNION ALLSELECT . FROM location WHERE region = MELBOURNE;SELECT . FROM location WHERE loc_id = 10OR loc_id = 20 OR loc_id = 30; SELECT . FROM location WHERE loc_id IN (10, 20,

13、30);5 高效索引5.1 索引的使用使用索引时，要考虑以下因素：1） 索引列的计算2） 索引列的增加3） 索引列不要用NOT4） 索引中空值的使用IS NULL, IS NOT NULL5） 索引列的数据类型的变换EMP_TYPE为varchar2类型，下列语句使用索引SELECT . FROM emp WHERE emp_type = 123; SELECT . FROM emp WHERE TO_NUMBER(emp_type) = 123;SELECT . FROM emp WHERE emp_type = 123;5.2 增加索引增加索引要考虑多种因素，要考虑对更新、插入的影响等。1

14、） 频繁查询不超过大表1%15%的场合。2） 列的不同值3） 列4） 列5） 同表6 解析表的使用方法6.1 SQLTRACE6.1.1 参数表的生成$sqlplus s /SQL$ORACLE_HOME/rdbms/admin/utlxplan.sqlSQL commitSQL exit6.1.2 AUTOTRACE的用法afe57gc2/users/home/ino 29 sqlplus sys/*SQL $ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL drop role plustrace;：SQL grant plustrace to ops$in

15、o;SQL exit6.1.3 AUTOTRACE功能的使用afe57gc2/oracle/product/8.1.7 37 sqlplus /SQL set autotrace onSQL select * from wcattori;执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF WCATTORI統計- 0 recursive calls 12 db block gets 4 consistent gets 0 physical reads 0 redo size 4779 bytes sent vi

16、a SQL*Net to client 314 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL6.2 TKPROF6.2.1 初始化afe57gc2/oracle/product/8.1.7 40 sqlplus /SQL ALTER SESSION SET TIMED_STATISTICS = true;SQL ALTER SESSION SET MAX_DUMP_ = 500;6.2.

17、2 SQL Trace开始SQL ALTER SESSION SET SQL_TRACE = true;6.2.3 实例SQL SELECT * FROM WCATTORI;6.2.4 SQL Trace结束SQL ALTER SESSION SET SQL_TRACE = false;SQL exit6.2.5 内容变换afe57gc2/users/home/ino 44 suPassword:# chmod 777 /oracle/product/8.1.7/rdbms/log/ora_14186_wca1.trc# exitafe57gc2/users/home/ino 46 tkpro

18、f $ORACLE_HOME/rdbms/log/ora_14186_wca1.trc output.txtTKPROF: Release 8.1.7.2.0 - Production on 木 Apr 18 15:55:17 2002(c) Copyright 2000 Oracle Corporation. All rights reserved.備考：出力先SQL column name format A20SQL column value format A30SQL select name, value from v$parameter where name like %dump_dest;NAME VALUE- -background_dump_dest ?/rdbms/loguser_dump_dest ?/rdbms/logcore_dump_dest ?/dbsSQL6.2.6 内容确认afe57gc2/users/home/ino 47 cat output.txt (一部抜粋)SELECT *FROM WCATTORIcall count cpu elapsed d