PL/SQL Record简介
PL/SQL Record是由一组存储在不同field中的相关项的集合。假设定义一个employee时,就会涉及到他的不同属性,比如姓名、薪水、雇佣日期等等。这些属性虽然在数据类型上是不一致但在逻辑上是相关的。PL/SQL Record就可以包含这样一个个的field和每个属性相对应,组成逻辑上的数据单元。因此,使用Record可以更好的更容易的组织和表达这些信息。
PL/SQL Record的定义有显式和隐式定义。隐式定义可以使用%ROWTYPE在一个Table上,它定义了一个可以表示数据库表的一行的Record类型。例如:
EmpEMPLOYEE%ROWTYPE;
下面显示定义一个Record Type
DECLARE
SALARYNUMBER(7,2),
HIRETIME TIMESTAMP);
BEGIN
...
END;
这里EMPLOYEE就是一个Record type的数据,它有3个field,分别为三种不同类型,在逻辑上又分别为姓名、薪水、雇佣日期。
PL/SQL Record应用场景
由于Record type的便捷性和易懂性,因此在Oracle Application中大量使用到这样类型的数据作为中间变量结合Cursor使用,或者作为stored procedure/stored function的参数传递数据。下面在PacakgeA中定义一个简单的stored procedure:
CREATE OR REPLACE package pkg as
type REC is record
( --simple record
AA number,
BB number
);
procedure proc_1(pa1 in REC, pa2 out NOCOPY REC);
end pkg;
/
CREATE OR REPLACE package body pkg as
procedure proc_1(pa1 in REC, pa2 out NOCOPY REC) as
begin
pa2.AA := pa1.AA + 1;
pa2.BB := pa1.BB + 1;
end proc_1;
end pkg;
/
这里procedure proc_1有两个参数都是Record type,对于这样的stored procedure,Oracle thin jdbc driver是没办法直接读取和调用的。所以,就需要一种workaround,在既不改变现有jdbc连接模式的前提下,同时又能很平滑的集成到原有系统中。
在这种workaround中,需要解决主要两个问题:
1. 第一是要读取到procedure proc_1得参数的metadata,其中特别是type REC的每个field的type信息,通过Oracle JDBC Thin dirver我们可以获取proc_1参数列表,但是对于PL/SQL Record type的参数driver并没有提供逻辑化的接口来获取其field的详细信息,因此,需要提供一种方法在不改变现有JDBC连接模式的前提下获取我们所需的信息,这里我们想到了Oracle提供的data dictionary:All_objects,All_arguments。经过,研究这两个table的属性列我们可以得到以下信息:
a. 从All_objects中我们可以找到所有用户定义的stored procedure和stored function等的系统标识,;
b. All_arguments包含关于每个存储在数据库(连接的用户在其上拥有EXECUTE权限中)的过程和函数的每个参数或自变量的信息。我可以根据从All_objects中获得的特定的stored procedure的标识,在All_arguments中检索其所有每个参数的具体信息,特别是PL/SQL Record type的field信息。
2. 第二是要能够直接调用并返回结果。由于Oracle JDBC Thin Driver不能直接调用含有PL/SQL Record type参数的procedure,我们需要创建一个Wrapper stored procedure,在这个Wrapper SP中所含的参数都是Oracle JDBC Thin Driver所能支持的类型。通过传参数的值给这个Wrapper SP,然后在Wrapper SP中做参数值转换,并用这些转换过后的值调用原有procedure,得到运行结果后,又将返回值转换,通过Wrapper SP返回运行结果。
发现SP/SF中是否使用了PL/SQL Record作为参数类型
一般使用时,用户是知道的stored procedure的参数的类型的,但是一般的动态执行procedure的系统,都是会先得到此procedure的参数的metadata信息,然后根据这些parameter的信息(特别是类型),去构造参数的值后用于调用procedure。所以,首先需要判断当前的procedure的所有parameters中是否含有PL/SQL Record类型,其中parameters既包括根级的,也包括根级下所有子孙级参数。例如:一个Table type的数据本身不是Record类型,但是如果它是Record的Table(A Table of Record),那么这个parameters也要当做Record type来处理。但是,Oracle JDBC Thin Driver不能提供对这种数据的类型的详细信息,我们可以按如下步骤使用Oracle提供的data dictionary查询需要的数据,做出相应的判断。
1. 按照Schema, package ,procedure name在All_objects中查询出可以在All_arguments中检索使用的Object_id;
2. 使用上述查询得到的object_id在All_arguments中检索此procedure的所有根和子孙参数,然后检索所有这些parameter的类型,一但查询到有PL/SQL RECORD类型,标定我们预定的标识变量为true,供系统做后续处理;如果没有查到,则按照一般类型的procedure继续处理。
生成原有SP/SF的Wrapper SP/SF的Create Script
在第一步检索结果中,如果查询到parameter中有是PL/SQL Record,接下来就需要对原始的stored procedure进行分析以便生成Wrapper SP的create script。这里,我们有以下的原则。
1. 不改变原来的参数个数和基本类型,只是对PL/SQL Record type的parameter做相应改变;
2. 将原有的PL/SQL Record type转换为Structs类型,各个field做对应转换;
3. 对PL/SQL Record type为子孙parameter的类型时,所有父类型做对应转换;
然后有下列步骤来生成create script:
1. 对所有需要转换的type做映射转换
i. PL/SQL Record -> Structs;
ii. TABLE ->TABLE, ARRAY ->ARRAY;
iii. Structs -> Structs;
iv. Simple type -> Simple type。
2. 对所有的新生成的type生成一对function做相应转换
i. Original type -> new type;
ii. New type -> Original type。
3. 生成Wrapper SP,完成所有调用过程;
i. 将所有Original的in, in/out参数使用New type -> Original type function做转换,得到Original type parameters;
ii. 使用第一步得到的Original type parameters调用Original stored procedure;
iii. 将Original stored procedure得到的结果中,所有out,in/out和返回类型使用Original type -> new type function做转换,通过Wrapper SP返回。
用下面的图,可以详细的解释转换的过程。
图1. PL/SQL Record转换图
执行Wrapper SP/SF的create script
将第二步生成的script执行,这里不是简单的执行,需要注意以下方面的处理:
1. 同一个stored procedure做转换时,同一个Record多次被使用时,只能生成一个Structs
对应Original PL/SQL Record type ;
2. 对不再需要的Wrapper SP,需要提供相应的Delete script来做rollback,删掉生成的Wrapper SP;
3. 对执行脚本时如果出现异常,需要做异常catch,做出相应的rollback,然后再re-run。
地址:http://space.itpub.net/618346/viewspace-606356#xspace-itemreply