了解表和事务的API之间的差异 [英] Understanding the differences between Table and Transaction API's
问题描述
友
这问汤姆<一个href=\"http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:672724700346558185\">thread我通过另一个问题SO发现，提到表和事务的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）是那里有对基础表没有访问并有干将＆放大器; 二传手来获取信息。
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）又是在没有直接进入修改信息表中，但我可以从中选择？ （这是我的理解是一种朦胧）
要选择一个地址我想：
select the_address
from some_table
where identifier = address_id
，然后改变它，我会叫
and then to change it I would call
...
change_address(address_id, new_address);
...
所以我可以在TAPI和XAPI之间看到的唯一区别是即选择对战一个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?
推荐答案
让我们先从表API。这是通过一个PL / SQL API调解对表的访问的做法。因此，我们有每桌一个包，它应该从数据字典生成。包presents一套标准发放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重新presents一个工作单元。它根本不暴露有关底层数据库对象的任何信息。事务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:
- 新的部门必须有一个名称和位置
- 新的部门必须有一个经理，谁必须是现有员工
- 其他现有员工可能会被转移到新的部门
- 新员工可能会被分配到新部门
- 新的部门必须分配至少有两名员工（包括经理）
使用表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的方式鼓励，以避免盲目使用效率低下的SELECT语句的通用的get（）功能。
例如，如果你只是想为雇员的薪金和佣金，查询这个...
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_sql := emp_utils.get_sal(p_eno);
l_comm := emp_utils.get_comm(p_eno);
...如果每个这些干将执行单独的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.
表API的支持者认为他们，他们从需要考虑SQL屏蔽开发的基础上。谁去precate他们不喜欢表的API的为同样的原因，人们的。即使是最好的表的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了很多价值。但它更可能是建出来的意见，并加入实施职能不是单个表中选择。
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.
顺便说一句，我认为建立在表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的不同implemntations更新每一个员工的薪酬在区（区域属该组织的大规模部分;部门被分配到区域）
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).
第一个版本有没有纯粹的SQL只是表API调用，我不认为这是一个稻草人：它使用的那种功能，我在表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等价实现：
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;
/
这是比嵌套游标循环和的previous版本单列更新更好。这是因为SQL是不在话下写的加盟，我们需要通过区域选择员工。它是使用Table API的困难很多，因为区域是不是员工的关键。
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.
要公平地说，如果我们有一个表的API，它支持动态SQL，事情更好，但仍不理想：
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.
如果你想调查表使用的API，开始最好的地方是史蒂芬斯坦的任务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.
这篇关于了解表和事务的API之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
