更新时间:2024-09-02 GMT+08:00

PL/pgSQL使用

总体开发原则

  1. 应完全按照设计文档进行开发。
  2. 程序模块应做到高内聚低耦合。
  3. 应有正确、全面的故障对策。
  4. 程序编写应做到结构合理,条理清晰。
  5. 程序名称命名应按照统一的命名规则进行命名。
  6. 应充分考虑程序的运行效率,包括程序的执行效率和数据库的查询、存储效率,在保证应用的同时应使用效率高的处理方法。
  7. 程序注释应详细、正确、规范。
  8. 除非应用特别需要控制commit和rollback的提交时机,否则应在存储过程结束时执行显式的commit或者rollback操作。
  9. 程序处理应支持7*24小时;对于中断,应用程序应提供安全、简单的断点再续处理。
  10. 应提供标准、简单的应用输出,为应用维护人员提供明确的进度显示、错误描述和运行结果;为业务人员提供明确、直观的报表、凭证输出。

程序编写原则

  1. 在PL/PGSQL中的SQL语句宜使用绑定变量。
  2. 在PL/PGSQL中的SQL语句宜使用RETURNING子句。
  3. 存储过程使用原则:
    1. 对于单个存储过程中Varchar或者Varchar2类型输出参数个数不应超过50个。
    2. 不应使用long类型作为输入或输出参数。
    3. 对于大小超过10MB的字符串类型输出,应使用CLOB类型。
  4. 变量声明原则:
    1. 变量声明时,如果含义和应用表某字段含义或某变量相同时,应使用%TYPE声明。
    2. 记录声明时,如果含义和某应用表行数据相同时,应使用%ROWTYPE声明。
    3. 变量声明每行应只包含一条语句。
    4. 不应声明LONG类型的变量。
  5. 游标使用类型:
    1. 显式游标使用后应关闭。
    2. 游标变量使用后应关闭,若游标变量需要传递数据给调用的应用程序,应在应用程序中进行游标关闭处理;若游标变量仅在存储过程中使用,应显式关闭游标。
    3. 在使用DBMS_SQL.CLOSE_CURSOR关闭游标前,应使用DBMS_SQL.IS_OPEN判断游标是否已打开。
  6. 集合使用原则:
    1. 引用集合中的元素时宜使用FORALL语句,不宜使用FOR循环语句。
  7. 动态语句使用原则:
    1. 联机系统的交易程序不宜使用动态SQL。
    2. PL/PGSQL中要实现DDL语句和系统控制命令,可使用动态SQL。
    3. 宜尽量使用变量绑定。
  8. 拼装SQL的使用原则:
    1. 拼装SQL宜使用绑定变量。
    2. 拼装SQL语句的条件如果有外部输入源,应对输入条件进行字符检查,防止攻击。
    3. 在PL/PGSQL脚本中,单行代码的长度,不宜超过2499字符。
  9. Trigger使用原则:
    1. Trigger可用于实现差量数据日志等于业务处理无关的可用性设计场景。
    2. 不应使用Trigger实现业务处理相关功能。

异常处理原则

任何在PL/pgSQL函数中发生的错误会中止该函数的执行,而且实际上会中止其周围的事务。你可以使用一个带有EXCEPTION子句的BEGIN块俘获错误并且从中恢复。

  1. 在使用PL/PGSQL块中,如果使用了不能返回确定结果的SQL语句,宜在EXCEPTION中对程序可能出现的异常进行处理,避免出现未处理的出错被传递到外层块,导致程序逻辑错误。
  2. 对于系统已经定义了的异常,可以直接使用。DWS暂不支持自定义异常。
  3. 进入和退出一个包含EXCEPTION子句的块要比不包含的块开销大的多。因此,非必要场景不应使用EXCEPTION。

书写规范

  1. 变量命名规则:
    1. 过程、函数的输入参数格式宜为:IN_参数名,参数名宜使用大写。
    2. 过程、函数的输出参数格式宜为:OUT_参数名,参数名宜使用大写。
    3. 过程、函数的输入输出参数格式宜为:IO_参数名,参数名宜使用大写。
    4. 过程、函数的程序中用到的变量宜由v_变量名组成,变量名宜使用小写。
    5. 将查询语句做成字符串拼接时,where语句的拼接变量名宜统一为v_where,select语句的拼接变量名宜为v_select。
    6. 记录(RECORD)的类型(TYPE)命名宜由T+变量名组成,名称宜使用大写。
    7. 游标命名宜由CUR+变量名组成,名称宜使用大写。
    8. 引用游标(REF CURSOR)的命名宜由REF+变量名组成,名称宜使用大写。
  2. 变量类型定义:
    1. 变量类型声明时,如果其含义和应用表某字段含义相同时,应使用%TYPE声明。
    2. 记录类型声明时,如果其含义和某应用表行数据相同时,应使用%ROWTYPE声明。
  3. 注释规范:
    1. 注释应该是有意义的,而不应是重述代码。
    2. 注释应简洁、易懂,以中文为主。为了表达准确,名词或操作上也可以使用英文。
    3. 应在每个存储过程、函数得开始加入注释,内容应包括:本程序的简要功能描述、编写者、编写日期、程序版本号信息和程序变更信息,而且各存储过程开头注释应保持统一格式。
    4. 应在输入输出参数的旁边添加注释,注明次变量的意义。
    5. 每个块或大分支的开始宜添加注释,描述块的简要功能,若使用算法,宜添加注释简单描述算法的目的和结果。
  4. 变量声明格式:

    每行应只包含一条语句,如同时需要赋初始值,应在同一行书写。

  5. 大小写规范:

    除了变量名,应一律使用大写。

  6. 缩进规范:

    创建存储过程语句中,同一层的CREATE、AS/IS、BEGIN、END这几个关键字应位于同一列,其他部分依次缩进。

  7. 语句详述:
    1. 变量定义语句。每行应只包含一条语句。
    2. 同一层的IF、ELSEIF、ELSE和END关键字应开始于同一列,执行语句缩进。
    3. CASE和END关键字应位于同一列,WHEN和ELSE关键字应缩进。
    4. 同一层的LOOP和END LOOP关键字应位于同一列,层内语句或嵌套应依次缩进。