Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Nice present, only discovered for Christmas
"Mladen Gogala" <gogala_at_sbcglobal.net> a écrit dans le message de news: pan.2005.12.25.08.48.24.275868_at_sbcglobal.net...
| On my Oracle 10.2, I got the following:
|
| 1 declare
| 2 cursor csr is select ename,deptno,
| 3 rank() over (partition by deptno order by sal)
| 4 as sal_seq
| 5 from emp
| 6 order by deptno;
| 7 begin
| 8 for c in csr
| 9 loop
| 10 dbms_output.put_line(c.ename||' '||c.deptno||' '||c.sal_seq);
| 11 end loop;
| 12* end;
| SQL> /
| MILLER 10 1
| CLARK 10 2
| KING 10 3
| SMITH 20 1
| ADAMS 20 2
| JONES 20 3
| SCOTT 20 4
| FORD 20 4
| JAMES 30 1
| MARTIN 30 2
| WARD 30 2
| TURNER 30 4
| ALLEN 30 5
| BLAKE 30 6
|
| PL/SQL procedure successfully completed.
|
| SQL>
|
| I was fully expecting a syntax error, which would happen in
| version 8i and 9i. In 10.2, one can use PL/SQL and analytic
| functions, without the need to resort to dynamic SQL and things
| like open csr for '<sql that contains an analytic function>';
| Nice! This is really a merry Christmas.
|
| --
| http://www.mgogala.com
|
Every possible at Chritmas:
=?/? SCOTT>declare
2 cursor csr is select ename,deptno,
3 rank() over (partition by deptno order by sal)
4 as sal_seq
5 from emp
6 order by deptno;
7 begin
8 for c in csr
9 loop
10 dbms_output.put_line(c.ename||' '||c.deptno||' '||c.sal_seq);
11 end loop;
12 end;
13 /
MILLER 10 1
CLARK 10 2
KING 10 3
SMITH 20 1
ADAMS 20 2
JONES 20 3
SCOTT 20 4
FORD 20 4
JAMES 30 1
WARD 30 2
MARTIN 30 2
TURNER 30 4
ALLEN 30 5
BLAKE 30 6
PL/SQL procedure successfully completed.
=?/? SCOTT>select * from v$version;
BANNER
5 rows selected.
Regards
Michel Cadot
Received on Sun Dec 25 2005 - 02:55:50 CST