oracle pl/sql中使用自定义数据类型

模式：

CREATE OR REPLACE TYPE 类型名 as OBJECT （属性名 属性类型,...）;

举例：

CREATE OR REPLACE TYPE BITS_IDX_BASE AS OBJECT ( NAME VARCHAR2(4000), FIELDS VARCHAR2(4000), CREATE_DATE VARCHAR2(4000), INCREMENT_DATE VARCHAR2(4000) );

CREATE OR REPLACE TYPE BITS_IDX_BASE_TBL AS TABLE OF BITS_IDX_BASE;

自定义类型可用于函数的返回类型：

FUNCTION LIST_BASE return BITS_IDX_BASE_TBL

as

c utl_tcp.connection; -- TCP/IP connection to the Web server

ret_val pls_integer;

lastRow varchar(3000);

currentRow varchar(3000);

idx_base_table BITS_IDX_BASE_TBL := BITS_IDX_BASE_TBL();

tmp_pos1 number;

tmp_pos2 number;

tmp_pos3 number;

BEGIN

c := utl_tcp.open_connection(remote_host => currentIdxServerAdr,

remote_port => currentIdxServerPort); -- open connection

ret_val := utl_tcp.write_line(c, 'BITS_IDX_TCP_CMD'); -- send TCP request

ret_val := utl_tcp.write_line(c, 'LISTBASE');

BEGIN

utl_tcp.flush(c);

currentRow := utl_tcp.get_line(c, TRUE);

--currentRow := utl_tcp.get_line(c, TRUE);

lastRow := null;

LOOP

currentRow := utl_tcp.get_line(c, TRUE);

if lastRow is not null then

--dbms_output.put_line(lastRow);

tmp_pos1 := instr(lastRow, '||',1,1);

tmp_pos2 := instr(lastRow, '||',1,2);

tmp_pos3 := instr(lastRow, '||',1,3);

idx_base_table.extend;

idx_base_table(idx_base_table.count) := BITS_IDX_BASE(substr(lastRow, 1, tmp_pos1 - 1), substr(lastRow, tmp_pos1 + 2, tmp_pos2 - tmp_pos1 - 2), substr(lastRow, tmp_pos2 + 2, tmp_pos3 - tmp_pos2 - 2), substr(lastRow, tmp_pos3 + 2));

end if;

lastRow := currentRow;

--dbms_output.put_line(utl_tcp.get_line(c, TRUE)); -- read result

END LOOP;

EXCEPTION

WHEN utl_tcp.end_of_input THEN

NULL; -- end of input

WHEN OTHERS THEN

NULL;

END;

utl_tcp.close_connection(c);

return idx_base_table;

END;