oracle下sql编程规范

1. 视图使用规范 1.1 视图的命名规则 1、 视图名用 V_开头，视图名长度不能超过 30 个字符。视图名用大写的英文单词来表示。 2、 视图由几个表产生就用下划线（_）连接几个表的名，如果表过多可以将表名适当简化， 但一定要列出所有表名。 1.2 必须在视图中指定每列名称的情形 1、 视图中有任何从算术表达式、内置函数或常量派生出的列。 2、 视图中两列或多列具有相同名称（通常由于视图定义包含联接，而来自两个或多个不 同表的列具有相同的名称） 。 3、 希望使视图中的列名与它的源列名不同。 （也可以在视图中重命名列。 ）无论重命名与 否，视图列都会继承其源列的数据类型。 1.3 其他 1、 不能将 AFTER 触发器与视图相关联，只有 INSTEAD OF 触发器可以与之相关联。 2、 定义视图的查询不可以包含 ORDER BY、COMPUTE 或 COMPUTE BY 子句或 INTO 关键字， 否则会占用大量时间。 3、 不能在视图上定义全文索引定义。 4、 不能创建临时视图，也不能在临时表上创建视图。 5、 不能除去参与到用 SCHEMABINDING 子句创建的视图中的表或视图，除非该视图已被除 去或更改而不再具有架构绑定。另外，如果参与具有架构绑定的视图的表执行 ALTER TABLE 语句影响视图定义，则这些语句将失败。 6、 不能对视图执行全文查询，但是如果查询所引用的表被配置为支持全文索引，就可以 在视图定义中包含全文查询。 7、 一般不建议试图套视图的建法，一个是逻辑太复杂，第二是如果底层视图发生问题， 那级联的所有视图都有问题。 8、 对多个表关联的查询建立视图，注意表间关键的字段以及条件字段是否用到索引。因 为视图其实就是 SQL，只不过封装了下，建立一个多表关联而又没有合理使用索引的视 图，效率是很低的。 2. 触发器使用规范 2.1 触发器命名规范 1、 触发器名用 Tr_开头，触发器名长度不能超过 30 个字符。必须缩写表名，以便附加触 发器属性信息。触发器名用小写的英文单词来表示。 2、 2.2 触发器使用规范 1、 触发器的使用，应该放在关键的，多方发起的，高频访问的数据表上。过多使用触发 器，会增加数据库负担，降低数据库性能。 2、 一个数据表可以有多个触发器，但是一个触发器只能对应一个表。 3、 Truncate Table 语句虽然类似于 Delete 语句可以删除记录，但是它不能激活 Delete 类型 的触发器。因为 Truncate Table 语句是不记入日志的。 4、 WRITETEXT 语句不能触发 Insert 和 Update 型的触发器。 5、 不同的 SQL 语句，可以触发同一个触发器，如 Insert 和 Update 语句都可以激活同一个 触发器 2.3触发器编写规范 1、 触发器代码的大小不能超过 32K。如果确实需要使用大量代码建立触发器，应该首先建 立存储过程，然后在触发器中使用 call 语句调用存储过程 2、 触发器只能包 SELECT，INSERT，UPDATE，DELET 语句，而不能包含 DDL 语句 （CREATE，ALTER，DROP）和事务控制性语句（COMMIT，ROLLBACK 和 SAVEPOINT） ， 也禁止直接或间接地调用含有上属于据的存储过程。 3、 触发器不接受参数,一个表最多可有 12 个触发器( 触发器类型刚好是 12 种),并且保证触 发器操作不冲突,同一时间,同一事件 ,同一类型的触发器只能有一个。 4、 建议不要让触发器返回任何结果。因为对这些返回结果的特殊处理必须写入每个允许 对触发器表进行修改的应用程序中。为了阻止从触发器返回任何结果，不要在触发器 内定义包含 SELECT 语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触 发器的开头使用 SET NOCOUNT ON 语句以避免返回任何结果集。 5、 在 DML 触发器中不能使用的语句 不能使用的语句 语句功能 Alter Database 修改数据库 Create Database 新建数据库 Drop Database 删除数据库 Load Database 导入数据库 Load Log 导入日志 Reconfigure 更新配置选项 Restore Database 还原数据库 Restore Log 还原数据库日志 2.4 建立 INSTEAD OF 触发器规范 1、 不能在带有 WITH CHECK OPTION 定义的视图中创建 INSTEAD OF 触发器。 2、 INSTEAD OF 选项只适用于视图。 3、 当基于视图建立触发器时，不能指定 before 和 after 选项。 4、 在同一个数据表中，对每个操作（如 Insert、Update、Delete）而言可以建立许多个 After 触发器，但 Instead Of 触发器针对每个操作只有建立一个。 5、 当建立 INSTEAD OF 触发器时，必须指定 for each row 选项。 6、 3 存储过程使用规范 3.1 存储过程命名规范 1、 存储过程名用 Pr_开头，存储过程名长度不能超过 30 个字符。存储过程名用小写的英 文单词来表示。 2、 对于类型的声明，尽量使用%TYPE 及%ROWTYPE 方式进行，预防因数据库的变动对程 序的影响。 3.2 编写存储过程的注意点 3.3.1 SQL 的使用规范 1、 尽量避免大事务操作，慎用 holdlock 子句，提高系统并发能力。 2、 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提 取数据到临时表中，然后再做连接。 3、 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就 应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。 4、 注意 where 字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件 子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。 5、 不要在 where 子句中的“= ”左边进行函数、算术运算或其他表达式运算，否则系统 将可能无法正确使用索引。 6、 尽量使用 exists 代替 select count(1)来判断是否存在记录，count 函数只有在统计表中 所有行数时使用，而且 count(1)比 count(*)更有效率。 7、 根据处理逻辑，程序中可能要做几个动作或者运算，每个动作或者运算作为一个逻辑 区块处理，应使用 BEGIN 及 END sec1 进行相应的标识。 8、 注释分为两种情况，即单行注释和多行区块注释。格式分别如下： - 单行注释示例 /* * 多行区块注释 */ 对于每个 PL/SQL 程序要在开始的多行区块注释中写明该段代码的书写人，创建日期，功能 描述，修改日志内容。 9、 代码之间空行不要超过一行，子程序之间空两行。 10、 使用制表符(TAB) 控制文本缩进。 11、 对于可能遇到的异常要提早作出预定，并在其可能发生的位置上将其捕获处理掉。 12、 删除不使用的变量：在程序编译之后，会提示那些变量没有使用到，对于这些没 有用到的变量需要把它删除掉。 13、 避免变量循环使用：一个变量在一个区块中使用即可，不要循环使用，以避免因 此产生的问题。 14、 尽量使用“=” ，不要使用“” 。 viii. 注意一些 or 子句和 union 子句之间的替换。 15、 注意表之间连接的数据类型，避免不同类型数据之间的连接。 16、 注意 Oracle 存储过程中参数和数据类型的关系。 17、 注意 insert、update 操作的数据量，防止与其他应用冲突。如果数据量超过 200 个数据页面（400k） ，那么系统将会进行锁升级，页级锁会升级成表级锁。 3.3.2 循环的使用规范 1、 对于 FOR 和 WHILE 循环不要 EXIT 和 RETURN； 2、 仅在执行指定次数时使用 FOR 循环； 3、 仅在条件边界为 FALSE 时退出循环； 4、 Cursor FOR loop 通常效率均比 OPEN-FETCH-EXIT-CLOSE 好，并且后者更复杂；作为一个 规则，仅在以下情况下使用 OPEN-FETCH-EXIT-CLOSE： 1）要与游标变量一起工作，编译器不能自动声明 ref cursors 的 cursor%ROWTYPE； 2）希望在循环后保留值。 3）使用匿名游标循环，FOR r IN (SELECT cols FROM sometable) LOOP；但是不能重用， 也不能使用 cursors %ROWTYPE。 3.3.3 索引的使用规范 1、 索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引。 2、 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引。 3、 避免对大表查询时进行 table scan，必要时考虑新建索引。 4、 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第 一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 5、 要注意索引的维护，周期性重建索引，重新编译 Oracle 存储过程。 3.3.4 tempdb 的使用规范 1、 尽量避免使用 distinct、order by、group by、having、join、*pute ，因为这些语句会 加重 tempdb 的负担。 2、 避免频繁创建和删除临时表，减少系统表资源的消耗。 3、 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免 log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先 create table，然后 insert。 4、 如果临时表的数据量较大，需