1、任务 4 Oracle 10g 数据库及其应用掌握 Oracle 10g、安装、基本操作，及其建表、数据库关系 图、 查询、视图、存储过程、函数、触发器等操作。按照网络商店数据库结构完成相关练习 操作。活动 1 认识 Oracle 10g Oracle 10g 的基本介绍和特点ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER) 或 B/S 体系结构的数据库之一。比如 SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据库管理系统，作为一
2、个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。Oracle 具有如下特点 1、完整的数据管理功能： 数据的大量性 数据的保存的持久性 数据的共享性 数据的可靠性 2、完备关系的产品： 信息准则 -关系型 DBMS 的所有信息都应在逻辑上用一中方法，即表中的值显式地表示； 保证访问的准则 视图更新准则-只要形成视图的表中的数据变化了，相应的视图中的数据同时变化 数据物理性和逻辑性独立准则 3、分布式处理功能： 4、用 ORA
3、CLE 能轻松的实现数据仓库的操作。这是一个技术发展的趋势，不在这里讨论。活动 2 Oracle 10g 安装 首先将 ORACLE 10g 的安装光盘放入光驱，自动运行，会出现下图安装界面。 安装-安装方法界面在这里你要指定自己的安装方法，ORACLE 提供了两种安装方法：选择安装方法包括：基本安装（I）和高级安装（ A） 基本安装（I）：使用标准配置选项（需要输入的内容最少）执行完整的 10g 安装。注意：此选项使用文件系统进行存储，并将一个口令用于所有数据库帐户（SYS、SYSTEM、SYSMAN、DBSNMP ） 。要设置安装的主目录、安装类型和创建启动数据库。 高级安装（A）：高级选
4、项，为不同的帐户设置不同的口令，选择数据库语言，产品语言，自动备份，定制安装以及备用存储选项（自动存储管理） 。选择基本安装，数据数据库口令和确认口令，如图 3-35 所示：图 3-35 Oracle 10 安装方法选择 选择下一步，测试产品安装的先决条件，如图 3-36 所示图 3-36 Oracle 10 安装产品特定的先决条件检查 选择下一步，显示安装信息，如图 3-37 所示：图 3-37 Oracle 10 安装概述 选择安装，系统进入安装界面，如图 3-38 所示：图 3-38 Oracle 10 安装界面 选择下一步，进入到安装配置界面，如图 3-39 所示：图 3-39 Ora
5、cle 10 数据库安装 进入口令管理界面，如图 3-40 所示。图 3-40 Oracle 10 口令管理对 SYS，SYSTEM 进行口令管理，输入新的密码，同时对用户 scott 用户名输入新的密码 tiger，并且激活用户。 安装结束，显示已安装产品，这时服务器端安装就已经完成，如图 3-41 所示：图 3-41 Oracle 10 安装完成活动 3 Oracle 10g 卸载Oracle 10g 安装是个比较麻烦的事情，读者可以按照下面的步骤，卸载 Oracle 10g。卸载步骤： 开始设置控制面板管理工具服务 停止所有 Oracle 服务。(没有起动的就不用停用了 ) 开始程序Or
6、acle - OraDb10g_home1Oracle Installation ProductsUniversal Installer卸装所有 Oracle 产品；(不要按下一步，在上面有一个卸载产品点那个按扭！) 运行 regedit，选择 HKEY_LOCAL_MACHINESOFTWAREORACLE，按 del 键删除这个入口。如果是别的盘那就找到它删除！ 运行 regedit，选择 HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices，滚动这个列表，删除所有 Oracle 入口 如果你按装是用的默认的目录在 C 盘下有一个以 ORACL
7、E 命名的文件夹，删除它！ 重新启动计算机，重起后才能完全删除 Oracle 所在目录 删除与 Oracle 有关的文件，选择 Oracle 所在的缺省目录 C:Oracle，删除这个入口目录及所有子目录，并从 Windows 2000 目录（一般为 C:WINNT）下删除以下文件ORACLE.INI 、oradim80.INI WIN.INI 文件中若有ORACLE的标记段，删除该段活动 4 PL/SQL 安装与使用PL/SQL（Procedural Language/SQL）是甲骨文公司专有的 SQL 扩展语言，应用在甲骨文公司的 Oracle 数据库系统。一些的 SQL 数据库管理系统也
8、提供了类似的扩展 SQL 语言。 安装 PL/SQL，点击 plsqldev.exe 启动安装向导，选择提取存放的位置，一路下一步即可，中文补丁可以到官方下载，如图 3-42 所示：图 3-42 PL/SQL 安装界面 PL/SQL 安装成功，如下图 3-43 所示。图 3-43 PL/SQL 安装完成 安装完成以后，桌面出现如下图标，单击 PL/SQL，可以进行账户登录，如图 3-44 所示。图 3-44 启动 PL/SQL 活动 5 Oracle 10g 应用 创建数据库安装完成 Oracle 数据库服务器后，就可以根据需要在数据库服务器中创建数据库。一个完整的数据库通常由物理结构、逻辑结
9、构、内存结构和进程结构等构成。 选择“开始-程序-Oracle-配置和移植工具- Database Configuration Assistant ”，启动DBCA(DBCA 提供典型的数据库类型标准模板)，如下图 3-45 所示。图 3-45 启动 Database Configuration Assistant选择“Database Configuration Assistant ”出现如下图 3-46 窗体所示图 3-46 Database Configuration Assistant 主界面 点击“下一步”按钮，进入下图的操作选择对话框，选择要进行的操作，如图 3-47 所示。图 3
10、-47 Database Configuration Assistant 创建数据库在该对话框中选择“创建数据库” 。 选择创建数据库，单击“下一步” ，出现下图所示的数据库模板选择对话框，如图 3-48 所示。图 3-48 Database Configuration Assistant 数据库模板选择 选择“一般用途” ，单击“下一步”按钮，进入下图所示，进入数据库标识设置对话框，如图 3-49 所示。图 3-49 Database Configuration Assistant 数据库标识设置“全局数据库名”和“SID” 使用 Enterprise Manager 配置数据库。这里选择默
11、认，单击“下一步” ，如图 3-50 所示。图 3-50 Database Configuration Assistant 管理选项 设置数据库身份证明，如图 3-51 所示。图 3-51 Database Configuration Assistant 数据库身份验证选择“所有账户使用同一口令” ，输入口令 选择数据库的存储机制，如图 3-52 所示。图 3-52 Database Configuration Assistant 存储选项使用默认选择的“文件系统” 指定要创建的数据库文件的位置，如图 3-53 所示。图 3-53 Database Configuration Assistan
12、t 数据库文件位置设置选择默认的“使用模板中的数据库文件位置” 。 默认选择数据库恢复选项。如图 3-54 所示。图 3-54 Database Configuration Assistant 恢复配置 创建示例方案，如图 3-55 所示。图 3-55 Database Configuration Assistant 示例数据库这里不选择创建“示例方案” 初始化参数设置，如图 3-56 所示：图 3-56 Database Configuration Assistant 初始化参数所有参数选择默认设置。 设置完成数据库相关初始化参数后，进入数据库存储设置对话框。在该对话框中，可以进行与数据库物
13、理结构和逻辑存储相关的设置，能够查看或修改数据库控制文件、表空间数据文件、重做日志等存储结构的信息，如图 3-57 所示。图 3-57 Database Configuration Assistant 数据库存储设置 创建数据库，如图 3-58 所示。图 3-58 Database Configuration Assistant 数据库创建选项单击“完成” ，完成 Oracle 数据库创建工作，如图 3-59 所示。图 3-59 Database Configuration Assistant 数据库安装界面 数据库创建完成以后，显示创建完成结果，可以进入口令管理对用户的口令进行设置，便于管理
14、，如图 3-60 所示。图 3-60 Database Configuration Assistant 数据库安装完成口令管理如下图所示，设置 Scott 用户名的口令，并解锁，如图 3-61 所示：图 3-61 Oracle10 数据库口令管理 数据库创建完成以后，可以使用 PL/SQL 用 Scott 打开数据库，如图 3-62 所示：图 3-62 PL/SQL 登录界面 登陆成功如下图 3-63 所示。图 3-63 PL/SQL 登录主界面 根据需求创建用户表根据学习情境 2，任务 6 数据库的设计举例里面的活动 2 E-R 图的设计，完成数据表的创建工作。数据库名称：Shopping表
15、名： 客户信息表(bClient)字段名称 英文名称 类型 长度客户编号(GUID) User_ID 整数客户姓名 User_Name 字符串类型 30性别 User_Sex 字符串类型 4出生日期 User_Birthday 日期类型VIP 客户类型 User_Type 整数地址 User_Address 字符串类型 100联系电话 User_Tel 字符串类型 20表名： 客户订单表(bOrder)字段名称 英文名称 类型 长度订单编号 Order_ID 整数 50下单日期 Order_Date 日期类型客户编号 User_ID 整数 50商品编号 Goods_ID 整数 50订单金额 O
16、rder_Money 双精度数值 12,2表名： 商品信息表(bGoods)字段名称 英文名称 类型 长度商品编号 Goods_ID 整数 50商品名称 Goods_Name 字符串类型 50价格 Goods_Price 双精度数值 12,2 创建 bClient 数据表，如下图所示。在左面的工具栏选择“Tables” ，单击右键，选择“New” ，在 Name 输入表名，如图 3-64所示。图 3-64 PL/SQL 创建表这里表名是：bClient，字段类型设置如下图 3-65 所示。图 3-65 PL/SQL 编辑表结构表主键设置如下图，选择 user_id 设置为表的主键，如图 3-6
17、6 所示。图 3-66 PL/SQL 编辑表主键SQL 脚本创建如下：- Create tablecreate table BCLIENT(USER_ID INTEGER not null,USER_NAME NVARCHAR2(30),USER_SEX NVARCHAR2(4),USER_BIRTHDAY DATE,USER_TYPE INTEGER,USER_ADDRESS NVARCHAR2(100),USER_TEL NVARCHAR2(20)tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 64Kminex
18、tents 1maxextents unlimited);- Create/Recreate primary, unique and foreign key constraints alter table BCLIENTadd constraint USER_ID_KEY primary key (USER_ID)using index tablespace USERSpctfree 10initrans 2maxtrans 255storage(initial 64Kminextents 1maxextents unlimited); 创建客户订单表(bOrder)，如下图 3-67 所示图
19、 3-67 PL/SQL 编辑客户订单表SQL 脚本如下：- Create tablecreate table BORDER(ORDER_ID INTEGER not null,ORDER_DATE DATE,USER_ID INTEGER,GOODS_ID INTEGER,ORDER_MONEY NUMBER(12,2)tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents unlimited);- Create/Recreate primary, unique and
20、 foreign key constraints alter table BORDERadd constraint KEY_ORDER_ID primary key (ORDER_ID)using index tablespace USERSpctfree 10initrans 2maxtrans 255storage(initial 64Kminextents 1maxextents unlimited); 创建商品信息表(bGoods)，如下图 3-68 所示图 3-68 PL/SQL 编辑商品信息表SQL 脚本如下：- Create tablecreate table BGOODS(GO
21、ODS_ID INTEGER not null,GOODS_NAME VARCHAR2(100),GOODS_PRISE NUMBER(12,2)tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents unlimited);- Create/Recreate primary, unique and foreign key constraints alter table BGOODSadd constraint KEY_GOODS_ID primary key (GOODS
22、_ID)using index tablespace USERSpctfree 10initrans 2maxtrans 255storage(initial 64Kminextents 1maxextents unlimited); 创建视图视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。通过视图来查看表，就像是从不同的角度来观察一个(或多个) 表。 视图有如下一些优点： 可以提高数据访问的安全性，通过视图往往只可以访问数据
23、库中表的特定部分，限制了用户访问表的全部行和列。 简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的检索却很简单。 一个视图可以检索多张表的数据，因此用户通过访问一个视图，可完成对多个表的访问。 视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。 视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。因为 scott/tiger 没有创建视图的权限，所以需要使用 system 或 sys 用户
24、登陆，使用命令，为 scott 用户分配创建视图的权限。grant create view to scott在 scott 账户下创建用户订单视图，SQL 脚本如下：CREATE VIEW 用户订单create or replace view 用户定单 asSELECT bOrder.Order_ID AS 订单号码, bOrder.Order_Date AS 下单日期, bOrder.User_ID AS 用户编号, bClient.User_Name AS 用户姓名 , bClient.User_Tel AS 用户电话, bClient.User_Address AS 用户地址 , bGo
25、ods.Goods_Name AS 商品名称, bOrder.Order_Money AS 订单金额FROM bClient RIGHT OUTER JOIN bOrder ON bClient.User_ID = bOrder.User_ID LEFT OUTER JOIN bGoods ON bOrder.Goods_ID = bGoods.Goods_ID显示结果如下图 3-69 所示。图 3-69 PL/SQL SQL 脚本编辑界面 数据表基本操作 用户表插入新用户Insert into bClient(User_ID,User_Name,User_Sex,User_Birthday,
26、User_Type,User_Address,User_Tel)values (1,张三,男,to_date(1980-01-01,yyyy-mm-dd),1,苏州国际教育园北区学府路号,13911111111);注意：在使用 Oracle 的 to_date 函数来做日期转换，例如下面的 ORACLE SQL 脚本Select to_date(2010-01-01 13:14:20,yyyy-MM-dd HH:mm:ss) from dual;24 小时的形式显示出来要用 HH24Select to_char(sysdate,yyyy-MM-dd HH24:mi:ss) from dual;
27、/mi 是分钟Select to_char(sysdate,yyyy-MM-dd HH24:mm:ss) from dual;/mm 会显示月份 商品表增加商品Insert into bGoods(Goods_ID,Goods_Name,Goods_Prise)values (1,联想笔记本 ,2800.5);Insert into bGoods(Goods_ID,Goods_Name,Goods_Prise)values (2,苹果 MP3,120); 用户订单表增加用户新的订单Insert into bOrder(Order_ID,Order_Date,User_ID,Goods_ID,O
28、rder_Money)values (1,to_date(2010-04-29,yyyy-mm-dd),1 ,1,160); 修改用户订单表里面订单编号为”1”的商品的价格为 180 元update bOrderset Order_Money=180where Order_ID=1 删除用户订单表里面订单编号为”1”的订单信息delete from bOrder where Order_ID=1 查询视图” 用户定单” ，里面订单号码为”1”的订单Select * from 用户定单 where 订单号码=1查询结果如下图 3-70 所示图 3-70 SQL 查询用户订单 创建存储过程 修改用
29、户编号为”1”的姓名为”李四”，同时把该用户的所有订单在现在的订单费用的基础上减少 10%， create or replace procedure update_Fun(p_User_ID in number,p_UserName in varchar2)isbeginsavepoint point1; /*/*保存点*/update bClient set User_Name=p_UserName where User_ID=p_User_ID;update bOrder set Order_Money=Order_Money*0.9 where bOrder.User_Id=p_User
30、_ID;commit;savepoint point2;exceptionwhen others thenrollback to savepoint point1; /*/*异常处理，保存点下面的操作都不会被执行*/return;end;调用存储过程，SQL 脚本如下。begin- Call the procedureupdate_fun(p_user_id = 1,p_username = 李四 );end; 查询用户定单视图，统计总定单金额Select 用户编号,用户姓名,sum(订单金额) as 订单总金额from 用户定单group by 用户编号,用户姓名 ; 创建触发器Oracle
31、 对用户主键不提供自动加 1 的功能，例如：商品表 bGoods，商品编号Goods_ID 需要自动加 1。这里需要对商品表 bGoods 创建触发器，Goods_ID 字段完成自动加 1 的功能。在oracle 中 sequence 就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。create sequence SE_RECORD_Goodsminvalue 1maxvalue 99999999999999999999999start with 41increment by 1cache 20;在 bGoods 表创建触发器create or replace tr
32、igger Tr_RECORD_Goodsbefore Insert on bGoodsfor each rowbeginSelect SE_RECORD_Goods.nextval into :new.Goods_ID from dual;end;这样对商品表 bGoods 执行插入操作的时候，Goods_ID 会在现在 Goods_ID 最大值的基础上加 1。 应用游标创建自定义函数，使用自定义函数，查询指定区间的订单数据。首先创建 types 结构定义为 OBJECT，bOrder_type 结构与 bOrder 表结构一致。CREATE OR REPLACE TYPE bOrder_t
33、ype AS OBJECT(Order_ID integer,Order_Date date,User_ID integer,Goods_ID integer,Order_Money number(12,2)然后对类型进行说明 OBJECT 是 Table 类型CREATE OR REPLACE TYPE bOrder_table AS TABLE OF bOrder_type最后可以创建函数create or replace function bOrder_filter(P_starDate in VARCHAR2,P_endDate in VARCHAR2)return border_ta
34、bleiscursor Cur_sql isSELECT Order_ID,Order_Date,User_ID,Goods_ID,Order_MoneyFROM bOrder where (Order_Date between to_date(P_starDate,yyyy-mm-dd) and to_date(P_endDate,yyyy-mm-dd) ;c11 Cur_sql%rowtype;Result bOrder_table:= bOrder_table();BEGINResult := bOrder_table();open Cur_sql;loopfetch Cur_sqlin
35、to c11;exit when Cur_sql%notfound;Result.extend;Result(Result.count) := bOrder_type(NULL, NULL, NULL,NULL,NULL);Result(Result.count). Order_ID := c11.Order_ID;Result(Result.count). Order_Date:= c11.Order_Date; Result(Result.count). User_ID:= c11.User_ID;Result(Result.count). Goods_ID:= c11.Goods_ID; Result(Result.count).Order_Money := c11.Order_Money; end loop;close Cur_sql;return(Result);end bOrder_filter;调用函数如下，查询指定时间区间的数据：Select * from table(cast(bOrder_filter(1999-01-01,2011-01-01) as bOrder_table);查询结果如下图 3-71 所示。图 3-71 PL/SQL 查询界面
