oracle bulk collects 之 java select

oracle bulk collects 之 java select[@more@]

stmt.execute("CREATE TYPE EMPLOYEE AS OBJECT
(EmpName VARCHAR2(50),EmpNo INTEGER))");

stmt.execute("CREATE TYPE EMPLOYEE_LIST AS TABLE OF EMPLOYEE");

stmt.execute("CREATE TABLE EMPLOYEE_TABLE (DeptName VARCHAR2(20),
Employees EMPLOYEE_LIST) NESTED TABLE Employees STORE AS ntable1");

stmt.execute("INSERT INTO EMPLOYEE_TABLE VALUES ("SALES", EMPLOYEE_LIST
(EMPLOYEE('Susan Smith', 123), EMPLOYEE('Scott Tiger', 124)))");
If you want to retrieve all the employees belonging to the SALES department into an array of instances of the custom object class EmployeeObj, then you must add an entry to the type map to specify mapping between the EMPLOYEE SQL type and the EmployeeObj custom object class.

To do this, first create your statement and result set objects, then select the EMPLOYEE_LIST associated with the SALES department into the result set. Cast the result set to OracleResultSet so you can use the getARRAY method to retrieve the EMPLOYEE_LIST into an ARRAY object (employeeArray in the following example).

The EmployeeObj custom object class in this example implements the SQLData interface.

Statement s = conn.createStatement();
OracleResultSet rs = (OracleResultSet)s.executeQuery
("SELECT Employees FROM employee_table WHERE DeptName = 'SALES'");

// get the array object
ARRAY employeeArray = ((OracleResultSet)rs).getARRAY(1);
Now that you have the EMPLOYEE_LIST object, get the existing type map and add an entry that maps the EMPLOYEE SQL type to the EmployeeObj Java type.

// add type map entry to map SQL type
// "EMPLOYEE" to Java type "EmployeeObj"
Map map = conn.getTypeMap();
map.put("EMPLOYEE", Class.forName("EmployeeObj"));
Next, retrieve the SQL EMPLOYEE objects from the EMPLOYEE_LIST. To do this, call the getArray method of the employeeArray array object. This method returns an array of objects. The getArray method returns the EMPLOYEE objects into the employees object array.

// Retrieve array elements
Object[] employees = (Object[]) employeeArray.getArray();
Finally, create a loop to assign each of the EMPLOYEE SQL objects to the EmployeeObj Java object emp.

// Each array element is mapped to EmployeeObj object.
for (int i=0; i{
EmployeeObj emp = (EmployeeObj) employees[i];
...
}

---------------------------------------------------------------------------------------------------------------------

create or replace type varchar_number as object (
v varchar2(10),
i number
);
/
The nested-table type using varchar_number as elements is created: create or replace type t_varchar_number as table of varchar_number;
/
A table containing some values: create table vn (
v varchar2(10),
i number
);

insert into vn values ('uno',1);
insert into vn values ('due',2);
insert into vn values ('tre',3);

commit;
The following function bulk collects the values of the table vn and returns it as a collection. create or replace function fct_t return t_varchar_number as
ret t_varchar_number;
begin
select varchar_number(v,i) bulk collect into ret from vn;

return ret;
end;
/
The function in action set serveroutput on size 10000

declare
x t_varchar_number;
begin
x := fct_t;

for r in (select * from table(cast(x as t_varchar_number))) loop
dbms_output.put_line(r.v || ', ' || r.i);
end loop;

end;
/
uno, 1
due, 2
tre, 3
Cleaning up... drop type t_varchar_number;
drop type varchar_number;
drop function fct_t;
drop table vn;
------------------------------------------------------------------------------------------------------