Oracle系列：表关联、序列、视图、索引、游标、触发器、存储过程、函数

表关联

Oracle中的多表连接种类有：
- 内连接（inner join）
- 自然连接（natural join）
- 外连接（outer join）
- 左外连接（left [outer] join）
- 右外连接（right [outer] join）
- 交叉连接（cross join）
内连接：

--两张表相互匹配，显示匹配上的内容；
select * from 表1 inner join 表2 on 表1.id = 表2.id;

自然连接：

--连接两张表相互匹配，显示匹配上的内容，不需要指定匹配条件，通过两张表共有字段，重复字段只显示一次，要求两张表必须有共有字段;
select * from 表1 natural join 表2;

左外连接：

--以左表为基础表,显示左表所有数据,右表显示匹配上的内容;[]代表可写可不写
select * from 表1 left [outer] join 表2 on 表1.id = 表2.id;

右外连接：

--以右表为基础表,显示右表所有数据,左表显示匹配上的内容;
select * from 表1 right [outer] join 表2 on 表1.id = 表2.id;

交叉连接：

--两个表中的数据相互匹配，结果为两个表数据条数的乘积(笛卡尔积);
select * from 表1 cross join 表2;

对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：

1. （+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
2. 当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
3. （+）操作符只适用于列，而不能用在表达式上。
4. （+）操作符不能与OR和IN操作符一起使用。
5. （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

序列：

关键字：sequence

创建一个序列：

create sequence  序列名称
minvalue : 最小值
maxvalue : 最大值
start with  : 开始于那个值
increment by : 每次递增多少
cache : 缓存

序列的使用

currval : 表示序列的当前值，新序列必须使用一次nextval 才能获取到值，否则会报错 　　
nextval :表示序列的下一个值。新序列首次使用时获取的是该序列的初始值，从第二次使用时开始按照设置的步进递增

查询序列的值：

select seq_name.[currval,nextval] from dual;

SQL语句中使用：

insert into table (id) values (seq_name.nextval)

索引

关键字：index 唯一索引：unique index

创建索引：

create  index 索引名  on  表名(字段名)；

创建唯一索引

create  unique index 索引名 on 表名(字段名)；

复合索引

 create  index 索引名 on 表名(字段1，字段2)

注意：

1. 主键约束自动添加唯一索引
2. 提高了查询效率
3. 索引需要有自己的存储空间
4. 检索频繁的的字段添加索引，杜绝对重复字段添加索引
5. 对数据量较大的表使用索引(万级以上)

视图：

关键字 ：view

创建视图：

create view 视图名称 as 查询语句
--或者
create [ or replace ] [ force ] view 视图名 [with read only]
--[] 代表可有可无

1 or replace： 如果存在同名的视图, 则使用新视图"替代"已有的视图

2 force："强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限

授予创建视图的权限：

grant create view to scott

视图的定义

1. 视图(view)，也称虚表,
   不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
2. 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
3. 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read
   only选项)

视图的优点：简单，安全

缺点： 性能差，修改限制(一般不建议对视图进行增删改操作)

游标：

关键字：cursor

概念：游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果，相当于JDBC中的ResultSet

游标的概念: 游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

笔记上记的：游标：创建一个临时数据空间，对数据遍历并操作

创建游标：

 cursor 游标名称  is SQL语句(增删改查语句)
    open  游标名称			--打开游标
    fetch 游标名称 into ...	--使用游标
    close 游标名称			--关闭游标

into：将查询结果赋值给变量

触发器

关键字：trigger 当发生指定动作是执行某些操作

创建触发器：

create or replace trigger 触发器名称 
    before/after insert/delete/update on 表名/视图名
    for each row 
    begin 
    plsql语句
    end;

before:在事件之间触发 after:在事件之后触发
insert/delete/update： 触发事件

存储过程

关键字：procedure

创建存储过程：

create or replace procedure 存储过程名称
    (
    --定义输入、输出参数--
    参数名1 in 参数类型,
    参数名2 in 参数类型,
    参数名3 in 参数类型,
    参数名4 out 参数类型
    )
    as
    --定义变量--
    --变量名 变量数据类型；如：
     -- numCount integer; 
    begin   
         --处理方法-
    end

• create创建，replace替换(没有就创建，如果存在就替换)
• in 表示输入参数，建议加上去，相当于值传递
• out 表示输出参数，不能省略，相当于引用传递
• “ , ”不能省略
• %type 获取对应表中对应字段的数据类型
• %rowtype: 获取复合数据类型

调用存储过程

declare 
    参数名1 := 值;
    参数名2 := 值;
    参数名3 := 值;
    begin
       --调用存储过程---
       Proc_Test(参数名1,参数名2,参数名3...);
       dbms_output.put_line('输出结果：'|| num_C );
    end;

:= 赋值操作

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点：

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：移植性差

列子：

/*****
** 创建加、减、乘、除计算的存储过程
**输入参数： 数字1，数字2，计算类型
**输出参数： 数字3
*****/
create or replace procedure Proc_Test
(
--定义输入、输出参数--
num_A in integer,
num_B in integer,
numType in integer,
num_C out integer
)
as
--定义变量--
 -- numCount integer;
 -- numStr varchar(20);  
begin   
     --判断计算类型--
     if numType=1 then
        num_C := num_A + num_B;
     elsif numType=2 then
        num_C := num_A - num_B;
     elsif numType=3 then
        num_C := num_A * num_B; 
     elsif numType=4 then
        num_C := num_A / num_B; 
     else
     --其它处理
       dbms_output.put_line('其它处理');
     end if;
end;

函数：

关键字：function

自定义一个函数：

create or replace function FunctionName (
        --传入参数
        para NCHAR
    ) return NUMBER is
        --函数内使用的临时变量
        result   NUMBER(38,0);
        n      NUMBER(38,0);
    begin
        --函数体
        。。。
        。。。
    end;

这里 is 和 as 是一样的作用，但是在视图(VIEW)中只能用as不能用is，在游标(CURSOR)中只能用IS不能用AS。

存储过程和函数的区别：

1. 创建关键字不同
2. 存储过程不能用 return 返回值，函数可以(必须要有)，存储过程要想返回，要使用out，这时返回的是对象、参数

相同点：

1. 创建语法结构相似，都可以携带多个传入参数和传出参数。
2. 都是一次编译，多次执行。

不同点：

1. 存储过程定义关键字用procedure，函数定义用function
2. 存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
3. 执行方式略有不同，存储过程的执行方式有两种
   （1.使用execute2.使用begin和end,函数除了存储过程的两种方式外，还可以当做 表达式使用，例如放在select中（select f1() form dual;）。

总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。

---

修道神仙，身后千年，还要几杯绿酒。