熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle自定義函數

2013-11-13 22:16:13  來源: Oracle 

  判斷任務過期時間

  create or replace function GetUrgentState(m_TaskID   varchar

  m_SendTime date

  m_flag     varchar)

  return varchar IS

  myDate     date;

  ExpireTime date;

  strsql     varchar();

  begin

  myDate := m_SendTime;

  strsql := select max(EXPIRETIME) from t_wf_supervise where TASKID = ||

  m_TaskID || ;

  execute immediate strsql

  into ExpireTime;

  沒有到期時間 就是正常狀態

  if ExpireTime is null then

  if m_flag = String then

  return 正常;

  end if;

  if m_flag = Img then

  return cb_executegif;

  end if;

  end if;

  未發送任務就是判斷當前時間

  if m_SendTime is null then

  myDate := sysdate;

  end if;

  if ExpireTime < myDate then

  if m_flag = String then

  return 超期;

  end if;

  if m_flag = Img then

  return cb_limitgif;

  end if;

  end if;

  小於天的任務預警

  if ExpireTime myDate < then

  if m_flag = String then

  return 預警;

  end if;

  if m_flag = Img then

  return cb_warninggif;

  end if;

  else

  if m_flag = String then

  return 正常;

  end if;

  if m_flag = Img then

  return cb_executegif;

  end if;

  end if;

  end;

  查詢其它表數據

  create or replace function GetPreNode(m_PreTaskID varchar) return varchar IS

  nodename varchar();

  strsql   varchar();

  begin

  if m_PreTaskID is null then

  return ;

  end if;

  strsql := select max(nodename) from t_Wf_Tasklist where TaskID = ||

  m_PreTaskID|| ;

  execute immediate strsql

  into nodename;

  return nodename;

  end;

  格式化標題輸出

  create or replace function FormatTitle(m_title    varchar

  m_length   number

  m_FillChar varchar) return varchar IS

  begin

  if lengthb(m_title) > m_length* then

  return substr(m_title m_length) || m_FillChar;

  else

  return m_title;

  end if;

  end;


From:http://tw.wingwit.com/Article/program/Oracle/201311/18645.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.