Oracle学习2

经典面试

删除一张表重复记录（ID 是自增唯一，重复记录：其他字段都是一样）
非常经典的一道面试题（可能存在很多数据，要求性能比较高）
1 louis 20
2 louis 20
3 jimmy 30
4 louis 20

delete from aa where id not in(select min(id) from aa group by name,age);

select a1.id from a a1,a a2 where a1.id>a2.id and a1.name =a2.name and a1.age=a2.age and a1.sex=a2.sex;

Oracle 的数据类型

Oracle 主要数据类型  number(4,2) 24.223
Char,nchar,varchar2,nvarchar2,number(),date,blob(binary 二进制流的大对象),clob（文件大对
象）
注意：
1、  由于 char 是以固定长度的，所以它的速度会比 varchar2 快得多!但程序处理起来要
麻烦一点，要用 trim 之类的函数把两边的空格去掉
2、  Varchar2 一般适用于英文和数字，Nvarchar2 适用中文和其他字符，其中 N 表示 Unicode
常量，可以解决多语言字符集之间的转换问题
3、  Number(4,2)  指的是整数占 2 位，小数占 2 位(99.994 可以。99.995 不行，因为是
四舍五入)
4、  Number 默认为 38 位

级联删除

在建立外键的时候必须指定级联删除（ON  DELETE  CASCADE）。
CREATE TABLE book
(
bid  NUMBER PRIMARY KEY NOT NULL ,
name  VARCHAR(50) ,
--  书应该属于一个人
pid  NUMBER ,
--  建立约束：book_pid_fk，与 person 中的 pid 为主-外键关系
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE
CASCADE
) ;

查看视图的创建语句

create View 视图名 （字段）AS 子查询

Select text from user_views;查看视图的创建语句

索引

select * from user_indexes 查询现有的索引

select * from user_ind_columns可获知索引建立在那些字段上

什么是索引

一种用于提升查询效率的数据库对象

通过快速定位数据的方法，减少磁盘I/O 操作

索引信息与表独立存放

Oracle数据库自动使用和维护索引

索引分类

唯一性索引 、非唯一性索引

创建索引的两种方式

自动创建-----在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引

手动创建===用户可以再其他列上创建非唯一的索引，以加速查询

create index abc on student(sid,sname);

create index abc1 on student(sname,sid);

索引的存储
索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同
一个表空间，这样可以避免产生 IO 冲突。使 Oracle 能够并行访问存放在不同硬盘中的索
引数据和表数据，更好的提高查询速度。

删除索引
drop index PK_DEPT1;

SQL 优化

删除一张表的重复记录（ID 是自增唯一主键，重复记录：其他字段都是一样）
(数据量很大,性能要求很高)
表名：T
Id name age
1 louis 20
2 louis 20
3 jimmy 30
4 louis 20
做法一：
Delete from t where id not in (Select min(id) from t Group by name,age);
做法二：
delete from t where id in(Select distinct a2.id from t a1,t a2 where
a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
做法三：
delete from t a1 where not exists(select *
from t a2
where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age
);
前提数据量>100，0000
以上三种做法，均可。但是第三种做法的性能最佳。第一种用 not in 没办法用到索引.第三
种方式也不会用到索引
数据量  1000      100000      100,0000
方法一  0.047      3.77          72
方法二  0.286        5.77         65
第二种方式快于第一种方式。
SQL 优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，执
行过程中访问尽量少的数据块，减少表扫描的 I/O 次数，尽量避免全表扫描和其他额外开
销。

尽量少用 IN 操作符
基本上所有的 IN 操作符都可以用 EXISTS 代替， 在选择 IN 或 EXIST 操作时，要根据主子
表数据量大小来具体考虑
17.2、尽量用 NOT EXISTS 或者外连接替代 NOT IN 操作符
因为 NOT IN 不能应用表的索引
17.3、尽量不用“<>”或者“!=”操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。比如：a<>0
改为  a>0 or a<0
17.4、在设计表时，把索引列设置为 NOT NULL
判断字段是否为空一般是不会应用索引的，因为 B 树索引是不索引空值的。
17.5、尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。比如用 T 表中
Column1  LIKE  „%5400%‟  这个条件会产生全表扫描，如果改成 Column1  ‟X5400%‟  OR
Column1 LIKE ‟B5400%‟  则会利用 Column1 的索引进行两个范围的查询，性能肯定大大提
高。
17.6、Where 子句中避免在索引列上使用计算
如果索引不是基于函数的，那么当在 Where 子句中对索引列使用函数时，索引不再起作用。
因此 Where 子句中避免在索引列上使用计算。
比如：
substr(no,1,4)=‟5400‟，优化处理：no like „5400%‟
trunc( hiredate)=trunc(sysdate) ， 优 化 处 理 ： hiredate  >=trunc(sysdate)  and  hiredate
<trunc(sysdate+1)
17.7、用“>=”替代“>”
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有
的情况下可以对它进行优化，如一个表有 100 万记录，一个数值型字段 A，  30 万记

录的 A=0，30 万记录的 A=1，39 万记录的 A=2，1 万记录的 A=3。那么执行 A>2 与
A>=3 的效果就有很大的区别了，因为  A>2 时 ORACLE 会先找出为 2 的记录索引再
进行比较，而 A>=3 时 ORACLE 则直接找到=3 的记录索引
17.8、利用 SGA 共享池，避开 parse 阶段
同一功能同一性能不同写法 SQL 的影响
如一个 SQL 在 A 程序员写的为
Select * from zl_yhjbqk
B 程序员写的为
Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C 程序员写的为
Select * from DLYX.ZLYHJBQK（大写表名）
D 程序员写的为
Select *   from DLYX.ZLYHJBQK（中间多了空格）
以上四个 SQL 在 ORACLE 分析整理之后产生的结果及执行的时间是一样的，但是从
ORACLE 共享内存 SGA 的原理，可以得出 ORACLE 对每个 SQL   都会对其进行一次分析，
并且占用共享内存，如果将 SQL 的字符串及格式写得完全相同则 ORACLE 只会分析一次，
共享内存也只会留下一次的分析结果，这不仅可以减少分析 SQL 的时间，而且可以减少共
享内存重复的信息，ORACLE 也可以准确统计 SQL 的执行频率。
不同区域出现的相同的 Sql 语句要保证查询字符完全相同， 建议经常使用变量来代替常量，
以尽量使用重复 sql 代码，以利用 SGA 共享池，避开 parse 阶段，防止相同的 Sql 语句被
多次分析，提高执行速度。
因此使用存储过程，是一种很有效的提高 share pool 共享率，跳过 parse 阶段，提高效率的
办法。
17.9、WHERE 后面的条件顺序要求
WHERE 后面的条件，表连接语句写在最前，可以过滤掉最大数量记录的条件居后。
比如：
Select * from zl_yhjbqk where dy_dj = '1KV 以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1   and dy_dj = '1KV 以下'
以上两个 SQL 中 dy_dj（电压等级）及 xh_bz（销户标志）两个字段都没进行索引，所
以执行的时候都是全表扫描，第一条 SQL 的 dy_dj = '1KV 以下'条件在记录集内比率为 99%，
而 xh_bz=1 的比率只为 0.5%，在进行第一条 SQL 的时候 99%条记录都进行 dy_dj 及 xh_bz

的比较，而在进行第二条 SQL 的时候 0.5%条记录都进行 dy_dj 及 xh_bz 的比较，以此可以
得出第二条 SQL 的 CPU 占用率明显比第一条低。
17.10、使用表的别名，并将之作为每列的前缀
当在 Sql 语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解
析时间
17.11、进行了显式或隐式的运算的字段不能进行索引
比如：
ss_df+20>50，优化处理：ss_df>30
„X‟||hbs_bh>‟X5400021452‟，优化处理：hbs_bh>‟5400021542‟
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
hbs_bh=5401002554，优化处理：hbs_bh=‟  5401002554‟，注：此条件对 hbs_bh  进行隐
式的 to_number 转换，因为 hbs_bh 字段是字符型。
17.12、用 UNION ALL 代替 UNION
UNION 是最常用的集操作，使多个记录集联结成为单个集，对返回的数据行有唯一性要求，
所以 oracle 就需要进行 SORT UNIQUE 操作（与使用 distinct 时操作类似） ，如果结果集又
比较大，则操作会比较慢；
UNION  ALL 操作不排除重复记录行，所以会快很多，如果数据本身重复行存在可能性较
小时，用 union all 会比用 union 效率高很多！
17.13、其他操作
尽量使用 packages：  Packages 在第一次调用时能将整个包 load 进内存，对提高性能有帮
助。
尽量使用 cached sequences  来生成 primary key  ：提高主键生成速度和使用性能。
很好地利用空间：如用 VARCHAR2  数据类型代替 CHAR 等
使用 Sql 优化工具：sqlexpert；toad；explain-table；PL/SQL；OEM
17.14、通过改变 oracle 的 SGA 的大小
SGA：数据库的系统全局区。
SGA 主要由三部分构成：共享池、数据缓冲区、日志缓冲区

1、 共享池又由两部分构成：共享 SQL 区和数据字典缓冲区。共享 SQL 区专门
存放用户 SQL 命令，oracle 使用最近最少使用等优先级算法来更新覆盖；数据字
典缓冲区（library cache）存放数据库运行的动态信息。数据库运行一段时间后，
DBA 需要查看这些内存区域的命中率以从数据库角度对数据库性能调优。通过执
行下述语句查看：
select (sum(pins - reloads)) / sum(pins) "Lib Cache"   from   v$librarycache;
--查看共享 SQL 区的重用率，最好在 90％以上，否则需要增加共享池的大小。
select  (sum(gets  -  getmisses  -  usage  -  fixED))  /  sum(gets)  "Row  Cache"   from
v$rowcache;
--查看数据字典缓冲区的命中率，最好在 90％以上，否则需要增加共享池的大小。
2、   数据缓冲区：存放 sql 运行结果抓取到的 data block；
SELECT  name,  value    FROM  v$sysstat    WHERE  name  IN  ('db  block  gets',
'consistent gets','physical reads');
--查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区
的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。命中率应该
在 90％以上，否则需要增加数据缓冲区的大小。
3、 日志缓冲区：存放数据库运行生成的日志。
select  name,value  from  v$sysstat  where  name  in  ('redo  entries','redo  log  space
requests');
--查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败
率：申请失败率＝requests/entries，申请失败率应该接近于 0，否则说明日志缓冲
区开设太小，需要增加 ORACLE 数据库的日志缓冲区。

序列

创建序列
Create sequence myseq
Start with 1
Increment by 1
Order
cache 20
Nocycle;

NextVal,CurrVal
Select myseq.nextval from dual;
Select myseq.currval from dual;
(必须先有 nextval，才能有 currval)
查询完之后就已经自增 1 了
Insert into table1 values(myseq.nextval)  这时候已经是 2 了

应用

create sequence myseq2 start with 1 increment by 1 cycle maxvalue 3
nocache ;
这样到 3 之后，要会重新从 1 开始

函数

函数就是一个有返回值的过程

定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪

create or replace function myfun(eno emp.empno%type) return number

as

rsal number;

begin

select (sal+nvl(comm,0))*12 into rsal from emp where empno=eno;

return rsal;

end;

直接写SQL语句，调用此函数

select myfun(7369) from dual;

存储过程

与过程相比，存储过程是存在数据库中的一个对象

现在定义一个简单的过程，就是打印一个数字

create or replace procedure myproc

as

i number;

begin

i:=100;

dbms_output.put_line('i='||i);

end;

执行过程  exec 过程名称

例子存储过程

写一个存储过程  要求  可以传入部门的编号、部门的名称，部门的位置，之后调用此过程就可以完成部门的增加操作

create  or replace procedure myproc (dno dept.deptno%type,name dept.dname%type,dl dept.loc%type)

as

cou number;

begin

--判断插入的部门编号是否存在，如果存在则不能插入

select count(deptno) into cou from dept where deptno=dno;

if cou=0 then

--可以增加新的部门

insert into dept(deptno,dname,loc) values(dno,name,dl);

dbms_output.put_line('部门插入成功！')；

else

dbms_output.put_line('部门已存在，无法插入')

end if;

end;

过程的参数类型

in：值传递，默认的

in out ：带值进，带值出

out：不带值进，带值出

存储过程

1、create or replace procedure 存储过程名

2、is

3、begin

4、null；

5、end；

行1： create or replace procedure是一个语句通知Oracle数据库去创建一个叫skeleton存储过程，如果存在就覆盖它。

行2：is关键词表示后面将跟随一个PL/SQL体。

行3：begin 关键词表明PL/SQL体的开始

行4：null PL/SQL语句表明什么事都不做，这句不能删除，因为PL/SQL体中至少需要一句

行5：END关键词表明PL/SQL 体的结束

存储过程创建语法： 
 create or replace procedure 存储过程名（param1 in type，param2 out type）  as  
变量1 类型（值范围）; --vs_msg   VARCHAR2(4000);  变量2 类型（值范围）; Begin 
Select count(*) into 变量1 from 表A where列名=param1；  
    If (判断条件) then 
       Select 列名 into 变量2 from 表A where列名=param1； 
       Dbms_output。Put_line(‘打印信息’);     Elsif (判断条件) then 
       Dbms_output。Put_line(‘打印信息’);     Else 
       Raise 异常名（NO_DATA_FOUND）;     End if; Exception 
    When others then        Rollback;

end；

 

begin  
while i < 10 LOOP  begin       i:= i + 1;  end;  end LOOP;   end test;  4 、数组  
首先明确一个概念：Oracle 中本是没有数组的概念的，数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。  
使用数组时，用户可以使用Oracle 已经定义好的数组类型，或可根据自己的需要定义数组类型。  
(1) 使用Oracle 自带的数组类型  x array; -- 使用时需要需要进行初始化  e.g:  

 

create or replace procedure test(y out array) is   x array;     begin  
x := new array();  y := x;  end test;  
(2) 自定义的数组类型 ( 自定义数据类型时，建议通过创建Package 的方式实现，以便于管理)  
create or replace package myPackage is     Public type declarations   type info is record(     name varchar(20),     y number);  
  type TestArray is table of info index by binary_integer;    
-- 此处声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table 而已，及TestArray 就是一张表，有两个字段，一个是name ，一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项，也可以不写，直接写成：type TestArray is  

 

table of info ，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();  
end TestArray;  
5. 游标的使用 Oracle 中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：  
(1)Cursor 型游标( 不能用于参数传递)  create or replace procedure test() is    
cusor_1 Cursor is select std_name from student where  ...;  --Cursor 的使用方式1   cursor_2 Cursor;  
begin  
select class_name into cursor_2 from class where ...;  --Cursor 的使用方式2  
可使用For x in cursor LOOP .... end LOOP; 来实现对Cursor 的遍历  
end test;  

 

 

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A ，就在总成绩上加20 分。  
create or replace procedure autocomputer(step in number) is  
rsCursor SYS_REFCURSOR;  commentArray myPackage.myArray;  math number;  article number;  language number;  music number;  sport number;  total number;  average number;  stdId varchar(30);  
record myPackage.stdInfo;  

 

i number;  begin  i := 1;  
get_comment(commentArray); -- 调用名为get_comment() 的存储过程获取学生课外评分信息  
OPEN rsCursor for select 
stdId,math,article,language,music,sport from student t where t.step = step;  
LOOP  
fetch rsCursor into 
stdId,math,article,language,music,sport; exit when rsCursor%NOTFOUND;  
total := math + article + language + music + sport;  for i in 1..commentArray.count LOOP    record := commentArray(i);      if stdId = record.stdId then     begin       

 

 if record.comment = &apos;A&apos; then         begin            total := total + 20;     
   go to next; -- 使用go to 跳出for 循环           end;      end if;    end;    end if;  end LOOP;  
<<continue>>  average := total / 5;  
 update student t set t.total=total and t.average = average where t.stdId = stdId;  
end LOOP;  end;  
end autocomputer;  
-- 取得学生评论信息的存储过程  

 

create or replace procedure 
get_comment(commentArray out myPackage.myArray) is  
rs SYS_REFCURSOR ；  record myPackage.stdInfo;  stdId varchar(30);  comment varchar(1);  i number;  begin  
open rs for select stdId,comment from out_school  i := 1;  LOOP  
 fetch rs into stdId,comment; exit when rs%NOTFOUND;  
record.stdId := stdId;  
 record.comment := comment;  recommentArray(i) := record;  

 

 

i:=i + 1;  end LOOP;  end get_comment;  -- 定义数组类型myArray  
create or replace package myPackage is begin  type stdInfo is record(stdId varchar(30),comment varchar(1));  
type myArray is table of stdInfo index by binary_integer;  end myPackage;