2010 Oracle总结

 

自打毕业之后，基本就与Oracle数据库打交道了。在学校做项目的过程中，用SQL Server比较多，MySQL也做过，Oracle的话应该说是见得比较多的一个数据库。为什么这么说了，因为在中软实习的时候，当时带团队做项目用的就是Oracle数据库，不过当时用的还是9i版本，然后到留服中心实习的时候，测试一直用的数据库也是Oracle，再然后毕业设计的时候用的也是Oracle，毕业后到公司之后用的数据库仍然是Oracle。看来Oracle确实市场比较大，基本我到过的公司用的都是它，由此看出它的强大。尽管毕业前接触过Oracle，但只是用于简单功能实现，因此利用工作的积累外加业余时间的学习，对Oracle的认识应该比以前有了一定的提高。但是目前的提高只是在于应用上，因此来年对Oracle数据库的认识需要在核心本质上得到认识。

1.  润乾报表对数据库的使用

2.  Oracle用户的认识

3.  Oracle数据字典

4.  约束

5.  索引

6.  SQL优化

7.  游标的书写

8.  过程的实现

9.  函数的实现

10.触发器的实现

11.Oracle与Java的接触

 

一．润乾报表对数据库的使用

在毕业前没有使用过报表工具，毕业设计上的报表也是通过代码自己进行实现，所以专业一点的报表工具到毕业之后才开始进行使用。七月份到现在为止的六个月的时间，有大概两个多月的时间一直在开发报表，尽管很讨厌那东东，但也算是撑过来了。下面对其进行简略的回顾。

1.  润乾报表的使用

（略），这个在专门的润乾报表的开发中进行回顾讲述。

2.  SQL语句的填充

打开一个报表之后，选择配置-》数据集（或者F11），此时将打开数据集设置及的窗口（图一），此时将发现有个数据集名称ds1，并有数据源。

数据源是需要自己进行配置的，由于开发过程中基本是将报表嵌入页面上进行查看，所以很少直接拿报表进行预览查看的。因此这个数据源的配置需要在Web工程中的配置文件里对其进行配置。

<jdbc-ds-configs>

          <jdbc-ds-config>

                <name>INFOSYSDATA</name>

                <db-type>1</db-type>

                <connection-url>

                      jdbc:oracle:thin:@10.207.9.11:1521:epdata

                </connection-url>

                <driver-class>

                      oracle.jdbc.driver.OracleDriver

                </driver-class>

                <user-name>sipcktkf</user-name>

                <password>sipcktkf123</password>

                <db-charset>gbk</db-charset>

                <client-charset>gbk</client-charset>

                <extend-properties>ddd;dd;dd;</extend-properties>

          </jdbc-ds-config>

   </jdbc-ds-configs>

      

 

（图一）

      双击之后，将写好的SQL语句粘贴进去即可，需要传递的参数用？进行替换，并在参数中进行配置即可。

      

 

（图二）

 

1.  报表中的SQL语句必须好好优化。由于报表很多都使用了统计数据，因此需要进行基本的数据运算。因此尤其需要注意除法的情形。另外报表中配置的参数类型需要注意。调试报表中的经验就是“进和出的判断”，进：看传进来的参数是否符合，是否传进来，是否数据正确

出：判断SQL执行出来的数据填充到报表的异常可能。

2.  报表中如果业务使用分组统计时，此时使用Oracle的一些基本函数挺有优势的。例如decode函数。

一．Oracle用户的认识

Oracle中用户用来干嘛？Oracle内部有两个建好的用户：System和Sys。其他用户的建立需要分配相应的表空间和权限。

一般在数据库中，一个用户的连接称为建立一个session，如果一个新的用户需要访问数据库，则必须授予创建session的权限。

查看系统用户权限：select * from user_sys_privs;

二．Oracle数据字典

列出某用户下的所有表：

select table_name,t.* from all_tables  t where t.owner=upper('hwkt');

列出ZZKT03表中的所有字段：

select * from all_tab_cols t where t.owner=upper('hwkt') and t.table_name='ZZKT03';

列出表的索引列：

select * from sys.all_ind_columns t where t.table_name='ZZKT03' ;

列出表的约束，有主键和外键：

select * from sys.all_constraints t where t.table_name='ZZKT03';

描述数据字典的视图：

select * from sys.dictionary t where t.table_name like '%ZZKT03%';

列出所有的索引：

select * from sys.user_indexes t where t.table_owner=upper('hwkt');

找出表的索引：

select * from sys.user_ind_columns t where t.table_name='ZZKT03';

找出用户下的索引：

select * from sys.user_triggers t ;

三．约束

1.  NOT NULL：非空约束

2.  PRIMARY KEY：主键约束

3.  UNIQUE：唯一约束，值不能重复（空值除外）

4.  CHECK：条件约束，插入的数据必须满足某些条件

age NUMBER CHECK(age BETWEEN 0 AND 150)

5.  Foreign Key：外键

在建立外键的时候可指定级联删除(ON DELETE CASCADE)

pid NUMBER REFERENCES person(pid) ON DELETE CASCADE

6.  删除约束

ALTER TABLE book DROP CONSTRAINT per;

alter table student drop unique(tel);--唯一约束

7.  启用约束

ALTER TABLE book enable CONSTRAINT per;

8.  禁止约束

ALTER TABLE book disable CONSTRAINT per;

四．索引

索引是一种用于提升查询效率的数据库对象，减少磁盘I/O操作，加速表之间的联系。索引是实际物理存在，因此它需要占据一定的物理空间。

在实际SQL运用过程中，并不是说索引使用越多越好，毕竟占有一定的空间大小，而且索引在表数据的DML操作时，也需要对索引进行维护，影响系统的性能。

那么根据日程项目中，什么情况下需要使用索引了？

1.选择性高的列做索引，例如什么ID的那些东东，反正把它做主键就行。

2.where条件下经常使用的连接条件可以考虑做索引

3.……

五．SQL优化

对了，差点忘记插入表数据的时候，可以采用这种方式：

Insert into A（……） select * from Table；

下面接着讲SQL的优化：

一直听别人说SQL优化，那么到底是SQL优化了。我觉得简单点来说，sql优化就是为了提高检索速度，减少运行时间。也就是说优化的实质是在结果必须正确的前提下，充分利用索引，执行中访问尽量少的数据块，减少I/O操作，避免全表扫描及额外开销。

毕业那会，写报表SQL的时候，就明确要求少些*，只需要将需要检索的列给整出来即可，避免不必要的开销。

In操作符在目前系统中我看使用的还是比较多的，为什么了？容易理解啊，但是in操作符基本都可以用exists代替，对于到底用in还是exists得视具体情况而定。

以前对exists的使用并不多，主要比较懒吧，不想动脑子，而且项目中使用几个in，性能方面没看出多大的区别，所以一直就那么用着。但今天既然说到这，还是将in和exists的区别进行分析下：

In的sql语句在这里就不做解释，很简单，检索出某列在哪个范围，一般用在枚举出的字段。Exists可能在某些方面稍微难点，其实只要理解执行流程（驱动顺序）也就好说了。

举个例子：

select * from table t where exists ( select a from t2 where y =t. x )

exists已外层表作为驱动，先访问外面的表数据，然后再访问子表。In则相反，先执行子表，然后在通过匹配检索数据。因此当子查询得出的数据集记录少，但主查询的表数据较多而且还有索引的情况，完全可以使用in。但子查询的记录较多的情况，建议还是用exists吧。

另外在开发过程中有时候使用<>或者!=的情况，这样处理的话照理不会使用到索引，它会对整个表数据进行检索。我们可以采用> or <进行处理，因为这样有索引列，避免全表扫描。

%的使用也和上面方式一样，避免%A%这种情况，因为这样理论上也会产生全表扫描，前面的%可以采取固定的数据表示，例如BA%。

如果索引不是基于函数的，那么索引列放到函数里面将不会起作用。例如

Substr（a,0,4）=1111,那么怎么解决了，可以采用like的方式。

>=的情况也得考虑下，假若这么种情况，A>3000,那么A得找出3000的记录索引，假如3000的情况有好几十万条，那么理论上数据库运行时会多扫描几十万条数据，建议改为>=3001

另外sql语句个人建议最好带上表名，至少解释编译的时候能够少占点时间，否则数据库还得分析字段是属于哪个表，这样的开销太不值得。

另外存储过程的使用可以提高效率，毕竟过程是经过编译之后，所以对于大数据的检索挺有优势的。

上次听讲座说将经常使用的视图数据存到物理表中，这种方式有优势也有劣势。首先只适合于不经常变动的数据，另外变动过程中对数据的维护也需要时间，而且当数据量达到一定程序，效果应该不会那么明显。但是不可否认，该方式对于静态的数据（理解为不动数据吧）还是有一定的优势，毕竟到时处理数据是单表操作，数据集扫描数量有明显的减少，而且表间联系也能减少很大的开销。在一定程度上有可用性。

（这些优化很多都是根据索引进行优化，而且理论上的东东。但有道理，欢迎有其它好的优化方法提供。）

六．游标的书写

游标这东东吧，挺好用。至少我现在能用oracle编程实现的我就用oracle实现，毕竟这是脚本文件，拿哪都好用。

游标是一种pl/sql控制结构，可以对sql语句进行显示控制，主要用于对表的行数据进行处理。目前我用的基本也就是对表的行数据的处理。

写游标的代码段，当然免不了逻辑的判断。这里也不做详细介绍，看个代码段就成了。

先说我的目标吧。当前我一个表中存在着统计的SQL语句，而且该SQL语句有一个特色，带上“？”这样的占位符，我现在想统计一下报表中的SQL哪些存在数据。如果我用java进行处理的话，无非也就这么个思路：

1.  需要对占位符采用正则表达式的匹配。这里不是要统计sql执行出来是否有数据吗？那好，我的经验是将？改为’’ or 1=1，很强大吧，最基本的数学概念嘛！真命题，假命题。

2.  采用JDBC的方式将SQL放到rs中执行，因此在这里述说下游标。游标其实就相当于rs，然后一条一条sql语句进行执行呗。

3.  说到这就将游标的基本步骤说一下：声明游标，打开游标，取出结果，关闭游标。

好了，现在就将游标的编写用代码形式进行描述。

-- Created on 2010/12/22 by LW 

declare
  sqlInfo ktsys.sys_report_file%rowtype; --这样可以使用多行记录数据,注意该语句必须写在前面那个游标的前面

  cursor mycur is
    select *  into sqlInfo from ktsys.sys_report_file t
    where t.is_page = '1' and t.FI_ID>=999 and t.FI_ID<=12370
   and t.re_sql is not null order by to_number(t.FI_ID) asc; --建立游标 
  cnt           integer;
  re_sql        ktsys.sys_report_file.re_sql%type;
  re_sql_regexp ktsys.sys_report_file.re_sql%type;
  i             integer;
  j             integer;
begin
  if (mycur%isopen) then
    null;   --判断游标是否打开，如果打开就不用做处理，如果未打开的话打开游标
  else
    open mycur;
  end if;

   loop
    fetch mycur
      into sqlInfo; --使游标向下一行
    exit when mycur%notfound;
    cnt := mycur%rowcount;
    select instr(sqlInfo.Re_Sql, '(+)', 1) into i from dual;
    if i>0 then
      DBMS_OUTPUT.put_line('左右连接不好判断的报表：' || sqlInfo.Filename);
    else
      select regexp_replace(regexp_replace(sqlInfo.Re_Sql,'\(\+\)',''), '\?', ''''''''' or ''a''=''a''')   --正则表达式的匹配，这个是整个代码的关键

        into re_sql_regexp

        from dual;

      re_sql := re_sql_regexp;

      updateView(re_sql, j);  --这里用到了过程。先把我书写的过程放出来

      if j = 1 then

        dbms_output.put_line('==该报表没有数据==' || sqlInfo.Filename);

      elsif j = 2 then

        dbms_output.put_line('==该报表有问题==' || sqlInfo.Filename);

      else
       dbms_output.put_line('==该报表有数据==' || sqlInfo.Filename);
      end if;
    end if;
  end loop;
  dbms_output.put_line(Concat('多少记录：', cnt));

end;

  

 

      过程代码的实现：

create or replace procedure updateView(str varchar2,j  out integer) is

  i int;

begin

  execute immediate str    into i;

  if i > 0 then

    j:=0;

  else

    j:=1;   

  end if;

  

exception

  when no_data_found then

    dbms_output.put_line('没有数据，中断：');

  when others then

       j:=2;

       null;

end updateView;

 

 

对于过程的讲述拿到下面，提前讲个知识点。updateView(re_sql, j)。此时的j属于不带值进，带值出。所以这里尽管j没有赋初值代码是没有问题的。另外execute immediate str    into i;属于动态执行sql。试想下，因为我们正则表达式匹配之后的sql语句，必须执行它。但这SQL是动态的，并不是一个静态的SQL语句，它是从表中取出数据进行匹配之后的sql语句，而且需要给其配相应的into语句，所以采用一般方式不好处理，因此这里采用动态执行sql的方式。我一般代码都加注释，所以这里就不做解释了。

评论

1 楼 liuwei8728 2011-01-11  

有时间再整理下吧，javaeye原来这么点字都不支持啊。