oracle 我如何对这个 PL/SQL 过程进行单元测试？（使用 utplsql）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1126416/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I unit test this PL/SQL procedure? (using utplsql)
提问by GoingTharn
Package is very very basic. Loops through a cursor, and updates 2 values where the record_ids are equal.
包是非常非常基本的。循环游标，并更新 2 个记录 ID 相等的值。
What's an appropriate unit test for this sort of procedure?
对于这种过程，什么是合适的单元测试？
I'm going to add some skeleton code because the answers so far, while good, tie to the crux of my issue here: What do I test?
我将添加一些框架代码，因为到目前为止的答案虽然很好，但与我在这里的问题的症结相关：我要测试什么？
PROCEDURE set_shift_times_to_null( RETVAL OUT VARCHAR2,
ERRBUF OUT VARCHAR2,
RECORDS_UPDATED OUT NUMBER) IS
CURSOR evening_shift_employees_cur IS
select employee
FROM employees
where SHIFT='EVENING'
;
BEGIN
RECORDS_UPDATED := 0;
RETVAL := '2';
FOR evening_shift_employees IN evening_shift_employees_cur LOOP
UPDATE NIGHT_SHIFT
Set SOME_DUMB_FIELD = evening_shift_employees.employee;
RECORDS_UPDATED := RECORDS_UPDATED + 1;
END LOOP;
COMMIT;
RETVAL := 0;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
ERRBUF := 'Error occurred - ' || SQLERRM;
END set_shift_times_to_null;
采纳答案by Brian
A couple of suggestions.
几个建议。
Use SQL%ROWCOUNT:
使用 SQL%ROWCOUNT：
BEGIN
UPDATE NIGHT_SHIFT
Set SOME_DUMB_FIELD = evening_shift_employees.employee;
v_rows_processed := SQL%ROWCOUNT;
dbms_output.put_line('There were '||v_rows_processed||' rows updated');
END;
Don't Use When Others (why do you want to lose the stack trace).Just use Exceptions, you will be relying on the caller to check the contents of the ERRBUF.
Don't Use When Others（为什么你想丢失堆栈跟踪）。只使用异常，你将依赖调用者来检查 ERRBUF 的内容。
begin
insert into t values ( 1 );
exception when others then
log_error;
raise;
end;
log_error implementation looks like:
log_error 实现看起来像：
create or replace procedure log_error
as
pragma autonomous_transaction;
l_whence varchar2(1024);
l_msg varchar2(1020) default sqlerrm;
l_code number default sqlcode;
begin
l_whence := whence;
insert into error_table
( timestamp, whence, msg, code )
values
( sysdate, whence, l_msg, l_code );
commit;
exception
when others then
rollback;
raise;
end;
Consider not using any pl/sql. on the surface the update appears completely 'doable' without any cursor. Perhaps an updateable inline view:
考虑不使用任何 pl/sql。从表面上看，更新看起来完全“可行”，没有任何光标。也许是一个可更新的内联视图：
update (
select e.sal as emp_sal, e.comm as emp_comm,
ns.sal as ns_sal, ns.sal/2 as ns_comm
from employees e, night_shift ns
where e.deptno = ns.deptno
)
set emp_sal = ns_sal, emp_comm = ns_comm
回答by FerranB
The appropriate unit test it to validate the affected tables to check that the updated records are what expected.
适当的单元测试它以验证受影响的表以检查更新的记录是否符合预期。
You can create temporary tables with the results you expect and the unit testing code compare the results. Of course is hard work but if you want to test you have to do something like this.
您可以使用您期望的结果创建临时表，并且单元测试代码会比较结果。当然是艰苦的工作，但如果你想测试你必须做这样的事情。
It depends on the work of procedure, but if you want to be sure that test is fine you have to check as possibilities as possible.
这取决于程序的工作，但如果您想确保测试正常，您必须尽可能检查可能性。
A lot of the conditions have to be validated with constraints and the test unit procedures have to execute code that force the database to check that constraints (inserts, and so on).
许多条件必须使用约束进行验证，并且测试单元过程必须执行强制数据库检查约束（插入等）的代码。
回答by GoingTharn
For anyone else who sees this, I found this in the documentation for utplsql: PROCEDURE utAssert.eqtable (
对于看到这个的其他人，我在 utplsql 的文档中找到了这个： PROCEDURE utAssert.eqtable (
msg_in IN VARCHAR2,
check_this_in IN VARCHAR2,
against_this_in IN VARCHAR2,
check_where_in IN VARCHAR2 := NULL,
against_where_in IN VARCHAR2 := NULL,
raise_exc_in IN BOOLEAN := FALSE
);
It's under the assert documentation; looks like it does exactly what I was trying to do.
它在断言文档下；看起来它正是我想要做的。
回答by Daniel F. Thornton
Basically, you want to exercise all the possibilities of your procedure:
基本上，您想锻炼程序的所有可能性：
- Test with equal record-ids.
- Test with non-equal record-ids.
- Test with invalid (non-integer? negative?) record-ids.
- 使用相同的记录 ID 进行测试。
- 使用不相等的记录 ID 进行测试。
- 使用无效（非整数？负数？）记录 ID 进行测试。
Also, test the boundary conditions:
另外，测试边界条件：
- Test with record-ids off by one (ex: 104 and 105).
- Test with maximum record-id (MAX_INT?).
- Test with zero-value record-id.
- 测试记录 ID 减一（例如：104 和 105）。
- 使用最大记录 ID (MAX_INT?) 进行测试。
- 使用零值记录 ID 进行测试。
Here is a nice exampleof good unit-testing practices.
这是一个很好的单元测试实践的例子。
EDIT: I don't know of a robust unit-testing tool for database queries. I would set up a test table of evening_shift_employeeswith various record-id conditions as described above. Then, as suggested by FerranB, check that the records are updated as expected for validation.
编辑：我不知道用于数据库查询的强大的单元测试工具。我会建立一个具有各种记录 ID 条件的night_shift_employees测试表，如上所述。然后，按照 FerranB 的建议，检查记录是否按预期更新以进行验证。
回答by GoingTharn
What I ended up doing was the following:
我最终做的是以下内容：
- Take record count of records that main cursor will update fields to null
- execute procedure ( returns a value of rows updated )
- Take record count of same records as first time
- The difference between the record counts should equal the number of records updated in the procedure. If so, the test passes.
- 获取主游标将字段更新为空的记录的记录数
- 执行过程（返回更新行的值）
- 获取与第一次相同记录的记录数
- 记录计数之间的差异应等于程序中更新的记录数。如果是，则测试通过。
Does this make sense, or is it circular logic?
这是有道理的，还是循环逻辑？
