Oracle 数据库对象

1. 数据库对象

1.1 序列

序列是Oracle数据库中用于实现值增长或者减少的一个对象，序列每次取值都会自动增加

序列的关键词： seqence 

创建序列的语法：

create sequence 序列名
    start with 50 -- 其实值
    increment by 10 -- 每次加几个
    nomaxvalue -- 不设置最大值
    nocycle -- 一直累加，不循环
    cache 10; -- 设置缓存序列个数，可以一次取多个序列，效率更快

如果序列的最大值设置为50000，没有设置nocycle的话序列的值到50000后会从0重新开始

序列必须手动调用：

-- 查看序列的当前值
select MySequence.currval from dual;
-- 取序列的下一个值
insert into dept(deptno,dname,loc) values(MySequence.Nextval,'财务','合肥');

自动实现列的增长值需要使用到触发器：trigger

删除序列

drop sequence 序列名称

1.2 同义词

同义词就是数据库对象的别名，永久存储的别名： select 别名 form  创建同义词：

select or replace synonym 名称 or 表名

同义词分为：公共同义词 私有同义词 创建公共同义词：create public synanym....

dual：公共同义词

通用词的作用：

• 避免应用程序直接引用

1.3 视图

视图是虚拟的表，存储的查询语句； 可以针对视图执行增删改查的操作，一般是执行查询操作，如果是连表的视图就无法进行增删改操作了

视图的作用

• 简化查询所使用的语句

• 起到安全和保密的作用

如果创建视图:

create view 视图名称
as
 --代码块

创建一个视图

create view v_emp
as
select e.empno,ename,d.dname,sal+nvl(comm,0) as salary
   from emp e
   left join dept d on e.deptno = d.deptno;

1.4 索引

索引可以优化查询速度，但是索引不会用就不要用，不然会降低查询速度

创建索引的语法：

-- 默认索引 B树索引
create index index_id on t(id);
-- 反向键索引
create inedex index_reverse_empno on emp(empno) reverse;
-- 位图索引
create bitmap index index_bit_job on emp(job)

反向键索引：用来较少磁盘的io瓶颈问题

位图索引：适合低基数的列，如：省份 订单状态

创建索引的原则：

• 频繁搜索、排序、分组的列可以作为索引

• 经常用作连接的列（主键、外键）可作为索引

• 将索引放在单独的表空间中

• 使用nologging子句可减少日志信息

• 定期重建索引（创建好索引后，数据被删除，索引不会自动删除）

• 仅包含几个不同值的列建议使用位图索引

• 不要在仅包含几行数据的表中建索引

1.5 PL/SQL 简介

SQL：结构化查询语言：增删改查、事务、数据控制、数据定义

PL/SQL：过程语言+数据结构语言

可以使用变量、循环、条件判断，配合sql语句实现一系列复杂的业务操作

PL/SQL的组成部分：

[declare] -- 声明变量
    <-- 声明部分，声明常量或者变量以及类型等 -->
begin -- 执行部分开始的标志
    <-- 执行部分，整个PL/SQL块的主体部分-->
[exception] -- 异常开始部分关键字
    <-- 处理异常部分 -->
end; -- 结束标志

输出语法是： dbms_output.put_line('111'); 

如果输出不出值需要设置：set serveroutput on;

弹框输入使用: &提示 

变量赋值使用： := 

1.5.1 if 条件判断

语法：

if 条件 then
    <--代码块-->
else
    <--代码块-->
end if;

1.5.2 lood 循环

declare
  i number(2) := 0;
begin
  loop
    exit when i >= 10; -- 满足条件后退出循环
    dbms_output.put_line('111');
    i := i+1;
  end loop;
end;

1.5.3 for 循环

begin
  -- 1到10
  for i in 1..10 loop
    dbms_output.put_line('aaa');
  end loop;
  -- 10到1
  for i in reverse 1..10 loop
    dbms_output.put_line('aaa');
  end loop;
end;

1.5.4 异常处理

找不到数据异常： no_data_found 

其他异常： others 

异常信息： sqlerrm 

案例

set serveroutput on;
declare
   v_sal emp.sal%type;
   v_sal_avg number;
   v_empno emp.empno%type := &编号;
begin
  select sal into v_sal from emp where empno = v_empno;
  select avg(sal) into v_sal_avg from emp;
  if v_sal < v_sal_avg then
    dbms_output.put_line('员工编号：'||v_empno||' 原工资:'||v_sal||'涨到'||v_sal_avg);
  else
    dbms_output.put_line('本轮涨工资没有赶上！');
  end if;
  exception
    when no_data_found then
      dbms_output.put_line('找不到这个员工');
    when others then
      dbms_output.put_line('程序异常！ '|| sqlerrm);
end

1.6 存储过程

在存储过程中部提交事务，交由调用者进行事务处理

创建存储过程语法

create or replace procedure proc_存储过程名称(参数列表)
as
  存储过程中使用到的变量
begin
        <-- 代码块 -->
end;
/   

1.6.1 带参数的存储过程（输入参数 输出参数）

1.6.1.1 输入参数

输入参数在参数列表中： 参数名称 参数类型 

代码：

create or replace procedure proc_text(no number) ...

1.6.1.2 输出参数

存储过程没有return 需要使用输出参数代表返回值

输出参数需要用out标识一下

语法： 参数名称 out 参数类型 

代码：

create or replace procedure proc_test(no out number) ...

1.7 函数

函数必须有一个返回值，函数只能有一个返回值

创建函数的方法与创建存储过程的语法基本一致，只需要把 procedure 换成 function 

代码：

create or replace function queryincome(no number)
return number
as
  v_sal number;
  v_comm number;
  v_income number;
begin
  select sal,comm into v_sal,v_comm from emp where empno = no;
  v_income := nvl(v_sal,0) + nvl(v_comm,0);
  return v_income;
end;
/

1.8 触发器

创建触发器的基本语法：

create or replace trigger 触发器名称
-- before 在...之前
-- after 在...之后
{before| after} 
--- 修改操作 添加操作 删除操作 选一个
{delete | insert | update | of 列名}
-- 表名
on 表名
-- 代码块
PLSQL块

在触发器中用抛出异常来阻止数据的增删改操作

抛出异常： raise_application_error(-20001,'错误信息') 

修改中获取新的数据使用： :new 

修改中获取旧的数据使用： :old 

创建一个触发器：

create or replace trigger trigger_insert
  before insert
  on dept 
  for each row
begin
   select MySequence.Nextval into :new.deptno from dual;      
end;
/