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;