tag:blogger.com,1999:blog-14034673387941201642024-02-20T12:12:03.352-08:00SQL語法筆記robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.comBlogger46125tag:blogger.com,1999:blog-1403467338794120164.post-44920824526597238652015-08-11T17:18:00.002-07:002015-08-11T17:18:50.129-07:00ORACLE 排程時間設定筆記&oracle SQL裡常用的時間函數<br /><br />TRUNC(LAST_DAY(SYSDATE)) + 10 + 2/24<br /><br /><br /><br /><br /> oracle SQL裡常用的時間函數,經典推薦 <br />相信很多人都有過統計某些數據的經歷,比如,要統計財務的情況,可能要按每年,每季度,每月,甚至每個星期來分別統計。 那在oracle中應該怎麼來寫sql語句呢,這個時候Oracle的日期函數會給我們很多幫助。<br /><br />常用日期型函數 <br />1。 Sysdate當前日期和時間 <br /> SQL> Select sysdate from dual;<br /><br />SYSDATE <br />---------- <br /> 21-6月-05<br /><br />2。 Last_day本月最後一天 <br /> SQL> Select last_day(sysdate) from dual;<br /><br />LAST_DAY(S <br />---------- <br /> 30-6月-05<br /><br />3。 Add_months(d,n)當前日期d後推n個月用於從一個日期值增加或減少一些月份 <br />date_value:=add_months(date_value,number_of_months)<br /><br /> SQL> Select add_months(sysdate,2) from dual;<br /><br />ADD_MONTHS <br />---------- <br /> 21-8月-05<br /><br />4。 Months_between(f,s)日期f和s間相差月數 <br /> SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;<br /><br />MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD')) <br /> -------------------------------------------------- -------- <br />-4.6966741<br /><br />5。 NEXT_DAY(d, day_of_week) <br />返回由"day_of_week"命名的,在變量"d"指定的日期之後的第一個工作日的日期。 參數"day_of_week"必須為該星期中的某一天。 <br /> SQL> SELECT next_day(to_date('20050620','YYYYMMDD'),1) FROM dual;<br /><br />NEXT_DAY(T <br />---------- <br /> 26-6月-05<br /><br />6。 current_date()返回當前會話時區中的當前日期 <br />date_value:=current_date <br /> SQL> column sessiontimezone for a15 <br /> SQL> select sessiontimezone,current_date from dual;<br /><br /> SESSIONTIMEZONE CURRENT_DA <br /> --------------- ---------- <br /> +08:00 13-11月-03<br /><br /><br /> SQL> alter session set time_zone='-11:00' 2 / <br />會話已更改。<br /><br /> SQL> select sessiontimezone,current_timestamp from dual;<br /><br /> SESSIONTIMEZONE CURRENT_TIMESTAMP <br /> --------------- ----------------------------------- - <br /> -11:00 12-11月-03 04.59.13.668000下午-11:00<br /><br />7。 current_timestamp()以timestamp with time zone數據類型返回當前會話時區中的當前日期 <br /> SQL> select current_timestamp from dual;<br /><br />CURRENT_TIMESTAMP <br /> -------------------------------------------------- ------------------------- <br /> 21-6月-05 10.13.08.220589上午+08:00<br /><br />8。 dbtimezone()返回時區 <br /> SQL> select dbtimezone from dual;<br /><br />DBTIME <br />------ <br />-08:00<br /><br />9。 extract()找出日期或間隔值的字段值 <br /> date_value:=extract(date_field from [datetime_value|interval_value]) <br /> SQL> select extract(month from sysdate) "This Month" from dual;<br /><br /> This Month <br />---------- <br />6<br /><br /> SQL> select extract(year from add_months(sysdate,36)) " Years" from dual;<br /><br />Years <br />---------- <br />2008<br /><br />10。 localtimestamp()返回會話中的日期和時間 <br /> SQL> select localtimestamp from dual;<br /><br />LOCALTIMESTAMP <br /> -------------------------------------------------- ------------------------- <br /> 21-6月-05 10.18.15.855652上午<br /><br />11。取得week number <br />TO_CHAR(TO_DATE('2007/05/16','YYYY/MM/DD'),'IW') AS WEEK<br /><br /><br /><br /><br />常用日期數據格式(該段為摘抄)<br /><br /> Y或YY或YYY年的最後一位,兩位或三位Select to_char(sysdate,’YYY’) from dual; 002表示2002年 <br /> SYEAR或YEAR SYEAR使公元前的年份前加一負號Select to_char(sysdate,’SYEAR’) from dual; -1112表示公元前111 2年 <br /> Q季度,1~3月為第一季度Select to_char(sysdate,’Q’) from dual; 2表示第二季度① <br /> MM月份數Select to_char(sysdate,’MM’) from dual; 12表示12月 <br /> RM月份的羅馬錶示Select to_char(sysdate,’RM’) from dual; IV表示4月 <br /> Month用9個字符長度表示的月份名Select to_char(sysdate,’Month’) from dual; May後跟6個空格表示5月 <br /> WW當年第幾週Select to_char(sysdate,’WW’) from dual; 24表示2002年6月13日為第24週 <br /> W本月第幾週Select to_char(sysdate,’W’) from dual; 2002年10月1日為第1週 <br /> DDD當年第幾, 1月1日為001,2月1日為032 Select to_char(sysdate,’DDD’) from dual; 363 2002年1 2月2 9日為第363天 <br /> DD當月第幾天Select to_char(sysdate,’DD’) from dual; 04 10月4日為第4天 <br /> D週內第幾天Select to_char(sysdate,’D’) from dual; 5 2002年3月14日為星期一 <br /> DY週內第幾天縮寫Select to_char(sysdate,’DY’) from dual; SUN 2002年3月24日為星期天 <br /> HH或HH12 12進制小時數Select to_char(sysdate,’HH’) from dual; 02午夜2點過8分為02<br /> HH24 24小時制Select to_char(sysdate,’HH24’) from dual; 14下午2點08分為14 <br /> MI分鐘數(0~59) Select to_char(sysdate,’MI’) from dual; 17下午4點17分 <br /> SS秒數(0~59) Select to_char(sysdate,’SS’) from dual; 22 11點3分22秒提示注意不要將MM格式用於分鐘(分鐘應該使用MI)。 MM是用於月份的格式,將它用於分鐘也能工作,但結果是錯誤的。<br /><br /><br />現在給出一些實踐後的用法:<br /><br />1。 上月末天: <br /> SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;<br /><br />LASTDAY <br />---------- <br />2005-05-31<br /><br />2。 上月今天 <br /> SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;<br /><br /><br />PRETODAY <br />---------- <br />2005-05-21<br /><br /> 3.上月首天 <br /> SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;<br /><br />FIRSTDAY <br />---------- <br />2005-05-01<br /><br /> 4.按照每週進行統計 <br /> SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');<br /><br />TO <br />-- <br />25<br /><br />5。 按照每月進行統計 <br /> SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');<br /><br />TO <br />-- <br />06<br /><br />6。 按照每季度進行統計 <br /> SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');<br /><br />T <br />- <br />2<br /><br />7。 按照每年進行統計 <br /> SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');<br /><br />TO_C <br />---- <br />2005<br /><br /> 8.要找到某月中所有周五的具體日期 <br /> select to_char(td,'YY-MM-DD') from ( <br /> select trunc(sysdate, 'MM')+rownum-1 as d <br /> from dba_objects <br /> where rownum < 32) t <br /> where to_char(td, 'MM') = to_char(sysdate, 'MM') --找出當前月份的周五的日期<br /><br /> and trim(to_char(td, 'Day')) = '星期五' <br />-------- <br />03-05-02 <br />03-05-09 <br />03-05-16 <br />03-05-23 <br />03-05-30<br /><br />如果把where to_char(td, 'MM') = to_char(sysdate, 'MM')改成sysdate-90,即為查找當前月份的前三個月中的每週五的日期。<br /><br /> 9.oracle中時間運算<br /><br />內容如下: <br /> 1、oracle支持對日期進行運算 <br /> 2、日期運算時是以天為單位進行的 <br /> 3、當需要以分秒等更小的單位算值時,按時間進制進行轉換即可 <br /> 4、進行時間進制轉換時注意加括號,否則會出問題<br /><br /> SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';<br /><br />會話已更改。<br /><br /> SQL> set serverout on <br /> SQL> declare <br /> 2 DateValue date; <br /> 3 begin <br /> 4 select sysdate into DateValue from dual; <br /> 5 dbms_output.put_line('原時間:'||to_char(DateValue)); <br /> 6 dbms_output.put_line('原時間減1天:'||to_char(DateValue-1)); <br /> 7 dbms_output.put_line('原時間減1天1小時:'||to_char(DateValue-1-1/24)); <br /> 8 dbms_output.put_line('原時間減1天1小時1分:'||to_char(DateValue-1-1/24-1/(24*60))); <br /> 9 dbms_output.put_line('原時間減1天1小時1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60))) ; <br /> 10 end; <br /> 11 / <br />原時間:2003-12-29 11:53:41 <br />原時間減1天:2003-12-28 11:53:41 <br />原時間減1天1小時:2003-12-28 10:53:41 <br />原時間減1天1小時1分:2003-12-28 10:52:41 <br />原時間減1天1小時1分1秒:2003-12-28 10:52:40<br /><br /> PL/SQL過程已成功完成。<br /><br /><br />在Oracle中實現時間相加處理 <br /> --名稱:Add_Times <br /> --功能:返回d1與NewTime相加以後的結果,實現時間的相加 <br /> --說明:對於NewTime中的日期不予考慮 <br /> --日期:2004-12-07 <br /> --版本:1.0 <br /> --作者:Kevin<br /><br /><br /> create or replace function Add_Times(d1 in date,NewTime in date) return date <br />is <br /> hh number; <br /> mm number; <br /> ss number; <br /> hours number; <br /> dResult date; <br />begin <br /> --下面依次取出時、分、秒 <br /> select to_number(to_char(NewTime,'HH24')) into hh from dual; <br /> select to_number(to_char(NewTime,'MI')) into mm from dual; <br /> select to_number(to_char(NewTime,'SS')) into ss from dual; <br /> --換算出NewTime中小時總和,在一天的百分幾 <br /> hours := (hh + (mm / 60) + (ss / 3600))/ 24; <br /> --得出時間相加後的結果 <br /> select d1 + hours into dResult from dual; <br />return(dResult); <br /> end Add_Times;<br /><br /><br /> --測試用例 <br /> -- select Add_Times(sysdate,to_date('2004-12-06 03:23:00','YYYY-MM-DD HH24:MI:SS')) from dual<br /><br /><br />在Oracle9i中計算時間差計算時間差是Oracle DATA數據類型的一個常見問題。 Oracle支持日期計算,你可以創建諸如“日期1-日期2”這樣的表達式來計算這兩個日期之間的時間差。<br /><br /><br />一旦你發現了時間差異,你可以使用簡單的技巧來以天、小時、分鐘或者秒為單位來計算時間差。 為了得到數據差,你必須選擇合適的時間度量單位,這樣就可以進行數據格式隱藏。<br /><br />使用完善複雜的轉換函數來轉換日期是一個誘惑,但是你會發現這不是最好的解決方法。<br /><br /> round(to_number(end-date-start_date))-消逝的時間(以天為單位)<br /><br /> round(to_number(end-date-start_date)*24)-消逝的時間(以小時為單位)<br /><br /> round(to_number(end-date-start_date)*1440)-消逝的時間(以分鐘為單位)<br /><br />顯示時間差的默認模式是什麼? 為了找到這個問題的答案,讓我們進行一個簡單的SQL *Plus查詢。<br /><br /> SQL> select sysdate-(sysdate-3) from dual;<br /><br />SYSDATE-(SYSDATE-3) <br />------------------- <br />3<br /><br />這裡,我們看到了Oracle使用天來作為消逝時間的單位,所以我們可以很容易的使用轉換函數來把它轉換成小時或者分鐘。 然而,當分鐘數不是一個整數時,我們就會遇到放置小數點的問題。<br /><br />Select <br />(sysdate-(sysdate-3.111))*1440 <br />from <br />dual;<br /><br />(SYSDATE-(SYSDATE-3.111))*1440 <br />------------------------------ <br />4479.83333<br /><br />當然,我們可以用ROUND函數(即取整函數)來解決這個問題,但是要記住我們必須首先把DATE數據類型轉換成NUMBER數據類型。<br /><br />Select <br />round(to_number(sysdate-(sysdate-3.111))*1440) <br />from <br />dual;<br /><br />ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440) <br />---------------------------------------------- <br />4480<br /><br />我們可以用這些函數把一個消逝時間近似轉換成分鐘並把這個值寫入Oracle表格中。 在這個例子裡,我們有一個離線(logoff)系統級觸發機制來計算已經開始的會話時間並把它放入一個Oracle STATSPACK USER_LOG擴展表格之中。<br /><br />Update <br />perfstat.stats$user_log <br />set <br /> elapsed_minutes = <br />round(to_number(logoff_time-logon_time)*1440) <br />where <br /> user = user_id <br />and <br /> elapsed_minutes is NULL;<br /><br />查出任一年月所含的工作日 <br /> CREATE OR REPLACE FUNCTION Get_WorkingDays( <br /> ny IN VARCHAR2 <br /> ) RETURN INTEGER IS <br /> /*------------------------------------------------ ------------------------------------------ <br />函數名稱:Get_WorkingDays <br />中文名稱:求某一年月中共有多少工作日作者姓名: XINGPING <br />編寫時間: 2004-05-22 <br />輸入參數:NY:所求包含工作日數的年月,格式為yyyymm,如200405 <br />返回值:整型值,包含的工作日數目。 <br />算法描述: <br /> 1).列舉出參數給出的年月中的每一天。 這裡使用了一個表(ljrq是我的庫中的一張表。這個表可以是有權訪問的、記錄條數至少為31的任意一張表或視圖)來構造出某年月的每一天。 <br /> 2).用這些日期和一個已知星期幾的日期相減(2001-12-30是星期天),所得的差再對7求模。 如果所求年月在2001-12-30以前,那麼所得的差既是負數,求模後所得值範圍為大於-6,小於0,如-1表示星期六,故先將求模的結果加7,再求7的模. <br /> 3).過濾掉結果集中值為0和6的元素,然後求count,所得即為工作日數目。 <br /> -------------------------------------------------- -----------------------------------------------*/ <br /> Result INTEGER; <br />BEGIN <br /> SELECT COUNT(*) INTO Result <br /> FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday <br /> FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq <br /> FROM (SELECT substr(100+ROWNUM,2,2) dd <br /> FROM ljrq z WHERE Rownum<=31 <br /> ) t <br /> WHERE to_date(ny||t.dd,'yyyymmdd') <br /> BETWEEN to_date(ny,'yyyymm') <br /> AND last_day(to_date(ny,'yyyymm')) <br />)q <br /> ) a <br /> WHERE a.weekday NOT IN(0,6); <br /> RETURN Result; <br /> END Get_WorkingDays;<br /><br />______________________________________<br /><br />還有一個版本 <br /> CREATE OR REPLACE FUNCTION Get_WorkingDays( <br /> ny IN VARCHAR2 <br /> ) RETURN INTEGER IS <br /> /*------------------------------------------------ ----------------------------------------- <br />函數名稱:Get_WorkingDays <br />中文名稱:求某一年月中共有多少工作日作者姓名: XINGPING <br />編寫時間: 2004-05-23 <br />輸入參數:NY:所求包含工作日數的年月,格式為yyyymm,如200405 <br />返回值:整型值,包含的工作日數目。 <br />算法描述:使用Last_day函數計算出參數所給年月共包含多少天,根據這個值來構造一個循環。 在這個循環中先求這個月的每一天與一個已知是星期天的日期(2001-12-30是星期天)的差,所得的差再對7求模。 如果所求日期在2001-12-30以前,那麼所得的差既是負數,求模後所得值範圍為大於-6,小於0,如-1表示星期六,故先將求模的結果加7,再求7的模.如過所得值不等於0和6(即不是星期六和星期天),則算一個工作日。 <br /> -------------------------------------------------- --------------------------------------*/ <br /> Result INTEGER := 0; <br /> myts INTEGER; --所給年月的天數 <br /> scts INTEGER; --某天距2001-12-30所差的天數 <br /> rq DATE; <br /> djt INTEGER := 1; -- <br />BEGIN <br /> myts := to_char(last_day(to_date(ny,'yyyymm')),'dd'); <br />LOOP <br /> rq := TO_date(ny||substr(100+djt,2),'yyyymmdd'); <br /> scts := rq - to_date('2001-12-30','yyyy-mm-dd'); <br /> IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN <br /> Result := Result + 1; <br /> END IF; <br /> djt := djt + 1; <br /> EXIT WHEN djt>myts; <br /> END LOOP; <br /> RETURN Result; <br /> END Get_WorkingDays;<br /><br />以上兩個版本的比較<br /><br />第一個版本一條SQL語句就可以得出結果,不需要編程就可以達到目的。 但需要使用任意一張有權訪問的、記錄條數至少為31的一張表或視圖。 <br />第二個版本需要編程,但不需要表或者視圖。 <br />這兩個版本都還存在需要完善的地方,即沒有考慮節日,如五一、十一、元旦、春節這些節假期都沒有去除。 這些節假日應該維護成一張表,然後通過查表來去除這些節假日。<br /><br /> <br /><br /> <br /><br />求一段連續日期,但排除六日 <br />自已標記一下,方便以後查尋<br /><br />select allday from (<br />select to_date('2007-02-15','yyyy-mm-dd')+rownum allday from<br />(select to_date('2007-02-15','yyyy-mm-dd'),to_date('2007-02-28','yyyy-mm-dd') from dual)<br />connect by rownum < to_date('2007-02-28','yyyy-mm-dd')-to_date('2007-02-15','yyyy-mm-dd')<br />) where to_char(allday,'D') >1 and to_char(allday,'D') robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-87795209277123655022015-07-14T04:36:00.001-07:002015-07-14T04:36:01.988-07:00NVL , NVL2 ,COALESCE ,DECODE用法<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
NVL (expr1, expr2)->expr1為NULL,返回expr2;不為NULL,返回expr1。註意兩者的類型要一致</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
NVL2 (expr1, expr2, expr3) ->expr1不為NULL,返回expr2;為NULL,返回expr3。expr2和expr3類型不同的話,expr3會轉換為expr2的類型</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
NULLIF (expr1, expr2) ->相等返回NULL,不等返回expr1</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
COALESCE (eXPression_1, expression_2, ...,expression_n)<br style="letter-spacing: normal;" />列表中第一個非空的表達式是函數的返回值,假如所有的表達式都是空值,最終將返回一個空值。 <br style="letter-spacing: normal;" />NVL進階版函式 <br style="letter-spacing: normal;" />select Coalesce('C','B','A') from dual ==> C <br style="letter-spacing: normal;" />select Coalesce(null,'B','A') from dual ==> B <br style="letter-spacing: normal;" />select Coalesce(null,null,'A') from dual ==> A</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
<br /></div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
DECODE用法:</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
DECODE(value,if1,then1,if2,then2,if3,then3,……,else),表示如果value 等于if1?,DECODE函數返回then1,……<br style="letter-spacing: normal;" /><br style="letter-spacing: normal;" />select decode( x , 1 , ‘x is 1 ‘, 2 , ‘x is 2 ‘, ‘others’) from dual</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
當x等於1時,則返回‘x is 1’。</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
當x等於2時,則返回‘x is 2’。</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
否則,返回others’。</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
在需要比較2個值的時候,我們可以配合SIGN()函數一起使用。</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
SELECT DECODE( SIGN(5 -6), 1 ‘Is Positive’, -1, ‘Is Nagative’, ‘Is Zero’)</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
同樣,也可以用CASE實現:</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
SELECT CASE SIGN(5 – 6)</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
WHEN 1 THEN ‘Is Positive’</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
WHEN-1 THEN ‘Is Nagative’</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
ELSE’Is Zero’ END</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
FROM DUAL</div>
<div style="background-color: #e9faff; color: #444444; font-family: arial, 'Century Gothic'; font-size: 13px; letter-spacing: 0.649999976158142px; line-height: 22.1000003814697px; margin-bottom: 1em; padding: 0px;">
另外,大家還可以在Order by中使用Decode。</div>
robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-73644293983919938052015-06-11T20:03:00.002-07:002015-06-11T22:03:31.400-07:00我查询出50条纪录,我只想要最前面的5条纪录(按某个字段排好序的)?<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">select * from (select * from tbname order by colname)<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">where rownum<6 u=""><!--6--></6></span></div>
<u></u><br />
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">oracle</span><span style="font-family: 新細明體, serif;">没有</span><span lang="EN-US">top</span><span style="font-family: 新細明體, serif;">的用法。</span><span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: 新細明體, serif;">为什么只能</span><span lang="EN-US">rownum<<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: 新細明體, serif;">如果用到</span><span lang="EN-US">rownum > </span><span style="font-family: 新細明體, serif;">就出错?</span><span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">rownum</span><span style="font-family: 新細明體, serif;">是随着结果集生成的,一旦生成,就不会变化了;</span><span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: 新細明體, serif;">同时</span><span lang="EN-US">,</span><span style="font-family: 新細明體, serif;">生成的结果是依次递加的,没有</span><span lang="EN-US">1</span><span style="font-family: 新細明體, serif;">就永远不会有</span><span lang="EN-US">2! <u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">rownum </span><span style="font-family: 新細明體, serif;">是在</span> <span style="font-family: 新細明體, serif;">查询集合产生的过程中产生的伪列,并且如果</span><span lang="EN-US">where</span><span style="font-family: 新細明體, serif;">条件中存在</span><span lang="EN-US"> rownum </span><span style="font-family: 新細明體, serif;">条件的话,则</span><span lang="EN-US">: <u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">1</span><span style="font-family: 新細明體, serif;">:</span> <span style="font-family: 新細明體, serif;">假如</span> <span style="font-family: 新細明體, serif;">判定条件是常量,则:</span><span lang="EN-US"> <u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: 新細明體, serif;">只能</span><span lang="EN-US"> rownum = 1, <= </span><span style="font-family: 新細明體, serif;">大于</span><span lang="EN-US">1 </span><span style="font-family: 新細明體, serif;">的自然数,</span><span lang="EN-US"> = </span><span style="font-family: 新細明體, serif;">大于</span><span lang="EN-US">1 </span><span style="font-family: 新細明體, serif;">的数是没有结果的,</span> <span style="font-family: 新細明體, serif;">大于一个数也是没有结果的</span><span lang="EN-US"> <u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: 新細明體, serif;">即</span> <span style="font-family: 新細明體, serif;">当出现一个</span><span lang="EN-US"> rownum </span><span style="font-family: 新細明體, serif;">不满足条件的时候则</span> <span style="font-family: 新細明體, serif;">查询结束</span><span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: 新細明體, serif;">如果要中间的纪录的话要怎样才行?</span><span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">select * from<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">(select *,rownum num from<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">(select * from tbname order by colname) a<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">where rownum<10 b="" u=""><!--10--></10></span></div>
<u></u><br />
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">where num>4;<u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">select * from(select * from table_name order by col_name) where rownum<6 u=""><!--6--></6></span></div>
<u></u><br />
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US"><u></u><u></u></span></div>
<div class="MsoNormal" style="background-color: white; color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US">select * from (select rownum rm,a.* from table_name a order by col_name where rownum<n rm="" where="">m;</n></span></div>
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;"><br /></span>
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">-- 我們想把最先進入公司的5個人找出來</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">SELECT * FROM emp WHERE ROWNUM <= 5 ORDER BY hiredate;</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">或</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;"> WHERE ROWNUM <= 5;</span><br />
<br style="background-color: whitesmoke; box-sizing: content-box; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;" />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">-- 我們想把最先進入公司的6~10個人找出來!!!!!!!!!!!!!!</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;"> WHERE ROWNUM <= 10 </span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">MINUS</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;">SELECT * FROM (SELECT * FROM emp ORDER BY hiredate)</span><br />
<span style="background-color: whitesmoke; color: #333333; font-family: Helvetica, Tahoma, Arial, sans-serif; font-size: 14px; line-height: 24px;"> WHERE ROWNUM <= 5;</span>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-17315159994954938052010-03-19T03:44:00.001-07:002010-03-19T03:44:24.781-07:00ORA-02069: global_names parameter must be set to TRUE for this operation<style>
<!--
/* Font Definitions */
@font-face
{font-family:新細明體;
panose-1:2 2 3 0 0 0 0 0 0 0;}
@font-face
{font-family:"\@新細明體";
panose-1:2 2 3 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
/* Page Definitions */
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;
layout-grid:18.0pt;}
div.Section1
{page:Section1;}
-->
</style><br />
<div align="left" dir="ltr"><span class="828012407-19032010"><span style="color: blue;">透過 db link insert,update remote db ,有些語法要注意</span></span></div><div align="left" dir="ltr"><span class="828012407-19032010"><span style="color: blue;">insert statement 不要再 call function</span></span></div>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-46183215477889238062010-01-06T22:55:00.000-08:002010-03-05T16:49:42.119-08:00Oracle 匯出/匯入 exp/impImport (回復):<br />
開啟命令提示字元,<br />
輸入 "imp system/用戶密碼@資料庫名稱" →<br />
詢問從哪個檔案回復 →<br />
詢問陣列緩衝區的大小 →<br />
詢問「是否僅列出匯入檔案的內容」(若選擇 "no",會逐步詢問以下的相關資料,否則會跳過一些步驟) →<br />
告知「物件已存在,建立物件的錯誤被略過不予以處理」(預設值為 "no",則系統碰到錯誤就會顯示錯誤訊息) →<br />
詢問「是否匯入整個匯出檔」(若選 "yes" 則會將原先所有 Export 的資料全部匯入;若選 "no" 則會詢問是要匯入哪位 User 的 Schema、哪一個 Table)<br />
→ 自動開始回復<br />
<br />
<br />
imp system@DB_NAME file=imp_File_name.dmp fromuser =sa touser=sa ignore=Y log=imp_log_name.log buffer=81920000<br />
<br />
<br />
exp user/pwd file=test.dmp log=test.log owner=test<br />
<br />
<span style="font-family: Verdana; font-size: x-small;">(13) 資料備份 - 使用 Export、Import:<br />
● 有關 Oracle 資料備份的方式有很多種,還可分為:離線式、線上式、熱備份、冷備份,本文僅挑重點敘述。欲知詳細步驟及各種備份方式的特點請自行參考相關書籍。<br />
● 此項 EXP 備份工具,較適合備份「部份 Table 或全部 Table」、「部份 Object 或所有 Object」,亦可備份「整個資料庫」。此工具將資料 Dump 出來是以 Binary 的方式,因此無法用記事本開啟檔案。此外,須具備足夠權限才能 Export 別人 Schema 的 Object,且有少數系統內建的 Schema 亦無法以此方式 Export。<br />
<span style="color: teal;">Export (備份):<br />
開啟命令提示字元,輸入 "exp system/用戶密碼@資料庫名稱" → 詢問陣列緩衝區的大小(值的大小僅會影響備份速度) → 匯出檔案名稱(如:backup1.dmp,預設最後會將檔案匯出在 C 槽底下) → 詢問匯出類型(分為:1.整個資料庫 2.使用者 3.表格) → 接下來選項大部份都可選 "yes"(例如是否要壓縮) → 自動開始備份<br />
<br />
Import (回復):<br />
<span style="font-family: Verdana; font-size: x-small;">開啟命令提示字元,輸入 "imp system/用戶密碼@資料庫名稱" →<span style="font-family: Verdana; font-size: x-small;"><span style="font-family: Verdana; font-size: x-small;"> 詢問從哪個檔案回復<span style="font-family: Verdana; font-size: x-small;"><span style="font-family: Verdana; font-size: x-small;"><span style="font-family: Verdana; font-size: x-small;"><span style="font-family: Verdana; font-size: x-small;"> →</span></span></span></span></span></span> 詢問陣列緩衝區的大小 → 詢問「是否僅列出匯入檔案的內容」(若選擇 "no",會逐步詢問以下的相關資料,否則會跳過一些步驟) → 告知「物件已存在,建立物件的錯誤被略過不予以處理」(預設值為 "no",則系統碰到錯誤就會顯示錯誤訊息) → 詢問「是否匯入整個匯出檔」(若選 "yes" 則會將原先所有 Export 的資料全部匯入;若選 "no" 則會詢問是要匯入哪位 User 的 Schema、哪一個 Table) → 自動開始回復</span><br />
<br />
</span>● 此項 Export、Import 的備份及回復功能,必須在資料庫啟動時才能操作。版工試過可在有使用者連線時備份和回復,且結束時亦顯示未發生任何錯誤。但建議實務上還是儘量選在使用者 離線時做備份,以避免影響運作績效或對用戶作業造成干擾,甚至造成資料的不一致或毀損。</span>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-33387173472423441032009-12-28T23:38:00.001-08:002009-12-28T23:38:47.170-08:00關於Windows Oracle「ORA-12638 Credential retrieval failed」<span style="font-weight: bold;"><span style="font-size:130%;"><span style="font-family: tahoma,arial,helvetica,sans-serif;">1. 發生狀況:在資料庫(Server:Oracle 10g / Windows)進行抄寫時,出現「<span style="color: rgb(255, 0, 0);">ORA-12638:Crendential retrieval failed(證明資料擷取失敗)</span>」錯誤訊息。<br /><br />2.說明:<br /><br />Client端無法登入到遠端Server時,就會出現該狀況。<br /><br />3.可能原因:<br /> <span style="color: rgb(0, 0, 255);">Server有開放OS認證模式,已知在Windows機器上,容易導致client端抄寫失敗</span></span></span></span><span style="font-weight: bold;"><span style="font-size:130%;"><span style="font-family: tahoma,arial,helvetica,sans-serif;"><span style="color: rgb(0, 0, 255);">。</span><br /><br />4.解決:<br /> <span style="color: rgb(0, 0, 255);">(1)登入到抄寫的Server(也就是提供資料的Oracle 10g機器)上的Windows作業系統。<br />(2)編輯「%Oracle_HOME%\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora」檔案,做如下方框內的修正:<br /></span></span></span></span><span style="font-weight: bold;"><span style="font-size:130%;"><span style="font-family: tahoma,arial,helvetica,sans-serif;"><span style="color: rgb(0, 0, 255);"> </span></span></span></span> <table style="width: 501px; height: 48px; font-family: tahoma,arial,helvetica,sans-serif; font-weight: bold; color: rgb(255, 0, 0);" border="1"><tbody><tr><td><span style="font-size:130%;"><span style="color: rgb(255, 0, 0);"># This file is actually generated by netca. But if customers choose to </span><br /><span style="color: rgb(255, 0, 0);"># install "Software Only", this file wont exist and without the native </span><br /><span style="color: rgb(255, 0, 0);"># authentication, they will not be able to connect to the database on NT.</span><br /><br /><span style="color: rgb(255, 0, 0);">#SQLNET.AUTHENTICATION_SERVICES = (NTS) <span style="color: rgb(0, 0, 255);">--> 註解這一行,取消OS認證!</span></span><br /><span style="color: rgb(255, 0, 0);">NAMES.DIRECTORY_PATH= (TNSNAMES)</span><br /></span></td></tr></tbody></table><br /><span style="font-weight: bold;"><span style="font-size:130%;"><span style="font-family: tahoma,arial,helvetica,sans-serif;"><span style="color: rgb(0, 0, 255);">(3)修改完成後存檔離開。離開後,可以再試試看DB抄寫,應該要能正常運作!</span></span></span></span>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-49528818571039534022009-12-18T01:59:00.001-08:002009-12-18T01:59:35.483-08:00查看那些 table被lock之前的工作從來沒有遇過 table 被 lock住, 但是到了新的環境居然table三天兩頭被lock, 我猜是ap開發習慣差異導致, anyway 以下的 sql是查看那些 table被lock的語法<br /><pre>select <br /><br /> oracle_username os_user_name, <br /><br /> locked_mode, <br /><br /> object_name, <br /><br /> object_type<br /><br />from v$locked_object a,dba_objects b<br /><br />where a.object_id = b.object_id</pre>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-76449811806903457532009-12-18T01:50:00.001-08:002010-03-19T03:45:32.259-07:00DBA<ul><li><a href="http://sql.robbin0919.com/2009/12/clear-shared-poolora-04031.html">Clear the Shared Pool及ORA-04031</a></li>
<li><a href="http://sql.robbin0919.com/2009/12/tablelock.html">查看那些 table被lock</a></li>
<li><a href="http://www.blogger.com/post-edit.g?blogID=1403467338794120164&postID=7644981180690345753" name="3338717347242344103"></a></li>
<li><a href="http://sql.robbin0919.com/2009/12/windows-oracleora-12638-credential.html">關於Windows Oracle「ORA-12638 Credential retrieval failed」</a></li>
<li><a href="http://sql.robbin0919.com/2010/01/oracle-expimp.html">Oracle 匯出/匯入 exp/imp</a></li>
<li><h3 class="post-title entry-title"> <a href="http://sql.robbin0919.com/2010/03/ora-02069-globalnames-parameter-must-be.html">ORA-02069: global_names parameter must be set to TRUE for this operation</a> </h3><style>
<!--
/* Font Definitions */
@font-face
{font-family:新細明體;
panose-1:2 2 3 0 0 0 0 0 0 0;}
@font-face
{font-family:"\@新細明體";
panose-1:2 2 3 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
/* Page Definitions */
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;
layout-grid:18.0pt;}
div.Section1
{page:Section1;}
-->
</style><br />
</li>
</ul>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-36510410941539970422009-12-18T01:47:00.000-08:002009-12-18T01:48:23.165-08:00Clear the Shared Pool及ORA-04031<p>有無清除Oracle先前Select的方法,</p><p>想用來評估新的SQL語句Perfromance優劣。</p><p>答案是有的,清空Shared Pool,語法如下:</p><p><code><span style="font-family:細明體;">ALTER SYSTEM FLUSH SHARED_POOL;</span></code></p><p><code><span style="font-family:細明體;">這通常也就是被拿來做Performance分析。</span></code></p><p><code><span style="font-family:細明體;">另外一個用途是當發生ORA-04031時,先拿來應急,</span></code></p><p><code><span style="font-family:細明體;">再慢慢找出根本原因及解決之道。</span></code></p>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-71096119866450759062009-12-17T22:25:00.001-08:002009-12-17T22:25:44.463-08:00oracle 日期常用函數 (SYSDATE、日期格式)<pre>SYSDATE<br />--◎ 可得到目前系統的時間 <br /> <br /> ex. <br /> select sysdate from dual;<br /> <br /> sysdate <br /> ---------- <br /> 20-SEP-07<br /> <br />常用之日期格式<br /><br />日期格式 說明<br />------------------------------------------------------------------------<br />YYYY/MM/DD -- 年/月/日<br />YYYY -- 年(4位)<br />YYY -- 年(3位)<br />YY -- 年(2位)<br />MM -- 月份<br />DD -- 日期<br />D -- 星期<br /> -- 星期日 = 1 星期一 = 2 星期二 = 3<br /> -- 星期三 = 4 星期四 = 5 星期五 = 6 星期六 = 7<br /> <br />DDD -- 一年之第幾天<br />WW -- 一年之第幾週<br />W -- 一月之第幾週<br />YYYY/MM/DD HH24:MI:SS -- 年/月/日 時(24小時制):分:秒<br />YYYY/MM/DD HH:MI:SS -- 年/月/日 時(非24小時制):分:秒<br />J -- Julian day,Bc 4712/01/01 為1<br />RR/MM/DD -- 公元2000問題<br /> -- 00-49 = 下世紀;50-99 = 本世紀<br />ex.<br />select to_char(sysdate,'YYYY/MM/DD') FROM DUAL; -- 2007/09/20<br />select to_char(sysdate,'YYYY') FROM DUAL; -- 2007<br />select to_char(sysdate,'YYY') FROM DUAL; -- 007<br />select to_char(sysdate,'YY') FROM DUAL; -- 07<br />select to_char(sysdate,'MM') FROM DUAL; -- 09<br />select to_char(sysdate,'DD') FROM DUAL; -- 20<br />select to_char(sysdate,'D') FROM DUAL; -- 5<br />select to_char(sysdate,'DDD') FROM DUAL; -- 263<br />select to_char(sysdate,'WW') FROM DUAL; -- 38<br />select to_char(sysdate,'W') FROM DUAL; -- 3<br />select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') FROM DUAL; -- 2007/09/20 15:24:13<br />select to_char(sysdate,'YYYY/MM/DD HH:MI:SS') FROM DUAL; -- 2007/09/20 03:25:23<br />select to_char(sysdate,'J') FROM DUAL; -- 2454364<br />select to_char(sysdate,'RR/MM/DD') FROM DUAL; -- 07/09/20</pre>robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-10730556012422889602009-10-16T20:43:00.001-07:002009-10-16T20:43:32.544-07:00最大值 MAX()<p>MAX() 函數來計算一個欄位的最大值。計算最大值的語法是: </p> <p><font color="#008000"><b>SELECT MAX("欄位名") <br />FROM "表格名" </b> <br /></font></p> <p>舉例來說,若要由我們的範例表格中求出 Sales 欄位的最大值, </p> <p><font size="2">Store_Information</font> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>store_name </td> <td>Sales </td> <td>Date </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$1500 </td> <td>Jan-05-1999 </td> </tr> <tr> <td>San Diego </td> <td align="right">$250 </td> <td>Jan-07-1999 </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$300 </td> <td>Jan-08-1999 </td> </tr> <tr> <td>Boston </td> <td align="right">$700 </td> <td>Jan-08-1999 </td> </tr> </tbody></table> <br /></p> <p>我們就鍵入, <br /></p> <p><font color="#0000ff"><b>SELECT MAX(Sales) FROM Store_Information </b></font> <br /> <br /><i>結果:</i> </p> <p> <table border="0"><tbody> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><u><b>MAX(Sales)</b></u></font></td> </tr> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><center><b>$1500</b></center></font></td> </tr> </tbody></table> </p> <p>$1500 代表所有 Sales 欄位 ($1500 、$250 、$300 、$700) 內的最大值。 </p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-4080254850466297572009-10-16T20:42:00.001-07:002009-10-16T20:42:35.387-07:00最小值 MIN()<p>MIN() 函數來計算一個欄位的最小值。計算最大值的語法是: </p> <p><font color="#008000"><b>SELECT MIN("欄位名") <br />FROM "表格名" </b> <br /></font></p> <p>舉例來說,若我們要由我們的範例表格中求出 Sales 欄位的最小值, </p> <p><font size="2">Store_Information</font> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>store_name </td> <td>Sales </td> <td>Date </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$1500 </td> <td>Jan-05-1999 </td> </tr> <tr> <td>San Diego </td> <td align="right">$250 </td> <td>Jan-07-1999 </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$300 </td> <td>Jan-08-1999 </td> </tr> <tr> <td>Boston </td> <td align="right">$700 </td> <td>Jan-08-1999 </td> </tr> </tbody></table> <br /></p> <p>我們就鍵入, <br /></p> <p><font color="#0000ff"><b>SELECT MIN(Sales) FROM Store_Information </b></font> <br /> <br /><i>結果:</i> </p> <p> <table border="0"><tbody> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><u><b>MIN(Sales)</b></u></font></td> </tr> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><center><b>$200</b></center></font></td> </tr> </tbody></table> </p> <p>$200 代表所有 Sales 欄位 ($1500 、$250 、$300 、$700) 內的最小值。 </p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-25748127225935747462009-10-16T20:40:00.001-07:002009-10-16T20:40:28.414-07:00總合 SUM()<p>SUM() 函數來計算一個欄位的總合。計算總合的語法是: </p> <p><font color="#008000"><b>SELECT SUM("欄位名") <br />FROM "表格名" </b> <br /></font></p> <p>舉例來說,若我們要由我們的範例表格中求出 Sales 欄位的總合, </p> <p><font size="2">Store_Information</font> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>store_name </td> <td>Sales </td> <td>Date </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$1500 </td> <td>Jan-05-1999 </td> </tr> <tr> <td>San Diego </td> <td align="right">$250 </td> <td>Jan-07-1999 </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$300 </td> <td>Jan-08-1999 </td> </tr> <tr> <td>Boston </td> <td align="right">$700 </td> <td>Jan-08-1999 </td> </tr> </tbody></table> <br /></p> <p>我們就鍵入, <br /></p> <p><font color="#0000ff"><b>SELECT SUM(Sales) FROM Store_Information </b></font> <br /> <br /><i>結果:</i> </p> <p> <table border="0"><tbody> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><u><b>SUM(Sales)</b></u></font></td> </tr> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><center><b>$2750</b></center></font></td> </tr> </tbody></table> </p> <p>$2750 代表所有 Sales 欄位 ($1500 、$250 、$300 、$700) 的總合。 </p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-62667962178881080752009-10-16T20:38:00.001-07:002009-10-16T20:40:46.734-07:00平均值 AVG()<p>AVG() 函數來計算平均值。計算平均值的語法是: </p> <p><font color="#008000"><b>SELECT AVG("欄位名") <br />FROM "表格名" </b> <br /></font></p> <p>舉例來說,若我們要由我們的範例表格中求出 Sales 欄位的平均值, </p> <p><font size="2">Store_Information</font> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>store_name </td> <td>Sales </td> <td>Date </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$1500 </td> <td>Jan-05-1999 </td> </tr> <tr> <td>San Diego </td> <td align="right">$250 </td> <td>Jan-07-1999 </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$300 </td> <td>Jan-08-1999 </td> </tr> <tr> <td>Boston </td> <td align="right">$700 </td> <td>Jan-08-1999 </td> </tr> </tbody></table> <br /></p> <p>我們就鍵入, <br /></p> <p><font color="#0000ff"><b>SELECT AVG(Sales) FROM Store_Information </b></font> <br /> <br /><i>結果:</i> </p> <p> <table border="0"><tbody> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><u><b>AVG(Sales)</b></u></font></td> </tr> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><center><b>$678.5</b></center></font></td> </tr> </tbody></table> </p> <p>$678.5 代表所有 Sales 欄位的平均值: ($1500 + $250 + $300 + $700) / 4。 </p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-24023374882859650772009-10-16T20:35:00.001-07:002009-10-16T20:35:12.455-07:00CONCAT 及 ||<p></p> <p>有的時候,我們有需要將由不同欄位獲得的資料串連在一起。</p> <p>CONCAT() 的語法如下: </p> <p><font color="#008000"><b>CONCAT(字串1, 字串2, 字串3, ...)</b></font>: 將字串1、字串2、字串3,等字串連在一起。請注意,Oracle的CONCAT()只允許兩個參數;換言之,一次只能將兩個字串串連起來。不過,在Oracle中,我們可以用'||'來一次串連多個字串。 </p> <p>來看一個例子。假設我們有以下的表格: </p> <p><font size="2">Geography 表格</font> <table class="content" border="1"><tbody> <tr> <td>region_name</td> <td>store_name</td> </tr> <tr> <td>East</td> <td>Boston</td> </tr> <tr> <td>East</td> <td>New York</td> </tr> <tr> <td>West</td> <td>Los Angeles</td> </tr> <tr> <td>West</td> <td>San Diego</td> </tr> </tbody></table> </p> <p>例子1: </p> <p><b>MySQL/Oracle</b>: <br /><font color="#0000ff"><b>SELECT CONCAT(region_name,store_name) FROM Geography <br />WHERE store_name = 'Boston';</b></font> </p> <p><i>結果</i>: </p> <p><font color="#4169e1"><b>'EastBoston'</b></font> </p> <p>例子2: </p> <p><b>Oracle</b>: <br /><font color="#0000ff"><b>SELECT region_name || ' ' || store_name FROM Geography <br />WHERE store_name = 'Boston';</b></font> </p> <p><i>結果</i>: </p> <p><font color="#4169e1"><b>'East Boston'</b></font></p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-12451472839376783602009-10-16T20:30:00.001-07:002009-10-16T20:30:26.689-07:00Replace<p><b>Replace</b>函數是用來改變一個字串的內容。這個函數的語法如下: </p> <p><font color="#008000"><b>Replace(str1, str2, str3)</b></font>: 在字串 str1 中,當 str2 出現時,將其以 str3 替代。</p> <p>舉個例子。假設我們有以下的表格: </p> <p><font size="2">Geography</font> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>region_name</td> <td>store_name</td> </tr> <tr> <td>East</td> <td>Boston</td> </tr> <tr> <td>East</td> <td>New York</td> </tr> <tr> <td>West</td> <td>Los Angeles</td> </tr> <tr> <td>West</td> <td>San Diego</td> </tr> </tbody></table> </p> <p>以下的 Replace 函數, </p> <p><font color="#0000ff"><b>SELECT REPLACE(region_name, 'ast', 'astern') <br />FROM Geography;</b></font> </p> <p><i>會獲得如下的結果</i>: <table class="result" border="1"><tbody> <tr> <td>region_name</td> </tr> <tr> <td>Eastern</td> </tr> <tr> <td>Eastern</td> </tr> <tr> <td>West</td> </tr> <tr> <td>West</td> </tr> </tbody></table></p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-73184711894708576112009-10-16T20:22:00.001-07:002009-10-16T20:22:58.178-07:00如何關/閉 table下所有TRIGGERS?<p>ALTER TABLE <font color="#0000ff"><strong><em> TABLE_NAME</em></strong></font>  DISABLE ALL TRIGGERS;</p> <p>ALTER TABLE <font color="#0000ff"><strong><em>TABLE_NAME</em></strong></font>  ENABLE ALL TRIGGERS;</p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-71481256277830562472009-09-16T23:13:00.001-07:002009-09-16T23:13:04.698-07:00substring<p></p> <p>SQL 中的 substring 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣: </p> <ul> <li>MySQL: SUBSTR(), SUBSTRING() </li> <li>Oracle: SUBSTR() </li> <li>SQL Server: SUBSTRING() </li> </ul> <p>最常用到的方式如下 (在這裡我們用SUBSTR()為例): </p> <p><font color="#008000"><b>SUBSTR(str,pos)</b></font>: 由<str>中,選出所有從第<pos>位置開始的字元。請注意,這個語法不適用於SQL Server上。 </p> <p><font color="#008000"><b>SUBSTR(str,pos,len)</b></font>: 由<str>中的第<pos>位置開始,選出接下去的<len>個字元。 </p> <p>假設我們有以下的表格: </p> <p><font size="2">Geography 表格</font> <table class="content" border="1"><tbody> <tr> <td>region_name</td> <td>store_name</td> </tr> <tr> <td>East</td> <td>Boston</td> </tr> <tr> <td>East</td> <td>New York</td> </tr> <tr> <td>West</td> <td>Los Angeles</td> </tr> <tr> <td>West</td> <td>San Diego</td> </tr> </tbody></table> </p> <p>例1: </p> <p><font color="#0000ff"><b>SELECT SUBSTR(store_name, 3) <br />FROM Geography <br />WHERE store_name = 'Los Angeles';</b></font> </p> <p><i>結果</i>: </p> <p><font color="#4169e1"><b>'s Angeles'</b></font> </p> <p>例2: </p> <p><font color="#0000ff"><b>SELECT SUBSTR(store_name,2,4) <br />FROM Geography <br />WHERE store_name = 'San Diego';</b></font> </p> <p><i>結果</i>: </p> <p><font color="#4169e1"><b>'an D'</b></font></p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-11321481389272357752009-09-04T20:12:00.001-07:002009-09-04T20:17:49.654-07:00自我連接Self-join<p> </p> <p>同 一 個 表 格 連 接 到 自 己 , 這 就 稱 為 「 自 我 連 接 」 (Self-join) 。假 設 你 想 知 道 每 個 僱 員 的 上 司 名 稱 , 你 可 以 把 EMP 進 行 自 我 連 接 :</p> <p>select  EMP.EMPLOYEEID  ,EMP.CHN_NAME ,EMP.TITLE   , <br />EMP.MANAGER_ID  <br /> from EMPLOYEES EMP </p> <table cellspacing="0" cellpadding="0" border="0"><tbody> <tr> <td width="123">EMPLOYEEID</td> <td width="109">CHN_NAME</td> <td width="93">TITLE</td> <td width="129">MANAGER_ID</td> </tr> <tr> <td>1</td> <td>張瑾雯</td> <td>業務</td> <td>2</td> </tr> <tr> <td>2</td> <td>陳季暄</td> <td>業務經理</td> <td>5</td> </tr> <tr> <td>3</td> <td>趙飛燕</td> <td>業務</td> <td>2</td> </tr> <tr> <td>4</td> <td>林美麗</td> <td>業務</td> <td>1</td> </tr> <tr> <td>5</td> <td>劉天王</td> <td>業務經理</td> <td>13</td> </tr> <tr> <td>6</td> <td>黎國明</td> <td>業務</td> <td>5</td> </tr> <tr> <td>7</td> <td>郭國臹</td> <td>業務</td> <td>5</td> </tr> <tr> <td>8</td> <td>蘇涵蘊</td> <td>業務主管</td> <td>2</td> </tr> <tr> <td>9</td> <td>孟庭亭</td> <td>業務</td> <td>5</td> </tr> <tr> <td>12</td> <td>賴俊良</td> <td>資深工程師</td> <td>2</td> </tr> <tr> <td>13</td> <td>何大樓</td> <td>助手</td> <td>1</td> </tr> <tr> <td>14</td> <td>王大德</td> <td>工程師</td> <td>2</td> </tr> </tbody></table> <p>select  EMP.EMPLOYEEID "員工編號",EMP.CHN_NAME "員工姓名",EMP.TITLE "員工職稱",'---->', <br />MANAGER.EMPLOYEEID "所屬主管編號",MANAGER.CHN_NAME "所屬主管姓名",MANAGER.TITLE "所屬主管職稱" <br /> from EMPLOYEES EMP,EMPLOYEES MANAGER  <br /> where  EMP.MANAGER_ID = MANAGER.EMPLOYEEID(+) </p> <p> </p> <div align="center"> <table style="width: 502pt; border-collapse: collapse" cellspacing="0" cellpadding="0" width="668" align="center" border="0" x:str="x:str"><colgroup><col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2624" span="span" width="82" /><col style="width: 70pt; mso-width-source: userset; mso-width-alt: 2976" width="93" /><col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1632" width="51" /><col style="width: 90pt; mso-width-source: userset; mso-width-alt: 3840" span="span" width="120" /></colgroup><tbody> <tr style="height: 16.5pt" height="22"> <td class="xl24" style="width: 62pt; height: 16.5pt" width="82" height="22">員工編號</td> <td class="xl24" style="width: 62pt" width="82">員工姓名</td> <td class="xl24" style="width: 70pt" width="93">員工職稱</td> <td class="xl24" style="width: 38pt" width="51" x:str="'---->'">---->'</td> <td class="xl24" style="width: 90pt" width="120">所屬主管編號</td> <td class="xl24" style="width: 90pt" width="120">所屬主管姓名</td> <td class="xl24" style="width: 90pt" width="120">所屬主管職稱</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">13</td> <td class="xl25">何大樓</td> <td class="xl25">助手</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">1</td> <td class="xl25">張瑾雯</td> <td class="xl25">業務</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">4</td> <td class="xl25">林美麗</td> <td class="xl25">業務</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">1</td> <td class="xl25">張瑾雯</td> <td class="xl25">業務</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">14</td> <td class="xl25">王大德</td> <td class="xl25">工程師</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">2</td> <td class="xl25">陳季暄</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">12</td> <td class="xl25">賴俊良</td> <td class="xl25">資深工程師</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">2</td> <td class="xl25">陳季暄</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">8</td> <td class="xl25">蘇涵蘊</td> <td class="xl25">業務主管</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">2</td> <td class="xl25">陳季暄</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">3</td> <td class="xl25">趙飛燕</td> <td class="xl25">業務</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">2</td> <td class="xl25">陳季暄</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">1</td> <td class="xl25">張瑾雯</td> <td class="xl25">業務</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">2</td> <td class="xl25">陳季暄</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">9</td> <td class="xl25">孟庭亭</td> <td class="xl25">業務</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">5</td> <td class="xl25">劉天王</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">7</td> <td class="xl25">郭國臹</td> <td class="xl25">業務</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">5</td> <td class="xl25">劉天王</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">6</td> <td class="xl25">黎國明</td> <td class="xl25">業務</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">5</td> <td class="xl25">劉天王</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">2</td> <td class="xl25">陳季暄</td> <td class="xl25">業務經理</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">5</td> <td class="xl25">劉天王</td> <td class="xl25">業務經理</td> </tr> <tr style="height: 16.5pt" height="22"> <td class="xl25" style="height: 16.5pt" height="22" x:num="x:num">5</td> <td class="xl25">劉天王</td> <td class="xl25">業務經理</td> <td class="xl25">----></td> <td class="xl25" x:num="x:num">13</td> <td class="xl25">何大樓</td> <td class="xl25">助手</td> </tr> </tbody></table> </div> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-5151884423862456822009-09-04T19:35:00.001-07:002009-09-04T19:35:11.011-07:00SQL 算排名<p>列出每一行的排名是一個常見的需求,可惜 SQL 並沒有一個很直接的方式達到這個需求。要以 SQL 列出排名,基本的概念是要做一個表格自我連結 (self join),將結果依序列出,然後算出每一行之前 (包含那一行本身) 有多少行數。這樣講讀者聽得可能有點困惑,所以最好的方式是用一個實例來介紹。假設我們有以下的表格: </p> <p><i><b>Total_Sales</b></i> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>Name</td> <td>Sales</td> </tr> <tr> <td>John</td> <td>10</td> </tr> <tr> <td>Jennifer</td> <td>15</td> </tr> <tr> <td>Stella</td> <td>20</td> </tr> <tr> <td>Sophia</td> <td>40</td> </tr> <tr> <td>Greg</td> <td>50</td> </tr> <tr> <td>Jeff</td> <td>20</td> </tr> </tbody></table> <br /></p> <p>要找出每一行的排名,我們就打入以下的 SQL 語句:</p> <p><font color="#0000ff"><b>SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank <br />FROM Total_Sales a1, Total_Sales a2 <br />WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name) <br />GROUP BY a1.Name, a1.Sales <br />ORDER BY a1.Sales DESC, a1.Name DESC;</b></font></p> <p><i>結果:</i></p> <table class="result" border="0"><tbody> <tr> <td><u>Name</u></td> <td><u>Sales</u></td> <td><u>Sales_Rank</u></td> </tr> <tr> <td>Greg</td> <td>50</td> <td>1</td> </tr> <tr> <td>Sophia</td> <td>40</td> <td>2</td> </tr> <tr> <td>Stella</td> <td>20</td> <td>3</td> </tr> <tr> <td>Jeff</td> <td>20</td> <td>3</td> </tr> <tr> <td>Jennifer</td> <td>15</td> <td>5</td> </tr> <tr> <td>John</td> <td>10</td> <td>6</td> </tr> </tbody></table> <p>我們先來看 <font color="#008000"><b>WHERE</b></font> 子句。在字句的第一部分 (a1.Sales <= a2.Sales),我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料,那這部分的 <font color="#008000"><b>WHERE</b></font> 子句本身就可以產生出正確的排名。 </p> <p>子句的第二部分,(a1.Sales=a2.Sales and a1.Name = a2.Name),則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料),仍然能夠產生正確的排名。 </p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-25842700624234704732009-09-04T19:27:00.001-07:002009-09-04T19:27:14.182-07:00EXISTS<p>基本上, <b>EXISTS</b> 是用來測試內查詢有沒有產生任何結果。如果有的話,系統就會執行外查詢中的 SQL。若是沒有的話,那整個 SQL 語句就不會產生任何結果。 </p> <p><b>EXISTS</b> 的語法是: </p> <p><font color="#008000"><b>SELECT "欄位1" <br />FROM "表格1" <br />WHERE EXISTS <br />(SELECT * <br />FROM "表格2" <br />WHERE [條件])</b></font></p> <p>在內查詢中,我們並不一定要用 * 來選出所有的欄位。我們也可以選擇表格2中的任何欄位。這兩種做法最後的結果是一樣的。 </p> <p>來看一個例子。假設我們有以下的兩個表格: </p> <p><i><b>Store_Information</b></i> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>store_name</td> <td>Sales</td> <td>Date</td> </tr> <tr> <td>Los Angeles</td> <td>$1500</td> <td>Jan-05-1999</td> </tr> <tr> <td>San Diego</td> <td>$250</td> <td>Jan-07-1999</td> </tr> <tr> <td>Los Angeles</td> <td>$300</td> <td>Jan-08-1999</td> </tr> <tr> <td>Boston</td> <td>$700</td> <td>Jan-08-1999</td> </tr> </tbody></table> </p> <p><i><b>Geography</b></i> 表格 <table class="content" border="1"><tbody> <tr> <td>region_name</td> <td>store_name</td> </tr> <tr> <td>East</td> <td>Boston</td> </tr> <tr> <td>East</td> <td>New York</td> </tr> <tr> <td>West</td> <td>Los Angeles</td> </tr> <tr> <td>West</td> <td>San Diego</td> </tr> </tbody></table> </p> <p>而我們打入的 SQL 是: </p> <p><font color="#0000ff"><b>SELECT SUM(Sales) FROM Store_Information <br />WHERE EXISTS <br />(SELECT * FROM Geography <br />WHERE region_name = 'West')</b></font></p> <p>我們會得到以下的答案: </p> <p> <table border="0"><tbody> <tr> <td><u><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><b>SUM(Sales)</b></font></u></td> </tr> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><b>2750</b></font></td> </tr> </tbody></table> </p> <p>乍看之下,這個答案似乎不太正確,因為內查詢有包含一個 [region_name = 'West'] 的條件,可是最後的答案並沒有包含這個條件。實際上,這並沒有問題。在這個例子中,內查詢產生了超過一筆的資料,所以 <b>EXISTS</b> 的條件成立,所以外查詢被執行。而外查詢本身並沒有包含 [region_name = 'West'] 這個條件。 </p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-42510776172907607112009-08-25T11:41:00.001-07:002009-08-25T11:41:57.927-07:00基本語法<table cellspacing="0" cellpadding="2" width="681" border="1"><tbody> <tr> <td valign="top" width="679">程式碼</td> </tr> <tr> <td valign="top" width="679"> <p>DECLARE </p> <p>V_STRING VARCHAR2(20); </p> <p>V_DIAMETER NUMBER(5, 2); </p> <p>V_CIRCUMFERENCE NUMBER(5, 2); </p> <p>V_RADIUS NUMBER(5, 2) := 5; </p> <p>C_PI CONSTANT NUMBER(3,2) := 3.14; </p> <p>V_COUNTER NUMBER(5); </p> <p>BEGIN </p> <p>-- THIS IS SINGLE-LINE COMMENT </p> <p>/* </p> <p>THIS IS </p> <p>MULTI-LINE </p> <p>COMMENT </p> <p>*/ </p> <p>V_DIAMETER := 9; </p> <p>V_CIRCUMFERENCE := V_DIAMETER * C_PI; </p> <p>DBMS_OUTPUT.PUT_LINE('V_CIRCUMFERENCE = ' || V_CIRCUMFERENCE); </p> <p>IF V_RADIUS IS NULL THEN </p> <p>DBMS_OUTPUT.PUT_LINE('V_RADIUS IS NULL.'); </p> <p>ELSIF V_RADIUS > 9 THEN </p> <p>DBMS_OUTPUT.PUT_LINE('V_RADIUS IS GREATER THAN 9.'); </p> <p>ELSIF V_RADIUS < 9 THEN </p> <p>DBMS_OUTPUT.PUT_LINE('V_RADIUS IS LESS THAN 9.'); </p> <p>ELSE </p> <p>DBMS_OUTPUT.PUT_LINE('V_RADIUS IS EQUAL TO 9.'); </p> <p>END IF; </p> <p>V_COUNTER := 1; </p> <p>LOOP </p> <p>EXIT WHEN V_COUNTER >= 6; </p> <p>DBMS_OUTPUT.PUT_LINE('LOOP: V_COUNTER = ' || V_COUNTER); </p> <p>V_COUNTER := V_COUNTER + 1; </p> <p>END LOOP; </p> <p>V_COUNTER := 1; </p> <p>WHILE (V_COUNTER <= 5) LOOP </p> <p>DBMS_OUTPUT.PUT_LINE('WHILE LOOP: V_COUNTER = ' || V_COUNTER); </p> <p>V_COUNTER := V_COUNTER + 1; </p> <p>EXIT WHEN V_COUNTER >= 9; </p> <p>END LOOP; </p> <p>FOR V_LOCAL_COUNTER IN 1 .. 5 LOOP </p> <p>DBMS_OUTPUT.PUT_LINE('FOR LOOP: V_LOCAL_COUNTER = ' || V_LOCAL_COUNTER); </p> <p>END LOOP; </p> <p>FOR V_LOCAL_COUNTER IN REVERSE 1 .. 5 LOOP </p> <p>DBMS_OUTPUT.PUT_LINE('FOR LOOP REVERSE: V_LOCAL_COUNTER = ' || V_LOCAL_COUNTER); </p> <p>END LOOP; </p> <p>END; </p> <p>/</p> </td> </tr> </tbody></table> <p> </p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="400">執行結果</td> </tr> <tr> <td valign="top" width="400"> <p>V_CIRCUMFERENCE = 28.26</p> <p>V_RADIUS IS LESS THAN 9.</p> <p>LOOP: V_COUNTER = 1</p> <p>LOOP: V_COUNTER = 2</p> <p>LOOP: V_COUNTER = 3</p> <p>LOOP: V_COUNTER = 4</p> <p>LOOP: V_COUNTER = 5</p> <p>WHILE LOOP: V_COUNTER = 1</p> <p>WHILE LOOP: V_COUNTER = 2</p> <p>WHILE LOOP: V_COUNTER = 3</p> <p>WHILE LOOP: V_COUNTER = 4</p> <p>WHILE LOOP: V_COUNTER = 5</p> <p>FOR LOOP: V_LOCAL_COUNTER = 1</p> <p>FOR LOOP: V_LOCAL_COUNTER = 2</p> <p>FOR LOOP: V_LOCAL_COUNTER = 3</p> <p>FOR LOOP: V_LOCAL_COUNTER = 4</p> <p>FOR LOOP: V_LOCAL_COUNTER = 5</p> <p>FOR LOOP REVERSE: V_LOCAL_COUNTER = 5</p> <p>FOR LOOP REVERSE: V_LOCAL_COUNTER = 4</p> <p>FOR LOOP REVERSE: V_LOCAL_COUNTER = 3</p> <p>FOR LOOP REVERSE: V_LOCAL_COUNTER = 2</p> <p>FOR LOOP REVERSE: V_LOCAL_COUNTER = 1</p> </td> </tr> </tbody></table> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-18662279294437952302009-08-25T11:31:00.001-07:002009-08-25T11:38:41.746-07:00HELLO<p></p> <table cellspacing="0" cellpadding="2" width="400" border="0"><tbody> <tr> <td valign="top" width="34"> </td> <td valign="top" width="366"> <p>SET SERVEROUTPUT ON</p> </td> </tr> </tbody></table> <p></p> <p> <br /></p> <table cellspacing="0" cellpadding="2" width="400" border="0"><tbody> <tr> <td valign="top" width="24"> </td> <td valign="top" width="376">DECLARE <br />  V_STRING VARCHAR(200); <br />BEGIN <br />  V_STRING := 'HELLO'; <br />  DBMS_OUTPUT.PUT_LINE(V_STRING); <br />END; <br />/ </td> </tr> </tbody></table> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-14186062378207691812009-08-25T11:04:00.001-07:002009-08-25T11:04:10.910-07:00Oracle Personal Edition 安裝<p><a title="http://www.oracle.com/technology/software/products/8i_personal/index.html" href="http://www.oracle.com/technology/software/products/8i_personal/index.html">http://www.oracle.com/technology/software/products/8i_personal/index.html</a></p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0tag:blogger.com,1999:blog-1403467338794120164.post-52422001612218198522009-08-25T10:48:00.001-07:002009-08-25T10:48:58.470-07:00GROUP BY "欄位1" HAVING (函數條件)<p></p> <p>舉例來說,我們可能只需要知道哪些店的營業額有超過 $1,500。在這個情況下,我們不能使用 <font color="#008000"><b>WHERE</b></font> 的指令。那要怎麼辦呢?很幸運地,SQL 有提供一個 <font color="#008000"><b>HAVING</b></font> 的指令,而我們就可以用這個指令來達到這個目標。 <font color="#008000"><b>HAVING</b></font> 子句通常是在一個 SQL 句子的最後。一個含有 <font color="#008000"><b>HAVING</b></font> 子句的 SQL 並不一定要包含 <font color="#008000"><b>GROUP BY</b></font> 子句。<font color="#008000"><b>HAVING</b></font> 的語法如下: </p> <p><font color="#008000"><b>SELECT "欄位1", SUM("欄位2") <br />FROM "表格名" <br />GROUP BY "欄位1" <br />HAVING (函數條件) </b></font></p> <p>請讀者注意: 如果被 SELECT 的只有函數欄, 那就不需要 <font color="#008000"><b>GROUP BY</b></font> 子句。 </p> <p>在我們 Store_Information 表格這個例子中, </p> <p><font size="2">Store_Information</font> 表格 <br /> <table class="content" border="1"><tbody> <tr> <td>store_name </td> <td>Sales </td> <td>Date </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$1500 </td> <td>Jan-05-1999 </td> </tr> <tr> <td>San Diego </td> <td align="right">$250 </td> <td>Jan-07-1999 </td> </tr> <tr> <td>Los Angeles </td> <td align="right">$300 </td> <td>Jan-08-1999 </td> </tr> <tr> <td>Boston </td> <td align="right">$700 </td> <td>Jan-08-1999 </td> </tr> </tbody></table> <br /></p> <p>若我們要找出 Sales 大於 $1,500 的 store_name,我們就鍵入, <br /></p> <p><font color="#0000ff"><b>SELECT store_name, SUM(sales) <br />FROM Store_Information <br />GROUP BY store_name <br />HAVING SUM(sales) > 1500</b></font> </p> <p><i>結果:</i> </p> <p> <table cellpadding="3" border="0"><tbody> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><u><b>store_name</b></u></font></td> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><u><b>SUM(Sales)</b></u></font><u></u></td> </tr> <tr> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><b>Los Angeles</b></font></td> <td><font face="Verdana, Arial, Helvetica, sans-serif" color="#4169e1" size="2"><center><b>$1800</b></center></font></td> </tr> </tbody></table></p> robbin0919http://www.blogger.com/profile/01411449160757693485noreply@blogger.com0