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;