模式：

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;

自定義類型可用于函數(shù)的返回類型：

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;

oracle pl/sql中使用自定義數(shù)據(jù)類型