PL/SQL 包开始/结束块之间的代码何时执行？
我的 PL/SQL 代码类似于以下代码片段：
create or replace
package body MY_PACKAGE as
type array_type is table of char index by varchar2(1);
lookup_array array_type;
function DO_SOMETHING(input nvarchar2)
return varchar2 as
begin
-- Do something here with lookup_array
end DO_SOMETHING;
procedure init_array as
begin
lookup_array('A') := 'a';
lookup_array('B') := 'b';
-- etc
end init_array;
begin
init_array;
end MY_PACKAGE;
它使用静态查找数组来处理提供给 DO_SOMETHING 的数据。我的问题是，什么时候调用 init_array 并将 Lookup_array 加载到内存中？包什么时候编译的？第一次调用是什么时候？是否被多次调用？有没有更好的方法来实现静态查找数组？
谢谢！
I have the PL/SQL code that is similar to the following snippet:
create or replace
package body MY_PACKAGE as
type array_type is table of char index by varchar2(1);
lookup_array array_type;
function DO_SOMETHING(input nvarchar2)
return varchar2 as
begin
-- Do something here with lookup_array
end DO_SOMETHING;
procedure init_array as
begin
lookup_array('A') := 'a';
lookup_array('B') := 'b';
-- etc
end init_array;
begin
init_array;
end MY_PACKAGE;
It uses a static lookup array to process data supplied to DO_SOMETHING. My question is, when is init_array called and lookup_array loaded into memory? When the package is compiled? When it is called for the first time? Is it called more than once? Is there a better way to implement a static lookup array?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以参考这个链接：
http://www.dba-oracle.com/plsql/t_plsql_lookup_tables.htm
“这意味着该过程是在包初始化期间执行的。因此，在会话的生命周期内，除非需要刷新缓存表，否则永远不会手动调用该过程。”
You can refer to this link:
http://www.dba-oracle.com/plsql/t_plsql_lookup_tables.htm
"This means the procedure is executed during package initialization. As a result during the lifetime of the session, the procedure is never called manually unless a refresh of the cached table is required."
Q1。 “什么时候调用
init_array并将lookup_array加载到内存中？什么时候编译包？什么时候第一次调用它？是否被多次调用？”init_array 在调用包中的任何函数或过程时被调用 - 即“及时”。每当包状态丢失时就会调用它（即每个会话可能会调用它多次）。
这对于包状态丢失的情况有影响 - 例如，当有人重新编译包时。在这种情况下，会发生以下顺序：
您的会话调用
do_something- 首先调用init_array，然后执行do_something- 现在您的会话在其 PGA 中分配了一些内存来保存数组。我的会话重新编译了包。在此阶段，为该包分配的会话内存被标记为“无效”。
您的会话调用
do_something- Oracle 检测到您的会话内存被标记为无效，并发出 ORA-04061“xxx 的现有状态已失效”。如果您的会话再次调用
do_something，它将继续执行，不会出现错误 - 它首先调用init_array，然后执行do_something。第二季度。 “有没有更好的方法来实现静态查找数组？”
只要您考虑到上述行为，我就没有看到这种方法有任何实际问题。
在某些情况下，我看到人们将 init 调用放在需要数组的每个函数/过程的开头 - 即每当调用 do_something 时，它都会检查是否需要初始化，如果所以调用
init_array。这种方法的优点是，您可以自定义init_array来仅初始化该函数/过程所需的位 - 如果init_array做了很多工作，这可能是有利的 - 这可能有助于避免每个会话的一次性启动开销。Q1. "When is
init_arraycalled andlookup_arrayloaded into memory? When the package is compiled? When it is called for the first time? Is it called more than once?"init_array is called when any function or procedure in the package is called - i.e. "just in time". It will be called whenever the package state is lost (i.e. it may be called more than once per session).
This has implications for the scenario where package state is lost - e.g. when someone recompiles the package. In this scenario, the following sequence occurs:
Your session calls
do_something-init_arrayis called first, thendo_somethingexecutes - your session now has some memory allocated in its PGA to hold the array.My session recompiles the package. At this stage, your session's memory that is allocated for that package is marked "invalid".
Your session calls
do_something- Oracle detects that your session's memory is marked invalid, and issues ORA-04061 "existing state of xxx has been invalidated".If your session calls
do_somethingagain, it proceeds without error - it first callsinit_arrayand then executesdo_something.Q2. "Is there a better way to implement a static lookup array?"
I don't see any real problems with this approach, so long as you take into account the behaviour described above.
In some cases I've seen people put the init call at the start of each function/procedure that needs the array - i.e. whenever
do_somethingis called, it checks to see if it needs to initialise, and if so callsinit_array. The advantage of this approach is that you can customiseinit_arrayto only initialise the bits that that function/procedure needs - which might be advantageous ifinit_arraydoes a lot of work - which might help to avoid a big one-time startup overhead for each session.