Oracle 视图、存储过程、函数、序列、索引、同义词、触发器

优质博文：IT-BLOG-CN

一、视图

从表中抽出的逻辑上相关的数据集合，视图是一种虚表，视图是建立在已有表的基础之上，视图赖以建立的这些表称为基表。向视图提供数据的是 SELECT语句，可以将视图理解为存储起来的SELECT语句。视图中的数据会随着基表的变化而变化。

那为什么要使用视图呢？
1）、控制数据访问
2）、简化查询
3）、避免重复访问相同的数据

【1】创建视图语句：CREATE VIEW举个栗子看下：

create or replace view empview 
as 
select employee_id emp_id,last_name name,department_name
from employees e,departments d
Where e.department_id = d.department_id

【2】删除视图：DROP VIEW；
【3】TOP_N分析：分析查询一个列中最大或最小的n个值：其实就是现实列的个数，相当于Mysql中的limit
在这里插入图片描述

二、存储过程

存储过程是存储在数据库中提供所有用户程序调用的子程序。它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

--创建存储过程的语句
create [or replace] procedure 存储过程名
　　[(@参数1 类型，@参数2 out 类型……)]

　　as
　　　　变量名　　类型；
　　begin
　　　　程序代码体
　　end;

【栗子】认真看看也就懂了

--如果命令行中创建时，修改结束语
delimiter //
create or replace procedure p2
(@name in varchar2,@age int,@msg out varchar2)
--参数列表中，声明变量类型时切记不能定义大小，只写类型名即可，例如参数列表中的name变量的声明
--参数列表中，输入参数用in表示，输出参数用out表示，不写时默认为输入参数。
------------输入参数不能携带值出去，输出参数不能携带值进来，当既想携带值进来，又想携带值出去，可以用in out
as
  --存储过程中定义的参数列表，当然我就举个栗子，下面这个参数后续无用
  @last_name varchar(50);
begin
  SELECT name FROM table WHERE id=1 INTO @msg;
  --赋值时除了可以使用：=，还可以用into来实现
  --上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;
end;
//

--执行存储过程
--在SQLPlus中运行PL/SQL块前，如果要将执行结果输出，需要先执行 set serveroutput on 命令
set serveroutput on;
declare
  msg varchar2(100);
--SQL环境下，基本语法为：call sp_name [参数名]；
--PL/SQL环境下，基本语法为：begin sp_name [参数名] end；
begin
  p2('张三',23,msg);
  dbms_output.put_line(msg);
end;

三、函数

函数与存储过程的结构类似，但是函数必须有一个return子句，用于返回函数值。

--创建序列号格式
CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增长的数值
       [START WITH n]    --从哪个值开始
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录

【栗子】好好研究下吧，精华都在下面：

create or replace function f1
RETURNS varchar--必须有返回值，且声明返回值类型时不需要加大小
as
  msg varchar(50);
begin
   msg := 'hello world';
   return msg;
end;

--执行函数方式1
select f1() from dual;
--执行函数方式2
set serveroutput on;
begin 
  dbms_output.put_line(f1());
end;

【总结】：函数与存储过程的区别：执行方式略有不同，存储过程的执行方式有两种（①:使用execute②:使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。还有就是如果只有一个返回值，用存储函数，否则，一般用存储过程。

四、序列

提供有规律的数值，可供多个用户用来产生唯一数值的数据库对象。主要用于提供主键值。将序列装入内存可以提高访问效率。

--创建序列号格式
CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增长的数值
       [START WITH n]    --从哪个值开始
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录

【1】查询数据字典视图USER_SEQUENCES获取序列定义信息。
【2】NEXTVALUE：返回序列中下一个有效的值，任何用户都可以引用。CURRVAL：存放序列的当前值。
【3】修改序列：修改序列的增量、最大值、最小值、循环选项、是否装入内存。

ALTER SEQUENCE dept_deptid_seq
               INCREMENT BY 20
               MAXVALUE 999999
               NOCACHE
               NOCYCLE;

【4】删除序列：DROP SEQUENCES语句删除。

DROP SEQUENCE dept_deptid_seq;

五、索引

提高查询效率，一种独立与表的模式对象，可以存储在于表不同的磁盘或表空间，索引损坏只影响查询速度。
【1】自动创建索引：在定义PRIMARY KEY或UNIQUE约束后系统自动在响应的列上创建唯一性索引。
【2】手动创建索引：可以在其他列上添加非唯一索引，以加速查询。

CREATE INDEX index
ON table (column[, column]...);

【3】当出现如下情况时，适合创建索引：①、列中数据值分布范围很广。②、列经常出现在WHERE子句或连接条件中。③、表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%。
【4】查询索引：USER_INDEXS，删除索引：DROP INDEX index；

六、同义词

给对象起别名，缩短名字方便访问。

CREATE [PUBLIC] SYNONYM synonym
FOR    object;

七、触发器

触发器是在事件发生时隐式地自动运行的PL/SQL程序块，不能接收参数，不能被调用。

DELIMITER //  --先临时修改一下语句分隔符，这样就可以在触发器定义中使用分号；

CREATE[ORREPLACE]TRIGGER trigger_name
　　{BEFORE|AFTER}
　　{INSERT|DELETE|UPDATE[OF column[,column…]]}ON{table_name|view_name}
　　[REFERENCING
　　{OLD[AS]old_name|NEW[AS]new_name}]
　　[FOR EACHROW]
　　[WHEN trigger_condition]
　　trigger_body;

--尾随一个实例
DELIMITER //
CREATE OR REPLACE TRIGGER POLEINFOTABLE_CYCLE  
   AFTER INSERT OR UPDATE OF CIRCUITRYTYPEPARTAL,TESHUQUPARTAL,UNITSTATUSPARTAL 
   ON POLEINFOTABLE  
   FOR EACH ROW  
   DECLARE PRAGMA AUTONOMOUS_TRANSACTION; 
   BEGIN    
    --这里可以使用 OLD 和 NEW ;
    --old表示插入之前的值，new表示新插入的值；（old用在删除和修改，new用在添加和修改）
      update poleinfotable set MINCYCLE=least(CIRCUITRYTYPEPARTAL,UNITSTATUSPARTAL,TESHUQUPARTAL); 
COMMIT; 
END;
//
DELIMITER ;