• Oracle创建上下文 SYS_CONTEXT


    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考试学习材料-手动实操课程

  • 相关阅读:
    推荐影视
    《算法设计》一、引言:某些典型的问题
    使用google地图API
    Python导入不同文件夹下模块
    Pycharm之远程编程
    linux之scp
    Photoshop脚本之jpg转换成eps
    win7重新安装win7
    查看修改swap空间大小
    用 EasyBCD 在 Win7/8 中硬盘安装 Ubuntu
  • 原文地址:https://www.cnblogs.com/plluoye/p/11098831.html
Copyright © 2020-2023  润新知