高分（100） oracle资料收集（越好分越多）

Question

高分（200） oracle资料收集（越好分越多）基础————〉具体例子实现都可以 
我新手

Answer 1

SQL 訪問 Oracle
安装Oracle客户端程序并配置TNSNAME.ORA。   
    
  然后创建Oracle链接服务器：   
    
  1、创建链接服务器以访问   Oracle   数据库实例     
  确保运行   SQL   Server   的服务器上的   Oracle   客户端软件已达到提供程序所要求的级别。用于   Oracle   的   Microsoft   OLE   DB   提供程序要求   Oracle   客户端软件支持文件的版本为   7.3.3.4.0   或更高版本，并且   SQL*Net   的版本为   2.3.3.0.4。   
    
    
  2、在运行   SQL   Server   的服务器上创建指向   Oracle   数据库实例的   SQL*Net   别名。有关更多信息，请参见   Oracle   文档。   
    
    
  3、执行   sp_addlinkedserver   创建链接服务器，指定   MSDAORA   为   provider_name，指定用于   Oracle   数据库实例的   SQL*Net   别名为   data_   source。     
    
  以下示例假设已将一个   SQL*Net   别名定义为   OracleDB。   
    
  sp_addlinkedserver   'OrclDB',   'Oracle',   'MSDAORA',   'OracleDB'   
    
    
  4、使用   sp_addlinkedsrvlogin   创建从   SQL   Server   登录到   Oracle   登录的登录映射。     
  以下示例通过   Oracle   登录名   OrclUsr   和密码   OrclPwd   将   SQL   Server   登录   Joe   映射到步骤   3   中定义的链接服务器：   
    
  sp_addlinkedsrvlogin   'OrclDB',   false,   'Joe',   'OrclUsr',   'OrclPwd'

Answer 2

oracle中方案的创建 
第一步：创建用户 
            create   user   wsl   
            identified   by   kmust.net 
            default   tablespace   wsl 
            temporary   tablespace   temp; 
第二步：赋予用户相应的权限 
　　grant   connect,resource   to   aa; 
第三步：以该用户登录 
　　connect   wsl/kmust.net@oracle 
第四步：创建schema 
　　create   schema   authorization   wsl--创建名为wsl的方案 
　　create   table   tab1(a   number)   
　　create   table   tab2(b   number,c   varchar2(10));

Answer 3

1.显示当前时间select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_time from dual;/*
CURRENT_TIME       
-------------------
2007-09-29 09:29:03
1 row selected
*/select current_date current_time from dual;/*
CURRENT_TIME   
----------------
2007-9-29 10:34:
1 row selected
*/2.只显示日期，不显示时间select to_char(sysdate,'yyyy-mm-dd') current_date from dual;/*
CURRENT_DATE
------------
2007-09-29  
1 row selected
*/

Answer 4

Oracle计算时间差表达式 --获取两时间的相差豪秒数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL;
/*
相差豪秒数
----------
  86401000
1 row selected
*/--获取两时间的相差秒数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;
/*
相差秒数
----------
     86401
1 row selected
*/--获取两时间的相差分钟数
select ceil(((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) * 24 * 60)  相差分钟数 FROM DUAL;
/*
相差分钟数
----------
      1441
1 row selected
*/--获取两时间的相差小时数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24)  相差小时数 FROM DUAL;
/*
相差小时数
----------
        25
1 row selected
*/--获取两时间的相差天数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')))  相差天数 FROM DUAL;
/*
相差天数
----------
         2
1 row selected
*/--获取两时间月份差
select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 + 
       EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) months
from dual;
/*
MONTHS
----------
        13
1 row selected
*/--获取两时间年份差
select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;
/*
YEARS
----------
         1
1 row selected
*/

Answer 5

/*
标题：合并相同列的数据
作者：爱新觉罗.毓华 
时间：2008-05-05
地点：广东深圳
*//*情况说明
NAME USERID
张三 KB001
张三 KB003
李四 KB001
李四 KB002
李四 KB003用SQL实现表示如下:
NAME USERID
张三 KB001,KB003
李四 KB001,KB002,KB003
*/create table tb(NAME varchar2(10) , USERID varchar2(10))
insert into tb values('张三' , 'KB001');
insert into tb values('张三' , 'KB003');
insert into tb values('李四' , 'KB001');
insert into tb values('李四' , 'KB002');
insert into tb values('李四' , 'KB003');--1
select name,rtrim(
  max(decode(USERID , 'KB001' , USERID || ',' , '')) ||
  max(decode(USERID , 'KB002' , USERID || ',' , '')) ||
  max(decode(USERID , 'KB003' , USERID || ',' , '')),',') userid
from tb
group by name--2
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') userid
FROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;drop table tb/*
NAME       USERID                           
---------- ---------------------------------
李四       KB001,KB002,KB003                
张三       KB001,KB003                      
2 rows selected
*/

Answer 6

两表数据关联的UPDATE表tb1及数据如下：
        ID          A          B
---------- ---------- ----------
         1         10         20
         2          1          2
         3          3          2表tb2及数据如下：
        ID          A          B
---------- ---------- ----------
         1         10         20
         2         10         20根据表tb2的id更新表tb1对应的a,b的值。update tb1 set (a,b) = (select a,b from tb2 where id = tb1.id) where id in (select distinct id from tb2)        ID          A          B
---------- ---------- ----------
         1         10         20
         2         10         20
         3          3          2
---------------------------------------------------------------------------------------------------------
表tb2及数据如下：
        ID          A          B
---------- ---------- ----------
         1         10         20
         2         10         20
         3          3          2
         1         20         40
         2         20         40根据ID分组求A，B的和，然后更新表tb1update tb1 set (a,b) = (select sum(a),sum(b) from tb2 where id = tb1.id) where id in (select distinct id from tb2)        ID          A          B
---------- ---------- ----------
         1         30         60
         2         30         60

Answer 7

/*
标题：静态行列转换
作者：爱新觉罗.毓华 
时间：2008-05-05
地点：广东深圳
*//*   
col1         col2         col3 
A               x                   1 
A               y                   2 
A               z                   3 
B               x                   2 
B               y                   1 
B               z                   1 转换为col1          x         y       z 
A             1         2       3 
B             2         1       1 
*/create table tb(col1 varchar2(10) , col2 varchar2(10) , col3 varchar2(10))
insert into tb values('A' , 'x' , '1') 
insert into tb values('A' , 'y' , '2') 
insert into tb values('A' , 'z' , '3') 
insert into tb values('B' , 'x' , '2') 
insert into tb values('B' , 'y' , '1') 
insert into tb values('B' , 'z' , '1')--1
select col1,
  max(case col2 when 'x' then col3 else '' end) x,
  max(case col2 when 'y' then col3 else '' end) y,
  max(case col2 when 'z' then col3 else '' end) z
from tb
group by col1--2
select col1 ,
  max(decode(col2 , 'x' , col3)) x,
  max(decode(col2 , 'y' , col3)) y,
  max(decode(col2 , 'z' , col3)) z
from tb
group by col1
  
drop table tb/*
COL1       X          Y          Z         
---------- ---------- ---------- ----------
A          1          2          3         
B          2          1          1         
2 rows selected
*/ 

Answer 8

oracle分析函数
oracle分析函数--SQL*PLUS环境
--1、GROUP BY子句 
create table students(id number(15,0),area varchar2(10),stu_type varchar2(2),score number(20,2));
insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'j', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;
--A、GROUPING SETS
select id,nvl(area,'小计') area,nvl(stu_type,'小计') stu_type,sum(score) score 
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;
/*
              ID AREA       STU_TYPE      SCORE
---------------- ---------- -------- ----------
               1 111        g                80
               1 111        j                80
               1 111        小计            160
28 rows selected
*/
/*理解grouping sets
select a, b, c, sum( d ) from t group by grouping sets ( a, b, c )
等效于
select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b 
union all
select null, null, c, sum( d ) from t group by c 
)
*/
--B、ROLLUP
select id,area,stu_type,sum(score) score 
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;
/*
              ID AREA       STU_TYPE      SCORE
---------------- ---------- -------- ----------
               1 111        g                80
               1 111        j                80
...
29 rows selected
*/
/*--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);等效于select * from (
select a, b, c, sum( d ) from t group by a, b, c 
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
*/--C、CUBEselect id,area,stu_type,sum(score) score 
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)等效于select a, b, c, sum( d ) from t
group by grouping sets( 
( a, b, c ), 
( a, b ), ( a ), ( b, c ), 
( b ), ( a, c ), ( c ), 
() )
*/--D、GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/select decode(grouping(id),1,'all_id',id) id,
       decode(grouping(area),1,'all_area',to_char(area)) area,
       decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
       sum(score) score
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type; 
/*
ID                                       AREA       STU_TYPE          SCORE
---------------------------------------- ---------- ------------ ----------
1                                        111        all_stu_type        160
1                                        111        g                    80
1                                        111        j                    80
1                                        222        all_stu_type        157
1                                        222        g                    89
1                                        222        j                    68
1                                        all_area   all_stu_type        317
2                                        111        all_stu_type        150
2                                        111        g                    80
2                                        111        j                    70
2                                        222        all_stu_type        125
2                                        222        g                    60
2                                        222        j                    65
2                                        all_area   all_stu_type        275
3                                        111        all_stu_type        133
3                                        111        g                    75
3                                        111        j                    58
3                                        222        all_stu_type        148
3                                        222        g                    58
3                                        222        j                    90
3                                        all_area   all_stu_type        281
4                                        111        all_stu_type        179
4                                        111        g                    89
4                                        111        j                    90
4                                        222        all_stu_type        179
4                                        222        g                    90
4                                        222        j                    89
4                                        all_area   all_stu_type        358
all_id                                   all_area   all_stu_type       1231
29 rows selected
*/--2、OVER()函数的使用
--1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()break on id skip 1
select id,area,score from students order by id,area,score desc;--允许并列名次、名次间断
select id,rank() over(partition by id order by score desc) rk,score from students;--允许并列名次、名次不间断
select id,dense_rank() over(partition by id order by score desc) rk,score from students;--即使SCORE相同，ROW_NUMBER()结果也是不同
select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_number 
row_number() over (order by id) rn,id,area,score from students;select id,max(score) over(partition by id order by score desc) as mx,score from students;select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别
--按照ID求AVG
select id,avg(score) over(partition by id order by score desc rows between unbounded preceding 
and unbounded following ) as ag,score from students;
--2、SUM()
select id,area,score from students order by id,area,score desc;
select id,area,score,
sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;
select id,area,score,
sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和
sum(score) over (partition by id) id总和, --各id的分数总和
100*round(score/sum(score) over (partition by id),4) "id份额(%)",
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;
--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据
select id,lag(score,1,0) over(order by id) lg,score from students;
select id,lead(score,1,0) over(order by id) lg,score from students;
--5、FIRST_VALUE()、LAST_VALUE()
select id,first_value(score) over(order by id) fv,score from students;
select id,last_value(score) over(order by id) fv,score from students;