wishmaster
CJSDN高级会员
发贴: 942
积分: 62
|
于 2002-09-28 11:40
To further squeeze your brain: The solution for the above as follows: Here is a method using a package. 1) Package spec. This declares the input and output array TYPEs: CREATE OR REPLACE PACKAGE pkg IS TYPE input_rec IS RECORD( key1 NUMBER , key2 NUMBER ); TYPE input_tab IS TABLE OF input_rec INDEX BY BINARY_INTEGER; TYPE output_rec IS RECORD( value1 NUMBER , value2 NUMBER , value3 NUMBER , value4 NUMBER , value5 NUMBER ); TYPE output_tab IS TABLE OF output_rec INDEX BY BINARY_INTEGER; PROCEDURE proc ( p_input_tab IN input_tab , p_output_tab OUT output_tab ); END pkg; / 2) Package body. This contains the procedure to do the work: CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE proc ( p_input_tab IN input_tab , p_output_tab OUT output_tab ) IS v_index PLS_INTEGER := p_input_tab.FIRST; BEGIN WHILE v_index IS NOT NULL LOOP SELECT value1, value2, value3, value4, value5 INTO p_output_tab(v_index).value1 , p_output_tab(v_index).value2 , p_output_tab(v_index).value3 , p_output_tab(v_index).value4 , p_output_tab(v_index).value5 FROM tbldata WHERE key1 = p_input_tab(v_index).key1 AND key2 = p_input_tab(v_index).key2; v_index := p_input_tab.NEXT(v_index); END LOOP; END proc; END pkg; / 3) A test program to show how the package is used: SET SERVEROUT ON SIZE 1000000 DECLARE v_input_tab pkg.input_tab; v_output_tab pkg.output_tab; BEGIN v_input_tab(1).key1 := 1; v_input_tab(1).key2 := 10; v_input_tab(2).key1 := 3; v_input_tab(2).key2 := 30; v_input_tab(3).key1 := 5; v_input_tab(3).key2 := 50; pkg.proc( v_input_tab, v_output_tab ); FOR i IN 1..3 LOOP DBMS_OUTPUT.PUT_LINE( 'output('||i||') = {' ||v_output_tab.value1||',' ||v_output_tab.value2||',' ||v_output_tab.value3||',' ||v_output_tab.value4||',' ||v_output_tab.value5||'}' ); END LOOP; END; / 4) The test data: tandrews@IDEV> select * from tbldata 2 / KEY1 KEY2 VALUE1 VALUE2 VALUE3 VALUE4 VALUE5 ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 10 100 1000 10000 100000 1000000 2 20 200 2000 20000 200000 2000000 3 30 300 3000 30000 300000 3000000 4 40 400 4000 40000 400000 4000000 5 50 500 5000 50000 500000 5000000 6 60 600 6000 60000 600000 6000000 7 70 700 7000 70000 700000 7000000 8 80 800 8000 80000 800000 8000000 9 90 900 9000 90000 900000 9000000 10 100 1000 10000 100000 1000000 10000000 10 rows selected. 5) Output from test program: output(1) = {100,1000,10000,100000,1000000} output(2) = {300,3000,30000,300000,3000000} output(3) = {500,5000,50000,500000,5000000} There are some issues I have not addressed. For example, if one of the rows in the input table contains key values that are not found in tbldata, then the procedure will abort with an exception. You need to decide what exactly you want to happen if there is an exception. Some possibilities: 1) As is, the procedure could raise NO_DATA_FOUND or TOO_MANY_ROWS, which the calling program could trap. 2) You could add an EXCEPTION handler to trap these errors and return the error some other way, e.g. via an OUT parameter. 3) You could put BEGIN/EXCEPTION around the SELECT statement so that the procedure does not abort, but maybe records the errors in another element in the output array: BEGIN SELECT value1, value2, value3, value4, value5 INTO p_output_tab(v_index).value1 , p_output_tab(v_index).value2 , p_output_tab(v_index).value3 , p_output_tab(v_index).value4 , p_output_tab(v_index).value5 FROM tbldata WHERE key1 = p_input_tab(v_index).key1 AND key2 = p_input_tab(v_index).key2; EXCEPTION WHEN OTHERS THEN p_output_tab(v_index).error_code := SQLCODE; END; Handle Exceptions??? Guess you guys are very familiar with this!!!!
The WishMaster ---------------------------------------------- SOA | ERP | EAI | B2Bi | BPM | BAM
|