根据身份证号码计算星座

最新推荐文章于 2022-09-29 17:53:09 发布

原创最新推荐文章于 2022-09-29 17:53:09 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #星座函数

--1.创建函数get_xingzuo

create or replace function get_xingzuo(yr in varchar2) RETURN VARCHAR2 is xingzuo VARCHAR2(20);

dnum number(4);
dt date;
begin
dt := to_date(yr,'MMDD');
dnum:=to_number(to_char(dt,'MMdd'));
select case
when dnum>=1222 or dnum<=119 then '摩羯座'
when dnum>=120 and dnum<=218 then '水瓶座'
when dnum>=219 and dnum<=320 then '双鱼座'
when dnum>=321 and dnum<=420 then '牡羊座'
when dnum>=421 and dnum<=520 then '金牛座'
when dnum>=521 and dnum<=621 then '双子座'
when dnum>=622 and dnum<=722 then '巨蟹座'
when dnum>=723 and dnum<=822 then '狮子座'
when dnum>=823 and dnum<=922 then '处女座'
when dnum>=923 and dnum<=1022 then '天秤座'
when dnum>=1023 and dnum<=1121 then '天蝎座'
when dnum>=1122 and dnum<=1221 then '射手座'
end into xingzuo from dual;
return xingzuo;
EXCEPTION
WHEN OTHERS THEN
RETURN '双鱼座';

end;

--2.函数使用

select get_xingzuo((substr(111111199608201111, 11, 4))) xz from dual；