Oracle事务和对象详解
一、Oracle事务 ·事务的含义：事务是业务上的一个逻辑单元，为了保证数据的所有操作要么全部完成，要么全部失败。 1、事务的开始是从一条SQL语句开始，结束于下面的几种情况： 1）显示提交：输入commit指令，事务完成提交 2）显示回滚：输入rollback指令，未提交的事务丢掉，回滚到事务开始时的状态。 3）DDL语句：即create、drop等语句，这些语句会使事务自动隐式提交 4）结束程序：输入exit退出数据库，则自动提交事务；或者意外终止、出现程序崩溃，则事务自动回滚。 2、事务的特点-ACID特性 1）原则性：要么同时成功，要么同时失败的原则 2）一致性：如，a转账给b，最总结果a+b的金钱总数是不变的 3）隔离性：当出现多个事务出现，它们之间是互相隔离、互不影响的 4）持久性：事务一旦提交，则数据永久修改。 3、关于事务的三个命令 commit ：立即提交事务 rollback ：回滚事务 set autocommit on/off ：设置/关闭自动提交 二、索引 ·索引是Oracle的一个对象，是与表关联的可选结构，用于加快查询速度，提高检索性能。 1、特点 1）适当使用索引可以提高查询速度、建立索引的数量无限制 2）可以对表的一列或者多列建立索引 3）索引是需要磁盘空间，可以指定表空间存储索引。 4）是否使用索引有Oracle决定 2、索引的分类 B树索引：从顶部为根，逐渐向下一级展开 唯一索引：定义索引的列没有任何重复 非唯一索引：与唯一索引相反 反向键索引：对与数字列作用较大，会将1234生成4321进行查询的索引 位图索引：应用于数据仓库和决策支持系统中。优点是相对于b树索引，可以减少响应时间；相对于其他索引，其空间占用少。 函数索引：使用函数涉及正在创建索引的列的索引 3、创建索引 操作时我们可以使用Oracle的scott用户进行测试，首先解锁，在改一个密码，登陆进去就可以操作了
create [unique] index i_name on t_name(c_list) [tablespace ts_name]; 注释： create ... index ... on 创建索引 unique 唯一索引 i_name 创建的索引名称 t_name 在哪个表创建 (c_list) 列名 ts_name 表空间名 举例： create index emp_ename_idx on emp(ename); ：b树索引 create unique index emp_ename_unique_idx on emp(ename); ：唯一索引 create index emp_ename_reverse_idx on emp(ename) reverse; ：反向索引 create index emp_ename_upper_idx on emp(upper(ename)); ：函数索引 4、创建索引的原则 1）频繁检索的列 2）经常排序、分组的列 3）主键/外键 4）大型索引使用nologing子句创建 5）需定期进行组织索引、碎片整理 5、维护索引的命令 1）重建索引 alter index i_table rebuild [tablespace ts_name]; 2）合并索引碎片 alter index i_name coalesce; 3）删除索引 drop index i_name; 4）查看索引（使用user_indexes、user_ind_columns字典） select index_name,index_tyep,table_name,column_name from user_indexes;或者 select index_name,table_name,_column_name from user_ind_columns where index_name like ‘emp%’; 三、视图 ·视图是一个虚表，不占用物理空间，视图本身的定义语句存放于字典里，可以由一个或者多个表中获得数据。目的是为了方便查询，而不需要在敲繁琐的查询语句。 1、视图的作用 1）通过限制对表中预定的一组行和列进行查看，可以防止用户看到无权限数据，提供了安全性 2）简化了用户的命令、隐藏了数据的复杂性，方便操作 3）视图可以对列进行重命名，提升了数据库的灵活性和人性化 4）视图将应用程序与基表定义的修改进行了隔离，表结构的修改不会影响视图结构。 2、创建视图 create [or replace] [force] view vname [(alias[,alias]...)] as SQL [with check option] [with read only]; 注释： or replace ：覆盖 force ：强制的；视图中的基表是否存在，都会创建 vname ：创建的视图名称 [(alias[,alias]...)] ：可选项，视图别名，可以由多个 SQL ：SQL查询语句 with check option ：用于更改视图时的约束 with read only ：只读 举例： create view vname as select from tname where ‘a=b’; 简单的视图 create view vname as select from tname where ‘a=b’ with read only; 只读视图 create force view vname as select from tname where ‘a=b’; 强制创建视图，假如‘tname’表不存在，也强制创建，也就是错误视图。 create table tname (a (char)); alter view vname compile; 查看错误视图（使用字典） show errors view; show 创建tname表；然后编译错误视图，使其生效 3、在视图中使用DML语句（insert、update、delete）的限制 1）DML语句只能修改视图中的一个基表 2）如果修改违反了check option约束，则无法修改 3）如果视图包括连接运算符、DISTINCT运算符、集合运算符、聚合函数和group by子句，则无法更新视图 4）如果包含伪列、表达式，也无法更新视图 4、维护视图 1）删除视图 drop view vname; 2）查询已有视图(user_views字典) select viewname from user_views; 5、物化视图 ·顾名思义，物化视图就是将视图物理化存到磁盘，它与基表实时同步。物化视图可以避免order by子句或者多表连接查询带来的效率降低和时耗，但是会占用用磁盘空间。 1）物化视图同步的刷新方式可分为： on commit：即提交事务以后 on demand：简单讲就是用户手动刷新 2）物化视图同步的刷新类型 complete：完全刷新 fast：追求速度，增量刷新 force：Oracle自动判断使用complete还是fast类型刷新 never：从不刷新 ·关于以上四个类型，Oracle默认选择force刷新。工作中无特殊需求，无需更改 6、创建物化视图 1）首先用sys登陆，为scott用户授权
2）创建物化视图日志
3）正式创建
注释： build immediate ：立即创建 refresh fast ：刷新类型为fast enable query rewrite ：启用查询重写 4）删除物化视图 **drop materialized view materview;***
更多详情见请继续阅读下一页的精彩内容： https://www.linuxidc.com/Linux/2018-11/155179p2.htm
一、Oracle的序列 ·序列是用来生成惟一的连续的整数的数据库对象。序列通常可以用来自动生成主键或者唯一键的值；可根据数值进行升序或者降序的排列 ·比如创建班级的学生信息登记表，可以将学号使用序列自动生成。班级的学号都是10000~10060这个范围，每登记一个学生，序列自动增长一个。 1、创建序列的命令语法 create sequence seq_name [start with interger] [increment by interger] [maxvalue interger ] [minvalue interger] [cycle|nocycle] [cache interger|nocache]; 注释： create sequence seq_name ：创建序列 start with ：从...开始生成序列 increment by ：每次增长的数值 maxvalue ：最大的序列值，默认不限制最大值 minvalue ：最小的序列值，默认不限制最小值 cycle ：翻译为中文是圆的意思，这里其实是指循环，当序列到达最大值/最小值时，从初始值开始继续生成。默认为nocycle，不进行循环生成。 cache ：预先分配出来的序列，保存于缓存中，可用于快速访问序列号。默认为nocache，即不分配缓存 举例： create sequence seq_1 start with 10000 increment by 1 maxvalue 10060 nocycle; ：还是上面的例子，创建一个seq_1序列，从10000开始，每次增长为1，最大值为10060，不进行循环生成。 2、我们建立好了序列自然需要将序列应用到我们创建的表（table）中。可以通过nextval、currval伪列来访问该序列的值。 ·nextval：这个伪列会在创建序列后第一次使用时，返回该序列的初始值。在以后使用时，将使用increment by子句的值来增加序列值 ·currval：会返回序列的最后一次引用nextval时返回的值。 举例： create table student_class25(stu_id number not null,stu_name varchar2(20), stu_age number); 创建一个student_class25表 insert into student_class25 values (seq_1.nextval,’zhangsan’,’13’); insert into student_class25 values (seq_1.nextval,’lisi’,’13’); ... 向student_class25表中插入数据，在对应stu_id这一列我们引用序列seq_1。 select seq_1.currval from dual; 查询序列当前值 ##如果在你指定缓存的环境下，执行startup force重启数据库，接着写的数据的序列号会从初始值+缓存值开始。例如，初始值为10，缓存值为30个，那么执行startup force后，序列号会从40开始生成。 3、维护序列 1）查看序列（使用字典user_sequences） select from user_sequences; 2）删除序列 drop sequence seq_name; 3）更改序列 alter sequence seq_name [...]; 二、Oracle的同义词 ·顾名思义，同义词就是意思相同的词语，你可以看做是对象的一个别名，就像liunx操作系统中的命令的别名一样。但是linux命令别名只提供了方便容易记忆的好处。 1、而在Oracle中，同义词可用来： 1）简化Oracle中输入的SQL语句，通过给对象建立同义词，我们可以简化复杂的SQL语句，方便记忆和操作 2）隐藏对象的名称和所有者，给对象建立了同义词，如scott模式下的emp表（scott,emp），我们建立一个同义词就叫做se。那么我们在查询这个表的时候就不用担心暴露表的位置，提供了一定的安全性 3）提供对数据库的公共访问，可以通过给远程连接建立公有同义词给其他的用户查看。 4）以及为分布式数据库的远程对象提供位置透明性，我们给数据库链接（将其看做是一个对象）建立同义词，使用户可以无阻碍的查看数据表，这时候对于用户而言，位置就是透明的。 2、同义词分类（两种） 1）私有同义词：为当前模式的用户私有，其他用户无法使用 2）公有同义词：与私有同义词相反 3、创建同义词 create [or replace] [public] synonym sy_name for object_name; 注释： create ... for ：给...创键同义词 [or replace] ：该可选项的作用是覆盖（如果存在同名的同义词就会将其覆盖掉） [public] ：加上该选项表示创建公有同义词 sy_name ：同义词名 object_name ：对象名称，如scott.emp（即scott用户下的emp表） 4、举例 create synonym sy_name for scott.emp; 为scott.emp表创建私有同义词 create public synonym sy_name for scott.emp; 创建公有同义词 ##创建好的公有同义词不代表用户就可以使用，我们还需要对需要操作这个表的用户进行授权 5、复杂应用 1）建立oracle连接的命令 举例： ①conn system/pwd123@neworcl; ②create database link link_1 connect system identified by pwd123 using orcl; ③create synonym sy_t for scott.emp@link_1; 通过以上命令建立一条连接到远程数据库的数据库链接，并为链接中数据库的scott.emp表建立同义词。 6、维护同义词 1）查看同义词 select from user_synonyms; 2）删除公有/私有同义词 drop [public] synonym sy_name; 三、数据库链接 1、数据库链接是定义一个数据库到另一个数据库路径的对象，可以通过数据库链接查询远程表、执行远程程序。在分布式环境中，Oracle数据库链接是必须的。有一点，oracle数据库链接是单向的；一个链接，能从a数据库链接到b数据库，那么无法从b数据库链接到a数据库。 2、数据库链接分类 private：属于创建该链接的用户，也只有该用户可以使用 public：属于public，允许本地数据库中所有拥有数据库访问权限的用户使用 global：全集类型，网络中的数据库用户均可使用 3、创建数据库链接 create database link connect to username identified by user passwd using ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dbtest) ) )’; 四、分区表 ·Oracle可将表的行分成多个部分（存储于不同的表空间），这些部分组成的表就是分区表，这些部分被称为Oracle分区。而分区表的应用一般来说，表的大小要足够大才可以体现分区表的作用，太小的表不建议使用。 1、分区表有很大的作用： 1）改善标的查询性能；同时实现分开管理 2）便于备份和恢复，如果一部分的数据丢失，只需要单独恢复对应分区即可 3）分开存储数据，提高安全性 4）分区对于用户而言也是透明的 2、创建分区表 create table t_name (stu_id number,stu_name varchar2(20),stu_age number) partition by range (stu_id) ( partition p1 values less than (), partition p2 values less than (), parti... );
：
一、Oracle事务 ·事务的含义：事务是业务上的一个逻辑单元，为了保证数据的所有操作要么全部完成，要么全部失败。 1、事务的开始是从一条SQL语句开始，结束于下面的几种情况： 1）显示提交：输入commit指令，事务完成提交 2）显示回滚：输入rollback指令，未提交的事务丢掉，回滚到事务开始时的状态。 3）DDL语句：即create、drop等语句，这些语句会使事务自动隐式提交 4）结束程序：输入exit退出数据库，则自动提交事务；或者意外终止、出现程序崩溃，则事务自动回滚。 2、事务的特点-ACID特性 1）原则性：要么同时成功，要么同时失败的原则 2）一致性：如，a转账给b，最总结果a+b的金钱总数是不变的 3）隔离性：当出现多个事务出现，它们之间是互相隔离、互不影响的 4）持久性：事务一旦提交，则数据永久修改。 3、关于事务的三个命令 commit ：立即提交事务 rollback ：回滚事务 set autocommit on/off ：设置/关闭自动提交 二、索引 ·索引是Oracle的一个对象，是与表关联的可选结构，用于加快查询速度，提高检索性能。 1、特点 1）适当使用索引可以提高查询速度、建立索引的数量无限制 2）可以对表的一列或者多列建立索引 3）索引是需要磁盘空间，可以指定表空间存储索引。 4）是否使用索引有Oracle决定 2、索引的分类 B树索引：从顶部为根，逐渐向下一级展开 唯一索引：定义索引的列没有任何重复 非唯一索引：与唯一索引相反 反向键索引：对与数字列作用较大，会将1234生成4321进行查询的索引 位图索引：应用于数据仓库和决策支持系统中。优点是相对于b树索引，可以减少响应时间；相对于其他索引，其空间占用少。 函数索引：使用函数涉及正在创建索引的列的索引 3、创建索引 操作时我们可以使用Oracle的scott用户进行测试，首先解锁，在改一个密码，登陆进去就可以操作了
create [unique] index i_name on t_name(c_list) [tablespace ts_name]; 注释： create ... index ... on 创建索引 unique 唯一索引 i_name 创建的索引名称 t_name 在哪个表创建 (c_list) 列名 ts_name 表空间名 举例： create index emp_ename_idx on emp(ename); ：b树索引 create unique index emp_ename_unique_idx on emp(ename); ：唯一索引 create index emp_ename_reverse_idx on emp(ename) reverse; ：反向索引 create index emp_ename_upper_idx on emp(upper(ename)); ：函数索引 4、创建索引的原则 1）频繁检索的列 2）经常排序、分组的列 3）主键/外键 4）大型索引使用nologing子句创建 5）需定期进行组织索引、碎片整理 5、维护索引的命令 1）重建索引 alter index i_table rebuild [tablespace ts_name]; 2）合并索引碎片 alter index i_name coalesce; 3）删除索引 drop index i_name; 4）查看索引（使用user_indexes、user_ind_columns字典） select index_name,index_tyep,table_name,column_name from user_indexes;或者 select index_name,table_name,_column_name from user_ind_columns where index_name like ‘emp%’; 三、视图 ·视图是一个虚表，不占用物理空间，视图本身的定义语句存放于字典里，可以由一个或者多个表中获得数据。目的是为了方便查询，而不需要在敲繁琐的查询语句。 1、视图的作用 1）通过限制对表中预定的一组行和列进行查看，可以防止用户看到无权限数据，提供了安全性 2）简化了用户的命令、隐藏了数据的复杂性，方便操作 3）视图可以对列进行重命名，提升了数据库的灵活性和人性化 4）视图将应用程序与基表定义的修改进行了隔离，表结构的修改不会影响视图结构。 2、创建视图 create [or replace] [force] view vname [(alias[,alias]...)] as SQL [with check option] [with read only]; 注释： or replace ：覆盖 force ：强制的；视图中的基表是否存在，都会创建 vname ：创建的视图名称 [(alias[,alias]...)] ：可选项，视图别名，可以由多个 SQL ：SQL查询语句 with check option ：用于更改视图时的约束 with read only ：只读 举例： create view vname as select from tname where ‘a=b’; 简单的视图 create view vname as select from tname where ‘a=b’ with read only; 只读视图 create force view vname as select from tname where ‘a=b’; 强制创建视图，假如‘tname’表不存在，也强制创建，也就是错误视图。 create table tname (a (char)); alter view vname compile; 查看错误视图（使用字典） show errors view; show 创建tname表；然后编译错误视图，使其生效 3、在视图中使用DML语句（insert、update、delete）的限制 1）DML语句只能修改视图中的一个基表 2）如果修改违反了check option约束，则无法修改 3）如果视图包括连接运算符、DISTINCT运算符、集合运算符、聚合函数和group by子句，则无法更新视图 4）如果包含伪列、表达式，也无法更新视图 4、维护视图 1）删除视图 drop view vname; 2）查询已有视图(user_views字典) select viewname from user_views; 5、物化视图 ·顾名思义，物化视图就是将视图物理化存到磁盘，它与基表实时同步。物化视图可以避免order by子句或者多表连接查询带来的效率降低和时耗，但是会占用用磁盘空间。 1）物化视图同步的刷新方式可分为： on commit：即提交事务以后 on demand：简单讲就是用户手动刷新 2）物化视图同步的刷新类型 complete：完全刷新 fast：追求速度，增量刷新 force：Oracle自动判断使用complete还是fast类型刷新 never：从不刷新 ·关于以上四个类型，Oracle默认选择force刷新。工作中无特殊需求，无需更改 6、创建物化视图 1）首先用sys登陆，为scott用户授权
2）创建物化视图日志
3）正式创建
注释： build immediate ：立即创建 refresh fast ：刷新类型为fast enable query rewrite ：启用查询重写 4）删除物化视图 **drop materialized view materview;***
腾讯云开发者
