oracle实验指导书之全本

OracleOracle 数据库数据库 实验指导实验指导 段华斌段华斌 编著编著 计算机与通信工程系计算机与通信工程系 实验一 实验名称 实验名称 数据库的管理 实验学时 实验学时 2 实验目的 实验目的 1 了解 Oracle 数据库的逻辑结构和物理结构 2 熟悉 Oracle 的命令操作环境 SQL PLUS 3 了解启动和关闭数据库的不同方式及其优缺点 4 熟悉掌握各种启动 关闭方式 实验内容及步骤 实验内容及步骤 1 SQL Plus 的启动与关闭的启动与关闭 一 一 SQL Plus 的启动的启动 当登录到操作系统后 有三种方法启动 SQL Plus 方法一 1 执行 开始 程序 Oracle OraDb11g home1 应用程序开发 SQL Plus 命令 打开 SQL Plus 窗口 显示登录界面 2 在登录界面中将提示输入用户名 根据提示输入相应的用户名和口令 例如 system 和 admin 后按 Enter 键 SQL Plus 将连接到默认数据库 3 连接到数据库之后 显示 SQL 提示符 可以输入相应的 SQL 命令 方法二 1 执行 开始 程序 附件 命令提示符 打开命令提示符窗口 2 输入命令 Sqlplus nolog Connect sys 口令口令 as sysdba 方法三 1 执行 开始 程序 附件 命令提示符 打开命令提示符窗口 2 输入命令 sqlplus 用户名 口令 as sysdba 二 断开与数据库的连接 二 断开与数据库的连接 SQL DISCONNECT 三 退出 三 退出 SQL Plus SQL EXIT 或 SQL Quit 四 启动 四 启动 sqlplus sqlplus 可能的故障可能的故障 ORA 12560ORA 12560 造成 ORA 12560 TNS 协议适配器错误的问题的原因有三个 1 监听服务没有起起来 windows 平台个一如下操作 开始 程序 管理工具 服务 打开服务面板 启动 TNSlistener 服务 2 database instance 没有起起来 windows 平台如下操作 开始 程序 管理工 具 服务 打开服务面板 启动 oracleserviceXXXX XXXX 就是你的 database SID 3 进入 sqlplus 前 在 command line 下输入 set oracle sid XXXX XXXX 就是你的 database SID 2 使用使用 shutdown 命令关闭数据库命令关闭数据库 练习使用 shutdown 命令关闭数据库实例 分别按以下方式启动数据库实例 1 正常关闭 等待当前所有已连接的用户断开与数据库的连接 然后关闭数据库 正常关闭的语句如下 SHUTDOWN NORMAL 2 立即关闭 回退活动事务处理并断开所有已连接的用户 然后关闭数据库 立即 关闭语句如下 SHUTDOWN IMMEDIATE 3 事务关闭 完成事务处理后断开所有已连接的用户 然后关闭数据库 事务处理 关闭语句如下 SHUTDOWN TRANSACTIONAL 4 中止关闭 中止数据库实例 立即关闭数据库 中止关闭的语句如下 SHUTDOWN ABORT 3 使用使用 startup 命令启动数据库命令启动数据库 练习使用 STARTUP 命令启动数据库实例 分别按以下方式启动数据库实例 1 启动数据库实例时不装载数据库 执行此操作的命令如下 STARTUP NOMOUNT 2 启动数据库实例 装载数据库 但不打开数据库 通常在数据库维护时执行此操 作 对应的命令如下 STARTUP MOUNT 3 启动后限制对数据库实例的访问 执行此操作的命令如下 STARTUP RESTRICT 4 强制实例启动 在遇到特殊异常的情况时 可以强制启动实例 强制启动实例的 语句如下 STARTUP FORCE 4 4 改变数据库的状态改变数据库的状态 1 装载数据库实例 在执行某些管理操作时 数据库必须启动 装载一个实例 但此时数据库处于关闭状态 ALTER DATABASE MOUNT 2 打开已关闭的数据库 ALTER DATABASE OPEN 3 以只读方式打开数据库 ALTER DATABASE OPEN READ ONLY 思考题 思考题 1 在数据库的 nomount mount open 三种模式下 分别打开数据库的什么文件 2 当关闭数据库时使用 shutdown 命令 好久没有关闭 随即将 命令提示符 窗口关闭 之后重新 启动命令提示符 并登录 SQL PLUS 使用 shutdown immediate 关闭数据库出 现如下问题如何解决 ORA 24324 未初始化服务句柄 ORA 24323 不允许此值 ORA 01090 正在关闭 不允许连接 3 如何把数据库从 nomount 状态改为 mount 状态 实验二 实验名称 实验名称 日志文件的管理 实验学时 实验学时 2 实验目的 实验目的 1 了解日志文件的作用 2 了解管理重做日志文件的准则 3 掌握重做日志文件组及成员的添加 清除 删除等操作方法 实验内容及步骤 实验内容及步骤 重做日志文件对于 Oracle 数据库来说是至关重要的 Oracle 数据库的变化 都会生成 相应的重做日志 这些日志被记录在重做日志文件中 1 重做日志文件的查看重做日志文件的查看 1 查看数据库各组联机重做日志文件的日志序列号 SQL ARCHIVE LOG LIST SQL SELECT GROUP SEQUENCE FROM V LOG 2 查看日志文件组的状态 SQL SELECT GROUP STATUS FROM V LOG 3 查看重做日志组及其成员的基本信息 SQL SELECT GROUP STATUS MEMBER FROM V LOGFILE 2 添加重做日志文件组添加重做日志文件组 SQL ALTER DATABASE ADD LOGFILE 2 GROUP 4 E REDO04 1 LOG 3 SIZE 10M 3 添加重做日志文件组的成员添加重做日志文件组的成员 方法一 SQL ALTER DATABASE ADD LOGFILE MEMBER 2 E REDO04 2 LOG 3 TO GROUP 4 方法二 如果不知道组编号 可以通过下述操作向组中其他成员的名称来添加新的成 员 SQL ALTER DATABASE ADD LOGFILE MEMBER 2 E REDO04 3 LOG 3 TO 4 E REDO04 1 log E REDO 04 2 log 查询 v log 可以看到添加完成后的结果 SQL SELECT GROUP MEMBERS STATUS FIRST TIME FROM V LOG 4 4 删除重做日志文件组成员删除重做日志文件组成员 SQL ALTER DATABASE DROP LOGFILE MEMBER E REDO04 3 LOG 5 5 删除重做日志文件组 删除重做日志文件组 SQL ALTER DATABASE DROP LOGFILE GROUP 4 可能的错误信息 Ora 01623 日志 10 是实例 orcl 的当前日志 解决方法 SQL alter system switch logfile SQL select from v log 交替日志文件组 如果多次切换后想要删除的重做日志组状态依旧是 active 的时候可以重 启动一下数据库 6 清除重做日志文件组 清除重做日志文件组 SQL ALTER DATABASE CLEAR LOGFILE GROUP 3 7 改变数据库的归档模式 改变数据库的归档模式 1 修改初始化参数 指定归档位置和归档日志文件的命名方法 SQL ALTER SYSTEM 2 SET LOG ARCHIVE DEST D ORACLE ORADATA ARCHIVE SQL ALTER SYSTEM 2 SET LOG ARCHIVE DUPLEX DEST E ORACLE ARCHIVE SQL ALTER SYSTEM 2 SET LOG ARCHIVE FORMAT R T S ARC SCOPE SPFILE 2 关闭数据库实例 SHUTDOWN IMMEDIATE 3 Startup mount 4 Alter database archivelog 5 Alter database open 8 强制进行日志切换 以检查日志文件能否正确归档 强制进行日志切换 以检查日志文件能否正确归档 SQL ALTER SYSTEM SWITCH LOGFILE 在资源管理器中查看指定归档位置下的归档日志文件 9 查看数据库的当前模式 查看数据库的当前模式 SQL SELECT NAME LOG MODE FROM V DATABASE 10 查看数据库的归档进程信息 查看数据库的归档进程信息 SQL SELECT FROM V ARCHIVE PROCESSES 11 查询数据库的归档日志文件名称 重做日志序列号以及每次归档的完成时间等 查询数据库的归档日志文件名称 重做日志序列号以及每次归档的完成时间等 SQL SELECT NAME SEQUENCE COMPLETION TIME FROM V ARCHIVED LOG 思考题 思考题 1 移动 重命名重做日志文件成员的步骤如下 请完成各步骤的命令代码 1 关闭数据库 2 将重做日志文件 E REDO04 3 LOG 移动或复制到 D 盘 并重命名为 REDO04 03 LOG 3 以 MOUNT 状态打开数据库 4 重命名数据库的重做日志文件 5 打开数据库 6 查看重做日志文件信息 2 在实验内容中添加重做日志文件组 group4 时 是否可以将 group4 改为 group1 改为 group6 呢 请分别说明原因 3 当重做日志文件组被清除后 该组还可以继续使用吗 为什么 实验三 实验名称 实验名称 角色和用户管理 实验学时 实验学时 2 实验目的 实验目的 1 了解数据库用户 2 熟悉创建 更改 删除 查看用户的方法 3 了解数据库的不同权限 4 掌握权限的授予和回收的方法 5 了解权限 角色的不同点以及它们之间的关系 6 熟练掌握对角色的管理 实验内容及步骤 实验内容及步骤 在 Oracle 中 只有具有一定权限的用户才能对数据库进行操作 在安装 Oracle 时 数 据库本身会自动创建一些用户或帐户 如 SYS SYSTEM SYSMAN DBSNMP 等 这些预定义 的用户具有特殊的权限能管理数据库 用 SYS 用户来实现创建 更改 删除用户等操作 1 创建用户 CREATE USER 语句在数据库中创建新用户 语法如下 CREATE USER IDENTIFIED BY EXTERNALLY GLOBALLY PASSWORD EXPIRE ACCOUNT LOCK UNLOCK TEMPORARY TABLESPACE DEFAULT TABLESPACE QUOTA quota K M UNLIMITED ON 表空间 PROFILE 概要文件 例 1 创建管理用户 USERMAN CREATE USER USERMAN IDENTIFIED BY USERMAN 2 更改用户 ALTER USER 语句也可以修改用户信息 1 修改密码密码 例 2 将用户 USERMAN 的密码修改为 NewPassword ALTER USER USERMAN IDENTIFIED BY NewPassword 2 PASSWORD EXPIRE 关键词设置密码过期 例 3 设置用户 USERMAN 的密码立即过期 它在下一次登录时必须修改密码 ALTER USER USERMAN PASSWORD EXPIRE 3 ACCOUNT LOCK 关键词锁定用户 例 4 锁定用户 USERMAN 使其无法登录到数据库 ALTER USER USERMAN ACCOUNT LOCK 4 ACCOUNT UNLOCK 关键词解锁用户 例 5 解除对用户 USERMAN 的锁定 ALTER USER USERMAN ACCOUNT UNLOCK 3 删除用户 DROP USER 语句也可以删除指定的用户 DROP USER 用户名 CASCADE 4 查询用户 例 5 SELECT username profile account status created FROM dba users WHERE username ty 不同用户可以对数据库进行不同的操作 所有对数据库可以进行操作的用户都必须具 有一定的操作权限 而不同用户权限不全相同 为不同用户赋予不同权限的目的是为了保 证数据库的安全 以下关于用户赋予 回收权限的操作 5 授予系统权限 使用 GRANT 语句 其语法如下 GRANT system privilege TO user name role name PUBLIC WITH ADMIN OPTION 例 6 对于用户 USERMAN 授予 SYSDBA 角色 GRANT SYSDBA TO USERMAN 6 授予对象权限 使用 GRANT 语句 其语法如下 GRANT object privilege ALL PRIVILEGES ON object name TO user name role name PUBLIC WITH GRANT OPTION Oracle 数据库的权限太多 对权限的管理工作太复杂 使用角色可以很好的解决这个 问题 角色是权限的集合 以下关于角色的管理 7 创建角色 创建角色的语法如下 Create role 角色名 not identified identified by 口令 externally globally 例 7 创建一个角色 sr admin 还用数据库口令认证方式 create role sr admin identified by admin 8 给角色授权 给角色授权的语法如下 Grant All 对象权限 1 对象权限 2 列 1 列 2 On 模式名 对象名 directory 目录名 to 角色 with grant option 例 8 grant create session to sr admin 9 删除角色 Drop role 删除角色 格式 Drop role 角色 思考题 思考题 完成如下操作的命令代码 1 以 sys 用户登陆 sqlplus sqlplus sys 123 as sysdba 2 创建角色 role1 Create role role1 3 为角色 role1 授予 create session resource 权限 grant create session to role1 grant resource to role1 3 创建用户 指定密码 表空间 用户名命名以本人姓名的拼音首字母缩写 学号 最后两位 Create user ty 06 identified by 123 default tablespace users temporary tablespace temp 5 修改用户密码 alter user ty06 identified by 123 6 为用户授予系统权限 UNLIMITED TABLESPACE grant UNLIMITED TABLESPACE to ty06 7 为用户指定角色 role1 grant role1 to ty06 8 收回用户的 UNLIMITED TABLESPACE 权限 revoke UNLIMITED TABLESPACE from ty06 9 锁定用户 10 解锁用户 12 删除用户 13 删除角色 role1 14 忘记 SYS 用户的解决方法 Sqlplus as sysdba Alter user sys identified by 123 Commit Connect sys 123 as sysdba 实验 4 实验名称 实验名称 表的管理 实验学时 实验学时 4 实验目的 实验目的 1 了解 Oracle 表和视图的概念 2 熟练掌握使用 SQL 语句创建表 3 学习使用 SELECT 语句查询数据 实验内容及步骤 实验内容及步骤 1 创建表创建表 CREATE TABLE 语句的基本使用方法如下所示 CREATE TABLE DEFAULT expr CONSTRAINT constraint name constraint def DEFAULT expr CONSTRAINT constraint name constraint def CONSTRAINT constraint name constraint def TABLESPACE 表空间名 PCTFREE n PCTUSED n INITRANS n STORAGE storage 例 1 创建表 Users SQL 语句如下 CREATE TABLE dhb Users1 UserIdNumber Primary Key UserName Varchar2 40 UserType Number 1 UserPwd Varchar2 40 2 修改表修改表 添加列语法形式如下 ALTER TABLE schema table name ADD new column data type dafault expr CONSTRAINT constraint name constraint def DEFAULT expr CONSTRAINT constraint name constraint def 例 2 在表 Users 中添加一个列 tmpcol SQL ALTER TABLE Users ADD tmpcol NUMBER 5 2 3 插入数据插入数据 INSERT 语句的基本使用方法如下所示 INSERT INTO 列名 1 列名 2 列名 n VALUES 值 1 值 2 值 n 例 3 向表 Users 中插入数据 INSERT INTO Users UserId UserName UserType UserPwd VALUES 1 Admin 1 Admin COMMIT 4 修改数据修改数据 UPDATE 语句的基本使用方法如下所示 UPDATE SET 列名 1 值 1 列名 2 值 2 列名 n 值 n WHERE 例 4 将用户 Admin 的密码修改为 111111 UPDATE Users SET UserPwd 111111 WHERE UserName Admin COMMIT WORK 5 删除数据删除数据 DELETE 命令删除表中的数据 DELETE WHERE 例 5 删除表 Users 中列 UserName 等于空 的数据 DELETE FROM Users WHERE UserName COMMIT WORK 6 查询数据查询数据 1 SELECT 语句的基本语法结构如下 SELECT 子句 INTO 子句 FROM 子句 WHERE 子句 GROUP BY 子句 HAVING 子句 ORDER BY 子句 SELECT 语句中各子句的说明 SELECT 子句子句描述描述 SELECT 子句指定由查询返回的列 INTO 子句创建新表并将结果行插入新表中 FROM 子句指定从其中检索行的表 WHERE 子句指定查询条件 GROUP BY 子句指定查询结果的分组条件 HAVING 子句指定组或聚合的搜索条件 ORDER BY 子句指定结果集的排序 UNION 运算符将两个或更多查询的结果组合为单个结果集 该结果集包含联合查询 中的所有查询的全部行 COMPUTE 子句生成合计作为附加的汇总列出现在结果集的最后 当与 BY 一起使用 时 COMPUTE 子句在结果集内生成控制中断和分类汇总 可在同一 查询内指定 COMPUTE BY 和 COMPUTE FOR 子句FOR 子句用于指定 BROWSE 或 XML 选项 OPTION 子句应在整个查询中使用指定的查询提示 每个查询提示只能指定一次 但允许指定多个查询提示 用该语句只可能指定一个 OPTION 子句 查询提示影响语句中的所有运算符 如果主查询中涉及 UNION 则只 有涉及 UNION 运算符的最后一个查询可以有 OPTION 子句 例 6 在 SELECT 语句中查询指定的列名 COL EMP NAME FORMAT A20 COL SEX FORMAT A10 COL TITLE FORMAT A10 SELECT Emp name Sex Title FROM dhb Employees 2 使用 ROWNUM 3 使用 LIKE 关键字实现模糊查询 Oracle 的通配符及其含义 包含零个或多个任意字符的字符串 任意单个字符 任意单个字符 表示 0 9 的数字 指定范围或集合中的任意单个字符 查询所有身份证号中包含 ddd 的员工记录 例 7 SELECT EMP NAME TITLE IDCard FROM DHB Employees WHERE IDCard LIKE ddd 3 在 SELECT 中使用 DECODE 函数 DECODE 函数语法如下 DECODE 如果等于 则 DECODE 函数返回 如果等于 则 DECODE 函数返回 以此类推 如果参数列表中没有与相等的值 则 DECODE 函数返回 例 8 SELECT Emp name DECODE Sex 男 先生 女 女士 未知 AS Sex FROM DHB Employees 4 在 SELECT 中使用 CASE 函数 CASE 函数还可以根据不同逻辑表达式是否成立来决定函数的返回值 语法如下 CASE WHEN THEN WHEN THEN ELSE END 例 9 在 Employees 中 将员工工资分 3 个级别 工资小于等于 3000 的级别为低 工资大 于 3000 且小于 5000 的级别为中 工资大于等于 5000 的级别为高 SELECT Emp name Wage CASE WHEN Wage3000 AND Wage 5000 THEN 高 END AS GRADE FROM DHB Employees 5 保存查询结果 在 CREATE TABLE 语句中使用 SELECT 子句可以将查询结果集填充到新建的表中 新表 的结构由选择列表中列的特性定义 语法如下 CREATE TABLE AS 例 10 将办公室的所有员工的姓名和职务信息保存到表 Office 中 CREATE TABLE DHB Office AS SELECT e Emp Name e Title FROM DHB Employees e DHB Departments d WHERE e Dep id d Dep Id AND d Dep Name 办公室 思考题 思考题 1 创建用户 指定密码 表空间 用户名命名以本人姓名的缩写 学号后两位命名 2 创建如下表 将表保存到自己的用户方案中 学生表 student 字段名称 字段类型 约束 sno varchar2 10 主键 sname varchar2 20 sage number 2 ssex varchar2 5 CREATE TABLE student snovarchar2 10 Primary Key sname varchar2 20 sage number 2 ssex varchar2 5 教师表 teacher tno varchar2 10 主键 tname varchar2 20 CREATE TABLE teacher tnovarchar2 10 Primary Key tname varchar2 20 课程表 course cno varchar2 10 与 tno 联合做主键 cname varchar2 20 tno varchar2 20 CREATE TABLE course cnovarchar2 10 cname varchar2 20 tno varchar2 20 Primary Key cno tno 成绩表 sc sno varchar2 10 与 cno 联合做主键 cno varchar2 10 score number 4 2 create table sc sno varchar2 10 cno varchar2 10 score number 4 2 primary key sno cno 3 为各表输入数据 insert into student values s001 张三 23 男 insert into student values s002 李四 23 男 insert into student values s003 吴鹏 25 男 insert into student values s004 琴沁 20 女 insert into student values s005 王丽 20 女 insert into student values s006 李波 21 男 insert into student values s007 刘玉 21 男 insert into student values s008 萧蓉 21 女 insert into student values s009 陈萧晓 23 女 insert into student values s010 陈美 22 女 commit insert into teacher values t001 刘阳 insert into teacher values t002 谌燕 insert into teacher values t003 胡明星 commit insert into course values c001 J2SE t002 insert into course values c002 Java Web t002 insert into course values c003 SSH t001 insert into course values c004 Oracle t001 insert into course values c005 SQL SERVER 2005 t003 insert into course values c006 C t003 insert into course values c007 JavaScript t002 insert into course values c008 DIV CSS t001 insert into course values c009 PHP t003 insert into course values c010 EJB3 0 t002 commit insert into sc values s001 c001 78 9 insert into sc values s002 c001 80 9 insert into sc values s003 c001 81 9 insert into sc values s004 c001 60 9 insert into sc values s001 c002 82 9 insert into sc values s002 c002 72 9 insert into sc values s003 c002 81 9 insert into sc values s001 c003 59 commit 4 查询每门课程被选修的学生数 SELECT cno count sno FROM sc GROUP BY cno Order by cno 5 查询出只选修了一门课程的全部学生的学号和姓名 SELECT sc sno student sname FROM sc student Where sc sno student sno GROUP BY sc sno student sname HAVING count cno 1 6 查询男生 女生人数 7 查询姓 张 的学生名单 8 查询同名同姓学生名单 并统计同名人数 9 1981 年出生的学生名单 10 查询每门课程的平均成绩 结果按平均成绩升序排列 平均成绩相同时 按课程号降 序排列 11 查询平均成绩大于 85 的所有学生的学号 姓名和平均成绩 12 统计每门课程的学生选修人数 超过 10 人的课程才统计 要求输出课程号和选修人 数 查询结果按人数降序排列 若人数相同 按课程号升序排列 13 查询各科成绩最高和最低的分 以如下形式显示 课程 ID 最高分 最低分 14 按各科平均成绩从低到高和及格率的百分数从高到低顺序 15 统计列印各科成绩 各分数段人数 课程 ID 课程名称 100 85 85 70 70 60 60 实验五 实验名称 实验名称 PL SQL 编程 实验学时 实验学时 4 实验目的实验目的 1 熟练掌握 PL SQL 程序设计的基本知识 2 熟练掌握 PL SQL 中控制结构的使用 具体包括选择结构语句 IF 语句和 CASE 语句 循环结构 四种循环结构 3 熟练使用 PL SQL 中系统函数 4 掌握 PL SQL 中异常处理语句的使用 5 掌握 PL SQL 中 SELECT 语句和 DML 语句的综合运用 实验内容及步骤实验内容及步骤 1 条件语句 条件语句 IF 的语法及使用的语法及使用 IF THEN ELSIF THEN ELSE END IF 例 1 声明一个整型变量 Num 使用 IF 语句判断 Num 变量是正数 负数或 0 SET ServerOutput ON DECLARE Num INTEGER 11 BEGIN IF Num 0 THEN dbms output put line 正数 ELSE dbms output put line 0 END IF END 2 分支语句 分支语句 CASE 语法 语法 CASE WHEN THEN 值 1 WHEN THEN 值 2 WHEN THEN 值 n ELSE 值 n 1 END 例 2 使用 CASE 语句根据给定的整数输出对应的星期值 SET ServerOutput ON DECLARE varDAY INTEGER 3 Result VARCHAR2 20 BEGIN Result CASE varDAY WHEN 1 THEN 星期一 WHEN 2 THEN 星期二 WHEN 3 THEN 星期三 WHEN 4 THEN 星期四 WHEN 5 THEN 星期五 WHEN 6 THEN 星期六 WHEN 7 THEN 星期七 ELSE 数据越界 END dbms output put line Result END 3 练习循环结构语句的使用方法 练习循环结构语句的使用方法 1 循环语句 循环语句 LOOP EXIT END LOOP IF THEN EXIT END IF END LOOP 例 1 计算 1 到 3 的累加和 SET ServerOutput ON DECLARE v Num INTEGER 1 v Sum INTEGER 0 BEGIN LOOP v Sum v Sum v Num dbms output put line v Num IF v Num 3 THEN EXIT END IF dbms output put line v Num v Num 1 END LOOP dbms output put line dbms output put line v Sum END 2 循环语句 循环语句 LOOP EXIT WHEN END LOOP EXIT WHEN END LOOP 例 2 计算 1 到 3 的累加和 SET ServerOutput ON DECLARE v Num INTEGER 1 v Sum INTEGER 0 BEGIN LOOP v Sum v Sum v Num dbms output put line v Num EXIT WHEN v Num 3 dbms output put line v Num v Num 1 END LOOP dbms output put line dbms output put line v Sum END 3 循环语句 循环语句 WHILE LOOP END LOOP WHILE LOOP END LOOP 4 循环语句 循环语句 FOR IN LOOP END LOOP FOR IN LOOP END LOOP 例 3 计算 1 到 3 的累加和 SET ServerOutput ON DECLARE v Num INTEGER v Sum INTEGER 0 BEGIN FOR v Num IN 1 3 LOOP v Sum v Sum v Num dbms output put line v Num IF v Num 3 THEN dbms output put line END IF END LOOP dbms output put line dbms output put line v Sum END 4 练习异常处理语句的使用方法 练习异常处理语句的使用方法 EXCEPTION WHEN THEN WHEN THEN WHEN OTHERS THEN 例 1 SET SERVEROUTPUT ON DECLARE x NUMBER BEGIN x a123 向 NUMBER 类型的变量 X 中赋值字符串 导致异常 EXCEPTION WHEN VALUE ERROR THEN DBMS OUTPUT PUT LINE 数据类型错误 END 例 2 SET SERVEROUTPUT ON DECLARE v result NUMBER 10 0 BEGIN v result 100 0 DBMS OUTPUT PUT LINE 结果是 v result EXCEPTION WHEN ZERO DIVIDE THEN DBMS OUTPUT PUT LINE 除数是零 默认用 1 替代除数 结果是 100 1 END 思考题 思考题 1 编写 PL SQL 程序 使用 LOOP EXIT END 语句计算 1 100 之间所有偶数之和 2 创建表 departments 和表 employees 并为两张表输入若干数据 表 departments 结构 字段名称 字段类型 约束 Dep id number 主键 Dep name varchar2 20 表 employees 结构 字段名称 字段类型 约束 emp id number 主键 emp name varchar2 20 sex varchar2 20 title varchar2 20 wage number idcard varchar2 12 dep id number 外键 3 编写 PL SQL 程序 查询 5 号员工工资 如果工资小于 3000 则加 200 员工资 并 提示信息 5 号员工工资已更新 如果工资大于 3000 则提示信息 5 号员工工 资为 XXX 已达到规定标准 4 编写 PL SQL 程序 查询 1 号员工的工资 使用 CASE 语句输出其工资等级 工资 小于等于 3000 等级为 低 工资大于 3000 小于 5000 等级为 中 工资大于 等于 5000 等级为高 5 编写 PL SQL 程序 查询的 departments 表中是否有部门号为 6 的记录 如果没 有 插入新记录部门号为 6 部门名称为 后勤部 如果有 显示查询结果 实验六 实验名称 实验名称 使用游标 存储过程和触发器 实验学时 实验学时 2 实验目的实验目的 1 了解游标的概念和工作原理 2 了解存储过程的分类和使用方法 3 了解触发器的概念 4 学习编写和执行自定义过程 5 学习创建和使用触发器 实验内容及步骤实验内容及步骤 1 游标的使用 游标的使用 一 使用显式游标 一 使用显式游标 1 说明游标 DECLARE CURSOR IS 2 打开游标 OPEN 3 读取数据 FETCH INTO 4 关闭游标 CLOSE 例 1 使用游标读取 1 号部门的名称 DECLARE 开始声明部分 varName VARCHAR2 50 声明变量 用来保存游标中的部门名称 定义游标 varId 为参数 指定部门编号 CURSOR MyCur varId NUMBER IS SELECT Dep Name FROM dhb Departments WHERE Dep id varId BEGIN 开始程序体 OPEN MyCur 1 打开游标 参数为 1 表示读取部门编号为 1 的记录 FETCH MyCur INTO varName 读取当前游标位置的数据 CLOSE MyCur 关闭游标 dbms output put line 部门名称 varName 显示读取的数据 END 结束程序体 二 带子查询的游标 二 带子查询的游标 forfor 循环的语法如下 循环的语法如下 FOR IN SELECT 子查询 LOOP 语句 1 语句 2 语句 n END LOOP 例 2 带子查询的游标 for 循环例子 打开显示模式 SET ServerOutput ON BEGIN 开始程序体 FOR var DeptRecord IN SELECT Dep id Dep Name FROM dhb Departments LOOP 显示保存在记录变量 var DeptRecord 中的数据 dbms output put line 部门编号 var DeptRecord Dep Id 部门名称 var DeptRecord Dep Name END LOOP END 结束程序体 2 过程的定义及使用 过程的定义及使用 CREATE PROCEDURE 语句来创建过程的语法格式 CREATE OR REPLACE PROCEDURE IS AS BEGIN END 例 3 创建过程 GetDepAvgWage 此过程的功能是根据输入的部门号获取指定部门的平均 工资 CREATE OR REPLACE PROCEDURE DHB GetDepAvgWage v DepId IN NUMBER v AvgWage OUT NUMBER AS BEGIN SELECT AVG Wage INTO v AvgWage FROM DHB Employees WHERE Dep Id v DepId END 执行过程 GetDepAvgWage 获取 1 号部门的平均工资 执行在使用输出参数时 必须首先定义参数的类型 SET ServerOutput ON DECLARE AvgWage NUMBER 0 BEGIN DHB GetDepAvgWage 1 AvgWage dbms output put line AvgWage END 3 3 触发器的创建及使用 触发器的创建及使用 CREATE TRIGGER 语句来创建触发器 CREATE OR REPLACE TRIGGER BEFORE AFTER INSERT DELETE UPDATE OF column column OR INSERT DELETE UPDATE OF column column ON FOR EACH ROW WHEN call procedure name 例 4 创建一个语句级 DML 触发器 1 首先创建表 Test 和表 LogTable 记录对表 Test 的更新操作 CREATE TABLE dhb Test idINTEGER nameVARCHAR2 50 CREATE TABLE dhb LogTable log date DATE actionVARCHAR2 50 2 创建语句触发器 LogUpdateTrigger CREATE OR REPLACE TRIGGER dhb LogUpdateTrigger AFTER INSERT OR UPDATE OR DELETE ON dhb Test DECLARE log action VARCHAR2 50 BEGIN IF INSERTING THEN log action Insert ELSIF UPDATING THEN log action Update ELSIF DELETING THEN log action Delete ELSE DBMS OUTPUT PUT LINE END IF INSERT INTO dhb LogTable log date action VALUES SYSDATE log action END 3 在表 Test 上执行插入 修改 删除操作 INSERT INTO dhb Test VALUES 1 Insert UPDATE dhb Test SET name Update DELETE FROM dhb Test WHERE id 1 COMMIT 4 查看表 LogTable 的内容 SELECT FROM DHB LogTable 例 5 创建一个行级触发器 MyTrigger 它的作用是当 dhb Departments 中 Dep id 列的值 发生变化时 自动更新表 Employees 中的 Dep id 列的值 从而保证数据完整性 CREATE OR REPLACE TRIGGER DHB MyTrigger AFTER UPDATE ON DHB Departments FOR EACH ROW BEGIN UPDATE DHB Employees SET Dep id new Dep id WHERE Dep id old Dep id END 验证触发器的功能 UPDATE DHB Departments SET Dep id 200 WHERE Dep id 1 Commit SELECT Emp name Dep id FROM DHB Employees 思考题 思考题 1 创建游标 mycur 从表 employees 中读取 1 号部门的员工姓名 职务 并输出 员 工姓名 XXX 职务 XXX 2 创建存储过程 GetGrade 查询指定员工的工资 使用 CASE 语句输出其工资等级 工资小于等于 3000 等级为 低 工资大于 3000 小于 5000 等级为 中 工资 大于等于 5000 等级为高 并执行该存储过程 3 创建存储过程 UpdateWage 查询指定员工的工资 如果工资小于 3000 则加 200 工资 并提示信息 XX 号员工工资已更新 如果工资大于 3000 则提示信息 XX 号员工工资为 XX