Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Nice present, only discovered for Christmas

Re: Nice present, only discovered for Christmas

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 25 Dec 2005 09:55:50 +0100
Message-ID: <43ae5e97$0$14562$626a14ce@news.free.fr>

"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



Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production

5 rows selected.

Regards
Michel Cadot Received on Sun Dec 25 2005 - 02:55:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US