Composite data types in Oracle

1) Display definition record type;

Declare

--Display defines a record type Myrec

Type Myrec is RECORD (

Combine_no VARCHAR2 (10),

Department_code VARCHAR2 (11)

);

--Declaring a variable RCD of the Myrec type;

RCD Myrec;

Begin

Select Nr.combine_no,nr.department_code to RCD from RATE_COMBINE_NR nr where nr.combine_no= ' G600000001 ';

Dbms_output.put_line (rcd.combine_no| | ' -' | | Rcd.department_code);

End

2) There are some PL/SQL directives that do not use the%rowtype attribute when using an implicit definition record, such as a cursor for loop;

Declare

Cursor Mycur is

SELECT * from Rate_combine_nr nr where rownum <10;

RCD Mycur%rowtype; --this can be omitted;

Begin

For RCD in Mycur Loop

Dbms_output.put_line (rcd.combine_no| | ' -' | | Rcd.department_code);

End Loop;

End

3) PL/SQL has three types of collections

The number of elements in the Varray collection is limited, and index_by and nested tables are unrestricted

.) index_by table

The definition syntax for the Index_by table collection is as follows:

. TYPE Type_name is TABLE of Element_type [not NULL] INDEX by Binary_interget;

. The important keyword in this is index by binary_interget, without this keyword, then the collection will be a nested table;

. Once you have defined the Index_by table, you can create variables of the index_by table as you would create other variables;

.) Nested tables

The nested table is very similar to the Index_by table, and the syntax created is very similar. Using the type statement, there is just no index by Binary_integer substring

TYPE Type_name is TABLE of Element_type [not NULL];

.) Varray

Varray or data variables have restrictions on elements. Think of him as a collection. Varray definitions still use the type statement, but the keyword Varray or varrying

Array tells Oracle that this is a Varray collection.

TYPE type_name is Varray (max_size) of Element_type [not NULL];

. The value of the subscript starts from 1;

This article is from the "Small Li Guangzhi Blog" blog, please be sure to keep this source http://6817977.blog.51cto.com/6807977/1597122

Composite data types in Oracle