oracle提高.docx
《oracle提高.docx》由会员分享，可在线阅读，更多相关《oracle提高.docx（21页珍藏版）》请在冰豆网上搜索。
oracle提高
山大地纬软件股份有限公司
Oracle提高
二零一三年一月
第一章oracle用户及权限、表空间、DBlink、触发器
1.1oracle用户权限
1.1.1系统用户
系统用户包括sys和system。
所有Oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于Oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。
sys用户拥有dba，sysdba，sysoper等角色或权限，是Oracle权限最高的用户。
（sysdba和sysoper属于systemprivilege，也称为administrativeprivilege，拥有例如数据库开启关闭之类一些系统管理级别的权限）system用户用于存放次一级的内部数据，如Oracle的一些特性或工具的管理信息。
system用户拥有普通dba角色权限。
1.1.2普通用户
我们可以根据应用的需要建立自己的用户。
比如：
地纬的社保软件中使用的用户AGED、CSI、BE3、SC、MD3，新建立用户之后默认只有登陆数据库的权限，没有其他权限。
创建和删除用户：
•CREATEUSERusernameIDENTIFIEDBYuserpassword[DEFAULTTABLESPACEtablespace]
•创建用户：
•Createusersi0001identifiedby123defaulttablespacets_si;
•删除用户：
Dropusersi0001cascade;
1.1.3角色
•角色相当于一个windows中的组，比如我们属于administrators组，我们就拥有administrator的所有权限，oracle为了方便管理也预定义了很多的组：
•比如：
•connect连上Oracle,做最基本操作
•resource具有程序开发最基本的权限
•dba数据库管理员所有权限
•exp-full-database可把数据库整个备份输出的权限
•imp-full-datsabase可把数据库整个备份恢复输入的权限
给用户分配角色：
•Connectsystem/manager;
•grantresourcetosi0001;
1.1.4系统权限
•Oracle把权限进行了细分，常用的系统权限如下所示：
–alterindex;
–altersequence;
–altertrigger;
–createindex;
–createtable;
–createprocedure;
–createsequence;
–createtrigger;
–createview;
–dropindex;
–droptable;
–dropprocedure;
–dropsequence;
–droptrigger;
–dropview;
–executeprocedure;
给用户授予系统权限：
•给用户创建触发器的权限：
•Grantcreatetriggertosi0001;
•给用户更改表的权限：
•Grantaltertabletosi0001;
1.1.5对象权限
•我们想让si0001可以访问md3用户的person_info表，我们需要给si0001赋予对象权限
•一定要注意：
md3是表的拥有者，因此只有md3用户才可以把访问该表的权限给别人
•表对象权限包括（七个）
•Insert,update,delete,select,index,alter,reference
•存储过程的有：
•exec
给用户授予对象权限：
•把对md3.person_info的select权限分配给si0001用户：
•Connectmd3/md3;
•Grantselectonmd3.person_infotosi0001;
1.1.6管理权限
•Si0001虽然拥有对md3.person_info的select权限，但是si0001并不能把他所拥有的这些权限给其他用户,如果需要它必须具备管理权限能力。
•语法如下：
•Grantselectonmd3.person_infotosi0001withgrantoption;
•这样si0001用户就可以把对md3的person_info表的select权限赋给其他用户了。
不区分用户赋权：
有时候我们需要给某个用户操作数据库中所有用户的权限，可以使用下面的语句：
•grantalteranyindextosi0001;
•grantalteranysequencetosi0001;
•grantalteranytriggertosi0001;
•grantcreateanyindextosi0001;
•grantcreateanytabletosi0001;
•grantcreateanyproceduretosi0001;
•grantcreateanysequencetosi0001;
•grantcreateanysynonymtosi0001;
•grantcreateanytriggertosi0001;
•grantcreateanyviewtosi0001;
•grantcreatepublicsynonymtosi0001;
•grantcreatesessiontosi0001;
•grantcreatejobtosi0001;
•grantmanageschedulertosi0001;
•grantdropanyindextosi0001;
•grantdropanytabletosi0001;
•grantdropanyproceduretosi0001;
•grantdropanysequencetosi0001;
•grantdropanysynonymtosi0001;
•grantdropanytriggertosi0001;
•grantdropanyviewtosi0001;
•grantdroppublicsynonymtosi0001;
•grantexecuteanyproceduretosi0001;
•grantinsertanytabletosi0001;
•grantselectanysequencetosi0001;
•grantselectanytabletosi0001;
•grantupdateanytabletosi0001;
•grantUNLIMITEDTABLESPACEtosi0001;
1.1.7权限回收
•收回部分权限：
•Revokeselectonmd3.person_infofromsi0001;
•收回所有权限：
•Revokeallonmd3.person_infofromsi0001;
1.2表空间
定义：
ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。
一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。
表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。
每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。
SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息（关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表）。
●所有存储在数据库中的数据都必须驻留在某个表空间内。
●每个表空间包括一个或多个数据文件，但每个数据文件只能属于一个表空间。
创建表空间：
●createtablespacets_test
datafile'X:
\ORA8\DATABASE\ts_test_1.dat'size500M
defaultstorage（initial10knext128k
minextents2maxextents121pctincrease10）
online;
删除表空间：
●droptablespaceTS_TESTincludingcontents;
给表空间添加数据文件：
●altertablespacets_test
adddatafile'd:
\oracle8\oradata\ts_test_02.dbf'size200M;
修改数据文件的大小：
●alterdatabasedatafile'd:
\oracle8\oradata\ts_test_02.dbf'resize500M;
1.3DBlink
定义：
当用户要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中必须创建了远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
创建dblink：
1.创建dblink的第一种方式，是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库。
createpublicdatabaselink
cp3linkconnecttoscidentifiedbyscusing'cp3';
其中cp3link是你创建的dblink名字sc/sc是登录到远程数据库的用户/密码。
然后在本地数据库中通过dblink访问远程数据库'cp3'中sc.per_natl表,sql语句如下所示
select*fromsc.per_natl@cp3link;
2.创建dblink的第二种方式，是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库，
createdatabaselinkcp3link1
connecttoscidentifiedbysc
using'（DESCRIPTION=
（ADDRESS_LIST=
（ADDRESS=（PROTOCOL=TCP）（HOST=10.1.0.7）（PORT=1521））
）
（CONNECT_DATA=
（SERVER=DEDICATED）
（SERVICE_NAME=ora1）
）
）';
1.4触发器
定义：
触发器是特定事件出现的时候，自动执行的代码块。
类似于存储过程，触发器与存储过程的区别在于:
存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。
功能：
1、允许/限制对表的修改
2、自动生成派生列，比如自增字段
3、强制数据一致性
4、提供审计和日志记录
5、防止无效的事务处理
6、启用复杂的业务逻辑
语法：
CREATE[ORREPLACE]TIGGER触发器名触发时间触发事件
ON表名
[FOREACHROW]
BEGIN
pl/sql语句
END
其中：
触发器名：
触发器对象的名称。
由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：
指明触发器何时执行，该值可取：
before---表示在数据库动作之前触发器执行;after---表示在数据库动作之后出发器执行。
触发事件：
指明哪些数据库动作会触发此触发器。
如：
insert：
数据库插入会触发此触发器;update：
数据库修改会触发此触发器;delete：
数据库删除会触发此触发器。
表名：
数据库触发器所在的表。
foreachrow：
对表的每一行触发器执行一次。
如果没有这一选项，则只对整个表执行一次。
一个例子：
下面的触发器实现bi3.ac01表中几个字段修改后同时修改ad3.ic03表，以保证这两张表的一致性：
CREATEORREPLACETRIGGERAD3.TRIG_PER_NATL_UPDATE
AFTERUPDATEOFAAC004,AAC003,BAC008,AAC002,BAE025,AAC006
ONBI3.AC01
REFERENCINGOLDASOLDNEWASNEW
FOREACHROW
begin
updatead3.ic03a
seta.aac002=:
new.aac002,
a.aac003=:
new.aac003,
a.bae025=:
new.bae025,
a.aac004=:
new.aac004,
a.aac006=:
new.aac006,
a.bac008=:
new.bac008
where:
new.aac001=a.aac001;
end;
1.5练习题
1、创建一个表空间ts_userid，要求该表空间有两个文件，每个文件100M。
2、采用两种方式修改ts_userid表空间的大小。
（增加文件和扩展原文件）
3、创建一个用户，用户名：
userid默认表空间为ts_userid.
4、为用户userid赋权，包括系统权限（任选3种）、对象权限（任选5种）、管理权限。
5、创建一个dblink，并使用dblink查询该数据库中任意一张表的数据。
6、新建一张表：
si0001.medi_account,字段：
grbh、xm、dwjze、grjze、zje
7、创建触发器，要求：
si0001.medi_account表中dwjze+grjze=zjezje由触发器自动计算，dwjze、grjze发生变化后重新计算zje。
第二章oracle常用数据字典
2.1数据字典
数据库数据字典是一组表和视图结构，它们存放在SYSTEM表空间中，是数据库的重要组成部分，存放数据库所用的有关信息，对用户来说是一组只读的表。
数据字典内容包括：
数据库中所有模式对象的信息，如表、视图、簇、及索引等，分配多少空间
当前使用了多少空间等。
列的缺省值。
约束信息的完整性。
用户的名字。
用户及角色被授予的权限。
用户访问或使用的审计信息。
其它产生的数据库信息。
2.2常用的数据字典
Oracle数据字典中，对象名称多数以"USER."，"ALL."，"DBA."为前缀。
"USER."视图中记录通常记录执行查询的帐户所拥有的对象的信息；
"ALL."视图中记录包括"USER"记录和授权至PUBLIC或用户的对象的信息；
"DBA."视图包含所有数据库对象，而不管其所有者。
视图名
描述
dba_tablespaces
v$tablespace
记录数据库都有哪些表空间及存储参数
仅记录数据中的表空间
dba_data_files
v$datafile（是别名）
记录数据库所有的数据文件及所在的表空间
记录数据文件创建时间等信息
dba_free_space
记录各个表空间剩余空间。
ALL_CATALOG
Alltables,views,synonyms,sequencesaccessibletotheuser
Dba_tables（index）
All_tables
All_views
系统所有表及所在表空间，存储参数。
所有表
所有试图
V$instance
V$database
当前数据库实例名
数据库名字
ALL_COL_COMMENTS
All_tab_comments
Commentsoncolumnsofaccessibletablesandviews
表的备注
ALL_CONSTRAINTS
Constraintdefinitionsonaccessibletables
ALL_DB_LINKS
Databaselinksaccessibletotheuser
ALL_DEPENDENCIES
Dependenciestoandfromobjectsaccessibletotheuser
ALL_SEQUENCES
DescriptionofSEQUENCEsaccessibletotheuser
ALL_SYNONYMS
Allsynonymsaccessibletotheuser
ALL_TAB_COLUMNS
Columnsofalltables,viewsandclusters
ALL_TRIGGERS
Triggersaccessibletothecurrentuser
DBA_USERS
Informationaboutallusersofthedatabase
2.3练习题
1、查看表空间的名称及大小
2、查看数据库库对象
3、查询md3用户下所有带sbjgbh字段的表
第三章PLSQL语言
3.1PL/SQL（ProcedureLanguage&StructuredQueryLanguage）简介
PL/SQ是ORACLE对标准数据库语言的扩展，是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。
PL/SQL代表面向过程化的语言与SQL语言的结合，集成了面向过程语言的过程结构和强大的数据库操作。
由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。
3.2基本数据类型和定义变量
1、数值型，存储数字值，包括整数和浮点数。
可以选择精度和刻度范围。
精度是数值中所有数字位的个数，而刻度范围是小数点右边的数字位的个数（如果刻度范围是个负数，那么就由小数点开始向左边进行计算数字位的个数）
格式：
number（x,y）x:
精度（数字中的数字位数）
y:
刻度范围（小数点右边的数字位数），其中小数点也占一位。
缺省的精度是38,刻度是0。
2、字符型，最长可以到32767字节。
格式：
char（x）,定长数据，在数据库中，如果不足用空格来补齐
Varchar2（x）,变长数据，定义多少位，在数据库中就占多少位
3、日期型
date存储固定长的日期和时间值，包含时分秒
4、布尔型
booleantrue/false/null
5、其他
%TYPE可以将变量类型定义为与数据库中表的字段相同的类型，当
字段的数据类型变化时则变量的类型也相应的变化
%ROWTYPE可以将变量类型定义为与具有相同类型的数据库行
3.3PL/SQL的结构
所有的PL/SQL程序中的基本单位都是块（block）。
PL/SQL程序都是由块组成的，这些块可以顺序出现（一个接一个）也可以相互嵌套（一个在另一个内部）。
每一个PL/SQL块都由声明部分、执行部分和异常处理部分组成：
DECLARE
/*声明部分，主要是定义变量、游标等，可以没有声明部分*/
BEGIN
/*执行部分，过程以及sql语句，实现逻辑功能，这一部分必不可少*/
EXCEPTION
/*异常处理部分，处理错误和异常，可以没有异常处理部分*/
END;
/
/*每一个PL/SQL必须以“/”作为结束并执行*/
3.4流程控制语句
1、条件控制语句
（1）if-then-else
If…then
Elsif…then
Elsif…then
Else
Endif
（2）case表达式
CASEselector
WHENexpression1THENresult1
WHENexpression2THENresult2
WHENexpressionNTHENresultN
[ELSEresultN+1]
END;
2、循环控制语句
（1）Loop循环
Loop
/*处理过程*/
exitwhen[条件]
Endloop;
（2）while循环
While（布尔表达式）loop
/*处理过程*/
Endloop;
（3）for循环
For循环计数器in[reverse]start..endloop
/*处理过程*/
Endloop;
举例：
SQL>declare
2vnumnumber:
=1;
3begin
4forvnumin1..10loop
5dbms_output.put_line（vnum）;
6endloop;
7end;
8/
（4）null语句
可以用null语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性
（5）标号和goto语句
PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。
语法如下：
GOTOlabel;
…
<
举例：
SQL>declare
2vnum1number:
=5;
3vnum2number:
=10;
4vnumnumber;
5begin
6forvnuminreversevnum1..vnum2loop
7dbms_output.put_line（vnum）;
8ifvnum>6then
9gotonext;
10endif;
11endloop;
12<>
13null;
14end;
15/
3.5游标
1、隐式游标
隐式游标不需要声明用于处理INSERT、UPDATE、DELETE和单行的SELECT..INTO语句。
隐式游标的打开、关闭由oracle控制，与OPEN、FETCH和CLOSE命令是无关的。
2、显式游标
（1）定义游标：
就是定义一个游标名，以及与其相对应的SELECT语句。
格式：
CURSORcursor_name[（parameter[,parameter]…）]ISselect_statement;
游标参数只能为输入参数，其格式为：
parameter_name[IN]datatype[{:
=|DEFAULT}expression]
在指定数据类型时，不能使用长度约束。
如NUMBER（4）、CHAR（10）等都是错误的。
（2）打开游标：
就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。
如果游标查询语句中带有FORUPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。
格式：
OPEN
升级会员 