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

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

---用户名：scott

---密码：tiger

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

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

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

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

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

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

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

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

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

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

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

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

 
 
   
  
  select max(sal) from emp；

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

 
 
   
  
  select deptno , avg_sal from   
  
   (select avg(sal) avg_sal,deptno from emp group by deptno) t    
  
  where avg_sal =    
  
   (select  max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t)；

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

 
 
   
  
  select deptno , avg_sal from   
  
   (select avg(sal) avg_sal,deptno from emp group by deptno) t    
  
  where avg_sal =    
  
   (select  max(avg(sal)) from emp group by deptno)；

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

 
 
   
  
  select dname from dept     
  
  where deptno =    
  
  (    
  
   select deptno from   
  
   (select avg(sal) avg_sal,deptno from emp group by deptno) t    
  
   where avg_sal =    
  
   (select  max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t)    
  
  )；

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

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

--部门平均薪水

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

--平均工资的最小值

 
 
   
  
  select avg_sal,deptno from     
  
  (select avg(sal) avg_sal,deptno from emp group by deptno) t    
  
  where avg_sal =    
  
  (    
  
  select min(avg_sal) from   
  
  (    
  
  select avg(sal) avg_sal,deptno from emp group by deptno    
  
  )    
  
  )

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

 
 
   
  
  select t.avg_sal,t.deptno,s.grade from     
  
  (select avg(sal) avg_sal,deptno from emp group by deptno) t    
  
  join salgrade s on (t.avg_sal between s.losal and s.hisal)     
  
  where avg_sal =    
  
  (    
  
  select min(avg_sal) from   
  
  (    
  
  select avg(sal) avg_sal,deptno from emp group by deptno    
  
  )    
  
  )

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

 
 
   
  
  select d.dname,t.avg_sal,t.deptno,s.grade from     
  
  (select avg(sal) avg_sal,deptno from emp group by deptno) t    
  
  join salgrade s on (t.avg_sal between s.losal and s.hisal)     
  
  join dept d on (t.deptno = d.deptno)    
  
  where avg_sal =    
  
  (    
  
  select min(avg_sal) from   
  
  (    
  
  select avg(sal) avg_sal,deptno from emp group by deptno    
  
  )    
  
  )

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

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

 
 
   
  
  select t1.deptno,t1.avg_sal,grade,d.dname from   
  
  (    
  
  select deptno,avg_sal,grade from   
  
  (select deptno,avg(sal) avg_sal from emp group by deptno) t    
  
  join salgrade s on (t.avg_sal between s.losal and s.hisal)    
  
  ) t1    
  
  join dept d on (t1.deptno = d.deptno)     
  
  where grade =    
  
  (     
  
  select min(grade) from   
  
  (    
  
  select deptno,avg_sal,grade from   
  
  (select deptno,avg(sal) avg_sal from emp group by deptno) t    
  
  join salgrade s on (t.avg_sal between s.losal and s.hisal)    
  
  )    
  
  );

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

 
 
   
  
  conn sys/sys as sysdba;

--已连接。

 
 
   
  
  grant create table, create view to scott;

--授权成功。

---创建视图

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

--视图已建立。

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

 
 
   
  
  select t1.deptno,t1.avg_sal,grade,d.dname from   
  
  v$_dept_avg_sal_info t1    
  
  join dept d on (t1.deptno = d.deptno)     
  
  where grade =    
  
  (     
  
  select min(grade) from   
  
  v$_dept_avg_sal_info    
  
  );

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

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

--普通员工的最高薪水

 
 
   
  
  select ename from emp     
  
  where empno in (select distinct mgr from emp where mgr is not null)    
  
  and sal >    
  
  (    
  
  select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)    
  
  );

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

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

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

 
 
   
  
  select ename,sal from   
  
  (select ename,sal,rownum r from   
  
  (    
  
  select ename, sal from emp order by sal desc   
  
  )    
  
  ) where r>=6 and r<=10;

---五种约束条件

 
 
   
  
  create table stu    
  
  (     
  
  id number(2),    
  
  name varchar2(20) constraint stu_name_nn not null,--非空约束    
  
  sex  number(2),    
  
  age number(3),    
  
  sdate date,    
  
  grade number(3) default 1,    
  
  class number(3),    
  
  email varchar2(50),    
  
  constraint stu_name_email_uin unique(name,email)--唯一主键    
  
  ) ;    
  
     
  
     
  
  insert into stu(name,email) values('','tianyuexing@163.com')   
  
  --ORA-01400: 无法将 NULL 插入 ("SCOTT"."STU"."NAME")    
  
     
  
     
  
  insert into stu(name,email) values('tianyuexing','tianyuexing@163.com');    
  
  insert into stu(name,email) values('tianyuexing','tianyuexing@163.com');  
  
  --ORA-00001: 违反唯一约束条件 (SCOTT.STU_NAME_EMAIL_UIN)

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

 
 
   
  
  set serveroutput on;    
  
   declare   
  
  v_num number :=0;    
  
  begin   
  
  v_num :=2/v_num;    
  
  dbms_output.put_line(v_num);    
  
  exception    
  
  when others then   
  
  dbms_output.put_line('error');    
  
  end;

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

 
 
   
  
  declare   
  
  v_empno2 emp.empno%type;    
  
  begin   
  
  dbms_output.put_line('test');    
  
  end;

---Table 变量类型

 
 
   
  
  declare   
  
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;    
  
  v_empnos type_table_emp_empno;    
  
  begin   
  
  v_empnos(0) := 2999;    
  
  v_empnos(1) := 2434;    
  
  v_empnos(-1) := 8989;    
  
  dbms_output.put_line(v_empnos(-1));    
  
  end;

---Record 变量类型

 
 
   
  
  declare   
  
  type type_record_dept is record    
  
  (    
  
  deptno dept.deptno%type,    
  
  dname  dept.dname%type,    
  
  loc dept.loc%type    
  
  );    
  
  v_temp type_record_dept;    
  
  begin   
  
  v_temp.deptno := 20;    
  
  v_temp.dname := 'tianyuexing';    
  
  v_temp.loc := 'qhd';    
  
  dbms_output.put_line(v_temp.dname || ' ' ||v_temp.loc);    
  
  end;

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

 
 
   
  
  declare   
  
  v_temp dept%rowtype;    
  
  begin   
  
  v_temp.deptno := 20;    
  
  v_temp.dname := 'yuexingtian';    
  
  v_temp.loc := 'qhd';    
  
  dbms_output.put_line(v_temp.dname || ' ' ||v_temp.loc);    
  
  end;

---SQL语句的运用

 
 
   
  
  declare   
  
  v_ename emp.ename%type;    
  
  v_sal emp.sal%type;    
  
  begin   
  
  select ename,sal into v_ename,v_sal from emp where empno = 7369;    
  
  dbms_output.put_line(v_ename ||' '||v_sal);    
  
  end;    
  
     
  
     
  
  declare   
  
  v_emp emp%rowtype;    
  
  begin   
  
  select * into v_emp from emp where empno = 7369;    
  
  dbms_output.put_line(v_emp.ename);    
  
  end;    
  
  --insert 语句    
  
  declare   
  
  v_deptno dept.deptno%type := 50;    
  
  v_dname dept.dname%type :='yuexingtian';    
  
  v_loc dept.loc%type := '秦皇岛';    
  
  begin   
  
  insert into dept2 values (v_deptno,v_dname,v_loc);    
  
  commit;    
  
  end;

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

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

--create语句

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

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

 
 
   
  
  declare   
  
  v_sal emp.sal%type;    
  
  begin   
  
  select sal into v_sal from emp    
  
  where empno = 7369;    
  
  if(v_sal < 1200) then   
  
  dbms_output.put_line('low');    
  
  elsif(v_sal < 2000) then   
  
  dbms_output.put_line('middle');    
  
  else   
  
  dbms_output.put_line('high');    
  
  end if;    
  
  end;

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

 
 
   
  
  declare   
  
  i binary_integer := 1;    
  
  begin   
  
  loop    
  
  dbms_output.put_line(i);    
  
  i := i+1;    
  
  exit when (i>=11);    
  
  end loop;    
  
  end;     
  
  ---when ……loop （相当于while）    
  
  declare   
  
  j binary_integer := 1;     
  
  begin   
  
  while j<11 loop    
  
  dbms_output.put_line(j);    
  
  j := j+1;    
  
  end loop;    
  
  end;     
  
  ---for ...in... loop     
  
  begin   
  
  for k in 1..10 loop    
  
  dbms_output.put_line(k);    
  
  end loop;    
  
  for k in reverse 1..10 loop --逆序    
  
  dbms_output.put_line(k);    
  
  end loop;     
  
  end;

--- 异常处理

 
 
   
  
  declare   
  
  v_temp number(4);    
  
  begin   
  
  select empno into v_temp from emp where deptno = 10;    
  
  exception    
  
  when too_many_rows then --多条记录的异常    
  
  dbms_output.put_line('记录太多了');    
  
  when others then   
  
  dbms_output.put_line('error');    
  
  end;    
  
     
  
     
  
  declare   
  
  v_temp number(4);    
  
  begin   
  
  select empno into v_temp from emp where empno = 4444;    
  
  exception    
  
  when no_data_found then   
  
  dbms_output.put_line('没有数据');    
  
  end;

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

 
 
   
  
  create table errorlog    
  
  (    
  
  id number primary key,    
  
  errcode number,    
  
  errmsg varchar2(1024),    
  
  errdate date   
  
  );    
  
     
  
     
  
  create sequence seq_errorlog_id start with 1 increment by 1; --创建递增序列    
  
     
  
     
  
  --PL/SQL    
  
  declare   
  
  v_deptno dept.deptno%type :=10;    
  
  v_errcode number;    
  
  v_errmsg varchar2(1024);    
  
  begin   
  
  delete from dept where deptno = v_deptno;    
  
  commit;    
  
  exception    
  
  when others then   
  
  rollback;    
  
  v_errcode := SQLCODE;    
  
  v_errmsg := SQLERRM;    
  
  insert into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);    
  
  commit;    
  
  end;    
  
     
  
     
  
  select to_char(errdate,'YYYY-MM-DD HH24:MI:ss') from errorlog; ---具体的出错时间。

---游标

 
 
   
  
  declare   
  
  cursor c is     
  
  select * from emp;    
  
  v_emp c%rowtype;    
  
  begin   
  
  open c;    
  
  fetch c into v_emp;    
  
  dbms_output.put_line(v_emp.ename);    
  
  close c;    
  
  end;    
  
  ---游标，循环取出所有的记录。    
  
  declare   
  
  cursor c is   
  
  select * from emp;    
  
  v_emp c%rowtype;    
  
  begin   
  
  open c;    
  
  loop    
  
  fetch c into v_emp;    
  
  exit when (c%notfound);    
  
  dbms_output.put_line(v_emp.ename);    
  
  end loop;    
  
  close c;     
  
  end;     
  
  ---游标while 循环    
  
  declare   
  
  cursor c is   
  
  select * from emp;    
  
  v_emp c%rowtype;    
  
  begin   
  
  open c;    
  
  fetch c into v_emp;    
  
  while (c%found) loop    
  
  dbms_output.put_line(v_emp.ename);    
  
  fetch c into v_emp;    
  
  end loop;    
  
  close c;    
  
  end;     
  
  ---for循环 不用声明变量，不用open游标 不用close游标 不用fetch    
  
  declare   
  
  cursor c is     
  
  select * from emp;    
  
  begin   
  
  for v_emp in c loop    
  
  dbms_output.put_line(v_emp.ename);    
  
  end loop;    
  
  end;

---带参数的游标

 
 
   
  
  declare   
  
  cursor c(v_deptno emp.deptno%type, v_job emp.job%type)    
  
  is   
  
  select ename,sal from emp where deptno = v_deptno and job = v_job;    
  
  begin   
  
  for v_temp in c(30,'CLERK') loop    
  
  dbms_output.put_line(v_temp.ename);    
  
  end loop;    
  
  end;

---课更新的游标

 
 
   
  
  declare   
  
  cursor c     
  
  is   
  
  select * from emp2 for update;    
  
  begin   
  
  for v_temp in c loop    
  
  if (v_temp.sal < 2000) then   
  
  update emp2 set sal = sal * 2 where current of c;     
  
  elsif (v_temp.sal = 5000) then   
  
  delete from emp2 where current of c;    
  
  end if;    
  
  end loop;    
  
  commit;    
  
  end;

----创建存储过程

 
 
   
  
  create or replace procedure p    
  
  is   
  
  cursor c is   
  
  select * from emp2 for update;    
  
  begin   
  
  for v_emp in c loop    
  
  if (v_emp.deptno = 10) then   
  
  update emp2 set sal = sal + 10 where current of c;    
  
  elsif (v_emp.deptno = 20) then   
  
  update emp2 set sal = sal + 20 where current of c;    
  
  else   
  
  update emp2 set sal = sal + 50 where current of c;    
  
  end if;    
  
  end loop;    
  
  commit;    
  
  end;     
  
  ---执行存储过程    
  
  exec p;    
  
  ---或者    
  
  begin   
  
  p;    
  
  end;

---带参数的存储过程

 
 
   
  
  create or replace procedure   
  
  max_num(v_a in number,v_b number,v_ret out number,v_temp in out number)    
  
  is   
  
  begin   
  
  if(v_a > v_b) then   
  
  v_ret := v_a;    
  
  else   
  
  v_ret := v_b;    
  
  end if;    
  
  v_temp := v_temp + 1;    
  
  end;    
  
  ---调用这个存储过程    
  
  declare   
  
  v_a number :=3;    
  
  v_b number :=4;    
  
  v_ret number;    
  
  v_temp number :=5;    
  
  begin   
  
  max_num(v_a, v_b, v_ret, v_temp);    
  
  dbms_output.put_line(v_ret);    
  
  dbms_output.put_line(v_temp);    
  
  end;

---函数

 
 
   
  
  create or replace function     
  
  sal_tax(v_sal number)    
  
  return number    
  
  is   
  
  begin   
  
  if (v_sal < 2000) then   
  
  return 0.10;    
  
  elsif (v_sal < 2750) then   
  
  return 0.15;    
  
  else   
  
  return 0.20;    
  
  end if;    
  
  end;     
  
  --调用这个函数(别的函数怎么用，这个函数就怎么用)    
  
  select ename,sal,sal_tax(sal) from emp;

----触发器

 
 
   
  
  --创建一个日志表    
  
  create table emp2_log    
  
  (    
  
  uname varchar2(20),    
  
  action varchar2(10),    
  
  atime date   
  
  );    
  
  --创建一个触发器    
  
  create or replace trigger trig    
  
  after insert or delete or update on emp2 for each row    
  
  begin   
  
  if inserting then   
  
  insert into emp2_log values (user,'insert',sysdate);    
  
  elsif updating then   
  
  insert into emp2_log values (user,'update',sysdate);    
  
  elsif deleting then   
  
  insert into emp2_log values (user,'delete',sysdate);    
  
  end if;    
  
  end;    
  
  --调用这个触发器    
  
  update emp2 set sal = sal*2 where deptno = 30;

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

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

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

 
 
   
  
  drop table article;    
  
  create table article    
  
  (    
  
  id number primary key,    
  
  cont varchar2(4000),    
  
  pid number,    
  
  isleaf number(1),--0 代表非叶子节点，1 代表叶子节点    
  
  alevel number(2)    
  
  );    
  
  insert into article values(1,'蚂蚁大战大象',0,0,0);    
  
  insert into article values(2,'蚂蚁大战大象',1,0,1);    
  
  insert into article values(3,'蚂蚁大战大象',2,1,2);    
  
  insert into article values(4,'蚂蚁大战大象',2,0,2);    
  
  insert into article values(5,'蚂蚁大战大象',4,1,3);    
  
  insert into article values(6,'蚂蚁大战大象',1,0,1);    
  
  insert into article values(7,'蚂蚁大战大象',6,1,2);    
  
  insert into article values(8,'蚂蚁大战大象',6,1,2);    
  
  insert into article values(9,'蚂蚁大战大象',2,0,2);    
  
  insert into article values(10,'蚂蚁大战大象',9,1,3);    
  
  commit;

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

 
 
   
  
  create or replace procedure p_tree(v_pid article.pid%type, v_level binary_integer) is   
  
  cursor c is select * from article where pid = v_pid;    
  
  v_preStr varchar2(1024) :='';    
  
  begin   
  
  for i in 1..v_level loop    
  
  v_preStr := v_preStr || '****';    
  
  end loop;    
  
  for v_article in c loop    
  
  dbms_output.put_line(v_preStr || v_article.cont);    
  
  if(v_article.isleaf = 0) then   
  
  p_tree(v_article.id, v_level + 1);    
  
  end if;    
  
  end loop;    
  
  end;     
  
  --执行这个存储过程    
  
  exec p_tree(0,0);     
  
  --SQL> exec p_tree(0,0);    
  
  --蚂蚁大战大象    
  
  --****蚂蚁大战大象    
  
  --********蚂蚁大战大象    
  
  --********蚂蚁大战大象    
  
  --************蚂蚁大战大象    
  
  --********蚂蚁大战大象    
  
  --************蚂蚁大战大象    
  
  --****蚂蚁大战大象    
  
  --********蚂蚁大战大象                          
  
  --********蚂蚁大战大象                          
  
  --PL/SQL 过程已成功完成。

【编辑推荐】

Oracle 数据集成的实际解决方案
对Oracle Raw常见类型的解释
Oracle归档模式的相关切换的实际操作步骤
Oracle数据库的启用与关闭的归档模式
Oracle修改字段类型2种方案介绍

网页名称：细数你应该修炼的Oracle基本功
网站路径：http://zsjierui.cn/article/coochgo.html

网站建设知识

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

其他资讯