汤阳光Oracle课程文档四.docx
《汤阳光Oracle课程文档四.docx》由会员分享，可在线阅读，更多相关《汤阳光Oracle课程文档四.docx（25页珍藏版）》请在冰点文库上搜索。
汤阳光Oracle课程文档四
1.存储过程和存储函数
1.1.存储过程与存储函数说明
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
●什么时候用存储过程/存储函数
?
?
?
原则：
如果只有一个返回值，用存储函数；否则，就用存储过程。
1.2.存储过程
1.2.1.创建存储过程
用CREATEPROCEDURE命令建立存储过程。
语法如下：
create[orreplace]PROCEDURE过程名[（参数列表）]
AS
变量声明
PLSQL子程序体；
1.2.2.调用存储过程
♦方法一：
♦方法二：
exec[ute]存储过程名
1.2.3.存储过程示例
♦为指定的职工在原工资的基础上长10%的工资，并打印涨工资前和涨工资后的工资
1.3.存储函数
1.3.1.创建存储函数
函数（Function）为一命名的存储程序，可带参数，并返回一计算值。
函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。
函数说明要指定函数名、结果值的类型，以及参数类型等。
语法如下：
CREATE[ORREPLACE]FUNCTION函数名（参数列表）
RETURN函数值类型
AS
变量声明
PLSQL子程序体；
1.3.2.调用存储函数
或写成：
1.3.3.存储函数示例
1.4.过程和函数中的in和out
问题：
有out的是返回值，还是return指明的那个值是返回值
out可以返回多个,声明的return类型必须和out的一致,并且多个的时候,那多个元素也应该一致.
1.5.在Java语言中调用存储过程与存储函数
1.5.1.JDBC调用存储过程
●存储过程：
●Java程序：
1.5.2.JDBC调用存储函数
●存储函数：
●Java程序
1.5.3.JDBC调用带光标类型out参数的存储过程或函数
2.触发器
2.1.说明
●数据库触发器是一个与表相关联的、存储的PL/SQL程序。
每当一个特定的数据操作语句（Insert,update,delete）在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
●触发器的类型
•语句级触发器
在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
•行级触发器（FOREACHROW）
触发语句作用的每一条记录都被触发。
在行级触发器中使用old和new伪记录变量,识别值的状态。
●触发器可用于
•数据确认
•实施复杂的安全性检查
•做审计，跟踪表上所做的数据操作等
•数据的备份和同步
2.2.创建触发器
2.3.触发语句与伪记录变量的值
触发语句
:
old
:
new
Insert
所有字段都是空（null）
将要插入的数据
Update
更新以前该行的值
更新后的值
delete
删除以前该行的值
所有字段都是空（null）
2.4.示例1：
限制非工作时间向数据库插入数据
2.5.示例2：
确认数据（检查emp表中sal的修改值不低于原值）
●运行效果
2.6.查询触发器、过程及函数
●Select*fromuser_triggers;
●Select*fromuser_source;
3.数据字典
3.1.数据字典说明
3.1.1.有2种作用的表
3.1.2.数据字典的结构
3.1.3.数据字典命名规则
前缀
说明
USER
用户自己的
ALL
用户可以访问到的
DBA
管理员视图
V$
性能或参数设置相关的数据
3.1.4.如何使用数据字典视图
DESCRIBEDICTIONARY
SELECT*
FROMdictionary
WHEREtable_name='USER_OBJECTS';
从DICTIONARY开始，这个数据对象包含了数据字典的表名和说明
3.1.5.USER_OBJECTS和ALL_OBJECTS
USER_OBJECTS:
•通过查询USER_OBJECTS可以确定当前用户所有创建的对象
•可以获得如下信息:
•Datecreated
•Dateoflastmodification
•Status（validorinvalid）
ALL_OBJECTS:
•可以通过查询ALL_OBJECTS来确定当前用户能访问的数据对象
3.1.6.USER_OBJECTS视图
3.1.7.表与列的信息
DESCRIBEuser_tables
SELECTtable_name
FROMuser_tables;
3.1.8.列的信息
DESCRIBEuser_tab_columns
SELECTcolumn_name,data_type,data_length,
data_precision,data_scale,nullable
FROMuser_tab_columns
WHEREtable_name='EMPLOYEES';
3.1.9.约束
♦USER_CONSTRAINTS:
当前用户表上的约束
♦USER_CONS_COLUMNS当前用户创建的列约束
DESCRIBEuser_constraints
SELECTconstraint_name,constraint_type,
search_condition,r_constraint_name,
delete_rule,status
FROMuser_constraints
WHEREtable_name='EMPLOYEES';
DESCRIBEuser_cons_columns
SELECTconstraint_name,column_name
FROMuser_cons_columns
WHEREtable_name='EMPLOYEES';
3.1.10.视图
DESCRIBEuser_views
SELECTDISTINCTview_nameFROMuser_views;
SELECTtextFROMuser_views
WHEREview_name='EMP_DETAILS_VIEW';
3.1.11.序列
DESCRIBEuser_sequences
♦通过USER_SEQUENCES查询序列信息
SELECTsequence_name,min_value,max_value,
increment_by,last_number
FROMuser_sequences;
•LAST_NUMBER表示当没有使用NOCAHCE时，下一个可用的值
3.1.12.同义词
DESCRIBEuser_synonyms
SELECT*
FROMuser_synonyms;
3.1.13.给表添加注释
•使用COMMENT语句给表或者列，添加注释:
•注释相关的视图:
•ALL_COL_COMMENTS
•USER_COL_COMMENTS
•ALL_TAB_COMMENTS
•USER_TAB_COMMENTS
•查询表的注释
•select*fromuser_tab_commentswheretable_name=‘?
?
?
';
3.1.14.权限相关的数据字典
♦DBA_TAB_PRIVS:
⏹包含数据库所有的对象权限信息
♦DBA_SYS_PRIVS:
⏹包含数据库中所有的系统权限信息
♦SESSION_PRIVS:
⏹包含当前用户可以使用的权限信息（Session就是当前用户的会话）
3.2.小结
♦DICTIONARY
♦USER_OBJECTS
♦USER_TABLES
♦USER_TAB_COLUMNS
♦USER_CONSTRAINTS
♦USER_CONS_COLUMNS
♦USER_VIEWS
♦USER_SEQUENCES
♦USER_TAB_SYNONYMS
♦表的注释
4.Oracle数据库管理
4.1.闪回删除的表
4.1.1.说明
♦闪回删除，实际上从系统的回收站中将已删除的对象，恢复到删除之前的状态。
♦系统的回收站只对普通用户有作用。
●回收站是所有被删除对象及其相依对象的逻辑存储容器，例如当一个表被删除（drop）时，该表及其相依对象并不会马上被数据库彻底删除，而是被保存到回收站中。
●回收站将用户执行的drop操作记录在一个系统表中，也就是将被删除的对象写到一个数据字典中。
如果确定不再需要该对象，可以使用purge命令对回收站进行清空。
●被删除的对象的名字可能是相同的，例如用户创建了一个test表，使用drop命令删除该表后，又创建了一个test表，这时，如果再次删除该表就会导致向回收站中添加了两个相同的表。
4.1.2.闪回删除（flashbackdrop）的语法
4.1.3.回收站中对象的命名规则
●为了确保添加到回收站中的对象的名称都是唯一的，系统会对这些保存到回收站中的对象进行重命名，重命名的格式如下：
BIN$globalUID$version
•其中：
BIN表示RECYCLEBIN；globalUID是一个全局唯一的、24个字非长的对象，该标识与原对象名没有任何关系；version指数据库分配的版本号。
4.1.4.Oracle回收站举例
4.2.管理用户与权限
4.2.1.预定义帐户:
SYS和SYSTEM
♦SYS帐户（数据库拥有者）:
⏹拥有DBA权限
⏹拥有ADMINOPTION的所有权限
⏹拥有startup,shutdown,以及若干维护命令
⏹拥有数据字典
♦SYSTEM帐户拥有DBA权限.
♦这些帐户并非用于常规操作
4.2.2.创建用户
Selecttheuser,andclickUnlockUser.
●验证用户
●密码验证方式（用户名/密码）
●外部验证方式（主机认证）
●全局验证方式（其他方式：
生物认证方式、token方式）
●管理员验证
●操作系统安全:
•DBA必须拥有创建和删除文件的操作系统权限
•普通数据库用户不应具有拥有创建和删除文件的操作系统权限
●管理员安全:
•SYSBA和SYSOPER通过密码文件或操作系统实现连接审定.
•密码文件使用名称鉴别DBA用户.
•OS验证并不记录特定用户.
•对于SYSDBA和SYSOPER来说OS验证优先于密码文件认证.
●解锁用户帐户和重置密码
4.2.3.权限
♦用户权限有两种:
⏹System:
允许用户执行对于数据库的特定行为，例如：
创建表、创建用户等
⏹Object:
允许用户访问和操作一个特定的对象，例如：
对其他方案下的表的查询
1.1.1.1.系统权限
1.1.1.2.对象权限
●赋予对象权限:
●选择对象类型.
●选择对象.
●选择权限.
1.1.1.3.使用ADMINOPTION撤销系统权限
1.1.1.4.使用GRANTOPTION撤销对象权限
注意：
grantoption只对DML语句起作用，例如select
1.1.1.5.角色
♦角色的优点
1.1.易于权限管理
1.2.动态权限管理
1.3.选择有效的权限
♦关联权限到角色，关联角色到用户
●预定义角色和权限
角色
权限
CONNECT
CREATESESSION
RESOURCE
CREATECLUSTER,CREATEINDEXTYPE,CREATE
OPERATOR,CREATEPROCEDURE,CREATE
SEQUENCE,CREATETABLE,CREATETRIGGER,
CREATETYPE
SCHEDULER_ADMIN
CREATEANYJOB,CREATEEXTERNALJOB,
CREATEJOB,EXECUTEANYCLASS,EXECUTEANY
PROGRAM,MANAGESCHEDULER
DBA
Mostsystemprivileges,severalotherroles.Donotgranttononadministrators.
SELECT_CATALOG_ROLE
Nosystemprivileges,butHS_ADMIN_ROLEandover1,700objectprivilegesonthedatadictionary
●创建角色
•SelectAdministration>Schema>Users&Privileges>Roles.
●关联角色到用户
4.3.数据的导入与导出（备份与恢复）
4.3.1.说明
使用的是命令行的命令，不是sqlplus命令：
Imp.exe
Exp.exe
4.3.2.数据导出：
exp命令
●格式：
•EXPKEYWORD=value或KEYWORD=（value1,value2,...,valueN）
●参数说明：
•参考帮助文档。
●exp命令举例：
●表方式：
将指定表的数据导出
●用户方式：
将指定用户的所有对象及数据导出
4.3.3.数据导入：
imp命令
●格式：
•IMPKEYWORD=value或KEYWORD=（value1,value2,...,valueN）
●参数说明：
•参考帮助文档。
●Imp命令举例：
●导入一张或几张表
●导入用户下的表
4.3.4.Exp和imp的提示模式
●导出：
●导入：
升级会员 