Oracle数据管理系统

英伦Harsh

一、简要说明
Oracle体系结构相当庞大，包括数据库服务器领域、应用服务器领域和应用开发领域的许多知识，因此我们在本次培训过程中不可能也没有必要面面俱到，这次培训不想讲太多的理论性知识，仅从实际应用角度出发，针对我们在实际开发过程中经常遇到的一些技术知识进行边讲解边演示。
二、Oracle数据库管理系统简介
Oracle是世界上最早的商品化的关系型的数据库管理系统之一，是数据库专业厂商Oracle公司的核心产品，也是当今关系型数据库产品市场上应用最为广泛、功能强大、具有面向对象特点的数据库系统。
Oracle数据库的推出最早可达到上世纪70年代，从那以后，一直领导着数据库发展的新潮流，融汇先进的技术并预见性地领域了全球数据库技术的发展。据统计，Oracle在全球数据库市场的占有率达33.3%，在关系型数据库市场上的拥有42.1%的市场份额，在Unix高端应用领域市场上占据66.2%的份额，现在，包括惠普、波音和通用电气等众多大型跨国企业都利用Oracle电子商务的套件来运行自己的业务。
作为一个通用的数据库管理系统，Oracle具有完整的数据库管理功能，包括存储海量数据、定义和操纵数据、并发控制、安全性控制、完整性控制、故障恢复、与高级语言接口等功能。
Oracle到9i版本后，支持各种分布式处理功能，特别是支持Internet处理，还支持面向对象处理的功能，支持类、方法和属性、空间数据等概念，使得Oracle产品逐渐成为了一种对象 — 关系型数据库管理系统。
Oracle发展到10g以后，采用一种叫网格计算的技术。这样用户和服务者都在网络上，对于服务提供商来说，服务变的更为可靠，负载均衡更为灵活，具有无限的扩展能力。对于用户来说，他只需要提出他的请求就可以了，无需知道是在哪个网上，哪个服务端在为他提供服务。具体来讲，Oracle 10G的网格计算包涵四个方面的内容：存储网格、数据库网格、应用程序网格和网格控件。
三、Oracle体系的组成部分简介
Oracle体系结构相当庞大，它是Oracle公司推出的针对电子商务应用的一整套解决方案，整体由三大体系组成。
（一）数据服务器体系（Oracle Database）
Oracle9i和10g的数据库服务器从版本上分企业版、标准版和个人版，共有以下18个组件。
1．JServer Enterprise Edition                        企业版支持Java的Web Server
2．InterMedia                                                互联网媒体服务
3．Object Option                                        目标功能
4．Networking Kit                                        网络包
5．Object for OLE                                        目标
6．Advanced Replication Option                高级复制功能
7．Distributed Option                                分布式功能
8．Parallel Query Option                        并行查询功能
9．Sql*Plus                                                查询工具
10．Workflow Enterprise Edition                工作流企业版
11．Advanced backup & recovery                高级备份恢复
12．Advanced Queuing                                高级队列
13．Connection Pool                                连接池
14．64 bit Option                                        64位功能
15．ODBC Driver                                        ODBC驱动
16．OCI                                                        调用接口
17．Enterprise Manager                                企业管理器
18．Enterprise Backup Utility                        企业备份工具
（二）应用服务器体系（Oracle Application Server）
Oracle9i和10g的数据库服务器用于构建互联网应用，提供企业级的分布应用解决方案，有以下11个组件。主要产品有：
1．Http/J2EE(Apache+)                                Apache HTTP服务器
2．Portal                                                        接口
3．OEM                                                        Oracle企业管理器
4．Web & DB Cache                                Web和数据库缓冲区
5．Form Service                                        表单服务
6．Report Service                                        报表服务
7．Discoverer Plus                                        数据挖掘
8．Email                                                        Email支持
9．Internet Directory                                互联网目录
10．Workflow                                                工作流
11．Application InterConnet ToolKit         应用连接包
（三）开发工具套件（Oracle Developer）
Oracle的开发工具套件提供了以下9个套件
1．Designer                                                设计器
2．Form Developer                                        表单开发工具
3．Report Developer                                报表开发工具
4．JDeveloper                                                开发工具
5．Portal                                                        接口
6．Discoverer Administration Edition        企业版数据挖掘器管理
7．1 Licence Discoverer Disktop Edition        1个用户的数据挖掘器
8．Warehouse Builder                                数据仓库建立器
三、Oracle的安装
Oracle的安装一般有数据库服务器的安装、客户端的安装。一般情况下，我们只需安装一台Oracle数据库服务器，用户端只需安装客户端连接服务器，然后就可以在统一的服务器上建立自己的表空间与用户。关于Oracle的安装在这里不详细介绍了，我们在这里需要注意两上概念：全局数据库名与数据库标识符（SID）的区别。
全局数据库名主要用于在分布式数据库系统中区分不同服务器上的数据库例程，比如在上海的例程可命名为Oracle.ShangHai，北京的为Oracle.BeiJing，这样即使数据库例程名相同，在分布式网络中也能区分。
数据库标识符（SID）主要用于区分同一台服务器上的不同数据库例程。前者为外部区分，后者为内部区分。在网络配置管理中，服务名一般是指全局数据库名，而网络服务名一般是指SID。比如说我们公司的数据库服务器的全局数据库服务名为：Oracle10、我们在本地针对船舶管理系统的SID为ShipMis。
五、Oracle数据库结构介绍
（一）逻辑结构
Oracle数据库按逻辑结构从大到小分有：数据库、表空间、逻辑对象（包括表、索引、视图、过程等21个）、数据段、数据区间、数据块。
一个数据库服务器可有多个数据库；一个数据库可有多个表空间；一个表空间可以有多个表；一个表可以有多个数据段；一个数据段可以有多个数据区间；一个数据区间可以有多个数据块。
（二）一些逻辑对象的介绍
1．过程：也就是存储过程对象。
2．程序包：程序包分别二个部分，一个是程序包，另一个是程序包体。程序包相当于Java中的接口定义，它是对一组存储过程进行定义，但并不对其进行实现。而程序包体则是对程序包中定义的过程进行具体实现。程序包与程序包体一一对应。程序包与过程的区别是：过程只能是一个单独的存储过程，而程序包是一组存储过程的集合。我们可以使用程序包对存储过程进行分门别类地管理。比如我们如果要对船舶管理系统进行后端开发的话，我们可以把针对PMS的所有过程放在一个PKG_PMS包下统一管理，而把备件物料的所有过程放在一个PKG_SPARE包下进行统一的管理。
3．簇：将一些互相关联的具有相同字段的数据表或索引集中存储的一种管理。
4．数据库链接：在分布式环境中链接其它节点的数据库服务器。
5．序列：它相当于在Oracle中建立的表的自增长字段,数据表可以利用它做为字段。
6．同义词：在Oracle中访问数据表的形式是“用户名.表名”，同义词可以用一个别名来代替它。比如说我们定义一个同义词a指向表：T_Component_Type_Parm，那么select * from T_Component_Type_Parm语句与select * from a的执行效果是一样的。
六、表空间的建立
为了提高数据库服务器管理和运行效率，Oracle使用表空间这个虚似概念来管理逻辑对象，用户可以将不同的逻辑对象存放在不同的表空间下，可以理解表空间就是Oracle数据库的文件夹，用户建立的各种数据库对象应该存放在相应的表空间下，Oracle9i和10g数据库服务器安装完毕后，将自动建立以下几个默认表空间。分别是：
1．CWMLITE                        OLAP
2．DRSYS                                存放与工作空间设置相关信息
3．EXAMPLE                        实例表空间，存放实例信息
4．INDEX                                索引表空间，存放索引信息
5．OEM_REPOSITORY       
6．SYSTEM                        系统表空间，存放管理信息
7．TEMP                                临时表空间，存放临时表
8．TOOLS                                工具表空间，存放工具软件所需要的数据库对象
9．UNDOTBS                        回滚表空间，存放数据库恢复信息
10．USERS                                用户表空间，存放用户私有信息
我们建立一个用户时如果不指定表空间，默认选择的表空间是USERS、临时表空间是TEMP。但一般情况最后是建立自己的表空间，以便单独进行管理。表空间的建立有如下两种方式：
（一）使用企业管理器建立表空间
（二）使用PLSQL命令来建立表空间
（1）建立
create tablespace tablespacetest datafile
                'd:\oracle\oradata\oracle10\tablespaces\test01.dbf' size 10M,
                'd:\oracle\oradata\oracle10\tablespaces\test02.dbf' size 10M,
                'd:\oracle\oradata\oracle10\tablespaces\test03.dbf' size 10M;
（2）扩大现有表空间
alter database Oracle10 tablespacetest ' d:\oracle\oradata\oracle10\tablespaces\test01.dbf ' resize 20M;
（3）删除表空间
drop tablespace tablespacetest including contents
七、用户的建立
Oracle中的用户就相当我们的业务数据库。我们建立数据库时需要指定用户名与口令。
（一）企业管理器建立用户
（二）使用PLSQL命令来建立用户
        （1）建立用户
    create user userTest identified by usertest
        default tablespace tablespacetest
        temporary tablespace temp
        quota unlimited on tablespacetest；
        （2）删除用户
        drop user userTest cascade；
八、Oracle中Net命令服务的配置
我们在建立一个用户之后，可以针对此用户建立一个SID实例名，可使用Oracle的Net Manager工具建立。
九、Oracle中的角色与权限
Oracle提供了两种类型的权限：系统权限和对象权限。
（一）系统权限
系统权限提供了在Oracle数据库执行多种任务的能力，系统权限不能访问数据库内的表和视图。通常系统权限可以用来许可或者限制DDL语句的执行，Oracle中定义了系统权限多达120多个，常用的系统权限有：
1．CREATE SESSION         允许用户在数据库上注册。
2．CONNECT                         允许注册和创建常用的数据库,CONNECT权限是一个预定义角色。
3．RESOURCE                         传送UNLIMITED TABLESPACE,也就是这种权限自动授权给用户。
4．CREATE TABLE                 允许用户创建表。
5．CREATE VIEW                 允许用户创建视图。
6．CREATE SEQUENCE         允许用户创建序列。
7．CREATE PROCEDURE 允许用户创建过程，函数与包。
8．CREATE TRIGGER         允许用户在其所属的表创建触发器。
9．CREATE SYNONYM         允许用户创建专用的同义词。
授予系统特权的语法：        grant system_privilege to username [with grant option]
去掉系统权限语句：                revoke system_privilege from usename
举例：
grant create table,create view,create procedure,create trigger,create sequence,create session,
select any dictionary,unlimited tablespace to userTest;
（二）对象权限
对象权限用来指定访问用户所属的数据类型。这个用户可以拥有多种对象如表，视图等。对象权限可以用来许可或阻止DML语句的执行。常见的有SELECT、INSERT、UPDATE、DELETE、EXECUTE（可以应用于PL/SQL过程、函数、程序包以及其他可执行元素如Java类）
授予对象权限的语法：grant object_privilege on object_name to username[with admin option]
取消对象权限的语句：revoke object_privilege on object_name from username
（三）角色管理
Oracle使用角色来限定各种用户的权力，在系统中可以有许多用户，各种用户有各种不同的角色，而角色拥有不同的权力。有时与其赋一组权限给每一个用户，不如先赋一定的权限给一个角色，然后把这个角色赋给用户，这样就可以实现动态的权限管理，当一组用户的权限必须改变时，只需改变角色的权限即可，而不用每个用户都一一改变权限。
Oracle中的角色有预定义角色和用户自定义角色。常见的预定义角色如：
1．CONNECT                                        登录数据库
2．RESOURCE                                        创建新对象（如表、视图等）
3．DBA                                                执行数据库管理员的功能，如创建新用户
4．EXP_FULL_DATABASE                输出完整的数据库
5．IMP_FULL_DATABASE                输入完整的数据库
6．SELECT_CATALOG_ROLE                查看数据字典视图
用户自定义的角色的创建必须具有管理权限。
定义：CREATE ROLE rolename；
为角色授予权限语句：GRANT CREATE TABLE TO rolename；
删除角色：DROP ROLE rolename
十、Oracle数据库的备份
Oracle中的备份方式可分两大类，一种是逻辑备份，另一种是物理备份。
（一）逻辑备份
逻辑备份就是我们常用的导入（Import）和导出备份（Export），这种备份方式能够针对对象进行备份，能够跨平台实施备份操作并适移数据，使用这种备份时，数据库可以不关闭。这种备份一般用于有规律的日常备份。采用这种方式我们可以进行表方式和用户方式的备份。
Export备份方式：
1．C:\>exp shipmis/shipmis@shipmis;
2．数组缓冲区大小4096:>按回车;
3．导出文件:EXPDAT.DMP>d:\shipmis.dmp;
4．U(用户),或(3)T(表)2)U>
5．导出权限(yes/no):yes>
6．导出表数据(yes/no):yes)
7．压缩区(yes/no):yes)
Import恢复方式
1．C:\>Imp shipmis/shipmis@shipmis
2．导入文件:EXPDAT.DMP>C:\shipmis.dmp
3．输入插入缓冲区大小（最小为8192>30720）>
4．只列出导入文件的内容(yes/no):no>回车(要导入内容必须选择no)
5．由于对象已存在,忽略创建错误(yes/no):no>yes
6．导入权限(yes/no):yes>yes
7．导入表数据(yes/no):yes>回车
8．导入整个导出文件(yes/no):no>yes(最好选yes)
（二）物理备份—冷备份
冷备份实质上是在关闭数据库的一种文件式的考贝备份。使数据库处于关闭状态时，把数据相关文件转移到安全的区域，当数据库遭到破坏，再从安全区域将备份的数据库相关文件拷贝回原来的位置。由于是在数据库不提供服务的关闭状态，所以称为冷备份。冷备份具有快速，方便，以及高效等特点。一次完整的冷备份步骤应该是：
1．关闭数据库（shutdown normal或shutdown abort）；
2．复制数据库相关文件
利用操作系统命令拷贝数据库的所有的数据文件、日志文件、控制文件、参数文件到一个安装区域。
3．重新启动数据库（startup）
恢复的时候，相对比较简单了，我们停掉数据库，将文件拷贝回相应位置，重启数据库就可以了。
（三）物理备份—热备份
热备份与冷备份的区域就是它是在归档模式下进行的，数据库仍处于打开和使用状态。当我们需要做一个精度比较高的备份，而且我们的数据库不可能停掉时，这时我们就需要归档方式下的备份，就是热备份。热备份可以非常精确的备份表空间级和用户级的数据，由于它是根据归档日志的时间轴来备份恢复的，理论上可以恢复到前一个操作，甚至就是前一秒的操作。热备份相对比较复杂，对于硬件以及操作人员的要求都比较高。在这里不做详细介绍。
十一、表、视图、程序包、序列介绍
（一）表
create table tb
    (
      equipid number,
      equipname nvarchar2(30) NOT NULL,
      constraint EQUIP_PK primary key(equipid),--主键
      constraint EQUIPNAME_UK unique(equipname) --唯一索引
    )
    建表时把表中不同范围的数据放在不同的表空间上以加快查询速度，这种情况一般在表数据量特别大（上亿条记录）时使用。
   create table tb
    （
       id number partition by range(id),(partition p1 values less than(10) tablespace pt1,
                                         partition p2 values less than(20) tablespace pt2)
    ）
（二）视图
在Oracle中有四种类型的视图：关系视图、内嵌视图、对象视图和物化视图。我们最常用的视图就是关系视图。
建立关系视图的语法：create or replace view view_name as query
删除视图语法：drop view view_name
     在查询中查询视图的语法：SELECT text FROM user_views WHERE view_name='VIEW_NAME'；VIEW_NAME必须大写。
（三）程序包
在Oracle中有四种类型的视图：关系视图、内嵌视图、对象视图和物化视图。我们最常用的视图就是关系视图。
我们前边说过，程序包是过程的一种改进，它可以对存储过程进行分门别类地进行管理。
建立程序包的语法为：
1．程序包定义
create or replace package employee_pkg
as
    procedure print_ename(p_empno in number)；        --第一个存储过程接口
    procedure print_sal(p_empno in number)；                --第二个存储过程接口
end employee_pkg；
2．程序包体的定义
create or replace package body employee_pkg
as
    procedure print_ename(p_empno number)  --第一个过程的实现
    is
    begin
      dbms_output.put_line(p_empno);
    end;

    procedure print_sal(p_empno number)    --第二个过程的实现
    is
      l_res number;
    begin
      l_res:=p_empno*p_empno;
      dbms_output.put_line(l_res);
    end;
end employee_pkg;
3．调用程序包：
begin
     employee_pkg.print_ename(100);
     employee_pkg.print_sal(100);
end;
在Oracle的SQLPLUS中还可以使用如下语法：
execute employee_pkg.print_ename(100);
execute employee_pkg.print_sal(100);
在应用系统中调用程序包：
(1)        NET中的调用如下：
OleDbCommand Cmd = New OleDbCommand("KG.Procedure", con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("varCaseName", OleDbType.VarChar).Value = cboAccidentName.Text;
con.Open();
Cmd.ExecuteNonQuery();
con.Close();
(2)        Java中的调用如下：
Connection con=DriverManager.getConnection();
Statement smt=con.createStatement();
CallableStatement cst=con.prepareCall("{call GetDataForYYT(?,?,?,?)}");

cst.setString(1,txtFreightBill.getText()); //设置输入参数
cst.registerOutParameter(2,java.sql.Types.INTEGER);   //注册输出参数的JDBC类型
cst.registerOutParameter(3,java.sql.Types.DECIMAL,2);
cst.registerOutParameter(4,java.sql.Types.DECIMAL,2);
cst.execute();
txtSumPieces.setText(String.valueOf(cst.getInt(2)));   //把返回的值赋给文本框
txtSumJWeight.setText(String.valueOf(cst.getFloat(3)));
txtLastResult.setText(String.valueOf(cst.getFloat(4)));
con.close();
（四）序列
我们在建立表时经常用自增长的方式为表指定主键，在SQL Server中可以直接在建立字段时指定自增长类型。在Oracle中的实现方式略有不同，Oracle中要使用序列的方式来实现自增长。序列是Oracle中的一个对象，我们需要建立一个序列对象，然后在每个表中用触发器的方式来实现自增长功能。原则上一个序列最好只针对一个表。触发器的写法如下：
begin
  select seq_name.nextval into :new.tbId from dual;
end;
也可以不建触发器，在我们的SQL语句中命名seq_name.nextval自动产生一个自增长值。
十二、PL/SQL简介
PL/SQL语言是标准SQL语言的一种扩展，它是Oracle公司在继承标准SQL语言基础上，又进行了相应的扩展从而更加适应Oracle数据库管理的一种过程性语言。
SQL是结构化的通用查询语言的缩写，它是国际化标准组织（ISO）定义的一种通用查询语言，许多数据库厂商都是在此基础上进而扩展了它们自己的语言，如Microsoft公司的Transaction-SQL，Oracle公司的PL/SQL等。
（一）PL/SQL结构块
declare
      --变量声明部分(注意,任何变量声明都必须放在这部分)
begin
      --主体语句部分
exception
      --异常处理部分
end;
在定义过程、程序包、函数和触发器时，不使用关键字DECLARE。
举例如下：
Declare
num1 number;
num2 number;
num3 number;
var1 varchar2(20);
        var2 varchar2(20);
        var3 varchar2(20);
begin
num1:=’100’;
num2:=’10’;
num3:=num1/num2;
dbms_output.put_line(num3);

          var1:='Visual';
          var2:=' Basic';
          var3:=var1 || var2;
          dbms_output.put_line(var3);
exception
when others then
     dbms_output.put_line('错误');
end;
（二）运算符
1．算术运算符：+、-、*、/、**（求幂，如10**5=10的5次方）
2．关系运算符：=、!=（或者<>）、<、>、<=、>=、LIKE(通配符是%和_)、BETWEEN、IN、IS NULL
3．逻辑运算符：NOT、AND、OR （x=1 OR x=2 OR x=3或用x in（1,2,3）表达式代替，从而使用代码更容易阅读）
4．连接运算符：||
（三）内置函数
1．number函数
ABS(n),SIN,COS,TAN,ASIN,ACOS,ATAN,CEIL(n),FLOOR(n),LN(n),EXP(n),LOG(m,n),MOD(m,n),POWER(m,n),ROUND(m,n),SIGN(n),SQRT(n),trunc函数,载取函数,载去小数部分,如trunc(25.59)=25,trunc(25.596,2)=25.59
2．Date函数
（1）add_months(date_value,number)：算出一个日期的在增加(或减少)指定月数后的日期。
（2）current_date：当前日期(current_timestamp，localtimestamp)，还有sysdate函数。
（3）extract(date_field from date_value)：返回一个日期的间隔值(年月日时分秒)date_field取值有:YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
3．字符函数
（1）initcap(value)：将一个字符串第一个字母转换成大写，其余的转换为小写，initcap('visual basic')返回的是'Visual Basic'，而不是'Visual basic'。
（2）instr(value,search_string,start_position,occurence)：在一个字符串中找出另一个字符串从指定起始位置的第occurence次出现的位置，instr('Visual Basic','Basic')=8。
（3）length(value)：返回一个字符串的长度。
（4）lower(value)(upper(value))。
4．格式转换函数
（1）to_char(日期转换为字符),例:to_char(sysdate,'yyyy.mm.dd')='2003.09.28'
（2）to_char(数值转换为字符),例:to_char(49000,'$99,999.99')='$49,000.00'
（3）to_date(),将字符转换为日期型数据(注意,在此函数中,格式掩码是必须的)
（4）to_number(),将字符型数据转换为数值型
（四）语法
（1）IF...ELSE...ENDIF条件语句的使用，如：
declare
  l_num number:=&NUM;
begin
  if l_num=1 then
    dbms_output.put_line('You selected one');
  elsif l_num=2 then
    dbms_output.put_line('You selected two');
end;
（2）loop…end loop循环，如：
  declare
    l_loops number:=0;
  begin
    dbms_output.put_line('Before my loop');
    loop
      if l_loops>10 then
        exit;
      end if;
      dbms_output.put_line('Looped'||l_loops||'times');
      l_loops:=l_loops+1;
    end loop;
    dbms_output.put_line('After my loop');
  end;
（3）for...end loop循环，如：
  declare
  begin
    for idx in 1..10 loop
      dbms_output.put_line(idx);
    end loop;
  end;
（4）while循环
begin
   loops:=1;
   while loops<=10 loop
     dbms_output.put_line(loops);
     loops:=loops+1;
   end loop;
end;