oracle学习笔记大全

1、decode函数：SELECTcheckup_type,DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,’Invalid’)FROMcheckup;DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值.其具体的语法格式如下：DECODE(input_value,value,result[,value,result…][,default_result]);其中：input_value试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，以决定最后的返回结果value是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果将被返回。对应一个空的返回值，可以使用关键字NULL于之对应result是一组成序偶的结果值default_result未能与任何一序偶匹配成功时，函数返回的默认值下面的例子说明了，如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。2、nvl函数的用法：如果你某个字段为空，但是你想让这个字段显示0nvl(字段名,0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是0，当然这个0也可以换成其他东西，如：1，2，3……NULL指的是空值，或者非法值。NVL(expr1,expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致NVL2(expr1,expr2,expr3)->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型NULLIF(expr1,expr2)->相等返回NULL，不等返回expr13、oracle的查询必须是select...from...成对出现：查询单行的时候可以用dual代替，dual表在系统中只有一行；例如：获取系统时间；selectsysdatefromdual4、oracle查询结果多行用逗号拼接：SELECTWM_CONCAT(GOODSTYPENAME)FROMTB_SYS_PRODUCT_FORBIDGOODSTYPEfibLEFTJOINTB_SYS_GOODSTYPEtyONfib.goodstypeid=ty.goodstypeidWHEREductid=ductid5、oracle递归获取所有子或者父节点：pid为子级的父级id的字段名称从Root往树末梢递归：获取子级select*fromtempstartwithid=3/*父级的id*/connectbypriorid=pid/*pid子级的父级id的字段名称*/从末梢往树ROOT递归：获取父级select*fromtempstartwithid=3connectbyid=priorpid/*pid子级的父级id的字段名称*/如果需要显示层次结构selectsys_connect_by_path(id,'/'),pidfromtempstartwithid=1connectbypriorid=pidRg:SELECT*fromtb_base_netpointtSTARTWITHpointid=74852CONNECTBYPRIORpointid=t.parentnetpoint6、oracle使用正则表达式：替换掉大写字母：SELECTREGEXP_REPLACE(printNo,'^[A-Z]*','')ASISSUENOFROMTB_BASE_PRINTORDERLISTOracle10g支持正则表达式的四个新函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。特殊字符：'^'匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。'$'匹配输入字符串的结尾位置。如果设置了RegExp对象的Multiline属性，则$也匹配'n'或'r'。'.'匹配除换行符n之外的任何单字符。'?'匹配前面的子表达式零次或一次。'*'匹配前面的子表达式零次或多次。'+'匹配前面的子表达式一次或多次。'()'标记一个子表达式的开始和结束位置。'[]'标记一个中括号表达式。'{m,n}'一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。'|'指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。num匹配num，其中num是一个正整数。对所获取的匹配的引用。正则表达式的一个很有用的特点是可以保存子表达式以后使用，被称为Backreferencing.允许复杂的替换能力如调整一个模式到新的位置或者指示被代替的字符或者单词的位置.被匹配的子表达式存储在临时缓冲区中，缓冲区从左到右编号,通过数字符号访问。下面的例子列出了把名字aabbcc变成cc,bb,aa.SelectREGEXP_REPLACE('aabbcc','(.*)(.*)(.*)','3,2,1')FROMdual；REGEXP_REPLACE('ELLENHILDISMITcc,bb,aa''转义符。字符簇：[[:alpha:]]任何字母。[[:digit:]]任何数字。[[:alnum:]]任何字母和数字。[[:space:]]任何白字符。[[:upper:]]任何大写字母。[[:lower:]]任何小写字母。[[:punct:]]任何标点符号。[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。各种操作符的运算优先级转义符(),(?:),(?=),[]圆括号和方括号*,+,?,{n},{n,},{n,m}限定符^,$,anymetacharacter位置和顺序|“或”操作7、oracle替换字母：替换所有字母：REGEXP_REPLACE(issueno,'^[A-Z]*','')8、oracle新建存储过程：createorreplaceprocedureTest_Get_User(IuseridinNUMBER,ttoutvarchar)isbeginselectusernameintottfromtb_sys_userwhereuserid=Iuserid;--dbms_output.put_line('tt='||tt);--打印结果endTest_Get_User;9、PLSQL添加新链接：1、<客户端目录>/network/admin，在admin目录中建立tnsnames.ora文件，内容大致如下：demo1=#oracle服务名(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=00)(PORT=1521))#oracle服务器地址与端口)(CONNECT_DATA=(SERVICE_NAME=demo1)))2、客户端配置：配置和移植工具——NetManager——服务命名；10、substr的用法：TSG.SCHEDULEORDERNO要截取的字符串，从3开始截取,字符串的下标从1开始;SUBSTR(TSG.SCHEDULEORDERNO,3)11、遍历所有数据：forlrin(SELECT*fromTB_FIN_CREDITORDERWHERE1=1ANDto_date(lr.startdate)=to_date(SYSDATE)ANDstate=8)loopendloop;跳出循环：exitwhenresult=500;12、C#调用执行sql插入数据返回序列号：stringstr_Sql=@"begininsertintotesttab(name)values('test');selectTESTTABSEQ.Currvalinto:IDfromdual;end;";ORAC.OracleCommandcmd=newORAC.OracleCommand(str_Sql,this.oracleConnection1);ORAC.OracleParameterparm=newORAC.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=cmd.Parameters[0].Value.ToString();13、查询表的所有分区：查询表的所有分区，表名必须大写select*fromuser_tab_partitionstwheret.table_name='ABC';14、查找字符串位置：INSTR（string,subString,position,ocurrence）查找字符串位置解释：string：源字符串subString：要查找的子字符串position：查找的开始位置ocurrence：源字符串中第几次出现的子字符串Forexample:INSTR('CORPORATEFLOOR','OR',3,2)中，源字符串为'CORPORATEFLOOR',目标字符串为'OR'，起始位置为3，取第2个匹配项的位置；返回结果为14'15、单据编号生成sql：单据编号加上前缀，加上每天日期，每天从1开始生成，SELECT'DD'||(NVL(SUBSTR(MAX(TSG.SCHEDULEORDERNO),3),TO_CHAR(SYSDATE,'yyyymmdd')||'00000')+1)FROMTB_SALE_SCHEDULEORDERTSGWHERETSG.SCHEDULEORDERNOLIKE'DD'||TO_CHAR(SYSDATE,'yyyymmdd')||'%'16、定义变量并输出：declarev_exitNUMBER;beginv_exit:=0;FORiIN0..100LOOPv_exit:=v_exit+i;ENDLOOP;dbms_output.put_line(v_exit);end;17、提取的列值为NULL：问题：提取的列值为NULL解决：有些列的值为NULL，用NVL（）转换；18、调试存储过程：存储过程右键--Test；按F9；19、运行存储过程：--运行存储过程,有返回值declarev_aNUMBER;v_bVARCHAR2(500);beginTEST_UPDATE_CUSTOMERCREDIT(v_a,v_b);dbms_output.put_line(v_a);dbms_output.put_line(v_b);end;20、整除：整除，不要四舍五入：TRUNC(5/3)----------121、新建作业：在DBMS_Jobs右键--新建：运行：作业右键--Run可以select*fromuser_jobs;查到job的id；然后execdbms_job.remove(21);commit;就删掉了select*fromuser_jobs;select*fromdba_users;oracle中创建作业/*创建作业任务*/DECLAREjobnoNUMBER;BEGIN--每15分钟执行一次DBMS_JOB.submit(jobno,'SP_DJ_KHXX;',SYSDATE,'SYSDATE+15/1440');--定时晚上11：00执行DBMS_JOB.submit(jobno,'SP_DJ_KHXX;',TRUNC(SYSDATE),'TRUNC(SYSDATE)+1380/1440');--每天执行一次DBMS_JOB.submit(jobno,'SP_DJ_KHXX;',TRUNC(SYSDATE),'TRUNC(SYSDATE)+1');--提交COMMIT;END;--查看任务编号SELECT*FROMUSER_JOBS;BEGINDBMS_JOB.run(任务编号);END;--删除JOBBEGINDBMS_JOB.remove(任务编号);END;--终止任务BEGINDBMS_JOB.broken(任务编号,FALSE);COMMIT;END;---------------------------------------------------------------------------------------------------------------------------------------------------------------一、创建一个存储过程createorreplaceprocedureMY_Procedureis--这里用到了游标cursorcurisselect*fromuserswherelength(name)>10;beginforuserincurloopinsertintousers_tempvalues(user.id,user.passwd);endloop;end;二、创建作业variablejob_numnumber;begindbms_job.submit(:job_num,'MY_Procedure;',sysdate,'sysdate+1');commit;end;三、运行作业begindbms_job.run(:job_num);end;四、查询作业selectpriv_user,job,whatfromuser_jobs;五、删除作业begindbms_job.remove(:job_num);end;六、时间Sysdate＋1/1440每1分钟执行一次Sysdate＋5/1440每5分钟执行一次trunc(sysdate+1)+2/24下一天的2点（凌晨）执行一次'SYSDATE+7'最后一次执行的7天之后执行'SYSDATE+1/48'每半个小时执行一次'NEXT_DAY(TRUNC(SYSDATE),''MONDAY'')+15/24'每个礼拜一的下午3点'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,''Q''),3),''THURSDAY'')'每个季度的第一个星期四

22、日期说明：1、to_date(SYSDATE)得到系统今天的日期：2014/01/162、to_date(updatetime)得到的是日期：2014/01/16（updatetime是数据库的字段）3、前3天的日期：to_date(SYSDATE-3)4、获取年、月：selectto_char(paremStartSettlement,'yyyy')fromdual;selectto_char(paremStartSettlement,'mm')fromdual;5、/*每个月第一天*/;selectto_char(add_months(last_day(sysdate)+1,0),'yyyy/MM/dd')FirstDayFROMdual;/*每个月最后一天*/;selectto_char(add_months(last_day(sysdate),1),'yyyy/MM/dd')FirstDayFROMdual6、只获取时间：selectto_char(SPR.PLANARRIVETIME,'hh24:mi:ss')ASPREARRIVETIMEfromdd;7、增加时分秒：对当前日期增加一个小时：SQL>selectsysdate,sysdate+numtodsinterval(1,’hour’)fromdual;2010-10-1421:38:192010-10-1422:38:19对当前日期增加50分种SQL>selectsysdate,sysdate+numtodsinterval(50,’minute’)fromdual;2010-10-1421:39:122010-10-1422:29:12对当前日期增加45秒SQL>selectsysdate,sysdate+numtodsinterval(45,’second’)fromdual;大于某天：t.createtime>to_date('2013/12/03','yyyy-MM-dd')查询某一天：to_date(T.CREATETIME)=TO_DATE('2014/01/03','yyyy/MM/dd')在某段时间内:select*fromup_datewhereupdatebetweento_date('2007-07-0700:00:00','yyyy-mm-ddhh24:mi:ss')andto_date('2007-09-0700:00:00','yyyy-mm-ddhh24:mi:ss')返回系统日期,输出25-12月-09selectsysdatefromdual;转换的格式：表示year的：y表示年的最后一位、yy表示年的最后2位、yyy表示年的最后3位、yyyy用4位数表示年表示month的：mm用2位数字表示月、mon用简写形式，比如11月或者nov、month用全称，比如11月或者november表示day的：dd表示当月第几天、ddd表示当年第几天、dy当周第几天，简写，比如星期五或者fri、day当周第几天，全称，比如星期五或者friday表示hour的：hh2位数表示小时12进制、hh242位数表示小时24小时表示minute的：mi2位数表示分钟表示second的：ss2位数表示秒60进制表示季度的：q一位数表示季度（1-4）另外还有ww用来表示当年第几周w用来表示当月第几周。24小时制下的时间范围：00：00：00-23：59：5912小时制下的时间范围：1：00：00-12：59：59数字格式:9代表一个数字0强制显示0$放置一个$符L放置一个浮动本地货币符.显示小数点,显示千位指示符补充：当前时间减去7分钟的时间selectsysdate,sysdate-interval'7'MINUTEfromdual;当前时间减去7小时的时间selectsysdate-interval'7'hourfromdual;当前时间减去7天的时间selectsysdate-interval'7'dayfromdual;当前时间减去7月的时间selectsysdate,sysdate-interval'7'monthfromdual;当前时间减去7年的时间selectsysdate,sysdate-interval'7'yearfromdual;时间间隔乘以一个数字selectsysdate,sysdate-8*interval'7'hourfromdual;含义解释：Dual伪列Dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select语句块中。不同系统可能返回日期的格式不一样。返回当前连接的用户：selectuserfromdual;常用的时间格式在oracle中有yyyy-mm-ddhh24:mi:ss而在Java中有些区别为yyyy-MM-ddHH:mm:ss这点还是经常容易模糊的。相信很多人都有过统计某些数据的经历，比如，要统计财务的情况，可能要按每年，每季度，每月，甚至每个星期来分别统计。那在oracle中应该怎么来写sql语句呢，这个时候Oracle的日期函数会给我们很多帮助。常用日期型函数1。Sysdate当前日期和时间SQL>Selectsysdatefromdual;SYSDATE----------21-6月-052。Last_day本月最后一天SQL>Selectlast_day(sysdate)fromdual;LAST_DAY(S----------30-6月-053。Add_months(d,n)当前日期d后推n个月用于从一个日期值增加或减少一些月份date_value:=add_months(date_value,number_of_months)SQL>Selectadd_months(sysdate,2)fromdual;ADD_MONTHS----------21-8月-054。Months_between(f,s)日期f和s间相差月数SQL>selectmonths_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))fromdual;MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-11-12','YYYY-MM-DD'))-----------------------------------------------------------4.69667415。NEXT_DAY(d,day_of_week)返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。SQL>SELECTnext_day(to_date('20050620','YYYYMMDD'),1)FROMdual;NEXT_DAY(T----------26-6月-056。current_date()返回当前会话时区中的当前日期date_value:=current_dateSQL>columnsessiontimezonefora15SQL>selectsessiontimezone,current_datefromdual;SESSIONTIMEZONECURRENT_DA-------------------------+08:0013-11月-03SQL>altersessionsettime_zone='-11:00'2/会话已更改。SQL>selectsessiontimezone,current_timestampfromdual;SESSIONTIMEZONECURRENT_TIMESTAMP----------------------------------------------------11:0012-11月-03068000下午-11:007。current_timestamp()以timestampwithtimezone数据类型返回当前会话时区中的当前日期SQL>selectcurrent_timestampfromdual;CURRENT_TIMESTAMP---------------------------------------------------------------------------21-6月-0510.13.08.220589上午+08:008。dbtimezone()返回时区SQL>selectdbtimezonefromdual;DBTIME-------08:009。extract()找出日期或间隔值的字段值date_value:=extract(date_fieldfrom[datetime_value|interval_value])SQL>selectextract(monthfromsysdate)"ThisMonth"fromdual;ThisMonth----------6SQL>selectextract(yearfromadd_months(sysdate,36))"Years"fromdual;Years----------200810。localtimestamp()返回会话中的日期和时间SQL>selectlocaltimestampfromdual;LOCALTIMESTAMP---------------------------------------------------------------------------21-6月-0555652上午常用日期数据格式（该段为摘抄）Y或YY或YYY年的最后一位，两位或三位Selectto_char(sysdate,’YYY’)fromdual；002表示2002年SYEAR或YEARSYEAR使公元前的年份前加一负号Selectto_char(sysdate,’SYEAR’)fromdual；-1112表示公元前1112年Q季度，1～3月为第一季度Selectto_char(sysdate,’Q’)fromdual；2表示第二季度①MM月份数Selectto_char(sysdate,’MM’)fromdual；12表示12月RM月份的罗马表示Selectto_char(sysdate,’RM’)fromdual；IV表示4月Month用9个字符长度表示的月份名Selectto_char(sysdate,’Month’)fromdual；May后跟6个空格表示5月WW当年第几周Selectto_char(sysdate,’WW’)fromdual；24表示2002年6月13日为第24周W本月第几周Selectto_char(sysdate,’W’)fromdual；2002年10月1日为第1周DDD当年第几,1月1日为001，2月1日为032Selectto_char(sysdate,’DDD’)fromdual；3632002年12月29日为第363天DD当月第几天Selectto_char(sysdate,’DD’)fromdual；0410月4日为第4天D周内第几天Selectto_char(sysdate,’D’)fromdual；52002年3月14日为星期一DY周内第几天缩写Selectto_char(sysdate,’DY’)fromdual；SUN2002年3月24日为星期天HH或HH1212进制小时数Selectto_char(sysdate,’HH’)fromdual；02午夜2点过8分为02HH2424小时制Selectto_char(sysdate,’HH24’)fromdual；14下午2点08分为14MI分钟数(0～59)Selectto_char(sysdate,’MI’)fromdual；17下午4点17分SS秒数(0～59)Selectto_char(sysdate,’SS’)fromdual；2211点3分22秒提示注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。现在给出一些实践后的用法：1。上月末天：SQL>selectto_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd')LastDayfromdual;LASTDAY----------2005-05-312。上月今天SQL>selectto_char(add_months(sysdate,-1),'yyyy-MM-dd')PreTodayfromdual;PRETODAY----------2005-05-213.上月首天SQL>selectto_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd')firstDayfromdual;FIRSTDAY----------2005-05-014.按照每周进行统计SQL>selectto_char(sysdate,'ww')fromdualgroupbyto_char(sysdate,'ww');TO--255。按照每月进行统计SQL>selectto_char(sysdate,'mm')fromdualgroupbyto_char(sysdate,'mm');TO--066。按照每季度进行统计SQL>selectto_char(sysdate,'q')fromdualgroupbyto_char(sysdate,'q');T-27。按照每年进行统计SQL>selectto_char(sysdate,'yyyy')fromdualgroupbyto_char(sysdate,'yyyy');TO_C----20058.要找到某月中所有周五的具体日期selectto_char(t.d,'YY-MM-DD')from(selecttrunc(sysdate,'MM')+rownum-1asdfromdba_objectswhererownum<32)twhereto_char(t.d,'MM')=to_char(sysdate,'MM')--找出当前月份的周五的日期andtrim(to_char(t.d,'Day'))='星期五'--------03-05-0203-05-0903-05-1603-05-2303-05-30如果把whereto_char(t.d,'MM')=to_char(sysdate,'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。9.oracle中时间运算内容如下：1、oracle支持对日期进行运算2、日期运算时是以天为单位进行的3、当需要以分秒等更小的单位算值时，按时间进制进行转换即可4、进行时间进制转换时注意加括号，否则会出问题SQL>altersessionsetnls_date_format='yyyy-mm-ddhh:mi:ss';会话已更改。SQL>setserveroutonSQL>declare2DateValuedate;3begin4selectsysdateintoDateValuefromdual;5dbms_output.put_line('源时间:'||to_char(DateValue));6dbms_output.put_line('源时间减1天:'||to_char(DateValue-1));7dbms_output.put_line('源时间减1天1小时:'||to_char(DateValue-1-1/24));8dbms_output.put_line('源时间减1天1小时1分:'||to_char(DateValue-1-1/24-1/(24*60)));9dbms_output.put_line('源时间减1天1小时1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*60)));10end;11/源时间:2003-12-2911:53:41源时间减1天:2003-12-2811:53:41源时间减1天1小时:2003-12-2810:53:41源时间减1天1小时1分:2003-12-2810:52:41源时间减1天1小时1分1秒:2003-12-2810:52:40PL/SQL过程已成功完成。在Oracle中实现时间相加处理--名称：Add_Times--功能：返回d1与NewTime相加以后的结果，实现时间的相加--说明：对于NewTime中的日期不予考虑--日期：2004-12-07--版本：1.0--作者：KevincreateorreplacefunctionAdd_Times(d1indate,NewTimeindate)returndateishhnumber;mmnumber;ssnumber;hoursnumber;dResultdate;begin--下面依次取出时、分、秒selectto_number(to_char(NewTime,'HH24'))intohhfromdual;selectto_number(to_char(NewTime,'MI'))intommfromdual;selectto_number(to_char(NewTime,'SS'))intossfromdual;--换算出NewTime中小时总和，在一天的百分几hours:=(hh+(mm/60)+(ss/3600))/24;--得出时间相加后的结果selectd1+hoursintodResultfromdual;return(dResult);endAdd_Times;--测试用例--selectAdd_Times(sysdate,to_date('2004-12-0603:23:00','YYYY-MM-DDHH24:MI:SS'))fromdual在Oracle9i中计算时间差计算时间差是OracleDATA数据类型的一个常见问题。Oracle支持日期计算，你可以创建诸如“日期1－日期2”这样的表达式来计算这两个日期之间的时间差。一旦你发现了时间差异，你可以使用简单的技巧来以天、小时、分钟或者秒为单位来计算时间差。为了得到数据差，你必须选择合适的时间度量单位，这样就可以进行数据格式隐藏。使用完善复杂的转换函数来转换日期是一个诱惑，但是你会发现这不是最好的解决方法。round(to_number(end-date-start_date))-消逝的时间（以天为单位）round(to_number(end-date-start_date)*24)-消逝的时间（以小时为单位）round(to_number(end-date-start_date)*1440)-消逝的时间（以分钟为单位）显示时间差的默认模式是什么？为了找到这个问题的答案，让我们进行一个简单的SQL*Plus查询。SQL>selectsysdate-(sysdate-3)fromdual;SYSDATE-(SYSDATE-3)-------------------3这里，我们看到了Oracle使用天来作为消逝时间的单位，所以我们可以很容易的使用转换函数来把它转换成小时或者分钟。然而，当分钟数不是一个整数时，我们就会遇到放置小数点的问题。Select(sysdate-(sysdate-3.111))*1440fromdual;(SYSDATE-(SYSDATE-3.111))*1440------------------------------4479.83333当然，我们可以用ROUND函数（即取整函数）来解决这个问题，但是要记住我们必须首先把DATE数据类型转换成NUMBER数据类型。Selectround(to_number(sysdate-(sysdate-3.111))*1440)fromdual;ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)----------------------------------------------4480我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入Oracle表格中。在这个例子里，我们有一个离线（logoff）系统级触发机制来计算已经开始的会话时间并把它放入一个OracleSTATSPACKUSER_LOG扩展表格之中。Updateperfstat.stats$user_logsetelapsed_minutes=round(to_number(logoff_time-logon_time)*1440)whereuser=user_idandelapsed_minutesisNULL;查出任一年月所含的工作日CREATEORREPLACEFUNCTIONGet_WorkingDays(nyINVARCHAR2)RETURNINTEGERIS/*------------------------------------------------------------------------------------------函数名称：Get_WorkingDays中文名称：求某一年月中共有多少工作日作者姓名:XINGPING编写时间:2004-05-22输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405返回值：整型值，包含的工作日数目。算法描述：1).列举出参数给出的年月中的每一天。这里使用了一个表（ljrq是我的库中的一张表。这个表可以是有权访问的、记录条数至少为31的任意一张表或视图）来构造出某年月的每一天。2).用这些日期和一个已知星期几的日期相减(2001-12-30是星期天)，所得的差再对7求模。如果所求年月在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模.3).过滤掉结果集中值为0和6的元素，然后求count,所得即为工作日数目。-------------------------------------------------------------------------------------------------*/ResultINTEGER;BEGINSELECTCOUNT(*)INTOResultFROM(SELECTMOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7)weekdayFROM(SELECTto_date(ny||t.dd,'yyyymmdd')rqFROM(SELECTsubstr(100+ROWNUM,2,2)ddFROMljrqzWHERERownum<=31)tWHEREto_date(ny||t.dd,'yyyymmdd')BETWEENto_date(ny,'yyyymm')ANDlast_day(to_date(ny,'yyyymm')))q)aWHEREa.weekdayNOTIN(0,6);RETURNResult;ENDGet_WorkingDays;______________________________________还有一个版本CREATEORREPLACEFUNCTIONGet_WorkingDays(nyINVARCHAR2)RETURNINTEGERIS/*-----------------------------------------------------------------------------------------函数名称：Get_WorkingDays中文名称：求某一年月中共有多少工作日作者姓名:XINGPING编写时间:2004-05-23输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405返回值：整型值，包含的工作日数目。算法描述：使用Last_day函数计算出参数所给年月共包含多少天，根据这个值来构造一个循环。在这个循环中先求这个月的每一天与一个已知是星期天的日期(2001-12-30是星期天)的差，所得的差再对7求模。如果所求日期在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模.如过所得值不等于0和6（即不是星期六和星期天），则算一个工作日。----------------------------------------------------------------------------------------*/ResultINTEGER:=0;mytsINTEGER;--所给年月的天数sctsINTEGER;--某天距2001-12-30所差的天数rqDATE;djtINTEGER:=1;--BEGINmyts:=to_char(last_day(to_date(ny,'yyyymm')),'dd');LOOPrq:=TO_date(ny||substr(100+djt,2),'yyyymmdd');scts:=rq-to_date('2001-12-30','yyyy-mm-dd');IFMOD(MOD(scts,7)+7,7)NOTIN(0,6)THENResult:=Result+1;ENDIF;djt:=djt+1;EXITWHENdjt>myts;ENDLOOP;RETURNResult;ENDGet_WorkingDays;以上两个版本的比较第一个版本一条SQL语句就可以得出结果，不需要编程就可以达到目的。但需要使用任意一张有权访问的、记录条数至少为31的一张表或视图。第二个版本需要编程，但不需要表或者视图。这两个版本都还存在需要完善的地方，即没有考虑节日，如五一、十一、元旦、春节这些节假期都没有去除。这些节假日应该维护成一张表，然后通过查表来去除这些节假日。

23、ORACLE获取汉字首字母：建立一个函数：CREATEORREPLACEFUNCTIONF_PINYIN(P_NAMEINVARCHAR2)RETURNVARCHAR2ASV_COMPAREVARCHAR2(100);V_RETURNVARCHAR2(4000);FUNCTIONF_NLSSORT(P_WORDINVARCHAR2)RETURNVARCHAR2ASBEGINRETURNNLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M');END;BEGINFORIIN1..LENGTH(P_NAME)LOOPV_COMPARE:=F_NLSSORT(SUBSTR(P_NAME,I,1));IFV_COMPARE>=F_NLSSORT('吖')ANDV_COMPARE<=F_NLSSORT('驁')THENV_RETURN:=V_RETURN||'A';ELSIFV_COMPARE>=F_NLSSORT('八')ANDV_COMPARE<=F_NLSSORT('簿')THEN