(1)编写一个PL/SQL块,输出所有员工的员工姓名、员工号、工资和部门号。
declare
v_emp scott.emp%rowtype;
cusor a is select empno,ename,sal,deptno from emp;
begin
open a ;
loop
fetch a into v_emp;
exit when a%notfound;
dbms_output.put_line(v_emp.empno||’ ‘||v_emp.ename||’ ‘||v_emp.sal||’ ‘);
end loop;
close a;
end;
(2)编写一个PL/SQL块,输出所有比本部门平均工资高的员工信息。
Declare
V_emp scott.emp%rowtype;
Cursor a is select * from emp a where sal >(select avg(sal) from emp b where a.deptno=b.deptno )
Begin
Open a
Loop
Fetch a into v_emp;
Exit when a%notfound;
Dbms_output.put_line(v_emp.ename||’ ‘||v_emp.empno||’ ‘||v_emp.sal||’ ‘||v_emp.depno);
End loop;
Close a;
End;
(3)编写一个PL/SQL块,输出所有员工及其部门领导的姓名、员工号及部门号。
Declare
V_emp scott.emp%rowtype;
Cursor a is select a.ename,a.empno,a.deptno,b.ename mname from emp a inner join emp m on e.empno=m.mrg
Begin
For v_emp in a
loop
Dbms_output.put_line(v_emp.empno||' '||v_emp.deptno||' '||v_emp.ename||'
'||v_emp.mname);
End loop;
End;
(4)查询姓为“SMITH”的员工信息,并输出其员工号、姓名、工资、部门号。
– –如果该员工不存在,则插入一条新记录,员工号为2012,员工姓名为“Smith”,工资为7500元,入职日期为“2002年3月5日”,部门号为50。
– –如果存在多个名“Smith”的员工,则输出所有名为“Smith”的员工号、姓名、工资、入职日期、部门号L。
Declare
V_emp emp%rowtype;
Begin
Select * into v_emp from emp where ename=’Smith’;
Dbms_ouput.put_line(v_emp.empno||’ ‘||v_emp.ename||’ ‘||v_emp.sal||’ ‘||v_emp.deptno);
Exception
When no_data_found then
Insert into emp(2012,’Smith’,7500,’5-3月-2002’,50);
When too_many_rows then
For v_emp in (select * from emp where ename=’Smith’)
Loop
Dbms_output.put_line(v_emp.empno||’ ‘ ||v_emp.ename||’ ‘||v.emp.sal||’ ‘||v_emp.hiredate||’ ‘||v_emp.deptno);
End loop;
End;