视图和游标

--视图 
--视图是从表中抽离出的逻辑相关的数据集合
--view
--本章学习目标
--描述视图
--创建和修改视图的定义,删除视图
--从视图中查询数据
--通过视图插入,修改和删除数据

--视图是一张虚表
--视图建立在已有表的基础上,视图赖以建立的这些表称为基表
--向视图提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句

--视图向用户提供基表数据的另一种表现形式

--视图的作用
--控制数据访问
--简化查询

--如何创建视图
create view 视图名 as select 字段 from 表名 where 条件;

create view emp_view03
as
select employee_id id,first_name name,department_name
from employee e,departments d
where e.department_id = d.department_id
with read only

--修改视图
--使用create or replace view 子句修改视图

--屏蔽DML操作
--可以使用with read only 选择屏蔽对视图的DML操作
--任何DML操作都会返回一个oracle server 错误

update emp_view03 set department_name = 'ABC' where id = 4;

--简单视图和复杂视图
特性           简单视图               复杂视图
表的数量         一个                  多个
函数              没有                  有
分组              没有                  有
DML操作           可以                  有时可以

create or replace view emp_view04
as
select department_name,avg(salary) avg_sal
from employee e,departments d
where e.department_id = d.department_id
group by department_name;

select * from emp_view04;

视图中使用DML的规定
.可以在简单视图中执行DML操作
.当视图定义中包含以下元素之一不能使用delete
-组函数
-group by子句
-distinct 关键字
-rownum 伪列
在复杂视图中不能使用DML操作(不觉对)

update emp_view04
set avg_sal = 10000 where department_name = 'renli5';
--原因:对于虚拟的列的修改无法对应实际列的修改

--删除视图
drop view emp_view04;

--小结:
对于简单的多表连接视图,可以使用增删查,但是不会影响基表,
但是无法进行修改,会报异常

--练习:
1.查询员工表中salary前10的员工信息

select first_name,salary from
(select first_name,salary from
employee order by salary desc)
where rownum<=10;
说明:rownum"伪列" 数据本身并没有这样的列,是oracle数据库为每个数据表加上的列,
可以表示行号,默认情况下,rownum按主索引来排序,若没有主索引则自然排序
对于rownum只能使用<或者<=,用=,>,>=都将不能返回任何数据

2.查询员工表salary10-20的员工信息
select * from
(select rownum rn,temp.* from
(select first_name,salary from
employee e order by salary desc) temp)
where rn>10 and rn<20;

--测试
--1.使用表employee创建视图,其中包括姓名,员工号,部门号
--2.显示视图的结构
--3.查询视图中全部内容
--4.将视图中数据限定在部门号80范围以内
--5.将视图变成只读视图
select * from employee
drop view employee_view5
create view employee_view5 as select first_name,employee_id,department_id from
employee with read only;

select * from user_views where view_name='EMPLOYEE_VIEW5';

create view employee_view5 as select first_name,employee_id,department_id from
employee where department_id<1012 with read only;

select * from employee_view5;

--游标的使用
定义:在pl/sql程序中,对于处理多行记录的事务经常使用游标来实现
举例:打印出部门号为80号的员工的工资
概念,为了处理SQL语句,Oracle必须分配一片叫做上下文(context area)的区域来处理所必须的信息,
一个指向语句被分析以后的表示形式的指针以及查询活动集合

游标:是一个指向上下文的句柄或指针

显示游标
1.定义游标
2.打开游标
3.提取游标数据
4.关闭游标

declare
--声明一个记录类型
type emp_record is record(
v_sal employee.salary%type,
v_empid employee.employee_id%type,
v_hire_date employee.hire_date%type 
);
--声明一个记录类型的变量
v_emp_record emp_record;
--定义游标
cursor emp_sal_cursor is select salary,employee_id,hire_date from employee where department_id = 1012;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
  --打印出工资信息
  dbms_output.put_line('v_empid:'||v_emp_record.v_empid||'salary:'||v_emp_record.v_sal||'hire_date'||v_emp_record.v_hire_date);
fetch emp_sal_cursor into v_emp_record;
end loop;
--关闭游标
close emp_sal_cursor;
end;
set serveroutput on;
游标的属性
%found:布尔类型,当最后一次读记录时成功返回值为true
%notfound:布尔类型,与%found相反
%isopen:布尔类型,当游标已经打开时返回true
%rowcount:数字类型,返回已从游标读取的记录数

游标的for循环
pl/sql语言提供了游标的for循环语句,自动执行游标的open,fetch,close语句和循环语句的功能.

练习:
利用游标,调整公司中员工的工资
工资范围       调整基础
0-5000           5%
5000-10000       4%
10000-15000      3%
15000-           2%

declare
--定义游标
cursor emp_sal_cursor is select first_name,salary from employee;
v_temp number(4,2);
v_emp_name employee.first_name%type;
v_emp_sal employee.salary%type;
--开启游标
begin
open emp_sal_cursor;
--提取游标数据
fetch emp_sal_cursor into v_emp_name,v_emp_sal;
--写业务逻辑
while emp_sal_cursor%found loop
  if v_emp_sal<5000 then v_temp:=0.05;
  elsif v_emp_sal<10000 then v_temp:=0.04;
  elsif v_emp_sal<15000 then v_temp:=0.03;
  else v_temp:=0.02;
end if;
  dbms_output.put_line(v_emp_name||','||v_emp_sal);
  update employee 
  set salary = salary*(1+v_temp) 
  where first_name = v_emp_name; 
fetch emp_sal_cursor into v_emp_name,v_emp_sal;  
end loop;
end;

--小结:游标主要用于处理返回的多行记录(类似于java中的迭代器)
--游标主要是配合存储函数一起使用,原因是游标可以将获取的数据保存在指定的变量中,
--以供其他函数调用.
--dbms_output.put_line将必要的信息输出,以便对"存储函数"进行调试,只有将serveroutput变量
--设为on后,信息才能显示在屏幕上.