Oracle 数据库对象


Oracle数据库有很多对象,比如表、视图、索引、函数、触发器、存储过程等。
 
数据库最重要的对象——表
 
如果表的数据量很大,可以考虑Oracle的分区表,这样可以改善性能,均衡IO。
 
数据库对象——视图
 
create view avgsal

as
 
select d.dname, count(*) ,round(avg(e.sal),0)from emp e,dept d where e.deptno=d.deptno group by d.dname;
 
查询当前用户下的视图
 
select * from user_views;
 
数据库对象——序列
 
序列(Sequence)是数据库对象之一,利用它可以生成唯一的整数。一个序列的值是有Oracle数据库自动生成的,序列可以定义自动递增或者递减。
 
create sequence testid start with 1increment by 1 maxvalue 9999999 nocache nocycle;
 
select testid.nextval from dual;
 
select testid.currval from dual;
 
 
数据库对象——函数
 
函数(Function)分为两种,一种是Oracle数据库自身的函数,一种是用户自己编写的函数。
 
例:根据员工ID获取员工薪水
 
create or replace function get_empsal(emp_noin number)
 
return number is emp_sal number(7,2);
 
begin
 
        selectsal into emp_sal from emp where empno=emp_no;
 
        return(emp_sal);
 
end;
 
 
 
执行函数
 
select get_empsal(7369) from dual;
 
 
 
数据库对象——存储过程
 
存储过程(Stored Procedure)是一组为了完成特定功能而建立的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果有参数)来执行它。
 
 
 
例:根据员工ID删除员工信息
 
create or replace procedure DelEmp(empid innumber) is

begin
 
        deletefrom emp where emp.empno=empid;
 
        commit;
 
end DelEmp;
 
执行存储过程
 
execute DelEmp(7369);
 
 
 
数据库对象——索引
 
索引(Index)是对数据库表中一列或多列的值进行排序的一种结构。索引可以加快数据库的查询速度。
 
 
 
例:
 
创建一个表,并批量插入数据,比较有索引和没有索引的查询速度。
 
create table test_index(id number,namevarchar2(200));
 
创建一个存储过程,循环的往test_index表中插入数据。
 
create or replace procedure insert_data is
 
test_name varchar2(20):='Dragon';
 
begin
 
        fori in 1 .. 10000000 loop
 
                  insertinto test_index(id,name) values (i,test_name);
 
        endloop;
 
        commit;
 
end;
 
 
 
执行存储过程(时间较长)
 
execute insert_data;
 
 
 
验证1000万条数据是否插入test_index表中。
 
select count(*) from test_index;
 
 
 
考察在没有索引的情况下查询ID为100的数据所需的时间。
 
使用PL/SQL Developer查看耗时
 
select * from test_index where id=100;
 
因为test_index表中没有ID列的索引,所以查询100号的数据时需要进行全表扫描,会需要很长时间。
 
全表扫描 此处就是计算机在查询数据时,从1到1000万行数据全部扫描一次。
 
 
 
创建表的索引(耗时较长)
 
create index test_index_id on test_index(id);
 
 
 
考察在使用索引的情况下查询ID为100的数据所需的时间
 
select * from test_index where id=100;
 
 
 
可以明显看出来使用索引后大大减少了查询时间。
 
 
 
数据库对象——同义词
 
同义词(Synonym)是现有对象的一个别名。分为私有同义词和公共同义词。如一个表名过长或者访问其他用户的表需要加上用户名作为前缀,可以通过别名来解决。
 
确认当前用户是否具有创建同义词的权限,如果没有则授权。
 
select * from session_privs where privilegelike '%SYNONYM%';
 
 
 
对test_index表创建同义词
 
create synonym test for test_index;
 
 
 
使用同义词进行查询
 
select count(*) from test;
 
 
 
公共同义词,其他用户通过公共同义词访问scott用户的test_index表。
 
首先使用sys账户新建一个用户
 
create user dragon identified by oracle;
 
grant connect to dragon;
 
grant select on scott.test_index to dragon;
 
 
 
select count(*) from scott.test_index;
 
dragon用户在查询scott用户的test_index表时需要加上前缀‘scott.’
 
而通过公共同义词,则不需要前缀。
 
create public synonym test_public fortest_index;
 
切换至dragon用户测试
 
select count(*) from test_public;
 
 
函数和存储过程的区别
 
1、函数必须有返回值,过程没有返回值
 
2、函数可以单独执行,过程必须通过execute执行
 
3、函数可以嵌入SQL中执行,过程不能。
 
可以将比较复杂的查询写成函数,然后在存储过程中调用这些函数。

相关内容