oracle实验指导书之全本.doc

Oracle数据库实验指导段华斌 编著计算机与通信工程系实验一实验名称：数据库的管理实验学时：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。（二）断开与数据库的连接 SQLDISCONNECT（三）退出SQL*PlusSQLEXIT或：SQLQuit（四）启动sqlplus,也许的故障ORA-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 ABORT3、 使用startup命令启动数据库练习使用STARTUP命令启动数据库实例，分别按以下方式启动数据库实例。 （1）启动数据库实例时不装载数据库。执行此操作的命令如下： STARTUP NOMOUNT （2）启动数据库实例，装载数据库，但不打开数据库。通常在数据库维护时执行此操作，相应的命令如下：STARTUP MOUNT （3）启动后限制对数据库实例的访问。执行此操作的命令如下：STARTUP RESTRICT （4）强制实例启动。在碰到特殊异常的情况时，可以强制启动实例。强制启动实例的语句如下： STARTUP FORCE4.改变数据库的状态（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）查看数据库各组联机重做日记文献的日记序列号：SQLARCHIVE LOG LIST;SQLSELECT GROUP#,SEQUENCE# FROM V$LOG;（2）查看日记文献组的状态：SQLSELECT GROUP#,STATUS FROM V$LOG;（3）查看重做日记组及其成员的基本信息：SQLSELECT GROUP#,STATUS,MEMBER FROM V$LOGFILE;2、 添加重做日记文献组 SQLALTER DATABASE ADD LOGFILE 2 GROUP 4 (E:REDO04_1.LOG) 3 SIZE 10M;3、 添加重做日记文献组的成员方法一：SQLALTER DATABASE ADD LOGFILE MEMBER2 E:REDO04_2.LOG 3 TO GROUP 4;方法二：假如不知道组编号，可以通过下述操作向组中其他成员的名称来添加新的成员：SQLALTER DATABASE ADD LOGFILE MEMBER2 E:REDO04_3.LOG 3 TO4 (E:REDO04_1.log, E:REDO 04_2.log);查询v$log可以看到添加完毕后的结果：SQLSELECT GROUP#,MEMBERS,STATUS,FIRST_TIME FROM V$LOG;4、 删除重做日记文献组成员:SQLALTER DATABASE DROP LOGFILE MEMBER E:REDO04_3.LOG ;5、 删除重做日记文献组：SQLALTER DATABASE DROP LOGFILE GROUP 4;也许的错误信息：Ora-01623:日记10 是实例orcl的当前日记。解决方法：SQL alter system switch logfile; SQL select * from v$log;交替日记文献组，假如多次切换后想要删除的重做日记组状态依旧是active的时候可以重启动一下数据库。6、 清除重做日记文献组：SQLALTER DATABASE CLEAR LOGFILE GROUP 3;7、 改变数据库的归档模式：(1) 修改初始化参数 ，指定归档位置和归档日记文献的命名方法。SQL ALTER SYSTEM 2 SET LOG_ARCHIVE_DEST=D:ORACLEORADATAARCHIVE; SQL ALTER SYSTEM 2 SET LOG_ARCHIVE_DUPLEX_DEST=E:ORACLEARCHIVE; 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、 强制进行日记切换，以检查日记文献能否对的归档。SQLALTER SYSTEM SWITCH LOGFILE;在资源管理器中查看指定归档位置下的归档日记文献。9、 查看数据库的当前模式：SQLSELECT NAME,LOG_MODE FROM V$DATABASE;10、 查看数据库的归档进程信息：SQLSELECT * FROM V$ARCHIVE_PROCESSES;11、 查询数据库的归档日记文献名称、重做日记序列号以及每次归档的完毕时间等。SQLSELECT 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_nameTO 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 sysdba2、 创建角色role1。Create role role13、为角色role1授予create session、resource权限。grant create session to role1;grant resource to role1;3、 创建用户，指定密码、表空间。用户名命名以本人姓名的拼音首字母缩写+_+学号最后两位。Create user ty_06identified by 123default tablespace userstemporary 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 sysdbaAlter 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 = 值nWHERE 例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和COMPUTEFOR子句FOR子句用于指定BROWSE或XML选项OPTION子句应在整个查询中使用指定的查询提醒。每个查询提醒只能指定一次，但允许指定多个查询提醒。用该语句只也许指定一个OPTION子句。查询提醒影响语句中的所有运算符。假如主查询中涉及UNION，则只有涉及UNION运算符的最后一个查询可以有OPTION子句。例6、在SELECT语句中查询指定的列名 COL EMP_NAME FORMAT A20COL SEX FORMAT A10COL TITLE FORMAT A10 SELECT Emp_name, Sex, Title FROM dhb.Employees;（2）使用ROWNUM（3）使用LIKE关键字实现模糊查询Oracle的通配符及其含义%包含零个或多个任意字符的字符串_任意单个字符?任意单个字符#表达09的数字指定范围或集合中的任意单个字符查询所有身份证号中包含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 SexFROM 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 GRADEFROM DHB.Employees;（5）保存查询结果在CREATE TABLE语句中使用SELECT子句可以将查询结果集填充到新建的表中。新表的结构由选择列表中列的特性定义。语法如下：CREATE TABLE AS例10：将办公室的所有员工的姓名和职务信息保存到表Office中：CREATE TABLE DHB.Office ASSELECT 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);教师表：teachertno varchar2(10) 主键tname varchar2(20) CREATE TABLE teacher (tnovarchar2(10) Primary Key, tname varchar2(20);课程表：coursecno varchar2(10) 与tno联合做主键cname varchar2(20)tno varchar2(20)CREATE TABLE course (cnovarchar2(10), cname varchar2(20),tno varchar2(20), Primary Key(cno,tno);成绩表：scsno 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 2023,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 scGROUP BY cnoOrder by cno;5、 查询出只选修了一门课程的所有学生的学号和姓名。SELECT sc.sno,student.sname FROM sc,studentWhere sc.sno= student.snoGROUP 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 + 1END;例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 星期六