现在已经有一个表:
CREATE TABLE EMP1
(EMPNO NUMBER(4) NOT NULL PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);
想知道下面问题的解答:
2、编写一个PL/SQL块,输出所有员工的员工名、员工号、工资和部门号。
3、查询名为“SMITH”的员工信息,并输出其员工号、工资、部门号。如果该员工不存在,则插入一条新记录,员工号为2007,员工名为“SMITH”,工资为1500,部门号为10。如果存在多个名为“SMITH”的员工,则输出所有名为“SMITH”的员工号、工资和部门号。
4、创建一个存储过程,以员工号为参数,输出该员工的工资。
5、创建一个函数,以部门号为参数,返回该部门的平均工资;
6、创建一个函数,以员工号为参数,返回该员工所在部门的平均工资。
7、在emp表上创建一个触发器,当插入、删除或修改员工信息时,统计各个部门的人数及平均工资,并输出。
8、创建一个包,包含一个过程和一个游标。游标返回所有员工的信息,过程实现每次输出游标中的5条记录。
9、创建一个存储过程,以2个整数为参数,输出工资在两者间的员工信息。
10、在emp表上创建一个触发器,保证修改员工工资时,改后的工资低于同部门的最高工资,同时低于同部门的最低工资。
CREATE TABLE EMP1
(EMPNO NUMBER(4) NOT NULL PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);
想知道下面问题的解答:
2、编写一个PL/SQL块,输出所有员工的员工名、员工号、工资和部门号。
3、查询名为“SMITH”的员工信息,并输出其员工号、工资、部门号。如果该员工不存在,则插入一条新记录,员工号为2007,员工名为“SMITH”,工资为1500,部门号为10。如果存在多个名为“SMITH”的员工,则输出所有名为“SMITH”的员工号、工资和部门号。
4、创建一个存储过程,以员工号为参数,输出该员工的工资。
5、创建一个函数,以部门号为参数,返回该部门的平均工资;
6、创建一个函数,以员工号为参数,返回该员工所在部门的平均工资。
7、在emp表上创建一个触发器,当插入、删除或修改员工信息时,统计各个部门的人数及平均工资,并输出。
8、创建一个包,包含一个过程和一个游标。游标返回所有员工的信息,过程实现每次输出游标中的5条记录。
9、创建一个存储过程,以2个整数为参数,输出工资在两者间的员工信息。
10、在emp表上创建一个触发器,保证修改员工工资时,改后的工资低于同部门的最高工资,同时低于同部门的最低工资。
--未测试过,你自己调试调试:
2.
set serveroutput on;
begin
for v_emp in (select ename,empno,sal,deptno from emp)
loop
dbms_output.put_line('ename:'||v_emp.ename||
' empno:'||v_emp.empno||
' sal:'||v_emp.sal||
' deptno:'||v_emp.deptno);
end loop;
end;
3.
declare
v_flag number;
v_empno emp.empno%type;
v_deptno emp.deptno%type;
v_sal emp.sal%type;
begin
select 1 into v_flag
from emp
where ename='SMITH';
if v_flag=1 then
select empno,sal,deptno
into v_empno,v_sal,v_deptno
from emp
where ename='SMITH';
dbms_output.put_line('the information of smith are:'
||' '||v_empno||' '||v_deptno||' '||v_sal);
else
insert into emp(empno,ename,sal,deptno)
values('2007','SMITH',1500,10);
commit;
end if;
exception
when others then
rollback;
end;
4.
create or replace procedure pro_emp(empno_in emp.empno%type)
as
v_sal emp.sal%type;
begin
select sal into v_sal
from emp
where empno=empno_in;
dbms_output.put_line(empno_in||' sal:'||v_sal);
end pro_emp;
5.
create or replace function fun_avg_sal(deptno_in emp.deptno%type)
return number as
v_avg_sal emp.sal%type;
begin
select avg(sal) into v_avg_sal
from emp
where deptno=deptno_in;
return v_avg_sal;
end fun_avg_sal;
6.
create or replace function fun_avg_sal_dept(empno_in emp.empno%type)
return number as
v_avg_sal_deptno emp.sal%type;
begin
select avg(sal) into v_avg_sal_deptno
from emp
where deptno = (
select deptno
from emp
where empno=empno_in);
return v_avg_sal_deptno;
end fun_avg_sal_dept;
7.
create or replace trigger tri_change_emp
before insert or delete or update on emp
for each row
begin
for v_f in (
select deptno,count(*) cnt,avg(sal) avg_sal
from emp
group by deptno)
loop
dbms_output.put_line('deptno:'|| v_f.deptno||
' the number of employee:'||v_f.cnt||
' the Average wage:'||v_f.avg_sal);
end loop;
end tri_change_emp;
8.
create or replace package pkg_show_emp as
type t_ref_cursor is refcursor;
procedure pro_show_employee;
end pkg_show_emp;create or replace package body pkg_show_emp
as
procedure pro_show_employee as
emp_info t_ref_cousor;
begin
open emp_info for
select * from emp;
for v_c in emp_info loop
if mod(v_c%rowcount,5)=0 then
dbms_output.put_line('-------------------');
else
dbms_output.put_line(v_c.empno||' '||
v_c.ename||' '||v_c.job||' '||v_c.mgr||' '
v_c.hiredate||' '||v_c.sal||' '||
v_c.comm||' '||v_c.deptno);
end if;
end loop;
end pkg_show_emp;
9.
create or replace procedure pro_emp(sal_1 in number,sal_2 number)
as
v_sal_1 number;
v_sal_2 number;
v_emp number;
begin
v_sal_1 := sal_1;
v_sal_2 := sal_2;
if v_sal_1 >= v_sal_2 then
temp := v_sal_1;
v_sal_1 := v_sal_2;
v_sal_2 := temp;
end if;
for v_c in (
select * from emp where sal between v_sal_1 and v_sal_2)
loop
dbms_output.put_line(v_c.empno||' '||
v_c.ename||' '||v_c.job||' '||v_c.mgr||' '
v_c.hiredate||' '||v_c.sal||' '||
v_c.comm||' '||v_c.deptno);
end loop;
end pro_emp;
10.
create or replace trigger tri_update_sal
before update sal on emp
for each row
declare
v_new_sal emp.sal%type;
v_max_sal emp.sal%type;
v_min_sal emp.sal%type;
excp exception;
begin
select :new.sal into v_new_sal
from emp
where empno= :new.empno;
select max(sal) into v_max_sal,
min(sal) into v_min_sal
from emp
where detpno=(
select deptno from emp
where empno= :new.empno);
if (v_new_sal > v_max_sal or v_new_sal < v_min_sal) then
raise a;
end if;
exception
when a then
dbms_output.put_line('insert error');
when others then
dbms_output.put_line(sqlerrm);
end tri_update_sal;
11、在emp表上创建一个触发器,当插入、删除或修改员工信息时,统计各个部门的人数及平均工资并输出。
12、创建一个包,包含一个过程和一个函数。过程以部门号为参数输出该部门中工资最高的员工名和员工号,函数以部门号为参数返回该部门员工的最高工资。