oracle自定义type类型的使用,ORACLE 自定义类型[转]

用 create type 变量 as table of 类型和 create type 变量 as object(

字段1 类型1,

字段2 类型2

);

与 type 变量 is table of 类型 和 type 变量 is record(

字段1 类型1,

字段2 类型2

);

区别是 用 create 后面用 as , 若直接用 type 后面用 is

create 是创 object , 而 type 是创 record .

1. TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;

定义:TYPE t_charTable IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

引用:tableName(index);

例子:declare

type t_table is table of varchar2(10) index by BINARY_integer;

MyTab t_table;

begin

MyTab(1) := 'A';

MyTab(2) := 'B';

MyTab(3) := 'C';

DBMS_OUTPUT.PUT_LINE('First index:'||' '|| mytab(1) ||' ');

end;

DECLARE

TYPE t_StudentTable IS TABLE OF students%ROWTYPE INDEX BY BINARY_INTEGER;

v_Students t_StudentTable;

BEGIN

SELECT * INTO v_Students(1100)

FROM students

WHERE id=1100;

DBMS_OUTPUT.PUT_LINE( v_Students(1100).OUUSRNM);

END;

二.

TYPE sales_country_t_rec IS RECORD (

YEAR VARCHAR (4),

country CHAR (2),

sum_amount_sold NUMBER

);

v_sales_country_t_rec sales_country_t_rec;

引用: v_les_country_t_rec.year := 'ssss'; v_sales_country_t_rec.country := 'a'; v_sales_country_t_rec.sum_amount_sold := 2 ;

也可这样定义: type v_test_array is table of sales_country_t_rec.

三.对象

1.定义对象类型

(1)定义对象类型：TYPE sales_country_t

CREATE TYPE sales_country_t AS OBJECT (

YEAR VARCHAR2 (4),

country CHAR (2),

sum_amount_sold NUMBER

);

(2)定义表类型：TYPE SUM_SALES_COUNTRY_T_TAB

CREATE TYPE sum_sales_country_t_tab AS TABLE OF sales_country_t;

(3)定义对象类型：TYPE sales_gender_t

CREATE TYPE sales_gender_t AS OBJECT (

YEAR VARCHAR2 (4),

country_id CHAR (2),

cust_gender CHAR (1),

sum_amount_sold NUMBER

);

(4)定义表类型：TYPE SUM_SALES_GENDER_T_TAB

CREATE TYPE sum_sales_gender_t_tab AS TABLE OF sales_gender_t;