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屋！