生年月日から年齢を取得 for Oracle

GET_AGE

CREATE OR REPLACE FUNCTION GET_AGE
    (
    IN_BIRTHDAY         IN DATE                  -- 生年月日
  , IN_DESIGNATED_DATE  IN DATE DEFAULT SYSDATE  -- 指定日
    )
    RETURN NUMBER -- 年齢(年のみ)
--
-- 生年月日から本日までの満年齢の取得。
--
IS
BEGIN
    RETURN(TRUNC(MONTHS_BETWEEN(IN_DESIGNATED_DATE, IN_BIRTHDAY) / 12)) ;
END GET_AGE ;
/
SHOW ERROR FUNCTION GET_AGE
--  PUBLIC SYNONYM に
-- ↓実行権をPUBLICにし、インスタンスの全スキーマから実行可能に
-- https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_9014.htm#i2155015  SQL language reference
GRANT EXECUTE ON GET_AGE TO PUBLIC ;
--
-- ↓PUBLIC SYNONYM に
-- http://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_7001.htm#CJAJCDDF  SQL language reference
CREATE OR REPLACE PUBLIC SYNONYM GET_AGE FOR GET_AGE ;

-- 使用例
SELECT GET_AGE(TO_DATE('1980/02/28', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/28', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/03/01', 'YYYY/MM/DD'), TO_DATE('2020/02/29', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2020/03/01', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2021/02/28', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;
SELECT GET_AGE(TO_DATE('1980/02/29', 'YYYY/MM/DD'), TO_DATE('2021/03/01', 'YYYY/MM/DD')) AS GET_AGE FROM DUAL ;