Java开发网 - [Help] Oracle Stored Proc takes arrary as input parameter

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate

打印话题 寄给朋友 订阅主题

作者

[Help] Oracle Stored Proc takes arrary as input parameter

wishmaster

CJSDN高级会员

发贴: 942
积分: 62

于 2002-09-26 08:12

I have an array to pass to the stored proc and expect to receive an array.
The input array has two keys and the output array has five values.
The process: take 2 keys and execute the select statements to retrieve corresponding 5 values.
input[i].key1, input[i].key2
output[i].value1, output[i].value2 output[i].value3, output[i].value4, output[i].value5
SELECT DISTINCT value1, value2, value3, value4, value5, key1, key2 FROM tblData WHERE key1 = input[i].key1 AND key2 = input[i].key2
Then assign the results to output array, like below:
output[i].value1 = value1;
output[i].value2 = value2;
output[i].value3 = value3;
output[i].value4 = value4;
output[i].value5 = value5;

How can I achieve all of this through a stored proc??? Code sample if possible...
Thanks a lot..

The WishMaster
----------------------------------------------
SOA | ERP | EAI | B2Bi | BPM | BAM

作者

回复： [Help] Oracle Stored Proc takes arrary as input parameter [Re:wishmaster]

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 Light Bulb

.value1||','
||v_output_tab Light Bulb

.value2||','
||v_output_tab Light Bulb

.value3||','
||v_output_tab Light Bulb

.value4||','
||v_output_tab Light Bulb

.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

已读帖子

新的帖子

被删除的帖子