Oracle基础使用2

Oracle基础2

视图

1. 介绍
   视图可以封装一条sql查询语句进行展现数据，视图是虚拟的表，不真实存在，为数据库真实的表提供多种展现方式供其他用户查看，可以屏蔽敏感信息
2. 视图的创建与查看

create or replace view 视图名称 as sql查询语句 -- 创建视图1(非只读)
说明：sql语句查询的数据会封账到视图中。这样创建视图存在一定的危险性，因为用户可以修改视图的内容，从而修改源表的数据

select * from 视图名称 -- 查看视图

create or replace view 视图名称 as sql查询语句 with read only
说明：设置为只读视图，则不能修改这个视图的数据

3. 视图的使用

存在一个员工表emp,包含了员工的基本信息(编号，名称，工作)
-- 创建视图
create or replace view view_emp as select empno,ename,job from emp with read only;

-- 创建用户 
create user testA(用户名) identified by testA(密码) default tablespace learn(全局数据库名称);  -- 括号的内容只起到注释作用，不是语法要求

-- 给用户授权
grant connect,resource to testA;

-- 给用户查询视图view_emp的权限
grant select on view_emp to testA;
  
 -- 用户testA查询视图
 select * from view_emp;
 用户A可以查询视图view_emp

索引

1. 介绍
   索引是用于提高查询数据性能的数据库对象。合理的使用索引可以大大降低I/O次数，提高数据访问性能
2. 数据库查询数据方式
   (1) 普通查询数据方式：从数据表的第一条位置开始查询，逐行查询，直至查到符合的数据
   (2) 索引查询数据方式：需要建立索引数据，数据库对数据建立B树数据结构，用户通过索引B树查找数据
3. Btree(B树)
   B树即二叉搜索树
   特点：
   (1) 所有非叶子结点最多拥有两个儿子节点
   (2) 所有结点存储一个关键字
   (3) 非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树
   
4. 索引类型和创建索引的语法

(1) 单列索引
单列索引是基于单个列建立的索引

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

(2) 复合索引
复合索引是基于两个或多个列的索引，在同一张表上可以创建多个索引

create index 表名 on emp(列名1,列名2)

5. 使用索引的原则
   (1) 在数据量大的表上建立索引才有意义(如5000000条数据)
   (2) 在where子句后面或是连接条件上的字段建立索引，添加了索引的字段作为第一个过滤条件
   (3) 表中数据修改频率高时不建议建立索引，数据库内部维护成本大

pl/sql基本语法

1. 介绍
   PL/SQL(Procedure Language/SQL)是oracle对sql语言的过程化扩展，在SQL命令语言中增加了过程处理语句(如分支，循环等)，使SQL语言具有过程处理能力
2. pl/sql程序语法

程序语法结构：
declare 
	说明部分(用于定义变量，常量，引用变量，记录型变量，游标)
begin 
	语句序列(DML语句)
end;
如果没有说明部分可以省略declare

3. 变量，常量，引用变量和记录型变量定义

(1) 变量
变量的基本类型就是oracle中建表时字段的变量，如char varchar2，date，number，boolean，long
定义语法：
v_name varchar(25):= 'SCOTT'; --声明并初始化
psal number(9,2); -- 只声明
注意：oracle中的赋值为“:=”

(2) 常量
定义常量：
married constant boolean:=true; 
使用“constant”关键字声明常量

(3) 引用变量
引用变量声明：
存在一个员工表emp(存在属性:empno,ename)
my_name emp.ename%type -- type表示接受同一个类型一个数据
my_name的类型与emp表中ename列的类型是一样的

在sql中使用into来给引用型变量赋值
declare 
	my_name emp.ename%type
begin
	select t.ename into my_name from emp t where t.empno = 1;
end;

(4) 记录型变量
记录型变量声明：
v_row emp%rowtype; -- 用于接收emp一条记录到记录型变量v_row中，rowtype表示接收一条记录型数据

使用into来给记录型变量赋值
declare 
	v_row emp%rowtype;
begin
	-- 给记录型变量赋值一条数据
	select * into v_row from emp where empno = 1;
	-- 打印记录型变量里面的数据
	dbms_output.put_line('记录型变量:' || v_row.ename || '--' || v_row.empno);
end;

4. 游标
   (1) 介绍：游标用于在pl/sql中记录多条数据，可以存储查询返回的多条数据。游标是一个数据集，与Java中jdbc里面的ResultSet类似

(2) 游标语法
cursor 游标名字 is sql查询语句;

-- 使用游标语法
open 游标名称;	-- 打开游标
	loop 	  	-- 通过遍历，将游标里面的内容逐条获取
		fetch 游标名称 into 记录型变量; -- 获取游标里面一条数据
		exit when 游标名称%notfound;  -- 游标里面没有数据退出循环
		逻辑处理获取记录型变量里面的数据
	end loop;
close 游标名称;



游标的使用示例：
存在一个员工表emp(ename,sal,job,deptno)，属性分别为姓名，工资，工资，部门编号

declare -- 声明
	cursor cur_emp is select * from emp where deptno = 10; -- 声明游标
	v_row emp%rowtype; -- 声明记录型变量
begin
	open cur_emp; -- 开启游标
		loop
			fetch cur_emp into v_row;
			exit when cur_emp%notfound;
			-- 打印信息
			dbms_output.put_line(v_row.ename || '--' || v_row.sal || '--' || v_row.job);
		end loop;
	close cur_emp; -- 关闭游标
end;

5. if 分支

语法1：
if 条件 then
	逻辑处理;
end if;

语法2：
if 条件 then
	逻辑处理
else
	逻辑处理
end if;

语法3：
if 条件 then
	逻辑处理
elsif 条件 then
	逻辑处理
else
	逻辑处理
end if;

使用示例：
判断不同年龄段的人，18岁以下是未成年人，18岁以上60岁以下是成年人，60岁以上是老年人

declare 
	v_age number(8):=# -- 会弹出窗口让用户输入，动态获取数据
begin
	if v_age < 18 then
		dbms_output.put_line(v_age || '未成年人');
	elsif vage >= 18 and v_age < 60 then
		dbms_output.put_line(v_age || '成年人');
	else
		dbms_output.put_line(v_age || '老年人');
	end if;
end;

6. loop循环语句

语法1：loop循环结构
loop
	exit [when 条件];
	逻辑处理
end loop;

语法2：while有条件循环
while 条件
	loop
		逻辑处理
	end loop;

语法3：for有限次循环
for 变量 in 起始值..结束值
	loop
		逻辑处理
	end loop;

存储过程

1. 介绍
   存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。简单来说，就是给pl/sql代码起个名字，之后调用它，类似于函数。
2. 语法

-- in代表输入参数，传递数据给存储过程，存储过程里面使用，如果不写in或out，默认是in输入参数
-- out代表输出参数，用于接收存储过程执行最后的结果，存储过程返回的值
create or replace procedure 存储过程名字(变量名字N [in]/out 数据类型)
is | as -- 用于声明，类似declare,但是is | as 不能省略
begin
	逻辑处理
end;

调用方式1：
call 存储过程名称(传递参数)

调用方式2:
begin
	存储过程名称(传递参数)
end;


使用示例：
前提：存在员工表emp(empno,sal),分别是员工编号和工资属性
需求：获取指定员工的年薪
create or replace procedure proc_year_sal(v_empno number,v_year_sal out number) -- 不需要指定长度
is  --用于声明，不能省略
begin
	select sal*12 into v_year_sal from emp where v_empno = emp.empno;
end;

-- 调用
declare 
	v_year_sal number(8,2);
begin
	proc_year_sal(1,v_year_sal);
end;

begin
	proc_year_sal(1);
end;

存储函数

语法：
create or replace function 函数名字(变量名字N [in]/out 数据类型)
return 数据类型
is | as
	声明变量
begin
	逻辑处理
end;

示例：获取指定员工的年薪(与上个示例类型)
create or replace function func_year_sal(v_empno number)
return number
is
	v_year_sal number(8,2);
begin
	select sal*12 into v_year_sal from emp where emp.empno = v_empno;
	return v_year_sal;
end;

调用：
select empno,sal,func_year_sal(empno) 年薪 from emp;

总结：存储过程与存储函数的区别

1. 存储函数可以直接内部返回，不需要传递输出参数，使用非常方便，经常在sql查询语句内部使用
2. 一般企业应用，java代码会直接执行sql语句，也会直接执行存储过程

触发器

1. 介绍
   数据库触发器是一个与表相关联的，存储的PL/SQL程序。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时，oracle会自动地执行触发器中定义的语句序列
2. 作用
   (1) 数据确认
   (2) 实施复杂的安全型检查
   (3) 做审计，跟踪表上所做的数据操作等
   (4) 数据的备份和同步
3. 触发器的类型
   (1) 语句级触发器：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行
   (2) 行级触发器(FOR EACH ROW)：触发器语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量，识别值的状态
4. 语法

当执行insert/update/delete等数据表改变的sql语句时就会触发触发器的执行

语法：
create or replace trigger 触发器名字
before | after  -- 设置触发器在改变数据之前或之后执行
insert | update | delete [of 列名] -- 设置具体改变数据的命令，可以卸下一个，也可以写多个，多个使用or连接
on 表名  -- 指定改变哪个表时触发
[for each now]  -- 触发器的级别
[declare]
	声明变量
begin
	逻辑处理
end;


使用示例：
需求：修改了员工的工资就要去将修改数据备份到一个单独的表中

-- 创建备份表
create table tab_sal_log(
	id number(8) primary key, 
	empno number(8), -- 被修改工资的员工编号
	oldsal number(8,2), -- 修改之前的工资
	newsal number(8,2), -- 修改之后的工资
	updatetime date     -- 修改日期
	);

-- 创建序列
create sequence seq_sal_log;
-- 创建触发器
create or replace trigger trg_emp_sal_update
after  -- 设置触发器在改变数据之后触发，为了以防数据修改之前发生错误，所以要在真正修改了数据之后再备份
update of sal --设置为修改了表的sal字段才会触发
on emp  -- 具体改变员工表
for each row -- 只有行级触发器才能使用:new和:old
begin
	--伪记录变量
	-- :new,代表的是新改变数据后的当前记录
	-- :old,代表的是改变数据之前的旧的当前记录
	-- 开始备份
	insert into tab_sal_log values(seq_sal_log.nextval,:old.empno,:old.sal,:new.sal,sysdate);
end;

伪记录变量有三种