细数你应该修炼的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  
• );

---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;

很好   整理的还可以   适用范围一般