Oracle PL/SQL 编程手册，SQL大全

fromemp

orderbyename;

列出有超过7个周边国家的国家名字和面积。

selectnation,area

fromnation

wherecodein

(selectnation_code

fromborder

groupbynation_code

havingcount(*)>7);

列出所有面积大于等于日本的岛国的国名和人口。

selectnation,population

fromnation,border

wherecode=nation_code(+)

andnation_codeisnull

andarea>=

(selectarea

fromnation

whereupper(nation)='JAPAN');

列出所有边界在其它国家中的国名，并且显示其边界国家名字。

breakonnation

selectnation1.nation,

nation2.nationborderin_country

fromnationnation1,border,nationnation2

wherenation1.code=border.nation_code

andborder.border_code=nation2.code

orderbynation1.nation;

-----------

-----------

PL/SQL

2PL/SQL的块结构和数据类型

块结构的特点

嵌套

begin

......

begin

......

exception

......

end;

exception

......

end;

标识符：

不能超过30个字符

第一个字符必须为字母

其余字符可以是字母，数字，$，_，或#

不区分大小写形式

如果用双引号括起来，则字符顺序可以任意排列，并区分大小写形式

无SQL保留字

数据类型

数字型：

整数，实数，以及指数

字符串：

用单引号括起来

若在字符串表示单引号，则使用两个单引号

字符串长度为零（两个单引号之间没有字符），则表示NULL

字符：

长度为1的字符串

数据定义

语法

标识符[常数>数据类型[NOTNULL>[:=PL/SQL表达式>;

':='表示给变量赋值

数据类型包括

数字型number(7,2)

字符型char(120)

日期型date

布尔型boolean（取值为true,false或null，不存贮在数据库中）

日期型

anniversarydate:='05-JUL-95';

project_completiondate;

布尔型

over_budgetbooleannotnull:=false;

availableboolean;

（初始值为NULL）

%type类型匹配

books_printednumber(6);

books_soldbook_printed%type;

manager_nameemp.ename%type;

变量赋值

变量名:=PL/SQL表达式

numvar:=5;

boolvar:=true;

datevar:='11-JUN-87';

字符型、数字型表达式中的空值

null+<数字>=null（空值加数字仍是空值）

null><数字>=null（空值与数字进行比较，结果仍是空值）

null｜｜'字符串'='字符串'（null即''）

（空值与字符串进行连接运算，结果为原字符串）

变量作用范围

标识符在宣言它的块中有效

标识符如果不在子块中重新定义，则在PL/SQL块的所有子块中同样有效

重新定义后的标识符，作用范围仅在本子块中有效

例

declare

e_messchar(80);

begin

/*子块1*/

declare

v1number(4);

begin

selectempnointov1fromemp

wherejob='president';

exception

whentoo_many_rowsthen

insertintojob_errors

values('morethanonepresident');

end;

/*子块2*/

declare

v1number(4);

begin

selectempnointov1fromemp

wherejob='manager';

exception

whentoo_many_rowsthen

insertintojob_errors

values('morethanonemanager');

end;

exception

whenothersthen

e_mess:=substr(sqlerrm,1,80);

insertintogeneralerrorsvalues(e_mess);

end;

---------

3SQL和PL/SQL

插入

declare

my_salnumber(7,2):=3040.55;

my_enamechar(25):='wanda';

my_hiredatedate:='08-SEP-88';

begin

insertintoemp

(empno,enmae,job,hiredate,sal,deptno)

values(2741,my_ename,'cabdriver',my_hiredate,my_sal,20);

end;

删除

declare

bad_child_typechar(20):='naughty';

begin

deletefromsantas_gift_listwhere

kid_rating=bad_child_type;

end;

事务处理

commit[WORK>;

rollback[WORK>;

（关键字WORK可选，但对命令执行无任何影响）

savepoint标记名;（保存当前点）

在事务中标记当前点

rollback[WORK>to[SAVEPOINT>标记名;（回退到当前保存点）

取消savepoint命令之后的所有对数据库的修改

关键字WORK和SAVEPOINT为可选项，对命令执行无任何影响

函数

PL/SQL块中可以使用SQL命令的所有函数

insertintophonebook(lastname)value(upper(my_lastname));

selectavg(sal)intoavg_salfromemp;

对于非SQL命令，可使用大多数个体函数

不能使用聚组函数和参数个数不定的函数，如

x:=sqrt(y);

lastname:=upper(lastname);

age_diff:=months_between(birthday1,birthday2)/12;

赋值时的数据类型转换

4种赋值形式:

变量名:=表达式

insertinto基表名values(表达式1,表达式2,...);

update基表名set列名=表达式;

select列名into变量名from...;

数据类型间能进行转换的有:

char转成number

number转成char

char转成date

date转成char

例

char_var:=nm_var;

数字型转换成字符型

date_var:='25-DEC-88';

字符型转换成日期型

insertinto表名(num_col)values('604badnumber');

错误，无法成功地转换数据类型

---------

4条件控制

例

declare

num_jobsnumber(4);

begin

selectcount(*)intonum_jobsfromauditions

whereactoryes';

ifnum_jobs>100then

updateactorsetactor_rating='wordclass'

whereactorid=&&actor_id;

elsifnum_job=75then

updateactorsetactor_rating='daytimesoaps'

whereactorid=&&actor_id;

else

updateactorsetactor_rating='waiter'

whereactorid=&&actor_id;

endif;

endif;

commit;

end;

--------

5循环

语法

loop

......

endloop;

exit;（退出循环）

exit[when>;（退出循环，当满足WHEN时）

例1

declare

ctrnumber(3):=0;

begin

loop

insertintotable1values('tastesgreat');

insertintotable2values('lessfilling');

ctr:=ctr+1;

exitwhenctr=100;

endloop;

end;

（注：如果ctr取为NULL，循环无法结束）

例2

FOR语法

for变量<范围>loop

......

endloop;

declare

my_indexchar(20):='fettucinialfredo';

bowlchar(20);

begin

formy_indexinreverse21..30loop

insertintotemp(coll)values(my_index);

/*循环次数从30到21*/

endloop;

bowl:=my_index;

end;

跟在inreverse后面的数字必须是从小到大的顺序，必须是整数，不能是变量或表达式

----------

6游标

显式游标

打开游标

open<游标名>

例

opencolor_cur;

游标属性

%notfound

%found

%rowcount

%isopen

例

fetchmy_curintomy_var;

whilemy_cur%foundloop

(处理数据)

fetchmy_curintomy_var;

exitwhenmy_cur%rowcount=10;

endloop;

%notfound属性

取值情况如下：

fetch操作没有返回记录，则取值为true

fetch操作返回一条记录，则取值为false

对游标无fetch操作时，取值为null

<游标名>%notfound

例

ifcolor_cur%notfoundthen...

注：如果没有fetch操作，则<游标名>%notfound将导致出错，

因为%notfound的初始值为NULL。

关闭游标

close<游标名>

例

closecolor_cur;

游标的FOR循环

语法

for<记录名>in<游标名>loop

<一组命令>

endloop;

其中：

索引是建立在每条记录的值之上的

记录名不必声明

每个值对应的是记录名，列名

初始化游标指打开游标

活动集合中的记录自动完成FETCH操作

退出循环，关闭游标

隐式游标

隐式游标是指SQL命令中用到的，没有明确定义的游标

insert,update,delete,select语句中不必明确定义游标

调用格式为SQL%

存贮有关最新一条SQL命令的处理信息

隐式游标的属性

隐式游标有四个属性

SQL%NOTFOUND

SQL%FOUND

SQL%ROWCOUNT：隐式游标包括的记录数

例：

deletefrombaseball_teamwherebatting_avg<100;

ifsql%rowcount>5thn

insertintotemp

values('yourteamneedshelp');

endif;

SQL%ISOPEN：取值总为FALSE。SQL命令执行完毕，PL/SQL立即关闭隐式游标。

---------

7标号

GOTO语句

用法：

gotoyou_are_here;

其中you_are_here是要跳转的语句标号

标号必须在同一组命令，或是同一块中使用

正确的使用

<>（标号）

x:=x+1

ifa>bthen

b:=b+c;

gotodinner;

endif;

错误的使用

gotojail;

ifa>bthen

b:=b+c;

<>（标号）

x:=x+1;

endif;

标号：解决意义模糊

标号可用于定义列值的变量

<>

declare

deptnonumber:=20;

begin

updateempsetsal=sal*1.1

wheredeptno=sample.deptno;

commit;

endsample;

如果不用标号和标号限制符，这条命令将修改每条记录。

----------

8异常处理

预定义的异常情况

任何ORACLE错误都将自动产生一个异常信息

一些异常情况已命名，如：

no_data_found当SELECT语句无返回记录时产生

too_many_rows没有定义游标，而SELECT语句返回多条记录时产生

whenevernotfound无对应的记录

用户定义的异常情况

由用户自己获取

在DECLARE部分定义：

declare

xnumber;

something_isnt_rightexception;

用户定义的异常情况遵循一般的作用范围规则

条件满足时，获取异常情况：raisesomething_isnt_right

注意：同样可以获取预定义的异常情况

exception_init语句

允许为ORACLE错误命名

调用格式：

pragmaexception_init(<表达式>,);

例

declare

deadlock_detectedexception;

pragmaexception_init(deadlock_detected,-60);

raise语句

单独使用RAISE命令，可再一次获取当前的异常情况（就象异常情况被重复处理了一样）。

在异常处理中，此语句只能单独使用。

异常处理标识符

一组用于处理异常情况的语句：

exception

when<表达式>or[表达式...>then

<一组语句>

...

whenothersthen--最后一个处理

<一组语句>

end;既结束PL/SQL块部分，也结束异常处理部分

--------

练习与答案

1:

接收contract_no和item_no值，在inventory表中查找，如果产品：

已发货，在arrival_date中赋值为今天后的7天

已订货，在arrival_date中赋值为今天后的一个月

既无订货又无发货，则在arrival_date中赋值为今天后的两个月，

并在order表中增加一条新的订单记录。

product_status的列值为'shipped'和'ordered'

inventory:

product_idnumber(6)

product_descriptionchar(30)

product_statuschar(20)

std_shipping_qtynumber(3)

contract_item:

contract_nonumber(12)

item_nonumber(6)

arrival_datedate

order:

order_idnumber(6)

product_idnumber(6)

qtynumber(3)

答案：

declare

i_product_idinventory.product_id%type;

i_product_descriptioninventory.product_description%type;

i_product_statusinventory.product_status%type;

i_std_shipping_qtyinventory.std_shipping_qty%type;

begin

selectproduct_id,product_description,product_status,std_shipping_qty

intoi_product_id,i_product_description,

i_product_status,i_std_shipping_qty

frominventory

whereproduct_id=(

selectproduct_id

fromcontract_item

wherecontract_no=&&contractnoanditem_no=&&itemno);

ifi_product_status='shipped'then

updatecontract_item

setarrival_date=sysdate+7

whereitem_no=&&itemnoandcontract_no=&&contractno;

elsifi_product_status='ordered'then

updatecontract_item

setarrival_date=add_months(sysdate,1)

whereitem_no=&&itemnoandcontract_no=&&contractno;

else

updatecontract_item

setarrival_date=add_months(sysdate,2)

whereitem_no=&&itemnoandcontract_no=&&contractno;

insertintoorders

values(100,i_product_id,i_std_shipping_qty);

endif;

endif;

commit;

end;

2:

1.找出指定部门中的所有雇员

2.用带'&'的变量提示用户输入部门编号

3.把雇员姓名及工资存入prnttable表中，基结构为：

createtableprnttable

(seqnumber(7),linechar(80));

4.异常情况为，部门中奖金不为空值的雇员信息才能存入prnttable表中。

答案：

declare

cursoremp_curis

selectename,sal,comm

fromempwheredeptno=&dno;

emp_recemp_cur%rowtype;

null_commissionexception;

begin

openemp_cur;

fetchemp_curintoemp_rec;

while(emp_cur%found)loop

ifemp_rec.commisnullthen

begin

closeemp_cur;

raisenull_commission;

end;

endif;

fetchemp_curintoemp_rec;

endloop;

closeemp_sur;

exception

whennull_commissionthen

openemp_cur;

fetchemp_curintoemp_rec;

while(emp_cur%found)loop

ifemp_rec.commisnotnullthen

insertintotempvalues(emp_rec.sal,emp_rec.ename);

endif;

fetchemp_curintoemp_rec;

endloop;

closeemp_cur;

commit;

end;





Java研究组织-版权所有2002-2002

RE:ORACLE数据库对象与用户管理（转）

作者：UB时间：2003-08-14 21:06:59[修改][回复][删除]

ORACLE数据库对象与用户管理

一、ORACLE数据库的模式对象的管理与维护

本节的主要内容是关于ORACLE数据库的模式对象的管理与维护，这些模式对象包括：表空间、表、视图、索引、序列、同义词、聚集和完整性约束。对于每一个模式对象，首先描述了它的定义，说明了它的功能，最后以基于SQL语言的实例说明如何对它们进行管理于维护。

1.1表空间

由于表空间是包含这些模式对象的逻辑空间，有必要先对它进行维护。

创建表空间

SQL>CREATETABLESPACEjxzy

>DATAFILE‘/usr/oracle/dbs/jxzy.dbf’

>ONLINE;

修改表空间

SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;

SQL>ALTERTABLESPACEjxzy

>RENAMEDATAFILE‘/usr/oracle/dbs/jxzy.dbf’

>TO‘/usr/oracle/dbs/jxzynew.dbf’

>ONLINE

SQL>CREATETABLESPACEjxzyONLINE

删除表空间

SQL>DROPTABLESPACEjxzy

>INCLUDINGCONTENTS

1.2表维护

表是数据库中数据存储的基本单位，一个表包含若干列，每列具有列名、类型、长度等。

表的建立

SQL>CREATETABLEjxzy.switch(

>OFFICE_NUMNUMBER(3,0)NOTNULL,

>SWITCH_CODENUMBER(8,0)NOTNULL,

>SWITCH_NAMEVARCHAR2(20)NOTNULL);

表的修改

SQL>ALTERTABLEjxzy.switch

>ADD(DESCVARCHAR2(30));

表的删除

SQL>DROPTABLEjxzy.switch

>CASCADECONSTRAINTS

//删除引用该表的其它表的完整性约束

1.3视图维护

视图是由一个或若干基表产生的数据集合，但视图不占存储空间。建立视图可以保护数据安全（仅让用户查询修改可以看见的一些行列）、简化查询操作、保护数据的独立性。

视图的建立

SQL>CREATEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_numASpath,

poleASdevice_numFROMpole

>UNION

>SELECTpipe_path_numASpath,

>wellASdevice_numFROMwell);

视图的替换

SQL>REPLACEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_numASpath,

poleASsupport_deviceFROMpole

>UNION

>SELECTpipe_path_numASpath,

wellASsupport_deviceFROMwell);

视图的删除

SQL>DROPVIEWjxzy.pole_well_view;

1.4序列维护

序列是由序列发生器生成的唯一的整数。

序列的建立

SQL>CREATESEQUENCEjxzy.sequence_cable

>STARTWITH1

>INCREMENTBY1

>NO_MAXVALUE;

建立了一个序列，jxzy.sequence_cable.currval返回当前值，jxzy.sequence_cable.nextval返回当前值加1后的新值

序列的修改

SQL>ALTERSEQUENCEjxzy.sequence_cable

>STARTWITH1//起点不能修改，若修改，应先删除，然后重新定义

>INCTEMENTBY2

>MAXVALUE1000;

序列的删除

SQL>DROPSEQUENCEjxzy.sequence_cable

1.5索引维护

索引是与表相关的一种结构，它是为了提高数据的检索速度而建立的。因此，为了提高表上的索引速度，可在表上建立一个或多个索引，一个索引可建立在一个或几个列上。

对查询型的表，建立多个索引会大大提高查询速度，对更新型的表，如果索引过多，会增大开销。

索引分唯一索引和非唯一索引

索引的建立

SQL>CREATEINDEXjxzy.idx_switch

>ONswitch(switch_name)

>TABLESPACEjxzy;

索引的修改

SQL>ALTERINDEXjxzy.idx_switch

>ONswitch(office_num,switch_name)

>TABLESPACEjxzy;

索引的删除

SQL>DROPINDEXjxzy.idx_switch;

1.6完整性约束管理

数据库数据的完整性指数据的正确性和相容性。数据完整型检查防止数据库中存在不符合语义的数据。

完整性约束是对表的列定义一组规则说明方法。ORACLE提供如下的完整性约束.

a.NOTNULL非空

b.UNIQUE唯一关键字

c.PRIMATYKEY主键一个表只能有一个，非空

d.FOREIGAKEY外键

e.CHECK表的每一行对指定条件必须是true或未知（对于空值）

例如：

某列定义非空约束

SQL>ALTERTABLEoffice_organization

>MODIFY(descVARCHAR2(20)

>CONSTRAINTnn_descNOTNULL)

某列定义唯一关键字

SQL>ALTERTABLEoffice_organization

>MODIFY(office_nameVATCHAR2(20)

>CONSTRAINTuq_officenameUNIQUE)

定义主键约束，主键要求非空

SQL>CREATETABLEswitch(switch_codeNUMBER(8)

>CONSTRAINTpk_switchcodePRIMARYKEY,)

使主键约束无效

SQL>ALTERTABLEswitchDISABLEPRIMARYKEY

定义外键

SQL>CREATETABLEPOLE(pole_codeNUMBER(8),

>office_numnumber(3)

>CONSTRAINTfk_officenum

>REFERENCESoffice_organization(office_num)

>ONDELETECASCADE);

定义检查

SQL>CREATETABLEoffice_organization(

>office_numNUMBER(3),

>CONSTRAINTcheck_officenum

>CHECK(office_numBETWEEN10AND99);

二、ORACLE数据库用户与权限管理

ORACLE是多用户系统，它允许许多用户共享系统资源。为了保证数据库系统的安全，数据库管理系统配置了良好的安全机制。

2.1ORACLE数据库安全策略

建立系统级的安全保证

系统级特权是通过授予用户系统级的权利来实现，系统级的权利（系统特权）包括：建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户，也可以随时回收。ORACLE系统特权有80多种。

建立对象级的安全保证

对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作（查询、增、删改）的权利来实现。

建立用户级的安全保证

用户级安全保障通过用户口令和角色机制（一组权利）来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组，为每个用户建立角色，然后把角色分配给用户，具有同样角色的用户有相同的特权。

2.2用户管理

ORACLE用户管理的内容主要包括用户的建立、修改和删除

用户的建立

SQL>CREATEUSERjxzy

>IDENTIFIEDBYjxzy_password

>DEFAULTTABLESPACEsystem

>QUATA5MONsystem;//供用户使用的最大空间限额

用户的修改

SQL>CREATEUSERjxzy

>IDENTIFIEDBYjxzy_pw

>QUATA10MONsystem;

删除用户及其所建对象

SQL>DROPUSERjxzyCASCADE;//同时删除其建立的实体

2.3系统特权管理与控制

ORACLE提供了80多种系统特权，其中每一个系统特权允许用户执行一个或一类数据库操作。

授予系统特权

SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER

>TOjxzy_new

>WITHADMINOPTION;

回收系统特权

SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER

>FROMjxzy_new

//但没有级联回收功能

显示已被授予的系统特权（某用户的系统级特权）

SQL>SELECT*FROMsys.dba_sys_privs

2.4对象特权管理与控制

ORACLE对象特权指用户在指定的表上进行特殊操作的权利。这些特殊操作包括增、删、改、查看、执行（存储过程）、引用（其它表字段作为外键）、索引等。

授予对象特权

SQL>GRANTSELECT,INSERT(office_num,office_name),

>UPDATE(desc)ONoffice_organization

>TOnew_adminidtrator

>WITHGRANTOPTION;

//级联授权

SQL>GRANTALLONoffice_organization

>TOnew_administrator

回收对象特权

SQL>REVOKEUPDATEONoffice_orgaization

>FROMnew_administrator

//有级联回收功能

SQL>REVOKEALLONoffice_organization

>FROMnew_administrator

显示已被授予的全部对象特权

SQL>SELECT*FROMsys.dba_tab_privs

2.5角色的管理

ORACLE的角色是命名的相关特权组（包括系统特权与对象特权），ORACLE用它来简化特权管理，可把它授予用户或其它角色。

ORACLE 数据库系统预先定义了CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE五个角色。CONNECT具有创建表、视图、序列等特权；RESOURCE具有创建过程、触发器、表、序列等特权、DBA具有全部系统特权； EXP_FULL_DATABASE、IMP_FULL_DATABASE具有卸出与装入数据库的特权。

通过查询sys.dba_sys_privs可以了解每种角色拥有的权利。

授予用户角色

SQL>GRANTDBATOnew_administractor

>WITHGRANTOPTION;

作者：UB时间：2003-08-14 21:06:59[修改][回复][删除]

ORACLE数据库对象与用户管理

一、ORACLE数据库的模式对象的管理与维护

本节的主要内容是关于ORACLE数据库的模式对象的管理与维护，这些模式对象包括：表空间、表、视图、索引、序列、同义词、聚集和完整性约束。对于每一个模式对象，首先描述了它的定义，说明了它的功能，最后以基于SQL语言的实例说明如何对它们进行管理于维护。

1.1表空间

由于表空间是包含这些模式对象的逻辑空间，有必要先对它进行维护。

创建表空间

SQL>CREATETABLESPACEjxzy

>DATAFILE‘/usr/oracle/dbs/jxzy.dbf’

>ONLINE;

修改表空间

SQL>ALTERTABLESPACEjxzyOFFLINENORMAL;

SQL>ALTERTABLESPACEjxzy

>RENAMEDATAFILE‘/usr/oracle/dbs/jxzy.dbf’

>TO‘/usr/oracle/dbs/jxzynew.dbf’

>ONLINE

SQL>CREATETABLESPACEjxzyONLINE

删除表空间

SQL>DROPTABLESPACEjxzy

>INCLUDINGCONTENTS

1.2表维护

表是数据库中数据存储的基本单位，一个表包含若干列，每列具有列名、类型、长度等。

表的建立

SQL>CREATETABLEjxzy.switch(

>OFFICE_NUMNUMBER(3,0)NOTNULL,

>SWITCH_CODENUMBER(8,0)NOTNULL,

>SWITCH_NAMEVARCHAR2(20)NOTNULL);

表的修改

SQL>ALTERTABLEjxzy.switch

>ADD(DESCVARCHAR2(30));

表的删除

SQL>DROPTABLEjxzy.switch

>CASCADECONSTRAINTS

//删除引用该表的其它表的完整性约束

1.3视图维护

视图是由一个或若干基表产生的数据集合，但视图不占存储空间。建立视图可以保护数据安全（仅让用户查询修改可以看见的一些行列）、简化查询操作、保护数据的独立性。

视图的建立

SQL>CREATEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_numASpath,

poleASdevice_numFROMpole

>UNION

>SELECTpipe_path_numASpath,

>wellASdevice_numFROMwell);

视图的替换

SQL>REPLACEVIEWjxzy.pole_well_viewAS

>(SELECTpole_path_numASpath,

poleASsupport_deviceFROMpole

>UNION

>SELECTpipe_path_numASpath,

wellASsupport_deviceFROMwell);

视图的删除

SQL>DROPVIEWjxzy.pole_well_view;

1.4序列维护

序列是由序列发生器生成的唯一的整数。

序列的建立

SQL>CREATESEQUENCEjxzy.sequence_cable

>STARTWITH1

>INCREMENTBY1

>NO_MAXVALUE;

建立了一个序列，jxzy.sequence_cable.currval返回当前值，jxzy.sequence_cable.nextval返回当前值加1后的新值

序列的修改

SQL>ALTERSEQUENCEjxzy.sequence_cable

>STARTWITH1//起点不能修改，若修改，应先删除，然后重新定义

>INCTEMENTBY2

>MAXVALUE1000;

序列的删除

SQL>DROPSEQUENCEjxzy.sequence_cable

1.5索引维护

索引是与表相关的一种结构，它是为了提高数据的检索速度而建立的。因此，为了提高表上的索引速度，可在表上建立一个或多个索引，一个索引可建立在一个或几个列上。

对查询型的表，建立多个索引会大大提高查询速度，对更新型的表，如果索引过多，会增大开销。

索引分唯一索引和非唯一索引

索引的建立

SQL>CREATEINDEXjxzy.idx_switch

>ONswitch(switch_name)

>TABLESPACEjxzy;

索引的修改

SQL>ALTERINDEXjxzy.idx_switch

>ONswitch(office_num,switch_name)

>TABLESPACEjxzy;

索引的删除

SQL>DROPINDEXjxzy.idx_switch;

1.6完整性约束管理

数据库数据的完整性指数据的正确性和相容性。数据完整型检查防止数据库中存在不符合语义的数据。

完整性约束是对表的列定义一组规则说明方法。ORACLE提供如下的完整性约束.

a.NOTNULL非空

b.UNIQUE唯一关键字

c.PRIMATYKEY主键一个表只能有一个，非空

d.FOREIGAKEY外键

e.CHECK表的每一行对指定条件必须是true或未知（对于空值）

例如：

某列定义非空约束

SQL>ALTERTABLEoffice_organization

>MODIFY(descVARCHAR2(20)

>CONSTRAINTnn_descNOTNULL)

某列定义唯一关键字

SQL>ALTERTABLEoffice_organization

>MODIFY(office_nameVATCHAR2(20)

>CONSTRAINTuq_officenameUNIQUE)

定义主键约束，主键要求非空

SQL>CREATETABLEswitch(switch_codeNUMBER(8)

>CONSTRAINTpk_switchcodePRIMARYKEY,)

使主键约束无效

SQL>ALTERTABLEswitchDISABLEPRIMARYKEY

定义外键

SQL>CREATETABLEPOLE(pole_codeNUMBER(8),

>office_numnumber(3)

>CONSTRAINTfk_officenum

>REFERENCESoffice_organization(office_num)

>ONDELETECASCADE);

定义检查

SQL>CREATETABLEoffice_organization(

>office_numNUMBER(3),

>CONSTRAINTcheck_officenum

>CHECK(office_numBETWEEN10AND99);

二、ORACLE数据库用户与权限管理

ORACLE是多用户系统，它允许许多用户共享系统资源。为了保证数据库系统的安全，数据库管理系统配置了良好的安全机制。

2.1ORACLE数据库安全策略

建立系统级的安全保证

系统级特权是通过授予用户系统级的权利来实现，系统级的权利（系统特权）包括：建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户，也可以随时回收。ORACLE系统特权有80多种。

建立对象级的安全保证

对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作（查询、增、删改）的权利来实现。

建立用户级的安全保证

用户级安全保障通过用户口令和角色机制（一组权利）来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组，为每个用户建立角色，然后把角色分配给用户，具有同样角色的用户有相同的特权。

2.2用户管理

ORACLE用户管理的内容主要包括用户的建立、修改和删除

用户的建立

SQL>CREATEUSERjxzy

>IDENTIFIEDBYjxzy_password

>DEFAULTTABLESPACEsystem

>QUATA5MONsystem;//供用户使用的最大空间限额

用户的修改

SQL>CREATEUSERjxzy

>IDENTIFIEDBYjxzy_pw

>QUATA10MONsystem;

删除用户及其所建对象

SQL>DROPUSERjxzyCASCADE;//同时删除其建立的实体

2.3系统特权管理与控制

ORACLE提供了80多种系统特权，其中每一个系统特权允许用户执行一个或一类数据库操作。

授予系统特权

SQL>GRANTCREATEUSER,ALTERUSER,DROPUSER

>TOjxzy_new

>WITHADMINOPTION;

回收系统特权

SQL>REVOKECREATEUSER,ALTERUSER,DROPUSER

>FROMjxzy_new

//但没有级联回收功能

显示已被授予的系统特权（某用户的系统级特权）

SQL>SELECT*FROMsys.dba_sys_privs

2.4对象特权管理与控制

ORACLE对象特权指用户在指定的表上进行特殊操作的权利。这些特殊操作包括增、删、改、查看、执行（存储过程）、引用（其它表字段作为外键）、索引等。

授予对象特权

SQL>GRANTSELECT,INSERT(office_num,office_name),

>UPDATE(desc)ONoffice_organization