Oracle其他常用函数

不懂01的ITer
126 阅读5分钟

Oracle中的其他常用函数


DECODE

语法:DECODE(base_expr,comparel,valuel,Compare2,value2,…default)

功能:把base_expr与后面的每个compare(n)进行比较,如果匹配返回相应的value(n).如果没有发生匹配,则返回default,每个valuel数据类型必须一致,如果没有default则返回null.*/

select decode(GRADE,1,'lowst',2,'low',3,'mid',4,'high',5,'highest','未知') LVL from scott.salgrade;

LEAST

语法:LEAST(expr1[,expr2]…)

功能:计算参数中最小的表达式.所有表达式的比较类型以expr1为准,比较字符的编码大小.

使用位置:过程性语言和SQL语句.*/

select least('AA','AB','AC') V1,least('啊','安','天') V2 from dual;

NULLIF

语法:NULLIF(expr1,expr2) ->相等返回NULL,不等返回expr1

功能:9i新增,用于比较表达式expr1和expr2,相等返回null,否则返回expr1.*/

select ENAME,nullif(SAL,COMM) V1,NULLIF(TO_CHAR(HIREDATE,'yyyy'),'1981') V2 from SCOTT.EMP;

NVL

语法:NVL(expr1,expr2)

功能:用于将NULL转变为实际值,如果expr1是NULL,那么返回expr2,否则返回expr1,expr1、expr2两者必须为同类型或expr2可以隐式转换为expr1,否则会报错.*/

select ENAME,nvl(COMM,0) V from SCOTT.EMP;

SELECT NVL(to_date('2023-03-10','yyyy-mm-dd')-sysdate,SYSDATE) V FROM dual;

NVL2

语法:NVL2(expr1,expr2,expr3)

功能:9i新增,expr1不为NULL,返回expr2；expr1为NULL,返回expr3，expr1可以是任意数据类型；expr2与expr3可以是除LONG外的任意数据类型,但需要类型一致或expr3可以隐式转换为expr2.*/

select ENAME,nvl2(COMM,SAL+COMM,SAL) V from SCOTT.EMP;

SELECT NVL2(to_date('2023-03-10','yyyy-mm-dd'),sysdate-to_date('2023-03-10','yyyy-mm-dd'),sysdate) V FROM dual;

OVER

语法:sum/count/row_number over( partition by XXX order by XXX )

功能:此函数为分析函数,常与其他聚合函数配合使用

使用位置:过程性语言和SQL语句*/


SYS_CONTEXT

语法:sys_coniext('context','attribute')

功能:该函数用于返回应用上下文的特定属性值,获得系统信息,其中context为上下文名,而attribute为应用上下文名,此函数可以得到oracle主机及客户端的信息.*/

SELECT SYS_CONTEXT('USERENV','TERMINAL') 客户端名称
,SYS_CONTEXT('USERENV','LANGUAGE') 客户端语言
,SYS_CONTEXT('USERENV','LANG') 语言简记
,SYS_CONTEXT('USERENV','SESSIONID') 当前会话标识
,SYS_CONTEXT('USERENV','INSTANCE') 当前实例
,SYS_CONTEXT('USERENV','ENTRYID') 会话入口标识
,SYS_CONTEXT('USERENV','ISDBA') DBA角色
,SYS_CONTEXT('USERENV','NLS_TERRITORY') 地区
,SYS_CONTEXT('USERENV','NLS_CURRENCY') 货币
,SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar
,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') 时间格式
,SYS_CONTEXT('USERENV','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') 数据库名称
,SYS_CONTEXT('USERENV','HOST') 客户端完整名称
,SYS_CONTEXT('USERENV','OS_USER') 客户端用户
,SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name
,SYS_CONTEXT('USERENV','IP_ADDRESS') 客户端IP地址
,SYS_CONTEXT('USERENV','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') uthentication_type
,SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data 
FROM DUAL;

SYS_DBURIGEN

语法:SYS_DBURIGEN(column)

功能:9i新增,根据列或属性生产类型为DBUriType的URL.*/

select SYS_DBURIGEN(ename) V from scott.emp;

SYS_GUID

语法:SYS_GUID()

功能:该函数用于生产类型为RAW的16字节的唯一标识符,每次调用该函数都会发生不同的RAW数据.*/

select SYS_GUID() V from DUAL;

SYS_TYPEID

语法:SYS_TYPEID(object_type_value)

功能:该函数用于返回唯一的类型ID值.*/

select name,SYS_TYPEID(value(p)) from scott.emp p;

SYS_XMLAGG

语法:SYS_XMLAGG(expr[,fmt])

功能:9i新增,用户汇总所有XML文档,并生成一个XML文档.*/

select SYS_XMLAGG(sys_xmlgen(ename)) V from scott.emp p;

SYS_XMLGEN

语法:SYS_XMLGEN(expr[,fmt])

功能:9i新增,根据数据库表的行和列生成一个XMLType实例.*/

select sys_xmlgen(ename) V from scott.emp p;

USER

语法:USER

功能:取得当前oracle用户的名字,返回的结果是一个VARCHAR2型字符串.

使用位置:过程性语言和SQL语句.*/

select user from dual;

USERENV

语法:USERENV(option)

功能:根据参数option,取得一个有关当前会话信息的VARCHAR2数值.option同SYS_CONTEXT()

使用位置:过程性语言和SQL语句.*/

select userenv('terminal') HOST,userenv('sessionid') SSID,userenv('language') LANG,userenv('isdba') ISDBA,userenv('CLIENT_INFO') CL_INFO from dual;

VSIZE

语法:VSIZE(value)

功能:获得value的内部表示的字节数.如果value是NULL,结果是NULL.

使用位置:SQL语句.*/

select vsize(user),user from dual;

XMLAGG

语法:XMLAGG(XMLType_instance [order by sort_list])

功能:9i新增,用于汇总多个XML块,并生成XML文档.*/

select xmlagg(xmlelement("employee",ename||' '||sal)) V from scott.emp where deptno=10;

XMLCOLATTVAL

语法:XMLCOLATTVAL(value_expr1[,value_expr2]…)

功能:9i新增,用于生成XML块,并增加”column”作为属性名.*/

select xmlelement("emp", XMLCOLATTVAL(ename,sal)) V from scott.emp where deptno=10;

XMLCONCAT

语法:XMLCONCAT(XMLType_instance1[,XMLType_instance2]…)

功能:9i新增,用于连接多个XMLType实例,并生成一个新的XMLType实例.*/

select XMLCONCAT(xmlelement("ename",ename), xmlelement("sal",sal)) V from scott.emp where deptno=10;

XMLELEMENT

语法:XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])

功能:9i新增,用于返回XMLType实例,其中参数identifier指定元素名,参数xml_attribute_clause指定元素属性子句,参数value_expr指定元素值.*/

select XMLELEMENT("emp",xmlattributes(empno as "id",ename,job,sal)) V from scott.emp;

XMLFOREST

语法:XMLPOREST(value_expr1[,value_expr2]…)

功能:9i新增,用于返回XML块.*/

select xmlelement("EMPLOYEE", XMLFOREST(ename,sal)) V from scott.emp where deptno=10;
avatar
文章
阅读
粉丝