Oracle数据库大题整理
19. 对所有员工,如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%;如果该员工职位是CLERK,并且在NEW YORK工作那么就给他薪金扣除5%;其他情况不作处理。要求:使用游标或函数编程实现。
set serveroutput on;
declare cursor c1 is select * from emp;
c1rec c1%rowtype;
v_loc varchar2(20);
begin
for c1rec in c1 loop
select loc into v_loc from dept where deptno = c1rec.deptno;
if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then
update emp set sal = sal * 1.15 where empno = c1rec.empno;
elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then
update emp set sal = sal * 0.95 where empno = c1rec.empno;
else
null;
end if;
end loop;
end;
20. 对直接上级是'BLAKE'的所有员工,按照参加工作的时间加薪:1981年6月以前入职的员工加薪10%、1981年6月以后入职的员工加薪5%。要求:使用游标或函数编程实现。
declare
cursor c1 is select * from emp where mgr = (select
empno from emp where ename='BLAKE'); --直接上级是'BLAKE'的所有员工
c1rec c1%rowtype;
begin
for c1rec in c1 loop
if c1rec.hiredate < '01-6月-81' then
update emp set sal = sal * 1.1 where empno = c1rec.empno;
else
update emp set sal = sal * 1.05 where empno = c1rec.empno;
end if; end loop;
end;
21. 创建一个角色MYROLE,此角色具有建表,建视图系统权限和查找SCOTT用户EMP,DEPT表的权限,将此角色授权给TEST用户。
CREATE ROLE MYROLE;
GRANT CREATE TABLE,CREATE VIEW TO MYROLE;
GRANT SELECT ON SCOTT.EMP TO MYROLE;
GRANT SELECT ON SCOTT.DEPT TO MYROLE;
CREATE USER TEST IDENTIFIED BY TEST;
GRANT MYROLE TO TEST;
22. 创建EMP,DEPT两张表的副表(包括它们之间的约束)
CREATE TABLE mydept AS SELECT * FROM dept;
ALTER TABLE mydept ADD CONSTRAINT pk_mydept_deptno PRIMARY KEY(deptno);
CREATE TABLE myemp AS SELECT * FROM emp
ALTER TABLE myemp ADD CONSTRAINT pk_myemp_empno PAIMARY KEY(empno);
ALTER TABLE myemp ADD CONSTRAINT fk_myemp_mydept FOREIGN KEY(deptno)
REFERENCES mydept(deptno);
39. 定义游标update_cur,根据职务调整雇员的工资,如果职务为
“SALESMAN”或者“ANALYST”工资上调100元,如果职务为“MANAGER”工资上调200元,其它职务工资上调50元。
oracle数据库大题整理
