1 编码规范

1.1 SQL语句规范

1) SQL 语句中的所有表名、字段名全部小写。

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

3) “不等于”统一使用"<>"。虽然"!="和"<>"是等价的，为了统一，不等于一律使用"<>"表示。

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

5) SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用trim(),lower()等函数格式化匹配条件。

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

7) SQL语句用大写，因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

例如:

SELECT A.ORDER_ID,A.ORDER_CONTENT

FROM CRM_ORDER A,CRM_PRODUCT B

WHERE A.ORDER_ID = B.FK_ORDER_ID

AND A.ORDER_ID = 'A001'

/*关键字、保留字、函数左对齐、首字母尽量大写*/

1.2 SELECT语句的格式标准

SELECT Column_name1,Column_name2

INTO :Parameter1,:Parameter2

FROM Table1 A, Table2 B

WHERE A.Column_name = B.Column_name;

在写查询语句的时候，要求语句的条件排列先后顺序要考虑语句执行的性能，要做到语句能很好的利用到现有的索引，一般原则上不允许在一条查询语句中有超过五张以上的表进行关联，因为当超过五张表关联时Oracle将不再做语句的优化处理；对语句的性能可以通过“执行计划”来跟踪，在PL/SQL Developer中的Explain Plan Window中可分析ORACLE的执行计划。

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

SELECT [DISTINCT | ALL] {* | column1[, column2]…}

FROM {table_1 | (subquery)} [alias]

[, {table_2 | (subquery)} [alias]]…

[WHERE condition]

[CONNECT BY condition [START WITH condition]

[GROUP BY expn] [HAVING expn]

[{ UNION [ALL] | INTERSECT | MINUS } SELECT . . . ]

[ ORDER BY [expn ] [ ASC | DESC]

[ FOR UPDATE [OF [user.]table | view] column ]

[NOWAIT]

1.3 UPDATE语句的格式标准

UPDATE Table SET Column_name1 = :Parameter1,

Column_name2 = :Parameter2

WHERE Column_name = :Pareameter;

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

UPDATE [user.]table[@db_link][alias]

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

(column1[,column2]…)=(subquery) }

[WHERE condition|current of cursor];

1.4 INSERT语句的格式标准

INSERT INTO TableName(Column_name1,Column_name2)

VALUES(:Parameter1,:Parameter2)

或

INSERT INTO TableName(Column_name1,Column_name2)

SELECT Column_name1,Column_name2

FROM Table

WHERE Column_name = :Pareameter

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

也不允许出现 INSERT INTO TableName SELECT * FROM Table 的写法。

1.5 DELETE语句的格式标准

DELETE FROM TableName WHERE Column_name1 = :Parameter1

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

DELETE [ FROM ] [user.]table [@db_link][Alias] [WHERE condition];

1.6 UNION,INTERSECT及MINUS

有时需要从多个表中组合具有一种相似类型的信息。Union 可以完成将两个以上的表的相类似的查询结果合并在一起，并且相同的只取其一；如果union all 则表示返回所有行（不管是否重复）。Intersect返回在两个表中都有相同内容的信息。Minus 则返回只在一个表中出现的信息

1． 语法：

select …

union[all]

select …

select …

intersect

select …

select …

minus

select …

以上语句进行连表操作，而表同表的字段顺序的类型相同但字段标题名可不同，使用ordey by时后面如果是字段名，要求所有的表的字段标题名相同，否则用字段的顺序号

例如：

select id,name,year from user1

union

select no,name,to_number(null) year from user2

order by 1,name,year

union all ,union,Minus,INTERSECT 比较

其中union all 效率最好

Union 集合并，不包含重复行

Union all 集合并，可以包含重复行

Minus 集合差，不包含重复行

INTERSECT 集合交,不包含重复行

逻辑分表,大表分成若干小表

假设一个表的数据有2 千万行，我们可以针对这样给划分成5 个逻辑分表，每个分为500

万行的数据，对于历史数据我们可以分成这样的表来操作，例如一年12 个月每个月都可以

创建一个表.

orderr_01

…

order_12;

这样一个大表就可以有效地分成12 个表的操作,尤其可以按时间或者按地域划分的都可以

这样操作。

可以创建视图连接多张表:

CREATE VIEW v_union_reginfo

AS

SELECT * FROM reginfo

UNION ALL

SELECT * FROM reginfo_temp

1.7 常用语句编写规范

例如: 条件语句

IF GA > 5 THEN

DBMS_OUTPUT.PUT_LINE('AAA');

ELSIF GA < 20 THEN

DBMS_OUTPUT.PUT_LINE('BBB');

ELSE

DBMS_OUTPUT.PUT_LINE('CCC');

END IF;

/*语句开闭位置对应*/

例如: 循环语句A:

FOR I IN 0..10 LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

/*LOOP 和END LOOP 结束*/

例如: 循环语句B:

DECLARE

GA NUMBER(3):=10;

BEGIN

WHILE GA <100 LOOP

DBMS_OUTPUT.PUT_LINE(GA);

GA := GA 1;

END LOOP;

END;

/*LOOP 和END LOOP 结束*/

1.8 游标规范

例如:

OPEN CUR_CRM_ORDER

LOOP

FETCH CUR_CRM_ORDER INTO

EXIT WHERE CUR_CRM_ORDER%NOTFOUND;

…

END LOOP;

CLOSE CUR_CRM_ORDER;

/*程序中显示使用游标的规范*/

带参数的游标写法

DECLARE

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

vregid INT;

cursor cu_a(var1 VARCHAR2) IS

SELECT regid INTO vregid FROM reginfo WHERE regnick=vregnick;

BEGIN

OPEN cu_a(vregnick) ;

Loop

FETCH cu_a INTO vregid;

EXIT WHEN cu_a%NOTFOUND;

dbms_output.put_line(vregid);

END LOOP;

CLOSE cu_a;

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;

END;

2 注释规范

2.1 代码注释规范

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

注释例子：

/*****************************************

模块名称：

模块功能：

创建人：

创建时间：

输入参数：

输出参数：

返回值：

修改历史：==============================

修改人：

修改时间：

修改说明:

================================

***************************************/

2.2 注释位置要求

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

2) 变量申明的注释放在变量申明语句的后面，并以– 为注释语句

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

4) 注释行中，注释命令与注释内容空一个空格，如：

V_BpMode VARCHAR2(10) ; — 用户服务类型

BEGIN

— 选取用户服务类型

BEGIN

SELECT BpMode INTO V_BpMode FROM ACC_BP WHERE User_ID = I_User_ID ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

WHEN OTHERS THEN

END ;

2.3 代码片断注释要求

1) 当处理流程比较复杂，不容易让其它人看懂时，应该加以注释。

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

3 编码命名规范

3.1 命名语言

命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。

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

当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用简写或缩写，缩写要基本能表达原单词的意义。

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

3.2 大小写

名称一律大写，以方便不同数据库移植，以及避免程序调用问题。

3.3 单词分隔

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

3.4 保留字

命名不允许使用SQL保留字。

3.5 命名长度

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

3.6 字段名称

同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。

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

4 编码数据类型规范

4.1 字符型

固定长度的字串类型采用char，长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用trim()函数截去字串后的空格。

4.2 数字型

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

4.3 日期和时间

4.3.1 系统时间

由数据库产生的系统时间首选数据库的日期型，如DATE类型。

4.3.2 外部时间

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

4.4 大字段

如无特别需要，避免使用大字段(blob，clob，long，text，image等)。

4.5 唯一键

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

5 SQL编码技巧

性能通常是指软件的“时间—空间”效率，而不仅是指软件的运行速度。人们总希望软件的运行速度快些，并且占用资源少些。

5.1 SELECT子句中避免使用Select * 语句

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

注意别名的使用：

SELECT A.ORDER_ID,A.ORDER_CONTENT FROM CRM_ORDER A

/*别名使用会加速查询速度*/

并行查询 ，效率可以提高

第一种形式

SELECT /* FULL(T) PARALLEL(T,2) */ * FROM CRM_ORDER T

/*多cpu 使用如上语句可以加速查询速度适合unix 服务器这样的配置*/

第二种形式

SELECT /* PARALLEL(T,2) */ * FROM CRM_ORDER T

5.2 避免嵌套的Select子句

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

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

和一般的观点相反，count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT(EMPNO)。如果没有主键SELECT COUNT(1) FROM stuinfo; 要快一些。

例如：

select count(*) from testtab

/*得到表testtab的记录数*/

select count(id) from testtab

/*得到表testtab id字段非空记录数*/

select count(distinct id) from testtab

/*得到表testtab id字段值非相同记录数*/

5.4 WHERE子句

1) ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

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

3) 避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

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

例如：

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

!= 运算符的使用.

不使用索引

SELECT * FROM reginfo WHERE regid != 8

使用索引

SELECT * FROM reginfo WHERE regid > 8 OR regid < 8

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

(3) ‘ '是数学函数。就象其他数学函数那样, 停用了索引。

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

5.5 用TRUNCATE替代DELETE

当删除表中的记录时，在通常情况下， 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。

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

如果是删除全表，请使用TRUNCATE TABLE TabName 来替代 DELETE FROM TabName，能有效提高速度，并释放该表所占的存储空间，减少磁盘碎片。由于TRUNCATE TABLE是DDL语言，在存储过程中不能直接使用，应加上EXECUTE IMMEDIATE，使用方法如下：EXECUTE IMMEDIATE ‘TRUNCATE TABLE TabName’;

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

5.6 尽量多使用COMMIT

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少 。

COMMIT所释放的资源：

1) 回滚段上用于恢复数据的信息。

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

3) redo log buffer 中的空间。

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

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

　　在第一次建立与数据库的连接时，在缺省情况下，连接是在自动提交模式下的。为了获得更好的性能，可以通过调用带布尔值false参数的Connection类的setAutoCommit()方法关闭自动提交功能，如下所示：

　　conn.setAutoCommit(false);

值得注意的是，一旦关闭了自动提交功能，我们就需要通过调用Connection类的commit()和rollback()方法来人工的方式对事务进行管理。

5.8 避免在索引列上使用IS NULL和IS NOT NULL

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

避免在索引中使用任何可以为空的列，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子句

使用In 或 not In子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。

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

/*in 语句走得全表扫描*/

SELECT * FROM reginfo a WHERE a.regid

IN (SELECT b.regid FROM reginfo_temp b)

/*正确的写法一:*/

SELECT * FROM reginfo a

WHERE EXISTS (SELECT 1 FROM reginfo_temp b WHERE a.regid=b.regid)

/*正确写法二：*/

SELECT a.regid,a.regnick FROM reginfo a

INNER JOIN reginfo_temp b ON a.regid=b.regid

/*正确写法三早期sql 写法:*/

SELECT a.regid,a.regnick FROM reginfo a, reginfo_temp b

WHERE a.regid=b.regid

5.10 用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

5.11 用NOT EXISTS替代NOT IN

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

我们在查询时经常在where 子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT 可用来对任何逻辑运算符号取反。

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

… where not (status ='VALID')

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

… where status <>'INVALID';

再看下面这个例子：

select * from employee where salary<>3000;

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

select * from employee where salary<3000 or salary>3000;

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

有时候会将一列和一系列值相比较。最简单的办法就是在where 子句中使用子查询。在where 子句中可以使用两种格式的子查询。

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

… where column in(select * from … where …);

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

… where exists (select 'X' from …where …);

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

第二种格式中，子查询以‘select 'X'开始。运用EXISTS 子句不管子查询从表中抽取什么数据它只查看where 子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where 语句中使用的列存在索引）。相对于IN 子句来说，EXISTS 使用相连子查询，构造起来要比IN 子查询困难一些。

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

同时应尽可能使用NOT EXISTS 来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS 要比NOT IN 查询效率更高。

5.12 用UNION替换OR (适用于索引列)

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

在下面的例子中, LOC_ID 和REGION上都建有索引。

高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”

低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

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

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

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的。

5.14 用>=替代>

高效: SELECT * FROM EMP WHERE DEPTNO >=4

低效: SELECT * FROM EMP WHERE DEPTNO >3

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

5.15 Order by 语句

ORDER BY 语句决定了Oracle 如何将返回的查询结果排序。Order by 语句对要排序的列没有什么特别的限制，也可以将函数加入列中（联接或者附加等）。任何在Order by 语句的非索引项或者有计算表达式都将降低查询速度。

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

避免不必要的排序，不必要的数据排序大大的降低系统性能。

5.16 避免带通配符（%）的like 语句

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

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

这里由于通配符（%）在搜寻词首出现，所以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 '甘肃%';

可以使用oracle 函数instr 代替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 相当于like‘%手册%’

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

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

有很多时候，使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候。然而通过这些文本定位的方法将导致全表扫描，对资源来说消耗比较昂贵，而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用oralce提供的全文检索功能。

设置全文检索：

步骤一：检查和设置数据库角色

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

步骤二：赋权

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

1 GRANT resource, CONNECT, ctxapp TO oratext;

2 GRANT EXECUTE ON ctxsys.ctx_cls TO oratext;

3 GRANT EXECUTE ON ctxsys.ctx_ddl TO oratext;

4 GRANT EXECUTE ON ctxsys.ctx_doc TO oratext;

5 GRANT EXECUTE ON ctxsys.ctx_output TO oratext;

6 GRANT EXECUTE ON ctxsys.ctx_query TO oratext;

7 GRANT EXECUTE ON ctxsys.ctx_report TO oratext;

8 GRANT EXECUTE ON ctxsys.ctx_thes TO oratext;

9 GRANT EXECUTE ON ctxsys.ctx_ulexer TO oratext;

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

Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来，记录在一组以dr$开头的表中，同时记下该term出现的位置、次数、hash值等信息。检索时，Oracle从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle针对不同的语言提供了不同的lexer,而我们通常能用到其中的三个：

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

chinese_vgram_lexer:专门的汉语分析器，支持所有汉字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term:‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。

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

要指定使用哪一个lexer,可以这样操作：

第一．建立一个preference:

1EXEC ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

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

1CREATE INDEX myindex ON mytable(mycolumn) indextype IS ctxsys.context parameters('lexer my_lexer');

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

全文检索举例：

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

步骤一：授权，ctxsys登陆并对oratext用户授权：

GRANT resource, CONNECT, ctxapp TO oratext;

GRANT EXECUTE ON ctxsys.ctx_cls TO oratext;

GRANT EXECUTE ON ctxsys.ctx_ddl TO oratext;

GRANT EXECUTE ON ctxsys.ctx_doc TO oratext;

GRANT EXECUTE ON ctxsys.ctx_output TO oratext;

GRANT EXECUTE ON ctxsys.ctx_query TO oratext;

GRANT EXECUTE ON ctxsys.ctx_report TO oratext;

GRANT EXECUTE ON ctxsys.ctx_thes TO oratext;

GRANT EXECUTE ON ctxsys.ctx_ulexer TO oratext;

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

BEGIN

–设置词法分析器

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

END;

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

SELECT pre_name, pre_object FROM ctx_preferences

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

步骤三：建立测试表，插入测试数据：

CREATE TABLE textdemo(

id NUMBER NOT NULL PRIMARY KEY,

book_author varchar2(20),–作者

publish_time DATE,–发布日期

title varchar2(400),–标题

book_abstract varchar2(2000),–摘要

path varchar2(200)–路径

);

commit;

INSERT INTO textdemo VALUES(1,'宫琦峻',to_date('2008-10-07','yyyy-mm-dd'),' 移动城堡','故事发生在19世纪末的欧洲，善良可爱的苏菲被恶毒的女巫施下魔咒，从18岁的女孩变成90岁的婆婆，孤单无助的她无意中走入镇外的移动城堡，据说它的主人哈尔以吸取女孩的灵魂为乐，但是事情并没有人们传说的那么可怕，性情古怪的哈尔居然收留了苏菲，两个人在四脚的移动城堡中开始了奇妙的共同生活，一段交织了爱与痛、乐与悲的爱情故事在战火中悄悄展开','E:textsearchmoveingcastle.doc');

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

INSERT INTO textdemo VALUES(3,'袁泉',to_date('2008-10-07','yyyy-mm-dd'),'主演吴彦祖和袁泉现身','电影《如梦》在上海同乐坊拍摄，主演吴彦祖和袁泉现身。由于是深夜拍摄，所以周围并没有过多的fans注意到，给了剧组一个很清净的拍摄环境，站在街头的袁泉低着头，在寒冷的夜里看上去还真有些像女鬼，令人毛骨悚然。','E:textsearchdream.txt');

commit;

步骤四：在book_abstract字段建立索引使用刚刚设置的ORATEXT_LEXER ：chinese_vgram_lexer作为分析器。

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

commit;

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

DR$DEMO_ABSTRACT$I(分词后的TOKEN表)DR$DEMO_ABSTRACT$KDR$DEMO_ABSTRACT$N DR$DEMO_ABSTRACT$R

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

SELECT * FROM ctx_USER_index_errors

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

CONTEXT用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持范围（range）分区，支持并行创建索引（Parallel indexing）的索引类型。

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

查询标识符CONTAINS

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

操作符：and,or,>,;<, =,between,in

查询标识符CATSEARCH

CTXRULE查询标识符MATCHES。

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

一般来说我们建立CONTEXT类型的索引（CONTAINS来查询）。

步骤五：查询测试

–查询或

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

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 or 欧洲',20)>0;

–基本查询

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡',20)>0;

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

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 and 欧洲',20)>0;

测试通过。

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

如果可能，请使用

CREATE TABLE TabName AS SELECT * FROM ……

来替代

INSERT INTO TabName SELECT * FROM …… ,

特别是在记录数比较多的情况下，前者的速度上会有非常明显的优势。

5.19 用索引提高效率

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

/*定期的重构索引是有必要的 */

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

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

SELECT /* INDEX(CRM_ORDER INDEX_A) */ * FROM CRM_ORDER A

/*强制使用索引可以加快查询速度*/

索引对SQL性能的提高：

/*不使用索引的情况，如果在字段regtime 上创建了索引：*/

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');

/*使用了索引index_com*/

SELECT * FROM reginfo WHERE regnick='张三' 也使用了索引index_com

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

如果下查询

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

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

例子：

SELECT /* index(reginfo index_com)*/* FROM reginfo WHERE

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

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

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

/*不使用索引的情况:*/

SELECT * FROM reginfo WHERE regnick = nvl(regnick,'张三');

/*使用索引的情况*/

SELECT * FROM reginfo WHERE regnick LIKE nvl(regnick,'%');

/*不要在索引列上使用运算*/

SELECT * FROM temp_stuinfo WHERE substr(stu_name,1,1)='张';

5.20 选择最有效率的表名顺序(只在基于规则的优化器中有效)

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

如果有3个以上的表连接查询， 那就需要选择交叉表(intersection table)作为基础表， 交叉表是指那个被其他表所引用的表。

5.21 共享SQL语句

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

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。

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

当你向ORACLE提交一个SQL语句，ORACLE会首先在这块内存中查找相同的语句。这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等)。

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

a) 字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同。

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

c) 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。

6 健壮性

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

健壮性有两层含义：一是容错能力，二是恢复能力。

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

容错是非常健壮的意思。而恢复则是指软件发生错误后（不论死活）重新运行时，能否恢复到没有发生错误前的状态的能力。

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

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

创建存储过程语句可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供这些参数可以提高执行效率。

6.2 对输入参数进行必要的的检查和预处理

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

正确的验证和预处理操作包括：

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

根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，NULL与空字符串或0的转换等。

6.3 异常处理

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

6.4 PL/SQL 异常处理规范

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

例如:

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

6.5 在 PL/SQL 中使用 sqlcode,sqlerrm

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

DECLARE

V_SQLCODE number(6);

V_ERRMSG varchar2(512);

BEGIN

UPDATE dept SET username = (SELECT username FROM work_group)

WHERE rownum=1;

EXCEPTION

WHEN OTHERS THEN

V_SQLCODE := sqlcode;

V_ERRMSG := sqlerrm;

Dmbs_output.put_line (‘程序出错，错误代码:’|| V_SQLCODE||’错误信息:’|| V_ERRMSG);

END;

6.6 后台验证异常信息规范

使用数据库过程、函数进行后台数据验证时，发现异常情况，需要记录异常，并返回到用户界面。

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

异常分为警告和错误两类。

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

6.7 Insert语句健壮性

使用Insert语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。

6.8 外键值可用null的问题

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

6.9 序列 sequence 跳号的问题

sequence 因回滚，系统崩溃（使用cache 内的值将认为已用），多表引用都将使其跳号，所以不能用于连续序号 。

7 安全性和完整性约束

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

如果不确定，最好先用Select语句带上相同条件来验证一下结果集，来检验条件是否正确。

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