ITPub博客

首页 > 数据库 > Oracle > ORACLE 自定义类型[转]

ORACLE 自定义类型[转]

原创 Oracle 作者:wzdoxu88 时间:2009-09-21 17:11:34 0 删除 编辑

用 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);

例子:

  1. declare
  2. type t_table is table of varchar2(10) index by BINARY_integer;
  3. MyTab t_table;
  4. begin
  5. MyTab(1) := 'A';
  6. MyTab(2) := 'B';
  7. MyTab(3) := 'C';
  8. DBMS_OUTPUT.PUT_LINE('First index:'||' '|| mytab(1) ||' ');
  9. end;
  10. DECLARE
  11. TYPE t_StudentTable IS TABLE OF students%ROWTYPE INDEX BY BINARY_INTEGER;
  12. v_Students t_StudentTable;
  13. BEGIN
  14. SELECT * INTO v_Students(1100)
  15. FROM students
  16. WHERE id=1100;
  17. DBMS_OUTPUT.PUT_LINE( v_Students(1100).OUUSRNM);
  18. 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;

http://blog.163.com/xxciof/blog/static/79781327200961721245149/

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9294812/viewspace-1027195/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    49
  • 访问量
    513315