Oracle语法（三：视图、索引、PL/SQL）

悠悠 2023-05-28 05:20 24阅读 0赞

#### 1. 视图 ####

**概念：**

1.  视图本身保存的是一条查询sql，创建视图之后这个sql就可以重复使用;
2.  视图可以看成是一张虚拟表，视图本身并不保存数据，数据是保存在表中；
3.  同一张表可以有多个视图（其实就是select语句查询的字段或者where条件不一样,最后展现的结果不一样）；
4.  可以屏蔽掉一些敏感数据（例如某个表中的字段不想让使用视图的人看到）。

**创建视图的语法：**

<table> 
 <thead> 
  <tr> 
   <th>create or replace view 视图名</th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>as 查询语句</td> 
   <td>查询语句</td> 
  </tr> 
  <tr> 
   <td>with read only</td> 
   <td>只读视图，只能查询不能修改数据</td> 
  </tr> 
 </tbody> 
</table>

> 注：创建视图需要权限  
>

-- 创建视图需要权限
    grant dba to scott;
    
    -- 复杂的查询操作，以视图的方式保存下来
    select e.ename 员工名, d.dname 部门名, s.grade 工资等级, m.ename 上级名字
     from emp e, dept d, salgrade s, emp m 
     where e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empno
     order by m.ename;
     
     -- 创建一个视图：新建或替换
     create or replace view view_iandboss as 
     select e.ename 员工名, d.dname 部门名, s.grade 工资等级, m.ename 上级名字
     from emp e, dept d, salgrade s, emp m  where e.deptno = d.deptno and e.sal between s.losal and s.hisal and e.mgr = m.empno
     order by m.ename;
     
     -- 使用视图，就可以当成表来直接使用
     select * from view_iandboss;
     
      select * from view_iandboss where 工资等级=3;

**案例效果：**  
![在这里插入图片描述][20200327193330891.png]

> 注：用户创建视图之后，其他用户是使用不了该视图的（用户没给其他用户授权的情况下）  
>

1)	创建用户test，并且给用户授权connect和resource权限
    create user test identified by orcl;
    grant connect,resource to test;
    2)	使用test/orcl登录，能否查询view_emp这个视图
    select * from view_emp;  -- 不能查询
    
    3)	使用scott登录，给test分配视图view_emp的查询权限
    grant select on view_emp to test;
    
    4)	使用test/orcl登录，查询scott用户下的view_emp
    select * from scott.view_emp;  -- 要指定用户名
    5)	使用test更新其中一条数据能否更新成功
    update scott.view_emp set ename='Rose' where empno=8080;  -- 没有权限

#### 2. 索引 ####

> 至于索引的概念在这里就不重复讲了，这里只写一下索引创建的语法.

**单列索引的创建语法：**

<table> 
 <thead> 
  <tr> 
   <th>create index 索引名 on 表名(列名)</th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

**复合索引的创建语法：**

<table> 
 <thead> 
  <tr> 
   <th>create index 索引名 on 表名(列1,列2)</th> 
  </tr> 
 </thead> 
 <tbody></tbody> 
</table>

#### 3. PL/SQL ####

PL/SQL：Proceduce Language 过程语言 SQL 结构化查询语言；  
这是Oracle在SQL的基础上，开发的针对Oracle的编程语言，面向过程的语言，不是面向对象的。对SQL语言增强。

[declare
        声明变量或游标
    ]
    begin 
        编程代码;
    end;

**3.1 四种变量类型语法：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1Jvb2tpZXhpYW9NdV9h_size_16_color_FFFFFF_t_70]  
**3.2 游标：**  
游标的概念：

1.  引用型变量：用来保存单行单列的数据
2.  记录型变量：用来保存一行数据
3.  游标：用来保存多行数据，类似于一张虚拟表

**3.2.1 游标的定义：**

cursor 游标名 is SQL语句

**3.2.2 打开并使用游标**

open 游标名;
       loop
          fetch 游标名 into 记录型变量
          exit when 游标名%notfound -- 满足条件退出
          游标处理的代码;
       end loop;
    close 游标名；

案例：定义游标：查找20号部门所有员工,将其姓名,薪水,职务打印出来

declare
    	-- 声明游标查询部门是20的员工
    	cursor cur_emp is
    		select * from emp where deptno = 20;
    	-- 声明记录类型的变量，用来保存游标中每条记录
    	v_emp emp%rowtype;
    begin
    	-- 打开游标，创建循环
    	open cur_emp;
    		loop
                -- 抓取游标中的每条记录
                fetch cur_emp into v_emp;
                -- 如果游标中没有记录则退出
                exit when cur_emp%notfound;
                -- 打印输出姓名，薪水，职务
                dbms_output.put_line('姓名：' || v_emp.ename || ', 薪水:' || v_emp.sal || '，职务：' || v_emp.job); 
        	end loop;
    	-- 关闭游标
    	close cur_emp;
    end;

案例效果：  
![在这里插入图片描述][20200327200115215.png]

[20200327193330891.png]: /images/20230528/be7147516f4848d48bab91da630e4804.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1Jvb2tpZXhpYW9NdV9h_size_16_color_FFFFFF_t_70]: /images/20230528/d93f8bb7a321468186eab9dcccb2e934.png
[20200327200115215.png]: /images/20230528/2a305e54c58d4300bc8af1ef1a04d708.png