一个plsql存储过程规范示例

 

根据一份规范文档，写了一个用于日后项目开发的示例用途的存储过程。

 

这篇文档对数据库开发的初学者有帮助。严格执行的规范有利于程序代码的阅读和扩展。

 

规范

变量类型	前缀	范例	说明
数值型	i_	i_variablename	用于number 、integer 等数值类型的变量。
字符型	str_	str_variablename	用于char 、varchar2 等字符类型的变量。
日期型	dt_	dt_variablename	用于date 、timestamp 等日期类型的变量。
BLOB	blb_	blb_blobvariablename	专用于BLOB 类型变量的定义。
CLOB	clb_	clb_clobvariablename	专用于CLOB 类型变量的定义。
LONG	lng_	lng_longvariablename	专用于LONG 类型变量的定义。
游标类型	tcur_	tcur_typename	t 表示类型（type ）； cur 表示游标（cursor ）。
游标	cur_	cur_cursorname	cur 表示游标（cursor ）
自定义记录类型	trcd _	trcd_typename	t 表示类型（type ）； rcd 表示记录（record ）。
自定义记录类型变量	vrcd_	vrcd_variablename	v 表示变量（variable ）； rcd 表示记录（record ）。
自定义嵌套表类型	tntbl_	tntbl_typename	t 表示类型（type ）； ntble 表示嵌套表（nested table ）。
自定嵌套表类型变量	vntbl_	vntbl_variablename	v 表示变量（variable ）； ntble 表示嵌套表（nested table ）。

 

 

 

存储过程示例如下：

create or replace procedure p_rpt_test is
  /**********************************************************************************
  NAME:       p_rpt_test 
  PURPOSE:    报表数据测试
  
  REVISIONS:
  Ver        Date         Author                 Description
  ---------  ----------  ----------------------  ------------------------------------
  V1.0       2012-09-12   WangXL 12345        1.创建此存储过程
  
  RETURN:   无返回值
  
  NOTES:   1、使用到不带参数的游标（即cursor）
           2、使用到for in loop end loop循环
           3、游标打开后，必须关闭。
  
  **********************************************************************************/
  /*按照规划，定义number,string,date三种类型变量名称*/
  i_id          number(8);
  str_testname  varchar2(30);
  dt_createdate date;
  /*按照规划，定义cursor变量名称，游标是一个查询结果集，可以传入变量参数，也可以不传。
  这里的结果集是test1表的三个字段*/
  cursor cur_test1 is
    select a.id, a.testname, a.createtime from test1 a;

begin

  /* for in loop end loop 循环体 */
  for c in cur_test1 loop
    -- 该循环体中，游标自动打开关闭，不需要手工打开再关闭。
  
    -- c 是循环内部变量，为了开发方便，所以简单命名为c。
    -- c 根据循环，依次读取游标cur_test1的每一行记录。
  
    --如果数据量很大，使用其他的方式取游标记录，如bulk collect into
    i_id          := c.id; --赋值游标中一行记录的id列值到i_id变量上
    str_testname  := c.testname || 'xx'; --将游标中一行记录的testname列值加上'xxx'处理后赋值到str_testname变量上
    dt_createdate := c.createtime - 2;
  
    insert into test2
      (id, testname, createtime)
    values
      (i_id, str_testname, dt_createdate); --将变量值插入到test2表中
  
  end loop;

  commit; --所有记录插入后，一次性提交。



  open cur_test1; --打开定义好的游标（cursor）

  /*loop end loop循环体*/

  loop
    fetch cur_test1
      into i_id, str_testname, dt_createdate;
    --fetch就是取游标中一行记录到三个变量中
    exit when cur_test1%notfound; --如果游标已经没有记录了，那么%notfound就是true，从而退出循环；如果还有记录继续下一步
  
    str_testname  := str_testname || 'yy'; --将游标中一行记录的testname列值加上'xxx'处理后赋值到str_testname变量上
    dt_createdate := dt_createdate + 2;
  
    insert into test2
      (id, testname, createtime)
    values
      (i_id, str_testname, dt_createdate); --将变量值插入到test2表中
  
  end loop;

  commit; --所有记录插入后，一次性提交。
  close cur_test1; --关闭开头打开的游标
exception
  when others then
    rollback; --如果上面操作失败，主动使用rollback取消所有的操作。
    dbms_output.put_line(sqlerrm);
end;

 

存储过程展示两种循环体和游标的使用。