在如何在JAVA程序中使用Struct一次传入多条数据给Oracle的存储过程中我介绍了如何通过定义Struct和Array在JAVA程序中一次传入多条数据给Oracle的存储过程。
步骤一:定义对象类型。
CREATE TYPE department_type AS
OBJECT (
DNO NUMBER (10 ),
NAME VARCHAR2 (50 ),
LOCATION VARCHAR2 (50 )
);
DNO NUMBER (10 ),
NAME VARCHAR2 (50 ),
LOCATION VARCHAR2 (50 )
);
步骤二:定义一个对象类型的数组对象。
CREATE TYPE dept_array AS TABLE OF department_type;
步骤三:定义存储过程来插入数据。
CREATE OR REPLACE PACKAGE objecttype AS
PROCEDURE insert_object (d dept_array);
END objecttype;
CREATE OR REPLACE PACKAGE BODY objecttype
AS
PROCEDURE insert_object (d dept_array)
AS
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
INSERT INTO department_teststruct
VALUES (d(i).dno,d(i).name,d(i).location);
END LOOP;
END insert_object;
END objecttype;
PROCEDURE insert_object (d dept_array);
END objecttype;
CREATE OR REPLACE PACKAGE BODY objecttype
AS
PROCEDURE insert_object (d dept_array)
AS
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
INSERT INTO department_teststruct
VALUES (d(i).dno,d(i).name,d(i).location);
END LOOP;
END insert_object;
END objecttype;
如果我们需要对dept_array类型的d进行的更新的话,那么直接使用下面的语句系统会提示错误。
CREATE
OR
REPLACE
PACKAGE objecttype
AS
PROCEDURE insert_object (d dept_array);
END objecttype;
CREATE OR REPLACE PACKAGE BODY objecttype
AS
PROCEDURE insert_object (d dept_array)
AS
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
d(i).location : = ' New Loc ' || i;
INSERT INTO department_teststruct
VALUES (d(i).dno,d(i).name,d(i).location);
END LOOP;
END insert_object;
END objecttype;
PROCEDURE insert_object (d dept_array);
END objecttype;
CREATE OR REPLACE PACKAGE BODY objecttype
AS
PROCEDURE insert_object (d dept_array)
AS
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
d(i).location : = ' New Loc ' || i;
INSERT INTO department_teststruct
VALUES (d(i).dno,d(i).name,d(i).location);
END LOOP;
END insert_object;
END objecttype;
错误提示: PLS-00363: expression 'D.LOCATION' cannot be used as an assignment target
正确的方法是:
CREATE
OR
REPLACE
PACKAGE BODY objecttype
AS
procedure insert_object(d in out dept_array)
is
begin
for i in 1 ..d. count loop --与FOR i IN d.FIRST..d.LAST 功能相同
AS
procedure insert_object(d in out dept_array)
is
begin
for i in 1 ..d. count loop --与FOR i IN d.FIRST..d.LAST 功能相同
d(i).location :
=
'
New Loc
'
||
i;
INSERT INTO department_teststruct
VALUES (d (i).dno,d (i).name,d (i).location);
end loop;
end insert_object;
END objecttype;
INSERT INTO department_teststruct
VALUES (d (i).dno,d (i).name,d (i).location);
end loop;
end insert_object;
END objecttype;
关键问题是:d 必须是output类型(代码中红色粗体标记部分)。
参考内容:http://forums.oracle.com/forums/thread.jspa?messageID=2208830�