细数你应该修炼的Oracle基本功_Oracle_数据库_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
最新资讯 | 百度新闻 | GOOGLE地图 | RSS订阅 | 更多
热搜：
更多>>
 
您所在的位置： 程序员俱乐部 > 数据库 > Oracle > 细数你应该修炼的Oracle基本功

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

 2010/11/8 20:49:36    程序员俱乐部  我要评论(0)
  • 摘要：以下的文章，主要为大家在实际工作中提供一种解决方法。---用户名：scott---密码：tiger---*********Oracle表连接与子查询示例************---求部门中哪些人的薪水最高selectename,salfromempjoin(selectmax(sal)max_sal,deptnofromempgroupbydeptno)ton(emp.sal=t.max_salandemp.deptno=t.deptno);---求部门平均薪水的等级selectdeptno
  • 标签：Oracle基本功

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

---用户名：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  
  4. ) 

--平均工资的最小值

  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. )  
  9. ) 

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

  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. )  
  10. ) 

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

  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. )  
  11. ) 

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

----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 table, create 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 过程已成功完成。  
上一篇： 详解Oracle多种表连接方式 下一篇： oracle中使用SQL递归语句的例子
细数你应该修炼的Oracle基本功

发表评论
用户名: 匿名

最新文章
将服务器上的oracle数据备份到本地
将服务器上的o..
NoSQL侵入移动开发平台 CouchDB发布Android版本
NoSQL侵入..

最新标签
  • Oracle重做日志
  • oracle索引
  • Win7
  • 监听
  • 开放
  • 启动
  • 邮件系统
  • 新睿科技
  • SP2
  • 数据库索引

    • 今日热点

    推荐文章
    将服务器上的oracle数据备份到本地
    将服务器上的o..
    NoSQL侵入移动开发平台 CouchDB发布Android版本
    NoSQL侵入..