oracle基础知识及应用.ppt

,Oracle 基础知识及应用 2008-09,浪潮通信信息系统有限公司,目标,通过该培训你应该能够掌握如下知识: 掌握基本的oracle数据库的用户对象，如table、view、function、procedure、job、trigger、package等等 能够在PL/SQL Developer里实现比较复杂的操作，如各种复杂的查询、修改、删除,能够利用函数和过程完成sql语句不能完成的工作 能够在程序上依赖于数据库实现你的业务逻辑 能够在数据库逻辑设计上呈现对你所了解的业务知识，对当前程序的设计有更深层次的了解,基础知识介绍 1.1 表 1.2 视图 1.3 函数 1.4 过程 1.5 任务 1.6 触发器 1.7 包 2. 对oracle数据库闪回的介绍 3. 查询上的优化案例 3.1 优化规则分析 3.2 表关联 3.3 优化案例分析,目录,1.1表,相关知识 建表语句 表的属性和约束 对表的操作 常用数据类型 数据查询及查询时注意事项 特殊字符 操作符 修改操作 删除操作 数据的导入导出,1.1.1建表语句,举例： create table GCA001 ( CELLID VARCHAR2(15) not null, CELLNAME VARCHAR2(30), STARTDAY NUMBER(8) not null, STARTTIME NUMBER(6) not null, PERIOD NUMBER(4) not null, REGIONID VARCHAR2(15), REGIONNAME VARCHAR2(20), GCA0010010 NUMBER, GCA0010020 NUMBER ) create unique index GCA001_INDEX_1 on GCA001 (STARTDAY, STARTTIME, PERIOD, CELLID); create index GCA001_INDEX_2 on GCA001 (STARTDAY, STARTTIME, PERIOD, REGIONID, MSCID, BSCID, BTSID); 说明：培训用到的用户为oracle数据库自带的hr用户，这个用户下几乎包含了能用到的所有数据库对象；是oracle培训经常使用的数据库典型案例。我们也是在这个用户下进行。,主键： 主键是用来唯一表示一条数据的字段,其值不能重复。 外键： 参照其它表的某个或者某些字段作为该表该字段的约束 非空： 该字段不能为空 索引： 索引就像一本书的目录，对常用字段建议单独建立索引 唯一索引： 功能同主键，但是可以为空 默认值： 指定该字段默认的值是什么。可以以pm系统的num字段为例 问题讲解： 当前在pm系统中为什么要给每个表建主键或者唯一索引？,1.1.2 表的属性和约束,添加一个字段： 举例：alter table TEST1 add test3 number default 1 not null; 删除一个字段： 举例：alter table TEST1 drop column TEST3; 更改字段类型或宽度： alter table TEST1 modify TEST1 VARCHAR2(100); alter table TEST1 modify TEST2 number not null; 修改约束： alter table TEST1 drop constraint DDDDDD cascade; alter table TEST1 add constraint DDDDDD primary key (TEST, TEST1) using index 删除表： 举例：drop table test3 创建表的其它方式： 举例：create table newtable as select * from table1 where 1=2 系统默认表： 举例：select sysdate from dual; 对这个表一般没有更改的操作。,1.1.3 对表的操作,Number Varchar2和char Date Row() Table() Integer Blob Clob Long Timestamp 自定义数据类型 等等 重点讲解：number、clob、row()、table()、自定义类型 举例：CREATE OR REPLACE TYPE “TY_STR_OF_SPLIT“ is table of varchar2(1000),1.1.4 常用数据类型,1.1.5 数据查询及注意事项,1.查询举例： 自关联的例子：列出员工名字和他上司名字 select worker.last_name , manager.last_name from employees worker , employees manager where worker.manager_id = manager.employee_id; 练习：列出底层员工的名字（没有下属） select manager.last_name from employees worker , employees manager where worker.manager_id(+) = manager.employee_id and worker.last_name is null;,1.1.5 数据查询及注意事项,2. 利用字典表进行辅助查询 常用字典表： user_tables user_tab_columns user_views user_indexes user_ind_columns user_constraints user_cons_columns user_objects user_source 举例： select select * from |table_name|; from user_tables; select update table |table_name| set bscname=eeeee where bscname=ddddd and starttime=0; from user_tables union select update table |table_name| set bscname=eeeee where bscname=ddddd and starttime=10000; from user_tables,1.1.5 数据查询及注意事项,3. 注意事项： 注意NULL的处理： （NULL和任何数值，做算术运算，结果为NULL） 0的处理:如分母为0的情况可用div()函数来处理 特殊字符的处理，如： 当列别名中有空格时，必须用双引号： select last_name , nvl(to_char(manager_id),no manager ) “mg r” from employees;,1.1.6 特殊字符,1.Null 专门处理null的函数 nvl() nvl(数值，0) nvl(日期，sysdate) nvl(字符，nooo) 注意： 两个参数的数据类型，必须一致 NULL和任何数值，做算术运算，结果为NULL，所以有时候是必须要进行转换 Order by排序时，是作为最大值处理的 Count()运算时，有null的字段，不能代表真实的结果,1.1.6 特殊字符,2. %和_ 这两个字符作为通配符来处理 举例 select * from user_tables where table_name like H_%escape 其中 代表任何长度的任意字符 _代表一个英文字符 Escape表示为通配符，以便于对通配符_进行转义,1.1.6 特殊字符,3. |连接符 该字符是Oracle的连接字符 举例： Select truncate table |table_name|; from user_tables;,1.1.6 特殊字符,4. 和“ 是Oracle字符常量的表示字符 如：history_sql varchar2(2048):=select sysdate from dual; 在字符串常量里也是自身的转义字符 如：time_str=select startday|lpad(starttime,6,|0|) as eventime from dual “ 是一个不常用的字符，但是当列别名中有空格时，必须用双引号进行查询， 如： select last_name , nvl(to_char(manager_id),no manager ) “mg r“ from employees;,1.1.7 操作符,在这里我们把比较运算符和关联查询的运算符等都列举在这同一成为操作符，这是不严格的。这些操作符有： like and or Exist = Between and In not in Is null , is not null 等等 注意：在大数据量查询时不要使用in来进行操作，否则会极大的影响效率。,1.1.8 update和delete,Update Update操作是对数据表的修改操作，大致语法举例如下： update table test set test1=xxx,test2=yyy where Delete Delete操作是对数据库记录的删除操作，大致语法举例如下： Delete * from test where test1=xxx and test2=yyy 注意事项 为了查询上的一致读和数据的恢复Update 和 delete都是占用回滚段的操作，当update或者delete要操作的数据量比较大时需要把逐步操作，例如3个月的数据量太大在update是需要一天天的数据进行删除，或者每10000行删除一次。否则会造成回滚段在瞬间暴涨，如果此时磁盘没有足够空间会造成数据库down掉等一系列的问题。,1.1.9 数据的导入/导出,数据的导入/导出在实际应用中常用的方法有3个如下 1.在excel和PL/SQL Developer之间的复制和粘贴数据，这种方式适用于比较小的数据量，其详细的操作见：excel和oracle_plsql之间的数据复制.doc 2.利用pl/sql Developer自带的工具导入/导出。其中经常用的导入导出命令是imp和exp，如下： imp username/passwordsid file=c:/test.dmp log=c:/testimp.log fromuser=nigeria_ad touser=nigeria_ad tables=(tab1,tab2,) exp username/passwordsid file=c:/test.dmp log=c:/testexp.log 3.使用sqlload 对数据进行导入，这种方法适合把固定格式的文本文件导入到数据库，如字段用|分割的文本。其详细的操作方法见：sqlload数据录入指南.doc,1.2 视图,相关知识 视图的概念和特点 视图的分类 视图的作用 使用视图 使用视图时注意问题,2.1 视图的概念和特点,视图在物理上不存储数据，是一张虚表； 它代表了一个查询的结果关系，或者说定一个了一个被存储的查询： CREATE OR REPLACE FORCE|NOFORCE VIEW viewname (alias, alias.) AS subquery WITH CHECK OPTION CONSTRAINT constraint WITH READ ONLY CONSTRAINT constraint; 注意： 后面的约束条件不常用，但是比较有用。,2.2 视图的分类categories,简单视图： 来源于一个表的数据，不含有group by,无组函数，可以insert,update,delete 复杂视图： 来源于几个表的数据,可能含有group by，不可以insert,update,delete 组函数： avg,min,max,sum,count,2.3 视图的作用purpose,简化查询：main simplify 对一个经常使用的查询可以定义为视图，如 top n 等 对非常复杂的查询第一个视图以便于查询，如对汇总数据的查询等 屏蔽敏感数据：security sensitivity 可以通过视图仅给用户开放无关紧要的字段； 可以通过视图仅给用户开放无关紧要的记录； 可以约束视图为read only，只允许用户查询数据； ,2.4 使用视图（举例）,例1 ：创建一个视图，内容是： 员工的工资比直接上司的工资高，列出员工名和工资，上司的名和工资 答案： create or replace view v1(wname,wsalary,mname,msalary) as select w.last_name,w.salary,m.last_name,m.salary from employees w,employees m where w.manager_id=m.employee_id and w.salarym.salary,2.4 使用视图（举例）,例2 ： 练习：随意创建一个只读视图（只能查询不可以修改） 示例： create or replace view v4 as select * from employees where department_id=50 with read only; 例3： 思考tips：1.为什么查询视图比直接查询表满，怎么可以是查询的速度提高？A little bit 2.现在的系统应用中有哪些地方能够用到视图，并且应用后效果和效率都会更好？current,2.4 使用视图时注意问题attention,1.可以使用drop view viewname来删除视图，但是不能删除数据； 2.对视图的查询确实不如直接查询表快，并且在分布式应用用利用视图感觉确实不如同义词快，所以对单一表的查询不建议使用视图； 3.为了加快查询速度可以使用物化视图；speed 4.如果有权限permission，可以对简单视图进行insert,update,delete等操作，所以也要注意修改大数据量时对回滚空间的使用情况。 quantity 大量Replication 复制,1.3 函数dead lock,相关知识 函数的作用 系统函数 自定义函数 函数的应用 一类不常用的函数：分析函数 函数的优缺点 Scenario 情节 simultaneous,1.3.1 函数示意 feature calculate optional,1.3.2 列举常用的系统函数,1、字符函数 lower upper initcap concat substr instr lpad rpad trim length 2、日期函数 convert minus add_months months_between round trunc next_day last_day 3、数值函数 round trunc mod 4、转换函数 to_char to_date to_number 5、组函数：avg min max sum count otherwise 5、其他函数 nvl nvl2 nullif case decode coalesc,1.3.3 函数举例,1、工作30年后的第一个星期一 是哪天 select last_name, next_day(add_months(hire_date,360),星期一) a from employees; ? 2、哪些部门，工资高于 4000 的人数 超过3个？ select department_id from employees where salary 4000 group by department_id having count(*) 3 ; 3、列出那些员工的名字里只含有一个字母a select lastname from employees where instr(last_name,a)0 and instr(substr(last_name,instr(last_name)+1),a)=0,1.3.4 自定义函数,自定义函数示例 1、改进的trim函数：blank realize create or replace function f_trim(v_value in varchar2, v_trimstr in varchar2) return varchar2 is Result varchar2(1000); v_value_temp varchar2(1000); begin v_value_temp:=v_value; if substr(v_value_temp,1,1)=v_trimstr then v_value_temp:=substr(v_value_temp,2,length(v_value_temp)-1); end if; if substr(v_value_temp,length(v_value_temp),1)=v_trimstr then v_value_temp:=substr(v_value_temp,1,length(v_value_temp)-1); end if; Result:=v_value_temp; return(Result); end f_trim; 用法示例：select f_trim(,aaa,bbb,ccc,) from dual;,1.3.4 自定义函数,自定义函数示例 2、字符分解函数： 该函数作用如java中的split函数： select f_split(abcd,dddd,eee,ffff,) from dual 结果： 实现：逐个字符进行分解，放到自定义table类型变量里,1.3.4 自定义函数,自定义函数示例 2、检查有没有环路的函数： 实现：函数实现自我递归，对父节点和孩子节点进行循环判断。这两个自定义函数会详细讲解。,1.3.5 分析函数 efficient,1、cume_dist() 计算一行在组中的相对位置 2、dense_rank() 根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。 3、first_value(显示的表达式),last_value(显示的表达式) 返回组内第一个值，最后一个值。 4、rank() 和 dense_rank （）函数功能类似，但是有重复值时序号是跳号的。 5、row_number() 返回有序组中的一行的偏移量，也就是对应的序号。 注意问题：分析函数使用时需要考虑排序、筛选的复杂度，大批量数据的过于复杂排序、筛选会导致系统资源繁忙。,1.3.6 函数的优缺点,1、优点：convenient 方便查询，方便对数据的处理 2、缺点：exact 在大量数据上使用函数，在一定程序上会减低查询速度这个时候可以考虑为这些函数建立函数索引 在自定义函数内部不能进行数据库的insert、update、delete等操作。存储过程可以。,1.4 存储过程,相关知识 存储过程的作用和优点 存储过程使用举例 游标的使用 存储过程的调用和单步调试,1.4.1 存储过程的作用和优点,作用和优点概括如下： 1.可以实现复杂的逻辑控制，这个在sql语句里是不可以的 2.可以大大简化程序员对数据库的操作，对单项任务进行了逻辑上的集成；符合基本的模块化的设计理念：一个过程完成了一件事情； 3.提供了应用完整性和更严密的安全控制； 4.存储过程的调用使用的是共享内存机制，一个副本装入内存，可以被多个用户执行； 5.对它的使用简化了web程序，减轻了对数据库操作的各种负担 6.应用过程后更好的实现了应用系统多层结构上的松耦合,1.4.2 存储过程使用举例,1.添加历史工作记录的过程 add_job_history CREATE OR REPLACE PROCEDURE add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history; 注意其中的数据类型的定义。,1.4.2 存储过程使用举例,2.Pm系统中循环删除数据的存储过程举例 create or replace procedure deleteTab (p_TableName in varchar2, p_Condition in varchar2 default 1=1, p_Count in varchar2 default 10000 ) as pragma autonomous_transaction; n_delete number:=0; begin while 1=1 loop EXECUTE IMMEDIATE delete from |p_TableName| where |p_Condition| and rownum = :rn USING p_Count; if SQL%NOTFOUND then exit; else n_delete:=n_delete + SQL%ROWCOUNT; end if; commit; end loop; commit; end;,1.4.2 存储过程使用举例,2.Pm系统中循环删除数据的存储过程举例 注意问题： 1.为什么要循环删除？回滚段的问题。 2.注意该例子中对隐含变量的使用。,1.4.3 游标的使用,在存储过程中使用游标是非常经常的事，它的作用如同c语言中的指针或者java中的引用变量。 游标知识点汇总： 1.隐式游标： 如SQL%NOTFOUND、 SQL%ROWCOUNT等 2.显式游标（用户自定义游标） cursor c1 is select 3. 游标的for循环 这个时候不需要显式的打开游标，即无需使用open语句 4.游标变量 这是一个非常有用的知识点，尤其适用于动态sql语句的情况，可以说是对游标的高级用法。 type t_c_table is ref cursor; 5.带参数的游标（略，自己看）,1.4.4 存储过程的调用和单步调试,1.存储过程的调用 exec procedure_name(para1,para2,) 2.存储过程的单步调试是一个非常有用的技巧，其详细信息，见文档：oracle存储过程单步调试的方法.doc,1.5 任务,1.5.1 定时任务定义 定时任务如同unix系统中的crontab，在特定间隔时间内调用一个过程执行，达到自动处理数据的目的。 1.5.2 定时任务定的应用场合 在需要有规律执行的数据处理上都可以应用，如日数据的汇总，周数据的汇总，月数据的汇总等有规律的数据处理，都可以交给来完成。 1.5.3 定时任务举例 VARIABLE jobdxm number; begin dbms_job.submit(:jobdxm,P_STARTNEMESS;,sysdate,trunc(sysdate+1)+7/24); commit; end;,1.5 任务,1.6触发器,定义：触发器是一种适用于数据库自我更新机制的sql封装，尤其适用于实时系统，如计费系统，在控制理论的嵌入式系统中也尤其适用于快速响应系统，如双闭环控制系统等。 分类：行级触发器、语句级触发器 优点：实时性好 缺点：1.在大多数并不需要非常实时的系统中并不建议适用触发器，因为它存在不可控制性，对测试数据等照样触发，如果对触发器的使用设计不好，在应用中会比较麻烦。2.不适用于单行触发大量数据便变动的场合,1.6.1 触发器,触发器举例： 一个触发器调用存储过程的例子： CREATE OR REPLACE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id); END; 调用的这个过程，既是在上一节讲的示例过程。,1.6.2 包,包的定义：它是一个oracle的数据对象，但是它定义了自己的变量、类型、函数、过程等，是对一组逻辑相关的pl/sql数据对象的封装 包的组成：包头和包体 包机制：体现了封装和信息隐藏的思想 包的建立： 建立包头： create or replace package pmpackage is 建立包体： CREATE OR REPLACE PACKAGE BODY pmpackage AS 包的使用： 包名.类型名 包名.数据项名 包名.存储过程名,1.6.2 包,包的高级话题： 包的重载和包地内部定义 包的优点： 模块化 简化应用程序设计 信息隐藏 附加功能 提高性能,2. oracle数据库的闪回,问题：oracle中有没有类似Windows操作系统的垃圾箱，可以恢复删除的东西。 引子：在Oracle 9i之前，如果DBA不小心删除了一些数据，或者是Drop了一个表，想要找回这些数据是非常麻烦甚至非常繁琐的。如Delete的数据只能通过备份或者是Logmnr来找回，而Drop的表，只能是通过老大备份，通过时间点恢复来找回了。所以，如果是重要的数据，导致系统长时间的宕机，会大大的降低可用度。怎么办呢？,2.1理解一些Flashback的特性,相关知识： 闪回表 闪回删除 闪回数据库 闪回版本查询 闪回事物查询 闪回数据归档 闪回查询,2.1.1 闪回表,语法： Flashback table table_name to timestamp; Flashback table table_name to