oracle數(shù)據(jù)庫學(xué)習(xí)筆記

1、1、decode函數(shù)：SELECT checkup_type, DECODE(blood_test_flag,Y,Yes,N,No,NULL,None,Invalid)FROM checkup;DECODE函數(shù)相當(dāng)于一條件語句(IF).它將輸入數(shù)值與函數(shù)中的參數(shù)列表相比較，根據(jù)輸入值返回一個對應(yīng)值。函數(shù)的參數(shù)列表是由若干數(shù)值及其對應(yīng)結(jié)果值組成的若干序偶形式。當(dāng)然，如果未能與任何一個實(shí)參序偶匹配成功，則函數(shù)也有默認(rèn)的返回值。區(qū)別于SQL的其它函數(shù)，DECODE函數(shù)還能識別和操作空值.其具體的語法格式如下： DECODE(input_value,value,result,value,result,

2、default_result);其中：input_value 試圖處理的數(shù)值。DECODE函數(shù)將該數(shù)值與一系列的序偶相比較，以決定最后的返回結(jié)果value 是一組成序偶的數(shù)值。如果輸入數(shù)值與之匹配成功，則相應(yīng)的結(jié)果將被返回。對應(yīng)一個空的返回值，可以使用關(guān)鍵字NULL于之對應(yīng)result 是一組成序偶的結(jié)果值default_result 未能與任何一序偶匹配成功時，函數(shù)返回的默認(rèn)值下面的例子說明了，如何讀取用戶CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的項(xiàng)目，作為DECODE函數(shù)的實(shí)參支持值。2、nvl函數(shù)的用法：如果你某個字段為空，但是你想讓這個字段顯示0nvl(字段

3、名,0)，就是當(dāng)你選出來的時候，這個字段雖然為空，但是顯示的是0，當(dāng)然這個0也可以換成其他東西，如：1，2，3NULL指的是空值，或者非法值。NVL (expr1, expr2)-expr1為NULL，返回expr2；不為NULL，返回expr1。注意兩者的類型要一致NVL2 (expr1, expr2, expr3) -expr1不為NULL，返回expr2；為NULL，返回expr3。expr2和expr3類型不同的話，expr3會轉(zhuǎn)換為expr2的類型NULLIF (expr1, expr2) -相等返回NULL，不等返回expr13、oracle的查詢必須是 select . from

4、 .成對出現(xiàn)：查詢單行的時候可以用dual代替，dual表在系統(tǒng)中只有一行；例如：獲取系統(tǒng)時間；select sysdate from dual4、oracle查詢結(jié)果多行用逗號拼接：SELECT WM_CONCAT(GOODSTYPENAME) FROM TB_SYS_PRODUCT_FORBIDGOODSTYPE fibLEFT JOIN TB_SYS_GOODSTYPE ty ON fib.goodstypeid=ty.goodstypeidWHERE ductid=ductid5、oracle遞歸獲取所有子或者父節(jié)點(diǎn)：pid為子級的父級id的字段名稱從Root往

5、樹末梢遞歸：獲取子級select * from tempstart with id=3 /*父級的id*/connect by prior id = pid /*pid 子級的父級id的字段名稱*/ 從末梢往樹ROOT遞歸：獲取父級select * from tempstart with id=3connect by id = prior pid /*pid子級的父級id的字段名稱*/如果需要顯示層次結(jié)構(gòu)select sys_connect_by_path(id,/),pid from tempstart with id=1connect by prior id = pidRg:SELECT

6、* from tb_base_netpoint tSTART WITH pointid=74852CONNECT BY PRIOR pointid=t.parentnetpoint6、oracle使用正則表達(dá)式：替換掉大寫字母：SELECT REGEXP_REPLACE(printNo,A-Z*,) AS ISSUENO FROM TB_BASE_PRINTORDERLISTOracle 10g支持正則表達(dá)式的四個新函數(shù)分別是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。特殊字符： 匹配輸入字符串的開始位置，在方括號表達(dá)式中使用

7、，此時它表示不接受該字符集合。$ 匹配輸入字符串的結(jié)尾位置。如果設(shè)置了 RegExp 對象的 Multiline 屬性，則 $ 也匹配 n 或 r。. 匹配除換行符 n之外的任何單字符。? 匹配前面的子表達(dá)式零次或一次。* 匹配前面的子表達(dá)式零次或多次。+ 匹配前面的子表達(dá)式一次或多次。( ) 標(biāo)記一個子表達(dá)式的開始和結(jié)束位置。 標(biāo)記一個中括號表達(dá)式。m,n 一個精確地出現(xiàn)次數(shù)范圍，m=出現(xiàn)次數(shù)=n，m表示出現(xiàn)m次，m,表示至少出現(xiàn)m次。| 指明兩項(xiàng)之間的一個選擇。例子(a-z+|0-9+)$表示所有小寫字母或數(shù)字組合成的字符串。num 匹配 num，其中 num 是一個正整數(shù)。對所獲取的匹配

8、的引用。正則表達(dá)式的一個很有用的特點(diǎn)是可以保存子表達(dá)式以后使用， 被稱為Backreferencing. 允許復(fù)雜的替換能力如調(diào)整一個模式到新的位置或者指示被代替的字符或者單詞的位置. 被匹配的子表達(dá)式存儲在臨時緩沖區(qū)中， 緩沖區(qū)從左到右編號, 通過數(shù)字符號訪問。 下面的例子列出了把名字 aa bb cc 變成cc, bb, aa.Select REGEXP_REPLACE(aa bb cc,(.*) (.*) (.*), 3, 2, 1) FROM dual；REGEXP_REPLACE(ELLENHILDISMITcc, bb, aa 轉(zhuǎn)義符。 字符簇： :alpha: 任何字母。:dig

9、it: 任何數(shù)字。:alnum: 任何字母和數(shù)字。:space: 任何白字符。:upper: 任何大寫字母。:lower: 任何小寫字母。:punct: 任何標(biāo)點(diǎn)符號。:xdigit: 任何16進(jìn)制的數(shù)字，相當(dāng)于0-9a-fA-F。 各種操作符的運(yùn)算優(yōu)先級轉(zhuǎn)義符(), (?:), (?=), 圓括號和方括號*, +, ?, n, n, n,m 限定符, $, anymetacharacter 位置和順序| “或”操作7、oracle替換字母：替換所有字母：REGEXP_REPLACE(issueno, A-Z*, )8、oracle新建存儲過程：create or replace proced

10、ure Test_Get_User(Iuserid in NUMBER,tt out varchar) isbegin select username into tt from tb_sys_user where userid=Iuserid; -dbms_output.put_line(tt=|tt); -打印結(jié)果end Test_Get_User;9、PLSQL添加新鏈接：1、/network/admin，在admin目錄中建立tnsnames.ora文件，內(nèi)容大致如下：demo1 = #oracle服務(wù)名 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

11、 (PROTOCOL = TCP)(HOST = 00)(PORT = 1521) #oracle服務(wù)器地址與端口 ) (CONNECT_DATA = (SERVICE_NAME = demo1) ) )2、客戶端配置：配置和移植工具Net Manager服務(wù)命名；10、substr的用法：TSG.SCHEDULEORDERNO要截取的字符串，從3開始截取,字符串的下標(biāo)從1開始;SUBSTR(TSG.SCHEDULEORDERNO, 3)11、遍歷所有數(shù)據(jù)：for lr in(SELECT * from TB_FIN_CREDITORDERWHERE 1=1AND to_

12、date(lr.startdate)=to_date(SYSDATE)AND state=8)loopend loop;跳出循環(huán)：exit when result=500;12、C#調(diào)用執(zhí)行sql插入數(shù)據(jù)返回序列號：string str_Sql = begin insert into testtab(name) values(test); select TESTTABSEQ.Currval into :ID from dual; end; ORAC.OracleCommand cmd= new ORAC.OracleCommand(str_Sql,this.oracleConnection1)

13、; ORAC.OracleParameter parm = new ORAC.OracleParameter(ID,ORAC.OracleType.Number); parm.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm); if(this.oracleConnection1.State = System.Data.ConnectionState.Closed) this.oracleConnection1.Open(); cmd.ExecuteNonQuery(); this.textBox1.Text = cm

14、d.Parameters0.Value.ToString();13、查詢表的所有分區(qū)：查詢表的所有分區(qū)，表名必須大寫select * from user_tab_partitions t where t.table_name = ABC ;14、查找字符串位置：INSTR（string,subString,position,ocurrence）查找字符串位置解釋：string：源字符串 subString：要查找的子字符串 position：查找的開始位置 ocurrence：源字符串中第幾次出現(xiàn)的子字符串For example:INSTR(CORPORATE FLOOR,OR, 3, 2)中

15、，源字符串為CORPORATE FLOOR, 目標(biāo)字符串為OR，起始位置為3，取第2個匹配項(xiàng)的位置；返回結(jié)果為 14 15、單據(jù)編號生成sql：單據(jù)編號加上前綴，加上每天日期， 每天從1開始生成，SELECT DD | (NVL(SUBSTR(MAX(TSG.SCHEDULEORDERNO), 3), TO_CHAR(SYSDATE, yyyymmdd) | 00000) + 1) FROM TB_SALE_SCHEDULEORDER TSG WHERE TSG.SCHEDULEORDERNO LIKE DD | TO_CHAR(SYSDATE, yyyymmdd) | %16、定義變量并輸出

16、：declare v_exit NUMBER;begin v_exit:=0;FOR i IN 0.100 LOOP v_exit:=v_exit+i;END LOOP;dbms_output.put_line(v_exit); end;17、提取的列值為NULL：問題：提取的列值為 NULL解決：有些列的值為NULL，用NVL（）轉(zhuǎn)換；18、調(diào)試存儲過程：存儲過程右鍵-Test；按F9；19、運(yùn)行存儲過程：-運(yùn)行存儲過程,有返回值declare v_a NUMBER; v_b VARCHAR2(500); begin TEST_UPDATE_CUSTOMERCREDIT(v_a,v_b);

17、dbms_output.put_line(v_a); dbms_output.put_line(v_b); end;20、整除：整除，不要四舍五入：TRUNC(5/3) - 121、新建作業(yè)：在DBMS_Jobs右鍵-新建：運(yùn)行：作業(yè)右鍵-Run可以select * from user_jobs;查到j(luò)ob的id； 然后exec dbms_job.remove(21); commit;就刪掉了select * from user_jobs; select * from dba_users; oracle中創(chuàng)建作業(yè) /*創(chuàng)建作業(yè)任務(wù)*/ DECLARE jobno NUMBER;BEGIN -每

18、15分鐘執(zhí)行一次 DBMS_JOB.submit(jobno,SP_DJ_KHXX;,SYSDATE,SYSDATE+15/1440); -定時晚上11：00執(zhí)行 DBMS_JOB.submit(jobno,SP_DJ_KHXX;,TRUNC(SYSDATE),TRUNC(SYSDATE)+1380/1440); -每天執(zhí)行一次 DBMS_JOB.submit(jobno,SP_DJ_KHXX;,TRUNC(SYSDATE),TRUNC(SYSDATE)+1); -提交 COMMIT;END;-查看任務(wù)編號SELECT * FROM USER_JOBS;BEGIN DBMS_JOB.run(任

19、務(wù)編號);END;-刪除JOBBEGIN DBMS_JOB.remove(任務(wù)編號);END;-終止任務(wù)BEGIN DBMS_JOB.broken(任務(wù)編號,FALSE); COMMIT;END;-一、創(chuàng)建一個存儲過程create or replace procedure MY_Procedureis -這里用到了游標(biāo) cursor cur is select * from users where length(name) 10 ;begin for user in cur loop insert into users_temp values(user.id,user.passwd); end

20、 loop;end;二、創(chuàng)建作業(yè)variable job_num number; begin dbms_job.submit(:job_num,MY_Procedure;,sysdate,sysdate+1); commit; end; 三、運(yùn)行作業(yè)begin dbms_job.run(:job_num); end; 四、查詢作業(yè)select priv_user,job,what from user_jobs;五、 刪除作業(yè) begin dbms_job.remove(:job_num); end;六、時間Sysdate1/1440 每1分鐘執(zhí)行一次Sysdate5/1440 每5分鐘執(zhí)行一次

21、trunc(sysdate+1)+2/24 下一天的2點(diǎn)（凌晨）執(zhí)行一次SYSDATE + 7 最后一次執(zhí)行的7天之后執(zhí)行SYSDATE + 1/48 每半個小時執(zhí)行一次NEXT_DAY(TRUNC(SYSDATE), MONDAY) + 15/24 每個禮拜一的下午3點(diǎn)NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, Q), 3),THURSDAY) 每個季度的第一個星期四22、日期說明：1、to_date(SYSDATE)得到系統(tǒng)今天的日期：2014/01/162、to_date(updatetime)得到的是日期：2014/01/16（updatetime是數(shù)據(jù)庫的字

22、段）3、前3天的日期：to_date(SYSDATE-3)4、獲取年、月：select to_char(paremStartSettlement,yyyy) from dual ;select to_char(paremStartSettlement,mm) from dual ;5、 /*每個月第一天*/; select to_char(add_months(last_day(sysdate)+1,0),yyyy/MM/dd) FirstDay FROM dual ; /*每個月最后一天*/; select to_char(add_months(last_day(sysdate),1),yy

23、yy/MM/dd) FirstDay FROM dual6、只獲取時間：select to_char(SPR.PLANARRIVETIME,hh24:mi:ss) AS PREARRIVETIME from dd ;7、增加時分秒：對當(dāng)前日期增加一個小時： SQL select sysdate, sysdate+numtodsinterval(1,hour) from dual ; 2010-10-14 21:38:19 2010-10-14 22:38:19對當(dāng)前日期增加50分種 SQL select sysdate, sysdate+numtodsinterval(50,minute) f

24、rom dual ; 2010-10-14 21:39:12 2010-10-14 22:29:12 對當(dāng)前日期增加45秒 SQL select sysdate, sysdate+numtodsinterval(45,second) from dual ;大于某天：t.createtimeto_date(2013/12/03,yyyy-MM-dd)查詢某一天：to_date(T.CREATETIME)=TO_DATE(2014/01/03, yyyy/MM/dd)在某段時間內(nèi): select * from up_date where update between to_date(2007-07

25、-07 00:00:00,yyyy-mm-dd hh24:mi:ss) and to_date(2007-09-07 00:00:00,yyyy-mm-dd hh24:mi:ss)返回系統(tǒng)日期,輸出 25-12月-09select sysdate from dual;轉(zhuǎn)換的格式： 表示 year 的：y 表示年的最后一位 、 yy 表示年的最后2位 、 yyy 表示年的最后3位 、 yyyy 用4位數(shù)表示年 表示month的： mm 用2位數(shù)字表示月 、 mon 用簡寫形式， 比如11月或者nov 、 month 用全稱， 比如11月或者november 表示day的：dd 表示當(dāng)月第幾天 、

26、 ddd 表示當(dāng)年第幾天 、 dy 當(dāng)周第幾天，簡寫， 比如星期五或者fri 、 day 當(dāng)周第幾天，全稱， 比如星期五或者friday 表示hour的：hh 2位數(shù)表示小時 12進(jìn)制、 hh24 2位數(shù)表示小時 24小時 表示minute的：mi 2位數(shù)表示分鐘 表示second的：ss 2位數(shù)表示秒 60進(jìn)制 表示季度的：q 一位數(shù) 表示季度 （1-4） 另外還有ww 用來表示當(dāng)年第幾周 w用來表示當(dāng)月第幾周。 24小時制下的時間范圍：00：00：00-23：59：5912小時制下的時間范圍：1：00：00-12：59：59 數(shù)字格式: 9 代表一個數(shù)字 0 強(qiáng)制顯示0 $ 放置一個$符

27、L 放置一個浮動本地貨幣符 . 顯示小數(shù)點(diǎn) , 顯示千位指示符 補(bǔ)充：當(dāng)前時間減去7分鐘的時間 select sysdate,sysdate - interval 7 MINUTE from dual; 當(dāng)前時間減去7小時的時間 select sysdate - interval 7 hour from dual; 當(dāng)前時間減去7天的時間 select sysdate - interval 7 day from dual; 當(dāng)前時間減去7月的時間 select sysdate,sysdate - interval 7 month from dual; 當(dāng)前時間減去7年的時間 select sy

28、sdate,sysdate - interval 7 year from dual; 時間間隔乘以一個數(shù)字 select sysdate,sysdate - 8*interval 7 hour from dual; 含義解釋： Dual偽列 Dual 是 Oracle中的一個實(shí)際存在的表，任何用戶均可讀取，常用在沒有目標(biāo)表的select語句塊中。 不同系統(tǒng)可能返回日期的格式不一樣。 返回當(dāng)前連接的用戶：select user from dual;常用的時間格式 在oracle中有 yyyy-mm-dd hh24:mi:ss 而在Java中有些區(qū)別 為yyyy-MM-dd HH:mm:ss 這點(diǎn)

29、還是經(jīng)常容易模糊的。 相信很多人都有過統(tǒng)計(jì)某些數(shù)據(jù)的經(jīng)歷，比如，要統(tǒng)計(jì)財(cái)務(wù)的情況，可能要按每年，每季度，每月，甚至每個星期來分別統(tǒng)計(jì)。那在oracle中應(yīng)該怎么來寫sql語句呢，這個時候Oracle的日期函數(shù)會給我們很多幫助。 常用日期型函數(shù) 1。Sysdate 當(dāng)前日期和時間SQL Select sysdate from dual;SYSDATE-21-6月 -052。Last_day 本月最后一天 SQL Select last_day(sysdate) from dual;LAST_DAY(S-30-6月 -053。Add_months(d,n) 當(dāng)前日期d后推n個月 用于從一個日期值增

30、加或減少一些月份 date_value:=add_months(date_value,number_of_months)SQL Select add_months(sysdate,2) from dual;ADD_MONTHS-21-8月 -054。Months_between(f,s) 日期f和s間相差月數(shù) SQL select months_between(sysdate,to_date(2005-11-12,yyyy-mm-dd)from dual;MONTHS_BETWEEN(SYSDATE,TO_DATE(2005-11-12,YYYY-MM-DD)- -4.6966741 5。NE

31、XT_DAY(d, day_of_week)返回由day_of_week命名的，在變量d指定的日期之后的第一個工作日的日期。參數(shù)day_of_week必須為該星期中的某一天。SQL SELECT next_day(to_date(20050620,YYYYMMDD),1) FROM dual;NEXT_DAY(T-26-6月 -056。current_date()返回當(dāng)前會話時區(qū)中的當(dāng)前日期 date_value:=current_date SQL column sessiontimezone for a15 SQL select sessiontimezone,current_date fr

32、om dual; SESSIONTIMEZONE CURRENT_DA - - +08:00 13-11月-03 SQL alter session set time_zone=-11:00 2 / 會話已更改。 SQL select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP - - -11:00 12-11月-03 068000 下午 -11:00 7。current_timestamp()以timestamp with time zone數(shù)據(jù)類型返回當(dāng)前會話

33、時區(qū)中的當(dāng)前日期SQL select current_timestamp from dual;CURRENT_TIMESTAMP-21-6月 -05 10.13.08.220589 上午 +08:008。dbtimezone()返回時區(qū)SQL select dbtimezone from dual;DBTIME-08:009。extract()找出日期或間隔值的字段值 date_value:=extract(date_field from datetime_value|interval_value) SQL select extract(month from sysdate) This Mon

34、th from dual;This Month- 6SQL select extract(year from add_months(sysdate,36) Years from dual; Years- 200810。localtimestamp()返回會話中的日期和時間 SQL select localtimestamp from dual;LOCALTIMESTAMP-21-6月 -05 55652 上午常用日期數(shù)據(jù)格式（該段為摘抄）Y或YY或YYY 年的最后一位，兩位或三位 Select to_char(sysdate,YYY) from dual； 002表示200

35、2年 SYEAR或YEAR SYEAR使公元前的年份前加一負(fù)號 Select to_char(sysdate,SYEAR) from dual； -1112表示公元前111 2年 Q 季度，13月為第一季度 Select to_char(sysdate,Q) from dual； 2表示第二季度 MM 月份數(shù) Select to_char(sysdate,MM) from dual； 12表示12月 RM 月份的羅馬表示 Select to_char(sysdate,RM) from dual； IV表示4月 Month 用9個字符長度表示的月份名 Select to_char(sysdate

36、,Month) from dual； May后跟6個空格表示5月 WW 當(dāng)年第幾周 Select to_char(sysdate,WW) from dual； 24表示2002年6月13日為第24周 W 本月第幾周 Select to_char(sysdate,W) from dual； 2002年10月1日為第1周 DDD 當(dāng)年第幾, 1月1日為001，2月1日為032 Select to_char(sysdate,DDD) from dual； 363 2002年1 2月2 9日為第363天 DD 當(dāng)月第幾天 Select to_char(sysdate,DD) from dual； 04

37、 10月4日為第4天 D 周內(nèi)第幾天 Select to_char(sysdate,D) from dual； 5 2002年3月14日為星期一 DY 周內(nèi)第幾天縮寫 Select to_char(sysdate,DY) from dual； SUN 2002年3月24日為星期天 HH或HH12 12進(jìn)制小時數(shù) Select to_char(sysdate,HH) from dual； 02 午夜2點(diǎn)過8分為02 HH24 24小時制 Select to_char(sysdate,HH24) from dual； 14 下午2點(diǎn)08分為14 MI 分鐘數(shù)(059) Select to_char

38、(sysdate,MI) from dual； 17下午4點(diǎn)17分 SS 秒數(shù)(059) Select to_char(sysdate,SS) from dual； 22 11點(diǎn)3分22秒 提示注意不要將MM格式用于分鐘(分鐘應(yīng)該使用MI)。MM是用于月份的格式，將它用于分鐘也能工作，但結(jié)果是錯誤的。 現(xiàn)在給出一些實(shí)踐后的用法：1。上月末天：SQL select to_char(add_months(last_day(sysdate),-1),yyyy-MM-dd) LastDay fromdual;LASTDAY-2005-05-312。上月今天SQL select to_char(add_

39、months(sysdate,-1),yyyy-MM-dd) PreToday from dual;PRETODAY-2005-05-213.上月首天SQL select to_char(add_months(last_day(sysdate)+1,-2),yyyy-MM-dd) firstDay from dual;FIRSTDAY-2005-05-014.按照每周進(jìn)行統(tǒng)計(jì)SQL select to_char(sysdate,ww) from dual group by to_char(sysdate,ww);TO-255。按照每月進(jìn)行統(tǒng)計(jì)SQL select to_char(sysdate

40、,mm) from dual group by to_char(sysdate,mm);TO-066。按照每季度進(jìn)行統(tǒng)計(jì)SQL select to_char(sysdate,q) from dual group by to_char(sysdate,q);T-27。按照每年進(jìn)行統(tǒng)計(jì)SQL select to_char(sysdate,yyyy) from dual group by to_char(sysdate,yyyy);TO_C-20058.要找到某月中所有周五的具體日期 select to_char(t.d,YY-MM-DD) from ( select trunc(sysdate,

41、MM)+rownum-1 as d from dba_objects where rownum alter session set nls_date_format=yyyy-mm-dd hh:mi:ss; 會話已更改。 SQL set serverout on SQL declare 2 DateValue date; 3 begin 4 select sysdate into DateValue from dual; 5 dbms_output.put_line(源時間:|to_char(DateValue); 6 dbms_output.put_line(源時間減1天:|to_char(D

42、ateValue-1); 7 dbms_output.put_line(源時間減1天1小時:|to_char(DateValue-1-1/24); 8 dbms_output.put_line(源時間減1天1小時1分:|to_char(DateValue-1-1/24-1/(24*60); 9 dbms_output.put_line(源時間減1天1小時1分1秒:|to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60); 10 end; 11 / 源時間:2003-12-29 11:53:41 源時間減1天:2003-12-28 11:53:41 源時間

43、減1天1小時:2003-12-28 10:53:41 源時間減1天1小時1分:2003-12-28 10:52:41 源時間減1天1小時1分1秒:2003-12-28 10:52:40 PL/SQL 過程已成功完成。在Oracle中實(shí)現(xiàn)時間相加處理- 名稱：Add_Times- 功能：返回d1與NewTime相加以后的結(jié)果，實(shí)現(xiàn)時間的相加- 說明：對于NewTime中的日期不予考慮- 日期：2004-12-07- 版本：1.0- 作者：Kevincreate or replace function Add_Times(d1 in date,NewTime in date) return dat

44、e is hh number; mm number; ss number; hours number; dResult date; begin - 下面依次取出時、分、秒 select to_number(to_char(NewTime,HH24) into hh from dual; select to_number(to_char(NewTime,MI) into mm from dual; select to_number(to_char(NewTime,SS) into ss from dual; - 換算出NewTime中小時總和，在一天的百分幾 hours := (hh + (mm

45、 / 60) + (ss / 3600)/ 24; - 得出時間相加后的結(jié)果 select d1 + hours into dResult from dual; return(dResult);end Add_Times;- 測試用例- select Add_Times(sysdate,to_date(2004-12-06 03:23:00,YYYY-MM-DD HH24:MI:SS) from dual在Oracle9i中計(jì)算時間差計(jì)算時間差是Oracle DATA數(shù)據(jù)類型的一個常見問題。Oracle支持日期計(jì)算，你可以創(chuàng)建諸如“日期1日期2”這樣的表達(dá)式來計(jì)算這兩個日期之間的時間差。 一旦

46、你發(fā)現(xiàn)了時間差異，你可以使用簡單的技巧來以天、小時、分鐘或者秒為單位來計(jì)算時間差。為了得到數(shù)據(jù)差，你必須選擇合適的時間度量單位，這樣就可以進(jìn)行數(shù)據(jù)格式隱藏。 使用完善復(fù)雜的轉(zhuǎn)換函數(shù)來轉(zhuǎn)換日期是一個誘惑，但是你會發(fā)現(xiàn)這不是最好的解決方法。 round(to_number(end-date-start_date)- 消逝的時間（以天為單位） round(to_number(end-date-start_date)*24)- 消逝的時間（以小時為單位） round(to_number(end-date-start_date)*1440)- 消逝的時間（以分鐘為單位） 顯示時間差的默認(rèn)模式是什么？為了

47、找到這個問題的答案，讓我們進(jìn)行一個簡單的SQL *Plus查詢。 SQL select sysdate-(sysdate-3) from dual; SYSDATE-(SYSDATE-3) - 3 這里，我們看到了Oracle使用天來作為消逝時間的單位，所以我們可以很容易的使用轉(zhuǎn)換函數(shù)來把它轉(zhuǎn)換成小時或者分鐘。然而，當(dāng)分鐘數(shù)不是一個整數(shù)時，我們就會遇到放置小數(shù)點(diǎn)的問題。 Select (sysdate-(sysdate-3.111)*1440 from dual; (SYSDATE-(SYSDATE-3.111)*1440 - 4479.83333 當(dāng)然，我們可以用ROUND函數(shù)（即取整函數(shù)）

48、來解決這個問題，但是要記住我們必須首先把DATE數(shù)據(jù)類型轉(zhuǎn)換成NUMBER數(shù)據(jù)類型。 Select round(to_number(sysdate-(sysdate-3.111)*1440) from dual; ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111)*1440) - 4480 我們可以用這些函數(shù)把一個消逝時間近似轉(zhuǎn)換成分鐘并把這個值寫入Oracle表格中。在這個例子里，我們有一個離線（logoff）系統(tǒng)級觸發(fā)機(jī)制來計(jì)算已經(jīng)開始的會話時間并把它放入一個Oracle STATSPACK USER_LOG擴(kuò)展表格之中。 Update perfstat.sta

49、ts$user_log set elapsed_minutes = round(to_number(logoff_time-logon_time)*1440) where user = user_id and elapsed_minutes is NULL; 查出任一年月所含的工作日CREATE OR REPLACE FUNCTION Get_WorkingDays( ny IN VARCHAR2) RETURN INTEGER IS/*-函數(shù)名稱：Get_WorkingDays中文名稱：求某一年月中共有多少工作日作者姓名: XINGPING編寫時間: 2004-05-22輸入?yún)?shù)：NY:所求包含工作日數(shù)的年月，格式為yyyymm,如200405返 回 值：整型值，包含的工作日數(shù)目。算法描述： 1).列舉出參數(shù)給出的年月中的每一天。這里使用了一個表（ljrq是我的庫中的一張表。這個表可以是有權(quán)訪問的、記錄條數(shù)至少為31的任意一張表或視圖）來構(gòu)造出某年月的每一天。 2).用這些日期和一個已知星期幾的日期相減(2001-12-30是星期天)，所得的差再對7求模。如果所求年月在2001-12-30以前，那么所得的差既是負(fù)數(shù)，求模后所得值范圍為大于-6,小于0，如-1表示星期六,故先將求模的結(jié)果加7，再求7的模. 3).過濾掉結(jié)果集中值為0和6