問題描述
朋友們，
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:
- 新部門必須有名稱和位置
- 新部門必須有一名經理，該經理必須是現有員工
- 其他現有員工可能會被轉移到新部門
- 新員工可能會被分配到新部門
- 新部門必須至少分配兩名員工(包括經理)
使用 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.
這篇關于了解 Table 和 Transaction API 之間的區別的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！