创建对象类型

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>

posted @ 2012-04-24 16:25  残阳飞雪  阅读(2537)  评论(0编辑  收藏  举报