从2个REF'd列获取两个日期之间的差异
问题描述:
我是SQL新手,我试图在两个REF'd列值之间获取2个值之间的日期。 我现在有表设置如下图所示:从2个REF'd列获取两个日期之间的差异
CREATE OR REPLACE TYPE Person_Type AS OBJECT
(PersonId NUMBER,
DateBorn DATE)
CREATE OR REPLACE TYPE Movie_Type AS OBJECT
(MovieId NUMBER,
ReleaseDate DATE)
CREATE OR REPLACE TYPE Role_Type AS OBJECT
(PersonId REF Person_Type,
MovieId REF Movie_Type,
Name VARCHAR2(40),
MAP MEMBER FUNCTION Actor_Age RETURN NUMBER)
插入数据是这样的:
INSERT INTO Person_Table
VALUES (10000, '11-NOV-1974')
INSERT INTO Movie_Table
VALUES(1000000, '19-DEC-1997')
INSERT INTO Role_Table
VALUES((SELECT REF(a) FROM Person_Table a WHERE a.PersonId = 10000),
(SELECT REF(b) FROM Movie_Table b WHERE b.MovieId = 1000000),
'Some Person')
我试图获得方法Actor_Age返回当天的区别有人出生,电影上映的日期。我已经试过以下,但它不会编译..
CREATE OR REPLACE TYPE BODY Role_Type
AS
MAP MEMBER FUNCTION Actor_Age
RETURN NUMBER
IS
BEGIN
RETURN (MONTHS_BETWEEN((SELECT g.PersonId.DateBorn FROM Role_Table g),
(SELECT f.MovieId.Releasedate FROM Role_Table f)/12));
END;
END;
答
的Oracle 11g R2架构设置:
CREATE OR REPLACE TYPE Person_Type AS OBJECT
(PersonId NUMBER,
DateBorn DATE)
/
CREATE OR REPLACE TYPE Movie_Type AS OBJECT
(MovieId NUMBER,
ReleaseDate DATE)
/
CREATE OR REPLACE TYPE Role_Type AS OBJECT
(PersonId REF Person_Type,
MovieId REF Movie_Type,
Name VARCHAR2(40),
MAP MEMBER FUNCTION Actor_Age RETURN NUMBER)
/
CREATE OR REPLACE TYPE BODY Role_Type
AS
MAP MEMBER FUNCTION Actor_Age
RETURN NUMBER
IS
p_age NUMBER;
BEGIN
SELECT MONTHS_BETWEEN(
DEREF(MovieID).ReleaseDate,
DEREF(PersonID).DateBorn
)/12
INTO p_age
FROM DUAL;
RETURN p_age;
END;
END;
/
CREATE TABLE Person_Table OF Person_Type
/
CREATE TABLE Movie_Table OF Movie_Type
/
CREATE TABLE Role_Table OF Role_Type
/
INSERT INTO Person_Table VALUES (10000, DATE '1970-01-01')
/
INSERT INTO Movie_Table VALUES (1000000, DATE '2000-01-01')
/
INSERT INTO Role_Table VALUES(
(SELECT REF(a) FROM Person_Table a WHERE a.PersonId = 10000),
(SELECT REF(b) FROM Movie_Table b WHERE b.MovieId = 1000000),
'Some Person'
)
/
查询1:
SELECT Name,
r.Actor_Age()
FROM Role_table r
| NAME | R.ACTOR_AGE() |
|-------------|---------------|
| Some Person | 30 |
答
在PL SQL,你必须使用建设select into
CREATE OR REPLACE TYPE BODY Role_Type
AS
MAP MEMBER FUNCTION Actor_Age
RETURN NUMBER
IS
v_date_born date ;
v_realse_date date ;
BEGIN
SELECT deref(self.PersonId).DateBorn into v_date_born FROM dual;
SELECT deref(self.MovieId).Releasedate into v_realse_date FROM dual;
RETURN (MONTHS_BETWEEN(v_date_born,v_realse_date)/12);
END;
END;
+0
这不起作用 - 'Role_Table'可以有多行,并且不会将它们过滤到当前行(除了不需要从整个表中选择该对象是表格行,您可以使用该对象的属性),并且当PersonID是REF时不能使用PersonId.DateBorn,您需要使用DEREF(PersonId).DateBorn。 – MT0
+0
你说得对。我犯了错。 –
邮政DDL 3代表为好。 – XING
已更新。你的意思是表INSERT的权利? – lesovren
不需要。我需要表'Movie_Table','Person_Table'和'Role_Table'的表DDL(结构脚本) – XING