plsql习题

使用pl/sql块编程实现，注意必要的异常处理。

1.输入一个员工号，输出该员工的姓名、薪金和工作时间（按年月日显示）。

Set serveroutput on Declare

V_ename scott.emp.ename%type; V_sal scott.emp.sal%type;

V_hiredate scott.emp. hiredate %type; Begin

Select ename, sal, hiredate into v_ename, v_sal, v_hiredate from scott.emp where empno=&empno;

Dbms_output.put_line('姓名：'|| v_ename||' 工资：'|| v_sal||' 工资日期：'||to_char(v_hiredate,'yyyy-mm-dd')); EXCEPTION

When no_data_found then

Dbms_output.put_line('输入编号有误！'); End; /

2.接收一个员工号，输出该员工所在部门的名称。

Set serveroutput on Declare

V_dname scott.dept.dname%type; Begin

select dname into v_dname from scott.dept a,scott.emp b where a.deptno=b.deptno and

1

b.empno=&empno;

Dbms_output.put_line('部门名称：'|| v_dname); EXCEPTION

When no_data_found then

Dbms_output.put_line('输入编号有误！'); End; /

3.接收一个部门号，如果该部门中员工职位是MANAGER，并且在DALLAS工作，那么就给他薪金加15%；如果该部门员工职位是CLERK，并且在NEW YORK工作，那么就给他薪金扣除5%；其他情况不作处理。

2

declare

v_deptno scott.emp.deptno%type:=&deptno; cursor emp_cursor is

select job,loc,sal from scott.emp,scott.dept where scott.emp.deptno in

(select scott.emp.deptno from scott.dept,scott.emp where scott.emp.deptno=scott.dept.deptno and scott.dept.deptno=v_deptno)for update of scott.emp.sal; begin

for emp_record in emp_cursor loop

if emp_record.job='CLERK' and emp_record.loc='NEW YORK'then

update scott.emp set sal=emp_record.sal*0.95 where current of emp_cursor;end if; if emp_record.job='MANAGER' and emp_record.loc='DALLAS'then

update scott.emp set sal=emp_record.sal*1.15 where current of emp_cursor; exit when emp_cursor%NOTFOUND; end if; end loop; end; /

4.接收一个员工号，输出这个员工所在部门的平均工资。

Set serveroutput on

3

Declare

V_deptno scott.dept.deptno%type; V_avg_salar scott.emp.sal%type; Begin

Dbms_output.put_line('请输入员工号：');

select scott.emp.deptno into v_deptno from scott.emp where scott.emp.empno = &empno; select avg(scott.emp.sal) into V_avg_salar from scott.emp where scott.emp.deptno = v_deptno;

Dbms_output.put_line('该员工所在的部门编号为：'|| V_deptno);

Dbms_output.put_line('该员工所在部门的平均工资为：'|| V_avg_salar); EXCEPTION

When no_data_found then

Dbms_output.put_line('输入编号有误！'); End; /

5.以交互的方式给部门表插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样。

select scott.dept.deptno ,scott.dept.dname, scott.dept.loc from scott.dept;

4

Set serveroutput on Begin

insert into scott.dept(scott.dept.deptno,scott.dept.dname,scott.dept.loc) values(&deptno,&dname,&loc); EXCEPTION

When DUP_VAL_ON_INDEX then

Dbms_output.put_line('部门号已被占用！'); End; /

5

建立存储过程

6.建立一个存储过程用来接收一个员工号，返回他的工资和他所在部门的平均工资并作为传出参数传出。

create or replace procedure getsalar (empno_in in number,his_salar out number,avg_salary out number) as

v_deptno number; begin

select scott.emp.deptno into v_deptno from scott.emp where scott.emp.empno = empno_in;

select scott.emp.sal into his_salar from scott.emp where scott.emp.empno = empno_in;

select avg(scott.emp.sal) into avg_salary from scott.emp where scott.emp.deptno = v_deptno; end getsalar ; /

6

7.建立一个存储过程用来接收一个部门号，找出其中两位最老的员工

Set serveroutput on

create or replace procedure getEmpno (v_empno number) as

v_name1 varchar2(20); v_name2 varchar2(20);

cursor v_e is select scott.emp.ename from scott.emp where

scott.emp.deptno in (select scott.emp.deptno from scott.emp where scott.emp.empno = v_empno ) order by scott.emp.hiredate ; begin

open v_e ;

fetch v_e into v_name1;

Dbms_output.put_line(v_name1);

fetch v_e into v_name2;

Dbms_output.put_line(v_name2); close v_e; end getEmpno; /

7

begin

getEmpno(7788); end;

8

本文来源：https://www.bwwdw.com/article/se3x.html