文档章节

自定义年龄函数 ★Oracle版本★(规则二)

streamlong
 streamlong
发布于 2015/12/31 15:33
字数 804
阅读 134
收藏 5

精选30+云产品,助力企业轻松上云!>>>

1.DateAdd
CREATE OR REPLACE Function DateAdd
(
    Datepart  In Varchar2,
    c In number,
    curDate   In Date
) Return Date Is
    ReallyDo Date;
Begin
    Select Case Upper(Datepart)
               When 'D' Then
                curDate+c
               Else
                sysdate
           End
    Into ReallyDo
    From Dual;
    Return(ReallyDo);
End DateAdd;
-------------------------------------
2.DateDiff
CREATE OR REPLACE Function Datediff
(
    Datepart  In Varchar2,
    StartDate In Date,
    EndDate   In Date
) Return Number Is
    ReallyDo Numeric;
Begin
    Select Case Upper(Datepart)
               When 'YYYY' Then
                Trunc(Extract(Year From EndDate) -
                      Extract(Year From StartDate))
               When 'M' Then
                Datediff('YYYY', StartDate, EndDate) * 12 +
                (Extract(Month From EndDate) -
                 Extract(Month From StartDate))
               When 'D' Then
                Trunc(EndDate - StartDate)
               When 'H' Then
                Datediff('D', StartDate, EndDate) * 24 +
                (to_Number(to_char(EndDate, 'HH24')) -
                 to_Number(to_char(StartDate, 'HH24')))
               When 'N' Then
                Datediff('D', StartDate, EndDate) * 24 * 60 +
                (to_Number(to_char(EndDate, 'MI')) -
                 to_Number(to_char(StartDate, 'MI')))
               When 'S' Then
                Datediff('D', StartDate, EndDate) * 24 * 60 * 60 +
                (to_Number(to_char(EndDate, 'SS')) -
                 to_Number(to_char(StartDate, 'SS')))
               Else
                -29252888
           End
    Into ReallyDo
    From Dual;
    Return(ReallyDo);
End Datediff;
------------------------------
3.DateDepart
CREATE OR REPLACE Function Datepart
(
    Datepart  In Varchar2,
    curDate   In Date
) Return number Is
    ReallyDo numeric;
Begin
    Select Case Upper(Datepart)
               When 'Y' Then
                to_number(to_char(curDate, 'YYYY'), '999999')
               When 'MM' Then
                to_number(to_char(curDate, 'MM'), '999999')
               When 'D' Then
                to_number(to_char(curDate, 'DD'), '999999')
               When 'H' Then
                to_number(to_char(curDate, 'HH24'), '999999')
               When 'MI' Then
                to_number(to_char(curDate, 'MI'), '999999')
               Else
                -29252888
           End
    Into ReallyDo
    From Dual;
    Return(ReallyDo);
End Datepart;
------------------------------------
4.自定义函数实现uf_getAge
create or replace function uf_GetAge(birthDate in date)
RETURN VARCHAR2 IS
  RESULT   VARCHAR2(50);
 curNow date;
 y number;
 m number;
 d number;
 h number;
 mi number;
 IsRunNian number := 0;
 lastMonthDayCount number;
 nowmonth number;
 nowday number;
 showMonth number := 3;  --显示月的最大年龄 <3
 showDay number := 1;  --显示天的最大月 <1
 showHour number := 3;  --显示小时的最大天 <3
 showMin number := 3;  --显示分钟的最大小时 <3
 allHours number;--总小时数
 allMins number;--总分钟数
 newBirthDate date;
begin
  select sysdate into curNow from dual;

  if to_char(birthDate, 'HH24:MI:SS') > to_char(curNow, 'HH24:MI:SS') then
		 select dateadd('D',1,birthDate) into newBirthDate from dual;
	else
     newBirthDate := birthDate;
  end if;

  select to_char(curNow, 'YYYY') - to_char(newBirthDate, 'YYYY') into y from dual;
  select to_char(curNow, 'MM') - to_char(add_months(newBirthDate, y*12), 'MM') into m from dual;
  select to_char(curNow, 'DD') - to_char(add_months(newBirthDate, y*12+m), 'DD') into d from dual;

--  return d
--  select y,m, d
  if ((mod(datepart('Y', curNow), 4) =0 and mod(datepart('Y', curNow),100)<>0) or ( mod(datepart('Y', curNow),400)=0)) then
    IsRunNian:=1;
  end if;

  select datepart('MM', curNow) into nowmonth  from dual;
  select datepart('D', curNow) into nowday from dual;
  nowmonth := nowmonth - 1;
  if nowmonth in(1,3,5,7,8,10,12) then--进入xx月xx天判断
       lastMonthDayCount := 31;
  elsif nowmonth in(4,6,9,11) then
       lastMonthDayCount := 30;
  elsif nowmonth=2 then
      if IsRunNian=0 then
       lastMonthDayCount:=28;
      elsif IsRunNian=1  then
       lastMonthDayCount:=29;
      end if;
 end if;

--  select lastMonthDayCount

  if (d < 0) then
     d := lastMonthDayCount + d;
     m := m -1;
  end if;

  if (m < 0) then
     m := 12 + m;
     y := y -1;
  end if;

  if(y >=0 and m >= 0 and d >= 0) then
    
    if y > 0 then
       result := to_char(y) || '岁';
    end if;
    
    if (y >= showMonth) then
        RETURN(result);
    end if;

    if m > 0 then
       result := result || to_char(m) || '月';
    end if;
    if (m >=showDay) then
        RETURN(result);
    end if;
    
    if d > 0 then
        result := result || to_char(d) || '天';
    end if;
    if (d >=showHour)  then
        RETURN(result);
    end if;
    
    SELECT  trunc(to_number(sysdate - (TO_DATE(to_char(curNow, 'YYYY-MM-DD ') ||to_char(newBirthDate, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:mi:ss')-1))*24) into allHours from dual; 
    select mod(allHours, 24) into h from dual;
    if h > 0 then
       result := result || h||'小时';
    end if;
    if (h >=showMin)   then
        RETURN(result);
    end if;
    
    SELECT  trunc(to_number(sysdate - (TO_DATE(to_char(curNow, 'YYYY-MM-DD ') ||to_char(newBirthDate, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:mi:ss')-1))*24*60) into allMins from dual;   
    select mod(allMins, 60) into mi from dual;
    if mi > 0 then
       result := result || mi||'分';
    else 
       result := result;
    end if;
    RETURN(result);
  end if;
  return '0';
end uf_getage;


streamlong

streamlong

粉丝 21
博文 45
码字总数 16725
作品 0
杭州
私信 提问
加载中
请先登录后再评论。
RDIFramework.NET ━ .NET快速信息化系统开发框架钜献 V3.0 版本强势发布

继上个版本“RDIFramework.NET V2.9版本”的推出,受到了重多客户的认可与选择,V2.9版本是非常成功与稳定的版本,感谢大家的认可与长期以来的关注与支持。V3.0版本在V2.9版本的基础上做了重...

yonghu86
2016/05/01
0
0
青少年编程竞赛汇总

青少年编程竞赛汇总 目前国家的政策在向多元化评价方式走,国家可不希望只有数学人才。所以越来越多的地方开始对奥数竞赛进行打压。同时举办更多有意思的比赛来逐渐取代奥数的选拔作用。其中...

osc_n5vrwm1c
2018/09/07
2
0
【Python学习笔记】函数的参数问题

定义函数时,就确定了函数名,必选参数个数和必选参数位置。所以函数头其实就定义好了函数调用接口 ★★★★★★★★★★★★★★★★★★★★★★★★函数参数问题★★★★★★★★★★★★...

Master_Li
2016/09/13
27
0
每天玩转3分钟 MyBatis-Plus - 3. 高级查询(一)(条件构造器)

每天玩转3分钟 MyBatis-Plus - 1. 配置环境 每天玩转3分钟 MyBatis-Plus - 2. 普通查询 每天玩转3分钟 MyBatis-Plus - 3. 高级查询 代码下载:https://github.com/Jackson0714/study-mybati...

osc_z7893gdn
01/29
1
0
140种Python标准库、第三方库和外部工具

导读:Python数据工具箱涵盖从数据源到数据可视化的完整流程中涉及到的常用库、函数和外部工具。其中既有Python内置函数和标准库,又有第三方库和工具。 这些库可用于文件读写、网络抓取和解...

osc_mwqvsfzo
2019/07/18
2
0

没有更多内容

加载失败,请刷新页面

加载更多

一种自我润泽的过程一种自我润泽的过程

读书,是一种自我润泽的过程,是一种“润物细无声”的悠然灌溉。书,是水,是一脉清泉,是精神的甘露;而心,则是一朵荷花,根植在水中,无声无息,吸足水分,自我开放,在晴日的午后,或者静...

asd369
43分钟前
22
0
Hacker News 简讯 2020-07-11

更新时间: 2020-07-11 02:01 The TikTok app is no longer permitted on mobile devices that access Amazon email - (twitter.com) TikTok应用程序不再允许在访问亚马逊电子邮件的移动设备上......

FalconChen
今天
150
0
是否有可能从另一个git存储库中挑选一个提交? - Is it possible to cherry-pick a commit from another git repository?

问题: I'm working with a git repository that needs a commit from another git repository that knows nothing of the first. 我正在使用一个git存储库,需要从另一个不知道第一个存储库......

技术盛宴
昨天
29
0
【LeetCode】53 盛最多水的容器

题目 解题思路 双指针法: https://leetcode-cn.com/problems/container-with-most-water/solution/sheng-zui-duo-shui-de-rong-qi-by-leetcode-solution/ 代码 public class Solution { ......

JaneRoad
昨天
20
0
阿里云OSS配置CDN加速

首先购买CDN流量包 然后添加域名 添加好后 然后将域名OSS.xxxx.com 解析到 生成的CDN域名上 这样就完成了

可达鸭眉头一皱
昨天
16
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部