OracleµÄrownumÔÀíºÍʹÓÃ
ÔÎijö×Ô£ºhttp://tenn.javaeye.com/blog/99339
ÔÚOracle
ÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum
¾Í¸ã¶¨ÁË¡£
select * from emp where rownum
<= 5
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownum
ÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
select * from emp where rownum
> 5
ÔòÊÇʧ°ÜµÄ¡£ÒªÖªµÀΪʲô»áʧ°Ü£¬ÔòÐèÒªÁ˽ârownum
±³ºóµÄ»úÖÆ£º
1 Oracle
executes your query.
2 Oracle
fetches the first row and calls it row number 1.
3 Have we gotten past row number meets the criteria? If no, then Oracle
discards the row, If yes, thenOracle
return the row.
4 Oracle
fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5 Go to step 3.
Á˽âÁËÔÀí£¬¾ÍÖªµÀrownum
>²»»á³É¹¦£¬ÒòΪÔÚµÚÈý²½µÄʱºò²éѯ³öµÄÐÐÒѾ±»¶ªÆú£¬µÚËIJ½²é³öÀ´µÄrownum
ÈÔÈ»ÊÇ1,ÕâÑùÓÀÔ¶Ò²²»»á³É¹¦¡£
ͬÑùµÀÀí£¬rownum
Èç¹ûµ¥¶ÀÓÃ=,Ò²Ö»ÓÐÔÚrownum
=1ʱ²ÅÓÐÓá£
¶ÔÓÚrownum
À´ËµËüÊÇoracle
ϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖÆ²éѯ·µ»ØµÄ×ÜÐÐÊý£¬¶øÇÒrownum
²»ÄÜÒÔÈκαíµÄÃû³Æ×÷Ϊǰ׺¡£
¾ÙÀý˵Ã÷£º
ÀýÈç±í£ºstudent(ѧÉú)±í£¬±í½á¹¹Îª£º
ID¡¡¡¡¡¡ char(6)¡¡¡¡¡¡¡¡¡¡ --ѧºÅ
name¡¡¡¡¡¡¡¡VARCHAR2(10)¡¡¡¡¡¡--ÐÕÃû create table student (ID char(6), name VARCHAR2(100));
insert into sale values('200001',‘ÕÅÒ»’);
insert into sale values('200002',‘Íõ¶þ’);
insert into sale values('200003',‘ÀîÈý’);
insert into sale values('200004',‘ÕÔËÄ’);
commit;
(1) rownum
¶ÔÓÚµÈÓÚijֵµÄ²éѯÌõ¼þ
Èç¹ûÏ£ÍûÕÒµ½Ñ§Éú±íÖеÚÒ»ÌõѧÉúµÄÐÅÏ¢£¬¿ÉÒÔʹÓÃrownum
=1×÷ΪÌõ¼þ¡£µ«ÊÇÏëÕÒµ½Ñ§Éú±íÖеڶþÌõѧÉúµÄÐÅÏ¢£¬Ê¹ÓÃrownum
=2½á¹û²é²»µ½Êý¾Ý¡£ÒòΪrownum
¶¼ÊÇ´Ó1¿ªÊ¼£¬µ«ÊÇ1ÒÔÉϵÄ×ÔÈ»ÊýÔÚrownum
×öµÈÓÚÅжÏÊÇʱÈÏΪ¶¼ÊÇfalseÌõ¼þ£¬ËùÒÔÎÞ·¨²éµ½rownum
= n£¨n>1µÄ×ÔÈ»Êý£©¡£
SQL> select rownum
,id,name from student where rownum
=1;£¨¿ÉÒÔÓÃÔÚÏ
Ïà¹ØÎĵµ£º
trunc()º¯ÊýÓÐÁ½ÖÖÓ÷¨ 1£ººóÃæ¸úÈÕÆÚ 2£º ºóÃæ¸úÊý×Ö
A£º SELECT a.times,to_date(a.times,'yyyymmdd'),trunc(to_date(a.times,'yyyymmdd'),'month') from dmf_loan_limit a
ÏÔʾµÄ½á¹ûΪ£º
1 20080131 2008-1-31 2008-1-1
2 20080131 &nb ......
decode()º¯數ʹÓü¼ÇÉ
·Èí¼þ»·¾³£º
1¡¢Windows NT4.0+ORACLE 8.0.4
2¡¢ORACLE°²×°Â·¾¶Îª£ºC:\ORANT
·º¬Òå½âÊÍ£º
decode(Ìõ¼þ,Öµ1,·ÒëÖµ1,Öµ2,·ÒëÖµ2,...Öµn,·ÒëÖµn,ȱʡֵ)
¸Ãº¯ÊýµÄº¬ÒåÈçÏ£º
IF Ìõ¼þ=Öµ1 THEN
¡¡¡¡¡¡¡¡RETURN(·ÒëÖµ1)
ELSIF Ìõ¼þ=Öµ2 THEN
¡¡¡¡¡¡¡¡RETURN(·ÒëÖµ2)
......
2010Äê2ÔÂ3ÈÕ£¬±¾ÈËÓÐÒ»個興奮µÄС發現£¬ÊµÑéÖ¤Ã÷£¬Ö±½ÓÓÃTOAD½«oracleÊý¾Ý¿âÖеÄÊý¾ÝÖ±½Óµ¼Èë
ACCESSÖÐÊDz»¿ÉÄܵġ£ÄÇ為ÁË×öµ½´ÓTOADÖн«Êý¾Ýµ¼ÈëACCESSÔËÓüä½ÓÇþµÀ¡£¿ÉÒÔÓÃÎı¾Îļþ»òxlsµÈÎļþ¡£Æäʵ×îÁîÎҸе½¾ªÏ²µÄÊÇ£¬ÎÒÊÇͨ¹ýsql 2000×÷為Ò»¸ö¼ä½ÓµÄÖн飬ÒòĠ ......
Redhat
as4 + oracle 10g(10.2.0)
+ freeradius1.1.2+ tomcat5.5
*****************************
±¾ÎÄĿ¼
********************************
Ò»¡¢°²×°
redhat as4
¶þ¡¢°²×°
oracle 10g
£¨
10201_database_linux32.zip
£©
£¨Ò»£©¼ì²é
oracle 10g
ËùÐèµÄ°ü×é¼þ£º
£¨¶þ£©ÉèÖÃϵͳ²ÎÊ ......
import Java.io.*;
¡¡¡¡import java.util.*;
¡¡¡¡import java.sql.*;
¡¡¡¡public class LobPros
¡¡¡¡{
¡¡¡¡/**
¡¡¡¡* OracleÇý¶¯³ÌÐò
¡¡¡¡*/
¡¡¡¡private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
¡¡¡¡/**
¡¡¡¡* ORACLEÁ¬½ÓÓÃURL
¡¡¡¡*/
¡¡¡¡p ......