Oracle object type

Source: Internet
Author: User

Oracle object type
Oracle object typeObject Type

In PL/SQL, object-oriented programmers are based on object types. The object type is a user-defined Composite data type. It encapsulates the data structure and processes and functions used to manipulate the data structure.

The object type of the database is similar to that of JAVA and C #, and can contain attributes (used to store object states) and methods (used to create object behavior models ). The object type is also user-defined.

The Object Type includes two parts: Object Type Specification and Object Type Body:

  • Object Type standard interface between objects and applications, used to define the public attributes and methods of objects.
  • Object Type bodies are used to implement the public methods defined by object type standards.
Create a simple object type

The syntax for creating a simple data type is as follows:

CREATE [OR REPLACE] TYPE type_name AS OBJECT(    column_name data_type    [,column_name data_type,...])

Object Type attributes are used to describe the features of an object. Each object type has at least one attribute, and contains at most 1000 attributes. The attribute type can be any Oracle data type (including the object type) except the following ): LONG, long raw, NCHAR, NCLOB, NVARCHAR2, ROWID, UROWID, and specific PL/SQL types % TYPE and % ROWTYPE.

Example 1:

First, grant the user the permission to create objects:

GRANT CREATE  ANY  TYPE TO siege;

Then create an object:

CREATE TYPE stu AS OBJECT(       name VARCHAR2(20),       sex     VARCHAR2(2),       birthday DATE,       note VARCHAR2(300))

We can also use an object as a normal type:

CREATE TYPE stu2 AS OBJECT(       sid NUMBER(4),       student stu)

Similarly, we can use a custom type when creating a table:

CREATE TABLE student2(       sid NUMBER(4),       student stu)
Create an object type with a function

Syntax for creating object types with functions:

CREATE [OR REPLACE] TYPE type_name AS OBJECT(    column_name data_type    [,column_name data_type,...],    member function method_name(args_list) return_type,    ...)

Function indicates a function. You can use member procedure to replace member function when creating an object-type member statement, but no return value exists.

If an object contains a function, you also need to declare an object BODY. The Object BODY defines the actual code of the function. Its creation syntax is as follows:

CREATE [OR REPLACE] TYPE BODY type_name AS     MEMBER FUNCTION method_name RETURN return_type {AS|IS}    variable declareations...;    BEGIN ...    RETURN return_value;    END;END;    column_name data_type    [,column_name data_type,...]

Example 2:

First, create an object type:

CREATE TYPE stu3 AS OBJECT(       name VARCHAR2(20),       sex     VARCHAR2(2),       birthday DATE,       note VARCHAR2(300),       MEMBER FUNCTION get_age RETURN NUMBER)

Then create the Object Body:

CREATE TYPE BODY stu3 AS       MEMBER FUNCTION get_age RETURN NUMBER AS                    v_months NUMBER;       BEGIN                    SELECT  FLOOR(MONTHS_BETWEEN(SYSDATE,birthday)/12) INTO                       v_months FROM dual;           RETURN v_months;       END;      END; 

Then we create the student3 table:

CREATE TABLE student3(       sid NUMBER(4),       student stu3)   

Insert a data entry:

INSERT INTO student3 VALUES(1,stu3('siege','M',TO_DATE('19910228','YYYYMMDD'),'my notes'));

Now we can query the inserted statement:

SELECT s.sid,s.student.name,s.student.sex,s.student.birthday,s.student.note,s.student.get_age() FROM student3 s

The result is as follows:

Sid student. name student. sex student. birthday student. note s. STUDENT. GET_AGE ()
1 siege M 28/02/1991 my notes 24

Get object type information

Before using the object type, you must specify the structure of the object type (mainly including the data type and data range of the attribute). You can use the DESCRIBE command to view the structure of the object.

In the command window of PL/SQL developer, open a window and enter the following statement to set the information depth:

SET DESCRIBE [DEPTH {1|n|ALL}]

The value range of depth is 1-50, and the default value is 1. Depth indicates the object level, similar to the level in JAVA. In this example, the depth of STU2 is 2.

DESCRIBE STU2;

You can query the object type information.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.