Deep sql oracle Recursive Query

Get all the table names of the database and all the column names of the table
select name from sysobjects where xtype='u'
select name from syscolumns where id= (select max (id) from sysobjects where xtype= 'u' and name= 'Table Name')

The recursive query data
Recursive query in Sql statement SqlServer 2005 and Oracle
Before using Oracle, think its recursive query is very useful, on the study of 1 under SqlServer, found that it also supports recursive query in Sql
For example:
The Sql version of SqlServer 2005 is as follows:
For example, a table has id and pId fields, id is the primary key, and pid represents its superior node, table structure and data:
CREATE TABLE [aaa](
[id] [int] NULL,
[pid] [int] NULL,
[name] [nchar](10)
)
GO
INSERT INTO aaa VALUES(1,0,'a')
INSERT INTO aaa VALUES(2,0,'b')
INSERT INTO aaa VALUES(3,1,'c')
INSERT INTO aaa VALUES(4,1,'d')
INSERT INTO aaa VALUES(5,2,'e')
INSERT INTO aaa VALUES(6,3,'f')
INSERT INTO aaa VALUES(7,3,'g')
INSERT INTO aaa VALUES(8,4,'h')
GO
--The following Sql is to query all the child nodes of Node 1
with my1 as(select * from aaa where id = 1
union all select aaa.* from my1, aaa where my1.id = aaa.pid
)
select * from my1-The result contains 1. If you don't want to include it, you can add where id at the end < > 1
--The following Sql is all the parent nodes of 8 nodes
with my1 as(select * from aaa where id = 8
union all select aaa.* from my1, aaa where my1.pid = aaa.id
)
select * from my1;
--The following is a statement that recursively deletes Node 1 and all child nodes:
with my1 as(select * from aaa where id = 1
union all select aaa.* from my1, aaa where my1.id = aaa.pid
)
delete from aaa where exists (select id from my1 where my1.id = aaa.id)
The Oracle version of Sql is as follows:
For example, a table has id and pId fields, id is the primary key, and pid represents its superior node. Please refer to SqlServer2005 for table structure and data, and Sql is as follows:
--The following Sql is to find out all the children of Node 1
SELECT * FROM aaa
START WITH id = 1
CONNECT BY pid = PRIOR id
--The following Sql is all the parent nodes of 8 nodes
SELECT * FROM aaa
START WITH id = 8
CONNECT BY PRIOR pid = id
Today, I helped others to do an interesting sql, which is also realized by recursion, as follows:
Suppose you have a sales table like this:
CREATE TABLE [tb](
[qj] [int] NULL,--month, this test assumes that the data starts in January, and the data are consecutive months with no intervals
[je] [int] NULL--Actual sales amount for the month
[rwe] [int] NULL--Sales Tasks for the Month
[fld] [float] NULL-The rebate point when the amount of this month is greater than the task amount, and the rebate amount is je*fld
) ON [PRIMARY]
It is now required to calculate the rebate amount for each month. The rules are as follows:
In January, the sales amount is greater than the task amount. Rebate amount = amount * rebate point
Rebate amount of sales amount greater than task amount in February = (amount-rebate amount in January) * rebate point
Rebate amount of sales amount greater than task amount in March = (Amount-Rebate amount in January and February) * Rebate point
In the following months, by analogy, when the sales amount is less than the task amount, the rebate is 0
The specific Sql is as follows:

WITH my1 AS (
                SELECT *,
                       CASE 
                            WHEN je > rwe THEN (je * fld)
                            ELSE 0
                       END fle,
                       CAST(0 AS FLOAT) tmp
                FROM   tb
                WHERE  qj = 1
                UNION ALL
                SELECT tb.*,
                       CASE 
                            WHEN tb.je > tb.rwe THEN (tb.je - my1.fle -my1.tmp) 
                                 * tb.fld
                            ELSE 0
                       END fle,
                       my1.fle + my1.tmp tmp --  Used to accumulate rebates from previous months 
                FROM   my1,
                       tb
                WHERE  tb.qj = my1.qj + 1
            )
SELECT *
FROM   my1

SQLserver2008 uses expression recursive queries
--Recursion from parent to subordinate
with cte(id,parentid,text)
as
(--Parent
select id,parentid,text from treeview where parentid = 450
union all
--Subordinates in the recursive result set
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.parentid = c.id
)
select id,parentid,text from cte
---------------------
--Recursive parent by child
with cte(id,parentid,text)
as
(--Subordinate parent
select id,parentid,text from treeview where id = 450
union all
--Parent item in recursive result set
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.id = c.parentid
)
select id,parentid,text from cte