oracle11g函数,Oracle11g学习-sql/函数练习

Oracle--1、查询工作地点在纽约或波士顿的员工信息 地点在dept

select * from emp e where e.deptno in(select deptno from dept where loc='NEW YORK' or loc='BOSTON');

--3、查询工资比scott用户高的用户

select * from emp e where e.sal>(select e.sal from emp e where e.ename='SCOTT');

--4、查询部门名称为SALES或ACCOUNTING的员工信息

select * from emp e where e.deptno in(select d.deptno from dept d where d.dname='SALES' or d.dname='ACCOUNTING');

--5、查询每一个部门中工资最低的员工信息

select * from emp e,(select deptno ,min(sal) minSal from emp group by deptno) s where e.deptno=s.deptno and e.sal=s.minSal;

--6、查询员工中工资前三的员工信息(利用rownum)

select * from (select * from emp order by sal desc) s where rownum<=3

--7、查询工资大于本岗位平均工资的员工信息

select * from emp e ,(select job,avg(sal) avgSal from emp group by job) s where e.job=s.job and e.sal>s.avgSal;

--8、查询工资大于最小工资且小于最大工资的员工信息

select * from emp e,(select min(e.sal) minSal,max(e.sal) maxSal from emp e) s where e.sal>s.minSal and e.sal

declare

begin

for i in 1..9

loop

for j in i..9

loop

dbms_output.put(i||'*'||j||' ');

end loop;

dbms_output.put_line('');

end loop;

end;

--2、使用存储过程给scott所在的部门的员工全体涨300工资

create or replace procedure pro_scottDept_addSal

as

p_scottDeptno emp.deptno%type;

begin

select deptno into p_scottDeptno from emp where ename='SCOTT';

update (select sal,empno from emp e where e.deptno= p_scottDeptno) s set s.sal=s.sal+300;

end;

exec pro_scottDept_addSal();

select * from emp where deptno=20;

select * from dept;

--4、使用存储函数，在给定员工编号的情况下，返回该员工的工作地点

create or replace function fun_getAddress_byEmpno(

p_empno in emp.empno%type

)

return varchar2

as

p_deptno dept.deptno%type;

p_empAddress dept.loc%type;

begin

select deptno into p_deptno from emp where empno=p_empno;

select loc into p_empAddress from dept where deptno=p_deptno;

return p_empAddress;

end;

select fun_getAddress_byEmpno(7499) from dual;