Oracle 讲义.doc

Oracle数据库简介1. 介绍课程特点和目标2. 相关背景知识回顾3. 介绍oracle数据库的优点，并比较与db2,Sybase ,sql server数据库的优势4. 介绍oracle数据库的历史和版本特点5. 介绍安装和使用的基本技巧Oracle 体系结构体系结构可以分为三个层面来理解：1. 逻辑结构粒度从细到粗可以分为如下结构：块，区，段，表空间，数据库，可以用图表述如下：块区段表空间数据文件数据库段可以跨数据文件存储2. 物理结构可以分为数据文件，日志文件，控制文件。此外参数文件虽然不属于数据库物理文件，但是在启动过程中也起到重要作用。3. 内存结构需要理解数据库和数据库实例的关系。主要的内存结构是SGA,可以分为如下：A. DBBuffer保持buffer,如代码表再生buffer，使用后就释放的内存，如频繁访问的大表B. 共享池1. 库缓冲区i.共享sql区 保持访问过的sql命令的解释版本ii.pl/sql区保持pl/sql过程，函数的编译版本2. 字典缓冲区保存数据库字典表的相关内容C. 大共享池可选的内存区，提供大内存块的隔离内存区D. 固定sqa区不能手工控制，完全由oracle管理E. Redobuffer重做日志缓冲区，管理用于回滚或者重做的内存块Sql plus介绍介绍sql plus的概念和使用方法登陆的命令格式Dos命令提示符下输入 sqlplus scott/tiger如果希望以管理员sys身份登陆必须使用 sqlplus sys/manager as sysdba关闭和启动oracle实例的命令Shutdown,shutdown transactional,shutdown immediate,shutdown abort,区别如图所示：允许新连接等待回话结束等待事务结束做检查点并关闭数据库文件启动需要实例恢复ShutdownnoyesyesYes yesnoShutdown transactionalnonoyesYes yesnoShutdown immediatenononoYes yesnoShutdown abortnononoNo noyes启动命令startupStartup mount,startup nomount ,startup启动次序和关闭相反，先要启动数据库实例，然后装配控制文件，最后打开数据和日志文件。Startup nomount ,只启动实例，不装配控制文件Startup mount ,启动数据库实例，装配控制文件Startup,启动数据实例，装配打开所有数据库文件。运行脚本文件可以用加上物理文件路径。Help index可以列出所有上下文帮助主题Set命令可以设置相关环境变量值，常用的有Set pagesize 100Set linesize 100Set echo on/off? set可以列出所有可以设置的环境参数和使用方法。常用的编辑sql的命令：Append ,change/old/newClear buffer,del ,input,list ,run or/Edi将缓冲区命令保持至文件命令save将查询结果保存至文件命令spool 格式化报表的命令:Column sal format $99,990 heading 工资Ttitile center 我的标题 skip 1 left 测试报表 right page: format 999 sql.pnoBreak on+compute命令组合可以产生小计统计的效果Break on deptno skip 1 on job skip 2Compute on sum of sal jobSelect * from emp order by deptno,job;Compute可以列出当前compute命令的定义Clear compute可以清除该定义可以在报表结束处现实统计结果Break on reportCompute sum of sal on reportSelect * from emp;Sql(一)分类1. DdlCreate ,alter ,drop ,truncateCreate user kp identified by kp account unlock;Alter user kp identified by pk;Drop user kp;Truncate table emp;Create table abc(a varchar2(10),b char(10);Alter table abc modify(a varchar2(20);Alter table abc add c number;Alter table abc drop column c；Alter table abc add constraint c1 check(c between 1 and 10);alter table abc add constraint c2 primary key (a);alter table abc add constraint c3 primary key(a);alter table abc add constraint c3 foreign key(b) references a(x);(create table a (x char(10);alter table a add constraint c1 primary key(x)2. DmlSelect ,update,delete,insert3. DclGrant,revoke,rollback,commit,savepointGrant resource to kp;Grant select on emp to kp;Revoke select on emp from kp;上机的正常操作顺序应该是先创建表空间，然后创建用户，然后创建相关db objectCreate temporary tablespace kp_temp tempfile size 50m autoextend on next 50m maxsize 20480m;Create tablespace kp_data datafile sie 50m autoextend on next 50m maxsize 20480m;Create user kp identified by kp default tablespace kp_data temporary tablespace kp_temp;Grant connect ,resource to kp;Sql(二)系统函数1. 字符Length,lengthb,ltrim,rtrim ,trim,replace,substr2. 日期函数Sysdate,current_date,next_dayCurrent_date,sysdate区别a. 前者是当前会话时间，后者是服务器时间b. 有时前者比后者块1秒，四舍五入结果c. 修改时区，将中国从东八区改为东九区，则前者比后者快一个小时Alter session set nls_data_format=yyyy-mm-dd hh:mi:ss;Alter session set nls_data_format=yyyy-mm-dd hh24:mi:ss;Alter session set time_zone=+09:00可以用命令 select * from v$nls_parameters;查询当前nls_date_languageAlter session set nls_date_language=AMERICAN;(simplified Chinese)可以通过 select username,sid ,serial# from v$session;Alter system kill session sid,serial#杀死相关会话Next_day根据相关参数给出对应周几的具体日期3. 类型转换To_char,to_date,to_number4. 聚集函数Sum,avg,max ,min,count5. 其他User,decode,nvl,nvl2Select sum(decode(sex,男,1,0),sum(decode(sex,女,1,0) from emp;问题：统计10,20部门人数？Select sum(decode(deptno,10,1,0),sum(decode(deptno,20,1,0) from emp;工资分为三等，一千以内，一千至两千，两千以上，分别统计各部门各等人数Select deptno,sum(decode(greatest(sal,0),least(sal,1000),1,0)，sum(decode(greatest(sal,1000),least(sal,2000),1,0),sum(decode(greatest(sal,2000),sal,1,0) from emp;也可以用case命令来实现：select x,count(*) as cfrom ( select eno, (case when sal between 0 and 999 then 一等 when sal between 1000 and 1999 then 二等 else 三等 end) x from emp) group by x 或者Select count(case when sal2000 then 1 else null end) 三等from emp;也可以写成：Select sum(case when sal2000 then 1 else 0 end) 三等from emp;要注意其异同。6. 分组7. 模糊查询记住通配符_和%8. 表连接分为三种，内连接（自然连接），外连接（左，右，全），自连接A内连接Select emp.empno,dept.loc form emp inner join dept on emp.deptno=dept.deptno OrSelect emp.empno,dept.loc from emp natural join on emp.deptno=dept.deptno;B外连接Select emp.empno,dept.deptno from emp left outer join dept on emp.deptno=dept.deptnoOr select emp.empno,dept.deptno from emp where emp.deptno=dept.deptno(+) (左连接)Select emp.empno,dept.deptno from emp right outer join dept on emp.deptno=dept.deptnoOr select emp.empno,dept.deptno from emp where emp.deptno(+)=dept.deptno (右连接)Select emp.empno,dept.deptno from emp full outer join dept on emp.deptno=detp.deptno(全外连接)自连接考虑查询员工和其领导的名字Select x.ename ,y.ename from emp x,emp y where x.mgr=y.empno9. 子查询10. 代数运算Intersect,union,minusSql(三)伪列rownum,rowid的使用什么是伪列RowID？1.首先是一种数据类型，唯一标识一条记录物理位置的一个id，基于64位编码的18个字符显示。2.未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。RowID的用途1.在开发中使用频率应该是挺多的，特别在一些update语句中使用更加频繁2.能以做快的方式访问表中的一行3.能显示表的行是如何存储的4.作为表中唯一标识删除重复数据比较高效Delete empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno);什么是rownum对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。注意select rownum ,id,name from student order by name;和select rownum ,id,name from (select * from student order by name);的区别对emp表，显示工资排名第三高的职工信息select empno,sal from(select rownum as rn,empno,sal from (select * from emp order by sal desc ) )where rn=3或者select * from (select emp.*,dense_rank() over (order by sal desc) rank from emp) where rank=3;层次查询中的伪列levelSelect level, employee_id,manager_id,first_name|last_name from more_employees start with Employee_id=1 connect by prior employee_id=manager_id;可以通过Select count(distinct level) from more_employees start with employee_id=1 connect by priorEmployee_id=manager_id 获得树的层次。格式化查询结果Set pagesize 999 Column employee format a25Select level,lpad( ,2*level-1)|first_name| |last_name as employee from more_employees start with employee_id=1 connect by prior employee_id=manager_id;Sql(四)小计统计函数rollup,cubeSelect deptno,job ,sum(sal) from emp group by rollup(deptno,job);如果希望对以上结果格式化输出，如何更改？将rollup改为cube，观察结果不同点使用评价函数rank,dense_rank考虑问题如下：找到各部门工资前三高的员工信息Select * from (select rank() over (partition by deptno order by sal desc) rk ,emp.* from emp) T where t.rk=2;select distinct x.s# from sc x,sc y where x.s#=y.s# and x.c#!=y.c#d.查询全部学生都选修的课程号与课程名select c#,cname from cwhere c# in (select c# from scgroup by c#having count(*)=(select count(*) from s);select c# ,cname from c where not exists (select * from s where not exists(select * from sc where s#=s.s# and c#=c.c#)e.查询选修了王五老师所教授所有课程的学生学号select distinct s# from sc x where not exists(select * from c where techar=wang and not exists(select * from sc y where y.s#=x.s# and y.c#=c.c#)2.关系模式如下markext(mno,mname,city),item(ino,iname,type,color),sales(mno,ino,price)a.列出北京每个商场都有售，且售价都超过10000元的商品号和商品名select ino,iname from item where not exists(select * from markedt where city=bj and not exists (select * from sales where price10000 and markext.mno=sales.mno and sales.ino=item.ino)b.列出在不同商场中最高售价和最低售价之差超过100的商品号，最高售价和最低售价select ino ,max(price),min(price) from sales group by ino having max(price)-min(price)100select deptno from dept where not exits(select * from emp where dept.deptno=emp.deptno)Sql(六)索引和优化索引可以分为B树索引，位图索引，基于函数的索引，分别对应不同的应用需求索引的创建语法： CREATE UNIUQE | BITMAP INDEX .ON .( | ASC | DESC, | ASC | DESC,.)TABLESPACE STORAGE LOGGING | NOLOGGINGCOMPUTE STATISTICSNOCOMPRESS | COMPRESSNOSORT | REVERSEPARTITION | GLOBAL PARTITION相关说明1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。2） | ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)4）STORAGE：可进一步设置表空间的存储参数5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)6）COMPUTE STATISTICS：创建新索引时收集统计信息7）NOCOMPRESS | COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区索引特点： 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能索引不足：第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 应该建索引列的特点：1）在经常需要搜索的列上，可以加快搜索的速度； 2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。不应该建索引列的特点：第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 Bitmapt 特点：适合与决策支持系统；做UPDATE代价非常高；非常适合OR操作符的查询；基数比较少的时候才能建位图索引；技巧：对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。Oracle查询优化的三种策略1. rule 2.cost 3.hintPL/sql简介概念介绍和主要技术特点主要结构如下： DECLARE declaration BEGIN execution body EXCEPTION exception handler END；条件判断Declare X number:=3;Y varchar2(20);BeginIf x=1 then Y:=a;Elsif x=2 then Y:=b;Else Y:=c;End if;Dbms_output.put_line(y);End;也可以改写为caseDeclare X number:=3;Y varchar2(20);BeginCase when x=1 then Y:=a;when x=2 then Y:=b;Else Y:=c;End case;Dbms_output.put_line(y);End;循环分为三种Loop End loo;While loop End loop ; For . LoopEnd loop完整例子如下：Insert a new record into table emp with empno 8000,ename Wucheng and the other fieds is the same as employee Smith,finally ,increase the salary of all the employees with $500Pls user pl/sql to realize the above requirement。 DECLARE myrecord emp%ROWTYPE; myempno emp.empno%type:=8000; myname emp.ename%TYPE; addsal CONSTANT number(4):=500; BEGIN SELECT * INTO myrecord FROM emp WHERE ENAME=SMITH; mynane :=WUCHEN; INSERT INTO emp ( EMPNO,ENAME,SAL,COMM,JOB,HIREDATE,DEPTNO) VALUES(myempno,myname,myrecord.sal, m,myrecord.job, myrecord.hiredate, myrecord.deptno); UPDATE emp SET sal=sal+addsal; COMMIT;END; 游标及游标变量的使用游标可分为显示和隐式两种显示游标需要显示的定义，打开，取值，关闭，示例如下：DE CLAREv_emno emp.deptno%type:=10;v_ename emp.ename%type;v_sal emp.sal%type;n number(3);CURSOR c1 is select ename,sal from empwhere deptno=v_emno;BEGIN select count(*) into n from emp where deptno=v_emno; open c1; for i in 1.n loop fetch c1 into v_ename, v_sal; dbms_output.put_line(v_ename | to_char (v_sal); end loop; close c1； END;用隐式游标实现如下：Begin For rec in （select ename ,sal from emp） loopDbms_output.put_line(rec.ename| |rec.sal);End loop;End;游标变量则更为灵活，只需在使用时再指向具体数据源declare （强类型）弱类型就是无返回值type cur_ref1 is ref cursor return emp%rowtype;c1 cur_ref1;x emp%rowtype;begin open c1 for select * from emp where empno=1000;fetch c1 into x;dbms_output.put_line(x.ename);close c1;open c1 for select * from emp where deptno=30;loopfetch c1 into x;exit when c1%notfound;dbms_output.put_line(x.ename);end loop;close c1;end;过程，函数declarez number;procedure add(x number,y number) isbeginz:=x+y;end;beginadd(3,5);dbms_output.put_line(z);end;declarez number;function add(x number,y number) return number is-total number;beginreturn x+y;end;begin-add(3,5);dbms_output.put_line(add(3,5);end;/存储过程，函数create or replace function add1(x number,y number) return number is beginreturen x+y;end;create or replace procedure add2（x number,y number ,z out number) isbeginz:=x+y;end;触发器触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。功能：1、允许/限制对表的修改2、自动生成派生列，比如自增字段3、强制数据一致性4、提供审计和日志记录5、防止无效的事务处理6、启用复杂的业务逻辑开始create trigger biufer_employees_department_idbefore insert or updateon employeesreferencing old as old_value new as new_valuefor each rowwhen (new_value.department_id80 )begin:new_mission_pct :=0;end;1、语句触发器是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与insert、update、delete或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。2、行触发器是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：a、定义语句中包含for each row子句b、在for each row触发器中，用户可以引用受到影响的行值。同一表上可以定义多个触发器，最多可以创建12种类型的触发器，但是其触发是有固定顺序的，其先后顺序为： 1.before语句级触发器 2.before行级触发器 3.after行级触发器 4.after语句级触发器3、替代触发器create or replace view v_emp_dept as select empno, ename , hiredate, sal,deptno, dname from emp,dept where emp.deptno=dept.deptno尝试往v_emp_dept表插入数据时只能通过替代触发器来完成create or replace trigger tr_vinstead of insert on v_emp_deptfor each rowbegininsert into deptment values(:new.deptno,:new.dname);insert into emp (empno,ename,hiredate,sal) values(:new.empno,:new.ename,:new.hiredate,:new.sal);end;4、系统事件触发器系统事件：数据库启动、关闭，服务器错误create trigger ad_startupafter startupon databasebegin- do some stuffend;5、用户事件触发器用户事件：用户登陆、注销，create / alter / drop / analyze / audit / grant / revoke / rename / truncate / logoffcreate or replace trigger log_drop_triggerbefore drop on donny.schemabegininsert into droped_objects values(ora_dict_obj_name, - 与触发器相关的函数ora_dict_obj_type,sysdate);end;/Pl/sql习题给定关系如下：Professor(prof_name,gendar,sal,department)要求如下：1. 部门名称限制在（math,hist,engl,scie）2.gendar只能是male或female3.薪水上限为300004.Department转换为大写格式5.Salary四舍五入6.对于任何insert，如果教授的薪水超过10000且部门是engl，则拒绝（注意，所有的工资都必须以30000为上限）7. 如果当前预算超过55000就不能增加教授总的预算不能超过600001.alter table Professor add constraint c1 check(department in(,);2.alter table Professor add constraint c1 check(gendar(,);3.alter table Professor add constraint c1 check(sal10000 and department=engl)BeginRaise _application_error();End;从键盘读入数据：declare x number;y varchar2(1