Oracle Miscellaneous Functions

发布时间：2020-12-12 15:06:52 所属栏目：百科来源：网络整理

导读：点击打开链接 Oracle Miscellaneous Functions Version 11.1 General Information Note: These are functions not covered on other site pages LNNVL Evaluates a condition when one or both operands of the condition may be null LNNVL(condition) conn

Oracle Miscellaneous FunctionsVersion 11.1General InformationNote:These are functions not covered on other site pagesLNNVL
Evaluates a condition when one or both operands of the condition may be nullLNNVL(<condition>)conn hr/hr

SELECTCOUNT(*)FROMemployeesWHEREcommission_pct >= .2;

SELECTCOUNT(*)FROMemployeesWHERELNNVL(commission_pct >= .2);NULLIF

Compares expr1 and expr2. If they are equal,then the function returns null. If they are not equal,then the function returns expr1. You cannot specify the literal NULL for expr1.

NULLIF(<expression1>,<expression2>)conn hr/hr

SELECTe.last_name,NULLIF(e.job_id,j.job_id) "OLD JOB ID"
FROMemployees e,job_history j
WHEREe.employee_id = j.employee_id
ORDER BYlast_name;NVL
Returns a Value if the Expression IS NULLNVL(
s1VARCHAR2CHARACTER SET ANY_CS,-- expression
s2VARCHAR2CHARACTER SET s1%CHARSET) -- return value if null
RETURNVARCHAR2CHARACTER SET s1%CHARSET;

NVL(B1 BOOLEAN,B2 BOOLEAN) return BOOLEAN;set serveroutput on

DECLARE
iPLS_INTEGER;
BEGIN
SELECTNVL(i,93)
INTOi
FROMDUAL;

dbms_output.put_line('i1: ' || i);

SELECTNVL(i,39)
INTOi
FROMDUAL;

dbms_output.put_line('i2: ' || i);
END;
/NVL2
Returns First Value if NOT NULL,Second Value if NULL

Thanks Cary Hogan and Kaifer Bohus for the correctionsNVL2(<expression>,<return_if_not_null>,<return_if_null>)CREATETABLEtest (
categoryVARCHAR2(20),
outvalNUMBER(3),
invalNUMBER(3));

INSERTINTOtestVALUES('Groceries',10,NULL);
INSERTINTOtestVALUES('Payroll',NULL,100);
INSERTINTOtestVALUES('Groceries',20,200);
INSERTINTOtestVALUES('Groceries',30,NULL);

SELECT*FROMtest;

SELECTcategory,SUM(NVL2(outval,-outval,inval)) NET
FROMtest
GROUP BYcategory;

Note: If used in PL/SQL must be used in the form ofSELECTINTO
thus you can not use this syntax:

set serveroutput on

DECLARE
xNUMBER(5);
BEGIN
x := NVL2(10,20);
dbms_output.put_line(TO_CHAR(x));
END;
/

but you can write:

DECLARE
xNUMBER(5);
BEGIN
SELECTNVL2(10,20)
INTOx
FROMDUAL;

dbms_output.put_line(TO_CHAR(x));
END;
/SQLCODE
Number of the most recent exception raised by PL/SQL. 0 if nonestandard.sqlcodeRETURNPLS_INTEGER;set serveroutput on

BEGIN
dbms_output.put_line(SQLCODE);
END;
/

See Exceptions PageSQLERRM
Error message associated with the specified codestandard.sqlerrm RETURNVARCHAR2;

standard.sqlerrm(code_in IN INTEGER := SQLCODE)RETURNVARCHAR2set serveroutput on

BEGIN
dbms_output.put_line(SQLERRM);
END;
/

See Exceptions PageSQL_GUID
Generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms,the generated identifier consists of a host identifier,a process or thread identifier of the process or thread invoking the function,and a nonrepeating value (sequence of bytes) for that process or thread.SYS_GUID()RETURNRAWCREATETABLEt (
ridRAW(32),
colVARCHAR2(20));

desc t

INSERTINTOt
(rid,col)
VALUES
(SYS_GUID(),'ABC');

INSERTINTOt
(rid,'DEF');

SELECT*FROMt;SYS_TYPEID
Returns the typeid of the most specific type of the operandSYS_TYPEID(<object_type_value>)CREATETYPEperson_tASOBJECT (nameVARCHAR2(30),ssnNUMBER)
NOTFINAL;
/

CREATETABLEpersons OF person_t;

INSERTINTOpersons
VALUES
(person_t('Morgan',123));

SELECTname,SYS_TYPEID(VALUE(p)) TYPE_IDFROMpersons p;UIDUser Session IDSELECTUID
FROMDUAL;

SELECTuser#
FROMgv$session
WHEREschemaname = USER;USERUser As Logged OnSELECTUSERFROMDUAL;USERENV(deprecated: use SYS_CONTEXT)UsageSELECTuserenv('<parameter>')
FROMDUAL;Session info. stored with DBMS_APPLICATION_INFOSELECTUSERENV('CLIENT_INFO')FROMDUAL;

exec dbms_application_info.set_client_info('TEST');

SELECTUSERENV('CLIENT_INFO')FROMDUAL;The current audit entry number. The audit entryid sequence is shared between fine-grained audit records and regular audit records. You cannot use this attribute in distributed SQL statements.SELECTuserenv('ENTRYID')
FROMDUAL;Current instance identifierSELECTuserenv('INSTANCE')
FROMDUAL;ISDBA returns 'TRUE' if the user has been authenticated as having DBA privileges either through the operating system or through a password file.SELECTuserenv('ISDBA')
FROMDUAL;The ISO abbreviation for the language name,a shorter form than the existing 'LANGUAGE' parameter.SELECTuserenv('LANG')
FROMDUAL;The language and territory currently used by your session,along with the database character set,in the form:
language_territory dot characterset.SELECTuserenv('LANGUAGE')
FROMDUAL;The auditing session identifier. You cannot use this option in distributed SQL statements.SELECTuserenv('SESSIONID')
FROMDUAL;

SELECTaudsid
FROMv_$session;TERMINAL returns the operating system identifier for the terminal of the current session. In distributed SQL statements,this parameter returns the identifier for your local session. In a distributed environment,this parameter is supported only for remote SELECT statements,not for remote INSERT,UPDATE,or DELETE operations.SELECTuserenv('TERMINAL')
FROMDUAL;VALUE
Takes as its argument a correlation variable (table alias) associated with a row of an object table and returns object instances stored in the object table. The type of the object instances is the same type as the object table.VALUE(correlation_variable)CREATETYPEaddress_tASOBJECT (
hnoNUMBER,
streetVARCHAR2(40),
cityVARCHAR2(20),
zipVARCHAR2(5),
phoneVARCHAR2(10));
/

CREATETYPEpersonASOBJECT (
nameVARCHAR2(40),
dateofbirthDATE,
homeaddress address_t,
manager REF person);
/

CREATEORREPLACETYPEperson_tASOBJECT (
nameVARCHAR2(100),
ssnNUMBER)
NOTFINAL;
/

CREATETABLEpersons OF person_t;

INSERTINTOpersonsVALUES(person_t('Bob',1234));

SELECTVALUE(p)FROMpersons p;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

点击打开链接