oracle 函数实例

 create or replace function ohs.F_CountUserLog(
       inlogcategory in  varchar2,
       operatorstaffno in  varchar2,
       minTimeStr in varchar2,
       maxTimeStr in varchar2
       )
return integer
as
/*
  Create Date: 2010-12-21
  Create User:PCITLXJ
  DESC: 计算指定时间内用户操作次数
  select max(cbi_id) from cpms_baseinfo
 */
returnMaxValue integer;
strSql varchar2(500);
Begin
      Begin
--         strSql:='select Max('||fieldName||') From  '||tableName;
           strSql:='select count (*)  from ohs.ohs_userviewlog where OPERATORDATETIME >=to_date('''||minTimeStr||''',''yyyy-MM-dd hh24:mi:ss'') and  OPERATORDATETIME <= to_Date('''||maxTimeStr||''',''yyyy-MM-dd hh24:mi:ss'') and OPeratorStaffNo = '''||operatorstaffno||''' and LogCategory ='''||inlogcategory||'''';
         Dbms_Output.put_line(strSql);
         execute immediate (strSql) into returnMaxValue;
         if(returnMaxValue is null) then
             returnMaxValue:=0;
         ELSE
             returnMaxValue:=returnMaxValue;
         End If;
      End;
   return returnMaxValue;
end F_CountUserLog;

 

create or replace function ohs.f_GetAge(brithday date)
 return number
 as
 v_Age number(3);
 begin
 select ceil((sysdate-brithday)/366-1) into v_Age from dual;
 return v_Age;
 end;

create or replace function ohs.F_GetCheckItemPrompt(
       inNORMALVALUETYPE in varchar2,
       inSCOPE in  varchar2,
       inUnit in  varchar2,
       inDEFAULTVALUE in varchar2,
       inNORMALVALUE in varchar2,
       inNORMALMINVALUE in varchar2,
       inNORMALMAXVALUE in varchar2
       )
return varchar2
as
/*
  Create Date: 2010-05-03
  Create User:PCITLXJ
  DESC: 获取体检项提示信息函数
  select max(cbi_id) from cpms_baseinfo
 */
strPROMPT varchar2(500);
Begin
      Begin
        if (inNORMALVALUETYPE='0') Then
                 strPROMPT:='适用范围:'||inSCOPE||';单位:'||inUnit||';默认值:'||inDEFAULTVALUE||';正常值:'||inNORMALVALUE;
              Else
                 strPROMPT:='适用范围:'||inSCOPE||';单位:'||inUnit||';默认值:'||inDEFAULTVALUE||';正常值范围:'||inNORMALMINVALUE||' 至 '||inNORMALMAXVALUE;
              End IF;
      End;
   return strPROMPT;
end F_GetCheckItemPrompt;

create or replace function ohs.F_GetCheckItemResultJudge(
       iid in integer,
       checkresult in  varchar2
       )
return varchar2
is
  returnResult varchar2(10) := '正常';
  checkValue number(8,2);
  CURSOR itemresult_cursor IS select * From OHS.OHS_HEALTHCHECKITEM WHERE HCIID= iid;
  one_Itme itemresult_cursor%rowtype;
Begin
     OPEN itemresult_cursor;
     LOOP
         FETCH itemresult_cursor INTO one_Itme;
         EXIT WHEN itemresult_cursor%notfound;        
         if (TO_CHAR(one_Itme.Normalvaluetype) = '1') then
            checkValue := to_number(checkresult);
            if ((one_Itme.Normalminvalue > checkValue) OR (one_Itme.Normalmaxvalue < checkValue)) then
               returnResult := '异常';
            else
                returnResult := '正常';
            end if;          
         end if;
     END LOOP;

     CLOSE itemresult_cursor;

     return returnResult;
Exception when others then  
     return '';
End F_GetCheckItemResultJudge;

 

create or replace function ohs.F_GetMaxIDByTableName(
       tableName in  varchar2,
       fieldName in  varchar2
       )
return integer
as
/*
  Create Date: 2010-12-21
  Create User:PCITLXJ
  DESC: 获取表格指定字段的最大值 已加　1　针对自增列interger类型数据 供存储过程调用
  select max(cbi_id) from cpms_baseinfo
 */
returnMaxValue integer;
strSql varchar2(500);
Begin
      Begin
         strSql:='select Max('||fieldName||') From  '||tableName;
         execute immediate (strSql) into returnMaxValue;
         if(returnMaxValue is null) then
             returnMaxValue:=1;
         ELSE
             returnMaxValue:=returnMaxValue+1;
         End If;
      End;
   return returnMaxValue;
end F_GetMaxIDByTableName;

create or replace function ohs.F_ValidateExistsReCheck(
       inHcsid in  integer
       )
return integer
as
/*
  Create Date: 2011-06-20
  Create User:PCITLXJ
  DESC: 判断是否存在复查计划项
  select max(cbi_id) from cpms_baseinfo
 */
returnMaxValue integer;
strSql varchar2(500);
Begin
      Begin
         strSql:='select count(*) From ohs.ohs_healthcheckscheme where PREVCHECKID='||inHcsid;
         execute immediate (strSql) into returnMaxValue;
         if(returnMaxValue is null) then
             returnMaxValue:=0;
         End If;
      End;
   return returnMaxValue;
   Exception
      when others then
      return 0;
end F_ValidateExistsReCheck;