了解 Table 和 Transaction API 之间的区别

2021-11-15 00:00:00 api design-patterns oracle plsql

朋友们，

This Ask Tom thread 我通过另一个 SO 问题发现，提到了 Table 和 Transactional API，我试图了解它们之间的区别.

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.

表 API (TAPI) 是无法访问底层表的地方，并且有getter"&setters"来获取信息.

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);

代替:

   select the_address
   from some_table
   where identifier = address_id

然后要更改地址，我将调用另一个负责更改的 TAPI:

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

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

事务 API (XAPI) 再次是无法直接访问修改表中的信息但我可以从中进行选择的地方?(这是我的理解有点模糊的地方)

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

然后改变它我会打电话

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

所以我能看出 TAPI 和 XAPI 之间的唯一区别是从数据库中检索记录的方法，即 Select 与 PL/SQL 调用?

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?

推荐答案

让我们从 Table API 开始.这是通过 PL/SQL API 调解对表的访问的做法.所以，我们每个表都有一个包，它应该从数据字典中生成.该包提供了一组标准的程序，用于针对表发出 DML，以及一些用于检索数据的函数.

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.

相比之下，事务 API 代表一个工作单元.它根本不公开有关底层数据库对象的任何信息.事务 API 提供更好的封装和更清晰的界面.

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. 新部门必须有名称和位置
  2. 新部门必须有一名经理，该经理必须是现有员工
  3. 其他现有员工可能会被转移到新部门
  4. 新员工可能会被分配到新部门
  5. 新部门必须至少分配两名员工(包括经理)

使用 Table API，事务可能如下所示:

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;
/

而使用事务 API 则要简单得多:

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;
/

那么为什么在检索数据方面存在差异呢?因为事务 API 方法不鼓励通用 get() 函数，以避免盲目使用低效的 SELECT 语句.

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);

...尤其是当员工记录有 LOB 列时.

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

它也比:

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

... 如果这些 getter 中的每一个都执行单独的 SELECT 语句.众所周知:这是一种糟糕的 OO 实践，会导致糟糕的数据库性能.

... 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.

Table API 的支持者为它们辩护，理由是它们使开发人员无需考虑 SQL.不赞成使用 Table API 的人出于同样的原因.即使是最好的 Table API 也倾向于鼓励 RBAR 处理.如果我们每次都编写自己的 SQL，我们更有可能选择基于集合的方法.

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.

使用事务性 API 并不一定排除使用 get_resultset() 函数.查询 API 仍有很多价值.但它更有可能由实现连接的视图和函数构建，而不是在单个表上使用 SELECT.

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.

顺便说一句，我认为在 Table API 之上构建事务 API 不是一个好主意:我们仍然使用孤立的 SQL 语句而不是精心编写的连接.

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.

作为示例，这里有两个不同的事务 API 实现，用于更新区域中每个员工的工资(区域是组织的一个大型部分；部门被分配到区域).

As an illustration, here are two different implementations 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).

第一个版本没有纯 SQL 只是 Table API 调用，我不认为这是一个稻草人:它使用了我在 Table API 包中看到的那种功能(尽管有些使用动态 SQL 而不是命名为 SET_XXX() 程序).

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;
/

SQL 中的等效实现:

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;
/

这比以前版本的嵌套游标循环和单行更新要好得多.这是因为在 SQL 中，编写我们需要按区域选择员工的连接是轻而易举的.使用 Table API 要困难得多，因为 Region 不是Employees 的键.

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.

公平地说，如果我们有一个支持动态 SQL 的 Table API，事情会更好，但仍然不理想:

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;
/

<小时>

最后一句话

话虽如此，在某些情况下，表 API 可能很有用，即我们只想以相当标准的方式与单个表进行交互的情况.一个明显的案例可能是从其他系统生成或使用数据馈送，例如ETL.

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.

如果您想研究 Table API 的使用，最好的起点是 Steven Feuerstein 的 Quest CodeGen 实用程序(前身为 QNXO).这和 TAPI 生成器一样好，而且是免费的.

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.

相关文章