CREATE TYPE t_address AS OBJECT(
street VARCHAR2(15),
city VARCHAR2(15),
state CHAR(2),
zip VARCHAR2(15)
);
/*创建对象 t_person*/
CREATE TYPE t_person AS OBJECT(
id INTEGER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
dob DATE,
phone VARCHAR2(12),
address t_address
);
CREATE TYPE t_product AS OBJECT(
id INTEGER,
name VARCHAR2(15),
description VARCHAR2(12),
price NUMBER(5,2),
day_valid INTEGER,
--get_sell_by_date() returns the date by which the
--product must be sold
MEMBER FUNCTION get_sell_by_date RETURN DATE
);
/*创建对象体t_product*/
CREATE TYPE BODY t_product AS
MEMBER FUNCTION get_sell_by_date RETURN DATE IS
v_sell_by_date DATE;
BEGIN
SELECT SYSDATE
INTO v_sell_by_date
FROM dual;
RETURN v_sell_by_date;
END get_sell_by_date;
END;
/
CREATE TABLE products(
product t_product,
quantity_in_stock INTEGER
);
/*向对象中插入数据*/
INSERT INTO products(product,
quantity_in_stock)
VALUES (t_product(1,'jiaduobao','liangcha',4.5,60),
50);
INSERT INTO products(product,
quantity_in_stock)
VALUES (t_product(2,'wanglaji','liangcha',3.5,60),
50);
select p.product from products p where p.product.id=2
/*查询使用表别名调用get_sell_by_date()函数*/
SELECT p.product.get_sell_by_date()
FROM products p
UPDATE products p set p.product.id=1 where p.quantity_in_stock=50;
SELECT * FROM products;