oracle数据库对象第一部分(表，视图，索引，函数，序列，存储过程)

oracle数据库对象第一部分(表，视图，索引，函数，序列，存储过程)简析

---

part_1:多表关联查询演示;

--求每个部门的平均工资和员工人数
select d.dname "部门",
 round(avg(e.sal + nvl(e.comm, 0)), 2) "平均工资",
count(*) "人数"
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

---

part_2:数据库对象—视图(View)：

--创建一个求每个部门平均工资和人数的视图（sys用户创建,因为scott权限不足）;
create view avgsal
as
select d.dname "部门",
 round(avg(e.sal + nvl(e.comm, 0)), 2) "平均工资",
count(*) "人数"
from scott.emp e, scott.dept d
where e.deptno = d.deptno
group by d.dname;

---

part_3:上面是用sys用户创建的视图，因为scott用户没有权限；下面的语句可以授予scott用户创建视图的权限：

SQL> grant create view to scott;--可以由sys用户完成授权

---

part_4:

--创建一个求每个部门平均工资和人数的视图（scott用户创建,在已授权创建视图的前提下）;
create view avgsal as
select d.dname "部门",
 round(avg(e.sal + nvl(e.comm, 0)), 2) "平均工资",
count(*) "人数"
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;

---

part_5:

--使用上面已创建的视图
select * from avgsal;

---

part_6:查看所有视图:

--查看当前用户(scott)下的所有视图
select * from user_views;

scott用户的所有视图：

sys用户的所有视图：

---

part_7:数据库对象—序列(Sequence):

--创建一个图书表
create table BOOK(
 bookID number(12) constraint PK_BOOK primary key,
 bookName varchar2(32) not null,
 price number(5,2),
 publishedTime date
);


--为上面的图书表字段bookID创建序列
create sequence bookIDSeq
start with 1
increment by 1
maxvalue 999999999999
nocache
nocycle

--向表中插入数据
insert into book values (bookIDSeq.Nextval, 'Game of Thrones', null, sysdate);
insert into book values (bookIDSeq.Nextval, '* Break', null, sysdate);
insert into book values (bookIDSeq.Nextval, 'West World', null, sysdate);

--通过虚表dual查看当前值,下个值
select bookIDSeq.Currval from dual;
select bookIDSeq.Nextval from dual;

--用Oracle11g创建序列后插入数据的初始值老是从2开始,参见
--http://www.cnblogs.com/fanjie167/p/5938102.html

---

part_8:数据库对象—函数(Functions)

  --创建函数
create or replace function getempsal(p_empno in number) return number is
 p_empsal number(7, 2);

begin
select sal into p_empsal from emp where empno = p_empno;
  return(p_empsal);
end;
--使用函数
select getempsal(7369) from dual;

---

part_9:数据库对象—存储过程(Stored Procedure)

  --创建存储过程
--根据empno删除对应的emp
create or replace procedure removeEmp(p_empno in number) is
begin
delete from emp where emp.empno = p_empno;
commit;
end removeEmp;

--先插入一行数据,用于测试上面创建的存储过程
insert into emp values(1, 'MIKE', 'CLERK', 7902, sysdate, 3500, null, 20);

--查看是否创建成功
select * from emp order by empno;
--执行删除操作(使用上面创建的存储过程)
  execute removeEmp(1); --在控制台执行,PL/SQL无法运行;
--查看是否删除成功
select * from emp order by empno;

---

附：
I.Oracle视图详解
http://blog.itpub.net/29785807/viewspace-1270120/
II.Oracle索引详解
https://www.oschina.net/question/30362_4057
http://www.cnblogs.com/djcsch2001/articles/1823459.html
III.Oracle函数详解
http://blog.csdn.net/sgzy001/article/details/17039921
III.Oracle序列详解
http://www.cnblogs.com/kerrycode/archive/2013/03/18/2965747.html
http://blog.csdn.net/java958199586/article/details/7360152
IV.Oracle存储过程
http://www.cnblogs.com/chuncn/archive/2009/01/29/1381291.html
http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html
http://www.jb51.net/article/31805.htm

20170110-12:29