Recursive query methods for oracle and mysql: oraclemysql Recursion

Recursive query methods for oracle and mysql: oraclemysql Recursion

Oracle:

1. query the institutions under the jurisdiction of an institution: (start)

Select * from t00_organ t start with t. organkey = # uporgankey # connect by prior t. organkey = t. uporgankey;

2. Example (with query)

With emps (employee_id, name, job_id, salary, lvl) (

Select employee_id, first_name | ',' | last_name name, job_id, salary, 1 as lvl

From employees

Where manager_id is null

Union all

Select emp. employee_id, emp. first_name | ',' | emp. last_name, emp. job_id, emp. salary, root. lvl + 1

From employees emp, emps root

Where emp. manager_id = root. employee_id

)

Select * from emps;

MYSQL:

Create function 'fcgettreelist' (rid INT)

RETURNS varchar (1000)

BEGIN

DECLARE sTemp VARCHAR (1000 );

DECLARE sTempChd VARCHAR (1000 );

SET sTemp = '$ ';

SET sTempChd = cast (rid as CHAR );

WHILE sTempChd is not null DO

SET sTemp = concat (sTemp, ',', sTempChd );

SELECT group_concat (id) INTO sTempChd FROM treeNodes where FIND_IN_SET (pid, sTempChd)> 0;

End while;

RETURN sTemp;

END

-- Call

SELECT * from test where FIND_IN_SET (id, fcGetTreeList (1 ));