[石家庄校区]Oracle总结

Oracle03

1.视图

1）什么是视图：

       视图是一种数据库对象，由一个或多个表导出的虚表，这个表并不是真实存在，也就是说数据并没有真正存在于视图中，而是一个真实表中的数据的引用。对视图的数据操作可以直接更改实际表中的数据。

视图将一组单表或多表联合查询到的数据结果封装到一个对象中，简化了用户处理数据时的繁琐的sql语句。视图中实际只封装了一条sql语句

2）语法：

CREATE [OR REPLACE] [FORCE] VIEW 视图名称 AS sql语句 [WITH CHECK OPTION] [WITH READ ONLY]

其中[]内的内容可以省略

OR REPLACE如果之前已经存在同名视图，将替代之前的视图

FORCE 不论sql语句中查询的表（基表）是否存在，都会创建视图，（基表可能在之后产生）

WITH CHECK OPTION 如果通过视图对象修改或插入表数据，必须符合视图定义的约束

WITH READ ONLY 只读视图，只能通过视图查询数据，不能通过视图修改数据

2.物化视图

1）什么是物化视图：

      相对于视图，视图只是封装了一条sql语句的对像，每次使用视图，都会执行一次其中的sql语句。而物化视图是将sql语句查询到的结果放入了一个新的表，并实际创建了一张表，以后每次访问直接访问这张表，而不用每次都通过执行sql语句查询原始表，提高了查询效率，降低了资源的消耗

2）语法：

CREATE METERIALIZED VIEW 视图名称 [BUILD IMMEDIATE | BUILD DEFERRED ] REFRESH [FAST|COMPLETE|FORCE]

[ ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT (next_time) ] AS sql语句

BUILD IMMEDIATE 是在创建物化视图的同时就生成数据，默认为此方式

BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。

刷新（REFRESH）：刷新方式，指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。刷新方法有三种：

FAST：增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE：对整个物化视图进行完全的刷新。FORCE：数据库在刷新时判断是否可以自动说新，如果可以就使用fast，否则采用complete。创建物化视图时默认为force方法；

刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要手动刷新物化视图（默认）。ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。

增量刷新基于物化视图的日志，刷新完成后，日志将会被清空，同时物化视图更新

3.序列

1）什么是序列：

     序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。

2）语法：

创建序列：CREATE SEQUENCE 序列名称

[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1

[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue

[{MAXVALUE n | NOMAXVALUE}] //最大值

[{MINVALUE n | NOMINVALUE}] //最小值

[{CYCLE | NOCYCLE}] //循环/不循环

[{CACHE n | NOCACHE}];//分配并存入到内存中，但需要注意的是每次放入缓存中的数量不能超过范围值

提取下一个值：select 序列名称.nextval from dual

提取当前值：select 序列名称.currval from dual

注意：刚创建的新序列，无法使用currval来获取当前值，要在使用nextval之后才能使用currval得到当前值

修改序列：ALTER SEQUENCE 序列名称 序列属性

注意：不能修改序列的开始值START WITH

4.同义词

       给指定的对象起个别名，使用起来更为方便，也更安全，同时还降低了sql语句的复杂程度。如果同义词的基对象发生了重命名或移动，只需要重新定义同义词即可使用，而基于基对象的代码程序不需要改变，可以继续使用。

如果给同义词赋予权限，等同于对其指向的基对象赋予权限，因为同义词只是一个别名，但实际上还是对基对象的操作。

1）语法：

create [public] SYNONYM 同义词名称for object;

public：加上public后，所有用户都能访问。Object要创建同义词的基对象，可以是表，视图，序列等数据库对象

5.索引

1）什么是索引：

       是加速数据存取的数据库对象。可以大大提高io的执行效率，提高数据访问性能。索引需要占据一定的存储空间，是一种树形结构，树的节点存储的是每条记录的物理地址，因而其查询效率高。

2）语法：

CREATE [UNIQUE] INDEX 索引名称 ON 表名(列名，列明，列明....) [REVERSE];

UNIQUE:唯一索引。REVERSE:反向索引，基于特定算法提高索引的查询性能。

Oracle04

1. PLSQL语法:

[declare

--声明变量

]begin

--代码逻辑

[exception

When 异常类型 then

异常处理逻辑;

When 异常类型 then

异常处理逻辑;

    .

    .

    .

End;

补充：

1）：声明变量语法（Oracle声明变量的语法和JAVA中正好相反,java中声明变量的语法是:(权限) 类型 变量名.）(在declare声明部分声明变量): 变量名 类型(长度)（这里可以是常规的方式例如:id number;类型还可以指定是表中的某一个字段的类型,语法是:变量名 表名.列名%type;类型还可以是表中的一条记录,这种情况下,这个变量会存储一条记录,记录中的字段值,可以使用 变量名.列名获得）.

2）：变量赋值语法(在代码逻辑部分给变量赋值): 1。常规方式给变量直接赋值:变量名:=变量值.2。使用从表中查询出来的数据给变量赋值语法(通过这种方式给变量赋值时,从表中返回的数据必须是一条记录,多条记录或者没有记录都会报错):select 列名（如果对应的变量表示的是一整条记录,那么要使用”*”来代替列名,进而获取到整条记录,将整条记录赋值给变量） into 变量名 from 表名 where 条件.

2. 条件判断逻辑:

1) if 条件 then

业务逻辑

End if;

2) if 条件 then

业务逻辑;

Elsif 条件 then

业务逻辑;

Else

业务逻辑;

End if;

3)if 条件 then

业务逻辑;

Elsif 条件 then

业务逻辑;

Else

业务逻辑;

End if;

3. 循环

1) 无条件循环:

loop

循环语句;

End loop;

2) 条件循环

While 条件

Loop

循环语句;

End loop;

3) For循环

For 变量 in 起始值..终止值

Loop

循环语句;

End loop;

4. 游标

1） 定义：

   游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们可以把游标理解为 PL/SQL 中的结果集。

2）声明游标:  在声明区声明游标 cursor 游标名称 is SQL 语句;

3）使用游标:

open 游标名称

loop

fetch 游标名称 into 变量

exit when 游标名称%notfound

end loop;

close 游标名称

4）使用方法示例:

declare

v_pricetable T_PRICETABLE%rowtype;-- 价格行对象

cursor cur_pricetable is select * from T_PRICETABLE where

ownertypeid=1;-- 定义游标

begin

open cur_pricetable;-- 打开游标

loop

fetch cur_pricetable into v_pricetable;-- 提取游标到变量

exit when cur_pricetable%notfound;-- 当游标到最后一行下面退出循环

dbms_output.put_line( '价格:'

||v_pricetable.price ||'吨位：

'||v_pricetable.minnum||'-'||v_pricetable.maxnum );

end loop;

close cur_pricetable;-- 关闭游标

end ;

5. 存储函数

1）定义 ：

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。

2）语法结构:

      CREATE [ OR REPLACE ] FUNCTION 函数名称

（参数名称 参数类型, 参数名称 参数类型, ...）

RETURN 结果变量数据类型

IS

变量声明部分;

BEGIN

逻辑部分;

RETURN 结果变量;

[EXCEPTION

异常处理部分]

END;

3）使用案例:

create function fn_getaddress(v_id number)

return varchar2

is

v_name varchar2(30);

begin

select name into v_name from t_address where id=v_id;

return v_name;

end;

测试语句:

select fn_getaddress(3) from dual;

6. 存储过程

1）定义：

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

2）创建存储过程语法结构:

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称

（参数名 类型, 参数名 类型, 参数名 类型）

IS|AS

变量声明部分;

BEGIN

逻辑部分

[EXCEPTION

异常处理部分]

END;

    3）使用方式示例

create or replace procedure pro_owners_add

(

v_name varchar2,

v_addressid number,

v_housenumber varchar2,

v_watermeter varchar2,

v_type number

)

is

begin

insert into T_OWNERS

values( seq_owners.nextval,v_name,v_addressid,v_housenumb

er,v_watermeter,sysdate,v_type );

commit;

end;

测试语句:

call pro_owners_add('赵伟',1,'999-3','132-7',1);

1) 注意:

参数只指定类型，不指定长度

过程参数的三种模式：

IN 传入参数（默认）

OUT 传出参数 ，主要用于返回程序运行结果

IN OUT 传入传出参数

7. 触发器

1）定义：

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

触发器可用于 数据确认实施复杂的安全性检查，做审计，跟踪表上所做的数据操作等数据的备份和同步

触发器分类

l 前置触发器（BEFORE）

l 后置触发器（AFTER）

2）创建触发器语法:

CREATE [or REPLACE] TRIGGER 触发器名

BEFORE | AFTER

[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]

ON 表名

[FOR EACH ROW ][WHEN(条件) ]

declare

……

begin

PLSQL 块

End ；

[石家庄校区]Oracle总结

Oracle03

1.视图

1）什么是视图：

       视图是一种数据库对象，由一个或多个表导出的虚表，这个表并不是真实存在，也就是说数据并没有真正存在于视图中，而是一个真实表中的数据的引用。对视图的数据操作可以直接更改实际表中的数据。

视图将一组单表或多表联合查询到的数据结果封装到一个对象中，简化了用户处理数据时的繁琐的sql语句。视图中实际只封装了一条sql语句

2）语法：

CREATE [OR REPLACE] [FORCE] VIEW 视图名称 AS sql语句 [WITH CHECK OPTION] [WITH READ ONLY]

其中[]内的内容可以省略

OR REPLACE如果之前已经存在同名视图，将替代之前的视图

FORCE 不论sql语句中查询的表（基表）是否存在，都会创建视图，（基表可能在之后产生）

WITH CHECK OPTION 如果通过视图对象修改或插入表数据，必须符合视图定义的约束

WITH READ ONLY 只读视图，只能通过视图查询数据，不能通过视图修改数据

2.物化视图

1）什么是物化视图：

      相对于视图，视图只是封装了一条sql语句的对像，每次使用视图，都会执行一次其中的sql语句。而物化视图是将sql语句查询到的结果放入了一个新的表，并实际创建了一张表，以后每次访问直接访问这张表，而不用每次都通过执行sql语句查询原始表，提高了查询效率，降低了资源的消耗

2）语法：

CREATE METERIALIZED VIEW 视图名称 [BUILD IMMEDIATE | BUILD DEFERRED ] REFRESH [FAST|COMPLETE|FORCE]

[ ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT (next_time) ] AS sql语句

BUILD IMMEDIATE 是在创建物化视图的同时就生成数据，默认为此方式

BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。

刷新（REFRESH）：刷新方式，指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。刷新方法有三种：

FAST：增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE：对整个物化视图进行完全的刷新。FORCE：数据库在刷新时判断是否可以自动说新，如果可以就使用fast，否则采用complete。创建物化视图时默认为force方法；

刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要手动刷新物化视图（默认）。ON COMMIT 指在基表发生 COMMIT 操作时自动刷新。

增量刷新基于物化视图的日志，刷新完成后，日志将会被清空，同时物化视图更新

3.序列

1）什么是序列：

     序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象。

2）语法：

创建序列：CREATE SEQUENCE 序列名称

[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1

[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue

[{MAXVALUE n | NOMAXVALUE}] //最大值

[{MINVALUE n | NOMINVALUE}] //最小值

[{CYCLE | NOCYCLE}] //循环/不循环

[{CACHE n | NOCACHE}];//分配并存入到内存中，但需要注意的是每次放入缓存中的数量不能超过范围值

提取下一个值：select 序列名称.nextval from dual

提取当前值：select 序列名称.currval from dual

注意：刚创建的新序列，无法使用currval来获取当前值，要在使用nextval之后才能使用currval得到当前值

修改序列：ALTER SEQUENCE 序列名称 序列属性

注意：不能修改序列的开始值START WITH

4.同义词

       给指定的对象起个别名，使用起来更为方便，也更安全，同时还降低了sql语句的复杂程度。如果同义词的基对象发生了重命名或移动，只需要重新定义同义词即可使用，而基于基对象的代码程序不需要改变，可以继续使用。

如果给同义词赋予权限，等同于对其指向的基对象赋予权限，因为同义词只是一个别名，但实际上还是对基对象的操作。

1）语法：

create [public] SYNONYM 同义词名称for object;

public：加上public后，所有用户都能访问。Object要创建同义词的基对象，可以是表，视图，序列等数据库对象

5.索引

1）什么是索引：

       是加速数据存取的数据库对象。可以大大提高io的执行效率，提高数据访问性能。索引需要占据一定的存储空间，是一种树形结构，树的节点存储的是每条记录的物理地址，因而其查询效率高。

2）语法：

CREATE [UNIQUE] INDEX 索引名称 ON 表名(列名，列明，列明....) [REVERSE];

UNIQUE:唯一索引。REVERSE:反向索引，基于特定算法提高索引的查询性能。

Oracle04

1. PLSQL语法:

[declare

--声明变量

]begin

--代码逻辑

[exception

When 异常类型 then

异常处理逻辑;

When 异常类型 then

异常处理逻辑;

    .

    .

    .

End;

补充：

1）：声明变量语法（Oracle声明变量的语法和JAVA中正好相反,java中声明变量的语法是:(权限) 类型 变量名.）(在declare声明部分声明变量): 变量名 类型(长度)（这里可以是常规的方式例如:id number;类型还可以指定是表中的某一个字段的类型,语法是:变量名 表名.列名%type;类型还可以是表中的一条记录,这种情况下,这个变量会存储一条记录,记录中的字段值,可以使用 变量名.列名获得）.

2）：变量赋值语法(在代码逻辑部分给变量赋值): 1。常规方式给变量直接赋值:变量名:=变量值.2。使用从表中查询出来的数据给变量赋值语法(通过这种方式给变量赋值时,从表中返回的数据必须是一条记录,多条记录或者没有记录都会报错):select 列名（如果对应的变量表示的是一整条记录,那么要使用”*”来代替列名,进而获取到整条记录,将整条记录赋值给变量） into 变量名 from 表名 where 条件.

2. 条件判断逻辑:

1) if 条件 then

业务逻辑

End if;

2) if 条件 then

业务逻辑;

Elsif 条件 then

业务逻辑;

Else

业务逻辑;

End if;

3)if 条件 then

业务逻辑;

Elsif 条件 then

业务逻辑;

Else

业务逻辑;

End if;

3. 循环

1) 无条件循环:

loop

循环语句;

End loop;

2) 条件循环

While 条件

Loop

循环语句;

End loop;

3) For循环

For 变量 in 起始值..终止值

Loop

循环语句;

End loop;

4. 游标

1） 定义：

   游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们可以把游标理解为 PL/SQL 中的结果集。

2）声明游标:  在声明区声明游标 cursor 游标名称 is SQL 语句;

3）使用游标:

open 游标名称

loop

fetch 游标名称 into 变量

exit when 游标名称%notfound

end loop;

close 游标名称

4）使用方法示例:

declare

v_pricetable T_PRICETABLE%rowtype;-- 价格行对象

cursor cur_pricetable is select * from T_PRICETABLE where

ownertypeid=1;-- 定义游标

begin

open cur_pricetable;-- 打开游标

loop

fetch cur_pricetable into v_pricetable;-- 提取游标到变量

exit when cur_pricetable%notfound;-- 当游标到最后一行下面退出循环

dbms_output.put_line( '价格:'

||v_pricetable.price ||'吨位：

'||v_pricetable.minnum||'-'||v_pricetable.maxnum );

end loop;

close cur_pricetable;-- 关闭游标

end ;

5. 存储函数

1）定义 ：

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。

2）语法结构:

      CREATE [ OR REPLACE ] FUNCTION 函数名称

（参数名称 参数类型, 参数名称 参数类型, ...）

RETURN 结果变量数据类型

IS

变量声明部分;

BEGIN

逻辑部分;

RETURN 结果变量;

[EXCEPTION

异常处理部分]

END;

3）使用案例:

create function fn_getaddress(v_id number)

return varchar2

is

v_name varchar2(30);

begin

select name into v_name from t_address where id=v_id;

return v_name;

end;

测试语句:

select fn_getaddress(3) from dual;

6. 存储过程

1）定义：

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

2）创建存储过程语法结构:

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称

（参数名 类型, 参数名 类型, 参数名 类型）

IS|AS

变量声明部分;

BEGIN

逻辑部分

[EXCEPTION

异常处理部分]

END;

    3）使用方式示例

create or replace procedure pro_owners_add

(

v_name varchar2,

v_addressid number,

v_housenumber varchar2,

v_watermeter varchar2,

v_type number

)

is

begin

insert into T_OWNERS

values( seq_owners.nextval,v_name,v_addressid,v_housenumb

er,v_watermeter,sysdate,v_type );

commit;

end;

测试语句:

call pro_owners_add('赵伟',1,'999-3','132-7',1);

1) 注意:

参数只指定类型，不指定长度

过程参数的三种模式：

IN 传入参数（默认）

OUT 传出参数 ，主要用于返回程序运行结果

IN OUT 传入传出参数

7. 触发器

1）定义：

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

触发器可用于 数据确认实施复杂的安全性检查，做审计，跟踪表上所做的数据操作等数据的备份和同步

触发器分类

l 前置触发器（BEFORE）

l 后置触发器（AFTER）

2）创建触发器语法:

CREATE [or REPLACE] TRIGGER 触发器名

BEFORE | AFTER

[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]

ON 表名

[FOR EACH ROW ][WHEN(条件) ]

declare

……

begin

PLSQL 块

End ；