oracle 过程、函数、触发器PL/SQL命名块习题集（答案详细）【一分耕耘一分收获:)】

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;
/