SYS_CONTEXT和USERENV(OPTION)函数

最新推荐文章于 2021-12-28 10:24:35 发布

原创最新推荐文章于 2021-12-28 10:24:35 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

oracle疑问集同时被 2 个专栏收录

81 篇文章

订阅专栏

oracle内置函数及包

27 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的SYS_CONTEXT函数的用途、语法、返回值类型、参数限制及内置命名空间等内容，并通过示例展示了如何使用SYS_CONTEXT函数获取上下文参数值。

SYS_CONTEXT

Syntax

Description of the illustration sys_context.gif

Purpose

SYS_CONTEXT returns the value ofparameter associated with the contextnamespace at the current instant（当前实例）. You can use this function in both SQL and PL/SQL statements.SYS_CONTEXT must be executed locally.

the context（context，即上下文，在数据库中是类似于表等概念，是一个数据库对象）namespace must already have been created, and the associated parameter and its value must also have beenset using the DBMS_SESSION.set_context procedure. Thenamespace must be a valid SQL identifier. Theparameter name can be any string. It is not case sensitive, but it cannot exceed 30 bytes in length.

The data type of the return value isVARCHAR2. The default maximum size of the return value is 256 bytes(默认返回值的数据类型最大所占字节为256字节). You canoverride this default （maximum size） by specifying the optional length parameter, which must be aNUMBER or a value that can be implicitly converted to NUMBER（参数length的数据类型必须是一个NUMBER类型或是能隐式转换为NUMBER的类型）. The valid range of values is 1 to 4000 bytes（参数length值的范围为1到4000）. If you specify an invalid value, then Oracle Database ignores it and uses the default.

Oracle provides the following built-in（即内建，也就是说已经预先在数据库中创建了的） namespaces:

USERENV - Describesthe current session（返回当前会话的信息）. The predefined（预定义） parameters of namespaceUSERENV are listed in Table 5-11.
SYS_SESSION_ROLES - Indicates whethera specified role is currently enabled for the session. This namespace is available starting with Oracle Database 11g Release 2 (11.2.0.4).

关于context这个数据库对象，参见：

Oracle Database Security Guide for information on using the application context feature in your application development
CREATE CONTEXT for information on creating user-defined context namespaces（如何创建用户自定义的context）
Oracle Database PL/SQL Packages and Types Reference for information onthe DBMS_SESSION.set_context procedure（如何使用theDBMS_SESSION.set_context来创建并设置context里的参数）

注释：

context类似于schema这个概念一样，就是一个容器，里面存放多个参数。

Examples

The following statement returns the name of the user who logged onto the database:

CONNECT OE/password
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
   FROM DUAL;

SYS_CONTEXT ('USERENV', 'SESSION_USER')
------------------------------------------------------
OE

The following example queries the SESSION_ROLES data dictionary view to show thatRESOURCE is the only role currently enabled for the session. It then uses theSYS_CONTEXT function to show that the RESOURCE role is currently enabled for the session and theDBA role is not.

CONNECT OE/password

SELECT role FROM session_roles;

ROLE
--------
RESOURCE

SELECT SYS_CONTEXT('SYS_SESSION_ROLES', 'RESOURCE')
  FROM DUAL

SYS_CONTEXT('SYS_SESSION_ROLES','RESOURCE')
--------------------------------------
TRUE

SELECT SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA')
  FROM DUAL;

SYS_CONTEXT('SYS_SESSION_ROLES','DBA')
--------------------------------------
FALSE

The following hypothetical example returns the group number that was set as the value for the attributegroup_no in the PL/SQL package that was associated with the context hr_apps when hr_apps was created（假设名为hr_apps的context已经创建好了，group_no是它的一个参数）:

SELECT SYS_CONTEXT ('hr_apps', 'group_no') "User Group" 
   FROM DUAL;

附加：

Oracle recommends that you use the SYS_CONTEXT function with the built-in USERENV namespace for current functionality（即USERENV(OPTION)函数）.

译文： SYS_CONTEXT函数可以以SYS_CONTEXT ('USERENV', 'OPTION') 取代USERENV(OPTION)函数的功能。

USERENV(OPTION)函数介绍：

USERENV(OPTION)，返回当前的会话信息.
　　　　OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
　　　　OPTION='LANGUAGE'返回数据库的字符集.
　　　　OPTION='SESSIONID'为当前会话标识符.
　　　　OPTION='ENTRYID'返回可审计的会话标识符.
　　　　OPTION='LANG'返回会话语言名称的ISO简记.
　　　　OPTION='INSTANCE'返回当前的实例.
　　 OPTION='terminal'返回当前计算机名

例子：　　　　SELECT USERENV('LANGUAGE') FROM DUAL;

疑问：

参数 ENTRYID 是什么意思？