存储过程如下: package部分: ------------------------------ create or replace package proc_test is
TYPE emp IS TABLE OF TEST_EMP.DEPT_DENWA%TYPE;
PROCEDURE proc_test02(p_deptNo in TEST_EMP.DEPT_NO%TYPE, p_tel out emp, p_result_count out number); end proc_test; ------------------------------ body部分: create or replace package body proc_test as procedure proc_test02(p_deptNo in TEST_EMP.DEPT_NO%TYPE, p_tel out emp, p_result_count out number) is -- 退避変数宣言 v_tel emp; v_count NUMBER NOT NULL := 0; w_index NUMBER NOT NULL := 0;
-- 電話番号を取得 cursor c_test is select t1.dept_denwa, count(1) as temp_count from test_emp t1 where t1.dept_no = p_deptNo group by t1.dept_denwa; begin -- SQL実行 begin for c_test01 in c_test loop v_count := c_test01.temp_count; if (v_count = 0) then dbms_output.put_line('この条件により、検索結果が0件になりました!'); else for w_index in 1 .. v_count loop p_tel(w_index) := v_tel(w_index); end loop; end if; p_result_count := v_count; end loop; end; exception when NO_DATA_FOUND then dbms_output.put_line('no_data_found!'); when others then dbms_output.put_line('other error found!'); end proc_test02;
end proc_test; ---------------------------- 测试语句: -- Created on 2012/03/27 by HX0627 declare -- Local variables here TYPE wordlist IS TABLE OF TEST_EMP.DEPT_DENWA%TYPE; v_out wordlist; v_sum number;
begin -- Test statements here -- 配列初期化 --v_out := wordlist(); dbms_output.put_line('----------開始------------'); proc_test.proc_test02(p_deptNo => 'S2K003', p_tel => v_out, p_result_count => v_sum);
-- 検索件数 dbms_output.put_line('v_sum = ' || v_sum); -- 1件以上の場合 if (v_sum >= 1) then for i in 1 .. v_sum loop -- 電話番号を出力 dbms_output.put_line('v_out(' || i || ') = ' || v_out(i)); end loop; end if; dbms_output.put_line('----------終了------------');