Oracle数据库基本知识

视图

实际上是一张虚拟的表，对sql语句的封装

使用视图的优点:
1.简化数据操作:视图可以简化用户处理数据的方式
2.着重于特定数据:不必要的数据或敏感数据可以不出现在视图中。
3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
4.提供向后兼容性:视图使用户能够在表的架构更改时为表创建向后兼容接口

1.基本语法

create [OR REPLACE] [FORCE] view view_name
as subquery
[WITH CHECK OPTION]
[WITH READ ONLY]

**[OR REPLACE]**：若所创建的试图已经存在，ORACLE 自动重建该视图;

**[FORCE]**：不管基表是否存在ORACLE 都会自动创建该视图

**[WITH CHECK OPTION]**：插入或修改的数据行必须满足视图定义的约束;

**[WITH READ ONLY]**：该视图上不能进行任何DML操作

subquery：一条完整的SELECT语句，可以在该语句中定义别名

2.删除已视图语法

drop view view_name

序列

1.创建简单序列

create sequence 序列名

2.查询序列的下一个值

select 序列名.nextval from dual;

3.查询序列的当前值

select 序列名.currval from dual;

同义词

同义词实质上就是指定对象的另一个名称

创建与使用同义词基本语法

具体语法如下：

create [public] SYNONYM synonym for object;

[public]：可选项，如果加上表示公有，如果不加表示私有。私有同义词其他的用户无法访问到

object：表示具体的对象。

索引

通过索引可以快速地找到一条记录的物理地址，加快查找速度。一般在数据量特别大的时候使用

带来的弊端是占用了磁盘的空间

1.普通索引的语法

create index 索引名 on 表名（列名）;

例如：

create index idx_name on t_owners(name);

2.创建唯一索引

create unique index 索引名称 on 表名（列名）;

3.创建复合索引

经常要对某几列进行查询，我们可以建立复合索引，也就是基于两个以上的列建立一个索引。

create index 索引名称 on 表名（列名,列名,列名····）;

4.反向键索引

当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布

语法：

create index 索引名称 on 表名（列名） reverse;

PL/SQL

在sql命令语言中增加了过程处理语句，使sql语言具有过程处理能力

1.基本语法结构

[declare
  -- 声明变量
]
begin
  -- 代码逻辑
[exception
  -- 异常处理
]
end;

2.变量

2.1 声明变量的语法

变量名 类型（长度）

2.2变量赋值的语法

变量名:=变量值

2.3示例

需求:
声明变量水费单价、水费字数、吨数、金额。

对水费单价、字数、进行赋值 。吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。计算金额，金额-单价*吨数。

输出单价 、数量和金额

declare
	v_price number(10,2);-- 10位 有2位小数
	v_usenum number;-- number不定长度默认长度为18
	v_usenum2 number(10,2);
	v_money number(10,2);
begin
	v_price:=2.45;
	v_usenum:=9243;
	v_usenum2:=round(v_usenum/1000,2);-- round表示四舍五入
	v_money:=v_price*v_usenum2;
	
	DBMS_OUTPUT.put_line('金额：'||v_money);
end;

3.条件判断

基本语法1：

if 条件 then
	业务逻辑
end if;

基本语法2：

if 条件 then
	业务逻辑
else
	业务逻辑
end if;

基本语法3：

if 条件 then
	业务逻辑
elseif 条件 then
	业务逻辑
else
	业务逻辑
end if;

4.循环

4.1无条件循环

语法结构：

loop
	-- 循环语句
end loop;

4.2有条件循环

语法结构：

while 条件
loop
end loop;

4.3 for循环

语法结构：

for 变量 in 起始值..终止值
loop

end loop;

5.异常

运行程序时出现错误叫做异常

异常有两种类型：

• 预定义异常
• 用户定义异常

语法结构：

exception
	when 异常类型 then
	异常处理逻辑

游标

游标是PL/SQL中的结果集

1.声明游标

cursor 游标名称 is SQL语句

2.使用游标语法

open 游标名称 -- 打开游标
loop
	fetch 游标名称 into 变量 -- 提取游标
	exit when 游标名称%notfound -- 这个属性的意思是游标已经走到底了 退出循环游标
end loop;
close 游标名称; -- 关闭游标

存储函数

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL进行逻辑的处理

1.创建或者修改存储过程的语法

create [or replace] function 函数名称
(参数名称 参数类型,参数名称 参数类型····)
return 结果变量数据类型
is
	变量声明部分
begin
	逻辑部分;
	return 结果变量;
[exception
	异常处理部分]
end;

2.示例

需求：创建存储函数，根据地址ID查询地址名称

语句：

create or replace function fn_getaddress
(v_id number)
return varchar2 -- varchar2是oracle特有的类型
is
	v_name varchar2(30);
begin
	-- 查询地址表
	select name into v_name from t_address where id=v_id;
	return v_name;
end;

-- 存储函数测试
select fn_getaddress(3) from dual;

存储过程

存储过程是被命名的PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑

存储过程与存储函数区别：

1. 存储函数中有返回值，且必须返回;而存储过程没有返回值，可以通过传出参数返回多个值
2. 存储函数可以在select语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3. 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

1.创建或修改存储过程的语法：

create [or replace] procedure
(参数名称 参数类型,参数名称 参数类型····)
-- 没有return了
is|as
	变量声明部分;
begin
	逻辑部分
[exception
	异常处理部分]
end;	

参数只指定类型，不指定长度

过程参数的三种形式：

**IN ** 传入参数（默认）

OUT 传出参数，主要用于返回程序运行的结果

IN OUT 传入传出参数

2.示例

2.1创建不带传出参数的存储过程

-- 增加业主信息序列
create sequence seq_owners

-- 增加业主信息存储过程
create or replace procedure pro_owners_add
(
	v_name varchar2,
	v_addressid number,
)
is
begin 
	insert into t_owners values(seq_owners.nexval,v_name,v_addressid)
	commit;
end;

-- 调用不带传出参数的存储过程
call pro_owners_add('ming',2);

2.2创建带传出参数的存储过程

需求：添加业主信息，传出参数为新增业主ID

-- 增加业主信息存储过程
create or replace procedure pro_owners_add
(
	v_name varchar2,
    v_addressid number
)
is 
begin
	-- 对传出参数赋值
	select seq_owners.nextval into v_id from dual;
	-- 新增业主
	insert into T_OWNERS
values(v_id,v_name,v_addressid);
commit;
end;

-- 调用传出参数的存储过程
declare
	v_id number
begin
	pro_owners_add('明',2);
	dbms_output.put_line(v_id);
end;

触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列

触发器可用于：

• 数据确认
• 实施复杂的安全性检查
• 做审计（查账），跟踪表上所做的数据操作等
• 数据的备份和同步

触发器分类：

• 前置触发器(BEFORE)
• 后置触发器(AFTER)

1.创建触发器的语法

create [or replace] trigger 触发器名
	before|after
	[delete][[or]insert] [[or]update [of 列名]]
	on 表名
	[for each row][where(条件)]
declare
 ···
begin 
	PL/SQL块
end;

for each row：代表该触发器为行级触发器 不写的话默认为语句级触发器

2.前置触发器的示例

需求：当用户输入本月累计表数后，自动算出本月使用数

语句：

create or reolace trigger tri_account_num1
before
update of num1
on t_account
for each row
declare
begin
	-- 通过伪记录变量修改usenum字段的值
	:new.usenum:=:new.num1-:new.num0;
end;

3.后置触发器的示例

需求：当用户修改了业主信息表的数据时记录修改前与修改后的值

语句：

-- 创建业主名称修改日志表：用于记录业主更改前后的名称
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);

-- 创建后置触发器，自动记录业主更改前后的日志
create or replace trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
	-- 向日志表中插入记录
	insert into t_owners_log values(sysdaye,:new.id,:old.name,;new.name);
end;
-- 触发器不用写commit