Oracle 关于 视图、索引及存储过程的实例操作

1.添加视图

--视图：虚拟表、可以作为表一样查询使用、预定义的查询
create [or replace] [[no] force] view 视图名
as
       select查询
[with read only];
--ex:
create or replace view empinfo
as
  select e.*, d.dname, d.loc
  from emp e
  left join dept d
       on e.deptno = d.deptno
with read only;

select *
from empinfo
where sal > 2000;

drop view empinfo;

2.添加索引

--索引：加速查询
create [unique] index 索引名字 on 表名(列);
--ex:
create index emp_index_id on emp(ename);

drop index emp_index_id;

3.有关分支的存储过程

create or replace procedure sp_sal(name in varchar)
-- 创建一个存储过程
is
       i number;
       --创建一个变量
begin
       select e.sal into i
       from emp e
       where e.ename = name;
       --查询语句，查询结果赋予给 i
       if i > 2000 then
              -- 条件成立
              update emp set sal = sal + 0.01  where ename = name;
       else
              --条件不成立
              update emp set sal = sal + 0.1  where ename = name;
       end if;
       --提交
       commit;
end sp_sal;

4.有关循环的存储过程

create or replace procedure sp_for1(num in number)
is
begin
       for i in 1..num loop
           insert into dept(deptno,dname,loc) values (to_char(50+i),concat('OREEE',to_char(i)),concat('OOOO',to_char(i)));
       end loop;
       commit;
end sp_for1;