如何在 Oracle 的 PL/SQL 中定义全局变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29362332/
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
How to define global variable in PL/SQL in Oracle?
提问by Samiul Al Hossaini
How can I define a global variable in PL/SQL which will be available in all functions / procedures / packages?
如何在 PL/SQL 中定义一个可用于所有函数/过程/包的全局变量？
Is it possible to define?
可以定义吗？
Or is there any alternate way to do this?
或者有没有其他方法可以做到这一点？
回答by Petr Pribyl
Create new package with your variable in package specification, like this:
使用包规范中的变量创建新包，如下所示：
CREATE PACKAGE my_public_package IS
my_var Number;
END;
Now you can access variable in any code with access to my_public_package
现在您可以通过访问 my_public_package 访问任何代码中的变量
...
my_public_package.my_var := 10;
...
回答by Lalit Kumar B
How can I define a global variable in PL/SQL which will be available in all functions / procedures / packages?
如何在 PL/SQL 中定义一个可用于所有函数/过程/包的全局变量？
You could use a Global Application Context variable.
您可以使用Global Application Context 变量。
An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace, for example, empno_ctx for an application context that retrieves employee IDs. Inside the context are the name-value pairs (an associative array): the name points to a location in memory that holds the value. An application can use the application context to access session information about a user, such as the user ID or other user-specific information, or a client ID, and then securely pass this data to the database. You can then use this information to either permit or prevent the user from accessing data through the application. You can use application contexts to authenticate both database and nondatabase users.
应用程序上下文是 Oracle 数据库存储在内存中的一组名称-值对。应用程序上下文有一个称为命名空间的标签，例如，empno_ctx 表示检索员工 ID 的应用程序上下文。上下文内部是名称-值对（关联数组）：名称指向内存中保存值的位置。应用程序可以使用应用程序上下文访问有关用户的会话信息，例如用户 ID 或其他特定于用户的信息，或客户端 ID，然后将此数据安全地传递到数据库。然后，您可以使用此信息来允许或阻止用户通过应用程序访问数据。您可以使用应用程序上下文来验证数据库和非数据库用户。
If you want that the variable value should be same across all the sessions for any PL/SQL object accessing the variable, then use a database tableto store the value.
如果您希望访问变量的任何 PL/SQL 对象的所有会话中的变量值都相同，则使用数据库表来存储该值。
For example, as T.Kyte suggests here
例如，正如 T.Kyte在这里建议的那样
CREATE TABLE global_value(x INT);
INSERT INTO global_value VALUES (0);
COMMIT;
CREATE OR replace PACKAGE get_global
AS
FUNCTION Val
RETURN NUMBER;
PROCEDURE set_val (
p_x IN NUMBER );
END;
/
CREATE OR replace PACKAGE BODY get_global
AS
FUNCTION Val
RETURN NUMBER
AS
l_x NUMBER;
BEGIN
SELECT x
INTO l_x
FROM global_value;
RETURN l_x;
END;
PROCEDURE Set_val(p_x IN NUMBER)
AS
PRAGMA autonomous_transaction;
BEGIN
UPDATE global_value
SET x = p_x;
COMMIT;
END;
END;
/
回答by haitam
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
