ORACLE基本知识培训教材.docx

ORACLE基本知识培训教材.docx

《ORACLE基本知识培训教材.docx》由会员分享，可在线阅读，更多相关《ORACLE基本知识培训教材.docx（23页珍藏版）》请在冰豆网上搜索。

ORACLE基本知识培训教材

ORACLE基本知识培训教材

前言

本教材主要是介绍ORACLE的基本知识，目的是为了让新员工快速了解ORACLE的基本知识，本教材培训及学习时间为一天，培训方式采用课堂讲课、自己学习、考试的方式。

本书只作为入门指南，另附有一些专题学习书，供大家进阶学习用。

ORACLE的环境介绍

●每一个Oracle数据库都是一个数据的集合，这些数据包含在一个或多个文件中。

数据库有物理和逻辑两种结构。

●在开发应用程序的过程中，用户创建表和索以便于数据进行保存和快速检索。

用户可以为对象的名称创建方便的同义词，通过将数据库链接到不同的数据库中查看对象，并且能够限制对象的访问权限。

●用户还可以使用外部表访问当前数据库之外的文件中的数据，就像访问表中的数据一样。

●Oracle实例由被称为系统全局区（systemglobalarea，SGA）的内存区域和相应的后台进程组成。

这些后台进程负责SGA和数据库磁盘文件之间的交互。

在OracleRAC中，可以有多个实例同时使用同一个数据库。

这些实例通常位于不同的服务器上，并且保持着高速互联。

●数据库中的所有逻辑结构都必须存储在数据库中的某个地方。

Oracle系统提供了一个记录与所有对象（对象所有者、定义、相关的优先级等）相关的元数据的数据字典。

对于需要物理空间保存的对象，Oracle系统会在一个表空间中为其分配空间。

●表空间由一个或多个文件组成，数据文件可能是表空间的全部，也可能是一个表空间的一部分。

●为了支持事务，Oracle系统能够动态地创建、管理、撤销数据段。

●OracleDatabase10g系统提出的回收站概念，使得表空间和数据文件对空间的需求发生了变化。

●在Oracle系统的体系结构中，有许多不同的内存区域。

不同的内存区域用于不同的目的。

Oracle的服务器进程和许多后台进程负责在内存区域中写入、更新、读取和删除数据。

●理解内存区域的用途，有助于理解数据库操作期间的数据和进程流程。

●Oracle中的3个主要内存区域是：

●系统全局区域（systemglobalarea，SGA）

●程序全局区域（programglobalarea，PGA）

●用户全局区域（userglobalarea，UGA）

2）本地连接需先安装客户端。

同时修改下图配置文件：

在TNSNAMES.ORA文件中增加

XZ_41_10g=

（DESCRIPTION=

（ADDRESS_LIST=

（ADDRESS=（PROTOCOL=TCP）（HOST=135.64.128.41）（PORT=1522））

）

（CONNECT_DATA=

（SID=ora10）

）

）

用PLSQLDeveloper连接数据库：

3）PLSQLDeveloper常用窗口：

首先最好使用下图：

把窗口调整到旁边。

常用窗口有：

1）command窗口

这里主要执行过程等。

比如execp_do_for_bak;

2）sql窗口：

这里执行sql语句。

如：

ORACEL的基本知识介绍

概念，用图，以及实例的方式讲解：

实例、用户、表、索引、视图、存储过程、触发器

SQL写法，以及一些高级应用案例

DML:

Select

Update

Delete

Truncate

DDL:

create

drop

alter

select语法讲解

●在Oracle系统中，select语句的基本语法格式如下：

–select[distinct][*,column_name[alias],…]

–fromtablename

–where

–groupby

–having

orderby

●如果在select子句后面使用星号（*），则表示检索表中所有的数据，包括所有的列和行。

●当然，也可以在select子句末尾列出将要检索的数据列名。

只要在select子句后面输入相应的列名，即可把指定的列检索出来。

●在执行select语句时要注意，select子句和from子句都是必须的，缺少任何一个子句都是错误的。

●在使用select语句执行操作时，不同数据类型的数据显示的方式也不同。

●在表中，如果某个列中没有值，那么把这种现象称为空值。

空值既不是字符串中的空格，也不是数字中的零，而只是表示没有值，这是一种不能使用的值。

如果某个列既不是主健约束列，也没有定义为notnull，那么该列就允许为空值。

任何数据类型的列都具有这种特性。

●空值的特性非常奇怪。

在算术表达式中，任何包含了空值运算的表达式的结果依然是空值。

●连接查询的理论基础是笛卡尔乘积。

也就是说，连接运算建立在笛卡尔乘积的基础上，所有连接运算的结果都是笛卡尔乘积的子集。

●笛卡尔乘积是指两个或两个以上的表进行相乘运算，结果是这些表中所有行的任意组合。

例如，emp表中有14行数据，dept表中有4行数据，其结果中有56行数据

●内连接把两个表连接成一个临时表，在这个表中，仅包含那些满足连接条件的记录行。

●内连接主要有两种形式，等价连接和非等价连接。

●等价就是在连接列相等的条件下执行的连接操作。

非等价连接是指在连接列不相等的连接条件下执行连接操作。

●在外连接中，某些不满足条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另外一个表中的行。

●这种连接形式在许多情况下是非常有用的。

●外连接只能用于两个表中。

●外连接的语法格式如下：

●selecttable1.column,table2.column

●fromtable1,table2

●wheretable1.column1（+）=table2.column2

●外连接又可以分为左外连接和右外连接。

在oracle中+号放在=右边相当于左连接，而+号放在=左边相当于右连接

●左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。

如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

●RIGHT JOIN 或 RIGHT OUTER JOIN。

 右向外联接是左向外联接的反向联接。

将返回右表的所有行。

如果右表的某行在左表中没有匹配行，则将为左表返回空值。

●自连接就是把某一个表中的行同该表中另外一些行连接起来。

自连接主要用于比较相同的信息，但是所比较的列必须有相同的或者兼容的数据类型。

为了连接同一个表，为该表指定两个别名是非常重要的，这样才可以把该表在逻辑上作为两个不同的表来使用。

●使用union集合运算符，可以把第一个查询语句中的所有数据行与第二个查询语句的所有数据行相加，消除重复行，最后返回结果。

●unionall集合运算符与union集合运算符非常类似，但是两者也有一个显著的差别，即不消除查询结果中重复的数据行，只是简单地把多个查询语句的结果合并在一起

●intersect集合运算符用来执行交集运算，集合的差集运算可以由minus集合运算符来实现

如：

selectTRUNC（89.985,2）fromdual

修改数据:

●向表中插入数据可以使用insert命令。

Insert命令的语法格式如下：

–insertintotable（column[,column…]）

–values（value[,value…]）

在上面的语法格式中，table表示将要插入数据的表名称，column表示将要操纵的表中的列名称，value是将要插入column列的数据值

●在插入数据之前，需要了解该表的结构。

如果在表名称后面没有指定列名称，那么表示按照表定义的列顺序插入数据。

如果在表名称后面指定了列名称，那么按照这些列的顺序插入数据。

●如果表中的某个列允许空值，则可以在插入数据的时候不为该列提供数据。

但是，如果某个列是非空的，那么不能为该列提供空值。

如果为该列提供了空值，那么系统返回相应的错误信息。

●在向表中插入数据时，可以使用系统提供的函数来插入数据还可以使用替换变量插入数据。

●如果表中的数据有错，那么可以使用update命令来更新。

使用update命令更新表中数据的语法格式如下：

●updatetable

●setcolumn=value[,column=value]

●wherecondition

如果希望更新表中的某行数据，那么可以使用where子句来指定将要更新的行。

如果在更新表中的数据时没有使用where子句，那么表示更新表中所有行的数据。

在更新表中数据的时候，可以使用子查询提供值，且可以使用多列子查询

●如果表中的某些数据没有用了，可以删除这些数据。

在OracleDatabase10g系统中，使用delete命令删除表中的数据。

Delete命令的语法格式如下：

–deletefromtable

–wherecondition

●在上面的语法格式中，table参数指定包含将要被删除的数据的表名称，condition参数指定将要删除的数据应该满足的条件。

在删除数据的时候，最好使用where子句指定需要删除的数据应该满足的条件如果没有指定where子句，则删除表中的所有数据

-DDL

●创建表的最简单、最常见的方法的语法格式如下：

–createtable[schema_name.]（

–[default][]

–[,[default][]]

–[,…]

–）;

●约束既可以在createtable语句中创建，也可以在altertable语句中添加。

在这两种定义方式中，定义约束的语法是类似的。

●下面主要介绍使用altertable语句定义约束的基本语法格式：

–altertable[schema.]

–add[constraint[]]

–）;

●表的主键可以确保在一个表中没有重复行。

尽管用户可以创建没有主键的表，但是应该尽量避免这样做。

在表的所有行中，用作主键的列或列的集合必须是唯一的，且不能为null。

●在Oracle系统中，主键可以由一个表的一个列或若干个列构成。

但是，主键最多只能由32个列构成。

●定义主键约束的基本语法格式如下：

–altertabletable_name

–addconstraintprimary_key_nameprimarykey

–primarykey（table_column）;

●在Oracle系统中，表能够通过使用所谓的外键约束进行关联。

外键约束可以放在一个表（子表）中的一个列或一组列上。

作为外键约束定义的组成部分，必须要定义子表的列去参照另一个表（父表）中的一个匹配列或组列。

●如果插入子表的行在外键列中具有非null值，那么在父表行中必须在所引用的列中有相同的值。

如果在父表中没有相应的值，那么这种插入操作将会失败。

通过定义外键约束，可以在表与表之间建立父子关系。

这是数据库中参照完整性的基础。

●定义外键约束的语法格式如下：

–altertablechild_table

–addconstraintforeign_key_name

–foreignkey（child_table_column）referencesparent_table（parent_table_column）;

●唯一性约束可以确保表中的各行，对于值为非null的给定列或列组都具有唯一值。

在使用唯一性约束时，应该遵循下面的原则：

–在表中，可以有多个行在相应的唯一性约束列中具有null值。

–可以使用多个列建立唯一性约束。

–唯一键最多有32个列组成；

–当定义唯一键约束时，Oracle会在后台建立一个唯一性索引来强制唯一性。

●定义唯一性约束的语法格式如下：

–altertabletable_name

–addconstraintunique_key_name

–unique（column_name）;

●在创建表时，必须将表放置在某个表空间中。

该表空间可以使用tablespace子句指定。

tablespace子句是可选的，如果没有明确规定tablespace子句，那么所建立的表就会存放在建立表的用户帐户默认的表空间中。

●为了提高数据库的可靠性，记录数据库中所有数据的改变，可以在创建表时使用logging子句。

这时表示对数据库的操作会产生重做日志。

如果所发生的故障使数据不能从内存传递到数据库的数据文件中，那么就可以从重做日志中获取这些改变。

这样，可以防止数据丢失，提高系统的可用性。

●当在createtable语句中规定了nologging子句时，就认为这个表是非日志记录表。

在这个表上进行的操作可能会导致数据库中很少的日志记录。

对于许多用户来说，这意味着当使用nologging子句创建表时，在表上执行的操作就不会产生重做日志。

事实并非如此。

●Oracle提供了增加新列、修改已有列，以及从已有表中删除列而不影响表中其他数据的能力。

如altertablepeopleadd（phone_numbervarchar2（10）；

●altertablepeoplemodify（ssnnumber（15）notnull）;

●altertablepeopledropcolumnemail;

●altertablemovetablespace

●altertablesetunusedcolumn-altertabletable_namesetunused（col1,col2,...）;-设置无效

●可以使用droptable语句删除表。

droptable语句的语法格式如下：

●droptable[cascadeconstraints];

●truncatetable是用来删除表中所有数据、但是不删除表本身的DDL语句。

同时，该表的索引也被删除。

truncatetable命令能够用于堆组织表、索引组织表、临时表等。

●truncatetable命令的语法格式如下：

–truncatetable[schema.][dropstorage|reusestorage]

droptablegender_tab；

droptablepurge;

●OracleDatabase10g提供了flashbackdrop语句。

通过使用flashbacktabletobeforedrop命令，该语句允许恢复droptable命令的操作效果。

droptablebin_table;

flashbacktablebin_tabletobeforedroprenametonew_bin_table;

ORACLER的一些高级应用

1）现在有两个数据库-DBlink

adb，用户名和密码分别是adb/adb，在本地主机配置的tns名字是tns_a,所在主机a;

bdb，用户名和密码分别是bdb/bdb，在本地主机配置的tns名字是tns_b,所在主机b;

现在需要在adb上面建一个连接到bdb数据库的dblink;

方法：

在a主机上编辑tnsnames.ora文件配置bdb数据库的tns别名tns_b，如下：

tns_b=

（DESCRIPTION=

（ADDRESS_LIST=

（ADDRESS=（PROTOCOL=TCP）（HOST=10.70.9.12）（PORT=1521））

）

（CONNECT_DATA=

（SERVICE_NAME=dbtestb）

）

）

然后创建数据库连接，如下：

createdatabaselink

connecttobdbidentifiedbyidentifiedbybdb

using'tns_b';

2）oracle的同义词总结：

　　从字面上理解就是别名的意思，和视图的功能类似。

就是一种映射关系

创建同义词语句：

　　create public synonym table_name for user.table_name;

　　其中第一个user_table和第二个user_table可以不一样。

　　此外如果要创建一个远程的数据库上的某张表的同义词，需要先创建一个DatabaseLink（数据库连接）来扩展访问，然后在使用如下语句创建数据库同义词：

createsynonymtable_namefortable_name@DB_Link;

　　当然，你可能需要在user用户中给当前用户（user2）授权：

grantselect/delete/updateonuser2

　　2.删除同义词：

　　drop public synonym table_name;

　　3.查看所有同义词：

　　select * from dba_synonyms

　　同义词拥有如下好处：

节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。

3）触发器

是特定事件出现的时候，自动执行的代码块。

类似于存储过程，但是用户不能直接调用他们。

功能：

1、允许/限制对表的修改

2、自动生成派生列，比如自增字段

3、强制数据一致性

4、提供审计和日志记录

5、防止无效的事务处理

6、启用复杂的业务逻辑

触发器类型：

1、语句触发器

2、行触发器

3、INSTEADOF触发器

4、系统条件触发器

5、用户事件触发器

6、这里只谈行触发器，其他请大家使用时自己研究。

例子：

CreateOrReplaceTriggertr_acct_balance_d

Beforedeleteontb_bil_acct_balance_999

foreachrow

declare

s_uservarchar2（20）;

Begin

selectsys.login_userintos_userfromdual;

insertintotb_bil_acct_balance_d（ACCT_BALANCE_ID,ACCT_ID,BALANCE_TYPE_ID,CYCLE_CEIL,CYCLE_FLOOR,USE_ORDER,

EFF_DATE,

EXP_DATE,

CREATED_DATE,

STATE,

FLAG,

BALANCE,

BALANCE_NAME,

LATN_ID,

OCS_FLAG,

SERV_ID,

s_user）

values（:

old.ACCT_BALANCE_ID,:

old.ACCT_ID,:

old.BALANCE_TYPE_ID,:

old.CYCLE_CEIL,:

old.CYCLE_FLOOR,:

old.USE_ORDER,

:

old.EFF_DATE,

:

old.EXP_DATE,

:

old.CREATED_DATE,

:

old.STATE,

:

old.FLAG,

:

old.BALANCE,

:

old.BALANCE_NAME,

:

old.LATN_ID,

:

old.OCS_CHANGE_FLAG,

:

old.SERV_ID,

s_user）;

End;

4）过程

存储过程就是命了名的PL/SQL块，可以被赋予参数，存储在数据库中，然后由另一个应用或者PL/SQL例程调用。

比如

CREATEPROCEDUREmy_procas

BEGIN

NULL;

END;

/

语法：

CREATE[ORREPLACE]PROCEDUREprocedure_name（参数）

IS|AS

[PRAGMAAUTONOMOUS_TRANACTION;]--声明自主事务处理。

[本地变量声明]

BEGIN

执行语句部分

[EXCEPTION]

错误处理部分

END[name];

/

CREATEORREPLACEPROCEDUREmy_procas--ORREPLACE

BEGIN

Dbms_output.put_line（‘Hello,world’）;

END;

/

执行存储过程

setserveroutputon

begin

my_proc

end;

/

直接执行：

executemy_proc

execmy_proc

权限：

表和视图具有SELECT,INSERT,UPDATE,DELETE这样的特权，而过程具有EXECUTE特权。

只有将EXECUTE特权赋予用户，用户才可以运行它。

而将它赋予PUBLIC用户，则所有用户都可以运行。

例子学习。

测试库P_DO_FOR_BAK。

带参数：

请见附件。

5）job:

注意只填what,interval.然后run

6）性能监控：

动态性能视图用于记录当前例程的活动。

启动例程时，oracle会自动建立动态性能视图；停止时，oracle会自动停止。

需要注意，数据字典信息是从数据文件中获得的，而动态性能视图信息是从SGA和控制文件中取得。

通过查询动态性能视图，一方面可以获得性能数据，另一方面还可以取得磁盘和内存结构相关的其他信息。

所有动态视图都是以V_$开始的，oracle为每个动态性能视图提供了相应的同义词。

例如，V_$datafile的同义词为v$datafile;V_$SGA的同义词是v$sga。

通过查询V$FIXED_TABLE,KY显示所有动态性能视图。

当数据库处于不同状态时，可以访问的动态性能视图有所不同

常用：

dba_data_files:

通常用来查询关于数据库文件的信息   

dba_db_links:

包括数据库中的所有数据库链路，也就是databaselinks。

dba_extents:

数据库中所有分区的信息                     

dba_free_space:

所有表空间中的自由分区

dba_indexs:

关于数据库中所有索引的描述                

dba_ind_columns:

在所有表及聚集上