oracle SQL 年齢判定

Question

PL/SQL文を使用して、年齢を判定するファンクションを作成しています。
empテーブルの「生年月日」カラムをもとに、employeeの年齢を判定することを目指しています。下記のファンクションを作成することはできましたが、実行時にエラーが起こります。

CREATE OR REPLACE FUNCTION AGE
(v_date in date
)
RETURN number
is
age number(20) ;
begin
SELECT 
TRUNC(( TO_CHAR(SYSDATE, 'YYYYMMDD') - TO_CHAR(TO_DATE(v_date, 'YYYY/MM/DD'), 'YYYYMMDD'))/10000) 
into age
from emp;
return age;
end;
/

select AGE(生年月日)
from emp;

: ORA-01830: 日付書式の変換で不要なデータが含まれています
ORA-06512: AGE, 行8

=======================
以下、SQLの実行モードの変更により解決しました。

下記のコードを実行しようとすると、下記のエラーメッセージが表示されます。該当するエラーメッセージを調べても原因がわかりませんでしたので、質問いたします。

SQL   : ORA-24344: success with compilation error

CREATE OR REPLACE FUNCTION AGE RETURN number
is
age number(3) as　年齢;
begin
SELECT 
TRUNC((TO_CHAR(TO_DATE(sysdate, 'YYYY/MM/DD'), 'YYYYMMDD') - TO_CHAR(TO_DATE(生年月日, 'YYYY/MM/DD'), 'YYYYMMDD'))/10000) 
into 年齢
from emp;
return age;
end;

Comments

[sazi]

> 該当するエラーメッセージを調べても原因がわかりませんでした
一体、どんな調べ方したんですか？

[sinzuku123321]

こちらのエラーは解決しました。

Answer 1

ORA-01830: 日付書式の変換で不要なデータが含まれています

メッセージの通りです。
日付を変換しようとして変換できないデータが存在している事を示唆しています。
日付の変換している所は以下だけですので、ここに問題があります。

TRUNC(( TO_CHAR(SYSDATE, 'YYYYMMDD') - TO_CHAR(TO_DATE(v_date, 'YYYY/MM/DD'), 'YYYYMMDD'))/10000) 

先ず式として成り立っていないところがあります。
TO_CHAR()してるんですから、文字列から文字列を引くという演算は普通成り立ちません。
但し、日付書式の文字列なので暗黙変換しようとしているのでしょうが、'YYYYMMDD'は単なる８桁の文字で日付書式ではありませんから、エラーになっている訳です。
学習しているのであれば、ご自身で答えを見つけた方が良いですね。

Comments

[sinzuku123321]

上記、日付算出式に間違いがあることがわかりました。ありがとうございます。

Answer 2

ORA-24344 の意味は、ストアドプロシジャ（ファンクション）は作成されたけど、コンパイルエラーがあるよってことです。
PL/SQL の文法を勉強してください。

年齢計算については、以下を参照ください。
「年齢を計算する（暦年齢）」
https://www.shift-the-oracle.com/sql/functions/years_of_age.html