oracle农历脚本

来源：互联网发布：致翔软件编辑：程序博客网时间：2024/05/27 21:07

CREATE TABLE SolarData (
yearid decimal(16, 0),
data char(7),
dataint decimal(16, 0));
INSERT INTO SolarData VALUES (1900, '0x04bd8', 19416);
INSERT INTO SolarData VALUES (1901, '0x04ae0', 19168);
INSERT INTO SolarData VALUES (1902, '0x0a570', 42352);
INSERT INTO SolarData VALUES (1903, '0x054d5', 21717);
INSERT INTO SolarData VALUES (1904, '0x0d260', 53856);
INSERT INTO SolarData VALUES (1905, '0x0d950', 55632);
INSERT INTO SolarData VALUES (1906, '0x16554', 91476);
INSERT INTO SolarData VALUES (1907, '0x056a0', 22176);
INSERT INTO SolarData VALUES (1908, '0x09ad0', 39632);
INSERT INTO SolarData VALUES (1909, '0x055d2', 21970);
INSERT INTO SolarData VALUES (1910, '0x04ae0', 19168);
INSERT INTO SolarData VALUES (1911, '0x0a5b6', 42422);
INSERT INTO SolarData VALUES (1912, '0x0a4d0', 42192);
INSERT INTO SolarData VALUES (1913, '0x0d250', 53840);
INSERT INTO SolarData VALUES (1914, '0x1d255', 119381);
INSERT INTO SolarData VALUES (1915, '0x0b540', 46400);
INSERT INTO SolarData VALUES (1916, '0x0d6a0', 54944);
INSERT INTO SolarData VALUES (1917, '0x0ada2', 44450);
INSERT INTO SolarData VALUES (1918, '0x095b0', 38320);
INSERT INTO SolarData VALUES (1919, '0x14977', 84343);
INSERT INTO SolarData VALUES (1920, '0x04970', 18800);
INSERT INTO SolarData VALUES (1921, '0x0a4b0', 42160);
INSERT INTO SolarData VALUES (1922, '0x0b4b5', 46261);
INSERT INTO SolarData VALUES (1923, '0x06a50', 27216);
INSERT INTO SolarData VALUES (1924, '0x06d40', 27968);
INSERT INTO SolarData VALUES (1925, '0x1ab54', 109396);
INSERT INTO SolarData VALUES (1926, '0x02b60', 11104);
INSERT INTO SolarData VALUES (1927, '0x09570', 38256);
INSERT INTO SolarData VALUES (1928, '0x052f2', 21234);
INSERT INTO SolarData VALUES (1929, '0x04970', 18800);
INSERT INTO SolarData VALUES (1930, '0x06566', 25958);
INSERT INTO SolarData VALUES (1931, '0x0d4a0', 54432);
INSERT INTO SolarData VALUES (1932, '0x0ea50', 59984);
INSERT INTO SolarData VALUES (1933, '0x06e95', 28309);
INSERT INTO SolarData VALUES (1934, '0x05ad0', 23248);
INSERT INTO SolarData VALUES (1935, '0x02b60', 11104);
INSERT INTO SolarData VALUES (1936, '0x186e3', 100067);
INSERT INTO SolarData VALUES (1937, '0x092e0', 37600);
INSERT INTO SolarData VALUES (1938, '0x1c8d7', 116951);
INSERT INTO SolarData VALUES (1939, '0x0c950', 51536);
INSERT INTO SolarData VALUES (1940, '0x0d4a0', 54432);
INSERT INTO SolarData VALUES (1941, '0x1d8a6', 120998);
INSERT INTO SolarData VALUES (1942, '0x0b550', 46416);
INSERT INTO SolarData VALUES (1943, '0x056a0', 22176);
INSERT INTO SolarData VALUES (1944, '0x1a5b4', 107956);
INSERT INTO SolarData VALUES (1945, '0x025d0', 9680);
INSERT INTO SolarData VALUES (1946, '0x092d0', 37584);
INSERT INTO SolarData VALUES (1947, '0x0d2b2', 53938);
INSERT INTO SolarData VALUES (1948, '0x0a950', 43344);
INSERT INTO SolarData VALUES (1949, '0x0b557', 46423);
INSERT INTO SolarData VALUES (1950, '0x06ca0', 27808);
INSERT INTO SolarData VALUES (1951, '0x0b550', 46416);
INSERT INTO SolarData VALUES (1952, '0x15355', 86869);
INSERT INTO SolarData VALUES (1953, '0x04da0', 19872);
INSERT INTO SolarData VALUES (1954, '0x0a5d0', 42448);
INSERT INTO SolarData VALUES (1955, '0x14573', 83315);
INSERT INTO SolarData VALUES (1956, '0x052d0', 21200);
INSERT INTO SolarData VALUES (1957, '0x0a9a8', 43432);
INSERT INTO SolarData VALUES (1958, '0x0e950', 59728);
INSERT INTO SolarData VALUES (1959, '0x06aa0', 27296);
INSERT INTO SolarData VALUES (1960, '0x0aea6', 44710);
INSERT INTO SolarData VALUES (1961, '0x0ab50', 43856);
INSERT INTO SolarData VALUES (1962, '0x04b60', 19296);
INSERT INTO SolarData VALUES (1963, '0x0aae4', 43748);
INSERT INTO SolarData VALUES (1964, '0x0a570', 42352);
INSERT INTO SolarData VALUES (1965, '0x05260', 21088);
INSERT INTO SolarData VALUES (1966, '0x0f263', 62051);
INSERT INTO SolarData VALUES (1967, '0x0d950', 55632);
INSERT INTO SolarData VALUES (1968, '0x05b57', 23383);
INSERT INTO SolarData VALUES (1969, '0x056a0', 22176);
INSERT INTO SolarData VALUES (1970, '0x096d0', 38608);
INSERT INTO SolarData VALUES (1971, '0x04dd5', 19925);
INSERT INTO SolarData VALUES (1972, '0x04ad0', 19152);
INSERT INTO SolarData VALUES (1973, '0x0a4d0', 42192);
INSERT INTO SolarData VALUES (1974, '0x0d4d4', 54484);
INSERT INTO SolarData VALUES (1975, '0x0d250', 53840);
INSERT INTO SolarData VALUES (1976, '0x0d558', 54616);
INSERT INTO SolarData VALUES (1977, '0x0b540', 46400);
INSERT INTO SolarData VALUES (1978, '0x0b5a0', 46496);
INSERT INTO SolarData VALUES (1979, '0x195a6', 103846);
INSERT INTO SolarData VALUES (1980, '0x095b0', 38320);
INSERT INTO SolarData VALUES (1981, '0x049b0', 18864);
INSERT INTO SolarData VALUES (1982, '0x0a974', 43380);
INSERT INTO SolarData VALUES (1983, '0x0a4b0', 42160);
INSERT INTO SolarData VALUES (1984, '0x0b27a', 45690);
INSERT INTO SolarData VALUES (1985, '0x06a50', 27216);
INSERT INTO SolarData VALUES (1986, '0x06d40', 27968);
INSERT INTO SolarData VALUES (1987, '0x0af46', 44870);
INSERT INTO SolarData VALUES (1988, '0x0ab60', 43872);
INSERT INTO SolarData VALUES (1989, '0x09570', 38256);
INSERT INTO SolarData VALUES (1990, '0x04af5', 19189);
INSERT INTO SolarData VALUES (1991, '0x04970', 18800);
INSERT INTO SolarData VALUES (1992, '0x064b0', 25776);
INSERT INTO SolarData VALUES (1993, '0x074a3', 29859);
INSERT INTO SolarData VALUES (1994, '0x0ea50', 59984);
INSERT INTO SolarData VALUES (1995, '0x06b58', 27480);
INSERT INTO SolarData VALUES (1996, '0x055c0', 21952);
INSERT INTO SolarData VALUES (1997, '0x0ab60', 43872);
INSERT INTO SolarData VALUES (1998, '0x096d5', 38613);
INSERT INTO SolarData VALUES (1999, '0x092e0', 37600);
INSERT INTO SolarData VALUES (2000, '0x0c960', 51552);
INSERT INTO SolarData VALUES (2001, '0x0d954', 55636);
INSERT INTO SolarData VALUES (2002, '0x0d4a0', 54432);
INSERT INTO SolarData VALUES (2003, '0x0da50', 55888);
INSERT INTO SolarData VALUES (2004, '0x07552', 30034);
INSERT INTO SolarData VALUES (2005, '0x056a0', 22176);
INSERT INTO SolarData VALUES (2006, '0x0abb7', 43959);
INSERT INTO SolarData VALUES (2007, '0x025d0', 9680);
INSERT INTO SolarData VALUES (2008, '0x092d0', 37584);
INSERT INTO SolarData VALUES (2009, '0x0cab5', 51893);
INSERT INTO SolarData VALUES (2010, '0x0a950', 43344);
INSERT INTO SolarData VALUES (2011, '0x0b4a0', 46240);
INSERT INTO SolarData VALUES (2012, '0x0baa4', 47780);
INSERT INTO SolarData VALUES (2013, '0x0ad50', 44368);
INSERT INTO SolarData VALUES (2014, '0x055d9', 21977);
INSERT INTO SolarData VALUES (2015, '0x04ba0', 19360);
INSERT INTO SolarData VALUES (2016, '0x0a5b0', 42416);
INSERT INTO SolarData VALUES (2017, '0x15176', 86390);
INSERT INTO SolarData VALUES (2018, '0x052b0', 21168);
INSERT INTO SolarData VALUES (2019, '0x0a930', 43312);
INSERT INTO SolarData VALUES (2020, '0x07954', 31060);
INSERT INTO SolarData VALUES (2021, '0x06aa0', 27296);
INSERT INTO SolarData VALUES (2022, '0x0ad50', 44368);
INSERT INTO SolarData VALUES (2023, '0x05b52', 23378);
INSERT INTO SolarData VALUES (2024, '0x04b60', 19296);
INSERT INTO SolarData VALUES (2025, '0x0a6e6', 42726);
INSERT INTO SolarData VALUES (2026, '0x0a4e0', 42208);
INSERT INTO SolarData VALUES (2027, '0x0d260', 53856);
INSERT INTO SolarData VALUES (2028, '0x0ea65', 60005);
INSERT INTO SolarData VALUES (2029, '0x0d530', 54576);
INSERT INTO SolarData VALUES (2030, '0x05aa0', 23200);
INSERT INTO SolarData VALUES (2031, '0x076a3', 30371);
INSERT INTO SolarData VALUES (2032, '0x096d0', 38608);
INSERT INTO SolarData VALUES (2033, '0x04bd7', 19415);
INSERT INTO SolarData VALUES (2034, '0x04ad0', 19152);
INSERT INTO SolarData VALUES (2035, '0x0a4d0', 42192);
INSERT INTO SolarData VALUES (2036, '0x1d0b6', 118966);
INSERT INTO SolarData VALUES (2037, '0x0d250', 53840);
INSERT INTO SolarData VALUES (2038, '0x0d520', 54560);
INSERT INTO SolarData VALUES (2039, '0x0dd45', 56645);
INSERT INTO SolarData VALUES (2040, '0x0b5a0', 46496);
INSERT INTO SolarData VALUES (2041, '0x056d0', 22224);
INSERT INTO SolarData VALUES (2042, '0x055b2', 21938);
INSERT INTO SolarData VALUES (2043, '0x049b0', 18864);
INSERT INTO SolarData VALUES (2044, '0x0a577', 42359);
INSERT INTO SolarData VALUES (2045, '0x0a4b0', 42160);
INSERT INTO SolarData VALUES (2046, '0x0aa50', 43600);
INSERT INTO SolarData VALUES (2047, '0x1b255', 111189);
INSERT INTO SolarData VALUES (2048, '0x06d20', 27936);
INSERT INTO SolarData VALUES (2049, '0x0ada0', 44448);

COMMIT;

CREATE OR REPLACE FUNCTION f_getlunar (
   i_solarday   DATE,
   i_flag       VARCHAR2 DEFAULT '1'
)
   RETURN VARCHAR2
-- 功能：计算阳历1900/01/31 - 2050/01/22间某一天对应的阴历是多少
-- 算法：在一张表中用10进制格式保存某个农历年每月大小,有无闰月,闰月大小信息
-- 1.用12个2进制位来表示某个农历年每月的大小，大月记为1，否则为0
-- 2.用低4位来表示闰月的月份，没有闰月记为0
-- 3.用一个高位表示闰月的大小，闰月大记为0，闰月小或无闰月记为0
-- 4.再将该2进制数转化为10进制，存入表中
-- 农历2000年: 0 110010010110 0000 -> 0x0c960 -> 51552
-- 农历2001年: 0 110110010101 0100 -> 0x0d954 -> 55636
-- 采用查表的方式计算出农历日期
-- 作者：Angel_XJW
-- 修改：jjx
-- 2.
AS
   v_offset          INT;
   v_lunar           INT; -- 农历年是否含闰月,几月是闰月,闰月天数,其它月天数
   v_yeardays        INT;                                   -- 农历年所含天数
   v_monthdays       INT;                                   -- 农历月所含天数
   v_leapmonthdays   INT;                                 -- 农历闰月所含天数
   v_leapmonth       INT;                 -- 农历年闰哪个月 1-12 , 没闰传回 0
   v_leapflag        INT;                   -- 某农历月是否为闰月 1:是 0:不是
   v_monthno         INT; -- 某农历月所对应的2进制数如农历3月: 001000000000
   i                 INT;
   j                 INT;
   k                 INT;
   tg                INT;
   dz                INT;
   nlcount           INT;
   nlcnt             INT;
   v_year            INT;                          -- i_SolarDay 对应的农历年
   v_month           INT;                          -- i_SolarDay 对应的农历月
   v_day             INT;                          -- i_SolarDay 对应的农历日
   o_outputdate      VARCHAR2 (31); -- 返回值格式：农历 ****年 **(闰)月 **日
   e_errmsg          VARCHAR2 (200);
   v_lunardate       VARCHAR2 (120)
      :=    '初一初二初三初四初五初六初七初八初九初十'
         || '十一十二十三十四十五十六十七十八十九二十'
         || '廿一廿二廿三廿四廿五廿六廿七廿八廿九三十';
   v_lunarmonth      VARCHAR2 (48)
                         := '正月二月三月四月五月六月七月八月九月十月冬月腊月';
   v_tiangan         VARCHAR2 (20) := '甲乙丙丁戊己庚辛壬癸';
   v_dizhi           VARCHAR2 (24) := '子丑寅卯辰巳午未申酉戌亥';
   v_shengxiao       VARCHAR2 (24) := '鼠牛虎兔龙蛇马羊猴鸡狗猪';
   v_nlyear          VARCHAR2 (4);
   p_enter           VARCHAR2 (10) := CHR (13) || CHR (10);
   e_errdate         EXCEPTION;
BEGIN
--输入参数判断
   IF    i_solarday < TO_DATE ('1900-01-31', 'YYYY-MM-DD')
      OR i_solarday >= TO_DATE ('2050-01-23', 'YYYY-MM-DD')
   THEN
      RAISE e_errdate;
   END IF;

-- i_SolarDay 到 1900-01-30(即农历1900-01-01的前一天) 的天数
v_offset := TRUNC (i_solarday, 'DD') - TO_DATE ('1900-01-30', 'YYYY-MM-DD');
-- 确定农历年开始
i := 1900;

   WHILE i < 2050 AND v_offset > 0
   LOOP
      v_yeardays := 348;
      -- 29*12 以每年12个农历月,每个农历月含29个农历日为基数
      v_leapmonthdays := 0;

-- 取出农历年是否含闰月,几月是闰月,闰月天数,其它月天数
-- 如农历2001年: 0x0d954(16进制) -> 55636(10进制) -> 0 110110010101 0100(2进制)
-- 1,2,4,5,8,10,12月大, 3,6,7,9,11月小, 4月为闰月，闰月小
      SELECT dataint
        INTO v_lunar
        FROM solardata
       WHERE yearid = i;

-- 传回农历年的总天数
j := 32768; -- 100000000000 0000 -> 32768

-- 0 110110010101 0100 -> 55636(农历2001年)
-- 依次判断v_Lunar年个月是否为大月，是则加一天
      WHILE j > 8
      LOOP                            -- 闰月另行判断 8 -> 0 000000000000 1000
         IF BITAND (v_lunar, j) + 0 > 0
         THEN
            v_yeardays := v_yeardays + 1;
         END IF;

j := j / 2; -- 判断下一个月是否为大
END LOOP;

-- 传回农历年闰哪个月 1-12 , 没闰传回 0 15 -> 1 0000
v_leapmonth := BITAND (v_lunar, 15) + 0;

-- 传回农历年闰月的天数 ,加在年的总天数上
      IF v_leapmonth > 0
      THEN
-- 判断闰月大小 65536 -> 1 000000000000 0000
         IF BITAND (v_lunar, 65536) + 0 > 0
         THEN
            v_leapmonthdays := 30;
         ELSE
            v_leapmonthdays := 29;
         END IF;

v_yeardays := v_yeardays + v_leapmonthdays;
END IF;

      v_offset := v_offset - v_yeardays;
      i := i + 1;
   END LOOP;

   IF v_offset <= 0
   THEN
-- i_SolarDay 在所属农历年(即i年)中的第 v_OffSet 天
      v_offset := v_offset + v_yeardays;
      i := i - 1;
   END IF;

-- 确定农历年结束
v_year := i;
-- 确定农历月开始
i := 1;

   SELECT dataint
     INTO v_lunar
     FROM solardata
    WHERE yearid = v_year;

-- 判断那个月是润月
-- 如农历2001年 (55636,15 -> 0 1101100101010100, 1111 -> 4) 即润4月,且闰月小
v_leapmonth := BITAND (v_lunar, 15) + 0;
v_leapflag := 0;

   WHILE i < 13 AND v_offset > 0
   LOOP
-- 判断是否为闰月
      v_monthdays := 0;

      IF (v_leapmonth > 0 AND i = (v_leapmonth + 1) AND v_leapflag = 0)
      THEN
-- 是闰月
         i := i - 1;
         k := i;                                       -- 保存是闰月的时i的值
         v_leapflag := 1;

-- 传回农历年闰月的天数
         IF BITAND (v_lunar, 65536) + 0 > 0
         THEN
            v_monthdays := 30;
         ELSE
            v_monthdays := 29;
         END IF;
      ELSE
-- 不是闰月
         j := 1;
         v_monthno := 65536;

-- 计算 i 月对应的2进制数如农历3月: 001000000000
         WHILE j <= i
         LOOP
            v_monthno := v_monthno / 2;
            j := j + 1;
         END LOOP;

-- 计算农历 v_Year 年 i 月的天数
         IF BITAND (v_lunar, v_monthno) + 0 > 0
         THEN
            v_monthdays := 30;
         ELSE
            v_monthdays := 29;
         END IF;
      END IF;

-- 解除闰月
      IF v_leapflag = 1 AND i = v_leapmonth + 1
      THEN
         v_leapflag := 0;
      END IF;

      v_offset := v_offset - v_monthdays;
      i := i + 1;
   END LOOP;

   IF v_offset <= 0
   THEN
-- i_SolarDay 在所属农历月(即i月)中的第 v_OffSet 天
      v_offset := v_offset + v_monthdays;
      i := i - 1;
   END IF;

-- 确定农历月结束
   v_month := i;
-- 确定农历日结束
   v_day := v_offset;
   nlcnt := MOD ((v_year - 4), 60);
   tg := 0;
   dz := 0;
   nlcount := 0;

LOOP
tg := tg + 1;

      IF tg > 10
      THEN
         tg := 1;
      END IF;

dz := dz + 1;

      IF dz > 12
      THEN
         dz := 1;
      END IF;

nlcount := nlcount + 1;

      IF nlcount > nlcnt
      THEN
         v_nlyear := SUBSTR (v_tiangan, tg, 1) || SUBSTR (v_dizhi, dz, 1);
         EXIT;
      END IF;
   END LOOP;

-- 格式化返回值
   IF i_flag = 1
   THEN
      o_outputdate :=
            v_nlyear
         || '年【'
         || SUBSTR (v_shengxiao, dz, 1)
         || '】'
         || p_enter
         || ' ';
   ELSE
      o_outputdate := '农历 ' || TO_CHAR (v_year) || '年 ';
   END IF;

   IF k = i
   THEN
      IF i_flag = 1
      THEN
         o_outputdate :=
            o_outputdate || '(润)' || SUBSTR (v_lunarmonth, v_day * 2 - 1, 2);
      ELSE
         o_outputdate :=
                 o_outputdate || LPAD (TO_CHAR (v_month), 2, '0')
                 || '(润)月 ';
      END IF;
   ELSE
      IF i_flag = 1
      THEN
         o_outputdate :=
              o_outputdate || SUBSTR (v_lunarmonth, v_month * 2 - 1, 2)
              || ' ';
      ELSE
         o_outputdate :=
                     o_outputdate || LPAD (TO_CHAR (v_month), 2, '0')
                     || '月 ';
      END IF;
   END IF;

   IF i_flag = 1
   THEN
      o_outputdate := o_outputdate || SUBSTR (v_lunardate, v_day * 2 - 1, 2);
   ELSE
      o_outputdate := o_outputdate || LPAD (TO_CHAR (v_day), 2, '0') || '日';
   END IF;

   RETURN o_outputdate;
EXCEPTION
   WHEN e_errdate
   THEN
      RETURN '日期错误! 有效范围(阳历): 1900/01/31 - 2050/01/22';
   WHEN OTHERS
   THEN
      e_errmsg := SUBSTR (SQLERRM, 1, 200);
      RETURN e_errmsg;
END;