oracle提高.docx

1、oracle提高山大地纬软件股份有限公司Oracle提高二零一三年一月第一章oracle用户及权限、表空间、DBlink、触发器1.1oracle用户权限1.1.1 系统用户系统用户包括sys和system。所有 Oracle 的数据字典的基表和视图都存放在 sys 用户中，这些基表和视图对于Oracle 的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper等角色或权限，是Oracle权限最高的用户。（sysdba和sysoper属于system privilege，也称为administrative privilege，拥有例如数据

2、库开启关闭之类一些系统管理级别的权限）system用户用于存放次一级的内部数据，如Oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。1.1.2普通用户我们可以根据应用的需要建立自己的用户。比如：地纬的社保软件中使用的用户AGED、CSI、BE3、SC、MD3，新建立用户之后默认只有登陆数据库的权限，没有其他权限。创建和删除用户： CREATE USER username IDENTIFIED BY userpassword DEFAULT TABLESPACE tablespace 创建用户： Create user si0001 identified by 12

3、3 default tablespace ts_si; 删除用户： Drop user si0001 cascade;1.1.3角色 角色相当于一个windows中的组，比如我们属于administrators组，我们就拥有administrator的所有权限，oracle为了方便管理也预定义了很多的组： 比如： connect 连上Oracle,做最基本操作 resource 具有程序开发最基本的权限 dba 数据库管理员所有权限 exp-full-database 可把数据库整个备份输出的权限 imp-full-datsabase 可把数据库整个备份恢复输入的权限 给用户分配角色： Con

4、nect system/manager; grant resource to si0001;1.1.4系统权限 Oracle把权限进行了细分，常用的系统权限如下所示： alter index ; alter sequence; alter trigger; create index; create table; create procedure; create sequence; create trigger; create view; drop index; drop table; drop procedure; drop sequence; drop trigger; drop view;

5、 execute procedure; 给用户授予系统权限： 给用户创建触发器的权限： Grant create trigger to si0001; 给用户更改表的权限： Grant alter table to si0001;1.1.5对象权限 我们想让si0001可以访问md3用户的person_info表，我们需要给si0001赋予对象权限 一定要注意：md3是表的拥有者，因此只有md3用户才可以把访问该表的权限给别人 表对象权限包括(七个) Insert ,update, delete, select, index ,alter ,reference 存储过程的有： exec给用户授

6、予对象权限： 把对md3.person_info 的select权限分配给si0001用户： Connect md3/md3; Grant select on md3.person_info to si0001;1.1.6管理权限 Si0001虽然拥有对md3.person_info的select权限，但是si0001并不能把他所拥有的这些权限给其他用户,如果需要它必须具备管理权限能力。 语法如下： Grant select on md3.person_info to si0001 with grant option; 这样si0001用户就可以把对md3的person_info表的selec

7、t权限赋给其他用户了。 不区分用户赋权：有时候我们需要给某个用户操作数据库中所有用户的权限，可以使用下面的语句： grant alter any index to si0001; grant alter any sequence to si0001; grant alter any trigger to si0001; grant create any index to si0001; grant create any table to si0001; grant create any procedure to si0001; grant create any sequence to si00

8、01; grant create any synonym to si0001; grant create any trigger to si0001; grant create any view to si0001; grant create public synonym to si0001; grant create session to si0001; grant create job to si0001; grant manage scheduler to si0001; grant drop any index to si0001; grant drop any table to si

9、0001; grant drop any procedure to si0001; grant drop any sequence to si0001; grant drop any synonym to si0001; grant drop any trigger to si0001; grant drop any view to si0001; grant drop public synonym to si0001; grant execute any procedure to si0001; grant insert any table to si0001; grant select a

10、ny sequence to si0001; grant select any table to si0001; grant update any table to si0001; grant UNLIMITED TABLESPACE to si0001;1.1.7 权限回收 收回部分权限： Revoke select on md3.person_info from si0001; 收回所有权限： Revoke all on md3.person_info from si0001;1.2表空间定义：ORACLE数据库被划分成称作为表空间的逻辑区域形成ORACLE数据库的逻辑结构。一个ORACL

11、E数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。 所有存储在数据库中的数据都必须驻留在某个表空间内。 每个表空间包括一个或多个数据文件，但每个数据文件只能属于一个表空间。创建表空间： c

12、reate tablespace ts_test datafile X:ORA8DATABASEts_test_1.dat size 500Mdefault storage(initial 10k next 128kminextents 2 maxextents 121 pctincrease 10)online;删除表空间： drop tablespace TS_TEST including contents; 给表空间添加数据文件： alter tablespace ts_test add datafile d:oracle8oradatats_test_02.dbf size 200M

13、;修改数据文件的大小： alter database datafile d:oracle8oradatats_test_02.dbf resize 500M; 1.3DBlink定义：当用户要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中必须创建了远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。创建dblink：1.创建dblink的第一种方式，是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库。create public database linkcp3link connect to sc identifie

14、d by sc using cp3;其中cp3link是你创建的dblink名字 sc/sc是登录到远程数据库的用户/密码。然后在本地数据库中通过dblink访问远程数据库cp3中sc.per_natl表,sql语句如下所示select * from sc.per_natlcp3link;2.创建dblink的第二种方式，是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库，create database link cp3link1connect to sc identified by scusing (DESCRIPTION =(ADDRESS_LIST =(ADDRESS

15、 = (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 OR REPLACE TIGGER触

16、发器名 触发时间 触发事件ON表名FOR EACH ROWBEGINpl/sql语句END其中：触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。触发时间：指明触发器何时执行，该值可取：before-表示在数据库动作之前触发器执行;after-表示在数据库动作之后出发器执行。触发事件：指明哪些数据库动作会触发此触发器。如：insert：数据库插入会触发此触发器; update：数据库修改会触发此触发器; delete：数据库删除会触发此触发器。表 名：数据库触发器所在的表。for each row：对表的每一行触发器执行一次。如果没有这一选项，则

17、只对整个表执行一次。一个例子：下面的触发器实现bi3.ac01表中几个字段修改后同时修改ad3.ic03表，以保证这两张表的一致性：CREATE OR REPLACE TRIGGER AD3.TRIG_PER_NATL_UPDATEAFTER UPDATE OF AAC004,AAC003,BAC008,AAC002,BAE025,AAC006ON BI3.AC01REFERENCING OLD AS OLD NEW AS NEWFOR EACH ROW begin update ad3.ic03 a set a.aac002=:new.aac002, a.aac003=:new.aac003

18、, 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，并使用

19、dblink查询该数据库中任意一张表的数据。6、新建一张表：si0001.medi_account ,字段：grbh、xm、dwjze、grjze、zje 7、创建触发器，要求：si0001.medi_account表中dwjze+grjze=zje zje由触发器自动计算，dwjze、grjze发生变化后重新计算zje。第二章oracle常用数据字典2.1数据字典数据库数据字典是一组表和视图结构，它们存放在SYSTEM表空间中，是数据库的重要组成部分，存放数据库所用的有关信息，对用户来说是一组只读的表。数据字典内容包括：数据库中所有模式对象的信息，如表、视图、簇、及索引等，分配多少空间当前使

20、用了多少空间等。列的缺省值。约束信息的完整性。用户的名字。用户及角色被授予的权限。用户访问或使用的审计信息。其它产生的数据库信息。2.2常用的数据字典Oracle数据字典中，对象名称多数以USER.，ALL.，DBA.为前缀。USER.视图中记录通常记录执行查询的帐户所拥有的对象的信息；ALL.视图中记录包括USER记录和授权至PUBLIC或用户的对象的信息；DBA.视图包含所有数据库对象，而不管其所有者。视图名描述dba_tablespacesv$tablespace记录数据库都有哪些表空间及存储参数仅记录数据中的表空间dba_data_filesv$datafile（是别名）记录数据库所有

21、的数据文件及所在的表空间记录数据文件创建时间等信息dba_free_space记录各个表空间剩余空间。ALL_CATALOGAll tables, views, synonyms, sequences accessible to the userDba_tables(index)All_tablesAll_views系统所有表及所在表空间，存储参数。所有表所有试图V$instanceV$database当前数据库实例名数据库名字ALL_COL_COMMENTSAll_tab_commentsComments on columns of accessible tables and views 表

22、的备注ALL_CONSTRAINTSConstraint definitions on accessible tables ALL_DB_LINKSDatabase links accessible to the user ALL_DEPENDENCIESDependencies to and from objects accessible to the user ALL_SEQUENCESDescription of SEQUENCEs accessible to the user ALL_SYNONYMSAll synonyms accessible to the user ALL_TAB

23、_COLUMNSColumns of all tables, views and clusters ALL_TRIGGERSTriggers accessible to the current user DBA_USERSInformation about all users of the database 2.3练习题1、查看表空间的名称及大小2、查看数据库库对象3、查询md3用户下所有带sbjgbh字段的表第三章PLSQL语言3.1 PL/SQL（Procedure Language & Structured Query Language）简介PL/SQ是ORACLE对标准数据库语言的扩展

24、，是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。PL/SQL代表面向过程化的语言与SQL语言的结合，集成了面向过程语言的过程结构和强大的数据库操作。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。 3.2基本数据类型和定义变量1、数值型，存储数字值，包括整数和浮点数。可以选择精度和刻度范围。 精度是数值中所有数字位的个数， 而刻度范围是小数点右边的数字位的个数（如果刻度范围是个负数，那么就由小数点开始向左边进行计算数字位的个数）格式：number(x,y) x:精度（数字中的数字位数）y:刻度范围（小数点右边的数字位数）

25、，其中小数点也占一位。缺省的精度是38, 刻度是0。2、字符型，最长可以到 32767 字节。格式：char(x),定长数据，在数据库中，如果不足用空格来补齐 Varchar2(x),变长数据，定义多少位，在数据库中就占多少位3、日期型date存储固定长的日期和时间值，包含时分秒4、布尔型boolean true/false/null5、其他%TYPE 可以将变量类型定义为与数据库中表的字段相同的类型，当字段的数据类型变化时则变量的类型也相应的变化%ROWTYPE 可以将变量类型定义为与具有相同类型的数据库行3.3 PL/SQL的结构所有的PL/SQL程序中的基本单位都是块(block)。PL

26、/SQL程序都是由块组成的， 这些块可以顺序出现(一个接一个)也可以相互嵌套(一个在另一个内部)。每一个PL/SQL块都由声明部分、执行部分和异常处理部分组成：DECLARE /*声明部分，主要是定义变量、游标等，可以没有声明部分*/BEGIN /*执行部分，过程以及sql语句，实现逻辑功能，这一部分必不可少*/EXCEPTION /*异常处理部分，处理错误和异常，可以没有异常处理部分*/END;/*每一个PL/SQL必须以“/”作为结束并执行*/3.4流程控制语句1、条件控制语句（1）if-then-elseIf thenElsifthenElsif thenElseEnd if（2）cas

27、e表达式CASE selectorWHEN expression1 THEN result1WHEN expression2 THEN result2WHEN expressionN THEN resultN ELSE resultN+1END;2、循环控制语句（1）Loop 循环Loop /*处理过程*/exit when条件End loop;（2）while循环While (布尔表达式) loop /*处理过程*/End loop;（3）for循环For 循环计数器 in reverse start.end loop /*处理过程*/End loop;举例：SQL declare 2 vn

28、um number :=1; 3 begin 4 for vnum in 1.10 loop 5 dbms_output.put_line(vnum); 6 end loop; 7 end; 8 /（4）null语句可以用null语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性（5）标号和goto语句PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：GOTO label;/*标号是用括起来的标识符 */举例：SQL declare 2 vnum1 number :=5; 3 vnum2 number :=10; 4 vnum

29、number; 5 begin 6 for vnum in reverse vnum1.vnum2 loop 7 dbms_output.put_line(vnum); 8 if vnum6 then 9 goto next; 10 end if; 11 end loop; 12 13 null; 14 end;15 /3.5游标1、隐式游标隐式游标不需要声明用于处理INSERT、UPDATE、DELETE和单行的SELECT .INTO语句。隐式游标的打开、关闭由oracle控制，与OPEN、FETCH和CLOSE命令是无关的。2、显式游标（1）定义游标：就是定义一个游标名，以及与其相对应的

30、SELECT 语句。格式：CURSOR cursor_name(parameter, parameter) IS select_statement; 游标参数只能为输入参数，其格式为： parameter_name IN datatype := | DEFAULT expression 在指定数据类型时，不能使用长度约束。如NUMBER(4)、CHAR(10) 等都是错误的。（2）打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。格式：OPEN