Oracle PL/SQL，如何使用简单的替换变量和months_between计算从出生日期开始的年龄 [英] Oracle PL/SQL, How to calculate age from date birth using simple substitution variable and months_between

问题描述

我正在研究一个简单的PL/SQL块，该块询问用户其出生日期并计算其年龄.我已经使用months_between除以12转换，但是在日期转换和用户为替代变量输入的日期上遇到了麻烦.请忽略v_birthday_year，因为这是我稍后要集成的代码的另一部分.

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.

我不确定用户必须输入的格式(例如05-07-1980或1978年3月6日)是否可以接受，以及如何根据输入内容计算年龄.

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.

我想要的程序是编写一个DD-MON-YYYY格式的PL/SQL程序以接受用户的生日，并以1个小数位计算年龄.因此，我也需要将其计算出小数点后一位.

这里是我所拥有的，但是我不确定在替代变量输入和实际计算中在DOB中键入哪种格式，且小数点后一位.我只是想看看我要去哪里哪里以及如何纠正它.

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.

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;
/

推荐答案

除非您正在学习有关PL/SQL的知识，否则其他人和我提供的所有SQL示例可能都是更好的选择.这是我想出的:

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:

 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

另一个SQL示例:

SELECT empno, ename, hiredate
    , TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service  
 FROM scott.emp
/

这篇关于Oracle PL/SQL，如何使用简单的替换变量和months_between计算从出生日期开始的年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！