从系统视图中生成存储过程的单元测试脚本的脚本（供参考）

从系统视图中生成存储过程的单元测试脚本（供参考）

[@more@]create or replace function f_get_proc_param(obj_name in varchar2)
return varchar2 is
res varchar2(4000);
cnt number default 0;
begin
res := res || 'PROMPT **********************************************' || chr(10);
res := res || 'PROMPT Test ' || obj_name || chr(10);
res := res || 'PROMPT **********************************************' || chr(10);
res := res || 'PROMPT 模拟数据语句块，请在此处添加SQL' || chr(10);
res := res || 'PROMPT ##############################################' || chr(10);
res := res || chr(10);
res := res || 'PROMPT 运行存储过程，请修改参数值' || chr(10);
res := res || 'PROMPT ##############################################' || chr(10);
--res := res || 'DECLARE' || chr(10);
for rec in (select argument_name,
decode(data_type,'VARCHAR2','VARCHAR2(1000)',data_type) data_type,
in_out
from user_arguments
where object_name = obj_name
and in_out like '%OUT%'
and argument_name is not null) loop
res := res || 'VARIABLE ' || rec.argument_name || ' ' || rec.data_type || ';' || chr(10);
end loop;
--res := res || 'BEGIN' || chr(10);
res := res || 'EXEC ' || obj_name || '(' || chr(10);
for rec in (select argument_name,
data_type,
default_value,
default_length,
in_out
from user_arguments
where object_name = obj_name
and argument_name is not null
order by position) loop
if rec.in_out like '%OUT%' then
if cnt = 0 then
res := res || ' ' || rec.argument_name || ' => :' || rec.argument_name || chr(10);
else
res := res || ' ,' || rec.argument_name || ' => :' || rec.argument_name || chr(10);
end if;
else
if cnt = 0 then
res := res || ' ' || rec.argument_name || ' => ?' || chr(10);
else
res := res || ' ,' || rec.argument_name || ' => ?' || chr(10);
end if;
end if;
cnt := 1;
end loop;
res := res || ' );' || chr(10);
--res := res || ' DBMS_OUTPUT.PUT_LINE(''返回代码："'' || V_AN_O_RET_CODE || ''"'');' || chr(10);
--res := res || ' DBMS_OUTPUT.PUT_LINE(''返回信息："'' || V_AC_O_RET_MSG || ''"'');' || chr(10);
--res := res || 'END;' || chr(10) || '/' || chr(10);
res := res || 'PROMPT 查询结果，请在此处添加SQL' || chr(10);
res := res || 'PROMPT ##############################################' || chr(10);
res := res || chr(10);
return res;
end;
/
set feedback off
set head off
spool e:UnitTest.sql
select 'spool e:unittestTest'||a.object_name||'.sql' || chr(10) ||
'select ''' || f_get_proc_param(a.object_name) || ''' from dual;' || chr(10) ||
'spool off' script
from user_procedures a,user_objects b
where a.object_name = b.object_name
and b.object_type = 'PROCEDURE';
spool off
spool e:unittestmain.sql
prompt PROMPT 测试开始 ......
prompt PROMPT ==============================================
select '@@Test'||a.object_name||'.sql' script
from user_procedures a,user_objects b
where a.object_name = b.object_name
and b.object_type = 'PROCEDURE';
spool off
drop function f_get_proc_param;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/802415/viewspace-842460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/802415/viewspace-842460/