使用oracle plsql触发器从生日计算年龄并将年龄插入表中 [英] Calculating age from birthday with oracle plsql trigger and insert the age in 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

我已经设置了一些SQL Fiddle来演示

I've set up a little SQL Fiddle to demonstrate

现在，回答你的问题

此过程工作正常，但仅适用于一行，,, 但适用于所有行我需要触发器，但如果我从触发器调用它，则会出现错误发生...

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:表 string.string 正在发生变化，触发器/函数可能不会看看

ORA-04091: table string.string is mutating, trigger/function may not see it

这是因为您的过程正在查询正在更新的表.Oracle 不允许这样做是为了维护数据的读取一致性视图.避免这种情况的方法是不查询表，您不需要这样做.将您的过程更改为在给定出生日期的情况下返回正确结果的函数:

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;

再次注意我正在使用 months_between() 功能，因为并非所有年份都有 365 天.

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;

:new. 语法是对正在更新的 的引用.在这种情况下，:new.age 是要放入表中的实际值.

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.

这意味着你的表会自动更新，这是重点DML 触发器.

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.

顺便说一句，您确定要让人们活到 9,999 岁吗?或者 0.000000000001998 (证明)?数值精度基于显着数em> 数字；这(根据 Oracle 的说法)只是 非零 数字.你很容易被这个发现.数据库的重点是将可能的输入值限制为仅那些有效的值.我会认真考虑将您的年龄列声明为 number(3,0) 以确保只包含可能"的值.

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屋！