Oracle数据库设计期末复习.docx

1、Oracle数据库设计期末复习简答题:1. 解释FUNCTION，PROCEDURE和PACKAGE区别?答：存储过程（procedure）：存储过程是预先编译好的代码，可以作为PL/SQL语句直接调用执行；过程存储没有返回值；存储过程在规格说明中不包含return语句，即使包含也不起任何作用。 函数（function）：函数可以作为一个表达式的一部分使用；函数必须有返回值；函数在规格说明中必须包含至少一条return语句。包（package）：包是一种数据库对象，将逻辑上相关的PL/SQL类型、对象和子程序组合成一个更大的单位；包有包头和包体两部分组成；包中可以将一些用于完成某功能的函数和存

2、储过程放进包中可以方便维护。2. 什么是序列？如何取得取某个序列的当前值?答：1）序列是序列号生成器，可以为表中的行生成序列号，产生一组等间隔的系列数值。 2）先通过调用nextval产生序列的下一个值，再调用currval产生序列的当前值。3. 列举出至少三种表约束，各有什么作用？答：1）实体完整性约束：减少数据的冗余，作用于行，例如主键约束； 2）域完整性约束：提高数据的准确性，作用于列，check检测； 3）引用完整性约束：满足数据的一致性，作用于表与表之间，例如外键约束；4. 取当前系统时间点日期（不包括年月）的SQL写法是怎样的？答： 写法一：select to_char(sysda

3、te,dd hh24:mi:ss) from dual; 写法二：select substr(to_char(sysdate,YYYYMMDDh 24hh:MM:SS),)from dual;5. 什么是同义词，分为哪几类？答：1）同义词是数据库某些复杂对象的一个别名，用于简化对象访问和提高对象访问的安全性。 2）同义词分公有和私有两类。公有同义词授权用户和system用户可以使用，私有同义词只有建立该同义词的用户才能使用。6. 请说明Oracle中存储过程和函数有什么区别，分别如何定义？ 答：存储过程：存储过程是预先编译好的代码，可以作为PL/SQL语句直接调用执行；过程存储没有返回值；存储

4、过程在规格说明中不包含return语句，即使包含也不起任何作用。Create or replace procedure 存储过程名(参数名 in 输入、默认方式 数据 类型，参数名 out 参数 类型)AsBegin -执行DML操作End;函数：函数可以作为一个表达式的一部分使用；函数必须有返回值；函数在规格说明中必须包含至少一条return语句。create function 函数名(变量名 类型,变量名 类型) return 返回类型 is 变量 类型;begin -执行部分 return 返回值; End；7. 什么是角色，如何创建一个角色并把角色赋予指定账户？ 答：1）角色是具有名称

5、的一组相关权限的组合。 2）创建角色有验证方式和非验证方式两种。例如：-创建public_role和private_role角色 Create role public_role not identified;Create role private_role identified by private; -给public_role角色授予某些系统权限和对象权限,如select,update Grant select,update on scott.emp to public_role; 3）把角色赋予指定账户的sql语句和直接把某一权限赋予指定账户相似。 例如： -把角色public_role授

6、予给用户wangcai Grant public_role to wangcai with admin option;编程题:1. 编写PL程序块循环给scott.emp表中所有职工增加工资，每次增加100元，直到平均工资高于5000或任一职工的工资超过6000元为止，最后显示出给所有员工一共增加了多少工资。DECLARE -声明变量 avg_sal emp.sal%TYPE; max_sal emp.sal%TYPE; v_count number:=0;BEGIN -循环遍历 loop SELECT AVG(sal) INTO avg_sal FROM emp; SELECT MAX(sa

7、l) INTO max_sal FROM emp; -退出条件判断 EXIT WHEN avg_sal5000 OR max_sal6000; UPDATE emp SET sal=sal+100; v_count:=v_count+SQL%ROWCOUNT; END LOOP; -打印结果 dbms_output.put_line(职工工资增加了￥|v_count*100);END;2. 编写一个PL块，定义一个静态游标存储scott.emp表中所有雇员的员empno、ename和sal，并使用循环打印出所有数据。declare cursor cur_emp is select * from

8、 emp; -创建一个游标变量 v_erow emp%rowtype; -存储结果集每行变量名begin open cur_emp; -打开游标 loop -开始遍历游标 fetch cur_emp into v_erow; exit when cur_emp%notfound; -打印员工信息 dbms_output.put_line(员工编号：|v_erow.empno|员工姓名：|v_erow.ename|员工工资：|v_erow.sal); end loop;end;3. 编写一个存储过程upsal接收一个员工号，如果该员工职位是MANAGER，并且在DALLAS工作，那么就给他薪金加

9、15%；如果该员工职位是CLERK，并且在NEW YORK工作，那么就给他薪金扣除5%；其他情况不作处理。-创建存储过程CREATE OR REPLACE PROCEDURE upsal(v_empno in emp.empno%TYPE)AS v_job emp.job%TYPE; v_loc dept.loc%TYPE;BEGIN - 查询数据 SELECT job,loc INTO v_job,v_loc FROM emp,dept WHERE empno = v_empno AND emp.deptno=dept.deptno; - 显示数据 IF v_job=MANAGER AND

10、v_loc=DALLAS THEN UPDATE emp SET sal=sal*1.15 WHERE empno=v_empno; ELSIF v_job=CLERK AND v_loc=NEW YORK THEN UPDATE emp SET sal=sal*0.95 WHERE empno=v_empno; ELSE NULL; END IF; - 异常处理 EXCEPTION WHEN OTHERS THEN dbms_output.put_line(数据没更新！);END;-调用存储过程DECLARE v_empno emp.empno%TYPE;BEGIN v_empno:=&请输

11、入职工号; upsal(v_empno);END;4. 编写一个存储过程comSalary，分别统计出scott.emp表中所有部门的平均工资，并且只显示出工资超过3500的部门。并编写PL块调用此存储过程。-创建存储过程CREATE OR REPLACE PROCEDURE comSalaryAS CURSOR cur IS SELECT deptno,AVG(sal) FROM emp GROUP BY deptno; line cur%ROWTYPE; avg_sal emp.sal%TYPE;BEGIN OPEN cur; -打开游标 LOOP -开始遍历游标 FETCH cur IN

12、TO line; EXIT WHEN cur%NOTFOUND; SELECT AVG(sal) INTO avg_sal FROM emp WHERE deptno=line.deptno; IF avg_sal3500 THEN -判断 dbms_output.put_line(line.deptno); -打印结果 ELSE NULL; END IF; END LOOP; CLOSE cur; -关闭游标END;-调用存储过程BEGIN comSalary;END;一、填空题1.每个Oracle数据库都由3种类型的文件组成：数据文件、日志文件和 控制文件。2.执行立即关闭的命令是_har

13、egnoracle_。3.改变数据库状态的语句是alterdatabase。4.用于创建表空间的语句是createtablespace，5.修改表空间的语句是altertablespace。6.向用户授权的命令为grantconnect，resourcetocdpfzx。7.创建用户的语句是createuserCDPFZXidentifiedbyCDPFZX。8.修改角色的语句是alterrole。9.在CREATETABLE语句中，定义主键的关键字是_PRIMARYKEY。10.在ALTERTABLE语句中，修改列名的关键字是_ALTER_。11.在SELECT语句中，设置查询条件的关键字是

14、_where_。12.在SELECT语句中，实现模糊查询的功能的关键字是_like_。13.declarations关键字标志着PL/SQL程序中声明段的开始，在声明段中可以声明变量、常量和游标等对象。14.PL/SQL的异常处理代码在异常处理块中实现。三、简答题1、简述用户和角色的关系。 答：Oracle使用角色的方法来限定各种用户的权力，在系统中可以有许多用户，各种用户有各种不同的角色，拥有不同的权力。实践表明与其很明确的赋一组权限给一个角色，不如赋给一个角色，然后把这个角色赋给一组用户。这样就可以实现动态的权限管理，当这组用户的权限必须改变时，只需改变角色的权限就可以了。2、简述表与视图

15、的区别与联系 答：区别：1、视图是已经编译好的sql语句,是基于SQL语句的结果集的可视化的表。而表不是2、视图没有实际的物理记录。而表有。3、表是内容，视图是窗口4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时四对它进行修改，但视图只能有创建的语句来修改5、表是内模式,视图是外模式6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。8、视图的建立和删除只影响视图本身，不影响对应的基本表。联系：视图（v

16、iew）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系四、应用题1创建、修改表空间的定义，以及编辑和维护表空间。（1）创建临时表空间tmptbs，文件大小为20M，可以重用，指定其最小区间为16M。CREATETEMPORARYTABLESPACEtmptbsTEMPFILE/oracle/oradata/db/tmptbs.dbfSIZE20Mautoextendonnext50mminsize16mextentmanagementloc

17、al;（2）在临时表空间tmptbs中添加临时文件，tmptbs01.dbf，文件大小为20MB。ALTERTABLESPACEtmptbsADDDATAFILE/oracle/oradata/db/tmptbs01.dbfSIZE20M;（3）将表空间tmptbs设置为脱机状态。ALTERTABLESPACEtmptbsOFFLINE;（4）将表空间tmptbs设置为只读表空间。ALTERTABLESPACEtmptbsREADONLY;（5）删除表空间tmptbs，在删除表空间的同时，删除包含的段和数据文件。DROPTABLESPACEtmptbsINCLUDINGCONTENTSANDD

18、ATAFILES;2创建和管理用户的基本属性，并对用户授予相应的角色和权限。（1）创建用户名为USERMAN，密码为USERPASS的用户。createuserUSERMANidentifiedbyUSERPASSdefaulttablespacetmptbs（2）设置用户USERMAN的密码立即过期。AlteruserUSERMANUSERPASSexpired（3）锁定用户USERMAN。AlteruserUSERMANlockaccountlock（4）对于用户USERMAN授予SYSDBA权限。GRANTSYSDBAtoUSERMAN（5）对用户USERMAN授予表USERS的SELE

19、CT、INSERT、UPDATE、DELETE权限。GRANTSELECT,INSERT,DELETE,UPDATEONUSERtouserman五、选做题编写PL/SQL程序，使用LOOPEXITEND语句计算1100之间所有偶数之和。declarev_countnumber:=1;v_sumnumber:=0;beginforv_countin1.100loopifmod(v_count,2)=0thenv_sum:=v_sum+v_count;endif;endloop;dbms_output.put_line(v_sum);试卷二二、填空题（每空2分，共40分）1、假设已在某远程客户端

20、完成网络服务名配置，服务名为aptech，请写出用户MARTIN（用户口令martinpass）连接到服务器的命令：_Connectmartin/martinpassaptech _；2、SYS用户以管理员身份登录后，要授予用户MARTIN可以对SCOTT用户的EMP表进行查询的权限，请写出授权命令：（假设MARTIN用户已存在）_ Grantselectonscott.emptomartin _；3、创建表employee的副本，但不包含表中的记录：CREATETABLEemployee_copyAS_ Select*fromemployeewhere1=2_；4、查询itemfile表中i

21、temrate列的信息，要求将数值转换为字符串，并使用当前货币符号作为前缀：SELECT_ To_char _(itemrate,C99999)FROMitemfile；5、查itemfile表中itemdesc、re_level列的信息，要求re_level为NULL时显示为0SELECTitemdesc,_ NVL _(re_level,0)FROMitemfile；6、完成以下PL/SQL块，功能是：显示2到50的25个偶数。BEGINFOR_ even_number _IN_1.25_LOOPDBMS_OUTPUT.PUT_LINE(even_number*2)；ENDLOOPEND；

22、7、完成以下PL/SQL块，功能是：接受职员编号并检索职员姓名。将职员姓名存储在变量empname中，如果代码引发VALUE_ERROR异常，则向用户显示错误消息。DELCAREempnameemployee.ename%TYPE;enoemployee.empno%TYPE;BEGINeno:=&employee_number;_ SELECTenameINTOempnameFROMemployeeWHEREempno=eno_;DBMS_OUTPUT.PUT_LINE(职员姓名：|empname);_ EXCEPTION _WHENVALUE_ERRORTHENDBMS_OUTPUT.PU

23、T_LINE(要存储在变量中的值过大)END;8、完成以下PL/SQL块，功能是：使用游标，显示所有单价低于250元的玩具的单价。DECLAREmy_toy_pricetoys.toyprice%TYPE;CURSORtoy_curISSELECTtoypriceFROMtoysWHEREtoypricesales_rec.asalesTHENDBMS_OUTPUT.PUT_LINE(产品：|sales_rec.pid|需提高销售额);ELSEIF_ sales_rec.tsales=sales_rec.asales _THENDBMS_OUTPUT.PUT_LINE(产品：|sales_re

24、c.pid|已达到销售额);ELSEDBMS_OUTPUT.PUT_LINE(产品：|sales_rec.pid|销售业绩出色);ENDIF;ENDIF;ENDLOOP;END;10、完成以下PL/SQL块，功能是：创建一个交换两数的过程。CREATEORREPLACEPROCEDUREswap(p1INOUTNUMBER,p2_ INOUT _NUMBER)ISv_tempNUMBER;BEGINv_temp:=p1;p1:=p2;p2:=v_temp;END;11、完成以下PL/SQL块，功能是：创建一个函数dept_name，其功能是接受职员编号后返回职员所在部门名称。（注：部门名称在d

25、ept表中，而职员信息在emp表中，职员所在部门号的列名为deptno）CREATEORREPLACEFUNCTIONdept_name(emp_noNUMBER)RETURNVARCHAR2ASdept_noNUMBER(2); resultdept.dname%TYPE;BEGIN _ SELECTdeptnoINTOdept_noFROMempWHEREempno=emp_no;_SELECTdnameINTOresultFROMdeptWHEREdeptno=dept_no; _ RETURNresult;_EXCEPTIONWHENOTHERSTHENRETURNNULL;END;1

26、2、要执行pack_me包中的order_proc过程（有一个输入参数），假设参数值为002，可以输入以下命令：EXECUTE_ pack_ma.order_proc(002）_13、完成以下PL/SQL块的功能是：创建一个触发器biu_job_emp，无论用户插入记录，还是修改EMP表的job列，都将用户指定的job列的值转换成大写。CREATEORREPLACETRIGGERbiu_job_emp_BEFOREINSERTORUPDATEOFjobONemp _FOREACHROW _BEGIN :NEW.job:=_ UPPER(:NEW.job)_;END;试卷三复习题一、填空题：1.

27、 OracleEnterpriseManager是一个基于B/S的框架系统。2Oracle数据库的存储结构分为物理结构和逻辑结构。3在游标或者游标变量打开后还没有进行第一次提取时，found属性为null。4.在oracle中已commit或rollback作为上一个事务的结束标志及下一个新事物开始的标志。5.DML表示数据操作语言，主要的DML有SELECT，INSERT，UPDATE，DELETE.6索引的主要目标是提高访问的速度。7在Oracledatabase10g系统中，可以使用DBCA工具和CREATEDATEBASE命令两种方式创建数据库。8.命令Startupnomount只能启动数据库实例，但不能装载和打开数据库。9在输入URL进入OracleEnterpriceManagerDatabaseControl的时候，必须指出ip地址和端口号。10在登录Oracle