Oracle数据库基础知识

1         概述

数据库是软件开发的基础构件。开发将涉及数据库基础知识、库互联、模拟数据制造等必要的知识。

本文档介绍数据库开发和Oracle的基础知识，包括：基本概念、基本命令、技术专题、设计方法、FAQ等，其中部分内容引用其他作者内容。

本文提供大量的操作实例，全部内容贯穿自己的心得和体会。

本文档适合所有开发人员、支持人员等。

2         基本概念

2.1         Schema

Schema   中文意思是   方案.     "方案"只是   Oracle   一个分类,不同用户有不同的方案.  DBA Studio来说吧,在左边分了四大类:(例程,存储,安全,方案)     例程:   是管理库的启动。    存储:   管理表空间,数据文件。     安全:   就是管理用户(密码,表空间分配...)     方案:   有(表,视图,存储过程...),不同用户有不同的案.     如:   在"方案"->选你的"用户名"之后,才会出现用户名相关的表.     就只是一个分类,清晰一点,方便管理。  如 Win2000以上的版本   里的控制面板来说,不是有   管理工具,网络连接...   都是一种分类。主要的是如何建表,视图等等这些内容.

注意事项：

1．如果只建立一个用户,而用户没有(表or存储过程...)的话，在schema里是看不到的.

2.2         Sysnonyms

A  synonym  is  an  alias  for  a  schema  object.  Synonyms  can  provide  a  level  of  ecurity  by  masking  the  name  and  owner  of  an  object  and  by  providing  location  transparency  for  remote  objects  of  a  distributed  database.  Also,  they  are  convenient  to  use  and  reduce  the  complexity  of  SQL  statements  for  database  users.  

Synonyms  allow  underlying  objects  to  be  renamed  or  moved,  where  only  the  synonym  needs  to  be  redefined  and  applications  based  on  the  synonym  continue  to  function  without  modification.   You  can  create  both  public  and  private  synonyms.  A  public  synonym  is  owned  by  the  special  user  group  named  PUBLIC  and  is  accessible  to  every  user  in  a  database.  A  private  synonym  is  contained  in  the  schema  of  a  specific  user  and  available  only  to  the  user  and  the  user’s  grantes.

2.3         视图

用户在一定的限制条件下可以通过视图更新、插入、删除数据。

如果视图连接多个表，那么在一个时间里只能更新一个表。所有的能被更新的列可以在数据字典USER_UPDATETABLE_COLUMNS中查到。

用户在CREATE VIEW中可以使用了WITH子串。WITH READ ONLY子串表示创建的视图是一个只读视图，不能进行更新、插入、删除操作。WITH CHECK OPTION表示可以进行插入和更新操作，但应该满足WHERE子串的条件。这个条件就是创建视图WHERE子句的条件，比如在上面的例子中用户创建了一个视图TOP_EMP,在这个视图中用户不能插入salary小于2000的数据行。

更多的操作细节可以参照：Oracle 9i

1.    修改视图需要create view 和 delete 权限。

2.    表和视图的命令在同一个命名空间中，所以同一个数据库不能有表名和视图名重复。

3.    可以对其他数据库中的表建视图

4.    视图的定义有以下的限制： 1、from子句中不能有子查询 2、select不能指向系统或者用户的变量 3、select不能指向prepared语法参数 4、定义中的表或视图必须存在 5、不能对临时表建视图，也不能建临时视图 6、视图定义中的表名必须已经存在 7、不能在触发器和视图之间建关联

5.    ORDER BY可以用在视图定义中，但是如果访问视图的select中使用的order by，则视图定义中的ORDER BY被忽略。

3         基本命令

3.1.1        ；

在sqlplus环境下， ；表示命令结束。

3.1.2        create

create table dxmp.cfg_sale_agent as select * from usdxmp4.cfg_sale_agent

3.1.3        decode

 

3.1.4        rowid

select rowid,t.* from 表名称 t 

可以直接在toad中，修改列字段中的值。

3.1.5        Insert

1． 如果两个表的结构相同时，可以不要制定列，直接的在两个表之间导数据。

INSERT INTO USDXMP4.DAT_DXMP_INFO select * FROM DXMP.DAT_DXMP_INFO@USDXMP_ZJ

 

2． 如果两个表字段不同时，必须把所有的字段列出来，并且，如果字段少的表向字段多的表导数据时，必须向字段多的表中，多的字段，插入空’’。

INSERT INTO USDXMP4.DAT_DXMP_INFO

              ( SN              , ACCOUNT_ID      ,        CONTRACT_ID     ,

       )    select            SN               ,   ACCOUNT_ID       ,                            CONTRACT_ID      ,                           UNIT_TEL         ,                        PROD_ID,'','',''            FROM DXMP.DAT_DXMP_INFO@USDXMP_ZJ

3．  

3.1.6         Like

1． select t.*, t.rowid from sys_apm_operation t where describe not like '%您好 %'

3.1.7        select嵌套

update cfg_area_code a

set location_id = (select location_id from cfg_location b where a.area_name = b.location_name)

where exists

(select 1 from  cfg_location c

where c.location_name=a.area_name); // 1 仅表示对于选择出来的列进行赋值

 

update cfg_area_code set location_id = substr(location_id,1,4)

where location_id <> area_code and length(location_id) = 6

and area_code in ('010','023','021','022');

 

update usdxmp4.dat_dxmp_info ddi set ddi.location_name =

( select cf.location_name from usboss2.cfg_location@usboss_zj cf

where ddi.LOCATION_ID = cf.LOCATION_ID )

 

两个表关联时，没有相互唯一的字段对应时，也就是一对多时，是不能update的。

          update usbms4.cfg_service_subscriber css set css.SA_ID = (

                    select css1.SA_ID from USBMS2.CFG_SERVICE_SUBSCRIBER@usbms_zj css1

                      where css1.SUBSCRIBER_ID = css.SUBSCRIBER_ID

                      and css.prod_id like 'DXMP%'

                      and substr(css.prod_id,6,3) = css1.prod_id

                 )

         

错误：只能记录数相同才能这样？

INSERT INTO USDXMP4.DAT_DXMP_INFO(location_name)

 select cf.location_name

 from usdxmp4.dat_dxmp_info ddi,usboss2.cfg_location@usboss_zj cf

 where ddi.LOCATION_ID = cf.LOCATION_ID

3.1.8         not exists

1．表一中电话号码在另外一个表中，不存在的电话号码。select  d.* from DAT_YYHB_ACCOUNT d, cfg_order_relation c

where   not exists (select c.ACCOUNT_ID from cfg_order_relation c

where d.acc_number = c.account_id)

或者用：not exists ç=è 0= (select count(*) from ……)

2．组合查询条件

select distinct d.ACC_NUMBER from DAT_YYHB_ACCOUNT d, cfg_order_relation c where ( not exists (select c.ACCOUNT_ID from cfg_order_relation c

where d.ACC_NUMBER = c.ACCOUNT_ID) )

or (d.acc_number = c.account_id and c.service_id  not like 'YYHB%')

 

3.下面方式相同：

select  d.* from DAT_YYHB_ACCOUNT d

where   not exists (select c.ACCOUNT_ID from cfg_order_relation c

where d.acc_number = c.account_id)

 

select  d.* from DAT_YYHB_ACCOUNT d

where   0= (select count(*) from cfg_order_relation  c

where d.acc_number = c.account_id)

 

4.跨不同的表空间的实现方式

select * from vpm2.customer  a  where not exists (select * from vpm2.linkman b

where a.id = b.CUSTOMER_ID )

3.1.9        in

SQL=select sum(c.enterprise_Adver_Rate) from channelorder c where c.available='0' and c.enterprise_Adver_Rate > 0 and  c.channel_Id='PDGG_HMCX1001' and c.channel_order_id in (select channel_order_id from advertisement where begin_date<sysdate and stop_date>=sysdate)

 

嵌套删除的方式：

delete from cfg_sys_user_role csur

where csur.USER_ID

in (

select csur.USER_ID

from cfg_sys_user csu, cfg_sys_user_role csur

where csu.OID = csur.USER_ID

and csu.USER_NAME = 'lanchuangsa'

)

3.1.10     ||

update dat_yyhb_account d set d.AREA_CODE = '0'||d.area_code

或update dat_yyhb_account d set d.AREA_CODE = concat('0',d.AREA_CODE)

 

select '0'||d.area_code from dat_yyhb_account d

 

update dat_yyhb_account d set d.area_code = ltrim(d.AREA_CODE, '0')

3.1.11     And 和 not 和 or 的优先级别

基本所有的语言都是not>and>or  

  true   or   false   and   false  

  =true   or   (false   and   false)  

  =true   or   false  

  =true  

  应该是1

3.1.12    In

delete from usdxmp4.cfg_enterprise_order_relation css

where css.SUBSCRIBER_ID in ('DXMP_0570_468688_HW', 'DXMP_0570_489490_HW', 'DXMP_0571_4508362_HW')

and css.PROD_ID like 'DXMP%'

 

 

select * from  DAT_97_ZX_PARAS

  where sn in (select sn from dat_97_zx d where to_char(d.R_DATETIME, 'YYYYMMDD') = '20070701')

 

UPDATE cfg_subscriber cs

   SET cs.status = '4'

 WHERE cs.subscriber_id IN (

                       SELECT DISTINCT cb.subscriber_id

                                  FROM cfg_bill_subscriber cb

                                 WHERE cb.OID IN (

                                          SELECT DISTINCT c.bill_subscriber_oid

                                                     FROM cfg_service_bill

 

3.1.13    Create view

CREATE VIEW Sales AS

SELECT Customer."Salesperson ID", "Sales Order"."Customer ID", Customer.Name Customer, Product."Product ID", Product.Description Product, SUM(ITEM.TOTAL) Amount

FROM "Sales Order", Item, Customer, Product

WHERE Item."Order ID" = "Sales Order"."Order ID" AND "Sales Order"."Customer ID" = Customer."Customer ID" AND Item."Product ID" = Product."Product ID"

GROUP BY Customer."Salesperson ID", "Sales Order"."Customer ID", Customer.Name, Product."Product ID", Product.Description

3.1.14    1=1

在where后，添加了1＝1，它使得可以直接使用and 关键字。

例如：where.append("  where 1=1 ");

              where.append(" and css.sa_id = csa.sa_id");

3.1.15     (+)

表的外连接，写在where条件的左右，可以到时测试一下， 可以选择。

（＋）外部联结运算符，可以在where子句中使用或使用outer join关键字建立联接。

例如：

sql.append(" sub.SUBSCRIBER_id = mr.account_id (+) ");

        sql.append(" and sub.SUBSCRIBER_id = ts.account_id (+) ");

        sql.append(" and sub.SUBSCRIBER_id = tl.account_id (+) ");

3.1.16    dual

dual是Oracle的一个表，oracle不像sql server可以不要from子句，所以增加了这个虚拟表dual

3.1.17    tab

1.      select * from tab where tname = 'cfg_service'

2.      select tname from tab where tname between'DAT_DR_200606' and 'DAT_DR_200606' order by tname

3.1.18    trunc & to_char()

trunc(sysdate) 只得到日期；to_char(sysdate,'hh24:mi:ss') 或sysdate - trunc(sysdate)可以获得时间；

3.1.19    concat

update CFG_ENTERPRISE_ORDER_RELATION set prod_id=concat('DXMP_',prod_id)

3.1.20    nvl

通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值 select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1这里关心的nvl的用法，nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value

实例：

UPDATE usbms4.cfg_service_subscriber

       SET area_code = nvl(substr(subscriber_id, 7, 3), '???'), location_id = nvl(substr(subscriber_id, 7, 3), '???'),

                     account_id = substr(subscriber_id, 6, 4) || account_id

       WHERE prod_id = 'DXMP_208' AND account_id NOT LIKE '0%';

       COMMIT;

3.1.21    sequence

不能修改值。只能删除后。再建。

3.1.22    having

select * from usdxmp4.cfg_enterprise_order_relation css

where css.SUBSCRIBER_ID in 

(select css.SUBSCRIBER_ID  from usdxmp4.cfg_enterprise_order_relation css  

group by css.SUBSCRIBER_ID having(count(css.SUBSCRIBER_ID )) > 1)

and css.prod_id not like 'DXMP%'

 

select cs.cust_id,  count(*) from cfg_subscriber cs 

group by cs.cust_id having count(*) > 1

 

 

SELECT   subscriber_id, prod_id

    FROM cfg_service_subscriber

   WHERE subscriber_id IN (SELECT   subscriber_id

                               FROM cfg_service_subscriber

                           GROUP BY subscriber_id

                             HAVING COUNT  > 1)

ORDER BY subscriber_id

3.1.23    group by

select cs.cust_id,  count(*) from cfg_subscriber cs  group by cs.cust_id having count(*) > 1

1. 不能在group by使用列的别名。

3.1.24    substr

UPDATE USDXMP4.WF_ENT_ORDER WEO SET WEO.LOCATION_ID = SUBSTR(WEO.SERVICE_AREA_CODE, 2, 3);

3.1.25    nextval

SELECT ('VTMP_DR_'||SEQ_VTMP_SDR.NEXTVAL) TMP_VIEW FROM dual

3.1.26    to_char 和to_date

按时间精确比较

select * from wf_ent_order weo where

weo.LAST_UPDATE_TIME >= to_date('20070615', 'yyyyMMdd')

and weo.LAST_UPDATE_TIME <= to_date('20070616', 'yyyyMMdd')

查询某天的数据

select * from wf_ent_order weo where to_char(weo.LAST_UPDATE_TIME , 'yyyyMMdd') = '20070615'

3.1.27    rownum

1. select * from wf_ent_order where rownum <= 1000 

 

2．区间查询select * from (select rownum rom, sn, ACCOUNT_ID, CONTRACT_ID,AREA_CODE, UNIT_ID from dat_dxmp_info d) where rom between 50 and 100

参照：DrQueryMonthlyReport类，有很多好的sql设计。

sql.append("select * from (");

           sql.append("select rownum rid, drTable.* from (").append(

                         buildReportTableSQL(tmpViewName)).append(

                         ") drTable where rownum <=").append(   // drTable is an alias

                                       (getPager() + 1) * getPageSize());

           sql.append(") where rid > ").append(getPager() * getPageSize());

 

select * from (select rownum rid, drTable.* from ( select dr.service_id service_id, dr.calling_id calling_id, dr.called_id called_id, dr.account_id  account_id, dr.start_time start_time, dr.end_time end_time,  (dr.fee_rate/100) fee_rate, (dr.fee /100) fee, dr.duration duration  from VTMP_DR_28 dr,cfg_service service  where dr.service_id = service.service_id  and dr.start_time >= to_date('200605', 'yyyyMM') and dr.start_time <= to_date('200606', 'yyyyMM') and dr.fee_rate_type = 'MR' order by start_time asc) drTable where rownum <=100) where rid > 0

4         技术专题

4.1         Oralce互连

4.1.1        不同Oracle互连1

1.    不同IP的oralce系统之间可以进行关联。在逻辑上，可以进行建立成一个大的数据库系统。

2.    134．96．71．13 建立了在97营业系统上的中间表的视图，然后可以对97系统上的表进行相关的操作。参见：操作命令。

3.    访问其他网段ip上的数据库，需要建DBlink，然后所有的insert,update,delete添加@dblinkName

4.    134．96．71．13使用的是linux系统。

在建立DBlink之前，必须修改tnsnames.ora这个文件。134．96．71．13目录是：/opt/oracle/product/9.2.0.4/network/admin

4.1.2        不同oracle互连2

1． 当一个Ip上的oracle要访问其他ip上的oracle系统时，需要进行必要的配置（需要一定的权限设置）。

a)        首先，获得对方ip地址、协议、端口号、服务名称。等等。

b)        定位到oracle安装ip地址和文件夹地址。Unix/Linux环境下：echo $ORACLE_HOME，然后进入目录，然后再进入，network/admin/>。 在toad工具上，在TNSnames Editors也可以看文件的配置。

c)        定位到文件tnsnames.ora。 增加一个关联的另外一台oracle数据库。

嘉兴测试库用户名：hmbst 密码为：hmbst   

jxtest_164 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 134.100.64.164)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = jxtest)

    )

  )

注意：有时数据库版本不同，SERVICE_NAME有时需要用SID来表示。

2． 然后，在toad中，建立一个DB link。 输入用户名和密码，建立连接。可以用图形来实现。

例如：create public database link ACCOUNT_97_0573

connect to HMBST

identified by "<pwd>"

using 'jxtest_164';：

3． 建立视图，通过视图来进行访问其他系统的相关的表。

操作命令：

CREATE OR REPLACE VIEW DAT_YYHB_ACCOUNT_0572 as select * from DAT_97_ACCOUNT_0572@account_97_link_13

将产生：

CREATE OR REPLACE VIEW DAT_SYN_CENTER_0573

(ID, AREA_CODE, ACC_NUMBER, ACT_TYPE, ACT_TIME,

 STS, MESSAGE, STS_TIME, PARAMETER_STR, SO_NBR,

 REMARKS, COMMENTS)

AS

select "ID","AREA_CODE","ACC_NUMBER","ACT_TYPE","ACT_TIME","STS","MESSAGE","STS_TIME","PARAMETER_STR","SO_NBR","REMARKS","COMMENTS" from dat_syn_center_0573@ACCOUNT_97_0573（这个是DB link名称）

/

4.2         导数据方法

1． 如果新表和旧表结构不同时，先建立原来相同的表。然后，把数据导入。然后，再修改表的结构。不要把顺序搞反了。否则，工作量会增加很多倍。

2． 在脚本中，在每一个操作之后，一定要添加上;来标识一个操作的结束。

4.3         制造测试数据

可以通过写存储过程构造测试数据。速度快，制造数据脚本可重用性高。

实例：

create or replace procedure insert_prepay ( num in number)

is

    str_sql varchar2(500);

    total number;

begin

    total := 0;

    str_sql:='INSERT INTO usbms41.CFG_SUBSCRIBER_PREPAY (SUBSCRIBER_ID, BALANCE, CREDIT, PRE_CHARGE_FEE, STATUS,

INSERT_TIME, RESERVE ) VALUES (trim(to_char(SEQ_PREPAY_DETAIL_ID.nextval,'||'99999999'||')),12020, 9999, 0,'|| '1'||',sysdate, NULL)';

    while total < num loop

             execute immediate str_sql;

              total := total + 1;

            if total mod 200 = 0 then

                  execute immediate 'commit';

             end if;

    end loop;

    execute immediate 'commit';

  end;

4.4         触发器的使用

数据库触发器常常用来生成审计日志，这种办法方便，且有很好的性能，缺点就是不能跨数据库平台。Hibernate的拦截器也可以生成日志，，它可以不依赖具体的数据库。

4.5         嵌套查询

select * from usdxmp4.cfg_enterprise_order_relation css

where css.SUBSCRIBER_ID in 

(select css.SUBSCRIBER_ ID  from usdxmp4.cfg_enterprise_order_relation css  

group by css.SUBSCRIBER_ID having(count(css.SUBSCRIBER_ID )) > 1)

and css.prod_id not like 'DXMP%'

4.5.1        一个表更新另外一个表字段

update usdxmp4.wf_ent_order weo

set weo.WF_STATUS =

(

select w.WF_STATUS from usdxmp4.wf_ent_order wo, dxmp.wf_ent_order w

where wo.sn = w.SN 

and wo.sn = weo.sn

) where  to_char(weo.SUBMIT_TIME, 'yyyyMMdd') > '20070531'

4.5.2        两个表之间的子查询

delete from cfg_order_relation c where exists

          (select * from  cfg_sys_user cs where c.USER_ID = cs.OWNER_ID

           and c.SERVICE_ID = 'YYHB_0572_0001' and cs.PASSWORD is null ) and rownum < 5

4.5.3        在不同表的字段之间复制值

update

 (select weo.UNIT_ID UNIT_ID,ddi.UNIT_ID UNIT_ID_1 from wf_ent_order weo,dat_dxmp_info ddi

where weo.SN = ddi.SN

and weo.wf_status = '00'

and ddi.UNIT_ID is not null

 )

 set UNIT_ID =UNIT_ID_1

4.5.4        多表之间子查询

     select distinct d.acc_number from DAT_YYHB_ACCOUNT d, CFG_ORDER_RELATION c              where  ((not exists (select c.account_id from CFG_ORDER_RELATION c           where d.acc_number = c.account_id) )

               or (d.acc_number = c.account_id and c.service_id  not like 'YYHB%') )                   and not exists (select cf.personnal_Id from cfg_personnal_cust cf  where d.acc_number = cf.personnal_Id)

4.6         时间专题

4.6.1        精确到小时和分钟和秒

select * from usbms2.cfg_service_subscriber css

where to_char(css.BOOK_TIME, 'yyyyMMddhh24mmss') > '20070604120000'

 

select acct_deal_batch,count(*) record_counts,sum(fee) total_fee

from   dat_third_dr

where  start_time >= to_date('20051121000000','yyyymmddhh24miss')

and    start_time <= to_date('20051208235959','yyyymmddhh24miss')

group  by acct_deal_batch

4.6.2        to_char不同使用方式

 1．       sql.append(" and ( status = 1 or ( status = 2 and to_char ( css.quit_time,'yyyyMMdd') >= '");

        sql.append(DateFormatUtils.format(getStartDate(), "yyyyMMdd"))

                .append("')");

to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)

to_char(viewDr.end_time, 'hh24:mi:ss')

 

delete from CFG_ENTERPRISE_ORDER_RELATION ceor where to_char(ceor.INSERT_TIME, 'yyyymmddhhmm24ss')  > '20070309171712'

                                                          

select count(*) from CFG_ENTERPRISE_ORDER_RELATION ceor where to_char(ceor.BOOK_TIME, 'yyyymmddhh24miss')  > '20070309171712'

4.6.3        关于时间和日期的实例

sysdate即是Oracle提供的取系统时间的函数

1.日期时间间隔操作 　　当前时间减去7分钟的时间 　　select sysdate,sysdate - interval ’7’ MINUTE from dual 　　当前时间减去7小时的时间 　　select sysdate - interval ’7’ hour from dual 　　当前时间减去7天的时间 　　select sysdate - interval ’7’ day from dual 　　当前时间减去7月的时间 　　select sysdate,sysdate - interval ’7’ month from dual 　　当前时间减去7年的时间 　　select sysdate,sysdate - interval ’7’ year from dual 　　时间间隔乘以一个数字 　　select sysdate,sysdate - 8 *interval ’2’ hour from dual 2.日期到字符操作 　　select sysdate,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual 　　select sysdate,to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual 　　select sysdate,to_char(sysdate,’yyyy-ddd hh:mi:ss’) from dual 　　select sysdate,to_char(sysdate,’yyyy-mm iw-d hh:mi:ss’) from dual 　　参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515) 3. 字符到日期操作 　　select to_date(’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual 　　具体用法和上面的to_char差不多。 4. trunk/ ROUND函数的使用 　　select trunc(sysdate ,’YEAR’) from dual 　　select trunc(sysdate ) from dual 　　select to_char(trunc(sysdate ,’YYYY’),’YYYY’) from dual 5.oracle有毫秒级的数据类型 　　--返回当前时间 年月日小时分秒毫秒 　　select to_char(current_timestamp(5),’DD-MON-YYYY HH24:MI:SSxFF’) from dual; 　　--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9) 　　select to_char(current_timestamp(9),’MI:SSxFF’) from dual; 6.计算程序运行的时间(ms) 　　declare 　　type rc is ref cursor; 　　l_rc rc; 　　l_dummy all_objects.object_name%type; 　　l_start number default dbms_utility.get_time; 　　begin 　　for I in 1 .. 1000 　　loop 　　open l_rc for 　　’select object_name from all_objects ’|| 　　’where object_id = ’ || i; 　　fetch l_rc into l_dummy; 　　close l_rc; 　　end loop; 　　dbms_output.put_line 　　( round( (dbms_utility.get_time-l_start)/100, 2 ) || 　　’ seconds...’ ); 　　end;

7．插入系统时间

insert into aa('11', sysdate) 其中：sysdate即是Oracle提供的取系统时间的函数

4.6.4        to_date()

sqlWhere.append(" and viewDr.start_time >= to_date('").append(

                         DateFormatUtils.format(getStartDate(), "yyyyMM"))

                         .append("', 'yyyyMM')").append(

                         " and viewDr.start_time <= to_date('").append(

                                       DateFormatUtils.format(newEndDate,"yyyyMM")).append(

                                       "', 'yyyyMM')").append( 

                                       " and viewDr.fee_rate_type = 'MR'"); // 包月类型

 

1． dr.service_id = service.service_id  and dr.start_time >= to_date('200606', 'yyyyMM') and dr.start_time <= to_date('200606', 'yyyyMM') and dr.fee_rate_type = 'MR' order by start_time asc)

4.6.5        oracle取得系统时钟

SELECT SYSDATE AS xxx FROM DUAL

1.      sysdate是Oracle的系统函数，用于取得系统时钟，这其中包括了日期和时间。 例如： select *  from cfg_order_relation where (sysdate-insert_time <30 )

2.      trunc(sysdate) 只得到日期；

3.      to_char(sysdate,'hh24:mi:ss') 或sysdate - trunc(sysdate)可以获得时间；select trunc(sysdate) from dual

4.      select trunc(sysdate ,'YEAR') from dual

5.      select CUSTOMER_CODE, PATTERN_OVER_TIME from CUSTOMER where PATTERN_OVER_TIME < sysdate

4.6.6        where中的时间使用

1、 select *  from cfg_order_relation where (sysdate-insert_time <30 )

2、 d dr.start_time >= to_date('200605', 'yyyyMM') and dr.start_time <= to_date('200606', 'yyyyMM')

3、 select * from dat_dxmp_info ddi

where ddi.STAFFNO = 'jxsa' and ddi.CREATE_DATE > to_date('2006-12-30', 'yyyy-mm-dd')

4、 ddi.CREATE_DATE > to_date('20061230', 'yyyyMMdd')

select sysdate, sysdate -  ddi.CREATE_DATE, ddi.CREATE_DATE, ddi.sn, ddi.STAFFNO from dat_dxmp_info ddi

where ddi.STAFFNO = 'jxsa'

and sysdate - ddi.CREATE_DATE < 3.1          // this is a day’s number

5、 查询具体某一天的数据量 要采用like 关键字

select ddi.CREATE_DATE from dat_dxmp_info ddi

where ddi.CREATE_date like to_date('20061230', 'yyyyMMdd')

 或者采用另外的一种方式：

select count(ddi.sn) count from dat_dxmp_info ddi

4.6.7        where to_char(ddi.CREATE_DATE, 'yyyyMMdd') like '20061230'SYS用户登陆问题

1． 原因：用SYS用户登录数据库时，语法与其它操作用户写法不同，必须写明登录权限。 解决方法：使用正确的书写方式。 例如：SYS/口令@服务名 AS SYSDBA；

2． 几种连接用到的命令形式 1.sqlplus / as sysdba 这是典型的操作系统认证，不需要listener进程 2.sqlplus sys/oracle 这种连接方式只能连接本机数据库，同样不需要listener进程 　　 3.sqlplus sys/oracle@orcl 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。 　　

以上连接方式使用sys用户或者其他通过密码文件验证的用户都不需要数据库处于可用状态，操作系统认证也不需要数据库可用，普通用户因为是数据库认证，所以数据库必需处于open状态。

4.7         默认对象

1． select * from all_objects where object_name = 'PK_WF_ENT_ORDER_HISTORY'

4.8         normal、sysdba、sysoper、sys、sysdba、dba

4.8.1        sys和system用户的区别

【system】用户只能用normal身份登陆em。 【sys】用户具有“SYSDBA”或者“SYSOPER”权限，登陆em也只能用这两个身份，不能用normal。 “SYSOPER”权限，即数据库操作员权限，权限包括：   打开数据库服务器   关闭数据库服务器   备份数据库       恢复数据库   日志归档         会话限制 “SYSDBA”权限，即数据库管理员权限，权限包括：   打开数据库服务器   关闭数据库服务器   备份数据库       恢复数据库   日志归档       会话限制   管理功能       创建数据库

4.8.2        normal 、sysdba、 sysoper有什么区别

normal 是普通用户 另外两个，你考察他们所具有的权限就知道了 sysdba拥有最高的系统权限 sysoper主要用来启动、关闭数据库，sysoper 登陆后用户是 public sysdba登陆后是 sys

SQL> conn / as sysdba 已连接。 SQL> grant sysoper to test;

授权成功。

SQL> conn test/test as sysoper; 已连接。 SQL> show user USER 为"PUBLIC" SQL> conn test/test as sysdba 已连接。 SQL> show user USER 为"SYS" SQL>

4.8.3        dba和sysdba的区别

dba、sysdba这两个系统角色有什么区别呢 在说明这一点之前我需要说一下oracle服务的创建过程 ·创建实例 ·启动实例 ·创建数据库(system表空间是必须的) 启动过程 ·实例启动 ·装载数据库 ·打开数据库

sysdba，是管理oracle实例的，它的存在不依赖于整个数据库完全启动， 只要实例启动了，他就已经存在，以sysdba身份登陆，装载数据库、打开数据库 只有数据库打开了，或者说整个数据库完全启动后，dba角色才有了存在的基础

5         设计方法

1.    表之间没有关联，middlegen生成后，也没互相设置，对方的对象的变量，但是它们也是可以互相程序来把它们关联起来。要有业务逻辑来决定。

2.    如果有两个表有实际的关联关系时，在ER Studio就划个连线，在生成code时，在类之间就有了联系。操作时，就更加方便。要根据具体情况来决定。

3.    由于新业务的要添加。需要添加新的表，如果的确和原来的表有必要关联，则需要修改po对象，和hbm之间的关联。这样操作方便。如果不这样做。也可以从程序层把他们关联起来。这样可以降低一定的耦合度（？）。

4.    当建表时，最好预留一定的保留字段，方便以后的扩展。根据具体情况，有时容易造成冗余信息。

5.    当建一个表时，最好要建一个创建时间字段。 这样可以方便的定位数据插入时间。

6.    表与表之间的多对多的关系时，要分拆为两个1对多的关系。当两个表建立关联时， 将产生一个关联属性。例如：学生和课程的关系， 学生id和课程id，他们的之间的关联属性是：成绩。这也是这两个id所能确定出来的一个标识。例如：订单和商品的关系，订单id和商品id可以唯一的定位关联属性：产品数量。例如：客户和短信表的关系，他们的关联属性是：发送次数。

7.    如果一个实体（或称为对象），有一个对象类型，例如：一个电话，有个电话类型字段，可以有公司、家庭、移动。The type of phone number, such as business, home or fax. 参见数据库设计技术。可以根据这个类型的不同来建立一个单独的表。

8.    建立一个基本的表，然后可以建立一个关联的Detail表，来描述基本表中的详细信息。

9.    部门领导和普通员工，可以建立一个自身的关联。可以是1对多的关系。参见，ER工具的实例。

10.    建一个表，一些基本的字段包括：ID，名称，描述，插入时间，备注信息。

11.    如果有不同的程序，或不同的合作方，共同的操作一个表时，一方要修改字段，然后读取字段信息时，建字段的方式，可以是： status(0表示原始 1 失败) message（成功，失败的原因）， 失败的message最好是发送的源头来写，因为它是源头，这样，读数据方，就可以直接的读了。

12.    当涉及到与固定电话的设计时，最好设计区号和带区号的电话。区号：当涉及到多个表操作时，可以方便的通过它来定位，不用通过程序每次都解析带区号的电话。带区号的电话: 可以方便业务处理，通过这一个电话可以方便的定位到具体的一条记录。

13.    在一个组织内，如果存在多用用户类型。不同的角色的用户，他们的字段也相差很多。可以先建立一个父表，存储公共信息，增加一个用户类型字段，来区别不同的用户。然后，员工表父表，飞行员子表、修理市为子表。员工表和其它两个表建立1：1的关系。采用相同的主键。这样就比较清晰。主键可以通过sequence来生成来，保证唯一性。

14.    如果主键是sequence，但是还想让另外一个字段，例如：如果还需要userName唯一，可以在数据库上配置为建唯一性索引。保证唯一性。

15.    对于关键数据，最好键入插入时间，这个插入时间，可以直接采用数据库的时间。Sysdate。

16.    如果要使得一个表中几个字段是唯一的，需要把几个键建成一个唯一性索引，Unique 属性一定要选中。

17.    主键标识。 通常命名为：PK＋表名

18.     

6         sql-plus

1.        只要安装oracle客户端，在dos下也可以连接数据库，或者直接用oracle的sqlplus。sqlplus wf97@10.130.24.173-zj114 wf97

wf97@10.130.24.173-zj114表示数据库名称

2.        start or @ 执行脚本文件。例如：@E:/USBOSS-4.1/cfg_sys_action.sql

3.         

7         学习阶梯

1.      装上一个   Oracle   数据库。

下载资源 http://otn.oracle.com/software/products/8i_personal/index.html

2.      基础入门。www.oradb.net

3.      电子书。www.pdown.net             http://www.studa.com/newdown/soft/713.htm     http://61.144.28.248:8080/d6i/bbs_armok01/bbs_content.jsp?bbsSerialNo=7722&bbsPageNo=1

4.      官方文档最有权威。http://otn.oracle.com/documentation/oracle8i_arch_816.html

5.      论坛www.oracle-dev.com   （开发）               www.itpub.net               otn.oracle.com

6.      榜样的力量

  http://www.ncn.cn/oracle/admin/2003003.htm     http://www.cnoug.org/bin/ut/topic_show.cgi?id=1657&h=1&bpg=1&age=30

建议: 少买书,看相关网站的   FAQ   &   精华区;多动手;多思考;

8         FAQ

1． 当查询语句比较复杂时，将报写文件错误。

答：在TablesSpaces/Temp，给temp文件分配更大的空间；如果，temp文件所在目录下已经没有空间，可以考虑分配一个其他目录下的建临时文件，视具体情况来分配。

2． 如果使得一个字段或多个字段的数据在表中是唯一的？

答：可以通过创建唯一性索引。建主键时，将默认建一个唯一索引。例如：

CREATE UNIQUE INDEX PK_CFG_SERVICE ON CFG_SERVICE (SERVICE_ID)

3． 存在约束时，不能删除表中数据。

答：把其他表对该表的约束，先设置为失效。等操作完数据后，在改为生效。

4．  

 

 

9         参考资料