南审2017orcal期末实验参考答案.docx

Oracle数据库基础实验一 表空间和用户权限专业：班级：姓名：学号：实验内容一、 修改Oracle配置文件并重启服务Tnsnames.ora, listener.ora二、 创建表空间（将每一步的脚本和将执行结果截图，并黏贴到文件中）1. 在D盘创建data目录2. 使用系统管理员登陆数据库。创建数据表空间tbs_学号（如tbs_14010101），数据文件存储在d:data，文件名称:data_学号.dbf，文件大小50m，可以自动扩展空间，每次扩展10m，最大100m。Create tablespace tbs_001 datafile d:datadata_01.dbf size 50m autoextend on next 10m maxsize 100m三、 用系统管理员创建用户（将每一步的脚本和将执行结果截图，并黏贴到文件中）1、创建用户用户名：usr_学号(如usr_14010101)密码：admin默认表空间在第二步所创建的表空间上(tbs_学号,如tbs_14010101)。Create user usr_001 identified by admin default tablespace tbs_001;2、赋予权限:将connect,resource 角色赋予该用户。Grant connect,resource to usr_001;四、 使用第三步创建的用户登录到数据库中（将执行结果截图，并黏贴到文件中）1、 登录用户Conn usr_001/admin2、 使用create table 命令，创建表t_test，字段只用一个col1,数据类型int.Create table t_test( col1 int);3、 向表t_test中写入数据1，2，3，4，5，6Insert into t_test(col1) values(1);Insert into t_test(col1) values(2);Insert into t_test(col1) values(3);Insert into t_test(col1) values(4);4、 查询t_test表,显示结果Select * from t_test;5、 查询系统视图dba_users，查看结果并截图。Select * from dba_users;(会提示表或视图不存在，原因是没有访问权限)五、 对usr_学号(如usr_14010101)进行对象授权(将每一步的脚本和执行结果截图，并黏贴到文件中)1. 使用sys用户登录数据库Conn sys/admin as sysdba2. 对usr_学号赋予查询（select）dba_users表的对象权限。Grant select on dba_users to usr_001;3. 再次用usr_学号登录，查询dba_users，查看结果并截图Select * from dba_users;Oracle数据库基础（实验二）表、约束管理、数据管理专业：班级：姓名：学号：实验内容六、 修改Oracle配置文件并重启服务Tnsnames.ora, listener.ora七、 用系统管理员创建用户（将每一步的脚本和将执行结果截图，并黏贴到文件中）1、创建用户用户名：usr_学号(如usr_14010101)密码：adminCreate user usr_001 identified by admin;2、赋予权限:将connect,resource 角色赋予该用户。Grant connect,resource to usr_001;八、 在所创建的用户(usr_学号)下创建表：1. 表：department序号列名描述类型精度约束1Deptid部门编号Char6主键，要求主键的名称为pk_department2Deptname部门名称Varchar240非空，唯一约束3Address地址Varchar2604Contacts联系人Varchar2305Phone单位电话Varchar220Check约束，要求电话长度在8位到11位之间。（提示：用oracle的length函数）phone varchar2(20) check(length(phone) between 8 and 11),6Memo备注Varchar22000Create table department(Deptid char(6) constraint pk_department primary key,Deptname varchar2(40) not null unique,Address varchar2(60),Contactsvarchar2(30),Phone varchar2(20) check(length(phone) between 8 and 11),Memovarchar2(2000); 2. 表：employee序号列名描述类型精度约束1Employeeid员工编号Char62Name员工姓名Varchar230非空3Sexid性别Char1Check约束，要求只能是填0或者1。其中0代表男，1代表女4Phone电话Varchar2205Birthday出生日期Varchar286Deptid所在部门Char6Create table employee(Employeeidchar(6),Name varchar2(30),Sexid char(1) check(sexid in (0,1),Phonevarchar2(20),Birthday varchar2(8),Deptid char(6);九、 数据管理：（进行数据管理的时候，不要忘记提交commit）1. 录入数据：向表department中录入以下数据：部门编号部门名称地址联系人单位电话备注100001开发部南京张三58311111空100002销售部上海李管华东和华南的市场100003技术支持部杭州王nsert into department values(100001,开发部,南京,张三,58311111,null);Commit;向表employee表中录入以下数据：员工编号姓名性别电话出生日期所在部门800001张三01234567819910202100001800002李四123456789019921201100002800003王五0345678901219901212100002800003王六0345678901219901212100002800004用户102233445567619891001100003800005用户21556677889919940205100003800006用户314433443222219890101100001800007用户41556677889919940405100003Insert into employee values(800001,张三,0,12345678,19910202,100001);Commit;2. 修改数据：修改部门编号为“100002”的部门地址为“广州”Update department set address=广州 where deptid=100002;Commit;3. 查询数据：（1）、 查询性别为女（sexid=1）的员工信息。Select * from employee where sexed=1;（2）、 统计各个部门的员工数量。要求输出：部门编号，部门名称，人数Select a.deptid,a.deptname,count(*) from department a ,employee b Where a.deptid=b.deptid Group by a.deptid,a.deptname;（3）、 统计各个部门的员工按性别统计的人数要求输出：部门编号，部门名称，性别，人数Select a.deptid,a.deptname,sexid,count(*) from department a ,employee b Where a.deptid=b.deptid Group by a.deptid,a.deptname,sexid;（4）、 查询部门编号在100001，100002的员工信息（使用in关键字）Select * from employee where deptid in (100002,100001);（5）、 查询员工姓名为“用”开头的员工信息（使用like关键字）Select * from employee where name like 用%;4. 用查询创建表：使用create table as 命令创建备份表：department_bak，要求将数据也要一并创建。Create table department_bak as select * from department;使用create table as 命令创建备份表：employee_bak，要求只创建表结构。Create table employee_bak as select * from employee where 1=2;十、 约束管理：1. 修改表departmenti. 增加字段crtdate（创建日期）数据类型：char（8），并修改department的数据，将所有记录的crtdate修改为当前日期（20170418）。Alter table department add crdate char(8);ii. 修改字段address，将其长度修改为80。Alter table department modify address varchar2(80);iii. 重命名字段deptname，修改为dname。Alter table department rename column deptname to dname;2. 修改表employeei. 增加主键约束：将字段employeeid作为主键，主键名称为pk_employee。Alter table employee add constraint pk_employee primary key(employeeid);ii. 增加外键约束：将deptid作为外键，并参照department表中的deptid。Alter table employee add constraint fk_dept foreign key(deptid) references department(deptid);十一、 数据字典管理：i. 查看用户表的数据字典user_tablesSelect * from user_Tables;ii. 查看约束的数据字典user_constraintsSelect * from user_constraints;iii. 查询定义了约束的列user_cons_columnsSelect * from user_cons_ columns;十二、 外键约束的补充说明1. 外键有三种级联方式：在定义和添加FOREIGN KEY约束时，也能够通过ON关键字指定引用行为的类型。当父表中的一条记录被删除时，需要通过引用行为来确定如何处理子表中的外键列。ON DELETE CASCADE:级联删除（当删除父表的数据时，同步删除子表相关的数据）。ON DELETE SET NULL:当删除父表的数据时，将子表相关记录的外键列的值设为NULL。ON DELETE NO ACTION: 当删除父表的数据时，如果对应的子表存在关联数据时，不允许删除，必须先将子表的关联数据删除或修改为非关联数据才能删除主表数据。举例：ON DELETE SET NULL 级联Alter Table emp2 Add Constraint new_fk_emp2 Foreign Key (deptno) References dept2(deptno) On Delete Set Null;ON DELETE CASCADE 级联Alter Table emp1 Add Constraint new_fk_emp1 Foreign Key (deptno) References dept1(deptno) On Delete cascade;ON DELETE NO ACTION 级联Alter Table emp3 Add Constraint new_fk_emp3 Foreign Key (deptno) References dept3(deptno) On Delete NO ACTION;Oracle数据库基础（实验三）视图、索引、序列专业：班级：姓名：学号：实验内容十三、 修改Oracle配置文件并重启服务Tnsnames.ora, listener.ora十四、 创建表空间用系统管理员登录，在d:data目录下创建表空间tbs_scott,文件名：tbs_data01.dbf，大小100m，自动增长，每次增长10m。Create tablespace tbs_scott datafile d:datatbs_data01.dbf size 100m autoextend on next 10m;十五、 用系统管理员登录，修改scott用户的账户状态为unlock;参考SQL：Alter user scott identified by tiger account unlock;scott下几个表的数据字典1、 部门表：dept名称类型描述1DEPTNONUMBER(2)表示部门编号，由两位数字所组成2DNAMEVARCHAR2(14)部门名称，最多由14个字符所组成3LOCVARCHAR2(13)部门所在的位置2、 雇员表：emp名称类型描述1EMPNONUMBER(4)雇员的编号，由四位数字所组成2ENAMEVARCHAR2(10)雇员的姓名，由10位字符所组成3JOBVARCHAR2(9)雇员的职位4MGRNUMBER(4)雇员对应的领导编号，领导也是雇员5HIREDATEDATE雇员的雇佣日期6SALNUMBER(7,2)基本工资，其中有两位小数，五倍整数，一共是七位7COMMNUMBER(7,2)奖金，佣金8DEPTNONUMBER(2)雇员所在的部门编号3、 工资等级表：salgrade名称类型描述1GRADENUMBER工资的等级2LOSALNUMBER此等级的最低工资3HISALNUMBER此等级的最高工资4、 工资表：bonus名称类型描述1ENAMEVARCHAR2(10)雇员姓名2JOBVARCHAR2(9)雇员职位3SALNUMBER雇员的工资4COMMNUMBER雇员的奖金注意：以下对象的创建和管理都要在scott用户下进行。十六、 创建视图：赋予：create view 1. 创建视图v_emp1.1视图要求：视图的内容为输出empno,ename,deptno,sal。先用系统管理员对scott授予create view 权限：Grant create view to scott;用scott用户登录：注意：连接选项要用normalcreate view v_Emp as select empno,ename,deptno,sal from emp;1.2 使用desc命令查看视图v_emp的结构Desc v_emp;1.3 使用insert命令向该视图中插入一条记录，看看效果。数据内容为：编号员工姓名部门编号工资7777张三103000 Insert into v_emp values(7777,张三,10,3000);2. 创建视图v_empsal2.1视图要求：视图的内容为输出empno,ename,dname（部门名称）,ann_salary（年薪，算法：(sal+comm)*12）。create view v_empsal as select empno,ename,dname,(sal+comm)*12 as ann_salary from emp,dept where emp.deptno=dept.deptno;2.2 使用desc命令查看视图v_empsal的结构Desc v_empsal;2.3 使用insert命令向该视图中插入一条记录，看看效果。数据内容为：编号员工姓名部门名称年薪8888张三SALES120000 Insert into v_empsal values(8888,张三,10,3000);3. 创建实体视图mv_emp 赋予: CREATE MATERIALIZED VIEW先用系统管理员对scott授予CREATE MATERIALIZED VIEW 权限：Grant CREATE MATERIALIZED VIEW to scott;3.1视图要求：视图参考脚本如下：CREATE MATERIALIZED VIEW MV_EMPREFRESH FORCEON DEMANDASSELECT e.empno, e.eName, dnameFROM emp e, dept dWHERE e.deptno=d.deptno;3.2 查询视图mv_empSelect * from mv_emp;3.3 向emp表添加记录，然后再次查询mv_emp，看看结果编号员工姓名部门编号工资8866Testuser203000Insert into emp(empno,ename,deptno,sal) values(8866,testuser,20,3000);3.3 手工刷新视图MV_EMP，再次查询，查看结果提示：使用dbms_mview.refresh方法进行刷新。exec dbms_mview.refresh(mv_Emp)十七、 创建索引：1、为部门表（dept）的dname字段创建唯一索引，索引名称为idx_dname。并将该索引存储到表空间tbs_scott中。Create unique index idx_dname on dept(dname) tablespace tbs_scott;2、为员工表（emp）的job字段创建位图索引，索引名称为idx_job。并将该索引存储到表空间tbs_scott中。Create bitmap index idx_job on emp(job) tablespace tbs_scott;3、为员工表（emp）的创建函数索引，索引名称为idx_totalsal，索引列为：年薪（计算公式为:(sal+comm)*12）。并将该索引存储到表空间tbs_scott中。Create index idx_totalsal on emp(sal+comm)*12) tablespace tbs_scott;十八、 创建序列：1、创建序列seq_num要求：序列名称为：seq_num起始值:1，没有最大值，每次增长1。Create sequence seq_num Start with 1NomaxvalueIncrement by 1;2、初始化序列：currval提示：select seq_num.nextval from dual;3、执行seq_num.nextval三次，查看输出。select seq_num.nextval from dual;4、执行seq_num.currval三次，查看输出。select seq_num.currval from dual;5、使用insert命令向该emp中插入三条记录，看看效果。数据内容为：员工编号员工姓名部门编号工资Test1103000Test2202000Test3301500注意：员工编号使用序列seq_num来自动生成（提示使用序列的nextval方法生成编号）。Insert into emp(empno,ename,deptno,sal) values(seq_num.nextval,test1,10,3000);Insert into emp(empno,ename,deptno,sal) values(seq_num.nextval,test2,20,2000);Insert into emp(empno,ename,deptno,sal) values(seq_num.nextval,test3,30,1000);6、手工刷新视图MV_EMP，并查看结果。提示：使用dbms_mview.refresh方法进行刷新。exec dbms_mview.refresh(mv_Emp)索引、视图、序列、实体化视图在plsqldeveloper的位置。Oracle数据库基础（实验四）PL/SQL编程基础专业：班级：姓名：学号：实验内容十九、 修改Oracle配置文件并重启服务Tnsnames.ora, listener.ora二十、 用系统管理员登录，修改scott用户的账户状态为unlock;参考SQL：Alter user scott identified by tiger account unlock;scott下几个表的数据字典1、 部门表：dept名称类型描述1DEPTNONUMBER(2)表示部门编号，由两位数字所组成2DNAMEVARCHAR2(14)部门名称，最多由14个字符所组成3LOCVARCHAR2(13)部门所在的位置2、 雇员表：emp名称类型描述1EMPNONUMBER(4)雇员的编号，由四位数字所组成2ENAMEVARCHAR2(10)雇员的姓名，由10位字符所组成3JOBVARCHAR2(9)雇员的职位4MGRNUMBER(4)雇员对应的领导编号，领导也是雇员5HIREDATEDATE雇员的雇佣日期6SALNUMBER(7,2)基本工资，其中有两位小数，五倍整数，一共是七位7COMMNUMBER(7,2)奖金，佣金8DEPTNONUMBER(2)雇员所在的部门编号3、 工资等级表：salgrade名称类型描述1GRADENUMBER工资的等级2LOSALNUMBER此等级的最低工资3HISALNUMBER此等级的最高工资4、 工资表：bonus名称类型描述1ENAMEVARCHAR2(10)雇员姓名2JOBVARCHAR2(9)雇员职位3SALNUMBER雇员的工资4COMMNUMBER雇员的奖金注意：以下对象的创建和管理都要在scott用户下进行。二十一、 编写PL/SQL程序块：1. 简单输出程序块编写程序块，定义一个变量i，整型；为i赋值为10；注意：输出函数使用使用dbms_output.put_line函数。使用set serveroutput on，设定输出显示。参考程序：set serveroutput on;declare i int;begin i := 10; dbms_output.put_line(i);end;/2. 使用for循环编写程序块使用for循环，编写程序块，循环输出1-10的数字。注意：输出函数使用使用dbms_output.put_line函数。使用set serveroutput on，设定输出显示。参考程序：set serveroutput on;declare i int;begin for j in 1.10 loop dbms_output.put_line(j); end loop;end;/3. 使用loop exit when end loop编写程序块使用loop循环编写程序块。定义整型变量i，循环输出1-10的数字。当i10的时候，退出循环。注意：输出函数使用使用dbms_output.put_line函数。使用set serveroutput on，设定输出显示。参考程序：set serveroutput on;declare i int;begin i:=1; loop exit when i10; dbms_output.put_line(i); i:=i+1; end loop;end;/4、使用替换变量&，以及分支判断语句case。使用替换变量&，利用交互输入学生成绩。使用case.when.end case语句对成绩进行分支判断，如果成绩=90输出优秀如果成绩在80-89之间，输出良好如果成绩在60-79之间，输出合格否则输出不及格。参考输出：参考答案：set serveroutput on;declare i int; result varchar2(30);begin i := &成绩; case when i = 90 then result := 优秀; when i = 80 and i = 60 and i (select sal from emp where empno=v_empno); return v_cnt;end ;提示：该员工工资高的人数，使用子查询。select count(*) into v_cnt from emp where sal (select sal from emp where empno=v_empno);2、编写程序块：根据输入的员工号，使用该函数，输出数值。DeclareV_cnt number;V_empno emp.empno%type;Begin V_empno :=&员工号;V_cnt := f_get_highsalcnt(v_empno);Dbms_output.put_line(V_cnt);End;3、通过emp表，使用select语句来查询每一个员工，输出比其工资高的人的数量。select empno,ename,f_get_highsalcnt(empno) from emp;二十五、 编写过程：1、 编写过程p_update_sal：使用游标，循环（loop.exitend loop循环或者for循环）处理emp表中的人员，根据不同类别（JOB字段），对其