1．下列不属于ORACLE的逻辑结构的是（C）
2.下面哪个用户不是ORACLE缺省安装后就存在的用户(A)
A.SYSDBA
B.SYSTEM
C.SCOTT
D.SYS
3下面哪个操作会导致用户连接到ORACLE数据库，但不能创建表(A)
A.汇总
B.分析
C分组、
D单行
A.FORINSERT
B.FORUPDATE
C.FORDELETE
D.FORREFRESH
7.在Oracle中，当FETCH语句从游标获得数据时，下面叙述正确的是（C）。
8.在Oracle中，下面关于函数描述正确的是（AD）。
9.阅读下面的PL/SQL程序块：
BEGIN
INSERTINTOemployee(salary,last_name,first_name)
VALUES(35000,’Wang’,'Fred’);
SAVEPOINTsave_a;
VALUES(40000,’Woo’,'David’);
SAVEPOINTsave_b;
DELETEFROMemployeeWHEREdept_no=10;
SAVEPOINTsave_c;
VALUES(25000,’Lee’,'Bert’);
ROLLBACKTOSAVEPOINTsave_c;
VALUES(32000,’Chung’,'Mike’);
ROLLBACKTOSAVEPOINTsave_b;
COMMIT;
END;
运行上面的程序，哪两个更改永久保存到数据库（CD）。
10.在Oracle中，表VENDOR包含以下列：
VENDOR_IDNUMBERPrimaryKey
NAMEVARCHAR2(30)
LOCATION_IDNUMBER
ORDER_DTDATE
ORDER_AMOUNTNUMBER(8,2)
下面对表VENDOR运用分组函数的子句合法的是（C）。
11.在Oracle中，表EMP包含以下列：
……
NAMEVARCHAR2(20)
ADDRVARCHAR2(60)
要以NAME’saddressisADDR格式返回数据，以下SQL语句正确的是（B）。
+的两边做To_number()
12.在Oracle中，以下不属于集合操作符的是（B）。
13.在Oracle中，表分区方式（C）建议分区数是2的幂（2、4、8等），以获得最平均的数据发布。
14.在Oracle中，关于锁,下列描述不正确的是（D）。
15.在Oracle中，关于表分区下列描述不正确的是（）。
16:
在Oracl中，哪种表分区维护操作可以将非分区表转换为分区表的分区（）。
17:
在Oracle中，使用带有（）子句的SELECT命令可以在表的一行或多行上放置排他锁。
18:
在Oracle中，序列venseq使用下面的语句创建：
CREATESEQUENCEvenseqINCREMENTBY1STARTWITH10
MAXVALUE100MINVALUE10CYCLECACHE5;
下面对序列venseq修改的语句，错误的是（A）。
19:
在Oracle中，使用以下语句创建视图：
CREATEORREPLACEVIEWpen_view
ASSELECT*FROMorder_master
WHEREostatus=‘p’WITHCHECKOPTIONCONSTRAINTpenv;
当用户试图执行以下语句:
UPDATEpen_viewSETostatus=‘d’WHEREostatus=’p’;
下列描述正确的是（B）。
20:
在Oracle中，在以下情形建立位图索引可以获得较好的性能（C）。
21:
在Oracle中，通过命令（C）可以释放锁。
22
在Oracle中，下面关于簇的描述不正确的是（B）。
23:
在Oracle中，抽象数据类型address_ty定义如下：
CREATETYPEaddress_tyASOBJECT
(street_nonumber(3),
street_namevarchar2(20),
cityvarchar2(20),
statevarchar2(20))
/
表vend_mast定义如下：
CREATETABLEvend_mast
(vencodevarchar2(5),vennamevarchar2(15),
venaddaddress_ty,tel_nonumber(10));
下列语句能正确执行的是（）。
WHEREvenname=’john’;
WHEREstreet_no=11;
24:
在Oracle中，（）数据字典表可以用来查看抽象数据类型的实际结构。
25:
在Oracle中，（B）操作符允许引用现有的行对象。
26:
在Oracle中，关于PL/SQL下列描述正确的是（C）。
27:
在Oracle中，阅读下列PL/SQL块：
DECLARE
v_lowerNUMBER:=2;
v_upperNUMBER:=100;
v_countNUMBER:=1;
I=2;I<=2;i++
FORiINv_lower..v_lowerLOOP
INSERTINTOtest(results)
VALUES(v_count);
v_count:=v_count+1;
ENDLOOP;
请问FORLOOP循环体执行了（A）次。
28:
在Oracle中，（）程序包中提供了所有预定义的异常的定义。
29:
在Oracle中，关于子程序的描述不正确的是（C）。
子程序就是存储过程和函数
30:
在Oracle中，当从SQL表达式调用函数时，下列描述不正确的是（AC）。
31:
在Oracle中，关于触发器的描述正确的是（D）。
32:
在Oracle中，你需要创建索引提高薪水审查的性能，该审查要对员工薪水提高12个百分点后进行分析处理，下面哪个createindex命令能解决此问题（A）。
33:
在Oracle中，执行下面的语句：
SELECTceil(-97.342),
floor(-97.342),-123.01-124
round(-97.342),
trunc(-97.342)
FROMdual;
哪个函数的返回值不等于-97（B）。
34:
在Oracle中，用以下SQL命令创建了一个序列：
CREATESEQUENCEmy_seq
STARTWITH394
INCREMENTBY12
NOMINVALUE
NOMAXVALUE
NOCYCLE
NOCACHE;
用户执行包含my_seq.NEXTVAL的SQL语句三次，然后执行包含(seq建好后的第一次my_seq.NEXTVAL值不增加还是394)
my_seq.CURRVAL的SQL语句四次，请问序列my_seq的当前值是（B）。
35:
在Oracle中，下列哪种标量类型不能保存到数据库表中（B）。
36:
在Oracle中，不属于游标属性的是（C）。
37:
在Oracle中，在SQL提示符下调用PL/SQL过程的方法是（ABC）。
38:
在Oracle中，用下列语句定义了一个过程：
CREATEORREPLACEPROCEDUREtest(aINVARCHAR2,
bINOUTNUMBER,
cOUTVARCHAR2)IS
假定使用的变量都已定义，下面对过程test的调用语法正确的是（C）。
39:
在Oracle中，关于程序包的描述不正确的是（B）。
40:
在Oracle中，用户（A）拥有所有内置的程序包。
41:
在Oracle中，DBMS_LOB程序包的（）过程用于删除指定的BLOB或CLOB。
42
在Oracle中，关于触发器的描述正确的是（D）。（选择一项）
43:
在Oracle中，通过命令（CD）可以释放锁。（选择两项）
44:
在Oracle中，事务中使用下列SQL语句不会引起锁定（A）。（选择一项）
45:
46:
在Windows操作系统中，Oracle的（A）服务器监听并接受来自客户端应用程序的连接请求。（选择一项）
47:
在Oracle中，有一个名为seq的序列对象，以下语句能返回序列值但不会引起序列值增加的是（C）。（选择一项）
48:
ORACLE中，执行语句：SELECTaddress1||’,'||address2||’,'||address2”Address”FROMemploy；将会返回（B）列。
49:
在Oracle中，INSTEADOF触发器主要用于（D）。（选择一项）
50:
Oracle数据库中，下面（C）可以作为有效的列名。
51:
在Oracle中，以下工具可以逻辑备份数据库对象或整个数据库（B）。（选择一项）
52:
Oracle数据库中，通过（B）访问能够以最快的方式访问表中的一行。
53:
在Oracle中，使用下列的语句产生序列：
CREATESEQUENCEid；
Oracle服务器会预开辟内存并维持的序列值有（C）个。（选择一项）
54:
Oracle数据库中，以下（C）命令可以删除整个表中的数据，并且无法回滚。
55:
Oralce数据库中，以下（A）函数可以针对任意数据类型进行操作。
56:
在Oracle中，语句（B）将ORDER_DATE日期值’2000年3月18日’显示为‘2000年1月1日’。（选择一项）
57:
在Oracle中，以下是STUDENTS表的结构：
SIDNUMBERNOTNULL，PrimaryKey
SNAMEVARCHAR2(30)
COURSE_IDVARCHAR2(10)NOTNULL
MARKSNUMBER
你需要查询参加了课程ID为C10的考试，并且分数排在前10名的学生，以下（D）语句能够实现此功能。
A.SELECTSID,marks,ROWNUM“Rank”
FORMstudents
WHEREROWNUM<=10ANDcourse_id=’C10′
ORDERBYmarksDESC;
B.SELECTSID,marks,ROWNUM”Rank”
ORDERBYmarks;
C.
SELECTSID,marks,ROWNUM”Rank”
FORM(SELECTSID,marks
D.
WHEREcourse_id=’C10′ORDERBYmarksDESC)
WHEREROWNUM<=10;
58:
在Oracle中,用下列语句定义一个过程:
CREATEPROCEDUREproc(value1invarchar2,value2outnumber,value3inoutvarchar2)
is
begin
end;
假定使用的变量都已定义,下面对过程proc的调用语法正确的是().(选择一项)
59:
在Oracle中,使用以下语句创建视图;
createorreplaceviewmyView
asselect*fromorders
wherestatus=’p';
假定Orders表中包含10条status=’p'的记录,当用户试图执行以下语句;
updatemyViewsetstatus=’o’wherestatus=’p';
下列正确的是()
60:
在Oracle中,用于PL/SQL程序输出调试信息的内置程序包是D
61:
CREATEPROCEDUREproc(value1INVARCHAR2,value2OUTNUMBER,value3INOUTVARCHAR2)
IS
假定使用的变量都已定义，下面对过程proc的调用语法正确的是(B)。
62:
在Oracle中，使用以下语句创建视图：C
CREATEORREPLACEVIEWMyView
ASSELECT*FROMorders
Wherestatus=’p';
假定orders表中包含10条status=’p'的记录，当用户试图执行以下语句：
UPDATEMyViewSETstatus=’o’WHEREstatus=’p';
下列描述正确的是()。
63:
在Oracle中,语句()将ORDER_DATE日期值’2000年3月18日’显示为’2000年1月1日’(选择两项)
64:
在Oracle中,使用下列语句产生序列:
CREATESEQUENCEid;
Oracle服务器会预开辟内存并维持的序列值有()个(选择一项)
65:
在Oracle中,以下工具可以逻辑备份数据库对象或整个数据库().
66:
在Oracle中,INSTEADOF触发器主要用于()
67:
在Oracle中，用于显示PL/SQL程序输出调试信息的内置程序包是()。
68:
在Oracle中，有一个名为seq的序列对象，以下语句能返回序列值但不会引起序列值增加的是()。
69:
在Oracle中，事务中使用下列SQL语句不会引起锁定()。
70.
在Oracle中，通过命令()可以释放锁。
71
在Oracle中，关于触发器的描述正确的是()。
72:
ORACLE中，执行语句：SELECTaddress1||’，’||address2||’，’||address2“Adress”FROMemploy；将会返回（）列。（选择一项）
73:
在Oracle中，PL/SQL块中定义了一个带参数的游标：
CURSORemp_cursor(dnumNUMBER)IS
SELECTsal,commFORMempWHEREdeptno=dnum;
那么正确打开此游标的语句是(AD)。
74:
Oracle数据库中，下面（）可以作为有效的列名。（选择一项）
Oracle数据库中，通过（B）访问能够以最快的方式访问表中的一行。（选择一项）
76:
Oracle数据库中，以下()函数可以针对任意数据类型进行操作。
77:
Oracle数据库中，以下（）命令可以删除整个表中的数据，并且无法回滚。（选择一项）
78:
在Oracle中，下列(B)语句不能用于控制游标。
79:
在Oracle中有表”cd”，它包含属性”cdcode”，”category”和”cdname”，要查询category取值为”CLASSIC”或”ROCK”的行，应采用语句(AD)。
80:
在Oracle中，在执行SQL语句时，你不小心使用Update命令将所有的ID值设置成了11111，那么使用(C)命令可以取消这一操作。
81
在Oracle中，数据库中的触发器是一个对关联表发出insert、update或（A）语句时触发的存储过程。（选择一项）
Adelete
Bdrop
Ccreate
Dtruncate
82
在Oracle中，使用了如下的语句创建用户TOM，则对于该用户而言，以下说法错误的是（D）。（选择一项）CREATEUSERTOMIDENTIFIEDBYTOMSYS
A该用户的口令为TOMSYS
BTOM默认为表空间为SYSTEM
CTOM的临时表空间为TEMP
D使ORANTUPDATE命令可以修改TOM的口令
83
在Oracle中，下述（A）命令会使挂起的事务完成。（选择一项）。（选择一项）
ACOMMIT
BDELETE
CUPDATE
DSELECT
e)INSERT
84
游标变量的类型是(B)
A隐式游标
B显示游标
CREF游标
D循环游标
85
在非归档方式下操作的数据库禁用了（A）。（选择一项）
A归档日志。
B联机日志。
C日志写入程序。
D日志文件。
86
由于软硬件问题导致的读写数据库文件失败，属于（D）故障。（选择一项）
A实例
B语句
C用户进程
D介质
87
(C)参数用于确定是否要导入整个导出文件
Aconstranints
Btables
Cfull
Dfile
88
在oracle程序中处理语句时发生的逻辑错误导致(C)故障
B介质
C语句
D用户进程
89
以下哪种备份方式需要在完全关闭数据库后进行。
A无归档日志模式。
B归档日志模式。
C使用导出实用程序进行逻辑备份。
D以上都不对。
90
(B)方式的导出会从指定的表中导出所有的数据。（选择一项）
A分区
B表
C全部数据库
D表空间
91
使用（B）命令可查看在创建触发器时发生的编译错误
AViewerrors
BShowerrors
CDisplayerrors
DCheckerrors
92
(A)包用于显示pl/sql块和存储过程中的调试信息
ADbms_output。
BDbms_standadr。
CDbms_Input。
DDbms_session。
93
(A)触发器允许触发操作的语句访问行的列值
A行级:old:new
B语句级
C模式
D数据库级
94
要审计用户执行的create,drop,和alter等ddl语句，应创建(E)触发器
A行级
CInsteadof
D模式
e)数据库
95
Oracle内置程序包由(A)用户所有
Asys
Bsystem
Cscott
DPublic
96
(D)程序包用于读写操作系统文本文件
ADbms_output
BDbms_lob(图片，电影)
CDbms_random
DUtl_file
97
以下不属于命令的pl/sql块的是()
A程序包
B过程。
C游标。
D函数
98
执行特定任务的子程序是(AB)
A函数
C程序包
D游标。
99
子程序的(A)模式参数可以在调用子程序时指定一个常量
Ain
Bout
CInout
Dinout
100
下面关于主事务处理和自主事务处理的说法错误的是(D)
A自主事务处理结果的变化不倚赖于主事务处理的状态
B自主事务处理提交或回退时，不影响主事务处理的结果
C自主事务处理提交，对主事务处理是不可见的
D自主事务处理还可以启动其他自主事务处理
Createprocp1
Begin
Insert…..
P2();
Update….
Commit;
End;
Createprocp2
Atuo………
Inusert….
Rollback;
13．列出在每个(每个是关键字,对此groupby)部门工作的员工数量、平均工资和平均服务期限。(经典的groupby用法)selectdeptno,count(*),avg(a.sal),avg(sysdate-HIREDATE)fromempagroupbydeptno;14．列出所有员工的姓名、部门名称和工资.(经典的两个表的连接查询，用具体的名称替换一个表中的主键的id(解决很多人在实际运用中会遇到的不能绑定多列的问题)，也可用where来查询,与题5比较)selectename,sal,(selectdnamefromdeptawherea.deptno=b.deptno)asdnamefromempb;15．列出所有部门的详细信息和部门人数。(因为是*，将显示dept和后面临时表b的全部字段(注意:不只是dept的字段,注意*号))select*fromdeptaleftjoin(selectdeptno,count(*)fromempgroupbydeptno)bona.deptno=b.deptno;16．列出各种(与每个同义(参看题13))工作的最低工资。selectjob,min(sal)fromempgroupbyjob;
17．列出各个部门的MANAGER（经理,经理唯一，不用groupby）的最低薪金。selectmin(sal)fromempwherejoblike’MANAGER’;(因为MANAGER是值不是字段，所以不能用小写)18．列出所有员工的年工资,按年薪从低到高排序。(nvl:空转化函数)selectename,(sal+nvl(comm,0))*12assalfromemporderbysal;
用户创建、修改、删除
用户创建
createuserusernameprofiledefaultidentifiedby“user_passwd”defaulttablespace
tablespace_nameaccountunlock;
修改用户密码：
alteruserusernameidentifiedbychar_name；
修改用户使用状态
alteruserusernameaccountunlock
用户赋权限：
grantconnect,resource,selectanytable,updateanytable,deleteanytable,insertany
table,select
anydictionary,createanyprocedure,executeanyprocedure,createanyTRIGGER,create
anyview,unlimitedtablespace，
dropanyview,createanysequence,selectanysequence,dropanysequence,CREATE
DATABASELINK,
CREATEPUBLICDATABASELINK,DROPPUBLICDATABASELINK,CREATE
ANYsynonym,DROPANYsynonym,
CREATEPUBLICsynonym,DROPPUBLICSYNONYM,SELECT_CATALOG_ROLE
to&u;
给某一个用户赋予某张表的某种权限：
grantprivilege_name（selectinsertupdate）table_nametousername;
例如：
grantupdateONTABLE_NAMETOusername;
grantdeleteONTABLE_NAMETOusername;
grantinsertONTABLE_NAMETOusername;
用户权限回收：
revokerole_namefromusername;
alteruserusernamedefaultroleall;
用户删除：
Dropuserusernamecascade（如果有数据，带cascade参数）
查看用户属性和状态：
select*fromdba_users;
select*fromdba_sys_privs
select*fromdba_tab_privs
权限名称：
administerdatabasetrigger
administerresourcemanager
alteranycluster
alteranydimension
alteranyevaluationcontext
alteranyindex
alteranyindextype
alteranylibrary
alteranyoutline
alteranyprocedure
alteranyrole
alteranyrule
alteranyruleset
alteranysequence
alteranysnapshot
alteranytable
alteranytrigger
alteranytype
alterdatabase
alterprofile
alterresourcecost
alterrollbacksegment
altersession
altersystem
altertablespace
alteruser
analyzeany
auditany
auditsystem
backupanytable
debuganyprocedure
debugconnectsession
deleteanytable
dequeueanyqueue
dropanycluster
dropanycontext
dropanydimension
dropanydirectory
dropanyevaluationcontext
dropanyindex
dropanyindextype
dropanylibrary
dropanyoperator
dropanyoutline
dropanyprocedure
dropanyrole
dropanyrule
dropanyruleset
dropanysequence
dropanysnapshot
dropanysynonym
dropanytable
dropanytrigger
dropanytype
dropanyview
dropprofile
droppublicdatabaselink
droppublicsynonym
droprollbacksegment
droptablespace
becomeuser
commentanytable
createanycluster
createanycontext
createanydimension
createanydirectory
createanyevaluationcontext
createanyindex
createanyindextype
createanylibrary
createanyoperator
createanyoutline
createanyprocedure
createanyrule
createanyruleset
createanysequence
createanysnapshot
createanysynonym
createanytable
createanytrigger
createanytype
createanyview
createcluster
createdatabaselink
createdimension
createevaluationcontext
createindextype
createlibrary
createoperator
createprocedure
createprofile
createpublicdatabaselink
createpublicsynonym
createrole
createrollbacksegment
createrule
createruleset
createsequence
createsession
createuser
createview
dropuser
enqueueanyqueue
executeanyevaluationcontext
executeanyindextype
executeanylibrary
executeanyoperator
executeanyprocedure
executeanyrule
executeanyruleset
executeanytype
exemptaccesspolicy
flashbackanytable
forceanytransaction
forcetransaction
globalqueryrewrite
grantanyobjectprivilege
grantanyprivilege
grantanyrole
insertanytable
lockanytable
manageanyqueue
managetablespace
oncommitrefresh
queryrewrite
restrictedsession
resumable
selectanydictionary
selectanysequence
selectanytable
underanytable
underanytype
underanyview
unlimitedtablespace
updateanytable
createsnapshot
createsynonym
createtable
createtablespace
createtrigger
createtype
2、角色创建、修改、删除
角色创建
createrolerole_name;
角色权限修改
grantprivilege_nametorole_name
revokeprivilege_namefromrole_name
角色删除
droprolerole_name
系统角色：
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUTHENTICATEDUSER
CONNECT
CTXAPP
DBA
DELETE_CATALOG_ROLE
EJBCLIENT
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
HS_ADMIN_ROLE
WKUSER
WM_ADMIN_ROLE
XDBADMIN
3、权限增加和收回
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
LOGSTDBY_ADMINISTRATOR
OEM_MONITOR
RECOVERY_CATALOG_OWNER
RESOURCE
SALES_HISTORY_ROLE
SELECT_CATALOG_ROLE
1、单表创建
直接创建
Createtabletable_name
(
（增加权限）
（收回权限）
字段1类型1，
字段2类型2，
、、、、、
字段n类型n
)tablespace_name;
创建类似表
Createtabletable_nameasselect*fromtable_name1;
创建表结构：
Createtabletable_nameasselect*fromtable_name字段1=字段2；
表查询：
Select*fromtable_name;
Select*fromtable_namewhere条件
Select字段1别名1，字段2
别名2，字段n别名nfromtable_name
字段中可以进行代数运算，包括：＋－×÷，countsunavgetrunkround等函数
表插入：
Insert字段1，字段2、、字段nintotable_namevalue(字段值1，字段值2、、字段值n)；
循环插入：
foriin1..10loop
insertintotable_namevalues(…);
endloop;
表更新：
Updatetable_nameset字段1where条件
表删除：
droptabletable_name
表记录删除：
deletetable_namewhere条件；
truncatetabletable_name清空表记录，保留表结构
表名修改：
renametable_name1totable_name2
注意：表名修改后，索引还在
表迁移，（从一个表空间迁移到另外一个表空间）
altertabletable_namemovetablespacetablespace_namenologgingparallele4;
注意：将表从一个表空间迁移到另外一个表空间，必须进行索引重建，存储过程、触发器、其
他程序包都需要编译，以免执行报错。例如：
表压缩：
不同表空间：altertabletable_namemovetablespacetablespace_namecompress;
相同表空间：altertabletable_namemovecompress;
表统计信息收集
execdbms_stats.gather_table_stats(ownname=>‘username’,tabname
=>’table_name’,degree=>10,cascade=>true,estimate_percent=>25);
execdbms_stats.gather_table_stats(ownname=>‘test’,tabname
=>’data_table1_name1′,degree=>4,cascade=>true,estimate_percent=>30);
selecta.row2_id,sum(nvl(b.item_value,0))
fromzk.name_201001a,zk.table_name_201001b
wherea.so_row1=b.so_row1anda.row_ain(1,4,5)anda.row2_id=XXXXXXXand
book_row3_idin(NNNNNXXX1,NNNNNXXX2,NNNNNXXX3,NNNNNXXX4,
NNNNNXXX5,NNNNNXXX6)groupbya.row_id;
表语句执行很慢，检查执行计划，zk.table_name_201001b不引用索引，做表分析、重建索引都无
效，执行计划中均没有引用索引，最后强制使用索引效果明显，执行计划改变，索引引用。效果明
显
select/*+index(bpk_table_name_201001)*/
a.serv_id,sum(nvl(b.item_value,0))
wherea.so_row1=b.so_row1anda.busi_codein(1,4,5)anda.row2_id=XXXXXXXand
NNNNNXXX5,NNNNNXXX6)
groupbya.row2_id;
2、分区表创建
为了使大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，
以改善大型应用系统的性能。使用分区的优点：
A、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
B、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
C、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
D、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
Oracle数据库提供对表或索引的分区方法有三种：
1、范围分区
2、Hash分区（散列分区）
3、列表分区
4、范围—散列分区
5、范围—列表分区
2.1、范围分区表
createtabletable_name
row1char1notnull,
row2char2notnull,
、、、、、、
rownnumbernotnull
)
partitionbyrange(paration_row)
partitionpartition_name1valueslessthan(to_date(‘value_name’,‘yyyy-mm-dd’,
‘nls_calendar=gregorian’))tablespacetablespace_name1,
partitionpartition_name2valueslessthan(to_date(‘value_name’,‘yyyy-mm-dd’,
‘nls_calendar=gregorian’))tablespacetablespace_name2,
、、、、、、、、、、
partitionpartition_namenvalueslessthan(to_date(‘value_name’,‘yyyy-mm-dd’,
‘nls_calendar=gregorian’))tablespacetablespace_namen,
partitionpartmaxvalueslessthan(maxvalue)
tablespaceora_data
pctfree10
initrans16
maxtrans255
storage
initial64k
minextents1
maxextentsunlimited))
Insertintotable_name1asselect*fromtable_name2
表分区删除：
altertable表名truncatepartition分区名称dropstorage;
注意：分区删除以后，需要重新创建索引。
分区分割：
altertabletable_namesplitpartitionpartmaxat(to_date(‘values_name’,‘yyyy-mm-dd’))into
(partitionprtition_namexxtablespacetablespace_name,partitionpartmax);
分区查询：
select*fromtable_namepartition(partition_name);
2.2、hash分区表（散列分区）
Hash分区表创建：hash分区表的分区数量一般是2的n次幂，这样记录分布在各个分区上就
比较均匀，可以进行I/O的均衡。
rownNUMBERnotnull
partitionbyhash(paration_row)
partitionpartition_name1tablespacetablespace_name1,
partitionpartition_name2tablespacetablespace_name2,
partitionpartition_name3tablespacetablespace_name3
partitionpartition_nameXtablespacetablespace_nameX
insertintotable_name1asselect*fromtable_name2
Select*Fromtable_namePartition(partition_name);hash分区表一般由于数据分布均衡性，查
询不是
通过对分区的操作进行的。update、insert操作同普通表。
2.3、列表分区表
散列分区表创建：
partitionbylist(paration_row)
partitionpartition_name1
values
(row_value1,row_value3,row_value8)
tablespace
tablespace_name1,
partitionpartition_name2
(row_value4,row_value6,row_value7)
tablespace_name2,
partition
partition_name3
(row_value9,row_value10,row_value11,row_value12,row_value15)
tablespacetablespace_name3
partitionpartition_nameXvalues(default)tablespacetablespace_nameX
表的插入、更新、删除和普通表相同，在hash分区和list分区中，比较困难的操作是：从一张5
千万以上的表中，要删除一千万条记录比较困，可以通过以下方法进行清理：
第一种方法：
a创建中间表和表备份，b进行数据插入，c进行锁表，d进行表名修改，e进行索引创建
和存储过程编译，f进行表truncate操作
第二种方法：直接创建job进行delete删除：这样长期做，会降低表的执行效果
a进行备份数据，b进行表记录删除，每次删除1000——2000条记录，不然会发生锁表，c重
建索引，d进行表分析。
declare
v_lognumnumber;–数据库中拥有的日志文件数
v_needarcnumber;–需要归档的日志文件数
selectcount(1)intov_lognumfromv$log;
loop
selectcount(1)intov_needarcfromv$archive;
ifv_needarc exit; endif; deletefrom 对象.table_namewhere条件1and条件2andrownum<1000; ifsql%rowcount=0then commit; ––––––——————–-–-–-–-–-–-–-–-–-–-–- createorreplaceprocedureprocedure_name(exp_date1varchar2,exp_date2varchar2) as del_sqlvarchar2(1024); v_cinteger; del_sql:=’deletefrom对象.表名awhereexists(select1from对象.表名bwhere 条件1and 条件2and、、、、 条件n）andrownum<1000′; foriin1..10000loop executeimmediatedel_sqlusingexp_date1,exp_date2; endprocedure_name; 3、分区操作 添加分区 altertabletable_partition_nameaddpartitionpartition_namevalueslessthan (to_date(‘row_values’,'yyyy-mm-dd’)); 注意：以上添加的分区界限应该高于最后一个分区界限。 altertabletable_partition_name modifypartitionpartition_name add subpartition subpartition_name values(‘row_values’); 删除分区 以下代码删除表分区： altertabletable_partition_namedroppartitionpartition_namestorage; 注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除 表 截断分区 截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表 中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区： altertabletable_partition_nametruncatepartitionpartition_name; 合并分区 合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能 将分区合并到界限较低的分区。以下代码实现了partition_name1与partition_name分区的合并： altertabletable_partition_namemergepartitionspartition_name1,partition_name2intopartition partition_name2; 拆分分区 拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分 区进行拆分。 altertabletable_partition_namesblitpartitionpartition_name1 at(to_date(‘row_value’,'yyyy-mm-dd’)) into(partitionpartition_name1,partitionpartition_name2); 接合分区(coalesca) 结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加 散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合 分区： altertabletable_hash_partition_namecoalescapartition; 重命名表分区 以下代码将partition_name1更改为partition_name2 altertabletable_partition_namerenamepartitionpartition_name1topartition_name2; 跨分区查询 selectsum(*)from (selectcount(*)cnfromtable_partition_namepartition(partition_name1) unionall selectcount(*)cnfromtable_partition_namepartition(partition_name2)); 查询表上有多少分区 select*fromuser_tab_partitionswheretable_name=’table_partition_name’ 查询索引信息 selectobject_name,object_type,tablespace_name,sum(value) fromv$segment_statistics wherestatistic_namein(‘physicalreads’,'physicalwrite’,'logicalreads’)andobject_type=’index’ groupbyobject_name,object_type,tablespace_nameorderby4desc –显示数据库所有分区表的信息： select*fromdba_part_tables –显示当前用户可访问的所有分区表信息: select*fromall_part_tables –显示当前用户所有分区表的信息： select*fromUSER_PART_TABLES –显示表分区信息显示数据库所有分区表的详细分区信息： select*fromdba_tab_partitions –显示当前用户可访问的所有分区表的详细分区信息： select*fromall_tab_partitions –显示当前用户所有分区表的详细分区信息： select*fromuser_tab_partitions –显示子分区信息显示数据库所有组合分区表的子分区信息： select*fromdba_tab_subpartitions –显示当前用户可访问的所有组合分区表的子分区信息： select*fromall_tab_subpartitions –显示当前用户所有组合分区表的子分区信息： select*fromuser_tab_subpartitions –显示分区列显示数据库所有分区表的分区列信息： select*fromdba_part_key_columns –显示当前用户可访问的所有分区表的分区列信息： select*fromall_part_key_columns –显示当前用户所有分区表的分区列信息： select*fromuser_part_key_columns –显示子分区列显示数据库所有分区表的子分区列信息： select*fromdba_subpart_key_columns –显示当前用户可访问的所有分区表的子分区列信息： select*fromall_subpart_key_columns –显示当前用户所有分区表的子分区列信息： select*fromuser_subpart_key_columns –怎样查询出oracle数据库中所有的的分区表 select*fromuser_tablesawherea.partitioned=’yes’ –删除一个表的数据是 truncatetabletable_name; –删除分区表一个分区的数据是 altertabletable_partition_nametruncatepartitionpartition_nameNdropstorage; 4、表状态查询 Select*FromDba_Tab_PartitionsWhereTable_Name=’%表名%’; Selectowner,index_name,status,degree,table_namefromdba_indexeswhere table_name=’table_name’; selectowner,bytes/1024/1024,segment_nam,segment_type,tablespace_namefromdba_segments where segment_name=’table_name’andsegment_type=’TABLE’; 5、DDL语句操作 创建表 基本语法： create[globaltemporary]tabletable_name( column_nametype[constraintconstraint_defdefaultdefault_exp] [,column_nametype[constraintconstraint_defdefaultdefault_exp]…]) [oncommit{delete|preserve}rows] tablespacetab_space; 其中： 1)globaltemporary说明改表的行都是临时的，这种表就称为临时表。 行的有效期由oncommit字句指定。临时表对于所有的会话都是可见的，但是这些行则是特定于 某个会话的。 2)table_name指定了要分配给该表的名称。 3)column_name指定了要分配给某个列的名称。 4)type指定了对某个列的类型。 5)constraint_def指定了对某个列的约束的定义。 6)default_def指定了一个表达式，用来为某个列赋予默认值。 7)oncommit控制临时表中行的有效期。delete说明这些行在事务的末尾要被删除。 preserve说明这些行在会话的末尾要被删除。若对临时表没有指定oncommit选项，那末默认值是 delete。 8)tab_space为该表指定表空间。若没有指定表空间，该表就被存储在该用户的默认表空间中。 获得有关表的信息可以通过如下操作获取有关表的信息： 对表执行describe命令。 descorder_status_temp –注意desc是SQL*PLUS命令，在SQL中不能执行。 all_tables。 selecttable_name,tablespace_name,temporary fromuser_tables wheretable_namein(‘table_name’,‘ORDER_STATUS_TEMP’); 获得表中列的信息 从user_tab_columns中可以获得有关表中各列的信息,另：通过访问all_tab_columns,可以获得有关 可以访问的表中所有列的信息。 selectcolumn_name,data_type,data_length,data_precision,data_scale fromuser_tab_columns wheretable_name=‘table_name’; 修改表 altertable语句可以用于对表进行修改。 altertable语句可以执行以下任务： 1)添加、修改、删除列； 2)添加或删除约束； 3)启用或禁用约束。 添加列 altertabletable_nameaddmodified_byinteger; altertabletable_nameaddrowname类型[datedefaultsysdatenotnull]; 修改列 1)修改列的长度，条件是该列的类型的长度可以修改，比如char或varchar2； 2)修改数字列的精度； 3)修改列的数据类型； 4)修改列的默认值。 修改列的长度 altertabletable_namemodifyrowname类型(长度); 只有在表中还没有任何行或所有列都为空值的情况下才可以减小列的长度。 修改数字列的精度 altertabletable_namemodifycolumn类型; 只有在表中还没有任何行或列为空值时才可以减小数字列的精度。 修改列的数据类型 若一个表中还没有任何行或列为空值，就可以将列修改为任何一种数据类型。否则，就只能将列的 数据类型修改为一种兼容的数据类型。 例如，可以将varchar2类型修改为char，条件是没有缩短列的长度；但是不能将date修改为 number。 修改列的默认值 altertabletable_namemodifycolumncolumn_name; 默认值只适用于新插入表中的行。 删除列 altertabletable_namedropcolumncolumn_name; 重命名表 rename语句可以用于对表进行重命名。 renametable_name1table_name2; 删除表数据，保留表结构 truncatetabletable_name 视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数 据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询 语句。视图的数据都来自于某些表，这些表被称为基表。通过视图来查看表，就像是从不同的角度 来观察一个(或多个)表。 视图有如下一些优点： a可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访 问表的全部行和列。 b简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的 检索却很简单。 c一个视图可以检索多张表的数据，因此用户通过访问一个视图，可完成对多个表的访问。 d视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访 问同一个表的不同部分。视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相 比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和 触发器在视图上将同样起作用。 1、视图创建 create[orreplace][force|noforce]viewview_name [(alias[,alias]…)] assubquery [withcheckoption[constraintconstraint]] [withreadonly] 创建视图empv，该属兔仅包含10部门雇员的细节信息。 createviewempv asselectempno,ename,job fromemp wheredeptno=10; 别名的使用 例1：createorreplaceviewsalv asselectempnoemployee_id,enamename,salsalary fromempwheredeptno=30; 例2：createorreplaceviewsalv (employee_id,name,salary) asselectempno,ename,sal wheredeptno=30; 创建复杂视图 例：创建一个基于两个表并且含有组函数的复杂视图 createorreplaceviewdept_sum_v(name,minsal,maxsal,avgsal) asselectd.dname,min(e.sal),max(e.sal),avg(e.sal) fromempe,deptdwheree.deptno=d.deptnogroupbyd.dname; 2、视图操作 查看视图 数据字典USER_VIEWS selectview_name,textfromuser_views; 从视图检索数据 select*fromsalv; 视图上执行DML操作 a)如果视图包含以下内容，则不能实现对数据的修改: -GROUP函数、GROUPBY子句、DISTINCT关键字 -使用表达式定义的列 -ROWNUM伪列 b)如果视图中包含以下内容则不能删除数据行 -GROUP函数 -GROUPBY子句 -DISTINCT关键字 WITHCHECKOPTION子句 -如果要确保在视图上执行的插入、更新操作仅限于一定的范围，便可使用WITHCHECK OPTION子句 例： createorreplaceviewempv asselect*fromempwheredeptno=20 withcheckoptionconstraintempv_ck; 测试一：updateempvsetdeptno=10whereempno=7369 结果： ERROR位于第1行: ORA-01402:视图WITHCHECKOPTIDN违反where子句 测试二：updateempvsetsal=2000whereempno=7369 结果：已更新1行。 拒绝DML操作 -在视图定义时使用WITHREADONLY选项可以确保不能对视图执行DML操作 createorreplaceviewempv(employee_id,employ_name,job_title) asselectempno,ename,jobfromempwheredeptno=10 withreadonly; 3、视图删除 删除视图并不会删除数据，因为视图是基于数据库中的基表 DROPVIEWview_name; 例：DROPVIEWempv; Oracle的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度; 索引直接指向包含所查询值的行的位置，减少磁盘I/O; 与所索引的表是相互独立的物理结构; Oracle自动使用并维护索引，插入、删除、更新表后，自动更新索引; 语法：CREATEINDEXindexONtable(column[,column]…); B-tree结构(非bitmap) 1、单表索引 索引创建： create[unique|bitmap]indexindex_nameontable_name(col_name)pctfree20 storage(initial100knext100kpctincrease tablespace_name; 0maxextents100)tablespace 索引重建： 重建索引(可以同时使用存储子句和参数,不重建时也可直接使用) alterindexindex_namerebuildtablespacetablespace_namenologgingparallel4; alterindexindex_namenoparallel; alterindexindex_namerebuildtablespacetablespace_namenologgingonline; 手动拓展索引的空间 alterindexindex_nameallocateextent; 收回未用到的空间 alterindexindex_namedeallocateunused; 索引碎片整理 alterindexindex_namecoalesce; 标识索引是否使用过 alterindexindex_namemonitoringusage; 查询: select*fromv$object_usage; 取消监控 alterindexindex_namenomonitoringusage 索引压缩： alterindexindex_namerebuildnologgingonlinetablespacetablespace_namecompress; 索引删除： dropindexindex_name 索引查看： selectowner,index_name,table_name,tablespace_name,index_type,degree,status fromdba_indexes; 索引列对照信息 selectindex_name,table_name,column_name,index_owner,table_owner fromdba_ind_columns; 索引存储信息 selectindex_name,pct_free,pct_increase,initial_extent,next_extent,min_extents, max_extentsfromdba_indexes; 2、分区表索引 局部索引 createindexindex_nameontable_name(column) local partitionpartition_name1tablespaceindex_tablespace_name1, partitionpartition_name2tablespaceindex_tablespace_name2, partitionpartition_name3tablespaceindex_tablespace_name3 全局索引 create[unique]indexindex_nameontable_name(column) globalpartitionbyrange(column) partitionpartition_name1valuelessthan(firstrangevalue)tablespace index_tablespace_name1, partitionpartition_name2valuelessthan(secondrangevalue)tablespace index_tablespace_name2, 、、、、、、、 partitionpartition_nameNvaluelessthan(maxvalue)tablespaceindex_tablespace_nameN create[unique]indexindex_nameontable_name(column,[column2]) globalpartitionbyhash(column,[column2]) 、、、、、、、、 partitionpartition_nameNtablespaceindex_tablespace_nameN alterindexindex_namerebuildtablespacetablespace_namenologgingonlineparallel4; dropindexindex_name; selectowner,index_name,table_name,tablespace_name,index_type,degree,statusfrom dba_indexes; selectindex_name,table_name,column_name,index_owner,table_ownerfrom dba_ind_columns; selectindex_name,pct_free,pct_increase,initial_extent,next_extent,min_extents,max_extents 3、主键、约束和唯一索引 唯一索引 唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都 不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此 数据。例如，如果在books_table表中的书名(book_name)列上,创建了唯一索引，则所有书不能 同名。 主键索引 主键索引是唯一索引的特殊类型,数据库表通常有一列或列组合，其值用来唯一标识表中的每一 行。该列称为表的主键。在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯 一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允 许快速访问数据。 主键和唯一索引的一些比较： (1)对于主健unique/constraintoracle自动建立唯一索引 (2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的 (3)主健可作外健，唯一索引不可， (4)主健不可为空，唯一索引可， (5)主健也可是多个字段的组合. (6)主键不同的是 a.有notnull属性b.每个表只能有一个 约束和唯一索引比较： 主键索引和主键约束的创建与删除顺序 创建主键索引–>创建主键约束删除主键约束–>删除主键索引 A分区字段不是主键的情况下,只可以创建全局分区索引,不可以创建本地主键分区索引.只有分 区字段为主键时才可以创建本地主键分区索引. B如果创建本地唯一分区索引,除指定索引字段外还要加上表分区字段.这种索引意义不大:因为 这样 成复合索引,索引改变,约束也改变了. C如果创建非唯一索引则不需要表分区字段. D创建全局分区索引后可以创建约束. 唯一索引创建： createuniqueindexindex_nameontable_name(column) partitionpartition_name1tablespaceindex_tablespace1, partitionpartition_name2tablespaceindex_tablespace2, partitionpartition_nameNtablespaceindex_tablespaceN 单表创建唯一索引：createuniqueindexindex_nameontable_name(column); 唯一索引重建： alterindexindex_namerebuildtablespacetablespace_namenologgingparallel8online 唯一索引删除： 如果有约束：先关闭约束altertabletable_name1disableconstraintconstraint_namecascade; 再drop索引：dropindexindex_name DBA查看索引分区 select*fromdba_ind_partitions USER查看索引分区 select*fromuser_ind_partitions DBA查看索引分区类型 select*fromdba_part_indexes USER查看索引分区类型 select*fromuser_part_indexes 约束创建： 在数据库中使用约束(constraints)是为了在该数据库中实施所谓的“业务规则“其实就是防止非法 信息进入数据库,满足管理员和应用开发人员所定义的规则集. ORACLE使用完整性约束(integrityconstraints)防止不合法的数据写入数据库,管理员和开发人 员可以定义完整性规则,增强商业规则,限制数据表中的数据.如果一个DML语句执行的任何结果破 坏了完整性约束,ORACLE就会回滚语句,返回错误信息. 在括号中指定,用逗号分隔.如果没有为约束提供一个名字,那么ORACLE会分配一个系统生成的唯 字. ORACLE支持五种类型的完整性约束 NOTNULL(非空)–防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许 在任何列中有NULL值. CHECK(检查)–检查在约束中指定的条件是否得到了满足. UNIQUE(唯一)–保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的. PRIMARYKEY(主键)–用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一 个主键约束. POREIGNKEY(外部键)–通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定 义的外部键可以指向主键或者其他表的唯一键. 定义约束 createtable[schema.]table (columndatatype[defaultexpr] [column_constraint], … [table_constraint][,...]); 创建约束： createtableemployees (employee_idnumber(6), first_namevarchar2(20), job_idvarchar2(10)notnull, constraintsemp_emp_id_pkprimarykey(employee_id)); 列级的约束定义 column[CONSTRAINTconstraint_name]constraint_type, 表级约束的定义 column,.. [constraintconstraint_name]constraint_type(column,…) NOTNULL约束 只能定义在列级,不能定义在表级： createtabletable_name1 (row_idnumber(6), row_timevarchar2(25)notnull, row_salarynumber(8,2), row_pctnumber(2,2), row_datedateconstraintconstraint_name1notnull); UNIQUE约束 用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE在 唯一键列上自动生成一个唯一索引以实现唯一性： row_datedateconstraintconstraint_name1UNIQUE(row_id)); PRIMARYKEY约束 唯一键的所有特征都适用于主键约束,只是在主键列中不允许有NULL值.一个表只能有一个主键： row_datedateconstraintconstraint_name1PRIMARYKEY(row_id)); foreignkey约束 用来保护一个表中的一个或者多个列,它会通过一个主键主键或者唯一键保证对于每个非NULL 值在数据库的其他地方都有一个数据可用.这个外部键就是在生成此约束的表(子表)中的一个或多 相同的表中(自引用完整性约束). row_datedatenotnull, dep_idnumber(6), constraintconstraint_name1foreignkey(row_id)referencestable_name2(dep_id), constraintconstraint_name2unique(dep_id)); 上例中是在表级定义外部键约束,如果在列级定义,不同的是: (…, dep_idnumber(4)constraintconstraint_name1referencestable_name2(dep_id), …); //没有关键字FOREIGNKEY FOREIGNKEY约束还有两个关键字是 默认情况下,如果没有指定以上两个中任一,则父表中被引用的记录将不能被删除. CHECK约束 [CONSTRAINT 这里CHECK子句中的CONDITION应该求值为一个布尔值结果,并且可以引用相同行中其他列的 值;不 能包含子查询,序列,环境函数(SYSDATE,UID,USER,USERENV)和伪列 (ROWNUM,LEVEL,CURRVAL,NEXTVAL),一个列上可以定义多个CHECK约束,如果所定义的条 件为 FALSE,则语句将回滚. CREATETABLEtable_name1 row_salNUMBER(8,2)CONSTRAINTconstraint_name1CHECK(row_sal>0), 添加约束 altertabletable_name1 ADDCONSTRAINTconstraint_nameFOREIGNKEY(rowmagr_id)REFERENCES table_name2(name_id); 删除约束 dropconstraintconstraint_name; dropprimarykeycascade; 对于NOTNULL约束,用ALTERTABLEMODIFY子句来删除 altertabletable_name1modifyrow_namenull; 关闭约束 disableconstraintconstraint_namecascade;//如果没有被引用则不需CASCADE关键字 当你生成一个约束时,约束自动打开(除非你指定了DISABLE子句,当用DISABLE关闭UNIQUE或 建立. 打开约束 altertabletable_nameenableconstraintconstraint_name; 约束信息查看： 可以从USER_CONSTRAINTS表和USER_CONS_COLUMNS视图中查询约束的信息 selectconstraint_name,constraint_type,search_condition fromuser_constraints wheretable_name=’table_name1′; 约束类型 C–CHECK和NOTNULL都算为CTYPE P–PRIMARYKEY R–REFERENTIALINTEGRITY就是外部键约束 U–UNIQUE selectconstraint_name,column_name fromuser_cons_columns 1、存储过程和函数 存储过程和函数也是一种pl/sql块，是存入数据库的pl/sql块。但存储过程和函数不 同于已经介绍过的pl/sql程序，我们通常把pl/sql程序称为无名块，而存储过程和函数 是以命名的方式存储于数据库中的。和pl/sql程序相比，存储过程有很多优点，具体归 纳如下： a）存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优 用或修改代码。 b）存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储 函数。 c）存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块， 用户编写的pl/sql程序或其他存储过程都可以调用它(但存储过程和函数不能调用pl/sql 程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表， 可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号 返回雇员的姓名，可以设计成存储函数。 d）像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递 也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通 过参数带回；函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名 的调用返回函数值。 存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。 创建存储过程，需要有createprocedure或createanyprocedure的系统权限。该权限可由 系统管理员授予。创建一个存储过程的基本语句如下： create[orreplace]procedure存储过程名[(参数[in|out|inout]数据类型…)] {as|is} [说明部分] 可执行部分 [exception 错误处理部分] end[过程名]; 其中：可选关键字orreplace表示如果存储过程已经存在，则用新的存储过程覆 盖，通常用于存储过程的重建。参数部分用于定义多个参数(如果没有参数，就可以省 略)。参数有三种形式：in、out和inout。如果没有指明参数的形式，则默认为in，关 键字as也可以写成is，后跟过程的说明部分，可以在此定义过程的局部变量，编写存 储过程可以使用任何文本编辑器或直接在sql*plus环境下进行，编写好的存储过程必须 要在sql*plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被 存入数据库。编译成功的存储过程就可以在oracle环境下进行调用了。一个存储过程 在不需要时可以删除。删除存储过程的创建者或者拥有dropanyprocedure系统权限的 人。删除存储过程的语法如下： 语法格式：dropprocedure存储过程名； 如果要重新编译一个存储过程，则只能是过程的创建者或者拥有alteranyprocedure 系统权限的人。语法如下： alterprocedure存储过程名compile； 执行(或调用)存储过程的人是过程的创建者或是拥有executeanyprocedure系统权 限的人或是被拥有者授予execute权限的人。执行的方法如下： 方法1： execute模式名.存储过程名[(参数...)]; 方法2： 模式名.存储过程名[(参数...)]; 【训练1】创建一个显示雇员总人数的存储过程。 步骤2：在sql*plus输入区中，输入以下存储过程： createorreplaceproceduredyk_count v_total1number(10); v_total2number(10); v_total3number(10); selectcount(*)intov_total1fromdyk_table1; dbms_output.put_line(‘雇员总人数为：‘||v_total1); deletedyk_table1whereownerIn(‘XDB’,‘CTSSYS’,‘OUTLN’,‘WMSYS’); selectcount(*)intov_total2fromdyk_table1; dbms_output.put_line(‘剩余总人数为：‘||v_total2); insertintodyk_table1nologging select*fromdba_objects; selectcount(*)intov_total3fromdyk_table1; dbms_output.put_line(‘增加后的总人数为：‘||v_total3); 步骤3：按“执行”按钮进行编译。 如果存在错误，就会显示:警告:创建的过程带有编译错误。 如果存在错误，对脚本进行修改，直到没有错误产生。 如果编译结果正确，将显示：（sql代码） 过程已创建。 步骤4：调用存储过程，在输入区中输入以下语句并执行： sql代码 setserverouton/从sql提示符下输出存储过程变量值 executedyk_count; 显示结果为： 雇员总人数为：337386 剩余总人数为：336456 增加后的总人数为：386254 说明：在该训练中，v_total变量是存储过程定义的局部变量，用于接收查询到的雇员总人数。 注意：在sql*plus中输入存储过程，按“执行”按钮是进行编译，不是执行存储过程。如果在存储 过程中引用了其他用户的对象，比如表，则必须有其他用户授予的对象访问权限。一个存储过程一 旦编译成功，就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行 该过程的权限。 存储过程没有参数，在调用时，直接写过程名即可。 【训练2】在pl/sql程序中调用存储过程。 grantexecuteonemp_counttostudent setserveroutputon scott.emp_count; 步骤4：执行以上程序，结果为： 雇员总人数为：14 pl/sql过程已成功完成。 用该存储过程。 注意：在程序中调用存储过程，使用了第二种语法。 【训练3】 编写显示雇员信息的存储过程emp_list，并引用emp_count存储过程。 步骤1：在sql*plus输入区中输入并编译以下存储过程： createorreplaceprocedureemp_list cursoremp_cursoris selectempno,enamefromemp; foremp_recordinemp_cursorloop dbms_output.put_line(emp_record.empno||emp_record.ename); emp_count; 步骤2：调用存储过程，在输入区中输入以下语句并执行： executeemp_list 7369smith 7499allen 7521ward 7566jones 执行结果： 说明：以上的emp_list存储过程中定义并使用了游标，用来循环显示所有雇员的信息。然后调用已 经成功编译的存储过程emp_count，用来附加显示雇员总人数。通过execute命令来执行emp_list 存储过程。 【练习1】编写显示部门信息的存储过程dept_list，要求统计出部门个数。 参数传递：参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以 大大增加存储过程的灵活性和通用性，参数的类型有三种，如下所示。 in定义一个输入参数变量，用于传递参数给存储过程 out定义一个输出参数变量，用于从存储过程获取数据 inout定义一个输入、输出参数变量，兼有以上两者的功能 参数的定义形式和作用如下： 参数名in数据类型default值； 定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可 以是常量、有值变量或表达式等。default关键字为可选项，用来设定参数的默认值。如果在调用 存储过程时不指明参数，则参数变量取默认值。在存储过程中，输入变量接收主程序传递的值，但 不能对其进行赋值。 参数名out数据类型； 定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。 在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。在存储过程中， 参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。 参数名inout数据类型default值； 定义一个输入、输出参数变量，兼有以上两者的功能。在调用存储过程时，主程序的实际参数只能 是一个变量，而不能是常量或表达式。default关键字为可选项，用来设定参数的默认值。在存储 过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中 必须给变量至少赋值一次。 如果省略in、out或inout，则默认模式是in。 【训练1】编写给雇员增加工资的存储过程change_salary，通过in类型的参数传递要增加工资的 雇员编号和增加的工资额。 步骤2：在sql*plus输入区中输入以下存储过程并执行： createorreplaceprocedurechange_salary(p_empnoinnumberdefault7788,p_raisenumberdefault 10) v_enamevarchar2(10); v_salnumber(5); selectename,salintov_ename,v_salfromempwhereempno=p_empno; updateempsetsal=sal+p_raisewhereempno=p_empno; dbms_output.put_line(‘雇员‘||v_ename||’的工资被改为‘||to_char(v_sal+p_raise)); exception whenothersthen dbms_output.put_line(‘发生错误，修改失败！‘); rollback; 步骤3：调用存储过程，在输入区中输入以下语句并执行： executechange_salary(7788,80) 雇员scott的工资被改为3080 说明：从执行结果可以看到，雇员scott的工资已由原来的3000改为3080。 参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可 以采用以下调用方法。如上例，执行语句可以改为： executechange_salary(p_raise=>80,p_empno=>7788); 可以看出传递参数的顺序发生了变化，并且明确指出了参数名和要传递的值，=>运算符左侧是参 数名，右侧是参数表达式，这种赋值方法的意义较清楚。 【练习1】创建插入雇员的存储过程insert_emp，并将雇员编号等作为参数。 在设计存储过程的时候，也可以为参数设定默认值，这样调用者就可以不传递或少传递参数了。 【训练2】 调用存储过程change_salary，不传递参数，使用默认参数值。 在sql*plus输入区中输入以下命令并执行： executechange_salary 雇员scott的工资被改为3090 说明：在存储过程的调用中没有传递参数，而是采用了默认值7788和10，即默认雇员号为7788， 增加的工资为10。 使用out类型的参数返回存储过程的结果。 步骤2：在sql*plus输入区中输入并编译以下存储过程： createorreplaceprocedureemp_count(p_totaloutnumber) selectcount(*)intop_totalfromemp; 执行结果为： 步骤3：输入以下程序并执行： v_empcountnumber; emp_count(v_empcount); dbms_output.put_line(‘雇员总人数为：‘||v_empcount); 说明：在存储过程中定义了out类型的参数p_total，在主程序调用该存储过程时，传递了参数 v_empcount。在存储过程中的select…into…语句中对p_total进行赋值，赋值结果由v_empcount 变量带回给主程序并显示。 以上程序要覆盖同名的emp_count存储过程，如果不使用orreplace选项，就会出现以下错误： error位于第1行: ora-00955:名称已由现有对象使用。 【练习2】创建存储过程，使用out类型参数获得雇员经理名。 【训练4】 createorreplaceprocedureadd_region(p_hpone_numinoutvarchar2) p_hpone_num:=’0755-’||p_hpone_num; 、 v_phone_numvarchar2(15); v_phone_num:=’26731092′; add_region(v_phone_num); 号码在原来基础上增加了区号0755和-。 创建和删除存储函数 创建函数，需要有createprocedure或createanyprocedure的系统权限。该权限可由系统管理员授 予。创建存储函数的语法和创建存储过程的类似，即 create[orreplace]function函数名[(参数[in]数据类型…)] return数据类型 return(表达式) end[函数名]; 其中，参数是可选的，但只能是in类型(in关键字可以省略)。 在定义部分的return数据类型，用来表示函数的数据类型，也就是返回值的类型，此部分不可省 略。在可执行部分的return(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说 明的函数返回值的数据类型一致。在函数的执行部分可以有多个return语句，但只有一个return 语句会被执行，一旦执行了return语句，则函数结束并返回调用环境。 一个存储函数在不需要时可以删除，但删除的人应是函数的创建者或者是拥有dropanyprocedure 系统权限的人。其语法如下： dropfunction函数名； 重新编译一个存储函数时，编译的人应是函数的创建者或者拥有alteranyprocedure系统权限 的人。重新编译一个存储函数的语法如下： alterprocedure函数名compile； 函数的调用者应是函数的创建者或拥有executeanyprocedure系统权限的人，或是被函数的拥 有者授予了函数执行权限的账户。函数的引用和存储过程不同，函数要出现在程序体中，可以参加 表达式的运算或单独出现在表达式中，其形式如下： 变量名:=函数名(…) 【训练1】 创建一个通过雇员编号返回雇员名称的函数get_emp_name。 步骤2：在sql*plus输入区中输入以下存储函数并编译： createorreplacefunctionget_emp_name(p_empnonumberdefault7788) returnvarchar2 electenameintov_enamefromempwhereempno=p_empno; return(v_ename); whenno_data_foundthen dbms_output.put_line(‘没有该编号雇员‘); return(null); whentoo_many_rowsthen dbms_output.put_line(‘有重复雇员编号！‘); dbms_output.put_line(‘发生其他错误！‘); 步骤3：调用该存储函数，输入并执行以下程序： dbms_output.put_line(‘雇员7369的名称是：‘||get_emp_name(7369)); dbms_output.put_line(‘雇员7839的名称是：‘||get_emp_name(7839)); 雇员7369的名称是：smith 雇员7839的名称是：king 说明：函数的调用直接出现在程序的dbms_output.put_line语句中，作为字符串表达式的一部分。 如果输入了错误的雇员编号，就会在函数的错误处理部分输出错误信息。试修改雇员编号，重新运 行调用部分。 【练习1】创建一个通过部门编号返回部门名称的存储函数get_dept_name。 存储过程和函数的查看可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询 当前用户的存储过程或函数的源代码，可以通过对 user_source数据字典视图的查询得到。user_source的结构如下： describeuser_source 结果为： 名称 是否为空类型 ————————————————————-————-———————– namevarchar2(30) typevarchar2(12) linenumber textvarchar2(4000) 说明：里面按行存放着过程或函数的脚本，name是过程或函数名，type代表类型(procedure或 function)，line是行号，text为脚本。 查询过程emp_count的脚本。 在sql*plus中输入并执行如下查询： selecttextfromuser_sourcewherename=’emp_count’; text ——————————————————————————– procedureemp_count(p_totaloutnumber) 查询过程get_emp_name的参数。 describeget_emp_name functionget_emp_namereturnsvarchar2 参数名称 类型 输入/输出默认值？ ——–——-——————– p_empnonumber(4)indefault 在发生编译错误时，显示错误。 showerrors 以下是一段编译错误显示： line/colerror ———-——————-————– 4/2pl/sql:sqlstatementignored 4/36pls-00201:必须说明标识符‘empp’ 说明：查询一个存储过程或函数是否是有效状态(即编译成功)，可以使用数据字典user_objects的 status列。 【训练4】查询emp_list存储过程是否可用： selectstatusfromuser_objectswhereobject_name=’emp_list’; status ———— valid 说明：valid表示该存储过程有效(即通过编译)，invalid表示存储过程无效或需要重新编译。当oracle 调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成valid并 执行，否则给出错误信息。当一个存储过程编译成功，状态变为valid，会不会在某些情况下变成 invalid。结论是完全可能的。比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过 程就会变成无效invalid。所以要注意存储过程和函数对其他对象的依赖关系。 如果要检查存储过程或函数的依赖性，可以通过查询数据字典user_denpendencies来确定，该表结 构如下： describeuser_dependencies; 是否为空 —–———-———— namenotnullvarchar2(30) referenced_ownervarchar2(30) referenced_namevarchar2(64) referenced_typevarchar2(12) referenced_link_namevarchar2(128) schemaidnumber dependency_typevarchar2(4) 说明：name为实体名，type为实体类型，referenced_owner为涉及到的实体拥有者账户， referenced_name为涉及到的实体名，referenced_type为涉及到的实体类型。 【训练5】 查询emp_list存储过程的依赖性。 selectreferenced_name,referenced_typefromuser_dependencieswherename=’emp_list’; referenced_namereferenced_type ——————————————————— standardpackage sys_stub_for_purity_analysispackage dbms_outputpackage dbms_outputsynonym dbms_outputnon-existent emptable emp_countprocedure 说明：可以看出存储过程emp_list依赖一些系统包、emp表和emp_count存储过程。如果删除 了emp表或emp_count存储过程，emp_list将变成无效。还有一种情况需要我们注意：如果一个 用户a被授予执行属于用户b的一个存储过程的权限，在用户b的存储过程中，访问到用户c的表， 用户b被授予访问用户c的表的权限，但用户a没有被授予访问用户c表的权限，那么用户a调用 用户b的存储过程是失败的还是成功的呢？答案是成功的。如果读者有兴趣，不妨进行一下实际测 试。 2、程序包 包的概念和组成 和包体(packagebody)。包头是包的说明部分，是对外的操作接口，对应用是可见的；包体是包的 代码和实现部分，对应用来说是不可见的黑盒。 包中可以包含的程序结构如下所示。 过程(procudure) 函数(function) 变量(variable) 常量(constant) 带参数的命名的程序模块 带参数、具有返回值的命名的程序模块 存储变化的量的存储单元 存储不变的量的存储单元 游标(cursor)用户定义的数据操作缓存区，在可执行部分使用 类型(type) 用户定义的新的结构类型 异常(exception) 在标准包中定义或由用户自定义，用于处理程序错误 说明部分可以出现在包的三个不同的部分：出现在包头中的称为公有元素，出现在包体中的称 为私有元素，出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同，如下所示。 公有元素(public) 过程有效 在包头中说明，在包体中具体定义在包外可见并可以访问，对整个应用的全 私有元素(private) 在包体的说明部分说明 只能被包内部的其他部分访问 局部变量(local)在过程或函数的说明部分说明 只能在定义变量的过程或函数中使用 在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体 中的说明一致。 包有以下优点： *包可以方便地将存储过程和函数组织到一起，每个包又是相互独立的。在不同的包中，过程、函 数都可以重名，这解决了在同一个用户环境中命名的冲突问题。 *包增强了对存储过程和函数的安全管理，对整个包的访问权只需一次授予。 *在同一个会话中，公用变量的值将被保留，直到会话结束。 *区分了公有过程和私有过程，包体的私有过程增加了过程和函数的保密性。 *包在被首次调用时，就作为一个整体被全部调入内存，减少了多次访问过程或函数的i/o次数。 创建包和包体 包由包头和包体两部分组成，包的创建应该先创建包头部分，然后创建包体部分。创建、删除和编 译包的权限同创建、删除和编译存储过程的权限相同。 创建包头的简要语句如下： create[orreplace]package包名 {is|as} 公有变量定义 公有类型定义 公有游标定义 公有异常定义 函数说明 过程说明 创建包体的简要语法如下： create[orreplace]packagebody包名 私有变量定义 私有类型定义 私有游标定义 私有异常定义 函数定义 过程定义 包的其他操作命令包括： 删除包头： droppackage包头名 删除包体： droppackagebody包体名 重新编译包头： alterpackage包名compilepackage 重新编译包体： alterpackage包名compilepackagebody 在包头中说明的对象可以在包外调用，调用的方法和调用单独的过程或函数的方法基本相同， 惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意，不同的会话 将单独对包的公用变量进行初始化，所以不同的会话对包的调用属于不同的应用。 系统包oracle预定义了很多标准的系统包，这些包可以在应用中直接使用，比如在训练中我们使 用的dbms_output包，就是系统包。put_line是该包的一个函数。常用系统包下所示。 dbms_output在sql*plus环境下输出信息 dbms_ddl dbms_session 编译过程函数和包 改变用户的会话，初始化包等 dbms_transaction 控制数据库事务 dbms_mail dbms_lock dbms_alert dbms_pipe dbms_job dbms_lob dbms_sql 连接oracle*mail 进行复杂的锁机制管理 识别数据库事件告警 通过管道在会话间传递信息 管理oracle的作业 操纵大对象 执行动态sql语句 dbms_session改变用户的会话，初始化包等 dbms_transaction控制数据库事务 包的应用 在sql*plus环境下，包和包体可以分别编译，也可以一起编译。如果分别编译，则要先编译包头， 后编译包体。如果在一起编译，则包头写在前，包体在后，中间用“/”分隔。 可以将已经存在的存储过程或函数添加到包中，方法是去掉过程或函数创建语句的createor replace部分，将存储过程或函数复制到包体中，然后重新编译即可。 如果需要将私有过程或函数变成共有过程或函数的话，将过程或函数说明部分复制到包头说明 部分，然后重新编译就可以了。 【训练1】创建管理雇员信息的包employe，它具有从emp表获得雇员信息，修改雇员名称，修 改雇员工资和写回emp表的功能。 createorreplacepackageemploye–包头部分 procedureshow_detail; procedureget_employe(p_empnonumber); proceduresave_employe; procedurechange_name(p_newnamevarchar2); procedurechange_sal(p_newsalnumber); endemploye; createorreplacepackagebodyemploye–包体部分 employeemp%rowtype; ————–显示雇员信息————— procedureshow_detail dbms_output.put_line(‘—–雇员信息—–’); dbms_output.put_line(‘雇员编号：‘||employe.empno); dbms_output.put_line(‘雇员名称：‘||employe.ename); dbms_output.put_line(‘雇员职务：‘||employe.job); dbms_output.put_line(‘雇员工资：‘||employe.sal); dbms_output.put_line(‘部门编号：‘||employe.deptno); endshow_detail; —————–从emp表取得一个雇员——————– procedureget_employe(p_empnonumber) select*intoemployefromempwhereempno=p_empno; dbms_output.put_line(‘获取雇员‘||employe.ename||’信息成功‘); dbms_output.put_line(‘获取雇员信息发生错误！‘); endget_employe; ———————-保存雇员到emp表————————– proceduresave_employe updateempsetename=employe.ename,sal=employe.salwhereempno= employe.empno; dbms_output.put_line(‘雇员信息保存完成！‘); endsave_employe; —————————-修改雇员名称—————————— procedurechange_name(p_newnamevarchar2) employe.ename:=p_newname; dbms_output.put_line(‘修改名称完成！‘); endchange_name; —————————-修改雇员工资————————– procedurechange_sal(p_newsalnumber) employe.sal:=p_newsal; dbms_output.put_line(‘修改工资完成！‘); endchange_sal; 步骤2：获取雇员7788的信息: executeemploye.get_employe(7788); 获取雇员scott信息成功 步骤3：显示雇员信息： executeemploye.show_detail; ——————雇员信息—————— 雇员编号：7788 雇员名称：scott 雇员职务：analyst 雇员工资：3000 部门编号：20 步骤4：修改雇员工资： executeemploye.change_sal(3800); 修改工资完成！ 步骤5：将修改的雇员信息存入emp表 executeemploye.save_employe; 雇员信息保存完成！ 说明：该包完成将emp表中的某个雇员的信息取入内存记录变量，在记录变量中进行修改编辑， 在确认显示信息正确后写回emp表的功能。记录变量employe用来存储取得的雇员信息，定义为 私有变量，只能被包的内部模块访问。 【练习1】为包增加修改雇员职务和部门编号的功能。 阶段训练 下面的训练通过定义和创建完整的包emp_pk并综合运用本章的知识，完成对雇员表的插入、删除 等功能，包中的主要元素解释如下所示。 程序结构 类型说明 v_emp_count公有变量 跟踪雇员的总人数变化，插入、删除雇员的同时修改该变量的值 init 公有过程 对包进行初始化，初始化雇员人数和工资修改的上、下限 list_emp insert_emp 显示雇员列表 通过编号插入新雇员 delete_emp公有过程 通过编号删除雇员 change_emp_sal 通过编号修改雇员工资 v_message c_max_sal c_min_sal 私有变量 存放准备输出的信息 对工资修改的上限 对工资修改的下限 show_message 私有过程 显示私有变量v_message中的信息 exist_emp 私有函数 判断某个编号的雇员是否存在，该函数被insert_emp、delete_emp和 change_emp_sal等过程调用 完整的雇员包emp_pk的创建和应用。 createorreplacepackageemp_pk –包头部分 v_emp_countnumber(5); –雇员人数 procedureinit(p_maxnumber,p_minnumber);–初始化 procedurelist_emp; –显示雇员列表 procedureinsert_emp(p_empnonumber,p_enamevarchar2,p_jobvarchar2, p_salnumber); –插入雇员 proceduredelete_emp(p_empnonumber);–删除雇员 procedurechange_emp_sal(p_empnonumber,p_salnumber); –修改雇员工资 endemp_pk; /createorreplacepackagebodyemp_pk –包体部分 v_messagevarchar2(50);–显示信息 v_max_salnumber(7);–工资上限 v_min_salnumber(7);–工资下限 functionexist_emp(p_empnonumber)returnboolean;–判断雇员是否存在函数 procedureshow_message;–显示信息过程 ——————————-初始化过程—————————- procedureinit(p_maxnumber,p_minnumber) selectcount(*)intov_emp_countfromemp; v_max_sal:=p_max; v_min_sal:=p_min; v_message:=’初始化过程已经完成！‘; show_message; endinit; —————————-显示雇员列表过程——————— procedurelist_emp dbms_output.put_line(‘姓名 职务 工资‘); foremp_recin(select*fromemp) dbms_output.put_line(rpad(emp_rec.ename,10,”)||rpad(emp_rec.job,10,’ ‘)||to_char(emp_rec.sal)); dbms_output.put_line(‘雇员总人数‘||v_emp_count); endlist_emp; —————————–插入雇员过程—————————– procedureinsert_emp(p_empno number,p_enamevarchar2,p_jobvarchar2,p_salnumber) ifnotexist_emp(p_empno)then insertintoemp(empno,ename,job,sal) v_emp_count:=v_emp_count+1; v_message:=’雇员‘||p_empno||’已插入!’; else v_message:=’雇员‘||p_empno||’已存在,不能插入!’; v_message:=’雇员‘||p_empno||’插入失败!’; endinsert_emp; —————————删除雇员过程——————– proceduredelete_emp(p_empnonumber) ifexist_emp(p_empno)then deletefromempwhereempno=p_empno; v_emp_count:=v_emp_count-1; v_message:=’雇员‘||p_empno||’已删除!’; v_message:=’雇员‘||p_empno||’不存在，不能删除!’; values(p_empno,p_ename,p_job,p_sal); v_message:=’雇员‘||p_empno||’删除失败!’; enddelete_emp; —————————————修改雇员工资过程———————————— procedurechange_emp_sal(p_empnonumber,p_salnumber) if(p_sal>v_max_salorp_sal v_message:=’工资超出修改范围!’; elsifnotexist_emp(p_empno)then v_message:=’雇员‘||p_empno||’不存在，不能修改工资!’; updateempsetsal=p_salwhereempno=p_empno; v_message:=’雇员‘||p_empno||’工资已经修改!’; v_message:=’雇员‘||p_empno||’工资修改失败!’; endchange_emp_sal; —————————-显示信息过程—————————- procedureshow_message dbms_output.put_line(‘提示信息：‘||v_message); endshow_message; ————————判断雇员是否存在函数——————- functionexist_emp(p_empnonumber) returnboolean v_numnumber;–局部变量 selectcount(*)intov_numfromempwhereempno=p_empno; ifv_num=1then returntrue; returnfalse; endexist_emp; —————————– 程序包已创建。 程序包主体已创建。 步骤2：初始化包： executeemp_pk.init(6000,600); 显示为： 提示信息：初始化过程已经完成！ 步骤3：显示雇员列表： executeemp_pk.list_emp; 姓名 工资 smithclerk1560 allensalesman1936 wardsalesman1830 jonesmanager2975 雇员总人数：14 步骤4：插入一个新记录： executeemp_pk.insert_emp(8001,’小王‘,’clerk’,1000); 提示信息：雇员8001已插入! 步骤5：通过全局变量v_emp_count查看雇员人数： dbms_output.put_line(emp_pk.v_emp_count); 15 步骤6：删除新插入记录： executeemp_pk.delete_emp(8001); 提示信息：雇员8001已删除! 再次删除该雇员： 提示信息：雇员8001不存在，不能删除! 步骤7：修改雇员工资： executeemp_pk.change_emp_sal(7788,8000); 提示信息：工资超出修改范围! 权： grantexecuteonemp_pktostudent; 每一个新的会话要为包中的公用变量开辟新的存储空间,所以需要重新执行初始化过程。两个会 话的进程互不影响。 步骤9：其他用户调用包。 executescott.emp_pk.emp_pk.init(5000,700); 说明：在初始化中设置雇员的总人数和修改工资的上、下限，初始化后v_emp_count为14人， 插入雇员后v_emp_count为15人。v_emp_count为公有变量，所以可以在外部程序中使用 dbms_output.put_line输出，引用时用emp_pk.v_emp_count的形式，说明所属的包。 而私有变量v_max_sal和v_min_sal不能被外部访问，只能通过内部过程来修改。 同样，exist_emp和show_message也是私有过程，也只能在过程体内被其他模块引用。 注意：在最后一个步骤中，因为student模式调用了scott模式的包，所以包名前要增加 模式名scott。不同的会话对包的调用属于不同的应用，所以需要重新进行初始化。 3、操作练习 1)如果存储过程的参数类型为out，那么调用时传递的参数应该为： a．常量b．表达式 c．变量d．都可以 2)下列有关存储过程的特点说法错误的是： a．存储过程不能将值传回调用的主程序 b．存储过程是一个命名的模块 c．编译的存储过程存放在数据库中 d．一个存储过程可以调用另一个存储过程 3)下列有关函数的特点说法错误的是： a．函数必须定义返回类型 b．函数参数的类型只能是in c．在函数体内可以多次使用return语句 d．函数的调用应使用execute命令 4)包中不能包含的元素为: a．存储过程b．存储函数 c．游标 d．表 5)下列有关包的使用说法错误的是： a．在不同的包内模块可以重名 b．包的私有过程不能被外部程序调用 c．包体中的过程和函数必须在包头部分说明 d．必须先创建包头，然后创建包体 4、触发器 触发器:是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用 他们，其功能如下： 1）允许/限制对表的修改 2）自动生成派生列，比如自增字段 3）强制数据一致性 4）提供审计和日志记录 5）防止无效的事务处理 6）启用复杂的业务逻辑 开始 createtriggerbiufer_employees_department_id beforeinsertorupdate ofdepartment_id onemployees referencingoldasold_value newasnew_value foreachrow when(new_value.department_id<>80) :new_value.commission_pct:=0; 触发器的组成部分：的 1）触发器名称 2）触发语句 3）触发器限制 4）触发操作 触发器名称： 命名习惯： biufer（beforeinsertupdateforeachrow） employees表名 department_id列名 触发语句 比如： 表或视图上的dml语句 ddl语句 数据库关闭或启动,startupshutdown等等 说明： 1）无论是否规定了department_id，对employees表进行insert的时候 2）对employees表的department_id列进行update的时候 限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。 其中的new_value是代表更新之后的值。 触发操作： 是触发器的主体 主体很简单，就是将更新后的commission_pct列置为0 触发： insertintoemployees(employee_id, last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct) values(12345,’chen’,’donny’,sysdate,12,‘donny@hotmail.com’,60,10000,.25); selectcommission_pctfromemployeeswhereemployee_id=12345; 触发器不会通知用户，便改变了用户的输入值。 触发器类型： 1）语句触发器 2）行触发器 3）insteadof触发器 4）系统条件触发器 5）用户事件触发器 5、Job创建和操作 1)设置初始化参数job_queue_processes altersystemsetjob_queue_processes=n;（n>0）job_queue_processes最大值为1000 查看jobqueue后台进程 selectname,descriptionfromv$bgprocess; 2)dbms_jobpackage用法介绍 包含以下子过程： broken()过程;change()过程;interval()过程;isubmit()过程;next_date()过程; remove()过程;run()过程;submit()过程;user_export()过程;what()过程; (1)broken()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。 这个过程有三个参数：job、broken与next_date。 procedurebroken(jobinbinary_integer,brokeninboolean,next_dateindate:=sysdate) job参数是工作号，它在问题中唯一标识工作。 broken参数指示此工作是否将标记为破——true说明此工作将标记为破，而flase说明此工作 将标记为未破。 job如果由于某种原因未能成功之行，oracle将重试16次后，还未能成功执行，将被标记为 broken重新启动状态为broken的job，有如下两种方式; a、利用dbms_job.run()立即执行该job dbms_job.run(:jobno)该jobno为submit过程提交时返回的jobnumber b、利用dbms_job.broken()重新将broken标记为false dbms_job.broken(:job,false,next_date) (2)change()过程用来改变指定工作的设置。 这个过程有四个参数：job、what、next_date与interval。 procedurechange(jobinbinary_integer, whatinvarchar2, next_dateindate, intervalinvarchar2) 此job参数是一个整数值，它唯一标识此工作。 what参数是由此工作运行的一块pl/sql代码块。 next_date参数指示何时此工作将被执行。 interval参数指示一个工作重执行的频度. interval。 procedureinterval(jobinbinary_integer,intervalinvarchar2) job参数标识一个特定的工作。interval参数指示一个工作重执行的频度。 (4)isubmit()过程用来用特定的工作号提交一个工作。这个过程有五个参数：job、what、next_date、 interval与no_parse。 procedureisubmit(jobinbinary_ineger, intervalinvarchar2, no_parseinbooean:=false) 这个过程与submit()过程的唯一区别在于此job参数作为in型参数传递且包括一个由开发者提 供的工作号。如果提供的工作号已被使用，将产生一个错误。 procedurenext_date(jobinbinary_ineger,next_dateindate) (6)remove()过程来删除一个已计划运行的工作。这个过程接收一个参数： procedureremove(jobin binary_ineger); job参数唯一地标识一个工作。这个参数的值是由为此工作调用submit()过程返回的job参数 的值。已正在运行的工作不能由调用过程序删除。 (7)run()过程用来立即执行一个指定的工作。这个过程只接收一个参数： procedurerun(jobinbinary_ineger) job参数标识将被立即执行的工作。 (8)使用submit()过程，工作被正常地计划好。 这个过程有五个参数：job、what、next_date、interval与no_parse。 proceduresubmit(joboutbinary_ineger, next_datein intervalin no_parsein date, varchar2, booean:=false) job参数是由submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。 what参数是将被执行的pl/sql代码块。 next_date参数指识何时将运行这个工作。 interval参数何时这个工作将被重执行。 no_parse参数指示此工作在提交时或执行时是否应进行语法分析——true指示此pl/sql代码在 它第一次执行时应进行语法分析，而false指示本pl/sql代码应立即进行语法分析。 (9)user_export()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交,此程序 有两个参数：job与my_call。 procedureuser_export(jobinbinary_ineger,my_callinoutvarchar2) job参数标识一个安排了的工作。my_call参数包含在它的当前状态重新提交此工作所需要的 正文。 (10)what()过程应许在工作执行时重新设置此正在运行的命令。这个过程接收两个参数：job与what procedurewhat(job inbinary_ineger,whatinoutvarchar2) —job参数标识一个存在的工作。what参数指示将被执行的新的pl/sql代码。 dba_jobs all_jobs user_jobs selectjob,next_date,next_sec,failures,broken fromdba_jobs; jobnext_datenext_secfailuresb ——-—————–——–- 912501-jun-0100:00:004n 1414424-oct-0116:35:350n 912701-jun-0100:00:0016y 3rowsselected. selectsid,r.job,log_user,r.this_date,r.this_sec fromdba_jobs_runningr,dba_jobsj wherer.job=j.job; sidjoblog_userthis_datethis_sec —–———-————-—————– 1214144hr24-oct-9417:21:24 258536qs24-oct-9416:45:12 2rowsselected. selectsid,type,id1,id2 fromv$lock wheretype=‘jq’; sidtyid1id2 —————————— 12jq014144 1rowselected. 4)实例操作 创建测试表 createtabletest(adate); ——createtable 创建一个自定义过程 createorreplaceproceduremyprocas insertintotestvalues(sysdate); ————–过程已创建。 创建job variablejob1number; dbms_job.submit(:job1,’myproc;’,sysdate,’sysdate+1/1440′); 运行test过程一次 运行job dbms_job.run(:job1); ——————- 2001/01/0723:51:21 2001/01/0723:52:22 2001/01/0723:53:24 删除job dbms_job.remove(:job1); 6、后台脚本 编辑文件：dyk_name_mtable1.sql写入以下内容： altertableowner.table_name1movetablespacetablespace_name1; altertableowner.table_name2movetablespacetablespace_name2; 编辑文件dyk_name_mtable1.sh –每天1440分钟，即一分钟 sqlplususername/passwdString@TNSstring<>dyk_name_mtable2.out