人大金仓数据库KingbaseES 管理上下文经验分享

在 KingbaseES 中，通过使用 dbms_session 系统包中的过程来管理上下文。在使用 dbms_session 之前，需要将他添加到 kingbase.conf 文件的 shared_preload_libraries 中，并重启 KingbaseES 数据库：

shared_preload_libraries = 'dbms_session'

上下文使用的示例：

1. 创建 package

CREATE EXTENSION dbms_session;

set ora_input_emptystr_isnull=off;

create schema context_schema1;

grant usage on schema context_schema1 to public;

grant all on schema context_schema1 to public;

create or replace context context1 using context_schema1.test_package;

\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. 创建 context

create or replace context c_user01 using context_schema1.test_package;

3. 设置 namespace 的 key-value

call context_schema1.test_package.set_context('c_user01', 'u_k2', 'u_v2');

4. 查询

select sys_context('c_user01', 'u_k2');

SYS_CONTEXT('C_USER01','U_K2')

---------------------------------------------------

u_v2

1. 修改 u_k2 的值并查询

call context_schema1.test_package.set_context('c_user01', 'u_k2', 'u_v2222');

Select sys_context('c_user01', 'u_k2');

SYS_CONTEXT('C_USER01','U_K2')

---------------------------------------------------

u_v2222

6. 增加新的属性值 u_k3 并查询

call context_schema1.test_package.set_context('c_user01', 'u_k3', 'u_v3');

select sys_context('c_user01', 'u_k3');

SYS_CONTEXT('C_USER01','U_K3')

-------------------------------------------------

u_v3

7. 清除上下文 c_user01 属性 u_k2 的值并再次查询

call context_schema1.test_package.clear_context('c_user01', null,'u_k2');

select sys_context('c_user01', 'u_k2');

sys_context

-------------

(1 行记录)

select sys_context('c_user01', 'u_k3');

sys_context

-------------

u_v3

(1 行记录)

8. 清除上下文 c_user01

call context_schema1.test_package.clear_all_context('c_user01');

select sys_context('c_user01', 'u_k3');

sys_context

-------------

(1 行记录)