Oracle创建上下文 SYS_CONTEXT
1. 系统默认的一些参数
set heading off
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;
-- 另一种展示方式
set serveroutput on
exec dbms_output.put_line(sys_context('USERENV','CURRENT_USER'));
2. 自定义上下文属性
create user king identified by king;
grant dba to king;
connect king/king
create table emp as select * from scott.emp;
create context emp_user using current_emp;
-- 创建包current_emp
CREATE OR REPLACE PACKAGE current_emp IS
PROCEDURE set_emp_info;
END;
/
CREATE OR REPLACE PACKAGE BODY current_emp IS
PROCEDURE set_emp_info IS
v_sal emp.sal%TYPE;
v_job emp.JOB%TYPE;
v_hiredate emp.HIREDATE%TYPE;
BEGIN
SELECT sal, JOB, HIREDATE
INTO v_sal, v_job, v_hiredate
FROM emp
WHERE ename = SYS_CONTEXT('USERENV', 'SESSION_USER');
DBMS_SESSION.SET_CONTEXT('emp_user', 'sal', v_sal);
DBMS_SESSION.SET_CONTEXT('emp_user', 'job', v_job);
DBMS_SESSION.SET_CONTEXT('emp_user', 'hiredate', v_hiredate);
DBMS_SESSION.SET_CONTEXT('emp_user', 'ename', SYS_CONTEXT('USERENV', 'SESSION_USER'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END;
/
-- 创建触发器
CREATE or REPLACE TRIGGER emp_logon
AFTER LOGON ON DATABASE
BEGIN
current_emp.set_emp_info;
END;
/
-- 验证
exit
sqlplus king/king
SET SERVEROUTPUT ON
EXEC dbms_output.put_line(sys_context('emp_user', 'SAL'));
EXEC dbms_output.put_line(sys_context('emp_user', 'job'));
EXEC dbms_output.put_line(sys_context('emp_user', 'hiredate'));
SELECT sys_context('emp_user', 'SAL') FROM DUAL;
--列出当前session 的所有上下文属性
sqlplus king/king
SET ECHO OFF
SET SERVEROUTPUT ON
DECLARE
list dbms_session.AppCtxTabTyp;
cnt number;
BEGIN
dbms_session.list_context(list, cnt);
IF cnt = 0 THEN
dbms_output.put_line('No contexts active.');
ELSE
FOR i IN 1 .. cnt LOOP
dbms_output.put_line(list(i).namespace || ' ' || list(i).attribute ||
' = ' || list(i).value);
END LOOP;
END IF;
END;
/
--查询
SELECT *
FROM dba_context
WHERE namespace = 'EMP_USER';
参考
【诗檀学院】ORACLE 11g OCM考试学习材料-手动实操课程