开发者

Oracle ORDB Hierarchical Data

I am trying to create some hierarchical data using Oracles object relation features.

I have defined a "Post" as follows:

create type Post as object (title varchar(20), body varchar(2000), 
  author varchar(200), parent REF Post,
  MEMBER FUNCTION numReplies RETURN NUMBER,
  PRAGMA RESTRICT_REFERENCES(numReplies, WNDS)
);
/

create table Posts of Post;

Now I want to write the function numReplies to find out how many posts have the "self" post as the parent:

create or replace type body Post AS

MEMBER FUNCTION numReplies RETURN number IS
i INT;
BEGIN 
  SELECT COUNT(*) INTO i FROM Posts p WHERE p.parent = SELF;
  RETURN i;
END;

END;
/

But I get a compilation error:

Warning: Type Body created with compilation errors.

SQL> show errors;
Errors for TYPE BODY POST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
6/56     PL/SQL: ORA-00932: inconsistent datatypes: expected TSTER123.POST
         got REF TSTER123.POST

I have tried doing REF(SELF) in the where clause and I get the same error message. I have also tried REF(p.parent) (which doesn't make sense anyways) and I get the error:

LINE/COL ERROR
-------- --------------------------------------开发者_如何转开发---------------------------
6/3      PL/SQL: SQL Statement ignored
6/49     PL/SQL: ORA-00904: "P"."PARENT": invalid identifier

I want to use the OR features (it's a class project) so I don't want to resort to adding an ID column to post and using that. How can I achieve this?

NOTE: the following query works, I just can't get it to work in a function using SELF.

SELECT title, (select count(*) from Posts p2 where p2.parent = REF(p))
FROM Posts p;

EDIT:

OK, I've gotten it to compile, but I'm not getting the data I expect.

Here is some dummy data I've used:

insert into Posts values ('foo', 'bar', 'tyler', null);
insert into Posts values ('hello world', 'bar', 'jatin', (select REF(p) FROM Posts p where p.title = 'foo'));
insert into Posts values ('bark', 'asd', 'tom', (select REF(p) FROM Posts p where p.title = 'hello world'));
insert into Posts values ('friendly', 'hgfags', 'tyler', (select REF(p) FROM Posts p where p.title = 'foo'));

And here is what compiles:

create or replace type body Post AS
MEMBER FUNCTION numReplies RETURN number IS
i INT;
BEGIN 
  SELECT COUNT(*) INTO i FROM Posts p WHERE DEREF(p.parent) = SELF;
  RETURN i;
END;
END;
/

But, I'm not getting the correct results:

SQL> SELECT title, (select count(*) from Posts p2 where p2.parent = REF(p)) From Posts p;

TITLE                (SELECTCOUNT(*)FROMPOSTSP2WHEREP2.PARENT=REF(P))
-------------------- ------------------------------------------------
foo                                                                 2
hello world                                                         1
bark                                                                0
friendly                                                            0

SQL> select title, p.numReplies() from Posts p;

TITLE                P.NUMREPLIES()
-------------------- --------------
foo                               0
hello world                       1
bark                              0
friendly                          0

If I use this:

create or replace type body Post AS
MEMBER FUNCTION numReplies RETURN number IS
i INT;
BEGIN 
  SELECT COUNT(*) INTO i FROM Posts p WHERE DEREF(p.parent).title = SELF.title;
  RETURN i;
END;
END;
/

I get the expected results, but I'm requiring each post to have a unique title.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜