1 编码规范

1.1 SQL语句规范

2) 连接符OR、IN、AND、以及＝、<=、>=等前后加上一个空格。当语句中出现括号时，括号的两边不留空格。

4) 关键字、保留字预留到左边起始位置，一行有多列，超过80 个字符时，基于列对齐原则，采用下行缩进。

6) 对于非常复杂的sql(特别是有多层嵌套，带子句或相关查询的)，应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。

SELECt A.ORDER_ID,A.ORDER_CONTENT

WHERe A.ORDER_ID = B.FK_ORDER_ID

1.2 SELECT语句的格式标准

INTO :Parameter1,:Parameter2

WHERe A.Column_name = B.Column_name;

以下是SELECT的语法，详细的使用方法请查阅相关资料

FROM {table_1 | (subquery)} [alias]

[WHERe condition]

[GROUP BY expn] [HAVINg expn]

[ ORDER BY [expn ] [ ASC | DESC]

[NOWAIT]

1.3 UPDATe语句的格式标准

Column_name2 = :Parameter2

以下是UPDATE的语法，详细的使用方法请查阅相关资料

SET { column1=express1[,column2=experss2]...|

[WHERe condition|current of cursor];

1.4 INSERT语句的格式标准

VALUES(:Parameter1,:Parameter2)

INSERT INTO TableName(Column_name1,Column_name2)

FROM Table

不允许出现 INSERT INTO TableName VALUES (:Parameter1,:Parameter2)的写法。

DELETe FROM TableName WHERe Column_name1 = :Parameter1

DELETe [ FROM ] [user.]table [@db_link][Alias] [WHERe condition];

1.6 UNIOn,INTERSECT及MINUS

1． 语法：

union[all]

intersect

minus

例如: 条件语句

DBMS_OUTPUT.PUT_LINE('AAA');

DBMS_OUTPUT.PUT_LINE('BBB');

DBMS_OUTPUT.PUT_LINE('CCC');

FOR I IN 0..10 LOOP

END LOOP;

例如: 循环语句B:

GA NUMBER(3):=10;

WHILE GA <100 LOOP

GA := GA+1;

END;

1.8 游标规范

OPEN CUR_CRM_ORDER

FETCH CUR_CRM_ORDER INTO

...

CLOSE CUR_CRM_ORDER;

带参数的游标写法

vregnick VARCHAR(20):='张三';

vregid INT;

SELECt regid INTO vregid FROM reginfo WHERe regnick=vregnick;

OPEN cu_a(vregnick) ;

END;

Ref 游标，绑定变量的游标的下发

DECLARE

TYPE cur_type IS REF CURSOR;

cur cur_type;

vregnick VARCHAr(20):='张三';

vreginfo reginfo%ROWTYPE;

BEGIN

OPEN cur FOR 'select * from reginfo where regnick=:a'

USING vregnick;

Loop

FETCH cur INTO vreginfo;

EXIT When cur%NOTFOUND;

dbms_output.put_line(vreginfo.regid);

END LOOP;

CLOSE cur;

每个存储过程、触发器、包、函数的开头要有详细的说明，包括程序的名称、参数、功能、返回值以及编写时间、编写人员等信息。每次修改在说明后面附上修改记录。

注释例子：

1) 注释行的长度以在最大化窗口内可以看到全部内容为宜，如果一行不够显示需要换行，下一行注释语句与上一行注释语句应对齐。

3) 注释行一律放在被注释语句的上一行。

V_BpMode VARCHAr2(10) ; -- 用户服务类型

-- 选取用户服务类型

SELECt BpMode INTO V_BpMode FROM ACC_BP WHERe User_ID = I_User_ID ;

WHEN NO_DATA_FOUND THEN

END ;

2.3 代码片断注释要求

2) 注释行放在 被注释的代码片断上一行，并与代码片断第一行对齐。

3 编码命名规范

3.1 命名语言

英文单词使用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名。

当出现对象名重名时，是不同类型对象时，在对象名前后加类型前缀或后缀以示区别。

3.2 大小写

命名的各单词之间可以使用下划线“_”进行分隔。

3.4 保留字

表名、字段名、视图名长度应限制在20个字符内(含前缀)。

3.6 字段名称

不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。

4 编码数据类型规范

4.1 字符型

数字型字段尽量采用number类型，要注意精度。

4.3 日期和时间

4.3.1 系统时间

由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：YYYYMMDDHH24MISS。

4.4 大字段

对于数字型唯一键值，尽可能用系列sequence产生。

5 SQL编码技巧

如果不是必要取出所有数据，不要用*来代替，应给出字段列表，使用动态SQL列引用 '*' 是一个方便的方法。但是，这是一个非常低效的方法。ORACLE在解析的过程中，会将'*' 通过查询数据库系统的数据字典依次转换成所有对应的列名，这意味着将耗费更多的时间和系统资源。

注意别名的使用：

并行查询 ，效率可以提高

SELECt * FROM CRM_ORDER T

第二种形式

这个实际上是In子句的特例。

5.3 使用SELECt COUNT(主键)计算表的记录数

例如：

5.4 WHERe子句

2) 除非你不考虑执行效率问题，否则请不要在WHERe 子句中使用函数作为条件。例如 WHERe TO_NUMBER(TEL_NBR)=7654321 这种写法是效率非常低下的。

4) 某些SELECT 语句中的WHERe子句不使用索引。

(1)‘!=' 将不使用索引。索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。

不使用索引

使用索引

(2) ‘||'是字符连接函数. 就象其他函数那样, 停用了索引。

(4)相同的索引列不能互相比较,这将会启用全表扫描。

5.5 用TRUNCATE替代DELETE

而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

使用前请注意是否有权限问题。

5.6 尽量多使用COMMIT

COMMIT所释放的资源：

2) 被程序语句获得的锁。

4) ORACLE为管理上述3种资源中的内部花费。

5.7 关闭自动提交功能，提高系统性能

　　conn.setAutoCommit(false);

任何SQL 语句，只要在where 子句中使用了is null 或is not null，那么Oracle 优化器就不允许使用索引了。

例如: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入)。然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERe子句中对索引列进行空值比较将使ORACLE停用该索引.
低效: (索引失效) SELECT … FROM DEPARTMENT WHERe DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECt … FROM DEPARTMENT WHERe DEPT_CODE >=0;

5.9 避免In子句

Char 比 varchar 查询时高效。在进行查询及建立索引时，char比varchar的效率要高，当然varchar在存储上比char要好。

SELECt * FROM reginfo a WHERe a.regid

WHERe EXISTS (SELECt 1 FROM reginfo_temp b WHERe a.regid=b.regid)

SELECt a.regid,a.regnick FROM reginfo a

WHERe a.regid=b.regid

5.10 用EXISTS替代IN

在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

下面是一个NOT 子句的例子：

如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT 运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where 子句中显式地加入NOT 词，NOT 仍在运算符中，见下例：

再看下面这个例子：

对这个查询，可以改写为不使用NOT：

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle 对salary 列使用索引，而第一种查询则不能使用索引。

第一种格式是使用IN 操作符：

第二种格式是使用EXIST 操作符：

我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle 中可以几乎将所有的IN 操作符子查询改写为使用EXISTS 的子查询。

通过使用EXIST，Oracle 系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle 系统在执行IN 子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS 比使用IN 通常查询速度快的原因。

通常情况下, 用UNIOn替换WHERe子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意, 以上规则只针对多个索引列有效。如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。

高效: SELECt LOC_ID , LOC_DESC , REGION FROM LOCATION WHERe LOC_ID = 10 UNIOn SELECt LOC_ID , LOC_DESC , REGION FROM LOCATION WHERe REGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面。

5.13 用UNIOn-ALL 替换UNIOn ( 如果有可能的话)

高效: SELECt * FROM EMP WHERe DEPTNO >=4

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

5.15 Order by 语句

仔细检查order by 语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by 语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by 子句中使用表达式。

同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton 的人。可以采用如下的查询SQL 语句：

这里由于通配符（%）在搜寻词首出现，所以Oracle 系统不使用last_name 的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%';

不使用索引情况:

SELECt * FROM temp_stuinfo WHERe stu_add LIKE '%甘肃%';

SELECt * FROM temp_stuinfo WHERe stu_add LIKE '甘肃%';

SELECt * FROM temp_stuinfo WHERe instr(stu_add ,'肃')>0;

instr和like的性能比较:

like有时可以用到索引，例如：name like ‘李%’，而当下面的情况时索引会失效：name like ‘%李’或者name like ‘%李%’。所以一般我们查找中文类似于‘%字符%’时，索引都会失效。与其他数据库不同的是，oracle支持函数索引。例如在name字段上建个instr索引，查询速度就比较快了，这也是为什么instr会比like效率高的原因。

instr(title,’手册’)=0 相当于not like‘%手册%’

5.17 使用oracle全文检索对海量的文本数据进行搜索

设置全文检索：

首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能（10G默认安装都有此用户和角色）。你必须修改数据库以安装这项功能。默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用户。

在ctxsys用户下，授予测试用户oratext以下权限：

2 GRANT EXECUTE ON ctxsys.ctx_cls TO oratext;

4 GRANT EXECUTE ON ctxsys.ctx_doc TO oratext;

6 GRANT EXECUTE ON ctxsys.ctx_query TO oratext;

8 GRANT EXECUTE ON ctxsys.ctx_thes TO oratext;

步骤三：设置词法分析器(lexer)

basic_lexer:针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if,is等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer分析的结果只有一个term,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。

chinese_lexer:这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持utf8,如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer。如果不做任何设置，Oracle缺省使用basic_lexer这个分析器。

第一．建立一个preference:

第二．在建立全文索引时，指明所用的lexer:

这样建立的全文检索索引，就会使用chinese_vgram_lexer作为分析器。

测试用户为oratext，建立此用户和对应表空间的内容就不写了：

GRANT resource, CONNECT, ctxapp TO oratext;

GRANT EXECUTE ON ctxsys.ctx_ddl TO oratext;

GRANT EXECUTE ON ctxsys.ctx_output TO oratext;

GRANT EXECUTE ON ctxsys.ctx_report TO oratext;

GRANT EXECUTE ON ctxsys.ctx_ulexer TO oratext;

步骤二：设置词法分析器，使用chinese_vgram_lexer作为分析器：

--设置词法分析器

ctx_ddl.create_preference ('oratext_lexer', 'chinese_vgram_lexer');

可以通过下面的语句查看系统默认及设置的oracle text参数：

可以看到我刚刚设置的语法分析器参数oratext_lexer，（默认的有一个MY_LEXER的语法分析器参数）。

CREATE TABLE textdemo(

book_author varchar2(20),--作者

title varchar2(400),--标题

path varchar2(200)--路径

commit;

INSERT INTO textdemo VALUES(2,'莫贝克曼贝托夫',to_date('2008-10-07','yyyy-mm-dd'),' 子弹转弯','这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6 月末在北美上映以来，已经在全球取得了超过3亿美元的票房收入。在亚洲上映后也先后拿下日本、韩国等地的票房冠军宝座。虽然不少网友在此之前也相继通过各种渠道接触到本片，但相信影片凭着在大银幕上呈现出的超酷的视听效果，依然能够吸引大量影迷前往影院捧场。','E:\textsearch\catch.pdf');

commit;

CREATE INDEX demo_abstract ON textdemo(book_abstract) indextype IS ctxsys.context parameters('lexer ORATEXT_LEXER');

之后如上所述多出很多dr$开头的表和索引，系统会创建四个相关的表：

下面的语句可以查看索引创建过程中是否发生了错误：

附：对于建立索引的类型（例如ctxsys.context），包括四种：context,ctxcat,ctxrule,ctxxpath。

支持类型：VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, and URIType.DML。操作后，需要CTX_DDL.SYNC_INDEX手工同步索引如果有查询包含多个词语，直接用空格隔开(如 oracle itpub)。

CTXCAT适用于混合查询语句（如查询条件包括产品id，价格，描述等）。适合于查询较小的具有一定结构的文本段。具有事务性。DML 操作后，索引会自动进行同步。

查询标识符CATSEARCH

CTXXPATH（这两个索引没有去更多搜索相关内容）

步骤五：查询测试

SELECt score(20),t.* FROM textdemo t WHERe contains(book_abstract,'移动城堡 or 俄罗斯',20)>0;

--基本查询

--查询包含多个词语and测试通过

测试通过。

5.18 使用CREATE TABLE AS 替代INSERT INTO…select

CREATE TABLE TabName AS SELECT * FROM ……

INSERT INTO TabName SELECt * FROM …… ,

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引。同样在联接多个表时使用索引也可以提高效率。另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列。通常, 在大型表中使用索引特别有效。当然,你也会发现,在扫描小表时,使用索引同样能提高效率。虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O 。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

必要的时候可以强制使用索引

索引对SQL性能的提高：

SELECT * FROM reginfo WHERe to_char(regtime,'yyyy-mm-dd') ='2013-01-01';

换成如下语句:

SELECt * FROM reginfo WHERe regtime = to_date('2013-01-01','yyyy-mm-dd');

效果就好些.

或者创建函数索引.

CREATE INDEX index_abc ON reginfo(to_char(regtime,'yyyy-mm-dd'));

组合索引（索引字段最好不超过三个）举例:

如果我们在查询中使用两列作为条件，就可以创建组合索引

CREATE INDEX index_com ON reginfo(regnick,regtime)

例子:

SELECt * FROM reginfo WHERe regnick='张三' AND regtime=to_date('2013-1-1','yyyy-mm-dd');

SELECt * FROM reginfo WHERe regnick='张三' 也使用了索引index_com

如果我们在regnick 和regtime 上面分别创建了单列索引，通过联合查询后，效率也没有组合索引好些。

如果下查询

SELECt * FROM reginfo WHERe regtime=to_date('2013-1-1','yyyy-mm-dd')

这时使用了组合索引的第二个字段，但是oracle9i 以后就使用了跳跃式索引，如果加上优化就是用了跳跃式索引:

例子：

SELECt * FROM reginfo WHERe

regtime=to_date('2013-1-1','yyyy-mm-dd');

跳跃索引比全表扫描也快，但是要慢于单列索引，所以有必要创建单列索引在此列上。

nvl 相同索引列不能互相比较

SELECt * FROM reginfo WHERe regnick LIKE nvl(regnick,'%');

SELECt * FROM temp_stuinfo WHERe substr(stu_name,1,1)='张';

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行排序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前的执行过的语句完全相同， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

数据库管理员必须在init.ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。

共享的语句必须满足三个条件：

b) 两个语句所指的对象必须完全相同。

健壮性是指在异常情况下，软件能够正常运行的能力。正确性与健壮性的区别是：前者描述软件在需求范围之内的行为，后者描述软件在需求范围之外的行为。想不到异常情况，把异常错当正常而不作处理，这些都会降低健壮性。提高软件的健壮性也是开发者的义务。

容错是指发生异常情况时系统不出错误的能力。高风险系统如航空航天、武器、金融等领域的系统，容错性设计非常重要。

从语义上理解，恢复不及容错那么健壮。

6.1 在创建存储过程语句中提供必要的参数

无论使用哪种编程语言，对输入参数的判断都是必须的。正确的参数验证是保证程序良好运行的前提。

如果输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。

在存储过程执行的过程中，经常因为数据或者其他问题产生异常（condition）。根据业务逻辑，存储过程应该对异常进行相应处理或直接返回给调用者。此处暂且将condition译为异常以方便理解。实际上有些异常（condition）并非是由于错误引起的。

6.4 PL/SQL 异常处理规范

对于存储过程、函数等程序块都要有异常处理部分，以提高程序的自检能力。

EXCEPTION

WHEN NO_DATA_FOUND THEN

当存储过程执行出错抛出EXCEPTION时，可通过 sqlcode 和 sqlerrm取得当前的ORACLE错误代码和错误信息，以下是使用范例：

V_SQLCODE number(6);

V_ERRMSG varchar2(512);

UPDATe dept SET username = (SELECT username FROM work_group)

WHERe rownum=1;

WHEN OTHERS THEN

V_ERRMSG := sqlerrm;

END;

6.6 后台验证异常信息规范

异常信息描述要求简洁、准确、完整，揭示异常实质，准确定位异常出现的位置。

由于每个实际项目，业务不同，异常信息也变化很大。每个项目开始时根据需求，统一进行定义。

6.7 Insert语句健壮性

外键列如没有明确说明not null，可插入null记录（而null是在外部表的记录中没有的），如无可插null记录的想法，要对外键字段加not null约束。

6.9 序列 sequence 跳号的问题

无论在使用Select,还是使用破坏力极大的Update和Delete语句时，一定要检查Where条件判断的完整性，不要在运行时出现数据的重大丢失。

有依赖关系的表，例如主外键关系表，在删除父表时必须级联删除其子表相应数据，或则按照某种业务规则转移该数据。9I中表中字段缩小及变类型，字段为空或表空，varchar和char长度不变可任意改，字段名和表名字段可用 ALTER TABLE table SET UNUSED (column) 设定为不可用，注意无命令再设为可用。