PL/SQL:如何声明会话变量? [英] PL/SQL: How do I declare session variables?
问题描述
我如何在PL/SQL中声明一个会话变量-该变量仅在会话期间持续存在,而不必将其存储在数据库本身中?
How do I declare a session variable in PL/SQL - one that will persist for the duration of the session only, without me having to store it in the database itself?
推荐答案
您可以使用用户创建的上下文"来存储会话中多个单元之间共享的数据.
You can use a 'user-created context' to store data shared across multiple units within a session.
首先,创建一个上下文:
First, create a context:
CREATE CONTEXT SYS_CONTEXT ('userenv', 'current_schema')|| '_ctx' USING PKG_COMMON
第二,创建一个可以管理您的上下文的程序包:
Second, create a package that would manage your context:
CREATE OR REPLACE PACKAGE PKG_COMMON
IS
common_ctx_name CONSTANT VARCHAR2 (60)
:= SYS_CONTEXT ('userenv', 'current_schema')
|| '_ctx';
FUNCTION fcn_get_context_name RETURN VARCHAR2;
PROCEDURE prc_set_context_value (var_name VARCHAR2, var_value NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY PKG_COMMON
IS
FUNCTION fcn_get_context_name
RETURN VARCHAR2
IS
BEGIN
RETURN common_ctx_name;
END;
PROCEDURE prc_set_context_value (var_name VARCHAR2, var_value NUMBER)
IS
BEGIN
DBMS_SESSION.set_context (common_ctx_name, var_name, var_value);
END;
END;
prc_set_context_value可以更高级,这只是一个示例. 创建上下文和包后,您就可以开始使用它们了. 使用过程调用
The prc_set_context_value can be more advanced, that's just an example. With the context and the package created you can start using them. Set a context variable using a procedure call
begin
PKG_COMMON.prc_set_context_value('MyVariable', 9000)
end;
并在任何地方使用它-任何过程,程序包,函数或事件视图.
and use it anywhere - any procedure, package, function or event a view.
CREATE VIEW V_TEST AS
SELECT ID, LOGIN, NAME
FROM USERS
WHERE ROLE_ID = SYS_CONTEXT(PKG_COMMON.FCN_GET_CONTEXT_NAME, 'MyVariable')
有关更多信息,请参见 http://www.psoug.org/reference/sys_context.html
For more information see http://www.psoug.org/reference/sys_context.html
这篇关于PL/SQL:如何声明会话变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!