Oracle数据库设计期末复习.docx
《Oracle数据库设计期末复习.docx》由会员分享，可在线阅读，更多相关《Oracle数据库设计期末复习.docx（36页珍藏版）》请在冰豆网上搜索。
Oracle数据库设计期末复习
简答题:
1.解释FUNCTION，PROCEDURE和PACKAGE区别?
答：
存储过程（procedure）：
存储过程是预先编译好的代码，可以作为PL/SQL语句直接调用执行；过程存储没有返回值；存储过程在规格说明中不包含return语句，即使包含也不起任何作用。
函数（function）：
函数可以作为一个表达式的一部分使用；函数必须有返回值；函数在规格说明中必须包含至少一条return语句。
包（package）：
包是一种数据库对象，将逻辑上相关的PL/SQL类型、对象和子程序组合成一个更大的单位；包有包头和包体两部分组成；包中可以将一些用于完成某功能的函数和存储过程放进包中可以方便维护。
2.什么是序列？
如何取得取某个序列的当前值?
答：
1）序列是序列号生成器，可以为表中的行生成序列号，产生一组等间隔的系列数值。
2）先通过调用nextval产生序列的下一个值，再调用currval产生序列的当前值。
3.列举出至少三种表约束，各有什么作用？
答：
1）实体完整性约束：
减少数据的冗余，作用于行，例如主键约束；
2）域完整性约束：
提高数据的准确性，作用于列，check检测；
3）引用完整性约束：
满足数据的一致性，作用于表与表之间，例如外键约束；
4.取当前系统时间点日期（不包括年月）的SQL写法是怎样的？
答：
写法一：
selectto_char（sysdate,＂ddhh24:
mi:
ss＂）fromdual;
写法二：
selectsubstr（to_char（sysdate,'YYYYMMDDh24hh:
MM:
SS'）,）fromdual;
5.什么是同义词，分为哪几类？
答：
1）同义词是数据库某些复杂对象的一个别名，用于简化对象访问和提高对象访问的安全性。
2）同义词分公有和私有两类。
公有同义词授权用户和system用户可以使用，私有同义词只有建立该同义词的用户才能使用。
6.请说明Oracle中存储过程和函数有什么区别，分别如何定义？
答：
存储过程：
存储过程是预先编译好的代码，可以作为PL/SQL语句直接调用执行；过程存储没有返回值；存储过程在规格说明中不包含return语句，即使包含也不起任何作用。
Createorreplaceprocedure存储过程名（参数名in[输入、默认方式]数据类型，参数名out参数类型）
As
Begin
…………--执行DML操作
End;
函数：
函数可以作为一个表达式的一部分使用；函数必须有返回值；函数在规格说明中必须包含至少一条return语句。
createfunction函数名（变量名类型,变量名类型）
return返回类型is变量类型;
begin
…………--执行部分
return返回值;
End；
7.什么是角色，如何创建一个角色并把角色赋予指定账户？
答：
1）角色是具有名称的一组相关权限的组合。
2）创建角色有验证方式和非验证方式两种。
例如：
--创建public_role和private_role角色
Createrolepublic_rolenotidentified;
Createroleprivate_roleidentifiedbyprivate;
--给public_role角色授予某些系统权限和对象权限,如select,update
Grantselect,updateonscott.emptopublic_role;
3）把角色赋予指定账户的sql语句和直接把某一权限赋予指定账户相似。
例如：
--把角色public_role授予给用户wangcai
Grantpublic_roletowangcaiwithadminoption;
编程题:
1.编写PL程序块循环给scott.emp表中所有职工增加工资，每次增加100元，直到平均工资高于5000或任一职工的工资超过6000元为止，最后显示出给所有员工一共增加了多少工资。
DECLARE
--声明变量
avg_salemp.sal%TYPE;
max_salemp.sal%TYPE;
v_countnumber:
=0;
BEGIN
--循环遍历
loop
SELECTAVG（sal）INTOavg_salFROMemp;
SELECTMAX（sal）INTOmax_salFROMemp;
--退出条件判断
EXITWHENavg_sal>5000ORmax_sal>6000;
UPDATEempSETsal=sal+100;
v_count:
=v_count+SQL%ROWCOUNT;
ENDLOOP;
--打印结果
dbms_output.put_line（'职工工资增加了￥'||v_count*100）;
END;
2.编写一个PL块，定义一个静态游标存储scott.emp表中所有雇员的员empno、ename和sal，并使用循环打印出所有数据。
declare
cursorcur_empisselect*fromemp;--创建一个游标变量
v_erowemp%rowtype;--存储结果集每行变量名
begin
opencur_emp;--打开游标
loop--开始遍历游标
fetchcur_empintov_erow;
exitwhencur_emp%notfound;
--打印员工信息
dbms_output.put_line（'员工编号：
'||v_erow.empno||'员工姓名：
'||v_erow.ename||'员工工资：
'||v_erow.sal）;
endloop;
end;
3.编写一个存储过程upsal接收一个员工号，如果该员工职位是MANAGER，并且在DALLAS工作，那么就给他薪金加15%；如果该员工职位是CLERK，并且在NEWYORK工作，那么就给他薪金扣除5%；其他情况不作处理。
--创建存储过程
CREATEORREPLACEPROCEDUREupsal（v_empnoinemp.empno%TYPE）
AS
v_jobemp.job%TYPE;
v_locdept.loc%TYPE;
BEGIN
--查询数据
SELECTjob,locINTOv_job,v_locFROMemp,deptWHEREempno=v_empnoANDemp.deptno=dept.deptno;
--显示数据
IFv_job='MANAGER'ANDv_loc='DALLAS'THEN
UPDATEempSETsal=sal*1.15WHEREempno=v_empno;
ELSIFv_job='CLERK'ANDv_loc='NEWYORK'THEN
UPDATEempSETsal=sal*0.95WHEREempno=v_empno;
ELSE
NULL;
ENDIF;
--异常处理
EXCEPTION
WHENOTHERSTHEN
dbms_output.put_line（'数据没更新！
'）;
END;
--调用存储过程
DECLARE
v_empnoemp.empno%TYPE;
BEGIN
v_empno:
=&请输入职工号;
upsal（v_empno）;
END;
4.编写一个存储过程comSalary，分别统计出scott.emp表中所有部门的平均工资，并且只显示出工资超过3500的部门。
并编写PL块调用此存储过程。
--创建存储过程
CREATEORREPLACEPROCEDUREcomSalary
AS
CURSORcurISSELECTdeptno,AVG（sal）FROMempGROUPBYdeptno;
linecur%ROWTYPE;
avg_salemp.sal%TYPE;
BEGIN
OPENcur;--打开游标
LOOP--开始遍历游标
FETCHcurINTOline;
EXITWHENcur%NOTFOUND;
SELECTAVG（sal）INTOavg_salFROMempWHEREdeptno=line.deptno;
IFavg_sal>3500THEN--判断
dbms_output.put_line（line.deptno）;--打印结果
ELSE
NULL;
ENDIF;
ENDLOOP;
CLOSEcur;--关闭游标
END;
--调用存储过程
BEGIN
comSalary;
END;
一、填空题
1. 每个Oracle数据库都由3种类型的文件组成：
数据文件、日志文件 和控制文件。
2. 执行立即关闭的命令是_hareg –n oracle_。
3. 改变数据库状态的语句是alter database。
4. 用于创建表空间的语句是create tablespace ，
5. 修改表空间的语句是alter tablespace。
6. 向用户授权的命令为grant connect，resource to cdpfzx。
7. 创建用户的语句是create user CDPFZX identified by CDPFZX。
8. 修改角色的语句是alter role 。
9. 在CREATE TABLE语句中，定义主键的关键字是_PRIMARY KEY。
10. 在ALTER TABLE语句中，修改列名的关键字是_______ALTER __________。
11. 在SELECT语句中，设置查询条件的关键字是_____where____________。
12. 在SELECT语句中，实现模糊查询的功能的关键字是_____like_____________。
13. [declarations]关键字标志着PL/SQL程序中声明段的开始，在声明段中可以声明变量、常量和游标等对象。
14. PL/SQL的异常处理代码在异常处理块中实现。
三、简答题
1、 简述用户和角色的关系。
答：
Oracle使用角色的方法来限定各种用户的权力，在系统中可以有许多用户，各种用户有各种不同的角色，拥有不同的权力。
实践表明与其很明确的赋一组权限给一个角色，不如赋给一个角色，然后把这个角色赋给一组用户。
这样就可以实现动态的权限管理，当这组用户的权限必须改变时，只需改变角色的权限就可以了。
2、 简述表与视图的区别与联系
答：
区别：
1、视图是已经编译好的sql语句,是基于 SQL 语句的结果集的可视化的表。
而表不是 2、视图没有实际的物理记录。
而表有。
3、表是内容，视图是窗口 4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改 5、表是内模式,视图是外模式6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。
从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。
联系：
视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。
一个视图可以对应一个基本表，也可以对应多个基本表。
视图是基本表的抽象和在逻辑意义上建立的新关系
四、应用题
1．创建、修改表空间的定义，以及编辑和维护表空间。
（1）创建临时表空间tmptbs，文件大小为20M，可以重用，指定其最小区间为16M。
CREATE TEMPORARY TABLESPACE tmptbs
TEMPFILE '/oracle/oradata/db/tmptbs.dbf' SIZE 20M
autoextend on next 50m minsize 16m extent management local;
（2）在临时表空间tmptbs中添加临时文件，tmptbs01.dbf，文件大小为20MB。
ALTER TABLESPACE tmptbs
ADD DATAFILE '/oracle/oradata/db/ tmptbs01.dbf ' SIZE 20M;
（3）将表空间tmptbs设置为脱机状态。
ALTER TABLESPACE tmptbs OFFLINE;
（4） 将表空间tmptbs设置为只读表空间。
ALTER TABLESPACE tmptbs READ ONLY;
（5） 删除表空间tmptbs，在删除表空间的同时，删除包含的段和数据文件。
DROP TABLESPACE tmptbs INCLUDING CONTENTS AND DATAFILES;
2．创建和管理用户的基本属性，并对用户授予相应的角色和权限。
（1）创建用户名为USERMAN，密码为USERPASS的用户。
create user USERMAN identified by USERPASS default tablespace tmptbs
（2）设置用户USERMAN的密码立即过期。
Alter user USERMAN USERPASS expired
（3）锁定用户USERMAN。
Alter user USERMAN lock account lock
（4）对于用户USERMAN授予SYSDBA权限。
GRANT SYSDBA to USERMAN
（5）对用户USERMAN授予表USERS的SELECT、INSERT、UPDATE、DELETE权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON USER to userman
五、选做题
编写PL/SQL程序，使用LOOP…EXIT…END语句计算1~100之间所有偶数之和。
declare
v_count number :
= 1;
v_sum number :
= 0;
begin
for v_count in 1..100 loop
if mod（v_count,2） = 0 then
v_sum :
= v_sum + v_count;
end if;
end loop;
dbms_output.put_line（v_sum）;
试卷二 二、填空题（每空2分，共40分）
1、假设已在某远程客户端完成网络服务名配置，服务名为aptech，请写出用户MARTIN（用户口令martinpass）连接到服务器的命令：
___Connect martin/martinpass@aptech__；
2、SYS用户以管理员身份登录后，要授予用户MARTIN可以对SCOTT用户的EMP表进行查询的权限，请写出授权命令：
（假设MARTIN用户已存在） ___Grant select on scott.emp to martin__；
3、 创建表employee的副本，但不包含表中的记录：
CREATE TABLE employee_copy AS____Select * from employee where 1=2__；
4、查询itemfile表中itemrate列的信息，要求将数值转换为字符串，并使用当前货币符号作为前缀：
SELECT __To_char___（itemrate,'C99999'） FROM itemfile；
5、查itemfile表中itemdesc、re_level列的信息，要求re_level为NULL时显示为0
SELECT itemdesc, __NVL__（re_level,0） FROM itemfile；
6、完成以下PL/SQL块，功能是：
显示2 到50的25个偶数。
BEGINFOR__even_number___ IN __1..25___ LOOP
DBMS_OUTPUT.PUT_LINE（even_number*2）；
END LOOP
END；
7、 完成以下PL/SQL块，功能是：
接受职员编号并检索职员姓名。
将职员姓名存储在变量empname中，如果代码引发VALUE_ERROR异常，则向用户显示错误消息。
DELCARE
empname employee.ename%TYPE;
eno employee.empno%TYPE;
BEGIN
eno:
=’&employee_number’;
___SELECT ename INTO empname FROM employee WHERE empno=eno___;
DBMS_OUTPUT.PUT_LINE（‘职员姓名：
’||empname）;
___EXCEPTION___
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE（‘要存储在变量中的值过大’） END;
8、完成以下PL/SQL块，功能是：
使用游标，显示所有单价低于250元的玩具的单价。
DECLARE
my_toy_price toys.toyprice%TYPE;
CURSOR toy_cur IS
SELECT toyprice FROM toys WHERE toyprice<250;
BEGIN
___OPEN toy_cur___ LOOP
___FETCH toy_cur INTO my_toy_price;___
EXIT WHEN toy_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE （toy_cur%ROWCOUNT || '. 玩具单价:
' || my_toy_price）;
END LOOP;
CLOSE toy_cur;
END;
9、完成以下PL/SQL块，功能是：
使用游标显示销售报表。
如果目标销售额（tsales）大于实际销售额（asales），则显示消息“需提高销售额”。
如果tsales等于asales，则显示消息“已达到销售额”，否则显示消息“销售业绩出色” DECLARE
CURSOR sales_cur IS SELECT * FROM salesdetails;
BEGIN
_FOR sales_rec IN sales_cur LOOP_
IF sales_rec.tsales > sales_rec.asales THEN
DBMS_OUTPUT.PUT_LINE（'产品：
'||sales_rec.pid||' 需提高销售额'）;
ELSE
IF _sales_rec.tsales = sales_rec.asales_ THEN
DBMS_OUTPUT.PUT_LINE（'产品：
'||sales_rec.pid||'已达到销售额'）;
ELSE
DBMS_OUTPUT.PUT_LINE（'产品：
'||sales_rec.pid||'销售业绩出色'）;
END IF;
END IF;
END LOOP;
END;
10、完成以下PL/SQL块，功能是：
创建一个交换两数的过程。
CREATE OR REPLACE PROCEDURE
swap（p1 IN OUT NUMBER, p2 __IN OUT__ NUMBER） IS v_temp NUMBER;
BEGIN
v_temp :
= p1;
p1 :
= p2;
p2 :
= v_temp;
END;
11、完成以下PL/SQL块，功能是：
创建一个函数dept_name，其功能是接受职员编号后返回职员所在部门名称。
（注：
部门名称在dept表中，而职员信息在emp表中，职员所在部门号的列名为deptno）
CREATE OR REPLACE FUNCTION dept_name （emp_no NUMBER）
RETURN VARCHAR2 AS dept_no NUMBER
（2）;
result dept.dname%TYPE;
BEGIN
_SELECT deptno INTO dept_no FROM emp WHERE empno=emp_no;_
SELECT dname INTO result FROM dept WHERE deptno = dept_no;
__RETURN result;___
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END;
12、要执行pack_me包中的order_proc过程（有一个输入参数），假设参数值为’002’，可以输入以下命令：
EXECUTE __pack_ma.order_proc（‘002’）__
13、完成以下PL/SQL块的功能是：
创建一个触发器biu_job_emp，无论用户插入记录，还是修改EMP表的job列，都将用户指定的job列的值转换成大写。
CREATE OR REPLACE TRIGGER biu_job_emp __BEFORE INSERT OR UPDATE OF job ON emp__FOR EACH ROW__
BEGIN:
NEW.job :
=__UPPER（:
NEW.job）_;
END;
试卷三 复习题
一、填空题：
1.Oracle EnterpriseManager是一个基于 B/S的框架系统。
2．Oracle数据库的存储结构分为物理结构和逻辑结构。
3．在游标或者游标变量打开后还没有进行第一次提取时，％found属性为null。
4. 在oracle中已commit或rollback作为上一个事务的结束标志及下一个新事物开始的标志。
5. DML表示数据操作语言，主要的DML有SELECT，INSERT，UPDATE，DELETE.
6．索引的主要目标是提高访问的速度。
7．在Oracle database 10g 系统中，可以使用DBCA工具和CREATE DATEBASE命令两种方式创建数据库。
8. 命令Startup nomount只能启动数据库实例，但不能装载和打开数据库。
9．在输入URL进入Oracle Enterprice Manager Database Control 的时候，必须指出ip地址和端口号 。
10．在登录Oracle
升级会员 