blogjava's web log

blogjava's web log
...

oracle基本语句(一)

区别是和sqlserver比较

-- 示例一:Create Table 命令,区别较小
Create   Table  vendor_master

 vencode 
varchar2 ( 5 ),
 venname 
varchar2 ( 20 ),
 venadd1 
varchar2 ( 20 ),
 venadd2 
varchar2 ( 20 ),
 venadd3 
varchar2 ( 20 )
 )
 
-- 示例二:Alter Table Modify 命令,区别较大
Alter   Table  vendor_master Modify (venname  varchar2 ( 25 ))

-- 示例三:Alter Table Add 命令,区别较小,主要是数据类型
Alter   Table  vendor_master
  
add  (tel_no  number ( 12 ),
       tngst_no 
number ( 12 ))
       
-- 示例四:Drop Column 命令:完全一样
Alter   Table  vendor_master  Drop   Column  tngst_no

-- 示例五:Oracle 独有
alter   Table  vendor_master  set  unused(tel_no)

-- 示例六:Truncate Table命令 :完全一样
truncate   table  vendor_master

-- 示例八:Desc命令:完全不一样
Desc  vendor_master

-- 示例九:Drop Table 命令:完全一样
drop   table  vendor_master



-- 示例10:Insert命令
Insert   into  vendor_master  values  ( ' v001 ' , ' John smith ' , ' 11 E main st ' , ' West Avenue ' , ' alabama ' , 1234567 )

-- 以下这种方法只在Orace中有效,l但不推荐使用此方法
Insert   into  vendor_master  values  ( ' &vencode ' , ' &venname ' , ' &venadd1 ' , ' &venadd2 ' , ' &venadd3 ' , & telno)


-- 示例15:Select命令
select   *   from  vendor_master


-- 示例20:Update命令 :注意大小写
update  vendor_master  set  tel_no  =   987654   where  vencode = ' V001 '    --  'v001'


-- 示例24:Grant 和 Revoke命令
grant   all   on  vendor_master  to  sys
revoke   all   on  vendor_master  from  sys

**********************************************************************************************
// 用户


connect system
/ manager @ydgl ;

-- 删除已有的用户和表空间

-- drop tablespace freemandatabase;
--
drop tablespace tempfreemandatabase;

-- 创建表空间
create  tablespace FreeManDataBase
datafile 
' c:\FreeManDataBase.ora '
size 25M;

-- 创建临时表空间
create   temporary  tablespace tempFreeManDataBase
tempfile 
' c:\tempFreeManDataBase.ora '
size 25M;

-- 创建用户
create   user  zong identified  by  " 123456 "
default  tablespace FreeManDataBase
temporary  tablespace tempFreeManDataBase;

-- 赋权限
grant  connect  to  zong;
grant  resource  to  zong;
grant  dba  to  aaa;

-- 登录
connect zong / 123456 @ydgl ;

create   table  zong.ccc(bh  varchar2 ( 10 ), xm  varchar2 ( 10 ), age  number , salary  number , birthday date) 
 
-- 事务处理 
    insert   into  zong.ccc(bh,xm,age,salary,birthday)  values  ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
   
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
   savepoint ppp;
   
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
   
rollback   to  ppp;
   
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
   
update  zong.ccc  set  bh = ' 008 '  ,salary = 5000 ,age = 33   where  bh = ' 004 ' ;
    
commit  ;
--  清除数据
    truncate   table  zong.cc
   
delete   from  zong.ccc  where  bh = ' 006 '


  
create   table  zong.firsttable (xm  varchar2 ( 10 ),age  number ( 4 ),salary  number ( 7 , 2 ))
  
-- 添加列
   alter   table  zong.firsttable  add  (kk  number ( 10 ), birthday date)
  
-- 更新列类型
   alter   table  zong.firsttable modify (xm  number ( 2 ), birthday  varchar2 ( 10 ))
  
  
-- 收回权限
    revoke  dba  from  zong
  
-- 授予管理员角色
    grant  dba  to  zong 
   
   
-- 授予对象权限
    grant   select  , update   on  firsttable  to  system
   
   
-- 删除表
    drop   table  zong.ccc
   
   


-- 集合操作
Create   Table  zong.YYY(xm  Varchar2 ( 10 ),age  Number ( 8 ));
Insert   Into  zong.yyy  Values ( ' aaa ' , 10 )
Insert   Into  zong.yyy  Values ( ' bbb ' , 20 )

Create   Table  zong.xxx(xm  Varchar2 ( 10 ),age  Number ( 8 ));
Insert   Into  zong.xxx  Values ( ' aaa ' , 10 )
Insert   Into  zong.xxx  Values ( ' ccc ' , 30 )

Select   *   From  zong.yyy  Union   Select   *   From  fei.xxx

Select   *   From  zong.yyy  Union   All   Select   *   From  fei.xxx

Select   *   From  zong.yyy  Intersect   Select   *   From  fei.xxx

Select   *   From  zong.yyy Minus  Select   *   From  fei.xxx
 
 
*********************************************
**********************************************
-- 字符串函数
   select   ascii ( ' A ' ) A, ascii ( ' a ' ) a, ascii ( ' 0 ' ) zero, ascii ( '   ' space   from  dual
  
  
select  chr( 54740 ) zhao,chr( 65 ) chr65  from  dual
  
  
select  concat( ' 010- ' , ' 88888888 ' ) || ' 连接 '  实例  from  dual
  
  
select  initcap( ' smith ' ) upp  from  dual;
  
  
select  instr( ' oracle traning ' , ' ra ' , 1 , 2 ) instring  from  dual
  
  
select  lpad(rpad( ' gao ' , 10 , ' * ' ), 17 , ' * ' ) from  dual;
  
  
select   ltrim ( rtrim ( ' gao qian jing     ' , '   ' ), '   ' from  dual;
  
  
select  substr( ' 13088888888 ' , 3 , 8 from  dual;
  
select   replace ( ' he love you ' , ' he ' , ' i ' from  dual;
  
  
 
-- 数学函数
   select   floor ( 2345.67 from  dual;
  
select  mod( 10 , 3 ),mod( 3 , 3 ),mod( 2 , 3 from  dual;
  
select   round ( 55.5 ), round ( - 55.4 ),trunc( 55.5 ),trunc( - 55.5 from  dual;
  
select   sign ( 123 ), sign ( - 100 ), sign ( 0 from  dual;
  
  
  
-- 日期函数
    select  to_char(add_months(to_date( ' 199912 ' , ' yyyymm ' ), 2 ), ' yyyymm ' from  dual;
   
select  to_char(sysdate, ' yyyy.mm.dd ' ),to_char((sysdate) + 1 , ' yyyy.mm.dd ' from  dual;
   
select  last_day(sysdate)  from  dual;
  
   
select  months_between( ' 19-12月-1999 ' , ' 19-3月-1999 ' ) mon_between  from  dual;
   
select  months_between(to_date( ' 2000.05.20 ' , ' yyyy.mm.dd ' ),to_date( ' 2005.05.20 ' , ' yyyy.mm.dd ' )) mon_betw  from  dual;
   
   
select  to_char(sysdate, ' yyyy.mm.dd hh24:mi:ss ' ) 北京时间,to_char(new_time
  (sysdate,
' PDT ' , ' GMT ' ), ' yyyy.mm.dd hh24:mi:ss ' ) 埃及时间  from  dual;
  
  
select  next_day( ' 18-5月-2001 ' , ' 星期五 ' ) next_day  from  dual;
  
//
  
select   round (sysdate, ' year ' from  ccc;
  
  
select  to_char(sysdate, ' dd-mm-yyyy day ' from  dual;
  
select   *   from  ccc  where  birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 120 ;
  
select   *   from  ccc  where  birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 30 ;
   
select   *   from  ccc  where  birthday > to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' );
   
   
  
select  to_char(t.d, ' YY-MM-DD ' from  ( 
select  trunc(sysdate,  ' MM ' ) + rownum - 1   as  d 
from  dba_objects 
where  rownum  <   32 ) t 
where  to_char(t.d,  ' MM ' =  to_char(sysdate,  ' MM ' -- 找出当前月份的周五的日期
and  trim(to_char(t.d,  ' Day ' ))  =   ' 星期五 '  

-- 类型转换函数
select  to_char(sysdate, ' yyyy/mm/dd hh24:mi:ss ' from  dual;

select  to_number( ' 1999 ' year   from  dual;

-- 系统函数
select  username, user_id   from  dba_users  where   user_id = uid;
select   user   from  dual;


-- 集合函数
  create   table  table3(xm  varchar ( 8 ),sal  number ( 7 , 2 ));
 
insert   into  table3  values ( ' gao ' , 1111.11 );
 
insert   into  table3  values ( ' gao ' , 1111.11 );
 
insert   into  table3  values ( ' zhu ' , 5555.55 );
 
 
-- select avg(distinct sal) from gao.table3;
  -- select max(distinct sal) from scott.emp;


-- 分组函数和统计函数 
  select  deptno, count ( * ), sum (sal)  from  scott.emp  group   by  deptno;
 
select  deptno, count ( * ), sum (sal)  from  scott.emp  group   by  deptno  having   count ( * ) >= 5 ;
 
select  deptno, count ( * ), sum (sal)  from  scott.emp  having   count ( * ) >= 5   group   by  deptno ;
 
select  deptno,ename,sal  from  scott.emp  order   by  deptno,sal  desc ;

**********************************************************************************************
    
CREATE   TABLE  "ZONG"."CCC"("BH"  VARCHAR2 ( 10 ), "XM"  VARCHAR2 ( 10 ), "AGE"  NUMBER , "SALARY"  NUMBER , "BIRTHDAY" DATE) 

    
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values  ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
 
    
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values  ( ' 001 ' , null , 33 , 4444 ,to_date( ' 1979-1-1 ' , ' yyyy-mm-dd ' ));
   
    
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
   
    
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
  
    
insert   into  zong.ccc(bh,xm,age,salary,birthday)  values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
 
    
select  bh 编号, nvl(xm, ' ggg ' as  姓名  from  ccc  where  bh = ' 001 '
 
    
select  bh 编号,  nvl2(xm, ' yes ' , ' no ' as  姓名   from  ccc
 
   
-- select bh 编号,NULLIF('bbb','aaa') from ccc
   
   
-- is null 的用法
   
   
select   *   from  ccc   where  xm  is   null
   
select   *   from  ccc   where  xm   is   NOT   null
   
   
-- not in的用法
    
    
select   *   from  ccc  where   birthday  between   to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' and   to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
    
select   *   from  ccc  where   birthday  not    between   to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' and   to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
   
  
--  in的用法
      select   *   from  ccc  where   xm  in ( ' aaa ' , ' peng ' , ' cao ' )
     
select   *   from  ccc  where   xm   not   in ( ' aaa ' , ' peng ' , ' cao ' )
     
  
-- like的用法和=、!=、<、>、<=、>=的用法
    
    
select   *   from  ccc  where  age > 24   and  age  <= 56   and  xm  like   ' %a% '



   
   
     
--
     create    table   sales (xm  varchar2 ( 10 ), dTime date,  count   number , totalmoney  number ,city  varchar2 ( 10 ))
    
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
     
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
    
    
select  xm, sum ( count ) 数量, sum (totalmoney) 金额 ,city  from  sales  group   by  xm , count  ,totalmoney,rollup(city)  order   by  xm , count  ,totalmoney,city
    
    
-- group分组语句
    select  xm, sum ( count ) 数量, sum (totalmoney) 金额 ,city  from  sales  group   by  xm , count  ,totalmoney,rollup(city)   having   count > 2000   order   by  xm , count  ,totalmoney,city
    
    
-- rollup函数
     select  xm,  sum ( count ) 数量,city  from  sales  group   by  xm , count  ,rollup(city)  order   by  xm , count  ,city
    
    
    
  
-- 事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束(commit or rollback),Oracle自动清除临时表中数据
     CREATE  GLOBAL  TEMPORARY   TABLE  admin_work_area
        (startdate DATE,
         enddate DATE,
         class 
CHAR ( 20 ))
      
ON   COMMIT   DELETE  ROWS;
   
create   table  permernate( a  number );
   
insert   into  admin_work_area  values (sysdate,sysdate, ' temperary table ' );
   
   
insert   into  permernate  values ( 1 );
   
commit ;
   
select   *   from  admin_work_area;
   
select    *   from  permernate;


-- 会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据

     
drop   table  admin_work_area;
     
CREATE  GLOBAL  TEMPORARY   TABLE  admin_work_area
        (startdate DATE,
         enddate DATE,
         class 
CHAR ( 20 ))
      
ON   COMMIT  PRESERVE  ROWS;
   
create   table  permernate( a  number );
   
insert   into  admin_work_area  values (sysdate,sysdate, ' temperary table ' );
   
   
insert   into  permernate  values ( 2 );
   
commit ;
   
select   *   from  admin_work_area;
   
select    *   from  permernate;

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

 
create    table   sales (xm  varchar2 ( 10 ), dTime date,  count   number , totalmoney  number ,city  varchar2 ( 10 ))
    
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
    
insert   into  sales  values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
     
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
     
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
    
insert   into  sales  values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
    
    
    
    
    
select   *   from  sales  where  xm = ' 张三 '   for   update   of   count
    
    
update  sales  set   count = 30000   where  xm = ' 张三 '
    
     
    
-- 另一用户登录
     update  ydgl.sales  set   count = 30000   where  xm = ' 张三 '
    
    
    
-- 在多个用户在同一张表中放置锁时,其他用户等待上一用户的时间.
     select   *   from  sales  where  xm = ' 张三 '   for   update   of   count   wait  10  
    
    
   
    
    
-- 只作查询
    lock  table  sales  in  share  mode
    
    
    
-- 能删除,更新,插入除锁定外的其他行
    
   lock 
table  sales  in  share  update  mode
   
   
   
-- 排他锁 ,在同一时间点上,只有一个用户在表中放置排他锁.
    lock  table  sales  in  exclusive  mode
    
    
-- 避免延迟时间,锁在用户之间的传递时间,不等待,立即提示错误信息
      lock  table  sales  in  exclusive  mode nowait
   
**********************************************************************************************
-- --创建临时表  
/* create temporary tablespace mydb
tempfile 'f:\mydb.ora'
size 10m;
*/


-- --创建抽象数据类型
create   or   replace  type address_ty  as  object
(street_no 
number ( 3 ),
street_name 
varchar2 ( 20 ),
city 
varchar2 ( 20 ),
state 
varchar2 ( 20 ));

-- --查看抽象数据类型实际值
select  attr_name,length,attr_type_name
from  user_type_attrs
where  type_name = ' ADDRESS_TY ' ;

-- --创建应用了抽象数据类型的表
create   table  vend_mast
(vencode 
varchar2 ( 5 ),
venname 
varchar2 ( 15 ),
venadd address_ty,
tel_no 
number ( 10 ));

-- --查看表结构
desc  vend_mast;

select  column_name,data_type  from  user_tab_columns  where
       table_name
= ' VEND_MAST ' ;
       
-- --插入记录
insert   into  vend_mast  values
(
' v100 ' , ' john ' ,address_ty( 110 , ' Clinton Rd ' ,
' Rosewood ' , ' Columbia ' , 234465987 );

-- --查看记录
select  a.venadd.city  from  vend mast a;

-- -修改记录,一定要用别名
update  vend_mast a
       
set  a.venadd.street_no = 10
       
where  venname = ' john ' ;
       
-- --删除记录       
delete   from  vend_mast a 
where  a.venadd.city = ' Rosewood ' ;

-- --强行删除抽象数据类型
drop  type address_ty force;

-- --创建应用了抽象数据类型的表的索引
create   index  streetnum  on  vend_mast(venadd.street_no);

-- --查看索引
select  owner,index_name,index_type,table_owner,table_name,table_type
from  all_indexes
where  owner = ' SCOTT ' ;

-- --创建不能继承的对象
create   or   replace  type Student_typ  as  object
(Ssn 
number ,
Name 
varchar2 ( 30 ),
Address 
varchar2 ( 100 )) not  final;

-- --修改是否能继承
alter  type Student_typ  not  final;

create  type t  as  object
(x 
number ,)
not  instantiable member  function  func1  return   number )
not  instantiable  not  final;

-- --创建可变数组
create  type itemcode  as  varray( 5 of   varchar2 ( 5 );

create  type qty_ord  as  varray( 5 of   number ( 5 );

create  type qty_deld  as  varray( 5 of   number ( 5 );

-- --基于可变数组创建表
create   table  order_detail(
      orderno 
varchar2 ( 5 ),
      item_va itemcode,
      qty_va qty_ord,
      qtyd_va qty_deld);

-- --插入记录
insert   into  order_detail 
       
values ( ' o100 ' ,itemcode( ' i100 ' , ' i101 ' , ' i102 ' , ' i103 ' , ' i104 ' ),
              qty_ord(
100 , 98 , 47 , 29 , 20 ),
              qty_deld(
100 , 900 , 800 , 700 , 600 ));

-- --查看整体
select   *   from  order_detail
-- --单个
select  item_va  from  order_detail
-- --查看可变数组内容
select   *   from   table (
-- --select * from order_detail a where a.orderno='o100')
select  a.item_va  from  order_detail a  where  a.orderno = ' o100 ' )

-- --嵌套表
--
--创建抽象数据类型即对象
create   or   replace  type ord_ty  as  object (
       itemcode 
varchar2 ( 5 ),
       qty_ord 
number ( 5 ),
       qty_deld 
number ( 5 ));

-- --表中包含嵌套表一定要基于一个对象创建一个新的对象作为嵌套表
create   or   replace  type ord_nt  as   table   of  ord_ty;

-- --创建包含嵌套表的表
create   table  order_master(
        orderno 
varchar2 ( 5 ),
        odate date,
        vencode 
varchar2 ( 5 ),
        dets ord_nt)
        nested 
table  dets store  as  ord_nt_tab;
-- 嵌套表放入某个任意任名的存储空间,嵌套表的存储空间与普通表不同,分别存储在不同的空间

insert   into  order_master  values (
' o100 ' ,to_date( ' 18-07-99 ' , ' DD-MM-YY ' ), ' v001 ' ,
ord_nt(ord_ty(
' i100 ' , 10 , 5 ),
ord_ty(
' i101 ' , 50 , 25 ),
ord_ty(
' i102 ' , 5 , 5 )));

-- --把记录插入到嵌套表中
insert   into   table ( select  p.dets  from  order_master p
where  p.orderno = ' o100 ' )
values ( ' i103 ' , 30 , 25 );

select  t.dets  from  order_master t  where  t.orderno = ' o100 ' ;

-- --查看嵌套表中的信息 
select   *   from   table ( select  t.dets  from  order_master t
where  t.orderno = ' o100 ' );

-- --修改
update   table ( select  t.dets  from  order_master t 
                    
where  t.orderno = ' o100 ' ) t
                           
set  value(t) = ord_ty( ' i103 ' , 50 , 45 )
                               
where  t.itemcode = ' i103 ' ;

-- --删除嵌套表的值
delete   from   table ( select  t.dets  from  order_master t
       
where  t.orderno = ' o100 ' ) t
       
where  t.itemcode = ' i102 ' ;
       
-- --把嵌套表中已存在的记录添加到创建的表中
insert   into  order_master  values ( ' o202 ' ,to_date( ' 2003-3-5 ' , ' YY-MM-DD ' ),
' v101 ' , cast (multiset( select   *   from   table ( select  dets  from  order_master
where  orderno = ' o201 ' ))  as  ord_nt));
       
-- --创建对象
create  type vend_ty  as  object(
vencode 
varchar2 ( 5 ),
venname 
varchar2 ( 20 ),
venadd1 
varchar2 ( 20 ),
venadd2 
varchar2 ( 20 ),
venadd3 
varchar2 ( 20 ),
tel_no 
number ( 6 ));

drop   table  vend_master;

-- --创建对象表,对象中不能定义约束,在对象表中可通过关键字constraint定义
create   table  vend_master  of  vend_ty(vencode  constraint  vc_pk  primary   key );

insert   into  vend_master  values (
vend_ty(
' v201 ' , ' John ' , ' 10 ' , ' Fezinnith ' , ' Mexico ' , 948456 ));

-- --查看地址(表中所分配的OID)
select  ref(a)  from  vend_master a;

-- --创建一个指向抽象数据类型的表
create   table  ord_master(
orderno 
varchar2 ( 5 ),
vendet ref vend_ty);
-- --数据类型为指向抽象数据类型的类型

-- --类似将查询记录插入一个表的语法插入记录
insert   into  ord_master( select ' o301 ' ,ref(a) from  vend_master a
where  vencode = ' v201 ' );

-- --查看所有记录
select   *   from  ord_master;

select  deref(a.vendet)  from  ord_master a;

delete   from  vend_master  where  vencode = ' v201 ' ;


-- --对象视图
create   table  item(
      itemcode 
varchar2 ( 10 ),
      item_on_hand 
number ( 10 ),
      item_sold 
number ( 10 ));

create   or   replace  type item_type  as  object
      (itemcode 
varchar2 ( 10 ),
      item_on_hand 
number ( 10 ),
      item_sold 
number ( 10 ));

create   view  item_view  of  item_type  with  object oid -- --表名of类型名with object oid
(itemcode)  as
select   *   from  item  where  item_on_hand < 20 ;

insert   into  item  values ( ' i201 ' , 10 , 5 );
-- --插入值调用函数
insert   into  item_view  values (item_type( ' i102 ' , 15 , 50 ));

create   view  nt_view  of  ord_ty  with  object oid(itemcode)
as   select   *   from   table ( select  d.dets
from  order_master d  where  d.orderno = ' o201 ' );

create   table  itemfile(
        itemcode 
varchar2 ( 5 primary   key ,
        itemdesc 
varchar2 ( 20 ),
        p_category 
varchar2 ( 20 ),
        qty_hand 
number ( 5 ),
        re_level 
number ( 5 ),
        max_level 
number ( 5 ),
        itemrate 
number ( 9 , 2 ));

create   table  order_detail (
        orderno 
varchar2 ( 5 ),
        itemcode 
varchar2 ( 5 ),
        qty_ord 
number ( 5 ),
        qty_deld 
number ( 5 ), primary   key (orderno,qty_ord,qty_deld),
        
foreign   key (itemcode)  references  itemfile(itemcode));

create   or   replace  type itemfile_ty  as  object
        (itemcode 
varchar2 ( 5 ),
        itemdesc 
varchar2 ( 20 ),
        p_category 
varchar2 ( 20 ),
        qty_hand 
number ( 5 ),
        re_level 
number ( 5 ),max_level  number ( 5 ),
        itemrate 
number ( 9 , 12 ));

create   view  itemfile_ov  of  itemfile_ty
       
with  object oid(itemcode)
       
as   select   *   from  itemfile;

select  make_ref(itemfile_ov,itemcode)  from  itemfile;

create   view  order_detail_ov 
as  
       
select  make_ref(itemfile_ov,itemcode) items,orderno,qty_ord,qty_deld 
              
from  order_detail;

-- --不能正确运行
select  deref(a.items)  from  order_detail_ov a;

**********************************************************************************************



-- --创建抽象数据类型
create   or   replace  type add_ty  as  object(
Street 
varchar2 ( 25 ),
City 
varchar2 ( 15 ),
State 
varchar2 ( 10 ),
Zip 
number );

-- --基于抽象数据类型创建表
create   table  customer(
Customer_id 
number ( 4 ),
person add_ty);

-- --插入记录
insert   into  customer  values (
1001 ,add_ty( ' No.2 downhill st. ' , ' Los Angles ' , ' California ' , 700023 ));

insert   into  customer  values (
1002 ,add_ty( ' No.120 stepahead rd. ' , ' houston ' , ' texas ' , 701024 ));

-- --查询记录
select  customer_id,c.person.city  from  customer c
where  c.person.state = ' texas ' ;

-- --删除记录
delete   from  customer a
where  a.person.zip = 701024 ;

-- --创建可变数组
create  type Phone  as  varray( 2 of   Number ( 8 );

-- --使用可变数组创建表
create   table  Employee(
Eno 
number ( 4 ),
name 
varchar2 ( 15 ),
phone phone);

-- --插入数据
insert   into  Employee  values (
1000 , ' George ' ,Phone( 67343344 , 3432342 ));

delete   from  Employee  where  name = ' gxj ' ;

select   *   from  employee;

select  phone  from  employee;

-- --创建对象
create  type person_details  as  object(
name 
varchar2 ( 15 ),
age 
number ( 2 ),
desg 
varchar2 ( 15 ));


create  type person_detail_table_ty  as   table   of  person_details;

create   table  other_info_person(
dept_name 
varchar2 ( 10 ),
dept_no 
number ( 3 ),
person_info person_detail_table_ty)
nested 
table  person_info store  as  person_store_table;


-- --创建抽象数据类型
create   or   replace  type Dept_type  as  object(
Deptno 
number ( 2 ),
Dname 
varchar2 ( 14 ),
Loc 
varchar2 ( 13 ));

-- --创建表
create   table  Student(
Name 
varchar2 ( 15 ),
Dept_detail Dept_type);

-- --插入数据
insert   into  Student  values (
' Jessica ' ,Dept_type( 20 , ' Computer ' , ' Chicago ' ));

insert   into  Student  values (
' Peter ' ,Dept_type( 40 , ' Electronics ' , ' California ' ));

-- --查询数据
select   *   from  Student;

select  name, a.dept_detail.Deptno  from  Student a 
where  a.Dept_detail.Loc = ' Chicago ' ;



insert   into  Employee  values (
1002 , ' Dick ' ,Phone( 33444876 , 87876565 ));
insert   into  Employee  values (
1003 , ' Jones ' ,Phone( 54576545 , 52457779 ));
  
plsql
--例二:创建具有LOB数据类型的表
CREATE TABLE vendor_master
 (vencode    
varchar2(5),
  venname    
varchar2(15),
  venadd1    
varchar2(20),
  venadd2    
varchar2(20),
  venadd3    
varchar2(20),
  tel_no     
number(6),
  msg        CLOB);
  
--例三:初始化LOB值
INSERT INTO vendor_master VALUES 
 (
'v201','aryay','10','first st','mds',475859,
  
'这是我们的初始化LOB值');
  
select * from vendor_master;


--例9:条件控制
select * from rs_employees
  
where hetongid='WL-090001';

DECLARE
  v_department   rs_employees.department
%type;
BEGIN
  
SELECT department INTO v_department
    
FROM rs_employees
    
WHERE HeTongId='WL-090001';
    
  
IF v_department = '车间工人' THEN
    
UPDATE rs_employees 
      
SET department='不是工人'
      
WHERE HeTongId='WL-090001';
  
ELSE
    
UPDATE rs_employees 
      
SET department='车间工人'
      
WHERE HeTongId='WL-090001';
  
END IF;
END;
/

--CASE语句示例(下面的写法有错)
SET SERVEROUT ON;
DECLARE
  I 
number:=2;
BEGIN
  
CASE 
    
WHEN (I=1THEN dbms_output.put_line('Result is 1');
    
WHEN (I=2THEN dbms_output.put_line('Result is 2');
  
END CASE;
END;



--和上面的区别是什么
SET SERVEROUT ON;
DECLARE
  I 
number:=2;
BEGIN
  
CASE I(用于选择器)
    
WHEN 1 THEN dbms_output.put_line('Result is 1');
    
WHEN 2 THEN dbms_output.put_line('Result is 2');
  
END CASE;
END;
/

--例11:简单循环:在Test Window中执行
--
SET SERVEROUT ON;
DECLARE
  a    
NUMBER := 100;
BEGIN
  LOOP
    a :
= a+25;
    
EXIT WHEN A=250;
  
END LOOP;
  dbms_output.put_line(TO_CHAR(a));
END;

--例12:While循环,此值书上有错
DECLARE
  i    
NUMBER :=0;
  J    
NUMBER :=0;
BEGIN
 
while i<=100 Loop
   J :
= J+1;
   i :
= i+2;
 
end loop;
 dbms_output.put_line(
'j的值是'||j);
END;
 
--例13:FOR循环,结果是5050
DECLARE
  i  
number :=0;
  j  
number :=0;
BEGIN
  
for i in 1..100
  loop
    j :
= j + 1;
  
end loop;
  dbms_output.put_line(
'j的值是'||j);
END;

posted on 2006-04-17 21:13 record java and net 阅读(1608) 评论(0)  编辑  收藏 所属分类: Database


只有注册用户登录后才能发表评论。


网站导航:
 

导航

常用链接

留言簿(44)

新闻档案

2.动态语言

3.工具箱

9.文档教程

友情链接

搜索

最新评论