oracle 10g函数大全--其他函数
DUMP(w[,x[,y[,z]]]) 【功能】返回数据类型、字节长度和在内部的存储位置. 【参数】 w为各种类型的字符串(如字符型、数值型、日期型……) x为返回位置用什么方式表达,可为:8,10,16或17,分别表示:8/10/16进制和字符型,默认为10。 y和z决定了内部参数位置 【返回】类型 <[长度]>,符号/指数位 [数字1,数字2,数字3,......,数字20] 如:Typ=2 Len=7: 60,89,67,45,23,11,102 SELECT DUMP('ABC',1016) FROM dual; 返回结果为:Typ=96 Len=3 CharacterSet=ZHS16GBK: 41,42,43 代码 数据类型 0 对应 VARCHAR2 1 对应 NUMBER 8 对应 LONG 12 对应 DATE 23 对应 RAW 24 对应 LONG RAW 69 对应 ROWID 96 对应 CHAR 106 对应 MSSLABEL 各位的含义如下: 1.类型: Number型,Type=2 (类型代码可以从Oracle的文档上查到) 2.长度:指存储的字节数 3.符号/指数位 在存储上,Oracle对正数和负数分别进行存储转换: 正数:加1存储(为了避免Null) 负数:被101减,如果总长度小于21个字节,最后加一个102(是为了排序的需要) 指数位换算: 正数:指数=符号/指数位 - 193 (最高位为1是代表正数) 负数:指数=62 - 第一字节 4.从<数字1>开始是有效的数据位 从<数字1>开始是最高有效位,所存储的数值计算方法为: 将下面计算的结果加起来: 每个<数字位>乘以100^(指数-N) (N是有效位数的顺序位,第一个有效位的N=0) 5、举例说明 SQL> select dump(123456.789) from dual; 返回:Typ=2 Len=6: 195,13,35,57,79,91 <指数>: 195 - 193 = 2 <数字1> 13 - 1 = 12 *100^(2-0) 120000 <数字2> 35 - 1 = 34 *100^(2-1) 3400 <数字3> 57 - 1 = 56 *100^(2-2) 56 <数字4> 79 - 1 = 78 *100^(2-3) .78 <数字5> 91 - 1 = 90 *100^(2-4) .009 123456.789 SQL> select dump(-123456.789) from dual; 返回:Typ=2 Len=7: 60,89,67,45,23,11,102 算法: <指数> 62 - 60 = 2(最高位是0,代表为负数) <数字1> 101 - 89 = 12 *100^(2-0) 120000 <数字2> 101 - 67 = 34 *100^(2-1) 3400 <数字3> 101 - 45 = 56 *100^(2-2) 56 <数字4> 101 - 23 = 78 *100^(2-3) .78 <数字5> 101 - 11 = 90 *100^(2-4) .009 123456.789(-) 现在再考虑一下为什么在最后加102是为了排序的需要,-123456.789在数据库中实际存储为 60,89,67,45,23,11 而-123456.78901在数据库中实际存储为 60,89,67,45,23,11,91 可见,如果不在最后加上102,在排序时会出现-123456.789<-123456.78901的情况。
greatest(exp1,exp2,exp3,……,expn) 【功能】返回表达式列表中值最大的一个。如果表达式类型不同,会隐含转换为第一个表达式类型。 【参数】exp1……n,各类型表达式 【返回】exp1类型 【示例】 SELECT greatest(10,32,'123','2006') FROM dual; SELECT greatest('kdnf','dfd','a','206') FROM dual;
least(exp1,exp2,exp3,……,expn) 【功能】返回表达式列表中值最小的一个。如果表达式类型不同,会隐含转换为第一个表达式类型。 【参数】exp1……n,各类型表达式 【返回】exp1类型 【示例】 SELECT least(10,32,'123','2006') FROM dual; SELECT least('kdnf','dfd','a','206') FROM dual;
【语法】NVL (expr1, expr2) 【功能】若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。 注意两者的类型要一致 【语法】NVL2 (expr1, expr2, expr3) 【功能】expr1不为NULL,返回expr2;expr2为NULL,返回expr3。 expr2和expr3类型不同的话,expr3会转换为expr2的类型
user 【功能】返回当前会话对应的数据库用户名。 【参数】无 【返回】字符型
uid 【功能】返回当前会话所对应的用户id号。 【参数】无 【返回】字符型
userenv(parameter) 【功能】返回当前会话上下文属性。 【参数】Parameter是参数,可以用以下参数代替: Isdba:若用户具有dba权限,则返回true,否则返回false. Language:返回当前会话对应的语言、地区和字符集。 LANG:返回当前环境的语言的缩写 Terminal:返回当前会话所在终端的操作系统标识符。 Sessionid:返回正在使用的审计会话号. Client_info:返回用户会话信息,若没有则返回null. 【返回】根据参数不同则类型不同 【示例】 Select userenv('isdba'),userenv('Language'),userenv('Terminal'),userenv('Client_info') from dual;
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 【功能】根据条件返回相应值 【参数】c1, c2, ...,cn,字符型/数值型/日期型,必须类型相同或null 注:值1……n 不能为条件表达式,这种情况只能用case when then end解决 ·含义解释: decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 该函数的含义如下: IF 条件=值1 THEN RETURN(翻译值1) ELSIF 条件=值2 THEN RETURN(翻译值2) ...... ELSIF 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END IF 或: when case 条件=值1 THEN RETURN(翻译值1) ElseCase 条件=值2 THEN RETURN(翻译值2) ...... ElseCase 条件=值n THEN RETURN(翻译值n) ELSE RETURN(缺省值) END 【示例】 ·使用方法: 1、比较大小 select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值 sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1 例如: 变量1=10,变量2=20 则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。 2、表、视图结构转化 现有一个商品销售表sale,表结构为: month char(6) --月份 sell number(10,2) --月销售金额 现有数据为: 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 想要转化为以下结构的数据: year char(4) --年份 month1 number(10,2) --1月销售金额 month2 number(10,2) --2月销售金额 month3 number(10,2) --3月销售金额 month4 number(10,2) --4月销售金额 month5 number(10,2) --5月销售金额 month6 number(10,2) --6月销售金额 month7 number(10,2) --7月销售金额 month8 number(10,2) --8月销售金额 month9 number(10,2) --9月销售金额 month10 number(10,2) --10月销售金额 month11 number(10,2) --11月销售金额 month12 number(10,2) --12月销售金额 结构转化的SQL语句为: create or replace view v_sale(year,month1,month2,month3,month4,month5,month6, month7,month8,month9,month10,month11,month12) as select substrb(month,1,4), sum(decode(substrb(month,5,2),'01',sell,0)), sum(decode(substrb(month,5,2),'02',sell,0)), sum(decode(substrb(month,5,2),'03',sell,0)), sum(decode(substrb(month,5,2),'04',sell,0)), sum(decode(substrb(month,5,2),'05',sell,0)), sum(decode(substrb(month,5,2),'06',sell,0)), sum(decode(substrb(month,5,2),'07',sell,0)), sum(decode(substrb(month,5,2),'08',sell,0)), sum(decode(substrb(month,5,2),'09',sell,0)), sum(decode(substrb(month,5,2),'10',sell,0)), sum(decode(substrb(month,5,2),'11',sell,0)), sum(decode(substrb(month,5,2),'12',sell,0)) from sale group by substrb(month,1,4);
【语法】NULLIF (expr1, expr2) 【功能】expr1和expr2相等返回NULL,不相等返回expr1
COALESCE(c1, c2, ...,cn) 【功能】返回列表中第一个非空的表达式,如果所有表达式都为空值则返回1个空值 【参数】c1, c2, ...,cn,字符型/数值型/日期型,必须类型相同或null 【返回】同参数类型 【说明】从Oracle 9i版开始,COALESCE函数在很多情况下就成为替代CASE语句的一条捷径 【示例】 select COALESCE(null,3*5,44) hz from dual; 返回15 select COALESCE(0,3*5,44) hz from dual; 返回0 select COALESCE(null,'','AAA') hz from dual; 返回AAA select COALESCE('','AAA') hz from dual; 返回AAA
rownum 【功能】返回当前行号 【参数】无 【返回】数值型
BFILENAME(dir,file) 【功能】函数返回一个空的BFILE位置值指示符,函数用于初始化BFILE变量或者是BFILE列。 【参数】dir是一个directory类型的对象,file为一文件名。 insert into lobdemo(key,bfile_col) values (-1,biflename('utils','file1'));
VSIZE(X)
【功能】返回X的大小(字节)数
【参数】x
select vsize(user),user from dual;
返回:6 asdied
select length('adfad合理') "bytesLengthIs" from dual --7
select lengthb('adfad') "bytesLengthIs" from dual --5
select lengthb('adfad合理') "bytesLengthIs" from dual --9
select vsize('adfad合理') "bytesLengthIs" from dual --9
select lengthc('adfad合理')"bytesLengthIs" from dual --7
lengthb=vsize
lengthc=length
case [<表达式>] when <表达式条件值1> then <满足条件时返回值1> [when <表达式条件值2> then <满足条件时返回值2> …… [else <不满足上述条件时返回值>]] end 【功能】当:<表达式>=<表达式条件值1……n> 时,返回对应 <满足条件时返回值1……n> 当<表达式条件值1……n>不为条件表达式时,与函数decode()相同, decode(<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2> ……,<不满足上述条件时返回值>) 【参数】 <表达式> 默认为true (逻辑型) <表达式条件值1……n> 类型要与<表达式>类型一致, 若<表达式>为字符型,则<表达式条件值1……n>也要为字符型 【注意点】 1、以CASE开头,以END结尾 2、分支中WHEN 后跟条件,THEN为显示结果 3、ELSE 为除此之外的默认情况,类似于高级语言程序中switch case的default,可以不加 4、END 后跟别名 5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略,得注意条件先后顺序 【示例】 建立环境: create table xqb (xqn number(1,0)); insert into xqb xqn values(1); insert into xqb xqn values(2); insert into xqb xqn values(3); insert into xqb xqn values(4); insert into xqb xqn values(5); insert into xqb xqn values(6); insert into xqb xqn values(7); commit; 查询结果: SELECT xqn, CASE WHEN xqn = 1 THEN '星期一' WHEN xqn = 2 THEN '星期二' WHEN xqn = 3 THEN '星期三' else '星期三以后' END 星期 FROM xqb 另类写法 SELECT xqn, CASE xqn WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三' else '星期三以后' END 星期 FROM xqb decode正确表达: SELECT xqn, decode(xqn,1,'星期一',2,'星期二',3,'星期三','星期三以后') 星期 FROM xqb decode错误表达: SELECT xqn, decode(TRUE,xqn=1,'星期一',xqn=2,'星期二',xqn=3,'星期三','星期三以后') 星期 FROM xqb 组合条件表达: SELECT xqn, CASE WHEN xqn <= 1 THEN '星期一' WHEN xqn <= 2 THEN '星期二' --条件同:not(xqn<=1) and xqn<=2 WHEN xqn <= 3 THEN '星期三' --条件同:not(xqn<=1 and xqn<=2) and xqn<=3 else '星期三以后' END 星期 FROM xqb
【语法】sys_guid() 【功能】生产32位的随机数,不过中间包括一些大写的英文字母。 【返回】长度为32位的字符串,包括0-9和大写A-F 【示例】 select sys_guid() from dual
【语法】SYS_CONTEXT(c1,c2) 【功能】返回系统c1对应的c2的值。可以使用在SQL/PLSQL中,但不可以用在并行查询或者RAC环境中 【参数】 c1,'USERENV' c2,参数表,详见示例 【返回】字符串 【示例】 select SYS_CONTEXT('USERENV','TERMINAL') terminal, SYS_CONTEXT('USERENV','LANGUAGE') language, SYS_CONTEXT('USERENV','SESSIONID') sessionid, SYS_CONTEXT('USERENV','INSTANCE') instance, SYS_CONTEXT('USERENV','ENTRYID') entryid, SYS_CONTEXT('USERENV','ISDBA') isdba, SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, SYS_CONTEXT('USERENV','CURRENT_USER') current_user, SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, SYS_CONTEXT('USERENV','SESSION_USER') session_user, SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, SYS_CONTEXT('USERENV','DB_NAME') db_name, SYS_CONTEXT('USERENV','HOST') host, SYS_CONTEXT('USERENV','OS_USER') os_user, SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data from dual
Oracle dbms_random包的用法 from:http://space.myfarmer.cn/?action-viewthread-tid-17039 1.dbms_random.value方法 dbms_random是一个可以生成随机数值或者字符串的程序包。这个包有initialize()、seed()、terminate()、value()、normal()、random()、string()等几个函数,但value()是最常用的,value()的用法一般有两个种,第一 function value return number; 这种用法没有参数,会返回一个具有38位精度的数值,范围从0.0到1.0,但不包括1.0,如下示例: SQL> set serverout on SQL> begin 2 for i in 1..10 loop 3 dbms_output.put_line(round(dbms_random.value*100)); 4 end loop; 5 end; 6 / 46 19 45 37 33 57 61 20 82 8 PL/SQL 过程已成功完成。 SQL> 第二种value带有两个参数,第一个指下限,第二个指上限,将会生成下限到上限之间的数字,但不包含上限,“学无止境”兄说的就是第二种,如下: SQL> begin 2 for i in 1..10 loop 3 dbms_output.put_line(trunc(dbms_random.value(1,101))); 4 end loop; 5 end; 6 / 97 77 13 86 68 16 55 36 54 46 PL/SQL 过程已成功完成。 2. dbms_random.string 方法 某些用户管理程序可能需要为用户创建随机的密码。使用10G下的dbms_random.string 可以实现这样的功能。 例如: SQL> select dbms_random.string('P',8 ) from dual ; DBMS_RANDOM.STRING('P',8) ---- 3q<M"yf[ 第一个参数的含义: ■ 'u', 'U' - returning string in uppercase alpha characters ■ 'l', 'L' - returning string in lowercase alpha characters ■ 'a', 'A' - returning string in mixed case alpha characters ■ 'x', 'X' - returning string in uppercase alpha-numeric characters ■ 'p', 'P' - returning string in any printable characters. Otherwise the returning string is in uppercase alpha characters. P 表示 printable,即字符串由任意可打印字符构成 而第二个参数表示返回的字符串长度。 3. dbms_random.random 方法 random返回的是BINARY_INTEGER类型值,产生一个任意大小的随机数 与dbms_random.value 的区别举例: Order By dbms_random.value; 这条语句功能是实现记录的随机排序 另外: dbms_random.value 和 dbms_random.random 两者之间有什么区别? 1。Order By dbms_random.value ,为结果集的每一行计算一个随机数,dbms_random.value 是结果集的一个列(虽然这个列并不在select list 中),然后根据该列排序,得到的顺序自然就是随机的啦。 2。看看desc信息便知道vlue和random这两个函数的区别了,value返回的是number类型,并且返回的值介于1和0之间,而random返回的是BINARY_INTEGER类型(以二进制形式存储的数字,据说运算的效率高于number但我没测试过,但取值范围肯定小于number,具体限制得查资料了) 如果你要实现随机排序,还是用value函数吧 4. dbms_random.normal方法 NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1,期望值为0。这个函数返回的数值中有68%是介于-1与+1之间,95%介于-2与+2之间,99%介于-3与+3之间。 5. dbms_random.send方法 用于生成一个随机数种子,设置种子的目的是可以重复生成随机数,用于调试。否则每次不同,难以调度。
Oracle包utl_inaddr
作用:用于取得局域网或Internet环境中的主机名和IP地址.
1、utl_inaddr.get_host_address 环境中IP地址
如果查询失败,则提示系统错误
查询www.qq.com的IP地址
select UTL_INADDR.get_host_address('www.qq.com') from dual;
查询本机IP地址
select UTL_INADDR.get_host_address() from dual;
查询局域网内yuechu的IP地址
select UTL_INADDR.get_host_address('yuechu') from dual;
2、UTL_INADDR.get_host_name返回环境中主机名
返回本机主机名
select UTL_INADDR.get_host_name() from dual;
返回局域网内指定IP地址的主机名
select UTL_INADDR.get_host_name('192.168.0.156') from dual;
返回intrenet中指定IP地址的网址
select UTL_INADDR.get_host_name('219.153.50.84') from dual;
分类:
Oracle
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· DeepSeek本地性能调优
· 一文掌握DeepSeek本地部署+Page Assist浏览器插件+C#接口调用+局域网访问!全攻略