從系統檢視中生成儲存過程的單元測試指令碼的指令碼（供參考）

從系統檢視中生成儲存過程的單元測試指令碼（供參考）

[@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 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;