Oracle 数据库对象
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中执行,过程不能。
可以将比较复杂的查询写成函数,然后在存储过程中调用这些函数。
评论暂时关闭