在哪里保存 PL/SQL 常量？
您通常在哪里存储 PL/SQL 常量？在封装体层面？在规范中？我还看到有些人将常量保存在专门的包中，只是为了常量。该领域的最佳实践是什么？
谢谢。
Where do you normally store your PL/SQL constants? On the package-body level? In the specification? I've also seen some people holding constants in a specialized package just for constants. What are best practices in this area?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
在包主体或规范中包含常量的一个缺点是，当您重新编译包时，任何在 PGA 中具有包状态的用户会话都会收到 ORA-04068。出于这个原因，在一个大型开发环境中，我们采用了一种约定，即使用一个单独的仅规范包来保存每个包的常量（以及包全局变量，如果有的话）。然后，我们会强加一条规则，规定这些仅规范的包只能由它们的“所属”包引用 - 我们在代码审查时强制执行该规则。这不是一个完美的解决方案，但它当时对我们有用。
出于同样的原因，我永远不会推荐使用一个常量包来规则所有常量，因为每次有人需要引入新常量或修改现有常量时，所有用户会话都会收到 ORA-04068。
One downside to having constants in a package body or spec is that when you recompile the package, any user sessions that had the package state in the PGA would get ORA-04068. For this reason, in one large development environment we adopted the convention of having a separate spec-only package to hold the constants (and package globals if any) for each package. We'd then impose a rule saying that these spec-only packages were only allowed to be referenced by their "owning" package - which we enforced at code review. Not a perfect solution, but it worked for us at the time.
For the same reason, I'd never recommend one-constant-package-to-rule-them-all because every time someone needs to introduce a new constant, or modify an existing one, all user sessions get ORA-04068.
在许多情况下，您希望将它们保留在规范中，以便其他包可以使用它们，特别是在从包中调用函数和过程时作为参数。
只有当您想让它们对包保密时，您才应该将它们放入包体中。
对于那些与任何特定代码段无关但与整个模式相关的常量来说，拥有一个仅用于常量的包可能是一个好主意。
In many cases, you want to keep them in the specification so other packages can use them, especially as parameters when calling functions and procedures from your package.
Only when you want to keep them private to the package, you should put them int the body.
Having a package just for constants might be a good idea for those constants that are not related to any piece of code in particular, but relevant for the whole schema.
对于我们的应用程序，所有常量都在表中。使用一个简单的函数来提取它们。重新编译没问题，ORA-04068，...
For our application, all constants are in a table. A simple function is used to extract them. No problem with recompilation, ORA-04068, ...
我认为最好的选择。将“常量”存储在表中并创建一个通用函数来获取值。没有 04068
Best option in my opinion. Store the "constant" in a table and create a generic function to get the values. No 04068 ????
默认情况下，我希望常量位于包体中，除非您使用该常量作为公共包过程/函数之一的参数值或作为函数的返回值。
将常量放入包规范中的问题是，如果您需要更改常量的类型，其他包可能会无法使用该常量，因为它就在那里。如果该常量首先是私有的，那么您不需要对每个更改执行影响分析。
如果您需要存储诸如默认语言之类的内容，那么我会将这些内容封装在诸如 get_default_language 等函数中，并将常量保持为私有。
I would prefer the constants to be, by default, in the package body unless you use the constant as a parameter value for one of your public package procedures/functions or as a return value for your functions.
The problem with putting your constants in the package specification is that if you need to change the constant's type, other packages might fail that use the constant because it was just there. If the constant was private in the first place, then you don't need to perform an impact analysis for each change.
If you need to store contants like default language or stuff like that, then I would encapsulate those contants in functions like
get_default_languageetc. and keep the constants private.我担心“用一个包来规则常量”，因为包状态（常量、变量和代码）在第一次调用任何公共变量或包时会缓存在用户的 PGA 中。包常量如果是公共的，则应将其范围限定为包，并且仅由包的方法使用。
范围跨越包的常量应该位于带有描述的代码表中，并根据需要加入。毕竟，常量不是，变量也不是。拥有“常量”的键值对表使它们全部公开，并使得动态更改它们成为可能。
I'd have a concern about having "one package to rule the constants" because package state -- constants, variables, and code -- get cached in the user's PGA at first invocation of any public variable or package. A package constant, if public, should be scoped to the package, and used by only by the methods of the package.
A constant whose scope spans packages should be in a code table with a description, joined in as required. Constant's aren't and variables don't, after all. Having a key-value-pair table of "constants" makes them all public, and makes changing them dynamically possible.
如果我们使用与常量相同的方式命名的无参数函数而不是在包中使用常量会怎么样？在这种情况下，我们可以向包中添加新的函数/常量，更改函数的返回值，甚至可以自由地从包中删除某些函数，重新编译后我们将不会得到ORA 04068。
在包体中函数的实现部分，我们可以使用常量作为返回值，尽管这是不必要的，因为我们显然无法更改返回值。我们还可以在函数签名中使用一些特殊的性能技术，例如确定性甚至结果现金。
作为一个积极的副作用，我们能够在 sql 查询中使用常量。
What if we use parameterless function named the same way as constant instead of using constant in the package. In that case we can add new function/constant to the package, change returning value of function or even remove some function from the package freely and we'll not get ORA 04068 after recompile it.
Inside implementation part of the function in the package body we can use constant as a returning value, though it's unnecessarily because we obviously can't change returning value. Also we can use in function signature some special performance technics, such as deterministic or maybe even result cash.
As a positive side effect we get ability to use constant in sql queries.