使用oracle plsql触发器从生日计算年龄，并在表中插入年龄 [英] Calculating age from birthday with oracle plsql trigger and insert the age in table
问题描述
我有一个表
日期
（dob日期，
年龄号b $ b）;
我将插入出生日期，触发器将计算年龄并将年龄插入年龄字段。
CREATE或REPLACE PROCEDURE getage IS
ndob date;
nage number（10）;
BEGIN
从日期中选择dob到ndob;
select（sysdate-to_date（ndob））/ 365 into nage from dual;
更新日期set age = nage;
END;
/
显示错误;
此过程可以正常工作，但只有一行，我需要触发所有的行，
在插入或更新开始日期之后，CREATE或REPLACE TRIGGER agec
FOR EACH ROW
BEGIN
getage;
END;
/
请帮助...我真的需要这个...
不，你不是。我不确定你会注意;没有理由为什么你应该:-)但是：
不要在你的数据库中存储年龄。 。每个人每年的年龄变化，但是，对于某些人来说，它每天都在变化。这反过来意味着您需要一个批处理作业来每天运行和更新年龄。
您应该总是 / em>计算您需要时的年龄。这是一个相当简单的查询，并在较长的运行中为您节省了很多痛苦。
select floor（months_between（sysdate， dob>）/ 12）from dual
我设置了一些 SQL Fiddle 演示
现在，要实际回答您的问题
此过程可以正常工作，但只有一行,,,但所有行
i需要触发，但如果我从触发器调用它，则错误
发生...
提到错误，请在将来这样做非常有帮助，但我怀疑你得到
ORA-04091：表字符串。字符串是突变，触发器/函数可能不
看到它
这是因为你的过程正在查询更新。 Oracle不允许这样做以便维护数据的读一致视图。避免这种情况的方法是不查询表，您不需要这样做。将您的过程更改为在出生日期时返回正确结果的函数：
function get_age（pDOB date）
/ *返回给定日期和sysdate之间的
之间的全年数。
* /
begin
return floor（months_between（sysdate，pDOB）/ 12）;
end;
再次注意我正在使用 months_between（） 功能并非所有年份都有365天。 / p>
在您的触发器中，您可以直接将值分配给列。
CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
：new.age：= get_age（：new.dob）;
END;
：new。< column> 语法是对正在更新的< column> 的引用。在这种情况下，：new.age 是将要放在表中的实际值。
表示您的表格会自动更新，这是 DML触发器的点。
正如你所看到的，几乎没有什么意义的功能;您的触发器可以成为
在插入或更新开始日期之前，CREATE或REPLACE TRIGGER agec
FOR EACH ROW
BEGIN
：new.age：= floor（months_between（sysdate，：new，DOB）/ 12）;
END;
但是，如果你要在数据库的其他地方使用这个函数，分离。这是一个很好的做法是保留在一个函数中的多个地方使用的代码，所以它总是以相同的方式使用。它还确保每当任何人计算年龄时，他们会正确地做。
稍微一点，你确定要允许人们9,999岁吗？或者0.000000000001998 （证明）？ 数字精度基于 significant 的数量， em> digits;这（根据Oracle）是非零数字。你可以很容易被抓住这一点。数据库的要点是将可能的输入值限制为仅有效的值。我会认真考虑将你的年龄列声明为 number（3,0），以确保只包含可能的值。
i have a table
dates
(dob date,
age number(4)
);
I will insert a date of birth and a trigger will calculate the age and insert that age in the age field.
CREATE OR REPLACE PROCEDURE getage IS
ndob date;
nage number(10);
BEGIN
select dob into ndob from dates;
select (sysdate-to_date(ndob))/365 into nage from dual;
update dates set age=nage;
END;
/
SHOW ERRORS;
this procedure works fine but for only one row, and I need trigger for all the rows but if I call it from a trigger then the error occurs.
CREATE OR REPLACE TRIGGER agec after INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
getage;
END;
/
please help...i really need this...
No, you don't. I'm not sure you'll pay attention; and there's no reason why you should :-) but:
Do not store age in your database. You are absolutely guaranteed to be wrong occasionally. Age changes each year for each person, however, it changes every day for some people. This in turn means you need a batch job to run every day and update age. If this fails, or isn't extremely strict and gets run twice, you're in trouble.
You should always calculate the age when you need it. It's a fairly simple query and saves you a lot of pain in the longer run.
select floor(months_between(sysdate,<dob>)/12) from dual
I've set up a little SQL Fiddle to demonstrate
Now, to actually answer your question
this procedure works fine but for only one row,,,but for all the rows i need trigger but if i call it from a trigger then the error occurs...
You don't mention the error, please do this in future as it's very helpful, but I suspect you're getting
ORA-04091: table string.string is mutating, trigger/function may not see it
This is because your procedure is querying the table that is being updated. Oracle does not allow this in order to maintain a read-consistent view of the data. The way to avoid this is to not query the table, which you don't need to do. Change your procedure to a function that returns the correct result given a date of birth:
function get_age (pDOB date) return number is
/* Return the the number of full years between
the date given and sysdate.
*/
begin
return floor(months_between(sysdate,pDOB)/12);
end;
Notice once again that I'm using the months_between() function as not all years have 365 days.
In your trigger you then assign the value directly to the column.
CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
:new.age := get_age(:new.dob);
END;
The :new.<column> syntax is a reference to the <column> that is being updated. In this case :new.age is the actual value that is going to be put in the table.
This means that your table will automatically be updated, which is the point of a DML trigger.
As you can see there's little point to the function at all; your trigger can become
CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
:new.age := floor(months_between(sysdate,:new,DOB)/12);
END;
However, having said that, if you are going to use this function elsewhere in the database then keep it separate. It's good practice to keep code that is used in multiple places in a function like this so it is always used in the same way. It also ensures that whenever anyone calculates age they'll do it properly.
As a little aside are you sure you want to allow people to be 9,999 years old? Or 0.000000000001998 (proof)? Numeric precision is based on the number of significant digits; this (according to Oracle) is non-zero numbers only. You can easily be caught out by this. The point of a database is to restrict the possible input values to only those that are valid. I'd seriously consider declaring your age column as number(3,0) to ensure that only "possible" values are included.
这篇关于使用oracle plsql触发器从生日计算年龄，并在表中插入年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
