金仓数据库KingbaseES之context上下文功能使用

 1、表空间限额使用前准备

1.1 安装启动数据库

1、成功安装kingbase数据库并为当前安装用户授予可执行权限

2、启动数据库，建立ksql连接

1.2 创建extension

执行语句create extension dbms_session;

1.3 参数设置

执行语句set ora_input_emptystr_isnull=off;

2、context上下文功能使用

2.1 context上下文功能使用

1、创建模式以及赋权

create schema context_schema1;

grant usage on schema context_schema1 to public;

grant all on schema context_schema1 to public;

2、创建context

create or replace context context1 using context_schema1.test_package;

3、创建关联包

\set SQLTERM /

create or replace package context_schema1.test_package as

procedure set_context(ts_name varchar, key varchar, value varchar);

procedure clear_context(ts_name varchar, client_identifier varchar, key varchar);

procedure clear_all_context(ts_name varchar);

end test_package;

/

create or replace package body context_schema1.test_package as

procedure set_context(ts_name varchar, key varchar, value varchar) as

begin

dbms_session.set_context(ts_name, key, value);

end;

procedure clear_context(ts_name varchar, client_identifier varchar, key varchar) as

begin dbms_session.clear_context(ts_name, client_identifier, key);

end;

procedure clear_all_context(ts_name varchar) as

begin dbms_session.clear_all_context(ts_name);

end;

end test_package;

/

\set SQLTERM ;

2.2 给context赋值以及查看

call context_schema1.test_package.set_context('context1','aa', 'caaaddd');

call context_schema1.test_package.set_context('context1','bb', 'caaaddd');

call context_schema1.test_package.set_context('context1','cc', 'caaaddd');

call context_schema1.test_package.set_context('context1','dd', 'caaaddd');

select * from V$context;

2.3 更改value值

call context_schema1.test_package.set_context('context1','aa', 'aa');

select sys_context('context1','aa');

     

​​​​​​​2.4 清除key-value

call context_schema1.test_package.clear_context('context1','','aa');

select * from v$context;

​​​​​​​2.5 Userenv和sys_context

select userenv('language') from dual;

select sys_context ('userenv', 'session_user') from dual;

2.6删除context

drop context context1;

        

​​​​​​​