17,086
社区成员
发帖
与我相关
我的任务
分享
public static void main(String[] args) throws Exception {
Connection conn = TestOracleProc.getConn();
boolean isAuto = conn.getAutoCommit();
conn.setAutoCommit(true);
// --------------------------------------
String sql = "{CALL TEST_ARRAY_PROC(?)}";
OracleCallableStatement proc = (OracleCallableStatement) conn.prepareCall(sql);
oracle.sql.ArrayDescriptor arrayDesc = oracle.sql.ArrayDescriptor.createDescriptor(
"USERTEST.TEST_COLUMN_ARRAY", conn);
List<String> phones = new ArrayList<String>();
phones.add("13666666666");
phones.add("13666666667");
phones.add("13666666668");
phones.add("13666666669");
// int[] phonts = new int[] { 111111, 2111111, 311111 };
ARRAY in_phones = new oracle.sql.ARRAY(arrayDesc, conn, phones.toArray(new String[phones
.size()]));
proc.setArray(1, in_phones);
proc.execute();
conn.setAutoCommit(isAuto);
System.out.println("================END==============");
}
create or replace
type TEST_COLUMN_ARRAY as table of VARCHAR2(20);
create or replace
PROCEDURE TEST_ARRAY_PROC(ARRAY_PHONE IN TEST_COLUMN_ARRAY)
AS
BEGIN
IF(array_phone IS NOT NULL AND array_phone.COUNT > 1)THEN
FOR i in ARRAY_PHONE.first..ARRAY_PHONE.last LOOP
IF(ARRAY_PHONE(i) IS NOT NULL)THEN
INSERT INTO TEST_DATA(MOBILE)VALUES(ARRAY_PHONE(i));
ELSE
INSERT INTO TEST_DATA(MOBILE)VALUES('11111111');
END IF;
END LOOP;
ELSE
INSERT INTO TEST_DATA(MOBILE)VALUES('22222222');
END IF;
COMMIT;
END TEST_ARRAY_PROC;
CREATE OR REPLACE PACKAGE TESTDOUBLEARRAY IS
-- Author : ZHANGS
-- Created : 2010-8-24 11:55:25
-- Purpose : 测试二维数组
-- 类型声明
TYPE REF_CUR IS REF CURSOR; --REF游标
--1型数组的元素
TYPE RECORD_E IS RECORD(
STR_KEY TAB_FOR_TEST.STR_KEY%TYPE,
STR_VALUE TAB_FOR_TEST.STR_VALUE%TYPE);
--1元素为record的数组
TYPE RECORD_E_ARRA IS TABLE OF RECORD_E;
--2元素为TAB_FOR_TEST表的行记录的数组
TYPE TAB_E_ARRA IS TABLE OF TAB_FOR_TEST%ROWTYPE;
--存储过程声明
/*
** 测试用第一种数组插入数据
** parameters:I_ARRA_1 IN RECORD_E_ARRA
** return: 无
*/
PROCEDURE INSERT_DATA_1(I_ARRA_1 IN RECORD_E_ARRA);
/*
** 测试用第二种数组插入数据
** parameters:I_ARRA_2 IN RECORD_E_ARRA
** return: 无
*/
PROCEDURE INSERT_DATA_2(I_ARRA_2 IN TAB_E_ARRA);
END TESTDOUBLEARRAY;
CREATE OR REPLACE PACKAGE BODY TESTDOUBLEARRAY IS
/*
** 测试用第一种数组插入数据
** parameters:I_ARRA_1 IN RECORD_E_ARRA
** return: 无
*/
PROCEDURE INSERT_DATA_1(I_ARRA_1 IN RECORD_E_ARRA) IS
--tempRecord RECORD_E;
BEGIN
FOR COUNTS IN 1 .. I_ARRA_1.COUNT LOOP
INSERT INTO TAB_FOR_TEST
VALUES
(I_ARRA_1(COUNTS).STR_KEY, I_ARRA_1(COUNTS).STR_VALUE);
END LOOP;
-- tempRecord:=I_ARRA_1(1);
END INSERT_DATA_1;
/*
** 测试用第二种数组插入数据
** parameters:I_ARRA_2 IN RECORD_E_ARRA
** return: 无
*/
PROCEDURE INSERT_DATA_2(I_ARRA_2 IN TAB_E_ARRA) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('asdasd');
END INSERT_DATA_2;
END TESTDOUBLEARRAY;
declare
-- Non-scalar parameters require additional processing
i_arra_1 testdoublearray.record_e_arra:=testdoublearray.record_e_arra();
a testdoublearray.RECORD_E;
b testdoublearray.RECORD_E;
c testdoublearray.RECORD_E;
begin
-- Call the procedure
a.STR_KEY:=11;a.STR_VALUE:='11111111';
b.STR_KEY:=12;b.STR_VALUE:='22222222';
c.STR_KEY:=13;c.STR_VALUE:='33333333';
i_arra_1.extend;
i_arra_1(1):=a;
i_arra_1.extend;
i_arra_1(2):=b;
i_arra_1.extend;
i_arra_1(3):=c;
testdoublearray.insert_data_1(i_arra_1 => i_arra_1);
end;
StructDescriptor structDes = StructDescriptor.createDescriptor("TESTDOUBLEARRAY.RECORD_E", conn);