创建对象类型
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> select * from store.purchases;
PRODUCT_ID CUSTOMER_ID QUANTITY
--------------------------------------- --------------------------------------- ---------------------------------------
1 4 1
2 4 1
SQL> select * from purchases;
PRODUCT_ID CUSTOMER_ID QUANTITY
--------------------------------------- --------------------------------------- ---------------------------------------
SQL> select * from products;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
SQL> select * from store.products;
PRODUCT_ID PRODUCT_TYPE_ID NAME DESCRIPTION PRICE
--------------------------------------- --------------------------------------- ------------------------------ -------------------------------------------------- -------
1 1 Modern Science A description of modern science 19.95
2 1 Chemistry Introduction to Chemistry 30.00
3 2 Supernova A star explodes 25.99
4 2 Tank War Action movie about a future war 13.95
5 2 Z Files Series on mysterious activities 49.99
6 2 2412: The Return Aliens return 14.95
7 3 Space Force 9 Adventures of heroes 13.49
8 3 From Another Planet Alien from another planet lands on Earth 12.99
9 4 Classical Music The best classical music 10.99
10 4 Pop 3 The best popular music 15.99
11 4 Creative Yell Debut album 14.99
12 My Front Line Their greatest hits 13.49
12 rows selected
SQL> create table object_products of product_typ;
Table created
SQL> insert into object_products values(
2 product_typ(1,'Pasta','20 oz bag of pasta',3.95,10));
1 row inserted
SQL> describe product_typ;
Element Type
---------------- ------------
ID NUMBER
NAME VARCHAR2(15)
DESCRIPTION VARCHAR2(22)
PRICE NUMBER(5, 2)
DAYS_VALID NUMBER
GET_SELL_BY_DATE FUNCTION
SQL> insert into object_products(id,name,description,price,days_valid)
2 values(
3 2,'Sardines','12 oz box of sardines',2.99,5);
1 row inserted
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
---------- --------------- ---------------------- ------- ----------
1 Pasta 20 oz bag of pasta 3.95 10
2 Sardines 12 oz box of sardines 2.99 5
SQL> select value(op)
2 from object_products op;
VALUE(OP)
---------
<Object>
<Object>
SQL> update object_products
2 set description='25 oz bag of pasta' where id=1;
1 row updated
SQL> select * from object_product;
select * from object_product
ORA-00942: 表或视图不存在
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
---------- --------------- ---------------------- ------- ----------
1 Pasta 25 oz bag of pasta 3.95 10
2 Sardines 12 oz box of sardines 2.99 5
SQL> create table object_customers of person_typ;
Table created
SQL> insert into boject_customers values(
2 person_typ(1,'John','Brown',date'1955-4-01','800-555-1211',address_typ(
3 '2 State Street','Beantown','MA','12345')));
insert into boject_customers values(
person_typ(1,'John','Brown',date'1955-4-01','800-555-1211',address_typ(
'2 State Street','Beantown','MA','12345')))
ORA-00942: 表或视图不存在
SQL> insert into object_customers value(
2 person_typ(1,'John','Brown',date'1955-4-01','800-555-1211',address_typ(
3 '2 State Street','Beantown','MA','12345')));
insert into object_customers value(
person_typ(1,'John','Brown',date'1955-4-01','800-555-1211',address_typ(
'2 State Street','Beantown','MA','12345')))
ORA-00917: 缺失逗号
SQL> insert into object_customers values(
2 person_typ(1,'John','Brown',date'1955-4-1','800-555-1211',
3 address_typ('2 State ','Middle Town','CA','12345'));
insert into object_customers values(
person_typ(1,'John','Brown',date'1955-4-1','800-555-1211',
address_typ('2 State ','Middle Town','CA','12345'))
ORA-00917: 缺失逗号
SQL> insert into object_customers values(
2 person_typ(1,'John','Brown',date'1955-4-1','800-555-1211',
3 address_typ('2 State Street','Beantown','MA','12345')));
1 row inserted
SQL> insert into object_customers(
2 id,first_name,last_name,dob,phone,address)
3 values(
4 2,'Cynthia','Green',date'1968-4-5','800-555-1212',address_typ(
5 '3 Free Street','Middle Town','CA','12345'));
1 row inserted
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
---------- --------------- ---------------------- ------- ----------
1 Pasta 25 oz bag of pasta 3.95 10
2 Sardines 12 oz box of sardines 2.99 5
SQL> commit;
Commit complete
SQL> select * from object_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS
---------- ---------- ---------- ----------- ------------ -------
1 John Brown 1955-4-1 800-555-1211 <Object
2 Cynthia Green 1968-4-5 800-555-1212 <Object
SQL> create table purchases(
2 id number primary ke,
3 customer ref person_typ scope is object_customers,
4 product ref product_typ scope id object_products);
create table purchases(
id number primary ke,
customer ref person_typ scope is object_customers,
product ref product_typ scope id object_products)
ORA-00905: 缺失关键字
SQL> create table purchases(
2 id number primary key,
3 customer ref person_typ scope id object_customers,
4 product ref product_typ scope id object_products
5 );
create table purchases(
id number primary key,
customer ref person_typ scope id object_customers,
product ref product_typ scope id object_products
)
ORA-00905: 缺失关键字
SQL> create table purchases(
2 id number primary key,
3 customer ref person_typ scope is object_customers,
4 product ref product_typ scope is object_products
5 );
create table purchases(
id number primary key,
customer ref person_typ scope is object_customers,
product ref product_typ scope is object_products
)
ORA-00955: 名称已由现有对象使用
SQL> describe purchases;
Name Type Nullable Default Comments
----------- ------- -------- ------- --------
PRODUCT_ID INTEGER
CUSTOMER_ID INTEGER
QUANTITY INTEGER
SQL> drop table purchases;
Table dropped
SQL> create table purchases(
2 id number primary ke,
3 customer ref person_typ scope is object_customers,
4 product ref product_typ scope is object_products
5 );
create table purchases(
id number primary ke,
customer ref person_typ scope is object_customers,
product ref product_typ scope is object_products
)
ORA-00905: 缺失关键字
SQL> create table purchases(
2 id number primary key,
3 customer ref person_typ scope is object_customers,
4 product ref product_typ scope is object_products
5 );
Table created
SQL> insert into purchases(
2 id,customer,product)
3 values(1,(select ref(oc) from object_customers oc where oc.id=1),(
4 select ref(op) from object_products op where op.id=1))
5 ;
1 row inserted
SQL> select * from purchases;
ID CUSTOMER PRODUCT
---------- -------- -------
1 <Referen <Refere
SQL> select deref(customer),deref(product) from purchases;
DEREF(CUSTOMER) DEREF(PRODUCT)
--------------- --------------
<Object> <Object>
SQL> update purchases set product=(
2 select ref(op) from object_products op where op.id=2)
3 where id=1;
1 row updated
SQL> create or replace
2 /
create or replace
ORA-00922: 选项缺失或无效
SQL> create or replace package product_package as
2 type ref_cursor_typ is ref cursor;
3 function get_prodcuts return ref_cursor_typ;
4 procedure insert_product(
5 p_id in object_products.id%type,
6 p_name in object_products.name%type,
7 p_description in object_products.description%type,
8 p_price in object_products.price%type,
9 p_days.valid in object_products.days_valid%type
10 );
11
12 end product_package;
13 /
Warning: Package created with compilation errors
SQL> show error;
Errors for PACKAGE SYS.PRODUCT_PACKAGE:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9/7 PLS-00103: 出现符号 "."在需要下列之一时： in out <an identifier> <a double-quoted delimited-identifier> ... long double ref char time timestamp interval date binary national character nchar
SQL> create or replace package product_package as
2 type ref_cursor_typ is ref cursor;
3 function get_products return ref_cursor_typ;
4 procedure insert_product(
5 p_id in object_products.id %type,
6 p_name in object_products.name%type,
7 p_description in object_products.description%type,
8 p_price in object_products.price%type,
9 p_days_valid in object_products.days_valid%type
10 );
11 end product_package;
12 /
Package created
SQL> create or replace package body product_package as
2 function get_products
3 return ref_cursor_typ is
4 products_ref_cursor ref_cursor_typ;
5 begin
6 open products_ref_cursor for
7 select value(op)
8 from object_products op;
9 return products_ref_cursor;
10 end get_products;
11 procedure insert_product(
12 p_id in object_products.id%type,
13 p_name in object_products.name%type,
14 p_description in object_products.description%type,
15 p_price in object_products.price%type,
16 p_days_valid in object_products.days_valid%type
17 )as
18 product product_typ:=
19 product_typ(
20 p_id,p_name,p_description,p_price,P_days_valid);
21 begin
22 insert into object_products values(product);
23 commit;
24 exception when
25 others then
26 rollback;
27 end insert_product;
28 end product_package;
29 /
Package body created
call
SQL> call product_package.insert_product(4,'salsa','15 oz jar of salsa',1.50,20);
Method called
SQL> select product_package.get_products from dual;
GET_PRODUCTS
------------
<Value Error
SQL> select product_package.get_products from dual;
GET_PRODUCTS
------------
<Value Error
SQL> select * from object_products;
ID NAME DESCRIPTION PRICE DAYS_VALID
---------- --------------- ---------------------- ------- ----------
1 Pasta 25 oz bag of pasta 3.95 10
2 Sardines 12 oz box of sardines 2.99 5
4 salsa 15 oz jar of salsa 1.50 20
SQL> select product_package.get_products from dual;
GET_PRODUCTS
------------
<Value Error
SQL> show error;
No errors
SQL> commit;
Commit complete
SQL> create type person_typ as object(
2 id number,
3 first_name varchar2(10),
4 last_name varchar2(10),
5 dob date,
6 phone varchar2(12),
7 address address_typ) no final;
8 /
create type person_typ as object(
id number,
first_name varchar2(10),
last_name varchar2(10),
dob date,
phone varchar2(12),
address address_typ) no final;
ORA-00955: 名称已由现有对象使用
SQL> show person_typ;
SQL> describe person_typ;
Element Type
---------- ------------
ID NUMBER
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
DOB DATE
PHONE VARCHAR2(12)
ADDRESS ADDRESS_TYP
SQL> alter type person_typ set not final;
alter type person_typ set not final
ORA-00922: 选项缺失或无效
SQL> alter type person_typ not final;
alter type person_typ not final
ORA-22312: 必须指定 CASCADE 或 INVALIDATE 选项
SQL> alter type person_typ not final cascade;
Type altered
SQL> create type business_person_typ under person_typ(
2 titile varchar2(20),
3 company varchar2(20)
4 );
5 /
Type created
SQL> create table object_business_customers of business_person_typ;
Table created
SQL> insert into object_business_customers values(
2 business_person_typ(1,'John','Brown',date'1955-4-01','800-555-1211',
3 address_typ('2 State Street','Beantown','MA','12345'),'Manager','XYZ Corp'));
1 row inserted
SQL> select * from object_business_customers;
ID FIRST_NAME LAST_NAME DOB PHONE ADDRESS TITILE COMPANY
---------- ---------- ---------- ----------- ------------ ------- -------------------- --------------------
1 John Brown 1955-4-1 800-555-1211 <Object Manager XYZ Corp
SQL> select bbc.address_typ.street from object_business_customers bbc;
select bbc.address_typ.street from object_business_customers bbc
ORA-00904: "BBC"."ADDRESS_TYP"."STREET": 标识符无效
SQL> select object_business_customers.address.street from object_business_customers where title='Manager';
select object_business_customers.address.street from object_business_customers where title='Manager'
ORA-00904: "TITLE": 标识符无效
SQL> show error;
No errors
SQL> select object_business_customers.address.city from object_business_customers where object_business_customers.id=1;
select object_business_customers.address.city from object_business_customers where object_business_customers.id=1
ORA-00904: "OBJECT_BUSINESS_CUSTOMERS"."ADDRESS"."CITY": 标识符无效
SQL> select pc.title from object_business_customers pc where pc.company='XYZ Corp';
select pc.title from object_business_customers pc where pc.company='XYZ Corp'
ORA-00904: "PC"."TITLE": 标识符无效
SQL> create type vehicla_typ as object(
2 id number,
3 make varchar2(15),
4 model varchar2(15)
5 )not final not instantiable;
6 /
Type created
SQL> create type car_typ under vehicle_typ(
2 convertible char(1)
3 );
4 /
Warning: Type created with compilation errors
SQL> show error;
Errors for TYPE SYS.CAR_TYP:
LINE/COL ERROR
-------- --------------------------------------------
1/20 PLS-00201: 必须声明标识符 'VEHICLE_TYP'
0/0 PL/SQL: Compilation unit analysis terminated
SQL> create type car_typ undr vehicle_typ(
2 convertible char(1)
3 );
4 /
create type car_typ undr vehicle_typ(
convertible char(1)
);
ORA-06545: PL/SQL: 编译错误 - 编译中止
ORA-06550: 第 1 行, 第 14 列:
PLS-00103: 出现符号 "UNDR"在需要下列之一时：
; is authid as compress
force compiled wrapped under
符号 "under在 "UNDR" 继续之前已插入。
ORA-06550: 第 0 行, 第 0 列:
PLS-00565: 作为潜在的 REF 目标 (对象类型), CAR_TYP 必须是完整的
SQL> create type car_typ under vehicle_typ(
2 convertible char(1)
3 );
4 /
Warning: Type created with compilation errors
SQL> show error
Errors for TYPE SYS.CAR_TYP:
LINE/COL ERROR
-------- --------------------------------------------
1/20 PLS-00201: 必须声明标识符 'VEHICLE_TYP'
0/0 PL/SQL: Compilation unit analysis terminated
SQL> create type car_typ under vehicle_typ(
2 vonvertible char(1)
3 );
4 /
Warning: Type created with compilation errors
SQL> show error
Errors for TYPE SYS.CAR_TYP:
LINE/COL ERROR
-------- --------------------------------------------
1/20 PLS-00201: 必须声明标识符 'VEHICLE_TYP'
0/0 PL/SQL: Compilation unit analysis terminated
SQL> create type vehicle_typ as object(
2 id number,
3 make varchar2(15),
4 model varchar2(15)
5 )not final not instantiable;
6 /
Type created
SQL> create type csr_typ under vehicle_typ(
2 convertible char(1)
3 );
4 /
Type created
SQL> creat type motorcyle_typ under vehicle_typ(
2 sidecar char(1)
3 );
creat type motorcyle_typ under vehicle_typ(
sidecar char(1)
)
ORA-00900: 无效 SQL 语句
SQL> /
creat type motorcyle_typ under vehicle_typ(
sidecar char(1)
)
ORA-00900: 无效 SQL 语句
SQL> create type motorcyle_typ under vehicle_typ(
2 sidecar char(1)
3 );
4 /
Type created
SQL> create table vehicles of vehicle_typ;
Table created
SQL> create table cars of car_typ;
create table cars of car_typ
ORA-04045: 在重新编译/重新验证 SYS.CAR_TYP 时出错
ORA-22345: 在尝试此操作之前应重新编译类型 SYS.CAR_TYP
SQL> create table cars of car_typ;
create table cars of car_typ
ORA-04045: 在重新编译/重新验证 SYS.CAR_TYP 时出错
ORA-22345: 在尝试此操作之前应重新编译类型 SYS.CAR_TYP
SQL> create type car_typ under vehicla_typ(
2 convertible char(1)
3 );
4 /
Type created
SQL> commit;
Commit complete
SQL> create table cars of car_typ;
Table created
SQL> create motorcycles of motorcycle_typ;
create motorcycles of motorcycle_typ
ORA-00901: 无效 CREATE 命令
SQL> show error;
No errors
SQL> create motorcycles of motorcycle_typ;
create motorcycles of motorcycle_typ
ORA-00901: 无效 CREATE 命令
SQL> create type motorcycles of motorcyle_typ;
2 /
Warning: Type created with compilation errors
SQL> show error
Errors for TYPE SYS.MOTORCYCLES:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------
1/18 PLS-00103: 出现符号 "OF"在需要下列之一时： ; is authid as compress force compiled wrapped under
SQL> create type motorcycle_typ under vehicle_typ(
2 sidecar char(1));
3 /
Type created
SQL> commit;
Commit complete
SQL> create table motorcycles of motorcycle_typ;
create table motorcycles of motorcycle_typ
ORA-00955: 名称已由现有对象使用
SQL> drop table motorcycles;
drop table motorcycles
ORA-00942: 表或视图不存在
SQL> create table motorcycles of motorcycle_typ;
create table motorcycles of motorcycle_typ
ORA-00955: 名称已由现有对象使用
SQL> show error;
No errors
SQL> drop object motorcycles;
drop object motorcycles
ORA-00950: 无效 DROP 选项
SQL> create table motorcycle of motorcycle_typ;
Table created
SQL> insert into cars values(
2 car_typ(1,'Toyota','MR2','Y')
3 );
1 row inserted
SQL> insert into motorcycles values(
2 motorcycle_typ(1,'Harley_Davidson','V-Rod','N'));
insert into motorcycles values(
motorcycle_typ(1,'Harley_Davidson','V-Rod','N'))
ORA-04044: 此处不允许过程, 函数, 程序包或类型
SQL> insert into motorcycle values(
2 motorcycle_typ(1,'Harley_Davidson','V-Rod','N');
insert into motorcycle values(
motorcycle_typ(1,'Harley_Davidson','V-Rod','N')
ORA-00917: 缺失逗号
SQL> insert into motorcycle values(
2 motorcyle_tye(1,'Harley-Davidson','V-Rod','N'));
insert into motorcycle values(
motorcyle_tye(1,'Harley-Davidson','V-Rod','N'))
ORA-00904: "MOTORCYLE_TYE": 标识符无效
SQL> insert into motorcle value(
2 motorcycle_typ(1,'Harley-Davidson','V-ROd','N'));
insert into motorcle value(
motorcycle_typ(1,'Harley-Davidson','V-ROd','N'))
ORA-00917: 缺失逗号
SQL> insert into motorcle values(
2 motorcycle_typ(1,'harley-Davidson','V-Rod','N'));
insert into motorcle values(
motorcycle_typ(1,'harley-Davidson','V-Rod','N'))
ORA-00942: 表或视图不存在
SQL> insert into motorcycle values(
2 motorcycle_typ(1,'harley_Davison','V-Rod','N'));
1 row inserted
SQL> select* from cars;
ID MAKE MODEL CONVERTIBLE
---------- --------------- --------------- -----------
1 Toyota MR2 Y
SQL> select * from motorcycles;
select * from motorcycles
ORA-04044: 此处不允许过程, 函数, 程序包或类型
SQL> select * from motorcycle;
ID MAKE MODEL SIDECAR
---------- --------------- --------------- -------
1 harley_Davison V-Rod N
SQL> commit;
Commit complete
SQL>
