细数你应该修炼的Oracle基本功

数据库 Oracle
Oracle数据库的基本功,主要涉及一些小算法,比如求部门中谁的薪水最高,谁是经理等等。希望能满足那些苦苦找寻正确解决方法的DBA们。

以下的文章,主要为大家在实际工作中提供一种解决方法。

---用户名:scott

---密  码:tiger

---*********Oracle表连接与子查询示例************

---求部门中哪些人的薪水最高

  1. select ename,sal from emp  
  2. join (select max(sal) max_sal, deptno from emp group by deptno) t  
  3. on (emp.sal = t.max_sal and emp.deptno = t.deptno); 

---求部门平均薪水的等级

  1. select deptno,avg_sal,grade from 
  2. (select deptno,avg(sal) avg_sal from emp group by deptno) t  
  3. join salgrade s on (t.avg_sal between s.losal and s.hisal); 

---求部门平均的薪水等级

  1. select deptno,avg(grade) from 
  2. (select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t  
  3. group by deptno; 

---雇员中哪些人是经理人

  1. select ename from emp where empno in (select distinct mgr from  emp); 

---不用组函数,求薪水的最高值

  1. select sal from emp where sal not in 
  2. (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal)); 

---用组函数,求薪水的最高值

  1. select max(sal) from emp; 

---求平均薪水最高的部门的部门编号

  1. select deptno , avg_sal from 
  2.  (select avg(sal) avg_sal,deptno from emp group by deptno) t  
  3. where avg_sal =  
  4.  (select  max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t); 

----组函数嵌套的写法

  1. select deptno , avg_sal from 
  2.  (select avg(sal) avg_sal,deptno from emp group by deptno) t  
  3. where avg_sal =  
  4.  (select  max(avg(sal)) from emp group by deptno); 

---求平均薪水最高的部门的名称

  1. select dname from dept   
  2. where deptno =  
  3. (  
  4.  select deptno from 
  5.  (select avg(sal) avg_sal,deptno from emp group by deptno) t  
  6.  where avg_sal =  
  7.  (select  max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t)  
  8. ); 

---求平均薪水的等级最低的部门的部门名称

  1. select avg(sal) avg_sal,deptno from emp group by deptno  

--部门平均薪水

  1. select min(avg_sal) from 
  2. (  
  3. select avg(sal) avg_sal,deptno from emp group by deptno  

--平均工资的最小值

  1. select avg_sal,deptno from   
  2. (select avg(sal) avg_sal,deptno from emp group by deptno) t  
  3. where avg_sal =  
  4. (  
  5. select min(avg_sal) from 
  6. (  
  7. select avg(sal) avg_sal,deptno from emp group by deptno  
  8. )  

--平均工资的最小值及部门编号

  1. select t.avg_sal,t.deptno,s.grade from   
  2. (select avg(sal) avg_sal,deptno from emp group by deptno) t  
  3. join salgrade s on (t.avg_sal between s.losal and s.hisal)   
  4. where avg_sal =  
  5. (  
  6. select min(avg_sal) from 
  7. (  
  8. select avg(sal) avg_sal,deptno from emp group by deptno  
  9. )  

--平均工资的最小值及部门编号和工资等级

  1. select d.dname,t.avg_sal,t.deptno,s.grade from   
  2. (select avg(sal) avg_sal,deptno from emp group by deptno) t  
  3. join salgrade s on (t.avg_sal between s.losal and s.hisal)   
  4. join dept d on (t.deptno = d.deptno)  
  5. where avg_sal =  
  6. (  
  7. select min(avg_sal) from 
  8. (  
  9. select avg(sal) avg_sal,deptno from emp group by deptno  
  10. )  

--平均工资的最小值及部门编号和工资等级及部门名称

----Another 按照题意的写法

  1. select t1.deptno,t1.avg_sal,grade,d.dname from 
  2. (  
  3. select deptno,avg_sal,grade from 
  4. (select deptno,avg(sal) avg_sal from emp group by deptno) t  
  5. join salgrade s on (t.avg_sal between s.losal and s.hisal)  
  6. ) t1  
  7. join dept d on (t1.deptno = d.deptno)   
  8. where grade =  
  9. (   
  10. select min(grade) from 
  11. (  
  12. select deptno,avg_sal,grade from 
  13. (select deptno,avg(sal) avg_sal from emp group by deptno) t  
  14. join salgrade s on (t.avg_sal between s.losal and s.hisal)  
  15. )  
  16. ); 

---创建视图或者表,如果没有权限

  1. conn sys/sys as sysdba; 

--已连接。

  1. grant create tablecreate view to scott; 

--授权成功。

---创建视图

  1. create view v$_dept_avg_sal_info as 
  2.   select deptno,avg_sal,grade from 
  3. (select deptno,avg(sal) avg_sal from emp group by deptno) t  
  4. join salgrade s on (t.avg_sal between s.losal and s.hisal); 

--视图已建立。

---创建这个v$_dept_avg_sal_info视图可以简化上面那个查询的重复代码

  1. select t1.deptno,t1.avg_sal,grade,d.dname from 
  2. v$_dept_avg_sal_info t1  
  3. join dept d on (t1.deptno = d.deptno)   
  4. where grade =  
  5. (   
  6. select min(grade) from 
  7. v$_dept_avg_sal_info  
  8. ); 

---求比普通员工的最高薪水还要高的经理的名称

  1. select max(sal) from emp where empno not in 
  2. (select distinct mgr from emp where mgr is not null); 

--普通员工的最高薪水

  1. select ename from emp   
  2. where empno in (select distinct mgr from emp where mgr is not null)  
  3. and sal >  
  4. (  
  5. select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)  
  6. ); 

--普通员工的最高薪水还要高的经理的名称

--- Oracle 联机归档日志 备份方式

---求薪水最高的第6名到第10名雇员(rownum)

  1. select ename,sal from 
  2. (select ename,sal,rownum r from 
  3. (  
  4. select ename, sal from emp order by sal desc 
  5. )  
  6. where r>=6 and r<=10; 

---五种约束条件

  1. create table stu  
  2. (   
  3. id number(2),  
  4. name varchar2(20) constraint stu_name_nn not null,--非空约束  
  5. sex  number(2),  
  6. age number(3),  
  7. sdate date,  
  8. grade number(3) default 1,  
  9. class number(3),  
  10. email varchar2(50),  
  11. constraint stu_name_email_uin unique(name,email)--唯一主键  
  12. ) ;  
  13.  
  14.  
  15. insert into stu(name,email) values('','tianyuexing@163.com'
  16. --ORA-01400: 无法将 NULL 插入 ("SCOTT"."STU"."NAME")  
  17.  
  18.  
  19. insert into stu(name,email) values('tianyuexing','tianyuexing@163.com');  
  20. insert into stu(name,email) values('tianyuexing','tianyuexing@163.com');
  21. --ORA-00001: 违反唯一约束条件 (SCOTT.STU_NAME_EMAIL_UIN) 

---PL/SQL 一个简单的存储过程 分为四块1.声明declare 2.begin 3.exception 4.end

  1. set serveroutput on;  
  2.  declare 
  3. v_num number :=0;  
  4. begin 
  5. v_num :=2/v_num;  
  6. dbms_output.put_line(v_num);  
  7. exception  
  8. when others then 
  9. dbms_output.put_line('error');  
  10. end

---%type 变量声明的好处。

  1. declare 
  2. v_empno2 emp.empno%type;  
  3. begin 
  4. dbms_output.put_line('test');  
  5. end

---Table 变量类型

  1. declare 
  2. type type_table_emp_empno is table of emp.empno%type index by binary_integer;  
  3. v_empnos type_table_emp_empno;  
  4. begin 
  5. v_empnos(0) := 2999;  
  6. v_empnos(1) := 2434;  
  7. v_empnos(-1) := 8989;  
  8. dbms_output.put_line(v_empnos(-1));  
  9. end

---Record 变量类型

  1. declare 
  2. type type_record_dept is record  
  3. (  
  4. deptno dept.deptno%type,  
  5. dname  dept.dname%type,  
  6. loc dept.loc%type  
  7. );  
  8. v_temp type_record_dept;  
  9. begin 
  10. v_temp.deptno := 20;  
  11. v_temp.dname := 'tianyuexing';  
  12. v_temp.loc := 'qhd';  
  13. dbms_output.put_line(v_temp.dname || ' ' ||v_temp.loc);  
  14. end

---使用 %rowtype声明record变量

  1. declare 
  2. v_temp dept%rowtype;  
  3. begin 
  4. v_temp.deptno := 20;  
  5. v_temp.dname := 'yuexingtian';  
  6. v_temp.loc := 'qhd';  
  7. dbms_output.put_line(v_temp.dname || ' ' ||v_temp.loc);  
  8. end

---SQL语句的运用

  1. declare 
  2. v_ename emp.ename%type;  
  3. v_sal emp.sal%type;  
  4. begin 
  5. select ename,sal into v_ename,v_sal from emp where empno = 7369;  
  6. dbms_output.put_line(v_ename ||' '||v_sal);  
  7. end;  
  8.  
  9.  
  10. declare 
  11. v_emp emp%rowtype;  
  12. begin 
  13. select * into v_emp from emp where empno = 7369;  
  14. dbms_output.put_line(v_emp.ename);  
  15. end;  
  16. --insert 语句  
  17. declare 
  18. v_deptno dept.deptno%type := 50;  
  19. v_dname dept.dname%type :='yuexingtian';  
  20. v_loc dept.loc%type := '秦皇岛';  
  21. begin 
  22. insert into dept2 values (v_deptno,v_dname,v_loc);  
  23. commit;  
  24. end

---sql%rowcount 多少条记录被影响

  1. declare 
  2. v_deptno emp2.deptno%type := 10;  
  3. v_count number;  
  4. begin 
  5. update emp2 set sal = sal/2 where deptno = v_deptno;  
  6. dbms_output.put_line(sql%rowcount ||'条记录被影响');  
  7. end

--create语句

  1. begin 
  2. execute immediate 'create table T (nnn varchar2(20) default ''yuexingtian'')';  
  3. end

---if语句,取出7369的薪水,如果<1200,则输出'low',如果<2000则输出'middle',否则输出'high'.

  1. declare 
  2. v_sal emp.sal%type;  
  3. begin 
  4. select sal into v_sal from emp  
  5. where empno = 7369;  
  6. if(v_sal < 1200) then 
  7. dbms_output.put_line('low');  
  8. elsif(v_sal < 2000) then 
  9. dbms_output.put_line('middle');  
  10. else 
  11. dbms_output.put_line('high');  
  12. end if;  
  13. end

---循环 loop (相当于do while)

  1. declare 
  2. i binary_integer := 1;  
  3. begin 
  4. loop  
  5. dbms_output.put_line(i);  
  6. i := i+1;  
  7. exit when (i>=11);  
  8. end loop;  
  9. end;   
  10. ---when ……loop (相当于while)  
  11. declare 
  12. j binary_integer := 1;   
  13. begin 
  14. while j<11 loop  
  15. dbms_output.put_line(j);  
  16. j := j+1;  
  17. end loop;  
  18. end;   
  19. ---for ...in... loop   
  20. begin 
  21. for k in 1..10 loop  
  22. dbms_output.put_line(k);  
  23. end loop;  
  24. for k in reverse 1..10 loop --逆序  
  25. dbms_output.put_line(k);  
  26. end loop;   
  27. end

--- 异常处理

  1. declare 
  2. v_temp number(4);  
  3. begin 
  4. select empno into v_temp from emp where deptno = 10;  
  5. exception  
  6. when too_many_rows then --多条记录的异常  
  7. dbms_output.put_line('记录太多了');  
  8. when others then 
  9. dbms_output.put_line('error');  
  10. end;  
  11.  
  12.  
  13. declare 
  14. v_temp number(4);  
  15. begin 
  16. select empno into v_temp from emp where empno = 4444;  
  17. exception  
  18. when no_data_found then 
  19. dbms_output.put_line('没有数据');  
  20. end

---记录数据库错误信息的errorlog

  1. create table errorlog  
  2. (  
  3. id number primary key,  
  4. errcode number,  
  5. errmsg varchar2(1024),  
  6. errdate date 
  7. );  
  8.  
  9.  
  10. create sequence seq_errorlog_id start with 1 increment by 1; --创建递增序列  
  11.  
  12.  
  13. --PL/SQL  
  14. declare 
  15. v_deptno dept.deptno%type :=10;  
  16. v_errcode number;  
  17. v_errmsg varchar2(1024);  
  18. begin 
  19. delete from dept where deptno = v_deptno;  
  20. commit;  
  21. exception  
  22. when others then 
  23. rollback;  
  24. v_errcode := SQLCODE;  
  25. v_errmsg := SQLERRM;  
  26. insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);  
  27. commit;  
  28. end;  
  29.  
  30.  
  31. select to_char(errdate,'YYYY-MM-DD HH24:MI:ss'from errorlog; ---具体的出错时间。 

---游标

  1. declare 
  2. cursor c is   
  3. select * from emp;  
  4. v_emp c%rowtype;  
  5. begin 
  6. open c;  
  7. fetch c into v_emp;  
  8. dbms_output.put_line(v_emp.ename);  
  9. close c;  
  10. end;  
  11. ---游标,循环取出所有的记录。  
  12. declare 
  13. cursor c is 
  14. select * from emp;  
  15. v_emp c%rowtype;  
  16. begin 
  17. open c;  
  18. loop  
  19. fetch c into v_emp;  
  20. exit when (c%notfound);  
  21. dbms_output.put_line(v_emp.ename);  
  22. end loop;  
  23. close c;   
  24. end;   
  25. ---游标while 循环  
  26. declare 
  27. cursor c is 
  28. select * from emp;  
  29. v_emp c%rowtype;  
  30. begin 
  31. open c;  
  32. fetch c into v_emp;  
  33. while (c%found) loop  
  34. dbms_output.put_line(v_emp.ename);  
  35. fetch c into v_emp;  
  36. end loop;  
  37. close c;  
  38. end;   
  39. ---for循环 不用声明变量,不用open游标 不用close游标 不用fetch  
  40. declare 
  41. cursor c is   
  42. select * from emp;  
  43. begin 
  44. for v_emp in c loop  
  45. dbms_output.put_line(v_emp.ename);  
  46. end loop;  
  47. end

---带参数的游标

  1. declare 
  2. cursor c(v_deptno emp.deptno%type, v_job emp.job%type)  
  3. is 
  4. select ename,sal from emp where deptno = v_deptno and job = v_job;  
  5. begin 
  6. for v_temp in c(30,'CLERK') loop  
  7. dbms_output.put_line(v_temp.ename);  
  8. end loop;  
  9. end

---课更新的游标

  1. declare 
  2. cursor c   
  3. is 
  4. select * from emp2 for update;  
  5. begin 
  6. for v_temp in c loop  
  7. if (v_temp.sal < 2000) then 
  8. update emp2 set sal = sal * 2 where current of c;   
  9. elsif (v_temp.sal = 5000) then 
  10. delete from emp2 where current of c;  
  11. end if;  
  12. end loop;  
  13. commit;  
  14. end

----创建存储过程

  1. create or replace procedure p  
  2. is 
  3. cursor c is 
  4. select * from emp2 for update;  
  5. begin 
  6. for v_emp in c loop  
  7. if (v_emp.deptno = 10) then 
  8. update emp2 set sal = sal + 10 where current of c;  
  9. elsif (v_emp.deptno = 20) then 
  10. update emp2 set sal = sal + 20 where current of c;  
  11. else 
  12. update emp2 set sal = sal + 50 where current of c;  
  13. end if;  
  14. end loop;  
  15. commit;  
  16. end;   
  17. ---执行存储过程  
  18. exec p;  
  19. ---或者  
  20. begin 
  21. p;  
  22. end

---带参数的存储过程

  1. create or replace procedure 
  2. max_num(v_a in number,v_b number,v_ret out number,v_temp in out number)  
  3. is 
  4. begin 
  5. if(v_a > v_b) then 
  6. v_ret := v_a;  
  7. else 
  8. v_ret := v_b;  
  9. end if;  
  10. v_temp := v_temp + 1;  
  11. end;  
  12. ---调用这个存储过程  
  13. declare 
  14. v_a number :=3;  
  15. v_b number :=4;  
  16. v_ret number;  
  17. v_temp number :=5;  
  18. begin 
  19. max_num(v_a, v_b, v_ret, v_temp);  
  20. dbms_output.put_line(v_ret);  
  21. dbms_output.put_line(v_temp);  
  22. end

---函数

  1. create or replace function   
  2. sal_tax(v_sal number)  
  3. return number  
  4. is 
  5. begin 
  6. if (v_sal < 2000) then 
  7. return 0.10;  
  8. elsif (v_sal < 2750) then 
  9. return 0.15;  
  10. else 
  11. return 0.20;  
  12. end if;  
  13. end;   
  14. --调用这个函数(别的函数怎么用,这个函数就怎么用)  
  15. select ename,sal,sal_tax(sal) from emp; 

----触发器

  1. --创建一个日志表  
  2. create table emp2_log  
  3. (  
  4. uname varchar2(20),  
  5. action varchar2(10),  
  6. atime date 
  7. );  
  8. --创建一个触发器  
  9. create or replace trigger trig  
  10. after insert or delete or update on emp2 for each row  
  11. begin 
  12. if inserting then 
  13. insert into emp2_log values (user,'insert',sysdate);  
  14. elsif updating then 
  15. insert into emp2_log values (user,'update',sysdate);  
  16. elsif deleting then 
  17. insert into emp2_log values (user,'delete',sysdate);  
  18. end if;  
  19. end;  
  20. --调用这个触发器  
  21. update emp2 set sal = sal*2 where deptno = 30; 

---更改有依赖关系的表的字段值的建立的一个触发器

  1. create or replace trigger trip_change  
  2. after update on dept2  
  3. for each row  
  4. begin 
  5. update emp2 set deptno = :NEW.deptno where deptno = :OLD.deptno;  
  6. end;   
  7. ---触发这个触发器  
  8. update dept2 set deptno = 99 where deptno = 10; 

---树状结构的存储与展示

  1. drop table article;  
  2. create table article  
  3. (  
  4. id number primary key,  
  5. cont varchar2(4000),  
  6. pid number,  
  7. isleaf number(1),--0 代表非叶子节点,1 代表叶子节点  
  8. alevel number(2)  
  9. );  
  10. insert into article values(1,'蚂蚁大战大象',0,0,0);  
  11. insert into article values(2,'蚂蚁大战大象',1,0,1);  
  12. insert into article values(3,'蚂蚁大战大象',2,1,2);  
  13. insert into article values(4,'蚂蚁大战大象',2,0,2);  
  14. insert into article values(5,'蚂蚁大战大象',4,1,3);  
  15. insert into article values(6,'蚂蚁大战大象',1,0,1);  
  16. insert into article values(7,'蚂蚁大战大象',6,1,2);  
  17. insert into article values(8,'蚂蚁大战大象',6,1,2);  
  18. insert into article values(9,'蚂蚁大战大象',2,0,2);  
  19. insert into article values(10,'蚂蚁大战大象',9,1,3);  
  20. commit

---用存储过程展示树状结构(用递归的方式实现)

  1. create or replace procedure p_tree(v_pid article.pid%type, v_level binary_integer) is 
  2. cursor c is select * from article where pid = v_pid;  
  3. v_preStr varchar2(1024) :='';  
  4. begin 
  5. for i in 1..v_level loop  
  6. v_preStr := v_preStr || '****';  
  7. end loop;  
  8. for v_article in c loop  
  9. dbms_output.put_line(v_preStr || v_article.cont);  
  10. if(v_article.isleaf = 0) then 
  11. p_tree(v_article.id, v_level + 1);  
  12. end if;  
  13. end loop;  
  14. end;   
  15. --执行这个存储过程  
  16. exec p_tree(0,0);   
  17. --SQL> exec p_tree(0,0);  
  18. --蚂蚁大战大象  
  19. --****蚂蚁大战大象  
  20. --********蚂蚁大战大象  
  21. --********蚂蚁大战大象  
  22. --************蚂蚁大战大象  
  23. --********蚂蚁大战大象  
  24. --************蚂蚁大战大象  
  25. --****蚂蚁大战大象  
  26. --********蚂蚁大战大象                        
  27. --********蚂蚁大战大象                        
  28. --PL/SQL 过程已成功完成。  

【编辑推荐】

  1. Oracle 数据集成的实际解决方案
  2. 对Oracle Raw常见类型的解释
  3. Oracle归档模式的相关切换的实际操作步骤
  4. Oracle数据库的启用与关闭的归档模式
  5. Oracle修改字段类型2种方案介绍
责任编辑:彭凡 来源: Chinaunix
相关推荐

2024-11-01 08:34:18

Spring配置@Bean

2014-11-20 14:39:12

网络传输

2009-10-10 16:57:33

布线工艺要求

2020-11-20 14:16:20

Python开发表格

2015-01-15 09:21:24

TCP窗口

2017-04-12 10:40:34

公有云

2020-11-06 09:05:18

前端web开发

2022-03-31 15:17:04

JavaSocketServlet容器

2017-02-27 21:30:29

数据中心光纤电缆

2020-12-07 10:38:13

Python开发语言

2020-10-21 09:18:50

程序员前端Github

2011-07-22 16:43:37

java

2023-06-28 11:58:00

2011-11-28 09:26:57

2012-05-09 17:32:42

云管理平台架构

2010-06-28 21:33:17

eMule协议

2017-01-15 17:15:27

Java基本功能

2022-09-09 16:38:09

Linux

2018-04-26 15:00:50

营销

2020-11-04 16:34:45

单元测试技术
点赞
收藏

51CTO技术栈公众号