ORACLE笔记05.docx

ORACLE笔记05.docx

《ORACLE笔记05.docx》由会员分享，可在线阅读，更多相关《ORACLE笔记05.docx（21页珍藏版）》请在冰豆网上搜索。

ORACLE笔记05

●oracle事务处理

先看一个问题：

基本概念：

事务是把对数据库的一系列操作看做一个整体，要么全部成功，要么全部失败，利用事务我们可以保证数据的完整性，事务具有原子性。

●事务的提交：

●保存点：

●在java程序中如何去控制事务的提交和回滚：

从smith的sal中减去10元加到allen的sal中去。

代码如下：

packagecom.test;

importjava.sql.*;

publicclassTest{

publicstaticvoidmain（String[]args）{

Connectionct=null;

try{

Stringsql1="updateempsetsal=sal-10whereename='SMITH'";

Stringsql2="updateempsetsal=sal+10whereename='ALLEN';

//怎么把多个dml语句看做一个整体，同时提交

ct=SQLHelper.getConnection（）;

//把事务设置为不自动提交

ct.setAutoCommit（false）;

PreparedStatementps1=ct.prepareStatement（sql1）;

PreparedStatementps2=ct.prepareStatement（sql2）;

ps1.executeUpdate（）;

ps2.executeUpdate（）;

//把两个操作看做是一个事务统一提交

mit（）;

}catch（Exceptione）{

e.printStackTrace（）;

//如果出现异常，可以回滚

try{

ct.rollback（）;

}catch（SQLExceptione1）{

//TODOAuto-generatedcatchblock

e1.printStackTrace（）;

}

}finally

{

//关闭资源

}

}

}

对上述代码的一个说明：

如果一个事务中，只有一个select语句，那么事务控制可以忽略，如果一个事务中有多条insert，update，delete语句，则需要考虑事务处理。

思考题：

如何把事务控制直接封装到SQLHelper这个类中去？

●事务隔离级别：

定义：

事务的隔离级别用于指定事务的隔离程度；

我们首先要搞清楚三个概念：

脏读、不可重复读、幻读。

如何在控制台设置事务控制级别：

请看幻灯片。

现在我们看看如何把oracle的事务隔离级别设置为serializable（串行化）？

oracle有三种隔离级别：

（1）、readcommited:

这是oracle缺省的事务隔离级别；保证不会脏读，但可能出现重读幻读。

（2）、serializable：

保证不出现脏读，不可重复读，幻读

（3）、readonly：

保证不出现脏读，不可重复读，幻读。

serializable与readonly的区别:

serializable可以执行dml语句

readonly不能做更新等操作，只能读。

java中如何设置隔离级别：

ct.setTransactionIsolation（Connection.TRANSATION_READ_COMMITTED）;

一般情况，作为程序员来说，不需要设置事务隔离级别，因为java默认是上面所设的级别。

●维护数据的完整性：

有三种方法：

约束，触发器，应用程序（函数，过程）。

（1）、约束：

①、notnull（非空）

②、unique（唯一）

用于指定特定列不能重复，但是可以为空。

SQL>createtableuser2（idnumber,namevarchar2（32）unique）;

Tablecreated

SQL>insertintouser2values（1,'abc'）;

1rowinserted

SQL>insertintouser2values（2,'abc'）;

insertintouser2values（2,'abc'）

ORA-00001:

违反唯一约束条件（SCOTT.SYS_C005432）

说明：

unique字段是可以为空的。

而且oracle可以为多个空值。

③、primarykey（主键）：

不但不能为空，也不能重复

SQL>createtableuser3（idnumberprimarykey,namevarchar2（32））;

Tablecreated

SQL>insertintouser3values（1,'abc'）;

1rowinserted

SQL>insertintouser3values（1,'abc1'）;

insertintouser3values（1,'abc1'）

ORA-00001:

违反唯一约束条件（SCOTT.SYS_C005433）

SQL>insertintouser3values（null,'abc1'）;

insertintouser3values（null,'abc1'）

ORA-01400:

无法将NULL插入（"SCOTT"."USER3"."ID"）

注意：

primarykey与unique的区别：

1、一个表可以有多个unique，但是只能有一个主键

2、我们每张表都应该有一个主键

3、unique列的值可以为空，但主键不可以为空

4、主键的所在列会自动的创建索引，但是unique不会自动创建索引。

④、foreignkey（外键）：

请看图解：

举例说明：

班级表：

createtableclass（idnumberprimarykey,namevarchar2（32））;

学生表：

createtablestus（idnumberprimarykey,namevarchar2（32）notnull,classidnumberreferencesclass（id））;

⑤、check（检查）:

用于强制行数据必须满足的条件

举例说明：

SQL>createtableuser4（idnumberprimarykey,salnumbercheck（sal>=1000andsal<=2000））;

Tablecreated

SQL>insertintouser4values（1,1200）;

1rowinserted

SQL>insertintouser4values（1,2200）;

insertintouser4values（1,2200）

ORA-02290:

违反检查约束条件（SCOTT.SYS_C005438）

其他用法：

createtableuser4（idnumberprimarykey,salnumbercheck（sal>=1000andsal<=2000），sexchar

（2）check（sexin（‘男’,’女’）））;

综合案例：

商品表：

createtablegoods（

goodsidnumberprimarykey,

goodNamevarchar2（36）,

unitpricenumbercheck（unitprice>0）,

categoryvarchar2（64）,

providervarchar2（64）

）;

客户表：

createtablecustomer（

customidnumberprimarykey,

namevarchar2（32）notnull,

addressvarchar2（64）,

emailvarchar2（64）unique,

sexchar

（2）default'男'check（sexin（'男','女'））,

cardidvarchar2（20）

）;

购买表：

createtablepurchase（

customeridnumberreferencescustomer（customid）,

goodsidnumberreferencesgoods（goodsid）,

numsnumbercheck（nums>=1andnums<=30）

）

如果表建好后，需要做对约束的修改，我们可以使用altertable命令

？

增加商品名不能为空

altertablegoodsmodifygoodsnamenotnull;

？

要求身份证不能重复

altertablecustomeraddconstraintuni_idunique（cardid）;

?

增加用户的住址只能是海淀，朝阳，东城，西城，通州，崇文

altertablecustomeraddconstraintch_addresscheck（addressin（'海淀','朝阳','东城','西城','通州','崇文'））;

总结下：

基本语法：

●删除约束：

基本语法：

altertable表名dropconstraint约束名；

约束名：

一个表的每个约束都对应一个名称

特别说明：

因为一张表只能有一个主键，因此在删除主键约束的时候，可以不指定约束名，可以直接使用primarykey：

altertable表名dropconstraintprimarykey;

如果有外键的值指向该主键的某个值，则不能直接删除成功，需要使用一个cascade【级联删除】：

altertable表名dropconstraintprimarykeycascade;

●列级定义和表级定义

在定义表后，直接在列后定义约束，我们称为列级定义；

把各个列定义完成后，在分别说明约束，称为表级定义，比如：

一般情况下，我们使用列级定义即可，如果遇到定义复合主键（两列或多列一起作为主键），此时我们需要表级定义。

例如：

createtableusr9（idusernumber,idgoodsnumber,numsnumber,

constraintpk_user_goodsprimarykey（iduser,idgoods）

）;

不推荐大家使用复合主键。

练习；

●序列

1、需求：

在oracle的一张表中，我们希望有一列假设类型是整型的，我们希望当我们添加一条记录的时候，该列值能够自动的增长（比如从1开始增长，每次增长1）。

在sqlserver和mysql中都可以在定义表的时候，直接给指定自增长：

sqlserver:

createtabletemp1（

idintprimarykeyidentity（1,1）,

namevarchar（36）

）;

mysql:

createtabletemp1（

idintprimarykeyauto_incrment,

namevarchar（36）

）;

②、解决之道：

oracle是利用序列（sequence）来完成的。

③、快速入门案例：

创建一个序列：

createsequencemyseq

startwith1

incrementby1//表示一次增长的步长

minvalue1

maxvalue300

cycle//cycle表示当序列增加到max值之后，从头再开始。

不希望循环可以是nocycle

nocache//nocache表示不缓存如果是【cache10】表示一次产生10个号供使用，使用缓存产生号，优点是提高效率，缺点是可能跳号。

使用：

创建一张表

createtabletest1（idnumberprimarykey,namevarchar2（32））;

insertintotest1values（myseq.nextval,'abc'）;

insertintotest1values（myseq.nextval,'ddd'）;

insertintotest1（id,name）selectmy_seq.nextval,enamefromemp;

说明：

myseq：

表示序列名字，

nextval：

是一个关键字。

④、序列的细节问题：

（1）、可以为表中的列自动产生值

（2）、由用户创建数据对象，并可以有多个用户共享：

system用户可以使用scott用户创建的序列。

假设：

如果system用户使用scott用户的序列，从哪个值开始增长？

答案是接着增长的。

比如scott用户已经使用到5，system用户使用时则从6开始增长了。

（3）、一般用于主键或唯一列（primarykey,unique）.

（4）、可以使用序列名.currval来查看当前的序列号是多少

select序列名.currvalfromdual;

（5）、如果希望去查询序列名.currval，必须先使用序列名.nextval的值，否则会出错；（6）、删除一个序列的语法：

dropsequence序列名

●索引

1、先看一个问题：

2、如何创建一个索引：

索引的分类：

1、单列索引：

createindex索引名on表名（列名）

2、复合索引：

createindex索引名on表名（列名1，列名2）

4、举例：

createtableusers2（namevarchar（32）,agenumber,emailvarchar2（32））;

createindexinx_nameonusers（name）;

5、细节：

1、在大表上建立索引才有意义

说明：

没必要，浪费资源

2、在where子句或是在连接条件上经常使用引用的列上建立索引

说明：

select*　from表名where列名=列值

３、索引的层次不要超过4层。

4、在逻辑型类型字段上，或者值得范围很小，不需要建索引。

索引的缺点：

1、建立索引会增加表的大小，越1.2倍

2、索引会影响数据插入，删除，修改的速度。

●oracle对用户和权限的管理

1、系统权限：

执行特定类型的sql语句，常用的请见幻灯片。

如何使用select语句来查询oracle的系统权限：

select*fromsystem_privilege_maporderbyname;

系统权限使用管理的案例：

创建两个用户ken,tom初始阶段他们没有任何权限，如果登录就会给出错误信息

1、创建两个用户并给定密码

createuserkenidentifiedbyken;

createusertomidentifiedbytom;

给用户ken授权

2、授予createsession和createtable权限时带withadminoption

基本用法：

grant权限名称to用户名

grantcreatesessiontokenwithadminoption;[带withadminoption可以把他得到的权限继续向别的用户授予]

grantcreatetabletokenwithadminoption;

3、授予createview时不带withadminoption

grantcreateviewtoken;

回收系统权限：

使用system用户回收ken[createsession权限]

基本语法：

revoke权限名称from用户名

revokecreatesessionfromken;

2、对象权限：

指访问其他方案对象的权利

oracle给我们提供了17种对象权限，可以通过如下指令来访问（需要是dba角色）

selectdistinctprivilegefromdba_tab_privs;

基本语法：

grant对象权限on方案.数据对象to用户[withgrantoption]

grant对象权限on方案.数据对象to角色

特别说明：

可以把权限直接赋给角色

案例说明：

1、monkey用户操作scott的emp表，则必须授予相应的对象权限

1、希望monkey可以查询scott的emp表数据，怎么操作？

使用scott或者system/sys用户来操作

grantselectonscott.emptomonkey;

2、希望monkey可以修改scott的emp表数据，怎么操作？

使用scott或者system/sys用户来操作

grantupdateonscott.emptomonkey;

3、希望monkey可以删除scott的emp表数据，怎么操作？

使用scott或者system/sys用户来操作

grantdeleteonscott.emptomonkey;

4、有没有更加简单的方法，一次性把所有权限赋给monkey呢？

使用scott或者system/sys用户来操作

grantallonscott.emptomonkey;

回收对象权限：

基本语法：

revoke对象权限名称on方案.数据对象from用户

注意：

对象的权限是级联回收。

注意：

现在我们使用scott将查询权限从blake处回收

revokeselectonempfromblake;

此时blake和jones都不能再对emp表进行查询，说明对象权限的回收是级联回收的。

●角色的管理

角色是一组权限的集合，目的是为了简化对权限的管理，从而达到简化对用户的管理。

为什么需要角色？

解决方法：

---角色

角色的分类：

1、预定义角色

oracle提供了33种预定义角色，常见的有connect，dba,resource。

？

如何知道某个角色具有哪些权限呢

select*fromdba_sys_privswheregrantee=’DBA’;

select*fromdba_sys_privswheregrantee=’CONNECT’;

select*fromdba_sys_privswheregrantee=’RESOURCE’;

//注意：

查询时，角色的名字要大写。

案例：

创建一个用户，然后赋给connect角色

createuseraaaindentifiedbyaaa;

grantCONNECTtoaaa;

如何知道某个用户具有什么样的角色？

？

select*fromdba_sys_privswheregrantee=’用户名’;用户名要大写

把某个角色赋给某个用户的基本语法：

grant角色名1to用户名[withadminoption];

2、自定义角色

oracle的设计者认为，33种预定义角色可能不能满足所有的需求，所以可以使用自定义角色来解决这个问题，这样可以让管理员更加灵活。

基本语法：

创建自定义角色有两种方式：

不带验证（常用）

createrole角色名notidentified;

带验证

createrole角色名identified密码；

删除角色：

基本语法：

droprole角色名；

createrolemyrolenotidentified;

给角色赋权限（角色可以有系统权限和对象权限）

grantcreatesessiontomyrole;

grantselectonscott.emptomyrole;

grantupdateonscott.emptomyrole;

grantdeleteonscott.emptomyrole;

这时，我们可以把myrole　这个自定义的角色赋给某个指定的用户

createuseryddidentifiedydd;

grantmyroletoydd[withadminoption];

特别强调：

角色可以包含系统权限，也可以包含对象权限。