Decode函数的语法
Decode函数的语法结构如下:
decode (expression, arch_1, result_1)
decode (expression, arch_1, result_1, arch_2, result_2)
decode (expression, arch_1, result_1, arch_2, result_2, ...., arch_n, result_n)
decode (expression, arch_1, result_1, default)落秋
decode (expression, arch_1, result_1, arch_2, result_2, default)
decode (expression, arch_1, result_1, arch_2, result_2, ...., arch_n, result_n, default)
decode函数⽐较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。
以下是⼀个简单测试，⽤于说明Decode函数的⽤法:
SQL> create table t as lect urname,default_tablespace,lock_date from dba_urs;Table created.SQL> lect * from
t;USERNAME DEFAULT_TABLESPACE LOCK_DATE------------------------------ ------------------------------ ---------SYS SYSTEMSYSTEM SYSTEMOUTLN SYSTEMCSMIG SYSTEMSCOTT SYSTEMEYGLE USERSDBSNMP SYSTEMWMSYS SYSTEM 20-OCT-048 rows lected.SQL> lect urname,decode(lock_date,null,'unlocked','locked') status from
t;USERNAME STATUS------------------------------ --------SYS unlockedSYSTEM unlockedOUTLN unlockedCSMIG unlockedSCOTT unlockedEYGLE unlockedDBSNMP unlockedWMSYS locked8 rows lected.SQL> lect日漫头像
urname,decode(lock_date,null,'unlocked') status from t;USERNAME STATUS------------------------------ --------SYS unlockedSYSTEM unlockedOUTLN unlockedCSMIG unlockedSCOTT unlockedEYGLE unlockedDBSNMP unlockedWMSYS8 rows lected. ------------------------------------------------------------------------------------------------------------------------------------------------
urenv命令的语法:
USERENV returns information about the current ssion. This information can be uful for writing an application-specific audit trail table or for determining the language-specific characters currently ud by your ssion. You cannot u USERENV in the condition of a CHECK constraint. Table 6-3 describes the values for the parameter argument.
简的成语All calls to USERENV return VARCHAR2 data except for calls with the SESSIONID, ENTRYID, and COMMITSCN parameters, which return NUMBER.
Table 6-3 Parameters of the USERENV Function
Parameter Return Value
CLIENT_INFO
CLIENT_INFO returns up to 64 bytes of ur ssion information that can be stored by an application using the
DBMS_APPLICATION_INFO package.
Caution: Some commercial applications may be using this context value. Check the applicable docu
mentation for tho applications to determine what restrictions they may impo on u of this context area.
See Also:
Oracle9i Databa Concepts for more on application context
CREATE CONTEXT and SYS_CONTEXT
ENTRYID
ENTRYID returns available auditing entry identifier. You cannot u this attribute in distributed SQL statements. To u this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be t to TRUE.
ISDBA
ISDBA returns 'TRUE' if the ur has been authenticated as having DBA privileges either through the operating system or through a password file.
LANG
LANG returns the ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
LANGUAGE
LANGUAGE returns the language and territory currently ud by your ssion along with the databa character t in this form:
language_territory.charactert
SESSIONID
SESSIONID returns your auditing ssion identifier. You cannot u this attribute in distributed SQL statements.
TERMINAL
TERMINAL returns the operating system identifier for your current ssion's terminal. In distributed SQL statements, this attribute returns the identifier for your local ssion. In a distributed environment, this is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations.
Examples
The following example returns the LANGUAGE parameter of the current ssion:
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
Language
-----------------------------------
AMERICAN_AMERICA.WE8DEC
----------------------------------------------------------------------------------------------------------------------------------------------------
sys_contex()函数作⽤:
Oracle9i(Version 9.2)SYS_CONTEXT函数的⽤法
这个函数在写⼀些触发器，函数的时候⾮常有⽤处。
⽤法：SELECT sys_context('USERENV', '<parameter>') FROM dual;
第⼆个参数的可选值：
AUTHENTICATION_DATA
Data being ud to authenticate the login ur. For X.503 certificate authenticated ssions, this field returns the context of the certificate in HEX2 format.
Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax. Values of up to 4000 are accepted. This is the only attribute of USERENV for which Oracle implements such a change.
AUTHENTICATION_TYPE
How the ur was authenticated:
DATABASE: urname/password authentication
OS: operating system external ur authentication
NETWORK: network protocol or ANO authentication
万年宫PROXY: OCI proxy connection authentication
BG_JOB_ID
Job ID of the current ssion if it was established by an Oracle background process. Null if the ssion was not established by a background process.
CLIENT_INFO
Returns up to 64 bytes of ur ssion information that can be stored by an application using the
DBMS_APPLICATION_INFO package.
CURRENT_SCHEMA
Name of the default schema being ud in the current schema. This value can be changed during the ssion with an ALTER SESSION SET CURRENT_SCHEMA statement.
CURRENT_SCHEMAID
Identifier of the default schema being ud in the current ssion.
CURRENT_USER
The name of the ur who privilege the current ssion is under.
CURRENT_USERID
Ur ID of the ur who privilege the current ssion is under.
DB_DOMAIN
Domain of the databa as specified in the DB_DOMAIN initialization parameter.
DB_NAME
Name of the databa as specified in the DB_NAME initialization parameter.
ENTRYID
The available auditing entry identifier. You cannot u this option in distributed SQL statements. To u this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be t to true.
EXTERNAL_NAME
External name of the databa ur. For SSL authenticated ssions using v.503 certificates, this field returns the distinguished name (DN) stored in the ur certificate.
FG_JOB_ID
Job ID of the current ssion if it was established by a client foreground process. Null if the ssion was not established by a foreground process.
HOST
Name of the host machine from which the client has connected.
INSTANCE
The instance identification number of the current instance.
IP_ADDRESS
IP address of the machine from which the client is connected.
ISDBA
TRUE if you are logged on as SYS.网站申请
LANG
The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
LANGUAGE
The language and territory currently ud by your ssion, along with the databa character t, in the
form:language_territory.charactert.
NETWORK_PROTOCOL
Network protocol being ud for communication, as specified in the 'PROTOCOL=protocol' portion of the connect string.
感谢老师NLS_CALENDAR
The current calendar of the current ssion.
NLS_CURRENCY
The currency of the current ssion.
NLS_DATE_FORMAT
The date format for the ssion.
NLS_DATE_LANGUAGE
The language ud for expressing dates.
小白菜怎么炒好吃
NLS_SORT BINARY
or the linguistic sort basis.
NLS_TERRITORY
The territory of the current ssion.
OS_USER
Operating system urname of the client process that initiated the databa ssion.
PROXY_USER
Name of the databa ur who opened the current ssion on behalf of SESSION_USER.
PROXY_USERID
Identifier of the databa ur who opened the current ssion on behalf of SESSION_USER.
SESSION_USER
Databa ur name by which the current ur is authenticated. This value remains the same throughout the duration of the ssion.
SESSION_USERID
Identifier of the databa ur name by which the current ur is authenticated.
SESSIONID
The auditing ssion identifier. You cannot u this option in distributed SQL statements.
TERMINAL
The operating system identifier for the client of the current ssion. In distributed SQL statements, this option returns the identifier for your local ssion. In a distributed environment, this is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations.qq说说文案
(The return length of this parameter may vary by operating system.)
