oracle 汇总

来源：互联网发布：安装补丁的软件编辑：程序博客网时间：2024/06/05 18:32

--create user t_scott,passwd t_scott should use user 'sys'
--grant resource,connect,create session to t_scott
--grant all on scott.emp to t_scott
--grant all on scott.dept to t_scott
--grant all on scott.salgrade to t_scott
--grant all on scott.bonus to _t_scott
-- select any table
-- login as user t_scott
--1. create table t_emp using sql as 'create table t_emp as select * from scott.emp where 1 = 1'
--2.create table t_dept as above
--3.create table t_salgrade as above
--4.create table t_bonus as above
--5.create index idx_empno on t_scott.t_emp.empno
-- create index idx_empno on t_emp(empno);
--6.insert date into t_emp,t_dept,t_salgrade,t_bonus from scott.emp,scott.dept,scott.salgrade,scott.bonus using
--'insert into table select * from ohters table'

--=====================================follows are all in the user t_scott===============
--=====================================sql practice====================

--1、列出至少有一个雇员的所有部门

　 select t.deptno from t_emp t group by t.deptno having count(1)>=1;

　　--2、列出薪金比"SMITH"多的所有雇员

select empno,ename from t_emp where sal>(select sal from t_emp where ename='SMITH');

　　--3、列出所有雇员的姓名及其直接上级的姓名

select a.ename as name,b.ename as mgr_name from t_emp a,t_emp b where a.mgr=b.empno;　　

　　--4、列出入职日期早于其直接上级的所有雇员

select a.empno,a.ename from t_emp a where a.hiredate<(select b.hiredate from t_emp b where a.mgr=b.empno);

　　--5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门

　 select d.dname,t.ename from t_emp t,t_dept d where t.deptno(+) = d.deptno;

　　--6、列出所有“CLERK”（办事员）的姓名及其部门名称

　 select t.ename,d.dname from t_emp t,t_dept d where t.deptno = d.deptno and t.job = 'CLERK';　

　　--7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

select min(t.sal) as sal from t_emp t group by t.job having min(t.sal)>1500;

　　--8、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号

select t.ename from t_emp t,t_dept d where t.deptno = d.deptno and d.dname like '%SALES%'; 　

　　--9、列出薪金高于公司平均水平的所有雇员

　 select t.empno,t.ename from t_emp t where t.sal > (select avg(sal) from t_emp);

　　--10、列出与“SCOTT”从事相同工作的所有雇员

　 select t.empno,t.ename from t_emp t where t.job = (select job from t_emp where ename = 'SCOTT');

　　--11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金

　　 select t.ename,t.sal from t_emp t where t.sal in (select sal from t_emp where deptno='30');

　　--12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金

　 select t.ename,t.sal from t_emp t where t.sal > all(select sal from t_emp where deptno='30');

　　--13、列出每个部门的信息以及该部门中雇员的数量

　 select d.deptno,d.dname,d.loc,count(empno) as count from t_emp t,t_dept d where t.deptno(+) = d.deptno group by d.deptno,d.dname,d.loc;

　　--14、列出所有雇员的雇员名称、部门名称和薪金

　　 select t.ename,d.dname,t.sal from t_emp t,t_dept d where t.deptno = d.deptno;

　　--15、列出从事同一种工作但属于不同部门的雇员的不同组合

select distinct t.job,t.deptno,min(t.empno)over(partition by t.job,t.deptno) from t_emp t order by t.job;

　　--16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员

select d.deptno,d.dname,d.loc,count(empno) as count from t_dept d left join t_emp t on d.deptno = t.deptno group by d.deptno,d.dname,d.loc;

　　--17、列出各种类别工作的最低工资

select t.job,min(sal) as sal from t_emp t group by t.job;

　　--18、列出各个部门的MANAGER（经理）的最低薪金

　 select t.deptno,d.dname,min(t.sal) as sal from t_emp t,t_dept d where t.deptno = d.deptno and t.job = 'MANAGER' group by t.deptno,d.dname;

　　--19、列出按年薪排序的所有雇员的年薪

select t.empno,t.ename,t.sal*12 as sal from t_emp t order by t.sal; 　

　　--20、列出薪金水平处于第四位的雇员

select empno,ename,sal from (select t.*,rownum r from (select empno,ename,sal from t_emp order by sal desc) t where rownum <5) where r=4;

--======================================function=============================
-- 1.新建函数f_getenamebyempno,参数传入empno，返回对应ename，函数尽量考虑异常等

CREATE OR REPLACE FUNCTION f_getenamebyempno(emp_no in number) RETURN VARCHAR2
IS
v_ename t_emp.ename%type;
BEGIN

FOR EMPINFO IN (SELECT ENAME FROM T_EMP WHERE EMPNO = EMP_NO) LOOP

v_ename := EMPINFO.ENAME;
END LOOP;

select t.ename into v_ename from t_emp t where t.empno = emp_no and rownum = 1;
RETURN(v_ename);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||':'||SQLERRM);
END f_getenamebyempno;

--2.新建函数f_gettaxbyempno 一个计算员工应缴所得税的函数,
--传入员工的编号，返回该员工应缴纳的个人所得税。
--个人所得税计算方式如下：
--工资所得税
--1600以下 0
--1600-3000 工资的5%-75
--3000-5000 工资的7.5%-125
--5000以上工资的10%-165

CREATE OR REPLACE FUNCTION f_gettaxbyempno(emp_no IN NUMBER) RETURN NUMBER
AS
v_money   T_EMP.SAL%TYPE:=0;

BEGIN
SELECT CASE
          WHEN T.SAL <= 1600 THEN 0
          WHEN T.SAL <= 3000 THEN (CASE WHEN T.SAL*0.05 <75 THEN T.SAL*0.05 ELSE 75 END)
          WHEN T.SAL <= 5000 THEN (CASE WHEN T.SAL*0.075 <125 THEN T.SAL*0.075 ELSE 125 END)
          WHEN T.SAL > 5000 THEN (CASE WHEN T.SAL*0.1 <165 THEN T.SAL*0.1 ELSE 165 END)
          ELSE 0
         END
    INTO v_money
    FROM T_EMP T
   WHERE T.EMPNO = emp_no
     AND ROWNUM = 1;

RETURN(round(v_money));

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE||':'||SQLERRM);
END f_gettaxbyempno;

--=======================================procedure============================
-- 1.新建过程p_outputempinfo ,无参数，循环打印所有t_emp 中的信息
--输出采用dbms_output.put_line();
--如果在命令行中显示结果，需开启输出--set serveroutput on
--要求 1.用游标形式定义 ,赋值是采用三种形式,分别打印输出
---1.1 单个变量赋值，如v_ename varchar2(30)
---1.2 type ,如 v_ename t_scott.t_emp.ename%type;
---1.3 rowtype v_emp t_scott.t_emp%rowtype;
-- 游标采用如下形式
--type cur_sor is ref cursor
-- v_empinfo cur_sor
--open v_empinfo for select * from emp;
--loop
--fetch
--end loop
--2.用for loop形式输出,不做其他要求

CREATE OR REPLACE PROCEDURE p_outputempinfo
AS
type cur_sor is ref cursor;
v_empinfo cur_sor;
v_empno    t_scott.t_emp.empno%type;
v_ename    t_scott.t_emp.ename%type;
v_job      t_scott.t_emp.job%type;
v_mgr      t_scott.t_emp.mgr%type;
v_hiredate t_scott.t_emp.hiredate%type;
v_sal      t_scott.t_emp.sal%type;
v_comm     t_scott.t_emp.comm%type;
v_deptno   number(2);

BEGIN
open v_empinfo for select * from t_emp;
   loop
     fetch v_empinfo into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno;
     exit when v_empinfo%notfound;
     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno);
   end loop;
CLOSE v_empinfo ;

open v_empinfo for select * from t_emp;
   loop
     fetch v_empinfo into rowtype ;
     exit when v_empinfo%notfound;
     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno);
   end loop;
CLOSE v_empinfo ;

exception
when others then
dbms_output.put_line(sqlcode||':'||sqlerrm);
END p_outputempinfo;

--2.新建过程p_outputempinfobyempno ,输入empno,打印出行信息

CREATE OR REPLACE PROCEDURE p_outputempinfobyempno(emp_no in number)
AS
type cur_sor is ref cursor;
v_empinfo cur_sor;
v_empno    t_scott.t_emp.empno%type;
v_ename    t_scott.t_emp.ename%type;
v_job      t_scott.t_emp.job%type;
v_mgr      t_scott.t_emp.mgr%type;
v_hiredate t_scott.t_emp.hiredate%type;
v_sal      t_scott.t_emp.sal%type;
v_comm     t_scott.t_emp.comm%type;
v_deptno   number(2);

BEGIN
open v_empinfo for select * from t_emp where empno = emp_no;
   loop
     fetch v_empinfo into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno;
     exit when v_empinfo%notfound;
     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno);
   end loop;
CLOSE cur_sor ;

exception
when others then
    dbms_output.put_line(sqlcode||':'||sqlerrm);
END p_outputempinfobyempno;

--3.新建过程p_updatesal,传入empno，sal，
--要求
--3.1 自定义异常，如果没有这个empno，抛出自定义异常 ex_noempno，并rollback
--3.2 如果update后的工资超过10000，抛出自定义异常，ex_toomuchmoney，并rollback
--3.3 如果修改成功，打印一条信息'Success'，

CREATE OR REPLACE PROCEDURE p_updatesal(emp_no IN NUMBER,sal1 IN NUMBER)
AS
ex_noempno      EXCEPTION;
ex_toomuchmoney EXCEPTION;
info            VARCHAR2(10):='Success';
va              number(1);
BEGIN
select 1 into va from t_emp where empno = emp_no;

if(0 = nvl(va,0)) then
raise ex_noempno;
end if;

if sal1 > 10000 then
raise ex_toomuchmoney;
end if;

update t_emp
     set sal = sal1
   where empno = emp_no;

commit;
dbms_output.put_line(info);

exception
when ex_noempno then
    rollback;
when ex_toomuchmoney then
    rollback;
when others then
    dbms_output.put_line(SQLCODE||':'||SQLERRM);
END p_updatesal;