oracle pl/sql 形参 指针,Oracle Pl / SQL：如何实现指向内存中记录类型的指针

在Oracle SQL中,

use REF and DEREF functions可以将逻辑指针传递给存储在数据库表中的对象.

但是PL / SQL中没有指针或引用这样的东西,所以你需要一个解决方法.

下面介绍PL / SQL中可能的解决方法.我提前为代码中的任何错误道歉.它的目的是展示方法,而不是用于生产.

方法1 – 缓存访问方法

通用的想法是通过缓存结果的函数访问实体：

create or replace package EntitytAccess as

procedure GetCompany1(

pCompanyId in number,

pCompany out nocopy pkg_company.typ_company

);

function GetCompany2(

pCompanyId in number

) return pkg_company.typ_company;

procedure ClearCompanyCache;

end;

访问包体：

create or replace package body EntitytAccess as

type typ_company_cache is table of pkg_company.typ_company index by number;

var_company_cache typ_company_cache;

procedure GetCompany1(

pCompanyId in number,

pCompany out nocopy pkg_company.typ_company

)

is

begin

if( var_company_cache.exists(pCompanyId) )

pCompany := var_company_cache(pCompanyId);

else

pCompany := pkg_company.get(pCompanyId);

var_company_cache(pCompanyId) := pCompany;

end if;

end;

function GetCompany2(

pCompanyId in number

) return pkg_company.typ_company

is

begin

if(not var_company_cache.exists(pCompanyId) )

var_company_cache(pCompanyId) := pkg_company.get(pCompanyId);

end if;

return var_company_cache(pCompanyId);

end;

procedure ClearCompanyCache

is

begin

var_company_cache.Delete;

end;

end;

用法：

declare

c pkg_company.typ_company;

e pkg_employee.typ_employee;

begin

EntityAccess.GetCompany2(12345,c);

for e in (select employeeid from employee where companyid=12345)

loop

e := pkg_employee.get(12345, c); -- c passed by reference in out nocopy

-- and c.companyid stored inside e.

pkg_employee.process(e); -- No need to pass company, but inside process()

-- method you need to call either

-- EntityAccess.GetCompany1(e.companyid, var_c)

-- or

-- var_c := EntityAccess.GetCompany2(e.companyid)

end loop;

end;

方法2 – 环境包

由于包状态属于一个会话,因此您可以使用包变量来保存当前处理状态并在需要时引用它.

create or replace package ProcessEnvironment as

var_current_company pkg_company.typ_company;

-- may be more "global" variables here

end;

/

create or replace package body ProcessEnvironment as

end;

用法：

declare

e pkg_employee.typ_employee;

begin

ProcessEnvironmant.var_current_company := pkg_company.get(12345);

for e in (select employeeid from employee where companyid=12345)

loop

e := pkg_employee.get(12345, ProcessEnvironmant.var_current_company);

-- c passed by reference in out nocopy

-- and c.companyid stored inside e.

pkg_employee.process(e); -- No need to pass company, but inside process()

-- method you references

-- ProcessEnvironmant.var_current_company

-- with appropriate checks

end loop;

end;

混合的方法

似乎在方法1实例中从集合中复制了实例,尤其是在使用函数GetCompany2()访问时.复制构造函数可能足够快但产生一些开销.

对于方法2,在业务逻辑功能的代码中必须存在一些检查,因此从另一个角度来看它是维护开销.

要处理这两个问题,您可以使用缓存,但在包中只保留一个值：

create or replace package EntitytAccess as

procedure GetCompany(

pCompanyId in number,

pCompany out nocopy pkg_company.typ_company

);

end;

包装体：

create or replace package body EntitytAccess as

var_cached_company pkg_company.typ_company;

procedure GetCompany(

pCompanyId in number,

pCompany out nocopy pkg_company.typ_company

)

is

begin

if( (var_cached_company is null)

or

(var_cached_company.comanyid != pCompanyId)

)then

var_company_cache := pkg_company.get(pCompanyId);

end if;

pCompany := var_cached_company;

end;

end;

用法：

declare

c pkg_company.typ_company;

e pkg_employee.typ_employee;

begin

EntityAccess.GetCompany(12345,c);

for e in (select employeeid from employee where companyid= c.companyid)

loop

e := pkg_employee.get(c.companyid , c); -- c passed by reference in out nocopy

-- and c.companyid stored inside e.

pkg_employee.process(e); -- No need to pass company, but inside process()

-- method you need to call

-- EntityAccess.GetCompany(e.companyid, var_c)

-- where var_c is company object variable.

end loop;

end;