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

 

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

转载于:http://blog.itpub.net/11813230/viewspace-671582/