oracle复习整理(一)//创建空间CREATETABLESPACE"ISTAUDIT"LOGGINGDATAFILE'E:\ORACLE\ORADATA\ISTAUDIT\ISTAUDIT.ora'SIZE2048MAUTOEXTENDONNEXT1024MMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;createusercamsidentifiedbycamsdefaulttablespacemagl;grantresource,connect,dbatocams;dropusercamscascade--CreatetheusercreateuserCAMSidentifiedby"CAMS"defaulttablespacemagltemporarytablespaceTEMPprofileDEFAULT;--Grant/RevokeroleprivilegesgrantconnecttoCAMSwithadminoption;grantdbatoCAMSwithadminoption;grantresourcetoCAMSwithadminoption;--Grant/RevokesystemprivilegesgrantalteranytabletoCAMS;grantcreateanytabletoCAMS;grantcreateanyviewtoCAMSwithadminoption;grantcreatesessiontoCAMSwithadminoption;grantdeleteanytabletoCAMS;grantselectanytabletoCAMS;grantunlimitedtablespacetoCAMSwithadminoption;grantupdateanytabletoCAMS;createtablespacetestdatafile'c:\oracle\oradata\orcl9\test.dbf'size50Mdefaultstorage(initial500KNext500Kminextents1maxextentsunlimitedpctincrease0);//创建用户createuserlxgidentifiedbylxgdefaulttablespacetest;//授权 grantresource,connect,dbatotest;//删除
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
空间droptablespace"空间名"includingcontentsanddatafiles//删除用户dropuser"lxg"cascade//增加表空间altertablespacechinawateradddatafile'c:\oracle\oradata\orcl9\ADDCHINAWATER.dbf'size200M//查询表空间对应的数据文件(位置,大小...)selecta.name,b.namefromv$tablespacea,v$datafilebwherea.ts#=b.ts#;//修改表空间为自动增长alterdatabasedatafile'E:\ORACLE\ORADATA\mydb.DBF'autoextendonnext20m//修改表空间大小alterdatabasedatafile'E:\ORACLE\ORADATA\mydb.DBF'resize100m//创建用户createuseruserNameidentifiedbypassword;创建用户userName,密码为password2给用户授权grantdbatolxg;--授予DBA权限grantunlimitedtablespacetolxg;--授予不限制的表空间grantselectanytabletolxg;--授予查询任何表grantselectanydictionarytolxg;--授予查询任何字典数据导出:1将数据库TEST完全导出,用户名system密码manager导出到D:\daochu.dmp中expsystem/manager@TESTfile=d:\daochu.dmpfull=y2将数据库中system用户与sys用户的表导出expsystem/manager@TESTfile=d:\daochu.dmpowner=(system,sys)3将数据库中的表inner_notify、notify_staff_relat导出expaichannel/aichannel@TESTDB2file=d:\data\newsmgnt.dmptables=(inner_notify,notify_staff_relat)4将数据库中的表table1中的字段filed1以"00"打头的数据导出expsystem/manager@TESTfile=d:\daochu.dmptables=(table1)query=\"wherefiled1like'00%'\"上面是常用的导出,对于压缩,既用winzip把dmp文件可以很好的压缩。也可以在上面命令后面加上compress=y来实现。数据的导入:1将D:\daochu.dmp中的数据导入TEST数据库中。impsystem/manager@TESTfile=d:\daochu.dmpimpaichannel/aichannel@HUSTfull=yfile=file=d:\data\newsmgnt.dmpignore=y上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。在后面加上ignore=y就可以了。2将d:\daochu.dmp中的表table1导入impsystem/manager@TESTfile=d:\daochu.dmptables=(table1)基本上上面的导入导出够用了。不少情况要先是将表彻底删除,然后导入。注意:操作者要有足够的权限,权限不够它会提示。数据库时可以连上的。可以用tnspingTEST来获得数据库TEST能否连上。附录一:给用户增加导入数据权限的操作第一,启动sql*puls第二,以system/manager登陆第三,createuser用户名IDENTIFIEDBY密码(如果已经创建过用户,这步可以省略)第四,GRANTCREATEUSER,DROPUSER,ALTERUSER,CREATEANYVIEW,DROPANYVIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATESESSIONTO用户名字第五,运行-cmd-进入dmp文件所在的目录,impuserid=system/managerfull=yfile=*.dmp或者impuserid=system/managerfull=yfile=filename.dmp执行示例:F:\Work\Oracle_Data\backup>impuserid=test/testfull=yfile=inner_notify.dmp屏幕显示Import:Release8.1.7.0.0-Productionon星期四2月1616:50:052006(c)Copyright2000OracleCorporation.Allrightsreserved.连接到:Oracle8iEnterpriseEditionRelease8.1.7.0.0-ProductionWiththePartitioningoptionJServerRelease8.1.7.0.0-Production经由常規来路径导出由EXPORT:V08.01.07创建的文件已经完成ZHS16GBK字符集和ZHS16GBKNCHAR字符集中的导入导出服务器使UTF8NCHAR字符集(可能的ncharset转换).正在将AICHANNEL的对象导入到AICHANNEL..正在导入表"INNER_NOTIFY"4行被导入准备启用约束条件...成功终止导入,但出现警告。附录二:Oracle不允许直接改变表的拥有者,利用Export/Import可以达到这一目的.先建立import9.par,然后,使用时命令如下:impparfile=/filepath/import9.par例import9.par内容如下:FROMUSER=TGPMSTOUSER=TGPMS2(注:把表的拥有者由FROMUSER改为TOUSER,FROMUSER和TOUSER的用户可以不同)ROWS=YINDEXES=YGRANTS=YCONSTRAINTS=YBUFFER=409600file==/backup/ctgpc_20030623.dmplog==/backup/import_20030623.logsqlplus命令和sql语句的区分分号desc登陆sqlplusconn账号/口令网络服务名connsys/oracleassysdba登陆isqlplushttp//127.0.0.1:5560/isqlplus登陆emhttp//127.0.0.1:1158/isqlplus网络服务名的创建两种方式:编辑文件,图形方式编辑文件:ORCL=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=china-08edeb268)(PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=orcl) ))图形方式:Oracle-->配置和移植工具-->NetManager-->服务命名。注释:单行注释--多行注释给一个表加注释:commentontableempis'emp';给一个列加注释:commentoncolumnemp.enameis'ename';查询对表或者列的注释:select*fromuser_tab_comments/user_col_comments表空间:可不可以低于1M?可以环境变量Oracle_Home: e:\oracle\product\10.2.0\db_1指的是哪个目录Oracle_base: e:\oracle\product\10.2.0sql语句的书写规则:不分大小写,不一定必须写在一行,分行只是为了提高可读性SQL语句尽量写得一致是因为在Oracle内部在执行SQL语句之前要先将SQL语句编译到执行
计划
项目进度计划表范例计划下载计划下载计划下载课程教学计划下载
树中如果在计划树当中可以找到一致的SQL语句则不需要再将其编译到执行计划树当中,可以直接执行,提高效率列的别名不可以用单引号双引号用在有空格和特殊字符的情况在sqlplus中执行脚本@/startDQL:selectDML:insertdeleteupdateDDL:createalterdropDCL:grantrevoke数据类型:char固定长度 如果在数据库中将char的长度定位5,那么你输入一个字符,测其长度依旧为5varchar2可变长度number类型的精度(5,2)小数部分过长可以四舍五入,整数部分必须符合精度,例如number(5,2)123.456可以正常插入但是1234.5则不能插入nls_session_parameters数据字典可以查看系统当前默认的日期,货币格式nls_database_parametersrownum和rowidrownum是select之后的结果的行号,可以接受<=任何值的条件和=1的条件rowid是物理编号是不可以重复的空值null的判断:isnull,isnotnull;null参与运算的结果一定是nullwhere条件中的like关键字用在字符串的匹配查询,通配符:%任意长度任意字符_一个任意字符转义使用escape数值型,字符型,日期型的转换函数数值型:靠上取整,靠下取整,截断trunc和四舍五入roundsqlplus中的次幂:3**2=9字符型:截取字串搜索字符串左填充,右填充连接字符串substr下标从一开始instr下标从一开始concat(x,y)initcap(x)每个字母首字母大写nvl(x,value)空值判断nvl2(x,value1,value2)空值判断lpad日期型:months_between next_dayto_charto_date日期的四舍五入和截断2009-7-6按月截断结果7-1round(x,'格式')日期取整为最近的一天trunc(x,'格式')截断MM按月截断为当月的第一天to_char/to_dateYYYY/YY/YEAR/YearMM/MONTH/Month/MON/MonWW一年中的第几周/w本月中的第几周DDD本年/DD本月/D本周HH24/HHMI分钟SS秒聚合函数:用在groupbymaxcountmin空值在聚合函数中的运算:不计算:特例:count(*)不管有无空值,只取行数内联视图groupby字句:groupby字句限制了select中的输出项 小结where和havingwhere是用来过滤行的,having是用来过滤组的层次查询,加了where字句是用来过滤行的,如果是过滤分支,则把过滤条件放在连接子句上DML:insertupdatedelete事务的开始和结束(rollbackcommitexit关闭会话,系统崩溃)事务开始在当前会话的第一条DML语句delete和truncatetruncate是一条DDL语句,自动提交delete不提交表的链接:内连接,外连接(左,右,全)left(right)join笛卡尔积:表没有链接条件自连接自然链接:不需要指定链接条件using字句:放共有列,同名,同类型等值链接,不等值链接三张表链接至少需要两个链接条件selectename,dnamefromdeptnaturaljoinemp向多表插入数据insertall如果两个表都满足,则两张表都插入数据insertfirst如果两个表都满足,则总是插入第一个满足条件的表层次查询中可以从上到下,也可以从下往上selectename,level fromemp where条件过滤节点 startwithmgrisnull --从一句开始查询 connectbymgr=priorempno and条件过滤分支查询结果为:KING 1JONES 2SCOTT 3ADAMS 4FORD 3SMITH 4BLAKE 2ALLEN 3WARD 3MARTIN 3TURNER 3JAMES 3CLARK 2MILLER 3DDL创建表:约束:主键,外键,非空,唯独,check非空只能定义在列级外键在表级和列级是有区别的创建用户,需要分配配额才可以插入数据指明默认的表空间defaulttablespace必须显式的指明表空间的配额回收站droptable后加purge不会添加到回收站清空回收站命令purgerecyclebin恢复回收站中的表:connsys/oracleassysbad;grantexecuteondbms_flashbacktoscott;?altertableempenablerowmovement;execdbms_flashback.enable_at_time();--开启回闪execdbms_flashback.disable;altertable可以修改列modify,追加列add,删除列dropcolumn,添加约束addconstraint,删除约束dropconstraintexist:如果内层的select没有查到数据行,则外层的查询也没有数据行权限:grant权限/角色to用户级联的授予系统权限,在回收的时候不可以级联回收级联的赋予系统权限with级联的赋予对象权限withselect*fromrole_sys_privs查看角色系统权限select*fromrole_tab_privs查看角色对象权限系统权限和对象权限的区别,有没有on关键字TCL:commitrollbacksavepoint视图:对简单的视图可以进行dml操作,复杂的视图通过insteadof触发器进行dml操作视图的好处plsql块的结构:declare变量名变量类型notnull/defaultbegin--必须exceptionend;常量的声明类型:标量:numberchardatevarchar2 引用类型:复合类型:集合索引表,嵌套表,数组标量中的boolean不作为列存储条件ifelsifelseendif;caseend循环loopexitwhenendloop;whileloopendloop;foriin1..9loopendloop;跳转:goto 可以跳出循环异常定义的方式:ifconditionthenraisemyexception在捕获异常的exception的时候:whenmyexceptionthen ********;用一个预定义的:raise_application_error(code,message,keep_errors)其中code为-20001~-20999在需要获取这个code的时候用sqlcode message是需要抛出的异常的信息在或许这个信息的时候用sqlerrm keep_errors为一个boolean类型,为true则表示新产生的错误将追加到错误列表中, 为false则替换当前已引发的错误异常的传播如果异常被系统捕获,那么异常后面的程序将会被正常执行下去,如果异常抛出但是没有被捕获,那么异常后面的代码是不会被执行的。执行部分的异常执行部分的异常,如果在执行体内部可以被捕获,则在内部处理;如果内部不捕获,则向外层传播,会被外层的处理;声明部分的异常在声明部分的异常则不会被内部的异常捕获机制所捕获,会直接向外层传播,被外层的异常处理机制所捕获,在内部即使没有处理机制也不能被处理。raise_application_error(code(-20001~-20999),message)自定义异常过程的调用局部子程序的声明 显式游标 声明游标 打开游标 提取游标 关闭游标用一个程序示例:declarecursorcur_empis selectename,sal fromemp wheredeptno=10;v_enameemp.enmae%type;v_salemp.sal%type;beginopencur_emp;loop fetchcur_empintov_ename,v_sal; exitwhencur_emp%notfound; dbms_out.putline('v_ename'||v_name||','||'v_sal'||v_sal);endloop;closecur_emp;exceptionwhenothersthendbms_out.put_line('程序发生异常!');end隐式游标declarecursorcur_empis selectename,sal fromemp wheredeptno=10;beginforrincur_emploop dbms_out.put_line('ename'||r.ename||'sal'||r.sal);endloop;exceptionwhenothersthen dbms_out.put_line('程序异常!');end;sql用%取游标属性如corsor%rowcount.......select..into..无行的多行都会返回异常动态sql语句:ddldcl语句 dml语句中含有不确定字符数据字典user_tablesuser_objectsuser_indexuser_sourceuser_view函数和过程的区别函数必须要有返回值过程可以有也可以没有子程序的重载:参数个数和参数类型不同,相似不行包包头:包
规范
编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载
和包说明包体包体依赖包头,独立被存储触发器触发器里不可以含有TCL语句,和事件处于同一个事务单元,由事件处理事务DML触发器insteadof视图触发器系统触发器,数据库触发器,模式触发器创建表空间的时候(可以)需要指明数据文件,创建其他则不需要指明数据文件UTL数据的导入导出sqlldr的控制文件函数总结:基本的:Oracle的SQL函数分为单行函数和多行函数。单行函数只对单条
记录
混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载
有效,多行函数对多条记录有效。单行函数包括,字符、数字、日期、转换和普通函数。字符函数举例:全小写LOWER('SQLCourse') sqlcourse全大写UPPER('SQLCourse') SQLCOURSE首字母大写INITCAP('SQLCourse') SqlCourse拼接CONCAT('Good','String') GoodString取子串SUBSTR('String',1,3) Str求长度 LENGTH('String') 6数字函数举例:ROUND--对数值进行四舍五入操作TRUNC--截断。注意截断和舍入的位数,可以是负数。ROUND(45.923,2) 45.92ROUND(45.923,0) 46ROUND(45.923,-1) 50TRUNC(45.923,2) 45.92TRUNC(45.923) 45TRUNC(45.923,-1) 40MOD--返回两数相除的余数MOD(1600,300)--100MOD(2.3,2.1)--0.2日期函数:?Oracle将日期按照内部格式将日期存成以下七个字节–世纪、年、月、日、时、分、秒。?在Oracle中日期缺省的显示格式为:DD-MON-YY。?可以使用系统变量SYSDATE获取系统时间?。查看SYSDATE时,可以使用虚拟表DUAL。举例:求两个日期间有多少个月份MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 1.9774194在日期上加制定的月数ADD_MONTHS('11-JAN-94',6) '11-JUL-94'下一个星期几是什么时候NEXT_DAY('01-SEP-95','FRIDAY') '08-SEP-95'某月最后一天LAST_DAY('01-SEP-95') '30-SEP-95'把日期四舍五入到月份ROUND('25-MAY-95','MONTH') 01-JUN-95ROUND('25-MAY-95','YEAR') 01-JAN-95把日期截断到月份TRUNC('25-MAY-95','MONTH') 01-MAY-95TRUNC('25-MAY-95','YEAR') 01-JAN-95转换函数TO_CHAR将数字或日期转换为字符串TO_NUMBER将字符串转换成数字TO_DATE将字符串转换成日期在转换函数中会使用格式串TO_CHAR(date,'fmt')格式串包含在''中,大小写敏感,可以是任意有效的日期格式格式串:YYYY表示完整的四位数字年YEAR表示英文拼写的年MM表示两位数字月MONTH表示英文全拼的年DY表示三位缩写的星期DAY表示英文全拼的星期一些特殊用法:时间格式 HH24:MI:SSAM15:45:32PM在格式串中加入字符串 DD"of"MONTH12ofOCTOBER用后缀拼出整个日期 ddspthfourteenthTO_CHAR(number,'fmt')使用此函数将数字转换成数字 9表示数字 0强制为0 $设置美元符号 L使用当前字符集的货币符号 .小数点 ,千位分隔符可以使用TO_NUMBER将字符串转换成数字可以使用TO_DATE将字符串转换成格式日期TO_DATE('10September1992','DDMonthYYYY')使用格式串TO_DATE(char[,'fmt'])普通函数NULL值处理函数NVL(expr1,expr2) 如果expr1为NULL,返回expr2NVL2(expr1,expr2,expr3) 如果expr1为非空,则返回expr2,如果expr1为NULL,则返回expr3NULLIF(expr1,expr2) 如果expr1=expr2,返回NULL,否则返回expr1COALESCE(expr1,expr2,...,exprn) 返回表达式列表中的第一个非空表达式条件表达式,用case表达式或decode实现条件逻辑CASE表达式CASEexprWHENcomparison_expr1THENreturn_expr1 [WHENcomparison_expr2THENreturn_expr2 WHENcomparison_exprnTHENreturn_exprn ELSEresults]ENDdecode函数DECODE(col|expression,search1,result1[,search2,result2,...,] [,default])分组函数:AVG(DISTINCT|ALL|n)COUNT(DISTINCT|ALL|expr|*)MAX(DISTINCT|ALL|expr)MIN(DISTINCT|ALL|expr)STDDEV(DISTINCT|ALL|n)
标准
excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载
差SUM(DISTINCT|ALL|n)VARIANCE(DISTINCT|ALL|n)方差oracle复习整理(二)注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。 单值函数在查询中返回单个值,可被应用到select,where子句,startwith以及connectby子句和having子句。(一).数值型函数(NumberFunctions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,andTANH支持36位小数点。ACOS,ASIN,ATAN,andATAN2支持30位小数点。1、MOD(n1,n2)返回n1除n2的余数,如果n2=0则返回n1的值。 例如:SELECTMOD(24,5)FROMDUAL;2、ROUND(n1[,n2])返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracledocuments上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。例如:SELECTROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1)FROMDUAL;3、TRUNC(n1[,n2]返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。例如:SELECTTRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1)FROMDUAL;(二).字符型函数返回字符值(CharacterFunctionsReturningCharacterValues) 该类函数返回与输入类型相同的类型。?返回的CHAR类型值长度不超过2000字节;?返回的VCHAR2类型值长度不超过4000字节;如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。?返回的CLOB类型值长度不超过4G;对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。1、LOWER(c)将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如:SELECTLOWER('WhaTistHis')FROMDUAL;2、UPPER(c)将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如:SELECTUPPER('WhaTistHis')FROMDUAL;3、LPAD(c1,n[,c2])返回指定长度=n的字符串,需要注意的有几点:?如果n
c1.lengthandc2isnull,以空格从左向右补充字符长度至n并返回;?如果n>c1.lengthandc2isnotnull,以指定字符c2从左向右补充c1长度至n并返回;例如:SELECTLPAD('WhaTistHis',5),LPAD('WhaTistHis',25),LPAD('WhaTistHis',25,'-')FROMDUAL;最后大家再猜一猜,如果n<0,结果会怎么样null4、RPAD(c1,n[,c2])返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反; 例如:SELECTRPAD('WhaTistHis',5),RPAD('WhaTistHis',25),RPAD('WhaTistHis',25,'-')FROMDUAL;5、TRIM([[LEADING||TRAILING||BOTH]c2FROM]c1)哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。看起来很复杂,理解起来很简单:?如果没有指定任何参数则oracle去除c1头尾空格例如:SELECTTRIM('WhaTistHis')FROMDUAL;?如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟)例如:SELECTTRIM('W'FROM'WhaTistHiswW')FROMDUAL;?如果指定了leading参数则会去掉c1头部c2例如:SELECTTRIM(leading'W'FROM'WhaTistHiswW')FROMDUAL;?如果指定了trailing参数则会去掉c1尾部c2例如:SELECTTRIM(trailing'W'FROM'WhaTistHiswW')FROMDUAL;?如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗?没区别!)例如:SELECTTRIM(both'W'FROM'WhaTistHiswW')FROMDUAL;注意:c2长度=16、LTRIM(c1[,c2])千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。例如:SELECTLTRIM('WWhhhhhaTistHiswW','Wh')FROMDUAL;7、RTRIM(c1,c2)与上同,不过方向相反 例如:SELECTRTRIM('WWhhhhhaTistHiswW','Ww')FROMDUAL;8、REPLACE(c1,c2[,c3])将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。 例如:SELECTREPLACE('WWhhhhhaTistHiswW','W','-')FROMDUAL;9、SOUNDEX(c)神奇的函数啊,该函数返回字符串参数的语音表示形式,对于比较一些读音相同,但是拼写不同的单词非常有用。计算语音的算法如下:?保留字符串首字母,但删除a、e、h、i、o、w、y。?将下表中的数字赋给相对应的字母:1:b、f、p、v2:c、g、k、q、s、x、z3:d、t4:l5:m、n6:R?如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f),或者只有h或w,则删除其他的,只保留1个;?只返回前4个字节,不够用0填充例如:SELECTSOUNDEX('dog'),soundex('boy')FROMDUAL;10、SUBSTR(c1,n1[,n2])截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。'_'代表空格n1=开始长度;n2=截取的字符串长度,如果为空,默认截取到字符串结尾;?如果n1=0thenn1=1?如果n1>0,则oracle从左向右确认起始位置截取例如:SELECTSUBSTR('Whatisthis',5,3)FROMDUAL;_is?如果n1<0,则oracle从右向左数确认起始位置例如:SELECTSUBSTR('Whatisthis',-5,3)FROMDUAL;_th?如果n1>c1.length则返回空例如:SELECTSUBSTR('Whatisthis',50,3)FROMDUAL;null然后再请你猜猜,如果n2<1,会如何返回值呢null11、TRANSLATE(c1,c2,c3)就功能而言,此函数与replace有些相似。但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。什么是绝对匹配替换呢?简单的说,是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解,我们通过几具实例来说明:例如:SELECTTRANSLATE('Whatisthis','','-')FROMDUAL;nullSELECTTRANSLATE('Whatisthis','-','')FROMDUAL;null结果都是空。来试试这个:SELECTTRANSLATE('Whatisthis','','')FROMDUAL;再来看这个:SELECTTRANSLATE('Whatisthis','ait','-*')FROMDUAL;是否明白了点呢?Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了?Replace是替换,而translate则像是过滤(三).字符型函数返回数字值(CharacterFunctionsReturningNumberValues)本类函数支持所有的数据类型1、INSTR(c1,c2[,n1[,n2]])返回c2在c1中位置?c1:原字符串?c2:要寻找的字符串?n1:查询起始位置,正值表示从左到右,负值表示从右到左(大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。如果为0的话,则返回的也是0?n2:第几个匹配项。大于0例如:SELECTINSTR('abcdefg','e',-3)FROMDUAL;2、LENGTH(c)返回指定字符串的长度。如果例如:SELECTLENGTH('A123中')FROMDUAL;5猜猜SELECTLENGTH('')FROMDUAL;的返回值是什么答:null(四).日期函数(DatetimeFunctions)本类函数中,除months_between返回数值外,其它都将返回日期。1、ADD_MONTHS()返回指定日期月份+n之后的值,n可以为任何整数。例如:SELECTADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12)FROMDUAL;2、CURRENT_DATE返回当前session所在时区的默认时间例如:SQL>altersessionsetnls_date_format='mm-dd-yyyy';SQL>selectcurrent_datefromdual;3、SYSDATE功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。例如:SELECTSYSDATE,CURRENT_DATEFROMDUAL;4、LAST_DAY(d)返回指定时间所在月的最后一天例如:SELECTlast_day(SYSDATE)FROMDUAL;5、NEXT_DAY(d,n)返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。例如:三思用的中文nt,nls_language值为SIMPLIFIEDCHINESESELECTNEXT_DAY(SYSDATE,5)FROMDUAL;SELECTNEXT_DAY(SYSDATE,'星期四')FROMDUAL;两种方式都可以取到正确的返回,但是:SELECTNEXT_DAY(SYSDATE,'Thursday')FROMDUAL;则会执行出错,提供你说周中的日无效,就是这个原因了。6、MONTHS_BETWEEN(d1,d2)返回d1与d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0例如:SELECTmonths_between(SYSDATE,sysdate),months_between(SYSDATE,add_months(sysdate,-1)),months_between(SYSDATE,add_months(sysdate,1))FROMDUAL;7、ROUND(d[,fmt])前面讲数值型函数的时候介绍过ROUND,此处与上功能基本相似,不过此处操作的是日期。如果不指定fmt参数,则默认返回距离指定日期最近的日期。例如:SELECTROUND(SYSDATE,'HH24')FROMDUAL;8、TRUNC(d[,fmt])与前面介绍的数值型TRUNC原理相同,不过此处也是操作的日期型。例如:SELECTTRUNC(SYSDATE,'HH24')FROMDUAL;(五).转换函数(ConversionFunctions)转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。1、TO_CHAR()本函数又可以分三小类,分别是?转换字符->字符TO_CHAR(c):将nchar,nvarchar2,clob,nclob类型转换为char类型;例如:SELECTTO_CHAR('AABBCC')FROMDUAL;?转换时间->字符TO_CHAR(d[,fmt]):将指定的时间(data,timestamp,timestampwithtimezone)按照指定格式转换为varchar2类型;例如:SELECTTO_CHAR(sysdate,'yyyy-mm-ddhh24:mi:ss')FROMDUAL;?转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;例如:SELECTTO_CHAR(-100,'L99G999D99MI')FROMDUAL;2、TO_DATE(c[,fmt[,nls]])将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julianday)转换,c则必须为大于0并小于5373484的正整数。例如:SELECTTO_DATE(2454336,'J')FROMDUAL;SELECTTO_DATE('2007-8-2323:25:00','yyyy-mm-ddhh24:mi:ss')FROMDUAL;为什么公元制的话,c的值必须不大于5373484呢?因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句:SELECTTO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j')FROMDUAL;3、TO_NUMBER(c[,fmt[,nls]])将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。例如:SELECTTO_NUMBER('-100.00','9G999D99')FROMDUAL;(六).其它辅助函数(MiscellaneousSingle-RowFunctions)1、DECODE(exp,s1,r1,s2,r2..s,r[,def])可以把它理解成一个增强型的ifelse,只不过它并不通过多行语句,而是在一个函数内实现ifelse的功能。exp做为初始参数。s做为对比值,相同则返回r,如果s有多个,则持续遍历所有s,直到某个条件为真为止,否则返回默认值def(如果指定了的话),如果没有默认值,并且前面的对比也都没有为真,则返回空。毫无疑问,decode是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记和熟练使用。例如:selectdecode('a2','a1','true1','a2','true2','default')fromdual;2、GREATEST(n1,n2,...n)返回序列中的最大值例如:SELECTGREATEST(15,5,75,8)"Greatest"FROMDUAL;3、LEAST(n1,n2....n)返回序列中的最小值例如:SELECTLEAST(15,5,75,8)LEASTFROMDUAL;4、NULLIF(c1,c2)Nullif也是个很有意思的函数。逻辑等价于:CASEWHENc1=c2THENNULLELSEc1END例如:SELECTNULLIF('a','b'),NULLIF('a','a')FROMDUAL;5、NVL(c1,c2)逻辑等价于IFc1isnullTHENc2ELSEc1END。c1,c2可以是任何类型。如果两者类型不同,则oracle会自动将c2转换为c1的类型。例如:SELECTNVL(null,'12')FROMDUAL;6、NVL2(c1,c2,c3)大家可能都用到nvl,但你用过nvl2吗?如果c1非空则返回c2,如果c1为空则返回c3例如:selectnvl2('a','b','c')isNull,nvl2(null,'b','c')isNotNullfromdual;7、SYS_CONNECT_BY_PATH(col,c)该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connectby子句共同使用。例如:createtabletmp3(rootcolvarchar2(10),nodecolvarchar2(10));insertintotmp3values('','a001');insertintotmp3values('','b001');insertintotmp3values('a001','a002');insertintotmp3values('a002','a004');insertintotmp3values('a001','a003');insertintotmp3values('a003','a005');insertintotmp3values('a005','a008');insertintotmp3values('b001','b003');insertintotmp3values('b003','b005');selectlpad('',level*10,'=')||'>'||sys_connect_by_path(nodecol,'/')fromtmp3startwithrootcol='a001'connectbypriornodecol=rootcol;8、SYS_CONTEXT(c1,c2[,n])将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。Oracle9i提供内置了一个命名空间USERENV,描述了当前session的各项信息,其拥有下列参数:?CURRENT_SCHEMA:当前模式名;?CURRENT_USER:当前用户;?IP_ADDRESS:当前客户端IP地址;?OS_USER:当前客户端操作系统用户;等等数十项,更详细的参数列还请大家直接参考OracleOnlineDocuments例如:SELECTSYS_CONTEXT('USERENV','SESSION_USER')FROMDUAL;注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。单值函数在查询中返回单个值,可被应用到select,where子句,startwith以及connectby子句和having子句。(一).数值型函数(NumberFunctions)数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT,TAN,andTANH支持36位小数点。ACOS,ASIN,ATAN,andATAN2支持30位小数点。1、ABS(n)返回数字的绝对值例如:SELECTABS(-1000000.01)FROMDUAL;2、COS(n)返回n的余弦值例如:SELECTCOS(-2)FROMDUAL;3、ACOS(n)反余弦函数,nbetween-1and1,返回值between0andpi。例如:SELECTACOS(0.9)FROMDUAL;4、BITAND(n1,n2)位与运算,这个太有意思了,虽然没想到可能用到哪里,详细说明一下:假设3,9做位与运算,3的二进制形式为:0011,9的二进制形式为:1001,则结果是0001,转换成10进制数为1。例如:SELECTBITAND(3,9)FROMDUAL;5、CEIL(n)返回大于或等于n的最小的整数值例如:SELECTceil(18.2)FROMDUAL;考你一下,猜猜ceil(-18.2)的值会是什么呢6、FLOOR(n)返回小于等于n的最大整数值例如:SELECTFLOOR(2.2)FROMDUAL;再猜猜floor(-2.2)的值会是什么呢7、BIN_TO_NUM(n1,n2,....n)二进制转向十进制例如:SELECTBIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1)FROMDUAL;8、SIN(n)返回n的正玄值,n为弧度。例如:SELECTSIN(10)FROMDUAL;9、SINH(n)返回n的双曲正玄值,n为弧度。例如:SELECTSINH(10)FROMDUAL;10、ASIN(n)反正玄函数,nbetween-1and1,返回值betweenpi/2and-pi/2。例如:SELECTASIN(0.8)FROMDUAL;11、TAN(n)返回n的正切值,n为弧度例如:SELECTTAN(0.8)FROMDUAL;12、