1. 查询某个员工的年收入

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. 记录类型作为返回类型，根据指定的部门号返回其对应的部门信息

函数创建：

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;

函数调用：

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. 定义函数，部门编码作为输入参数，查询出该部门的员工总数。

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. 定义包规范，分别定义存储过程完成部门信息的添加和删除操作，定义函数实现根据传递的部门号返回部门信息。

包头创建：

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;

定义包体，实现上面的包规范：

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;

包调用：

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部门的员工信息。要求用静态游标

定义包规范：

create or replace package emp_pack
is
cursor mycursor return emp%rowtype;
PROCEDURE pro_emp;
end;

实现包体：

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;

包调用：

set serveroutput on
begin
emp_pack.pro_emp;
end;

6. 只有在每个月的10号才允许办理职员的入职或离职手续，其它时间不允许操作

7. 每天12点以后，不允许修改雇员的工资和奖金

8. 每一位雇员都要根据收入缴所得税，假设所得税的上缴原则为：2000以下上缴3%，2000—5000上缴8%，5000以上上缴10%。现在要求建立一张新的数据表，可以记录出雇员的编号、姓名、工资、奖金和上缴所得税数据，并且在每次修改雇员表中sal和comm字段后可以自动更新记录。

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

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. 写一个函数，传入员工编号，返回所在部门名称

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.写一个函数，传入时间，返回入职时间比这个时间早的所有员工的平均工资

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. 编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此

过程就可以完成部门的增加操作：

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 一致（不拷贝数据）

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. 执行该存储过程

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. 写一个存储过程 (给一个用户名，判断该用户名是否存在)

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 为奇数的数据）

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. 统计员工工资变化

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. 只统计销售员工资的变化

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%。

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. 将插入的雇员的名字变成以大写字母开头。

CREATE OR REPLACE TRIGGER INITCAP
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
:new.ename:=INITCAP(:new.ename);
END;

20. 创建一个显示雇员总人数的存储过程。

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元。

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内容请搜索靠谱客的其他文章。