oracle数据库函数创建对象,oracle 数据库对象学习一

学习数据库对讲的定义，使用对象创建表，对象表，及dml操作。

对象引用(不使用外键)的操作，构造函数的定义及使用等。

关键字：type  ,type body ,value  ,ref  ,deref  ,construtor ,self......

/*数据库对象

--地址

--产品

--人

*/

Create Or Replace Type Address_Typ As Object

(

Street Varchar2(128),

City   Varchar2(64),

State  Varchar2(64),

Zip    Varchar2(32)

);

Create Or Replace Type Person_Typ As Object

(

Id         Number,

First_Name Varchar2(64),

Last_Name  Varchar2(64),

Dob        Date,

Phone      Varchar2(32),

Address    Address_Typ

);

Create Or Replace Type Product_Typ As Object

(

Id          Number,

Name        Varchar2(64),

Description Varchar2(512),

Price       Number(5, 2),

Days_Valid  Number,

Member Function Get_Sell_By_Date Return Date

)

;

CREATE OR REPLACE Type Body Product_Typ As

Member Function Get_Sell_by_Date Return Date Is

v_Sell_By_Date Date;

Begin

Select Days_Valid + Sysdate Into v_Sell_By_Date From Dual;

Return v_Sell_By_Date;

End;

End;

----------------------tables

Create Table products(

product product_typ,

quantity_in_stock Number);

Create Table object_products Of product_typ;

Create Table object_customers Of person_typ;

Create Table purchases(

Id Number Primary Key,

customer Ref person_typ Scope Is object_customers,

product   Ref product_typ Scope Is object_products);

---------------dml

Select  * From products For Update

Insert Into Products (Product, Quantity_In_Stock) Values (Product_Typ(1, 'pasta', 'pasta desc', 3.95, 10), 50);

Insert Into Products (Product, Quantity_In_Stock) Values (Product_Typ(2, 'sandiness', 'sandiness desc', 2.99, 5), 50);

Select  * From products  p Where p.product.Id=1;

Select Sysdate, p.Product.Days_Valid, p.Product.Get_Sell_By_Date() From Products p Where p.Product.Id = 1;

/******************

object_products object_customers

*****************/

--使用product_typ构造函数

Insert Into object_products Values(product_typ(1,'pasta','pasta desc',3.95,10));

--不使用product_typ构造函数

Insert Into object_products(Id,Name ,Description,price,days_valid)

Values(2,'sardiness','sardiness desc',2.95,5);

Select * From object_products;

Select Value(op) From object_products op;

Insert Into Object_Customers

Values

(Person_Typ(1, 'john', 'brown', Sysdate, '123456', Address_Typ('street1 ', 'bj', 'bj', '100000')));

Insert Into Object_Customers(Id,first_name,last_name,dob,phone,address)

Values

(2, '2john', '2brown', Sysdate, '2123456', Address_Typ('street2 ', 'tj', 'tj', '200000'));

Insert Into Object_Customers

Values(Null);-- cannot

Insert Into Object_Customers

Values

(Person_Typ(1, 'john', 'brown', Sysdate, '123456', Null));

Select  * From Object_Customers;

/*alter table OBJECT_CUSTOMERS

add constraint PK_OJB_CUSTOMER_ID primary key (ID);*/

Select  * From Object_Customers oc  Where oc.address.city='bj' Or oc.address.city Is Null ;

Insert Into purchases(Id,customer,product)

Values(1,

(Select Ref(oc) From object_customers oc Where oc.Id=1),

(Select Ref(op) From object_products op Where op.Id=1));

/* Select Ref(oc) From object_customers oc Where oc.Id=1*/

Select * From purchases;

Insert Into purchases(Id,customer,product)

Values(2,

Person_Typ(1, 'john', 'brown', Sysdate, '123456', Address_Typ('street1 ', 'bj', 'bj', '100000')),

product_typ(1,'pasta','pasta desc',3.95,10));-- error

Select id,Deref(customer),Deref(product) From purchases;

/* Update purchases Set product =Select Ref(op) From object_products op Where op.Id=2;*/

/*

创建带有构造函数的对象 */

CREATE OR REPLACE Type obj_typ As Object(

Id Number,

Name Varchar2(32),

ages Number,

/*Constructor Function obj_typ()Return Self As Result,*/--not permitted

Constructor Function obj_typ(p_id Number,p_name Varchar2)Return Self As Result,

/*Constructor Function obj_typ(p_id Number,p_name Varchar2,p_ages Number)Return Self As Result*/--不能调用否则报错ORA-06553: PLS-307: too many declarations

Constructor Function obj_typ(p_id Number)Return Self As Result

)

CREATE OR REPLACE Type Body Obj_Typ As

Constructor Function Obj_Typ

(

p_Id   Number,

p_Name Varchar2

) Return Self As Result Is

Begin

Self.Id   := p_Id;

Self.Name := p_Name;

Self.Ages := 0;

Return;

End;

/*  Constructor Function Obj_Typ

(

p_Id   Number,

p_Name Varchar2,

p_Ages Number

) Return Self As Result Is

Begin

Self.Id   := p_Id;

Self.Name := p_Name;

Self.Ages := p_Ages;

Return;

End;*/

Constructor Function Obj_Typ

(

p_Id   Number

) Return Self As Result Is

Begin

Self.Id   := p_Id;

Self.Name :='kady';

Self.Ages :=0;

Return;

End;

End;

----use the obj_typ

Create Table obj_tab Of obj_typ;

Select  * From obj_tab;

Insert Into obj_tab(Id,Name,ages) Values(1,'jim',20);

Insert Into obj_tab Values(obj_typ(2,'kavy'));

Insert Into obj_tab Values(obj_typ(3));

Insert Into obj_tab Values(obj_typ(4,'ta',50));