原来数据库编程也可以这么美好！

在项目实际开发过程中，最近几次用到oracle中的函数Function、触发器和行列转换，发现有时候直接在数据库层次操作来得更方便

1.Function

说实在的，现在我还不知道怎么去写这个，只是从网上摘选了一段用上了。

由于项目开发过程中，需要直接在SQL把字符串按照分隔符变换为多条记录，具体代码如下：

create or replace function strtab(p_str   in   varchar2)
  return   mytabletype   
  as   
  lstr   varchar2(1000)   default   p_str||',';   
  ln       number;   
  ldata       mytabletype:=mytabletype();   
  begin   
  loop   
      ln:=instr(lstr,',');   
      exit   when   (nvl(ln,0)=0);   
      ldata.extend;   
      ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));   
      lstr:=substr(lstr,ln+1);   
  end   loop;   
  return   ldata;   
  end;

 

2.触发器

项目开发过程中，需要对业务对象的操作进行记录，这时候如果在应用层去做，可能有点麻烦，但是利用数据库的触发器就很好解决了问题

create or replace trigger bd_gqzydp_attachment 
  before delete On mwt_om_fsdata 
  for each row 
  when (old.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988') 
declare
  --定义变量  
  uname    varchar2(64);
begin
  --从另外一张表获取用户姓名 
  SELECT US.USER_WID
    INTO UNAME
    FROM MWT_OM_OBJ OBJ, MWT_IS_USER US
   WHERE US.USER_ID = OBJ.MUSER_ID
     AND OBJ_ID = :OLD.OBJ_ID;

  --更新记录
  insert into bhdzd_log
    (obj_id, attr_id, modify_time, modify_type, user_name)
  values
    (:OLD.obj_id, :OLD.attr_id, sysdate, 'D', uname);
end bd_gqzydp_attachment;

 

Create Or Replace Trigger biu_gqzydp_attachment 
  Before insert or update On mwt_om_fsdata 
  for each row 
  
when (new.attr_id = '4DC345D9-071F-4534-B1C1-B43A93D1C988')
declare
  --定义变量  
  uname    varchar2(64);
  action   varchar2(64);
  filename varchar2(256);
begin
  --从另外一张表获取用户姓名 
  SELECT US.USER_WID
    INTO UNAME
    FROM MWT_OM_OBJ OBJ, MWT_IS_USER US
   WHERE US.USER_ID = OBJ.MUSER_ID
     AND OBJ_ID = :NEW.OBJ_ID;

  --记录操作类型 
  CASE
    WHEN inserting THEN
      action := 'I';
    WHEN updating THEN
      action := 'U';
  END CASE;

  --获取文件名称
  select v.vfile_name
    into filename
    from mwt_is_vfile v
   where v.vfile_id = :NEW.VFILE_ID;

  --更新记录
  insert into bhdzd_log
    (obj_id, attr_id, modify_time, modify_type, user_name, new_filename)
  values
    (:new.obj_id, :new.attr_id, sysdate, action, uname, filename);
end biud_gqzydp_attachment;

 

create or replace trigger bu_rfs_caption 
  before update On mwt_om_obj 
  for each row 
when (old.cls_id = '31181CE1-20E2-422F-92B8-80E3E6AD7FF9')
declare
  --定义变量  
  captionname    varchar2(64);
begin
  captionname := :new.obj_caption;
  if captionname = to_char(sysdate, 'yyyy')||'年'||to_char(sysdate, 'mm')||'月'||to_char(sysdate, 'dd')||'日'  then
     Raise_application_error(-20001, '日方式标题不合法，拒绝修改.'); 
   end if;
end bu_rfs_caption;

这里需要注意的是，对于删除触发器，我们是无法获取删除的文件名的，这里触发器是为了防止脏读。

这是oracle的规定，不能对执行触发器的表进行操作。  
可以对new.xxx进行操作啊，

对于oracle行级触发器(for   each   row)，不能对本表做任何操作，包括读取

原则：  
  在before   insert触发器中，可以实现对本表的访问；  
  在after   insert触发器中，不能实现对本表的访问；  
  在before/after   update/delete触发器中，都不能实现对本表的访问  

这里所说的访问是指不能通过sql语句去访问本表，但是我们可以通过:old和:new来访问表的字段。 

另外再介绍一点

写oracle行级触发器时,不能操作本表,报"表 *** 发生了变化，触发器/函数不能读"的错误的解决办法
原因已经很明显了就是行级的触发器代码中不能操作该表,包括select,是挺郁闷的
当然解决方法就是要根据原因了,正因为限定了行级触发器的操作,只能选择表级的触发器了,但是在表级的触发器又不能获得:new和:old的值,那就只能采取两种触发器并用的方法了,并且还要包或者临时表加以辅助.

首先在行级触发器中将所需的,:new或者:old的值,写到包或者临时表中

然后在表级触发器中处理包或者临时表中已经写入的数据,操作成功后可以按照需求再删除临时表的数据.

3.行列转换

项目中有两种数据需要比较，不过一种是列形式，一种是行形式；

这样把其中一种直接转换，变成同样的形式，这样就方便比较了

/// <summary>
/// 从A获取相关数据的查询字符串
/// </summary>
/// <param name="p_sTableName">A对应数据表名</param>
/// <param name="p_sColName">A对应数据列名</param>
/// <param name="p_date">查询日期</param>
/// <returns>查询字符串</returns>
private string GetEmsSql(string p_sTableName, string p_sColName,DateTime p_date)
{
    StringBuilder result = new StringBuilder();
    
    result.Append("select ");        
    for (int i = 0; i < nDataSum; i++)
    {
        result.Append(GetColumnStr(i, "a", p_sColName, ""));
    }
    result.AppendFormat(" from {0} t where to_char(t.occur_time, 'yyyy-mm-dd') = '{1}' order by t.occur_time", p_sTableName, p_date.ToShortDateString());

    return result.ToString();
}

/// <summary>
/// 获取A和B需要对比的列名
/// </summary>
/// <param name="p_nIndex">对比点的索引</param>
/// <param name="p_type">对比列的归属：EMS|DMIS</param>
/// <returns></returns>
private string GetColumnStr(int p_nIndex, string p_type, string p_sColName, string p_sXs)
{
    string result = string.Empty;
    string type = p_type.ToUpper();

    //获取查询点对应列名
    int hour = p_nIndex / 4;
    int min = p_nIndex % 4 * 15;
    string sIndex = hour.ToString("00") + min.ToString("00");

    switch (type)
    {
        case "A":
            result = string.Format("MAX(decode(to_char(to_char(occur_time, 'hh24')||to_char(occur_time, 'mi'),'0000'), to_char('{0}','0000'), {1}, null)) AS V{2}", sIndex, p_sColName, sIndex);
            break;
        case "B":
            result = string.Format("V{0}*{1} V{0}", sIndex, p_sXs);
            break;
        default:
            break;
    }

    if (p_nIndex + 1 != nDataSum)
    {
        result += string.Format(",");
    }

    return result;
}

不过这里需要对比的列长达2000多列，估计数据库对组成的sql分析就占用很长时间，所以在项目实际应用中还是没有采用这样的办法，但是估计在少量的转换还是划算的

项目实际采用下面的方式

 

/// <summary>
/// 将A获取的数据由行格式转换为列格式
/// </summary>
/// <param name="p_sTableName">A对应数据表名</param>
/// <param name="p_sColName">A对应数据列名</param>
/// <param name="p_date">查询日期</param>
/// <returns>查询结果集</returns>
private DataTable GetADataTable(string p_sTableName, string p_sColName, DateTime p_date)
{
    DataTable result = new DataTable();
    DataColumn dc = null;
    DataRow dr = null;

    string sql = string.Format("select Replace('V'||to_char(to_char(occur_time, 'hh24') ||to_char(occur_time, 'mi'),'0000'),' ','') colname,{0} ", p_sColName);
    sql += string.Format("from {0} t where to_char(t.occur_time, 'yyyy-mm-dd') = '{1}' order by t.occur_time", p_sTableName, p_date.ToString("yyyy-MM-dd"));
    DataTable dt = ADataAccessService.getDataTable(sql);

    string sColName = string.Empty;
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        sColName = dt.Rows[i]["colname"].ToString();
        dc = new DataColumn(sColName, Type.GetType("System.String"));
        result.Columns.Add(dc);
    }

    if (dr == null)
    {
        dr = result.NewRow();
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        sColName = dt.Rows[i]["colname"].ToString();
        dr[sColName] = dt.Rows[i][p_sColName].ToString();     
    }

    result.Rows.Add(dr);

    return result;
}