asoka.hang's oracle/java blog
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  4 随笔 :: 0 文章 :: 0 评论 :: 0 Trackbacks

2006年4月13日 #

请保持约束条件的一直存在：最好先add后drop
大家知道，oracle中没有提供修改约束条件的命令，如果要用到修改约束条件时，常规做法就是先drop掉当前约束再重新创建一个新约束条件，从应用层次而言，这样做是不妥的。

常规做法如下：
SQL >   create   table  test_constraint
   2   (
   3   id  number ,
   4   name  varchar ( 25 ),
   5   age  number   constraint  age_ct  check (age  between   0   and   99 )
   6   );

表已创建。

SQL >   alter   table  test_constraint  drop   constraint  age_ct;

表已更改。

SQL >   alter   table  test_constraint  add   constraint  age_ct  check (age  between   18   and   99 );

表已更改。

tom在一篇文章中这样讲： “我会用两条命令：一条增加一个新的约束，另一条删除旧的约束。”，仔细理解也就是说他会先将新的约束条件进行添加以达到新的要求需求，而事后再对旧的约束条件予以drop，这种做法通过sqlplus表现如下：
SQL >   drop   table  test_constraint;

表已丢弃。

SQL >   create   table  test_constraint
   2   (
   3   id  number ,
   4   name  varchar ( 25 ),
   5   age  number   constraint  age_ct  check (age  between   0   and   99 )
   6   );

表已创建。

SQL >   alter   table  test_constraint  add   constraint  agenew_ct  check (age  between   18   and   99 );

表已更改。

SQL >   alter   table  test_constraint  drop   constraint  age_ct;

表已更改。

从应用层面上而言，更改一个约束名称并不会受到影响，通过上面方法既将新的约束条件增加进去了又没有影响到应用(先drop后add的做法在这里显得是一种错误的做法)。
posted @ 2006-04-19 17:30 asoka的oracle/java博客 阅读(258) | 评论 (0) | 编辑 收藏

很妙的一句SQL语句 ：）
case：要求在一张销售信息表中查询每件产品的最近销售日期(不用PL/SQL实现)。

创建test_purchase表：
SQL >   create   table  test_purchase(
   2   product_name  varchar2 ( 25 ),
   3   salesperson  varchar2 ( 3 ),
   4   purchase_date date,
   5   quantity  number ( 4 , 2 )
   6   );

表已创建。

往test_purchase表中插入数据：
SQL >   insert   into  test_purchase  values ( ' small widget ' , ' ca ' ,to_date( ' 2003-7-14 ' , ' YYYY-MM-DD ' ), 1 );

已创建  1  行。

SQL >   insert   into  test_purchase  values ( ' medium wodget ' , ' bb ' ,to_date( ' 2003-7-14 ' , ' YYYY-MM-DD ' ), 75 );

已创建  1  行。

SQL >   insert   into  test_purchase  values ( ' chrome phoobar ' , ' ga ' ,to_date( ' 2003-7-14 ' , ' YYYY-MM-DD ' ), 2 );

已创建  1  行。

SQL >   insert   into  test_purchase  values ( ' small widget ' , ' ga ' ,to_date( ' 2003-7-15 ' , ' YYYY-MM-DD ' ), 8 );

已创建  1  行。

SQL >   insert   into  test_purchase  values ( ' medium wodget ' , ' lb ' ,to_date( ' 2003-7-15 ' , ' YYYY-MM-DD ' ), 20 );

已创建  1  行。

SQL >   insert   into  test_purchase  values ( ' round snaphoo ' , ' ca ' ,to_date( ' 2003-7-16 ' , ' YYYY-MM-DD ' ), 5 );

已创建  1  行。

关键的select 子查询语句来了，呵呵

SQL >   select   *   from  test_purchase  where (product_name,purchase_date)
   2    in
   3   ( select  product_name, max (purchase_date)  from  test_purchase  group   by  product_name);

PRODUCT_NAME              SAL PURCHASE_D   QUANTITY
 -- ----------------------- --- ---------- ----------
chrome phoobar            ga   2003 - 07 - 14            2
medium wodget             lb   2003 - 07 - 15           20
round  snaphoo             ca   2003 - 07 - 16            5
small widget              ga   2003 - 07 - 15            8

其实仔细想一想也不是很复杂，不过这条语句乍一想来如何写还真有点困难，所以用了个妙字，:p

posted @ 2006-04-13 23:04 asoka的oracle/java博客 阅读(287) | 评论 (0) | 编辑 收藏

ORACLE中的OOP：可变数组的运用
case:在scott.emp中有一个字段为deptno用来存储部门编号，在关系型数据库中一个字段只能够存储一列值(像ENAME列只能够存储员工姓名的值)，要想查询emp与dept两表间的关联值还得运用主从表间的关系，查询效率不高，在emp表中deptno字段并不是很复杂(因为dept表中仅三个字段)的情况下，oracle允许在emp表中的deptno字段里存储一个表，这虽然违反关系型数据库的规则，但是提高了性能。
我们将举一个入库明细的例子来说明可变数组在上面类似情况的应用。

创建一个入库的明细类型mingxitype：
SQL >   create   or   replace  type mingxitype  as  object
   2   (
   3      goodsid  varchar ( 15 ),
   4      incount  int ,
   5      providerid  varchar ( 10 )
   6   );
   7    /

创建一个基于mingxitype类型的可变数组：
SQL >   create   or   replace  type arrmingxitype  as  varray( 100 )  of  mingxitype;
   2    /

创建一个产品入库主表：
SQL >   create   table  instock
   2   (
   3      orderid  int   primary   key ,
   4      indate date,
   5      mingxi arrmingxitype
   6   );

向主表中插入数据：
SQL >   insert   into  instock  values ( 1001 ,to_date( ' 2006-1-1 ' , ' YYYY-MM-DD ' ),arrmingxitype
   2   (mingxitype( ' 101 ' , 10 , ' S01 ' ),mingxitype( ' 102 ' , 30 , ' S05 ' )));

通过查询可以查出主表instock中的字段mingxi中其实包括了一张表的内容，因为显示出来的arrmingxitype可变数组内容比较零乱，降低了可读性，运用PL/SQL可以读出，但是这里我们用table()函数即可完成这个输出修改，SQL语句如下：
SQL >   select   *   from   table ( select  s.mingxi  from  instock s  where  orderid = 1001 );

GOODSID            INCOUNT PROVIDERID
 -- ------------- ---------- ----------
101                       10  S01
 102                       30  S05

这样，通过在表中插入一个可变数组就将文中case里的问题给解决了。

可变数组也有一定的缺陷，比如更新起来比较麻烦，因为不能更新varray中的单个元素，也就意味着，如果要去更新varray中的某个字段，需得将整个varray中的数据全部重新输入一次，所以，可变数组在应用过程中需得慎重选择，可以将其应用于一些平常不需要去修改数据的环境中，比如医院里医生开的处方归档资料库等case中。
posted @ 2006-04-13 16:18 asoka的oracle/java博客 阅读(640) | 评论 (0) | 编辑 收藏