Oracle数据库设计期末复习资料-简答题+编程题.doc

简答题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写法是怎样的？答：写法一：select to_char(sysdate,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语句直接调用执行；过程存储没有返回值；存储过程在规格说明中不包含return语句，即使包含也不起任何作用。Create or replace procedure 存储过程名(参数名 in 输入、默认方式 数据 类型，参数名 out 参数 类型)AsBegin -执行DML操作End;函数：函数可以作为一个表达式的一部分使用；函数必须有返回值；函数在规格说明中必须包含至少一条return语句。create function 函数名(变量名 类型,变量名 类型) return 返回类型 is 变量 类型;begin -执行部分 return 返回值; End；7.什么是角色，如何创建一个角色并把角色赋予指定账户？答：1）角色是具有名称的一组相关权限的组合。2）创建角色有验证方式和非验证方式两种。例如：-创建public_role和private_role角色Create role public_role not identified;Create role private_role identified by private;-给public_role角色授予某些系统权限和对象权限,如select,updateGrant select,update on scott.emp to public_role;3）把角色赋予指定账户的sql语句和直接把某一权限赋予指定账户相似。例如：-把角色public_role授予给用户wangcaiGrant 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(sal) 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 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工作，那么就给他薪金加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 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:=&请输入职工号; 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 INTO line; EXIT WHEN cur%NOTFOUND; SELECT AVG(sal) INTO avg_sal FR