Oracle系列：表关联、序列、视图、索引、游标、触发器、存储过程、函数_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;
1
2

自然连接：

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

左外连接：

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

右外连接：

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

交叉连接：

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

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

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

序列：

关键字：sequence

创建一个序列：

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

序列的使用

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

查询序列的值：

select seq_name.[currval,nextval] from dual;
1

SQL语句中使用：

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

索引

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

创建索引：

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

创建唯一索引

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

复合索引

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

注意：

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

视图：

关键字 ：view

创建视图：

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

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

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

授予创建视图的权限：

grant create view to scott
1

视图的定义

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 游标名称			--关闭游标
1
2
3
4

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

触发器

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

创建触发器：

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

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

存储过程

关键字：procedure

创建存储过程：

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

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

调用存储过程

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

:= 赋值操作

存储过程是一个预编译的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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

函数：

关键字：function

自定义一个函数：

create or replace function FunctionName (
        --传入参数
        para NCHAR
    ) return NUMBER is
        --函数内使用的临时变量
        result   NUMBER(38,0);
        n      NUMBER(38,0);
    begin
        --函数体
        。。。
        。。。
    end;
1
2
3
4
5
6
7
8
9
10
11
12

这里 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;）。

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

---

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