我是靠谱客的博主 秀丽缘分,这篇文章主要介绍oracle 过程、函数、触发器PL/SQL命名块习题集(答案详细)【一分耕耘一分收获:)】1. 查询某个员工的年收入2. 记录类型作为返回类型,根据指定的部门号返回其对应的部门信息3. 定义函数,部门编码作为输入参数,查询出该部门的员工总数。4. 定义包规范,分别定义存储过程完成部门信息的添加和删除操作,定义函数实现根据传递的部门号返回部门信息。5. 定义一个包,创建一个过程,显示雇员表中10部门的员工信息。要求用静态游标6. 只有在每个月的10号才允许办理职员的入职或离职手续,其它时间不允许,现在分享给大家,希望可以做个参考。
oracle 过程、函数、触发器PL/SQL命名块习题集(答案详细)【一分耕耘一分收获】:)
- 1. 查询某个员工的年收入
- 2. 记录类型作为返回类型,根据指定的部门号返回其对应的部门信息
- 3. 定义函数,部门编码作为输入参数,查询出该部门的员工总数。
- 4. 定义包规范,分别定义存储过程完成部门信息的添加和删除操作,定义函数实现根据传递的部门号返回部门信息。
- 5. 定义一个包,创建一个过程,显示雇员表中10部门的员工信息。要求用静态游标
- 6. 只有在每个月的10号才允许办理职员的入职或离职手续,其它时间不允许操作
- 7. 每天12点以后,不允许修改雇员的工资和奖金
- 8. 每一位雇员都要根据收入缴所得税,假设所得税的上缴原则为:2000以下上缴3%,2000—5000上缴8%,5000以上上缴10%。现在要求建立一张新的数据表,可以记录出雇员的编号、姓名、工资、奖金和上缴所得税数据,并且在每次修改雇员表中sal和comm字段后可以自动更新记录。
- 9. 写一个函数 输入一个员工名字,判断该名字在员工表中是否存在。存在返回 1,不存在返回 0
- 10. 写一个函数,传入员工编号,返回所在部门名称
- 11.写一个函数,传入时间,返回入职时间比这个时间早的所有员工的平均工资
- 12. 编写一个过程,要求,可以传入部门的编号,部门的名称,部门的位置,之后调用此
- 13.
- (1)创建三张表 dept10,dept20,dept30,表结构和 dept 一致(不拷贝数据)
- (2). 编写一个存储过程 mypro,
- i. 把 dept 表中 depto=10 的数据,存到 dept10,
- ii. 把 dept 表中 depto=20 的数据,存到 dept20
- iii. 把 dept 表中 depto=30 的数据,存到 dept30
- iv. 执行该存储过程
- 14. 写一个存储过程 (给一个用户名,判断该用户名是否存在)
- 15. 编写一个存储过程,批量插入 1000 条数据(只插入 ID 为奇数的数据)
- 16. 统计员工工资变化
- 17. 只统计销售员工资的变化
- 18. 限定新工资不能低于其原来工资,也不能高于 20%。
- 19. 将插入的雇员的名字变成以大写字母开头。
- 20. 创建一个显示雇员总人数的存储过程。
- 21. 编写一个过程,可以输入一个雇员号,如果雇员的补助不是0,则在原来基础上增加100元;如果雇员的补助为0,则把补助设为200元。
1. 查询某个员工的年收入
复制代码
1
2
3
4
5
6
7
8
9
10create or replace function queryempincomm(eno in number) RETURN NUMBER as psal emp.sal%type; pcomm emp.comm%type; begin select sal,comm into psal,pcomm from emp where empno=eno; RETURN psal*12+ nvl(pcomm,0); end;
2. 记录类型作为返回类型,根据指定的部门号返回其对应的部门信息
函数创建:
复制代码
1
2
3
4
5
6
7
8
9
10
11create or replace function dept_info(dno dept.deptno%type) RETURN dept%rowtype is rec_dept dept%rowtype; begin select * into rec_dept from dept where deptno=dno; return rec_dept; EXCEPTION when no_data_found then dbms_output.put_line('指定的部门不存在'); end;
函数调用:
复制代码
1
2
3
4
5
6
7
8set serveroutput on declare dept_rec dept%rowtype; begin dept_rec:=dept_info(10); dbms_output.put_line('10部门信息为:'||dept_rec.deptno||' '||dept_rec.dname); end;
3. 定义函数,部门编码作为输入参数,查询出该部门的员工总数。
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13create or replace function myfunc(dno dept.deptno%type) return number is vnum number; begin select count(empno) into vnum from emp group by deptno having deptno=dno; return vnum; end; declare dno dept.deptno%type := &deptno; begin dbms_output.put_line(dno || '这个部门的员工总数为:' || myfunc(dno)); end;
4. 定义包规范,分别定义存储过程完成部门信息的添加和删除操作,定义函数实现根据传递的部门号返回部门信息。
包头创建:
复制代码
1
2
3
4
5
6
7
8create or replace package dept_pack is procedure add_dept(dept_rec dept% rowtype); procedure del_dept(p_no dept.deptno%type); function get_dept(p_no dept.deptno% TYPE) return dept%rowtype; end;
定义包体,实现上面的包规范:
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54create or replace package body dept_pack is /*check_dept是包的私有子程序*/ function check_dept(p_dno dept.deptno% TYPE) RETURN boolean is f_count NUMBER; begin select count(*) into f_count from dept where deptno= p_dno; if f_count>0 THEN RETURN true; else return false; end if; end; --实现add_dept过程 procedure add_dept(dept_rec dept%rowtype) is begin if check_dept(dept_rec.deptno)=false THEN insert into dept values(dept_rec.deptno,dept_rec.dname,dept_rec.loc); dbms_output.put_line('插入成功'); else dbms_output.put_line('插入失败,部门号已经存在'); end if; end; --实现del_dept过程 procedure del_dept(p_no dept.deptno% TYPE) is begin if check_dept(p_no)=true THEN DELETE from dept where deptno= p_no; dbms_output.put_line('删除成功'); else dbms_output.put_line('删除失败,无此部门'); end if; end; --实现get_dept function get_dept(p_no dept.deptno% TYPE) return dept%rowtype; is rec_dept dept%rowtype; no_result EXCEPTION; begin if check_dept(p_no)=true THEN select * into rec_dept from dept where deptno= p_no; return rec_dept; else raise no_result; end if; exception when no_result THEN dbms_output.put_line('部门不存在'); when others THEN dbms_output.put_line('查询出错'); end; end;
包调用:
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16set serveroutput on declare rec_d dept%rowtype; begin --插入记录 rec_d.deptno:=&no; rec_d.dname:='&dname'; rec_d.loc:='&loc'; dept_pack.add_dept(rec_d); --删除记录 dept_pack.del_dept(80); --获取部门信息,注意函数调用是有返回值的,不能直接写函数名调用 rec_d:=dept_pack.get_dept(99); dbms_output.put_line(rec_d.deptno ||' '||rec_d.dname ||' '||rec_d.loc); end;
5. 定义一个包,创建一个过程,显示雇员表中10部门的员工信息。要求用静态游标
定义包规范:
复制代码
1
2
3
4
5
6create or replace package emp_pack is cursor mycursor return emp%rowtype; PROCEDURE pro_emp; end;
实现包体:
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17create or replace package body emp_pack is cursor mycursor return emp%rowtype is select * from emp where deptno=10; procedure pro_emp is rec_emp emp%rowtype; begin open mycursor; loop fetch mycursor into rec_emp; exit when mycursor%notfound; dbms_output.put_line(rec_emp.empno||' '|| rec_emp.ename||' '|| rec_emp.sal); END LOOP; close mycursor; END ; end;
包调用:
复制代码
1
2
3
4
5set serveroutput on begin emp_pack.pro_emp; end;
6. 只有在每个月的10号才允许办理职员的入职或离职手续,其它时间不允许操作
7. 每天12点以后,不允许修改雇员的工资和奖金
8. 每一位雇员都要根据收入缴所得税,假设所得税的上缴原则为:2000以下上缴3%,2000—5000上缴8%,5000以上上缴10%。现在要求建立一张新的数据表,可以记录出雇员的编号、姓名、工资、奖金和上缴所得税数据,并且在每次修改雇员表中sal和comm字段后可以自动更新记录。
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21create or replace trigger tax_trig after insert or update of sal,comm on emp declare cursor tax_cur is select * from emp; incomm emp.sal%type; emp_tax newtax.taxvalue% TYPE; begin delete from newtax; for rec_tax in tax_cur loop incomm:=rec_tax.sal+nvl(rec_tax.comm,0); if incomm<2000 THEN emp_tax:=incomm*0.03; elsif incomm<5000 THEN emp_tax:=incomm*0.05; else emp_tax:=incomm*0.08; end if; insert into newtax values(rec_tax.empno,rec_tax.ename,rec_tax.sal,rec_tax.comm, emp_tax); end loop; end;
9. 写一个函数 输入一个员工名字,判断该名字在员工表中是否存在。存在返回 1,不存在返回 0
复制代码
1
2
3
4
5
6
7
8
9create or replace function empfun(en emp.ename%type) return number as is_exist number; begin select count(*) into is_exist from emp where ename=upper(en); return is_exist; end; /
10. 写一个函数,传入员工编号,返回所在部门名称
复制代码
1
2
3
4
5
6
7
8
9create or replace function myfun(eno emp.empno%type) return varchar as name varchar(30); begin select d.dname into name from emp e,dept d where e.deptno = d.deptno and e.empno = eno; return name; end; /
11.写一个函数,传入时间,返回入职时间比这个时间早的所有员工的平均工资
复制代码
1
2
3
4
5
6
7
8
9create or replace function getAvgSal(hdate emp.hiredate%type) return number as esal number; begin select avg(sal) into esal from emp where hdate>emp.hiredate; return esal; end; /
12. 编写一个过程,要求,可以传入部门的编号,部门的名称,部门的位置,之后调用此
过程就可以完成部门的增加操作:
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE) AS cou NUMBER ; BEGIN -- 判断插入的部门编号是否存在,如果存在则不能插入 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ; IF cou=0 THEN -- 可以增加新的部门 INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ; DBMS_OUTPUT.put_line('部门插入成功!') ; ELSE DBMS_OUTPUT.put_line('部门已存在,无法插入!') ; END IF; END;
13.
(1)创建三张表 dept10,dept20,dept30,表结构和 dept 一致(不拷贝数据)
复制代码
1
2
3
4create table dept10 as select * from dept where 1=2; create table dept20 as select * from dept where 1=2; create table dept30 as select * from dept where 1=2;
(2). 编写一个存储过程 mypro,
i. 把 dept 表中 depto=10 的数据,存到 dept10,
ii. 把 dept 表中 depto=20 的数据,存到 dept20
iii. 把 dept 表中 depto=30 的数据,存到 dept30
iv. 执行该存储过程
复制代码
1
2
3
4
5
6
7
8
9create or replace procedure myproc as begin insert into dept10 select * from dept where deptno=10; insert into dept20 select * from dept where deptno=20; insert into dept30 select * from dept where deptno=30; end; /
14. 写一个存储过程 (给一个用户名,判断该用户名是否存在)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18create or replace procedure findName(name emp.ename%type) as i number; begin select count(*) into i from emp where ename=name; if i=1 then dbms_output.put_line('用户存在'); else dbms_output.put_line('用户不存在'); end if; end; / --调用该存储过程 BEGIN findName(upper('aaa')) ; END ; /
15. 编写一个存储过程,批量插入 1000 条数据(只插入 ID 为奇数的数据)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12create table test(i number(10)); create or replace procedure add1 as i number(10); begin for i in 1..1000 loop if mod(i,2) = 1 then insert into test values(i); end if; end loop; end;
16. 统计员工工资变化
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20Create table audit_emp_change( Name varchar2(10), Oldsal number(6,2), Newsal number(6,2), Time date); Create or replace trigger tr_sal_sal after update of sal on emp for each row declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name=:old.ename; if v_temp=0 then insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate); else update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename; end if; end; /
17. 只统计销售员工资的变化
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16Create or replace trigger tr_sal_sal after update of sal on emp for each row when (old.job=‟SALESMAN‟) declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name=:old.ename; if v_temp=0 then insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate); else update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename; end if; end; /
18. 限定新工资不能低于其原来工资,也不能高于 20%。
复制代码
1
2
3
4
5
6
7
8
9Create or replace trigger tr_check_sal Before update of sal on emp For each row When(new.sal<old.sal or new.sal>1.2*old.sal) Begin Raise_application_error(-20931,‟ddd‟); End; /
19. 将插入的雇员的名字变成以大写字母开头。
复制代码
1
2
3
4
5
6
7CREATE OR REPLACE TRIGGER INITCAP BEFORE INSERT ON EMP FOR EACH ROW BEGIN :new.ename:=INITCAP(:new.ename); END;
20. 创建一个显示雇员总人数的存储过程。
复制代码
1
2
3
4
5
6
7
8CREATE OR REPLACE PROCEDURE EMP_COUNT AS V_TOTAL NUMBER(10); BEGIN SELECT COUNT(*) INTO V_TOTAL FROM EMP; DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL); END;
21. 编写一个过程,可以输入一个雇员号,如果雇员的补助不是0,则在原来基础上增加100元;如果雇员的补助为0,则把补助设为200元。
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13create or replace procedure proc_emp(v_empno varchar2) is v_comm emp.comm%type; begin select comm into v_comm from emp where empno=v_empno; if v_comm<>0 then update emp set comm=comm+100 where empno=v_empno; else update emp set comm=200 where empno=v_empno; end if; end; /
最后
以上就是秀丽缘分最近收集整理的关于oracle 过程、函数、触发器PL/SQL命名块习题集(答案详细)【一分耕耘一分收获:)】1. 查询某个员工的年收入2. 记录类型作为返回类型,根据指定的部门号返回其对应的部门信息3. 定义函数,部门编码作为输入参数,查询出该部门的员工总数。4. 定义包规范,分别定义存储过程完成部门信息的添加和删除操作,定义函数实现根据传递的部门号返回部门信息。5. 定义一个包,创建一个过程,显示雇员表中10部门的员工信息。要求用静态游标6. 只有在每个月的10号才允许办理职员的入职或离职手续,其它时间不允许的全部内容,更多相关oracle内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复