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

例子:

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;
12. DECLARE
13. TYPE t_StudentTable IS TABLE OF students%ROWTYPE INDEX BY BINARY_INTEGER;
14. v_Students t_StudentTable;
15. BEGIN
16. SELECT * INTO v_Students(1100)
17. FROM students
18. WHERE id=1100;
19. DBMS_OUTPUT.PUT_LINE( v_Students(1100).OUUSRNM);
20. 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@]