Recursion:

RECURSION is the condition where an entity has a relationship to itself. Recursion is also referred as a SELF - ASSOCIATION.

Example

CREATE TYPE EMPLOYEE AS OBJECT (

EMPNO NUMBER,

ENAME VARCHAR2(20),

JOB     VARCHAR2(20),

MGR   REF EMPLOYEE,

SAL    NUMBER(10,2),

);

With the primary key create an object table EMP of type EMPLOYEE constraint on the EMPNO attribute and to place a SCOPE on the REFs in the MGR column.

CREATE TABLE EMP OF EMPLOYEE

(

EMPNO PRIMARY KEY

);

ALTER TABLE EMP ADD (SCOPE FOR (MGR) IS EMP);

OR

CREATE TABLE EMP OF EMPLOYEE;

ALTER TABLE EMP ADD PRIMARY KEY (EMPNO);# define primary key#

ALTER TABLE EMP ADD (SCOPE FOR (MGR) IS EMP); #define scope#

INSERT INTO EMP VALUES ( 1000,'LAN','PRESIDENT', NULL, 15000);#input values#

INSERT INTO EMP SELECT

2000,'PARTHA','MANAGER', REF (E), 7000

FROM EMP E WHERE E.EMPNO=1000;

An Oracle gives implicit dereferencing of REFs.

Example

To show the employee details with the information of their manager:

SELECT         e.empno,e.ename,e.job,deref(e.mgr),e.sal# for display table#

FROM EMP e WHERE e.mgr IS NOT DANGLING;

To display the employee and manager name

SELECT e.ename,e.mgr.ename FROM EMP e WHERE e.mgr IS NOT DANGLING ;