PL/SQL 几个重要的语法

    好长一段时间没有写PL/SQL了，发现很多东西不用就忘记了，所以现在决定回过头来把学过的东西整理一下，方便以后查阅，也算为需要的朋友贡献点资源吧，虽然网上很多这样的资料。

    以下这些用法，平时有的可能用得不多，但是个人觉得还是蛮有用的。

 

--回顾merge into的用法

merge into emp_1 a

using emp b

on (a.empno = b.empno)

when matched then

update set a.ename=b.ename,a.sal=b.sal

when not matched then

insert values(b.empno ,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno);

-----------------

--回顾returning的用法

declare

v_ename emp.ename%type;

begin

update emp set ename='haha' where empno=7369 returning ename into v_ename;

dbms_output.put_line(v_ename);

end;

-------

 

---------一次删除表的多个列

alter table emp_2 drop (Hiredate,sal,comm);

--------

 

-------回顾多表insert

--------insert all

--不带条件的插入

insert all

into emp_1 values(empno,ename,job,mgr,hiredate,sal,deptno)

into emp_2 values(empno,ename,job,deptno)

select * from  emp ;

 

--带条件的多表插入

insert all

when empno=7566 then into emp_2 values(empno,ename,job,deptno)

else

into emp_1 values(empno,ename,job,mgr,hiredate,sal,deptno)

select * from emp ;

 

-------insert first

insert first

when empno=7839 then into emp_1 values(empno,ename,job,mgr,hiredate,sal,deptno)

when deptno=10 then into emp_2 values(empno,ename,job,deptno)

select * from emp

--执行后的结果，emp_1一条数据，emp_2两条数据 (10号部门总共3个员工，7839也是10部门)

 

 

insert first

when deptno=10 then into emp_2 values(empno,ename,job,deptno)

when empno=7839 then into emp_1 values(empno,ename,job,mgr,hiredate,sal,deptno)

select * from emp

--执行结果，emp_2 三条数据，emp_1 没有数据

 

insert All

when empno=7839 then into emp_1 values(empno,ename,job,mgr,hiredate,sal,deptno)

when deptno=10 then into emp_2 values(empno,ename,job,deptno)

select * from emp

--执行结果，emp_1表一条数据，emp_2表三条数据

 

 

/*由对比可得，使用insert first 的时候，当数据同时满足多个when 时，只会往第一个then插入数据。

 但是使用insert all 的时候，数据满足几个条件会执行几个then，即会重复插入

*/

 

 

---------group by \ rollup\ cube\  grouping sets 回顾

select deptno ,job ,avg(sal) from emp group by deptno, job; --统计出了各个部门的各个工种的平均工资

 

select deptno ,job ,avg(sal)

from emp group by rollup(deptno, job); --统计出了各个部门各工种的平均工资，各部门的平均工资，以及所有员工的平均工资

 

select deptno ,job ,avg(sal)

from emp group by cube(deptno, job);--统计出了各个部门各工种的平均工资，各部门的平均工资，各工种的平均工资，以及所有员工的平均工资

 

select deptno ,job ,avg(sal)

from emp group by grouping sets (deptno, job); --统计出了各个部门的平均工资，各个工种的平均工资

-------------------------------

---

--标量子查询

select (select dname from dept d where  e.deptno=d.deptno)deptname,ename,mgr,sal,job from emp e

---

 

----with重用查询块

with A as

(select deptno, dname from dept)

select (select dname from a where a.deptno=e.deptno )deptname,ename,mgr,sal,job from emp e

 

---当然，with里面可以定义多个子查询的，格式如下：

with test_with1 as(select * from A),

test_with2 as(select * from B)

select * from B where B.id in(select id from test_with1)

union all

select * from test_with2

-------

 

---分层查询回顾

 

select * from emp e

where level <=4

and empno<>7698

start with MGR is null

connect by prior empno=MGR

 

--使用函数填充

select lpad(empno,length(empno)+level*10-10,'-') ,

        ename,job,MGR,hiredate,sal,comm,deptno

from emp e

start with MGR is null

connect by prior empno=MGR