Oracle PL/SQL，如何使用简单的替换变量和months_between 从出生日期计算年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14759219/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL, How to calculate age from date birth using simple substitution variable and months_between
提问by MeachamRob
I am working on a simple PL/SQL block that asks the user their Date Of Birth and calculates their age. I've used the months_between divided by 12 conversion but I am having trouble with the date conversion and date input from the user for the substitution variable. Please ignore the v_birthday_year as that is another part of the code I have to integrate in later.
我正在研究一个简单的 PL/SQL 块，它询问用户他们的出生日期并计算他们的年龄。我已经使用了months_between 除以12 的转换，但是我在日期转换和用户输入的替换变量的日期方面遇到了问题。请忽略 v_birthday_year 因为这是我稍后必须集成的代码的另一部分。
I'm not sure the format that the user has to type in, ie 05-07-1980, or 06 Mar 1978 that would be acceptable, and how to calculate their age from what they put in.
我不确定用户必须输入的格式，即 05-07-1980 或 1978 年 3 月 6 日是可以接受的，以及如何根据他们输入的内容计算他们的年龄。
My desired program is to write a PL/SQL program to accept the user's birthdate in this format DD-MON-YYYY, and calculates the Age in with 1 Decimal place. So I need it calculated out to one decimal place too.
我想要的程序是编写一个 PL/SQL 程序来接受用户的生日，格式为 DD-MON-YYYY，并用 1 个小数位计算年龄。所以我也需要把它计算到小数点后一位。
Here is what I have, but I'm not sure what format to type in the DOB at the substitution variable input and the actual calculation with one decimal place. I'm just trying to see where I'm going wrong and how to correct it.
这是我所拥有的，但我不确定在替换变量输入处输入 DOB 的格式以及带一位小数的实际计算。我只是想看看我哪里出错了以及如何纠正它。
SET SERVEROUTPUT ON
DECLARE
--v_birthday_year NUMBER(4) := &v_birthday_year;
v_dob DATE := &v_dob
v_your_age NUMBER(3, 1);
BEGIN
v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;
DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/
回答by Mohsen Heydari
You may use any of these (pure sql)
您可以使用其中任何一个（纯 sql）
SELECT months_between(sysdate, user_birth_date) /12 FROM dual;
or
或者
SELECT floor(months_between(sysdate, user_birth_date) /12) FROM dual;
or
或者
SELECT EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM user_birth_date) FROM dual
or
或者
SELECT (sysdate - user_birth_date) / 365.242199 FROM dual
Hope this will help
希望这会有所帮助
回答by Art
All SQL examples from others and me are probably better thing to use unless you are learning something about PL/SQL. Here's what I came up with:
除非您正在学习有关 PL/SQL 的知识，否则来自其他人和我的所有 SQL 示例都可能更适合使用。这是我想出的：
DECLARE
v_dob DATE:= to_date('&v_dob', 'MM/DD/YYYY'); -- copy this line and you'll be fine
v_your_age NUMBER(3, 1);
BEGIN
v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;
DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/
I passed 01/01/2010. Got this in output:
Your age is 3.1
Another SQL example:
另一个 SQL 示例：
SELECT empno, ename, hiredate
, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service
FROM scott.emp
/
回答by Raju
Create or replace function getAge(birthday date) return number is
v_your_age NUMBER(3, 1);``
BEGIN
v_your_age := floor(MONTHS_BETWEEN(SYSDATE, birthday))/12;
return v_your_age;
END;
/
then you call the function
然后你调用函数
BEGIN
DBMS_OUTPUT.PUT_LINE ('Your age is ' || getAge(to_date('10/10/2015','DD/MM/YYYY')));
end;
/
回答by rajesh
select dob,sysdate,trunc(months_between(sysdate,dob)/12) as year,
trunc(mod(months_between(sysdate,dob),12)) as month,
trunc(sysdate - add_months(dob,((trunc(months_between(sysdate,dob)/12)*12) + trunc(mod(months_between(sysdate,dob),12))))) as day
from emp;
