了解Table和Transaction API之间的差异 [英] Understanding the differences between Table and Transaction API's

问题描述

朋友，

这个问问汤姆线程，我通过另一个SO问题发现，提到表和事务API，我正在试图了解它们之间的区别。

表API（TAPI）是无法访问底层表的地方，有getter& 设置者获取信息。

例如，选择一个地址我将：

  the_address： = get_address（address_id）; 
  

而不是：

 从some_table 
中选择the_address 
其中identifier = address_id 
  

然后更改地址我将调用另一个TAPI来处理更改：

  ... 
 change_address（address_id，new_address）; 
 ... 
  

事务API（XAPI）再次是没有直接访问的地方修改表中的信息，但我可以从中选择？ （这是我的理解有点朦胧）

要选择一个地址，我会：

 从some_table 
中选择the_address 
其中identifier = address_id 
  

然后更改它将调用

  ... 
 change_address address_id，new_address）; 
 ... 
  

所以在TAPI和XAPI之间唯一的区别是从数据库检索记录的方法，即选择与PL / SQL调用？

是吗？或者我完全错过了这一点？

解决方案

让我们从Table API开始吧。这是通过PL / SQL API调解对表的访问的做法。因此，我们每个表都有一个包，它应该从数据字典中生成。该软件包提供了一组用于向表发出DML的过程和一些用于检索数据的功能的过程。

相比之下，事务API代表工作单位。它根本不暴露关于底层数据库对象的任何信息。事务API提供更好的封装和更清洁的界面。

对比度就是这样。考虑创建新部门的这些业务规则：

1. 新部门必须具有名称和位置


2. 新部门必须有一名经理，他们必须是现有的员工。


3. 其他现有员工可以转移到新部门。


4. 新员工可以分配到新部门。


5. 新部门必须至少分配两名员工（包括经理）

使用表API，事务可能如下所示：

  DECLARE 
 dno pls_integer; 
 emp_count pls_integer; 
 BEGIN 
 dept_utils.insert_one_rec（：new_name，：new_loc，dno）; 
 emp_utils.update_one_rec（：new_mgr_no，p_job =>'MGR'，p_deptno => dno）; 
 emp_utils.update_multi_recs（：transfer_emp_array，p_deptno => dno）; 
 FOR idx IN：new_hires_array.FIRST ..：new_hires_array.LAST LOOP 
：new_hires_array（idx）.deptno：= dno; 
 END LOOP; 
 emp_utils.insert_multi_recs（：new_hires_array）; 
 emp_count：= emp_utils.get_count（p_deptno => dno）; 
 IF emp_count< 2 THEN 
 raise_application_error（-20000，雇员不足）; 
 END IF; 
 END; 
 / 
  

而使用事务API更简单：

  DECLARE 
 dno subtype_pkg.deptno; 
 BEGIN 
 dept_txns.create_new_dept（：new_name 
，：new_loc 
，：new_mgr_no 
，：transfer_emps_array 
，：new_hires_array 
，dno） ; 
 END; 
 / 
  

那么为什么检索数据的区别？因为事务API方法阻止通用的 get（）函数，以避免无意识地使用无效的SELECT语句。

例如，如果你只想要一个雇员的工资和佣金，查询这个...

  select sal，comm 
 into l_sal，l_comm 
 from emp 
其中empno = p_eno; 
  

...比执行此更好...

  l_emprec：= emp_utils.get_whole_row（p_eno）; 
  

...特别是如果员工记录有LOB列。

它也比以下更有效：

  l_sql：= emp_utils.get_sal（p_eno）; 
 l_comm：= emp_utils.get_comm（p_eno）; 
  

...如果每个这些getter执行单独的SELECT语句。哪个不是未知的：这是一个糟糕的OO实践，导致可怕的数据库性能。

表API的支持者认为他们是基于他们屏蔽开发人员不需要考虑SQL。不太赞成的人们不喜欢表API ，原因相同。即使是最好的表API也可以鼓励RBAR处理。如果我们每次编写自己的SQL，那么我们更有可能选择基于集合的方法。

使用事务性APis并不一定排除使用 get_resultset（）函数。在查询API中仍然有很多价值。但是，与单独表格中的SELECTs相比，实现联接的视图和函数更有可能被构建。

顺便提一句，我认为在表API之上构建事务API不是好的主意：我们仍然拥有SQL语句，而不是仔细的连接。

作为一个例证，这里是一个事务性API更新每个员工工资的不同实现在一个区域（REgion是组织的大型部分;部门被分配到区域）。

第一个版本没有纯SQL只是表API调用，我不要认为这是一个稻草人：它使用了我在Table API包中看到的那种功能（尽管有些使用动态SQL而不是命名的SET_XXX（）过程）。

 创建或替换过程adjust_sal_by_region 
（ptregion in dept.region％type 
，p_sal_adjustment in number） 
 as 
 emps_rc sys_refcursor; 
 emp_rec emp％rowtype; 
 depts_rc sys_refcursor; 
 dept_rec dept％rowtype; 
 begin 
 depts_rc：= dept_utils.get_depts_by_region（p_region）; 
 
<< depts>> 
 loop 
 fetch depts_rc into dept_rec; 
 exit depts_rc％notfound; 
 emps_rc = = emp_utils.get_emps_by_dept（dept_rec.deptno）; 
 
<< emps>> 
 loop 
 fetch emps_rc into emp_rec; 
退出时emps_rc％notfound; 
 emp_rec.sal：= emp_rec.sal * p_sal_adjustment; 
 emp_utils.set_sal（emp_rec.empno，emp_rec.sal）; 
 end loop emps; 
 
 end loop depts; 
 
 end adjust_sal_by_region; 
 / 
  

SQL中的等效实现：

 创建或替换过程adjust_sal_by_region 
（p_region in dept.region％type 
，p_sal_adjustment in number）
 as 
 begin 
更新emp e 
 set e.sal = e.sal * p_sal_adjustment 
其中e.deptno in（select d.deptno 
 from dept d 
 where d.region = p_region）; 
 end adjust_sal_by_region; 
 / 
  

这比以前的嵌套游标循环和单行更新更好版。这是因为在SQL中写入我们需要按地区选择员工的联接是一个契机。使用Table API要难得多，因为Region不是Employees的关键。

为了公平起见，如果我们有一个支持动态SQL的表API，事情会更好，但仍然不太理想：

pre $ 创建或替换过程adjust_sal_by_region
（p_region in dept.region％type
，p_sal_adjustment in number）
as
emps_rc sys_refcursor;
emp_rec emp％rowtype;
begin
emps_rc：= emp_utils.get_all_emps（
p_where_clause =>'deptno in（select d.deptno
from dept d where d.region ='|| p_region || '）'）;

<< emps>>
loop
fetch emps_rc into emp_rec;
退出时emps_rc％notfound;
emp_rec.sal：= emp_rec.sal * p_sal_adjustment;
emp_utils.set_sal（emp_rec.empno，emp_rec.sal）;
end loop emps;

end adjust_sal_by_region;
/


---

最后一个字说完所有这一切，有一些情况下，表格API可以是有用的，我们只想以相当标准的方式与单个表进行交互的情况。一个明显的情况可能是从其他系统产生或消耗数据馈送，例如ETL。

如果您想调查使用Table API，最好的开始之处是Steven Feuerstein的 Quest CodeGen Utility （以前称为QNXO）。这与TAPI生成器一样好，它是免费的。

Friends,

This Ask Tom thread which I found via another SO question, mentions Table and Transactional API's and I'm trying to understand the difference between them.

A Table API (TAPI) is where there is no access to the underlying tables and there are "getters" & "setters" to obtain information.

For example to select an address I would:

   the_address := get_address(address_id);

Instead of:

   select the_address
   from some_table
   where identifier = address_id

And then to change the address I would invoke another TAPI which takes care of the change:

   ...
   change_address(address_id, new_address);
   ...

A Transactional API (XAPI) is again where there is no direct access to modify the information in the table but I can select from it? (this is where my understanding is kind of hazy)

To select an address I would:

   select the_address
   from some_table
   where identifier = address_id

and then to change it I would call

   ...
   change_address(address_id, new_address);
   ...

So the only difference I can see between a TAPI and a XAPI is the method in which a record is retrieved from the database, i.e. a Select Versus a PL/SQL call?

Is that it? or have I missed the point entirely?

解决方案

Let's start with the Table API. This is the practice of mediating access to tables through a PL/SQL API. So, we have a package per table, which should be generated from the data dictionary. The package presents a standard set of procedures for issuing DML against the table and some functions for retrieving data.

By comparison a Transactional API represents a Unit Of Work. It doesn't expose any information about the underlying database objects at all. Transactional APIs offer better encapsulation, and a cleaner interface.

The contrast is like this. Consider these business rules for creating a new Department:

1. The new Department must have a Name and Location
2. The new Department must have a manager, who must be an existing Employee
3. Other existing Employees may be transferred to the new Department
4. New employees may be assigned to the new Department
5. The new Department must have at least two Employees assigned (including the manager)

Using Table APIs the transaction might look something like this:

DECLARE
    dno pls_integer;
    emp_count pls_integer;
BEGIN
    dept_utils.insert_one_rec(:new_name, :new_loc, dno);
    emp_utils.update_one_rec(:new_mgr_no ,p_job=>'MGR’ ,p_deptno=>dno);
    emp_utils.update_multi_recs(:transfer_emp_array, p_deptno=>dno);
    FOR idx IN :new_hires_array.FIRST..:new_hires_array.LAST LOOP
        :new_hires_array(idx).deptno := dno;
    END LOOP;
    emp_utils.insert_multi_recs(:new_hires_array);
    emp_count := emp_utils.get_count(p_deptno=>dno); 
    IF emp_count < 2 THEN
        raise_application_error(-20000, ‘Not enough employees’);
    END IF;
END;
/

Whereas with a Transactional API it is much simpler:

DECLARE
    dno subtype_pkg.deptno;
BEGIN
    dept_txns.create_new_dept(:new_name
                                , :new_loc
                                , :new_mgr_no
                                , :transfer_emps_array
                                , :new_hires_array
                                , dno);
END;
/

So why the difference in retrieving data? Because the Transactional API approach discourages generic get() functions in order to avoid the mindless use of inefficient SELECT statements.

For example, if you just want the salary and commission for an Employee, querying this ...

select sal, comm
into l_sal, l_comm
from emp
where empno = p_eno;

... is better than executing this ...

l_emprec := emp_utils.get_whole_row(p_eno);

...especially if the Employee record has LOB columns.

It is also more efficient than:

l_sql := emp_utils.get_sal(p_eno);
l_comm := emp_utils.get_comm(p_eno);

... if each of those getters executes a separate SELECT statement. Which is not unknown: it's a bad OO practice that leads to horrible database performance.

The proponents of Table APIs argue for them on the basis that they shield the developer from needing to think about SQL. The people who deprecate them dislike Table APIs for the very same reason. Even the best Table APIs tend to encourage RBAR processing. If we write our own SQL each time we're more likely to choose a set-based approach.

Using Transactional APis doesn't necessarily rule out the use of get_resultset() functions. There is still a lot of value in a querying API. But it's more likely to be built out of views and functions implementing joins than SELECTs on individual tables.

Incidentally, I think building Transactional APIs on top of Table APIs is not a good idea: we still have siloed SQL statements instead of carefully written joins.

As an illustration, here are to different implemntations of a transactional API to update the salary of every Employee in a Region (REgion being a large scale section of the organisation; Departments are assigned to Regions).

The first version has no pure SQL just Table API calls, I don't think this is a straw man: it uses the sort of functionality I have seen in Table API packages (although some use dynamic SQL rather than named SET_XXX() procedures).

create or replace procedure adjust_sal_by_region
    (p_region in dept.region%type
           , p_sal_adjustment in number )
as
    emps_rc sys_refcursor;
    emp_rec emp%rowtype;
    depts_rc sys_refcursor;
    dept_rec dept%rowtype;
begin
    depts_rc := dept_utils.get_depts_by_region(p_region);

    << depts >>
    loop
        fetch depts_rc into dept_rec;
        exit when depts_rc%notfound;
        emps_rc := emp_utils.get_emps_by_dept(dept_rec.deptno);

        << emps >>
        loop
            fetch emps_rc into emp_rec;
            exit when emps_rc%notfound;
            emp_rec.sal := emp_rec.sal * p_sal_adjustment;
            emp_utils.set_sal(emp_rec.empno, emp_rec.sal);
        end loop emps;

    end loop depts;

end adjust_sal_by_region;
/

The equivalent implementation in SQL:

create or replace procedure adjust_sal_by_region
    (p_region in dept.region%type
           , p_sal_adjustment in number )
as
begin
    update emp e
    set e.sal = e.sal * p_sal_adjustment
    where e.deptno in ( select d.deptno 
                        from dept d
                        where d.region = p_region );
end adjust_sal_by_region;
/

This is much nicer than the nested cursor loops and single row update of the previous version. This is because in SQL it is a cinch to write the join we need to select Employees by Region. It is a lot harder using a Table API, because Region is not a key of Employees.

To be fair, if we have a Table API which supports dynamic SQL, things are better but still not ideal:

create or replace procedure adjust_sal_by_region
    (p_region in dept.region%type
           , p_sal_adjustment in number )
as
    emps_rc sys_refcursor;
    emp_rec emp%rowtype;
begin
    emps_rc := emp_utils.get_all_emps(
                    p_where_clause=>'deptno in ( select d.deptno 
                        from dept d where d.region = '||p_region||' )' );

    << emps >>
    loop
        fetch emps_rc into emp_rec;
        exit when emps_rc%notfound;
        emp_rec.sal := emp_rec.sal * p_sal_adjustment;
        emp_utils.set_sal(emp_rec.empno, emp_rec.sal);
    end loop emps;

end adjust_sal_by_region;
/

---

last word

Having said all that, there are scenarios where Table APIs can be useful, situations when we only want to interact with single tables in fairly standard ways. An obvious case might be producing or consuming data feeds from other systems e.g. ETL.

If you want to investigate the use of Table APIs, the best place to start is Steven Feuerstein's Quest CodeGen Utility (formerly QNXO). This is about as good as TAPI generators get, and it's free.

这篇关于了解Table和Transaction API之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！