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的方式。我一般代码都加注释,所以这里就不做解释了。