Oracle Iterative Queries

Oracle iterative query with background menu as an example

This is the SQL to be prepared

1 Create TableTbl_menu (2Id Number Primary Key,3parent_id Number default 0,4Namevarchar2( -) not NULL,5 direntions Clob,6 Info1 Clob,7 Info2 Clob,8 Info3 Clob9 );Ten  One Createsequence Seq_tbl_menu; A  - Insert  intoTbl_menu (ID, name, direntions)Values(Seq_tbl_menu.nextval,'Virtual root directory','The virtual root of all menus all other menus are directly or indirectly a submenu of it'); - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,1,'Rights Management'); the  - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,2,'Resource Management'); - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,2,'Role Management'); - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,2,'User Management'); +  - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,3,'Menu Page Management'); + Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,3,'Non-menu page management'); A  at Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,4,'Role Management'); - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,4,'Role Permissions'); -  - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,5,'User Management'); - Insert  intoTbl_menu (ID, parent_id, name)Values(Seq_tbl_menu.nextval,5,'User Rights');

View Code

This is the SQL for the query

1 Select2 ID,3 parent_id,4    Case 5      whenparent_id= 0  Then '------'||name6      whenparent_id= 1  Then '------------'||name7      whenparent_id!= 1 orparent_id!= 0  Then '------------------------'||name8   ElseNameEnd9  fromTbl_menuTenStart withId= 1 OneConnect byPrior ID=parent_id A Order  byId

View Code

That's the effect.

ID parent_id casewhenparent_id=0then '------
Virtual root directory------
------------Rights Management
------------------------Resource Management
------------------------Role Management
------------------------User Management
------------------------Menu Page Management
------------------------Non-menu page management
------------------------Role Management
94------------------------Role Permissions
------------------------User Management
------------------------User Rights

Oracle Iterative Queries