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

表关联

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

--两张表相互匹配,显示匹配上的内容;
select * from1 inner join2 on1.id =2.id;

自然连接:

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

左外连接:

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

右外连接:

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

交叉连接:

--两个表中的数据相互匹配,结果为两个表数据条数的乘积(笛卡尔积);
select * from1 cross join2;

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

  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;)。

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


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

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值