Oracle数据库开发_全13周-CSDN博客

--第一课
set serveroutput on

declare
msg varchar2(50);
begin
msg := 'hello,world';
dbms_output.put_line(msg);
end;
/

create or replace procedure p
as
msg varchar2(50);
begin
msg := 'hello,world';
dbms_output.put_line(msg);
end;
/

exec p;

--第二课
select job,count(distinct deptno) from emp where mgr is not null group by job order by count(distinct deptno) desc,job
select job,count(distinct deptno) from emp where mgr is not null group by job order by 2 desc,job
select job,count(distinct deptno) num from emp where mgr is not null group by job order by num desc,job

select deptno,to_char(hiredate,'yyyy'),count(*) from emp group by deptno,to_char(hiredate ,'yyyy')
select deptno,to_char(hiredate,'yyyy'),count(*) from emp group by deptno,2----------错
select deptno,to_char(hiredate,'yyyy') num,count(*) from emp group by deptno,num----错

order by 可以用别名
group by 不可以用别名

--第三课
Union ：并级、去重、排序
Union All ：不去重、不排序
Intersect ：交级
Minus ：差级

create table test (id int,name varchar2(20));

insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');
insert into test values(4,'d');
insert into test values(5,'e');
insert into test values(6,'f');
insert into test values(7,'g');

###################################
scott@OCMDB> select * from test;

        ID NAME
---------- ---------------------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f
         7 g

###################################
scott@OCMDB> select * from test
2 where id>2 and id <5
3 union
4 select * from test
5 where id<4
6 ;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c
         4 d

#####################################
scott@OCMDB> select * from test
2    where id>2 and id <5
3    union all
4    select * from test
5    where id<4 ;

        ID NAME
---------- ---------------------
         3 c
         4 d
         1 a
         2 b
         3 c

#########################
select * from test
where id>2 and id <5
Intersect
select * from test
where id<4 ;

        ID NAME
---------- ---------
         3 c

#########################
select * from test
where id>2 and id <5
minus
select * from test
where id<4 ;

ID NAME
---------- ---------
4 d

select * from test
where id<4
minus
select * from test
where id>2 and id <5 ;

        ID NAME
---------- --------
         1 a
         2 b
#############################

#连接

select a.empno,a.ename,a.deptno,b.dname
from emp a,dept b
where a.deptno=b.deptno

=======================

1) 左条件(+) = 右条件
左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!
也称为右外连接.

select a.empno,a.ename,a.deptno,b.dname
from emp a,dept b
where a.deptno(+)=b.deptno;

select a.empno,a.ename,a.deptno,b.dname
from emp a right join dept b
on a.deptno=b.deptno;
===============================

2) 左条件 = 右条件(+)
右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!
也称为左外连接.

select a.empno,a.ename,a.deptno,b.dname
from emp a,dept b
where a.deptno=b.deptno(+);

select a.empno,a.ename,a.deptno,b.dname
from emp a left join dept b
on a.deptno=b.deptno;

#############################
#with as

with
rs1 as (select * from test where id in(1,2)),
rs2 as (select * from test where id in(3,4))
select * from rs1
union
select * from rs2;

        ID NAME
---------- ------------
         1 a
         2 b
         3 c
         4 d

with
rs1 as (select * from test where id in(1,2)),
rs2 as (select * from test where id in(3,4))
select * from
(select * from rs1
union
select * from rs2)
where id =3;
######################################
#connect by

SELECT
RPAD( ' ', 2*(LEVEL-1), '-' ) || empno "empno",
ename "ename",
mgr "mgr",
CONNECT_BY_ROOT empno "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(empno, '/') "PATH",
SYS_CONNECT_BY_PATH(ename, '/') "PATH2"
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

empno           ename                  mgr       ROOT     ISLEAF      LEVEL PATH                                               PATH2
--------------- --------------- ---------- ---------- ---------- ---------- -------------------------------------------------- --------------------------------------------------
7839            KING                               7839          0          1 /7839                                              /KING
-7566          JONES                 7839       7839          0          2 /7839/7566                                         /KING/JONES
---7788        SCOTT                 7566       7839          0          3 /7839/7566/7788                                    /KING/JONES/SCOTT
-----7876      ADAMS                 7788       7839          1          4 /7839/7566/7788/7876                               /KING/JONES/SCOTT/ADAMS
---7902        FORD                  7566       7839          0          3 /7839/7566/7902                                    /KING/JONES/FORD
-----7369      SMITH                 7902       7839          1          4 /7839/7566/7902/7369                               /KING/JONES/FORD/SMITH
-7698          BLAKE                 7839       7839          0          2 /7839/7698                                         /KING/BLAKE
---7499        ALLEN                 7698       7839          1          3 /7839/7698/7499                                    /KING/BLAKE/ALLEN
---7521        WARD                  7698       7839          1          3 /7839/7698/7521                                    /KING/BLAKE/WARD
---7654        MARTIN                7698       7839          1          3 /7839/7698/7654                                    /KING/BLAKE/MARTIN
---7844        TURNER                7698       7839          1          3 /7839/7698/7844                                    /KING/BLAKE/TURNER
---7900        JAMES                 7698       7839          1          3 /7839/7698/7900                                    /KING/BLAKE/JAMES
-7782          CLARK                 7839       7839          0          2 /7839/7782                                         /KING/CLARK
---7934        MILLER                7782       7839          1          3 /7839/7782/7934                                    /KING/CLARK/MILLER

执行顺序：

1、语句中有多表关联，先执行关联，无论是join还是where条件中的关联条件。
2、执行start with的条件，选出第一个节点。
3、执行connect by 的条件，层级关联，选出子节点。
4、执行where中的过滤条件，排除结果集中不满足条件的记录，但是不会因为排除一条记录而把它对应的子节点排除。
5、执行order siblings by的排序条件，对同级节点排序。

level：标记层级级数，最上层节点为1，之后为2、3……。
CONNECT_BY_ISCYCLE：标记此节点是否为某一个祖先节点的父节点，导致循环，1为是，0为否。
CONNECT_BY_ISLEAF ：标记此节点是否为叶子节点，即没有子节点，1为是，0为否。
CONNECT_BY_ROOT：标记此节点的祖先节点，后面加列名或表达式，取祖先节点的记录值。
SYS_CONNECT_BY_PATH（column,char）函数：记录根节点到此节点的路径，column是每个节点的路径值，以char分割。column和char都必须是char,varchar2,nchar,或者nvarchar2。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22193071/viewspace-1189876/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1189876/