我们在哪里存储在PLSQL包规范中声明的全局变量 [英] Where do we store the global variables which are declared in the package specification in PLSQL

问题描述

在PLSQL中，我们在哪里存储程序包规范中声明的全局变量

解决方案

Oracle将全局变量存储在程序全局区域的一部分的内存结构中.您可以在.

只能通过PL/SQL访问变量，我们不能通过数据字典视图访问它们.

会话变量只能容纳在PGA中，它们不能溢出到磁盘上，因此我们可能必须谨慎加载太多数据.我们需要避免将大表存储在变量中，通常可以通过使用FOR循环或使用LIMIT子句来处理游标来完成此操作.

例如，以下代码将大量数据加载到一个简单的集合中.

--Load 20,000 large strings.  Takes about 10 seconds.
declare
    type string_nt is table of varchar2(4000);
    v_strings string_nt := string_nt();
begin
    for i in 1 .. 20000 loop
        v_strings.extend;
        v_strings(v_strings.count) := lpad('A', 4000, 'A');
    end loop;
    null;
end;
/

我们无法在数据字典中查看变量数据，但可以通过数据字典检查数据的大小.在这种情况下，存储80MB的原始数据大约需要105MB的内存:

--Maximum session PGA memory.
select value/1024/1024 mb
from v$sesstat
join v$statname
    on v$sesstat.statistic# = v$statname.statistic#
where v$statname.name = 'session pga memory max'
order by value desc;

(我的回答基于您正在询问的假设，因为您担心会存储大量数据.如果我的假设是错误的，请更新问题以准确解释您要查找的内容.)

In PLSQL where do we store the global variables declared in the package specification

解决方案

Oracle stores global variables in memory structures that are part of the Program Global Area. You can read about the PGA in the Memory Architecture chapter of the Oracle Concepts Guide.

Variables are only accessible through PL/SQL, we can't access them through data dictionary views.

Session variables can only fit in PGA, they cannot spill to disk, so we may have to be careful about loading too much data. We need to avoid storing large tables in variables, which we can often do by processing cursors with FOR loops or using a LIMIT clause.

For example, the following code loads a lot of data into a simple collection.

--Load 20,000 large strings.  Takes about 10 seconds.
declare
    type string_nt is table of varchar2(4000);
    v_strings string_nt := string_nt();
begin
    for i in 1 .. 20000 loop
        v_strings.extend;
        v_strings(v_strings.count) := lpad('A', 4000, 'A');
    end loop;
    null;
end;
/

We can't view the variable data in the data dictionary, but we can check the size of the data through the data dictionary. In this case, it takes about 105MB of memory to store 80MB of raw data:

--Maximum session PGA memory.
select value/1024/1024 mb
from v$sesstat
join v$statname
    on v$sesstat.statistic# = v$statname.statistic#
where v$statname.name = 'session pga memory max'
order by value desc;

(My answer is based on the assumption that you're asking because you're worried about storing lots of data. If my assumption is wrong, please update the question to explain precisely what you're looking for.)

这篇关于我们在哪里存储在PLSQL包规范中声明的全局变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！