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

本文链接：https://blog.csdn.net/Love_Legain/article/details/54311653

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, 'Prison 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;