Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

±Ê¼Ç£ºOracle³£Ó÷ÖÎöº¯Êý

Oracle³£Ó÷ÖÎöº¯Êý
ROW_NUMBER
·µ»ØÓÐÐò×éÖÐÒ»ÐÐµÄÆ«ÒÆÁ¿£¬´Ó¶ø¿ÉÓÃÓÚ°´Ìض¨±ê×¼ÅÅÐòµÄÐкÅ
row_number() over(partition by ... order by ...)
RANK
¸ù¾ÝORDER BY×Ó¾äÖбí´ïʽµÄÖµ£¬´Ó²éѯ·µ»ØµÄÿһÐУ¬¼ÆËãËüÃÇÓëÆäËüÐеÄÏà¶ÔλÖá£×éÄÚµÄÊý¾Ý°´ORDER BY×Ó¾äÅÅÐò£¬È»ºó¸øÃ¿Ò»Ðи³Ò»¸öºÅ£¬´Ó¶øÐγÉÒ»¸öÐòÁУ¬¸ÃÐòÁдÓ1¿ªÊ¼£¬ÍùºóÀÛ¼Ó¡£Ã¿´ÎORDER BY±í´ïʽµÄÖµ·¢Éú±ä»¯Ê±£¬¸ÃÐòÁÐÒ²ËæÖ®Ôö¼Ó¡£ÓÐͬÑùÖµµÄÐеõ½Í¬ÑùµÄÊý×ÖÐòºÅ£¨ÈÏΪnullʱÏàµÈµÄ£©¡£È»¶ø£¬Èç¹ûÁ½ÐеÄÈ·µÃµ½Í¬ÑùµÄÅÅÐò£¬ÔòÐòÊý½«ËæºóÌøÔ¾¡£ÈôÁ½ÐÐÐòÊýΪ1£¬ÔòûÓÐÐòÊý2£¬ÐòÁн«¸ø×éÖеÄÏÂÒ»ÐзÖÅäÖµ3£¬DENSE_RANKÔòûÓÐÈκÎÌøÔ¾
rank() over(partition by ... order by ...)
DENSE_RANK
¸ù¾ÝORDER BY×Ó¾äÖбí´ïʽµÄÖµ£¬´Ó²éѯ·µ»ØµÄÿһÐУ¬¼ÆËãËüÃÇÓëÆäËüÐеÄÏà¶ÔλÖá£×éÄÚµÄÊý¾Ý°´ORDER BY×Ó¾äÅÅÐò£¬È»ºó¸øÃ¿Ò»Ðи³Ò»¸öºÅ£¬´Ó¶øÐγÉÒ»¸öÐòÁУ¬¸ÃÐòÁдÓ1¿ªÊ¼£¬ÍùºóÀÛ¼Ó¡£Ã¿´ÎORDER BY±í´ïʽµÄÖµ·¢Éú±ä»¯Ê±£¬¸ÃÐòÁÐÒ²ËæÖ®Ôö¼Ó¡£ÓÐͬÑùÖµµÄÐеõ½Í¬ÑùµÄÊý×ÖÐòºÅ£¨ÈÏΪnullʱÏàµÈµÄ£©¡£Ãܼ¯µÄÐòÁзµ»ØµÄʱûÓмä¸ôµÄÊý
dense_rank() over(partition by ... order by ...)
COUNT
¶ÔÒ»×éÄÚ·¢ÉúµÄÊÂÇé½øÐÐÀÛ»ý¼ÆÊý£¬Èç¹ûÖ¸¶¨*»òһЩ·Ç¿Õ³£Êý£¬count½«¶ÔËùÓÐÐмÆÊý£¬Èç¹ûÖ¸¶¨Ò»¸ö±í´ïʽ£¬count·µ»Ø±í´ïʽ·Ç¿Õ¸³ÖµµÄ¼ÆÊý£¬µ±ÓÐÏàֵͬ³öÏÖʱ£¬ÕâЩÏàµÈµÄÖµ¶¼»á±»ÄÉÈë±»¼ÆËãµÄÖµ£»¿ÉÒÔʹÓÃDISTINCTÀ´¼Ç¼ȥµôÒ»×éÖÐÍêÈ«ÏàͬµÄÊý¾Ýºó³öÏÖµÄÐÐÊý
count(...) over(partition by ... order by ...)
MAX
ÔÚÒ»¸ö×éÖеÄÊý¾Ý´°¿ÚÖвéÕÒ±í´ïʽµÄ×î´óÖµ
max(...) over(partition by ... order by ...)
MIN
ÔÚÒ»¸ö×éÖеÄÊý¾Ý´°¿ÚÖвéÕÒ±í´ïʽµÄ×îСֵ
min(...) over(partition by ... order by ...)
SUM
¸Ãº¯Êý¼ÆËã×éÖбí´ïʽµÄÀÛ»ýºÍ
sum(...) over(partition by ... order by ...)
AVG
ÓÃÓÚ¼ÆËãÒ»¸ö×éºÍÊý¾Ý´°¿ÚÄÚ±í´ïʽµÄƽ¾ùÖµ
avg(...) over(partition by ... order by ...)
FIRST_VALUE
·µ»ØÊý¾Ý×éÖеÚÒ»¸öÖµ
first_value(...) over(partition by ... order by ...)
LAST_VALUE
·µ»ØÊý¾Ý×éÖÐ×îºóÒ»¸öÖµ
last_value(...) over(partition by ... order by ...)
LAG
¿ÉÒÔ·ÃÎʽá¹û¼¯ÖÐµÄÆäËüÐжø²»ÓýøÐÐ×ÔÁ¬½Ó¡£ËüÔÊÐíÈ¥´¦ÀíÓα꣬¾ÍºÃÏñÓαêÊÇÒ»¸öÊý×éÒ»Ñù¡£ÔÚ¸ø¶¨×éÖпɲο¼µ±Ç°ÐÐ֮ǰµÄÐУ¬ÕâÑù¾Í¿ÉÒÔ´Ó×éÖÐÓ뵱ǰÐÐÒ»ÆðÑ¡ÔñÒÔǰµÄÐС£OffsetÊÇÒ»¸öÕýÕûÊý


Ïà¹ØÎĵµ£º

×Ô¼ºÐ´µÄoracle´æ´¢¹ý³Ì»áÓõ½

create or replace procedure prc_statistic_declare(table_name varchar2 ,table_name_pass varchar2 ,not_exist varchar2,not_exist_record varchar2)
--eg:'t_statistic_bianyuanhu_month',t_statistic_bianyuanhu_month,('YEAR','STATISTIC_ID')','YESR'
is
   v_sql_column varchar2(1000);
   ......

Oracle×ܽá

Ò».OracleÊý¾Ý¿âÖг£ÓõÄÊý¾ÝÀàÐÍ
varchar2(³¤¶È)¿É±ä³¤×Ö·û´®
char(³¤¶È) ¶¨³¤
number()±íʾÕûÊý»òÕ߸¡µãÊýnumber(8) number(8,2)
clog ×Ö·ûµÄ´ó¶ÔÏó
blog ¶þ½øÖƵĴó¶ÔÏó
¶þ.Êý¾Ý¿â²éѯ
1£©SELECTÓï¾ä
´Ó±íÖÐÌáÈ¡²éѯÊý¾Ý.Ó﷨ΪSELECT [DISTINCT] {column1,column2,…} from tablename WHERE {con ......

OracleÊý¾Ý¿âµÄһЩ²Ù×÷

ÈçºÎ±¸·ÝOracleÊý¾Ý¿â£º
MD F:\DB_BAK\%date:~0,10%
EXP userid=bszlhr/bszlhr@ORCL_14 file=F:\DB_BAK\%date:~0,10%\bszlhr%date:~0,10%.dmp log=f:\DB_BAK\%date:~0,10%\log_bszlhr_%date:~0,10%.log
  buffer=64000 owner=bszlhr
-----´´½¨ÐÅÓÃÐÅÏ¢µÄ±í----------
create table GCCredit(
 itemID numb ......

Oracleѧϰ±Ê¼Ç5 JDBC²Ù×÷Oracle

ÓÃJavaÀ´²Ù×÷Êý¾Ý¿â£¬ÓÐÈçϼ¸ÖÖ·½Ê½£º
1JDBC-ODBCÇÅ£ºODBCÊý¾ÝÔ´ÔÚ²Ù×÷ϵͳ²ãÃæ¸øÓèÁËÖ§³Ö£¬ÎÒÃÇ¿ÉÒÔ²»ÓÃÁ˽âµ×²ãµ½µ×ÊÇ·â×°µÄÄÄÖÖÊý¾ÝÔ´£¬Ò²ÕÕÑù¿ÉÒÔÍê³É¶ÔÊý¾ÝµÄ²Ù×÷¡£µ«ÊÇËüÓÐÆ½Ì¨ÒÀÀµÐÔ£¬²»Ì«ÍƼöʹÓá£
2Javaµ½±¾µØAPI
3Javaµ½ÍøÂçЭÒé
4Javaµ½Êý¾Ý¿âЭÒ飺ËÙ¶È×î¿ì
Á¬½ÓÊý¾Ý¿â¿ÉÒÔ·ÖΪËIJ½£º
A¼ÓÔØÇý¶¯³ÌÐò£¨Ï ......

OracleÊý¾Ý¿âÌá¸ßÃüÖÐÂʼ°Ïà¹ØÓÅ»¯

1)Library CacheµÄÃüÖÐÂÊ:
.¼ÆË㹫ʽ:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SQL>SELECT SUM(pinhits)/sum(pins)    from V$LIBRARYCACHE; 
ͨ³£ÔÚ98%ÒÔÉÏ£¬·ñÔò£¬ÐèÒªÒª¿¼ÂǼӴó¹²Ïí³Ø£¬°ó¶¨±äÁ¿£¬ÐÞ¸Äcursor_sharingµÈ²ÎÊý¡£
2)¼ÆËã¹²Ïí³ØÄÚ´æÊ¹ÓÃÂÊ:
SQL>SELECT ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ