Oracle with的使用

法6400

　　create table yl(id integer,yorder integer);
　　插入记录：
　　SQL> insert into yl values(1,null);
　　1 row inserted
　　SQL> insert into yl values(3,null);
　　1 row inserted
　　SQL> insert into yl values(2,null);
　　1 row inserted
　　SQL> commit;
　　SQL> select * from yl;
　　ID YORDER
--------------------------------------- ---------------------------------------
1
3
2
　　SQL> insert into yl values(100,null);
　　1 row inserted
　　SQL> commit;
　　SQL> select y.*,rownum from (select * from yl order by id) y;
　　ID YORDER ROWNUM
--------------------------------------- --------------------------------------- ----------
1 1
2 2
3 3
100 4
　　查询id，不是顺序号的
　　select y.*,rownum rn from (select * from yl order by id) y
with a as (select y.*,rownum rn from (select * from yl order by id) y)
select * from a,a b where a.id!=b.rn and a.id=b.id;
　　ID YORDER RN ID YORDER RN
--------------------------------------- --------------------------------------- ---------- --------------------------------------- --------------------------------------- ----------
100 4 100
　　另外的实例
　　实现自然数N的全排列
　　n=3;
　　WITH A AS (SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL <= 3)
SELECT A.ID || B.ID || C.ID
FROM A, A B, A C
WHERE A.ID != B.ID
AND A.ID != C.ID
AND B.ID != C.ID
;
　　n=4;
　　WITH A AS (SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL <= 4)
SELECT A.ID || B.ID || C.ID || D.ID
FROM A, A B, A C, A D
WHERE A.ID != B.ID
AND A.ID != C.ID
AND A.ID != D.ID
AND B.ID != C.ID
AND B.ID != D.ID
AND C.ID != D.ID;
　　
　　CREATE OR REPLACE FUNCTION F_TEST(P_IN NUMBER) RETURN SYS_REFCURSOR AS
V_STR VARCHAR2(32767);
V_RETURN SYS_REFCURSOR;
BEGIN
V_STR := 'WITH A AS (SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL <= ' || P_IN ||
') SELECT ';
FOR I IN 1 .. P_IN LOOP
V_STR := V_STR || 'A' || I || '.ID || ';
END LOOP;
V_STR := RTRIM(V_STR, '| ') || ' FROM ';
FOR I IN 1 .. P_IN LOOP
V_STR := V_STR || 'A A' || I || ', ';
END LOOP;
V_STR := RTRIM(V_STR, ', ') || ' WHERE ';
FOR I IN 1 .. P_IN LOOP
FOR J IN I + 1 .. P_IN LOOP
V_STR := V_STR || 'A' || I || '.ID != A' || J || '.ID AND ';
END LOOP;
END LOOP;
OPEN V_RETURN FOR SUBSTR(V_STR, 1, LENGTH(V_STR) - 4);
　　RETURN V_RETURN;
END;