【Oracle基础】视图，索引，pl/sql基本语法

1. 视图

视图就是封装了一条复杂查询的语句。视图就是提供一个查询的窗口，所有数据来自于原表。

• 语法一

CREATE VIEW 视图名称 AS 子查询

首先我们可以将scott用户下的emp表复制到system,通过system用户操作，（因为创建视图必须拥有dba权限）

--查询语句创建表
create table emp as select * from scott.emp

--创建视图【必须有dba权限】
create view v_emp as select ename ,job from emp

视图创建完毕就可以使用视图来查询，

--查询视图
select * from v_emp

• 语法二：

CREATE OR REPLACE VIEW 视图名称 AS 子查询

如果视图已经存在我们可以使用语法 2 来创建视图，这样已有的视图会被覆盖。

create or replace view v_emp as select * from emp

--修改视图[不推荐]
update v_emp set job='CLERK' where ename='ALLEN';
commit;

修改视图我们会发现视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。
我们可以设置视图为只读。

• 语法三

CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY

--创建只读视图
create view v_emp1 as select ename ,job from emp with read only;

• 视图的作用？

1. 第一：视图可以屏蔽掉一些敏感字段。
2. 第二：保证总部和分部数据及时统一。

2. 索引

索引是用于加速数据存取的数据对象。 合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。索引有很多种我们主要介绍常用的几种:

• 单列索引
  单列索引是基于单个列所建立的索引，比如:

CREATE index 索引名 on 表名(列名)

--单例索引
---创建一个单例索引
create index idx_ename on emp(ename);
---单例索引触发规则，条件必须是索引列中的原始值。
---单行函数，模糊查询，都会影响索引的触发
select * from emp where ename='SCOTT'

• 复合索引
  复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同,比如：

--创建复合索引
create index idx_enamejob on emp (ename,job);
--复合索引第一列为优先检索列
--如果要触发复合索引，必须包含有优先检索列中的原始值。
select * from emp where ename='SCOTT' and job='xx'；--触发符合索引
select * from emp where ename='SCOTT' or job='xx'；--不触发索引
select * from emp where ename='SCOTT';--触发单例索引

• 索引的使用原则：

1. 在大表上建立索引才有意义
2. 在 where 子句后面或者是连接条件上的字段建立索引
3. 表中数据修改频率高时不建议建立索引

3. pl/sql基本语法

• 什么是 PL/SQL?
  PL/SQL（Procedure Language/SQL）,PLSQL 是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

范例 1： 为职工涨工资，每人涨 10％的工资。

update emp set sal=sal*1.1

范例 2： 例 2: 按职工的职称长工资,总裁涨 1000 元,经理涨 800 元，其他人员涨 400 元。
这样的需求我们就无法使用一条 SQL 来实现，需要借助其他程序来帮助完成，也可以使用 pl/sql。

3.1 pl/sql程序语法

• 程序语法：

declare
	说明部分 （变量说明， 游标申明，例外说明 〕
begin
	语句序列 （DML 语句〕 …
exception
	例外处理语句
end;

3.2 常量和变量定义

在程序的声明阶段可以来定义常量和变量。

• 变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number,boolean, long

定义语法： varl char(15);
		 psal number(9,2);
说明变量名、数据类型和长度后用分号结束说明语句。
常量定义： married constant boolean:=true

• 引用变量

myname emp.ename%type;

引用型变量，即 myname 的类型与 emp 表中 ename 列的类型一样
在 sql 中使用 into 来赋值.

declare
	emprec emp.ename%type;
begin
	select t.ename into emprec from emp t where t.empno = 7369;
	dbms_output.put_line(emprec);
end;

• 记录型变量

emprec emp%rowtype

记录变量分量的引用

emprec.ename:='ADAMS';

declare
	p emp%rowtype;
begin
	select * into p from emp t where t.empno = 7369;
	dbms_output.put_line(p.ename || ' ' || p.sal);
end;

3.3 if分支

• 语法一：

IF 条件 THEN 
	语句 1;
	语句 2;
END IF;

• 语法二：

IF 条件 THEN 
	语句序列 1;
ELSE 
	语句序列 2;
END IF;

• 语法三：

IF 条件 THEN 
	语句;
ELSIF 语句 THEN 
	语句;
ELSE 
	语句;
END IF;

--pl/sql中的if判断
--输入小于18的数字，输出未成年
--输入大于18小于40的数字，输出中年人
--输入大于40的数字，输出老年人
declare 
  i number(3) := &1;
begin
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<40 then
    dbms_output.put_line('中年');
  else
    dbms_output.put_line('老年人');
  end if;
end;

3.4 LOOP循环语句

• 语法一：

WHILE total <= 25000 LOOP
	.. .
	total : = total + salary;
END LOOP;

• 语法二：

Loop
	EXIT [when 条件];
	……
End loop

• 语法三：

FOR I IN 1 . . 3 LOOP
	语句序列 ;
END LOOP ;

--pl/sql中的loop循环
---用三种方式输出1-10的数字
---while 
declare
  i number(2) :=1;
begin
  while i<11 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
--exit循环
declare
  i number(2) :=1;
begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    i := i+1;
   end loop;
end;
--for循环
declare
  i number(2) :=1;
begin
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

3.5 游标Cursor

在写 java 程序中有集合的概念，那么在 pl/sql 中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。

游标：可以存放多个对象，多行记录

• 语法：

CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;

cursor c1 is select ename from emp;

• 游标的使用步骤：
  • 打开游标： open c1; (打开游标执行查询)
  • 取一行游标的值： fetch c1 into pjob; (取一行到变量中)
  • 关闭游标： close c1;(关闭游标释放资源)
  • 游标的结束方式 exit when c1%notfound
  • 注意： 上面的 pjob 必须与 emp 表中的 job 列类型一致：
    定义： pjob emp.empjob%type;

--游标：可以存放多个对象，多行记录
--输出emp表中所有员工的姓名
declare 
  cursor c1 is select * from emp;
  emprow emp%rowtype;
begin
  open c1;
      loop
        fetch c1 into emprow;
        exit when c1%notfound;
        dbms_output.put_line(emprow.ename);
      end loop;
  close c1;
end;

--给指定部门员工涨工资
declare 
  cursor c2(eno emp.deptno%type) is select empno from emp where deptno =  eno;
  en emp.empno%type;
begin
  open c2(10);
       loop
         fetch c2 into en;
         exit when c2%notfound;
         update emp set sal = sal + 100 where empno=en;
         commit; 
       end loop;
  close c2;
end;