-------------------------------oracle基础------------------------------------------
1.oracle中声明变量时,变量类型是在变量名的后面。如:v_count number(7,1)

2.oracle中赋值用:=,等于用=.不同于java中等于用:“==”,赋值用"=".
v_msg varchar2(32) := 'testing'

3.字符常量用单引号,而不是双引号。若要输出带字符可以如:'in='||v_count.
而不能被java中,用"in="+v_count.

4.在存储过程或函数中,声明变量名的规则是:
第一个字符必须是字母,不能是数字如:200_count;
变量不分大小写;
不能含有"-","/","$"等符号,但可以用"_".
 
5.可以用"<>"或"!="来表示不等于。
单行注释,用--,多行注释,用/**/

6.条件语句if,case:
if,case以及以前的循环标签,结果时,都须用end[关键字]来表示结果。

6.1
if      条件 then ...
else if 条件 then ...
else              ...
end if
如:
if      salse>5000 then bonus :=500;
else if salse>3000 then bonus :=300;(即当销售额大于3000小于等于5000时)
else    bonus :=100
end if
 
6.2
case格式用二种,一种是有选择器,另一种则没有。如下:
case grade
when 'A' then status :='good';
when 'B' then status :='normal'
else status :='bad'
end case

case
when grade='A' then status :='good';
when grade='B' then status :='normal'
else status :='bad'
end case
 
7.循环语句,while<条件> loop...end loop / for...loop ...end loop.
实例如下:
create or replace procedure a(max in number,value out number)
as
index number :=0;
begin
while index<=max
loop
value :=value*value;
end loop;
end
若用for,可以是:
for <循环计数变量> in [reverse] <上限> .. <下限>
for m in 1 .. 10
loop
.........
end loop
将执行从1到10的循环;
若用
for m in reverse 1 ..10
loop ...end loop
则实现从10到1的逆向循环。
 
oracle左/右/全连接
inner join  --内连接和where相同;相当于join;
left join   --左向外连接,返回左边表所有符合条件的 
right join  --右向外连接,返回右边表所有符合条件的 
full join   --完整外部连接,左向外连接和右向外连接的合集 
--建立测试数据 
create table a(id number); 
create table b(id number); 
insert into a values(1); 
insert into a values(2); 
insert into a values(3); 
insert into b values(1); 
insert into b values(2); 
insert into b values(4); 
--左: 
--主流数据库通用的方法 
select * from a left join b on a.id=b.id; 
--Oracle特有的方法  --最好不要用这样,不便于sql语句的移植。
select * from a, b where a.id=b.id(+); 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
         3  
 
--右: 
--主流数据库通用的方法 
select * from a right join b on a.id=b.id; 
--Oracle特有的方法 
select * from a, b where a.id(+)=b.id; 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
                    4  
--内 
--主流数据库通用的方法 
select * from a join b on a.id=b.id; 
--where关联 
select * from a, b where a.id=b.id; 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2          
--全外 
--主流数据库通用的方法 
select * from a full join b on a.id=b.id; 
--Oracle特有的方法 
select * 
  from a, b 
where a.id = b.id(+) 
union 
select *  
  from a, b  
where a.id(+) = b.id; 
 
        ID         ID 
---------- ---------- 
         1          1 
         2          2 
         3  
                    4 
 
对于多表查询,如下:
select * from a
left join b on a.id=b.id  
left join c on c.id=b.id;
        ID         ID         ID 
---------- ---------- ---------- 
         1          1          1 
         2          2 
         3  
自己理解:
相当于先进行第一个left join,得到结果集A(3条记录);结果集A再与第二个left join进行查询;
select * from a
left join b on a.id=b.id 
right join c on c.id=b.id;
相当于先进行第一个left join,得到结果集A(3条记录);结果集A再与right join进行查询,即以c表为主,因为C表只有一个记录,所以最终才显示一条记录;
-----------------------------------------------------------------------------------------
1.
NVL(ac.ovrided_ac_nam, en.stmt_nam) accountDisplayName,
NVL(EXPR1,EXPR2)
若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1; --注意不能是双引号。
2.
DUAL叫虚表,又叫哑表。这表示其中的内容并不是在数据库实际存在的,而是随表达式的不同而不是同的。
select * from dual,返回什么?
D
-
X
这表示其中没有任何内容,也不要对DUAL这个虚表进行增删改,这样做没有实际意义,更不能DROP。强调,它是一个系统表。
使用DUAL比较常见的情况是查询当前的系统时间,如:
SQL> select to_char(sysdate,'yyyy.mm.dd') as x from dual;
当我们计算表达式时,可用
  SELECT sqrt(81) FROM dual;
  SELECT (5+6)*7 FROM dual;
3.
to_char,to_date
日期到字符操作
select to_char(sysdate,'yyyy.mm.dd') as x from dual;
字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
to_char(1210.73, '9999.9') would return '1210.7'
 
to_char(1210.73, '9,999.99') would return '1,210.73'
 
to_char(1210.73, '$9,999.00') would return '$1,210.73'
 
to_char(21, '000099') would return '000021'
 
4.
select a,
       CASE WHEN t.b = 'yes' THEN
          t.c ELSE 0 END yescolumn,
       CASE WHEN t.b = 'no' THEN
          t.c ELSE 0 END nocolumn,
在select语句中,也可应用case.在select的字段中,会同时出现yes,no两个字段。
5.
不等于:WHERE ROWID <> (SELECT MAX(ROWID)
6.
字段合并:

select ac_id || unit_cde from cc_ac t --make two column into one
相当于:
select concat(ac_id , unit_cde) from cc_ac t --make two column into one
----------------------------------sqlplus命令(系统命令)------------------------------
sql语句主要是对数据库的定义与操作,而对像环境设置,数据库会话等SQL语句是不能完成的。
Oracle公司提供的附加语句(或称命令),可以满足程序人员和管理员的一些特殊操作要求。比如,在显示超过上百行记录信息时,可以采用每屏“暂停”来实现。要达到这样的目的,就要在SQL>下发 set pause on 命令。由于SQL*PLUS命令较多,下面仅给出最常用的几个命令的说明:
1.
logon可以是:
{username[/password][@connect_identifier]|/} [AS {SYSOPER|SYSDBA}]
切换用户:
connect user/password
退出:disconnect
2.
EXIT和QUIT
可以用 exit 或quit 来终止SQL*PLUS的操作(会话)。
3.
DESCRIBE(显示表、视图结构)
DESCRIBE可以用(只要用DESC即可)来显示表、视图的列的定义,也可以显示同义词、函数或存储过程的说明。语法如下:DESC[RIBE] {[schema.]object[@connect_identifier]}
Schema:用户名,如果省去,则为对象的所有者。
object
可以是 表(table), 视图(view),类型( type), 存储过程(procedure),函数( function), 包(package)或同义词( synonym)
4.
LIST(列出)命令
可以用 LIST 命令来列出当前SQL缓冲区中的一行或多行命令语句。
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
n 列出第n行
n m 列出n到m行
n * 列出第n行到当前行
n LAST 列出第n行到最末行
* 列出所有行
* n 列出当前行到第n行
* LAST列出当前行到最末行
LAST 列出最末行
例:
SQL> LIST
1 SELECT ENAME, DEPTNO, JOB
2 FROM EMP
3 WHERE JOB = ’CLERK’
4* ORDER BY DEPTNO
5.
Save保存当前缓冲区命令到文件
可以用SAVE命令将当前的命令行保存到操作系统的文件中。
例:
SQL>select table_name from dict where table_name like ‘%ROLE%’;
SQL>save c:\get_role
6.
GET将命令文件读到缓冲区
可以用GET 命令将操作系统的目录下的命令文件读到缓冲区(但不执行)。语法如下:
GET filename [.ext] [LIS[T]|NOL[IST]]
其中:
filename: 希望加载到SQL缓冲区的文件名
ext: 文件的扩展名,缺省为 SQL.
7.
再运行当前缓冲区的命令
在SQL>方式下,如果希望在运行当前的命令,可用Run(或R)或用 / 来实现,如:
1* select table_name from dict where table_name like '%ROLE%'
SQL> /
8.
设置显示行字符数:set linesize 180(默认80个字符)
设置页面显示行数:set pagesize 66
9.
事务自动提交:
show auto,会显示:autocommit on/off
set auto on/off.
设置暂停:
set pause on
---------------------------------------数据类型---------------------------------
Char 定长字符,≤255个字符
Varchar2 变长字符,≤2000个字符
Number(m,n) 数字类型,含整数、浮点、双精度等
Date 固定长度(7字节)的日期型
Blob 大二进制对象,≤4GB
Clob 大字符串对象,≤4GB
对于数字类型,也可以用:
Decimal
Double PREcision
Float
Integer
Int
Numeric
但,oracle在保存时,会将上面的类型自动转换为number.
------------------group by 与 分类统计-----------------
1.报表合计专用的Rollup函数
销售报表
广州 1月 2000元
广州 2月 2500元
广州 4500元
深圳 1月 1000元
深圳 2月 2000元
深圳 3000元
所有地区 7500元
以往的查询SQL:
Select area,month,sum(money) from SaleOrder group by area,month
然后广州,深圳的合计和所有地区合计都需要在程序里自行累计

1.其实可以使用如下SQL:
Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)
就能产生和报表一模一样的纪录
2.如果year不想累加,可以写成
Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)
另外Oracle 9i还支持如下语法:
Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)
 
3.Grouping让合计列更好读
RollUp在显示广州合计时,月份列为NULL,但更好的做法应该是显示为"所有月份"
Grouping就是用来判断当前Column是否是一个合计列,1为yes,然后用Decode把它转为"所有月份"
Select Decode(Grouping(area),1,'所有地区',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);
说明:
decode格式:
decode(字段或字段的运算,值1,值2,值3)
这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3。
当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多。
Decode(Grouping(area),1,'所有地区',area),判断Grouping(area)是否为1,若是,则采用“所有地区”,否则采用area.
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
select count(*) from 表 where 性别 = 男;
select count(*) from 表 where 性别 = 女;
要想显示到一起还要 union一下,太麻烦了
用decode呢,只需要一句话
select decode(性别,男,1,0),decode(性别,女,1,0) from 表
 
--------------------------------多级层次查询---------------------------------
Oracle中Start with...Connect By理解及用法
其基本语法是:
select ... from tablename start with cond1
connect by cond2
where cond3;
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段: id,parentid,那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。
 
其中COND1是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
COND2是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID,即本记录的父亲是上一条记录。
COND3是过滤条件,用于对返回的所有记录进行过滤。
 
例子:
创建示例表:
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归(取所有点)
select * from TBL_TEST
start with id=1
connect by prior id = pid
从末梢往树ROOT递归(只取根结点,根结点的根结点......)
select * from TBL_TEST
start with id=5
connect by prior pid = id
------------------------------------------------
3.更多报表/分析决策功能
3.1 分析功能的基本结构
分析功能() over( partion子句,order by子句,窗口子句)
概念上很难讲清楚,还是用例子说话比较好.
 
3.2 Row_Number 和 Rank, DENSE_Rank
用于选出Top 3 sales这样的报表
当两个业务员可能有相同业绩时,就要使用Rank和Dense_Rank
比如
金额 RowNum Rank Dense_Rank
张三 4000元 1 1 1
李四 3000元 2 2 2
钱五 2000元 3 3 3
孙六 2000元 4 3 3
丁七 1000元 5 5 4
这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。
SELECT salesperson_id, SUM(tot_sales) sp_sales, RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank FROM orders GROUP BY salesperson_id
3.3 NTILE 把纪录平分成甲乙丙丁四等
比如我想取得前25%的纪录,或者把25%的纪录当作同一个level平等对待,把另25%当作另一个Level平等对待
SELECT cust_nbr, SUM(tot_sales) cust_sales, NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile FROM orders GROUP BY cust_nbr ORDER BY 3,2 DESC;
Ntitle(4)把纪录以 SUM(tot_sales)排序分成4份.
 
(1)
between,add_months用法
如:
intCalc.Calc_Date between  TO_DATE( '20100330', 'DDMMYYYY') and ADD_MONTHS(TO_DATE('20100330','YYYYMMDD'),1) - 1
分析:
between .. and ..:
相当于大于A,小于B。 在其中不能加括号,如:between(.. and ..)是错的。
add_months("时间",正负数):
正数,表示之前的几个月,负数,表示之前几个月。
add_months("时间",正负数) +/- A:表示之后之前的几天。
ADD_MONTHS(TO_DATE('20100330','YYYYMMDD'),1) - 1:表示之后的一个月的前一天。
(2)
可以点击pl/sql的美化功能。F8运行功能。
(3)
TRUNC(i.expr_date, 'YYYY-MM'):
TRUNC(date[,fmt])
   其中:
   date 一个日期值
   fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
2.TRUNC(for number)
   TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相
应舍入选择处理
,而统统截去。
   其具体的语法格式如下
   TRUNC(number[,decimals])
   其中:
   number 待做截取处理的数值
   decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
   下面是该函数的使用情况:
   TRUNC(89.985,2)=89.98
   TRUNC(89.985)=89
   TRUNC(89.985,-1)=80
   注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
(3)
oracle获取子字符串函数:
substr(‘123456789’,1,2) -从1到2
substr(‘123456789’2)     -从2到完
(4)
oracle:
在oracle中,若group aCurrency,bCurrency,cCurrency,则表示当:
aCurrency=HKD,bCurrency=HKD,cCurrency=HKD;
aCurrency=HKD,bCurrency=HKD,cCurrency=USD;
aCurrency=USD,bCurrency=HKD,cCurrency=HKD;
.....
每种排列组合时,进行分组.统计。