oracle PL/SQL：如何声明会话变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/301369/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL: How do I declare session variables?
提问by user38871
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?
我如何在 PL/SQL 中声明一个会话变量——一个只在会话期间持续存在的变量，而我不必将它存储在数据库本身中？
回答by user34850
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;
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
prc_set_context_value 可以更高级，这只是一个例子。创建上下文和包后，您可以开始使用它们。使用过程调用设置上下文变量
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')
For more information see http://www.psoug.org/reference/sys_context.html
回答by WW.
You create a package level variable. This is a minimal example:
您创建一个包级别变量。这是一个最小的例子：
CREATE OR REPLACE PACKAGE my_package
AS
FUNCTION get_a RETURN NUMBER;
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package
AS
a NUMBER(20);
FUNCTION get_a
RETURN NUMBER
IS
BEGIN
RETURN a;
END get_a;
END my_package;
/
If you do this you should read up on (and handle correctly) ORA-04068errors. Each database session will have it's own value for a. You can try this with:
如果您这样做，您应该阅读（并正确处理）ORA-04068错误。每个数据库会话都有它自己的 a 值。你可以试试这个：
SELECT my_package.get_a FROM DUAL;
回答by Andreas Dietrich
I like to use a short, but intuitive syntaxand thus would create e.g. some ctxpackagethat just provides one function to set and get some global "variables".
(valid for the current session only ; in my case no need to implement it as user-created-context variables, but easily changeable to it under the hood ; example with some foo varchar2and bar numbervars)
我喜欢用一个短，但直观的语法，因此会造成一些如ctx包，只是提供一个功能设置，并得到一些全球性的“变量”。
（仅对当前会话有效；在我的情况下，不需要将其实现为用户创建的上下文变量，但可以在后台轻松更改它；使用 somefoo varchar2和bar numbervars 的示例）
usage:
用法：
select ctx.foo from dual -- => null (init)
select ctx.foo('a') from dual -- => 'a'
select ctx.foo('b') from dual ; select ctx.foo from dual -- => 'b', 'b'
.
.
-- (optimizer should cause the subquerys unselected columns not to be executed:)
select 'ups' from (select ctx.foo('a') from dual) ; select ctx.foo from dual -- => null
select ctx.bar(1.5) from dual ; select ctx.bar from dual -- => 1.5, 1.5
-- ...
package header:
包头：
create or replace package ctx as
-- select ctx.foo from dual -- => null (init)
-- select ctx.foo('a') from dual -- => 'a'
-- select ctx.foo('b') from dual ; select ctx.foo from dual -- => 'b', 'b'
-- (optimizer should cause the subquerys unselected columns not to be executed:)
-- select 'ups' from (select ctx.foo('a') from dual) ; select ctx.foo from dual
-- => null
-- parallel_enable for queries since it should not change inside of them
function foo( set varchar2 := null ) return varchar2 parallel_enable;
-- (samples like in foo above as executable test comments like in foo above skipped for
-- brevity)
function bar( set number := null ) return number parallel_enable;
end;
package body:
包体：
create or replace package body ctx as
foo_ varchar2(30); -- e.g. 'blabla'
bar_ number;
-- internal helper function for varchars
function set_if_not_null( ref in out varchar2, val varchar2 ) return varchar2 as
begin
if val is not null then ref := val; end if;
return ref ;
end;
-- internal helper function for numbers
function set_if_not_null( ref in out number, val number ) return number as begin
if val is not null then ref := val; end if;
return ref ;
end;
-- (same test comments like in foo above skipped for brevity)
function foo( set varchar2 := null ) return varchar2 parallel_enable as begin
return set_if_not_null( foo_, set ) ;
end;
-- (same test comments like in foo above skipped for brevity)
function bar( set number := null ) return number parallel_enable as begin
return set_if_not_null( bar_, set ) ;
end;
end;
If you know that the variable (foo) may change within a single query then remove parallel_enable, which should otherwise be more performant if queries are parallelizable.
如果您知道变量 ( foo) 可能会在单个查询中发生变化，则 removeparallel_enable，否则如果查询是可并行的，它的性能应该会更高。
Depending on ones needs one could of course add some foo_reset()to set it to null etc..
根据需要，当然可以添加一些foo_reset()以将其设置为 null 等。
