我是靠谱客的博主 秀丽缘分,这篇文章主要介绍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
10
create 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
11
create 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
8
set 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
13
create 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
8
create 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
54
create 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
16
set 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
6
create 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
17
create 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
5
set 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
21
create 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
9
create 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
9
create 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
9
create 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
21
CREATE 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
4
create 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
9
create 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
18
create 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
12
create 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
20
Create 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
16
Create 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
9
Create 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
7
CREATE 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
8
CREATE 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
13
create 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内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(64)

评论列表共有 0 条评论

立即
投稿
返回
顶部