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

kooness · 发表于 2010-10-26 09:00:03

以下的文章，主要为大家在实际工作中提供一种解决方法。
---用户名：scott
---密码：tiger
---*********Oracle表连接与子查询示例************
---求部门中哪些人的薪水最高

select

from

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

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

avg

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

from

where

not

in

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

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

select
max(sal) from emp；

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

select

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

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

from

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

from

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

--平均工资的最小值

select

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

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

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

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

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

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

richardtc · 发表于 2010-10-26 09:37:56

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

set

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

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

duolanshizhe · 发表于 2010-10-26 13:07:21

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