oracle教程:PLSQL常用方法匯總

標籤：

oracle教程:PLSQL常用方法匯總

 

在SQLPLUS下，實現中-英字元集轉換
alter session set nls_language=‘AMERICAN‘;
alter session set nls_language=‘SIMPLIFIED CHINESE‘; 
主要知識點：
一、有關表的操作
1)建表

create table test as select * from dept; --從已知表複製資料和結構
create table test as select * from dept where 1=2; --從已知表複製結構但不包括資料
2)插入資料：
insert into test select * from dept;

 

 

二、運算子
算術運算子：+ - * / 可以在select 語句中使用
串連運算子：|| select deptno|| dname from dept; 
比較子：> >= = != < <= like between is null in
邏輯運算子：not and or 
集合運算子： intersect ，union， union all， minus 
要求：對應集合的列數和資料類型相同
查詢中不能包含long 列
列的標籤是第一個集合的標籤
使用order by時，必須使用位置序號，不能使用列名
例：集合運算子的使用：
intersect ，union， union all， minus 
select * from emp intersect select * from emp where deptno=10 ;
select * from emp minus select * from emp where deptno=10;
select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重複行 
select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重複行

三，常用 ORACLE 函數
sysdate為系統日期 dual為虛表
一）日期函數[重點掌握前四個日期函數]
1,add_months[返回日期加(減)指定月份後(前)的日期]
select sysdate S1,add_months(sysdate,10) S2,
add_months(sysdate,5) S3 from dual;
2,last_day [返回該月最後一天的日期] 
select last_day(sysdate) from dual; 
3,months_between[返回日期之間的月份數]
select sysdate S1, months_between(‘1-4月-04‘,sysdate) S2,
months_between(‘1-4月-04‘,‘1-2月-04‘) S3 from dual
4,next_day(d,day): 返回下個星期的日期,day為1-7或星期日-星期六,1表示星期日 
select sysdate S1,next_day(sysdate,1) S2,
next_day(sysdate,‘星期日‘) S3 FROM DUAL 
5,round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,‘year‘) YEAR,
round(sysdate,‘month‘) MONTH ,
round(sysdate,‘day‘) DAY from dual
6,trunc[截斷到最接近的日期] 
select sysdate S1,
trunc(sysdate) S2,
trunc(sysdate,‘year‘) YEAR,
trunc(sysdate,‘month‘) MONTH ,
trunc(sysdate,‘day‘) DAY from dual
7,返回日期列表中最晚日期
select greatest(‘01-1月-04‘,‘04-1月-04‘,‘10-2月-04‘) from dual

二）字元函數(可用於字面字元或資料庫列)
1，字串截取
select substr(‘abcdef‘,1,3) from dual
2,尋找子串位置
select instr(‘abcfdgfdhd‘,‘fd‘) from dual
3,字串串連
select ‘HELLO‘||‘hello world‘ from dual;
4, 1)去掉字串中的空格
select ltrim(‘ abc‘) s1,
rtrim(‘zhang ‘) s2,
trim(‘ zhang ‘) s3 from dual
2)去掉前置和尾碼
select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 9998767999) s3 from dual;
5,返回字串首字母的Ascii值
select ascii(‘a‘) from dual
6,返回ascii值對應的字母
select chr(97) from dual
7,計算字串長度 
select length(‘abcdef‘) from dual
8,initcap（首字母變大寫） ,lower(變小寫),upper(變大寫)
select lower(‘ABC‘) s1,
upper(‘def‘) s2, 
initcap(‘efg‘) s3 from dual; 
9,Replace
select replace(‘abc‘,‘b‘,‘xy‘) from dual;
10,translate
select translate(‘abc‘,‘b‘,‘xx‘) from dual; -- x是1位
11,lpad [左添充] rpad [右填充](用於控制輸出格式)
select lpad(‘func‘,15,‘=‘) s1, rpad(‘func‘,15,‘-‘) s2 from dual;
select lpad(dname,14,‘=‘) from dept;
12, decode[實現if ..then 邏輯]
select deptno,decode(deptno,10,‘1‘,20,‘2‘,30,‘3‘,‘其他‘) from dept;
三）數字函數
1，取整函數（ceil 向上取整,floor 向下取整）
select ceil(66.6) N1,floor(66.6) N2 from dual;
2, 取冪(power) 和 求平方根(sqrt)
select power(3,2) N1,sqrt(9) N2 from dual;
3,求餘
select mod(9,5) from dual;
4,返回固定小數位元 (round:四捨五入，trunc:直接截斷)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual; 
5,傳回值的符號(正數返回為1,負數為-1)
select sign(-32),sign(293) from dual;
四）轉換函式
1，to_char()[將日期和數字類型轉換成字元類型]
1) select to_char(sysdate) s1,
to_char(sysdate,‘yyyy-mm-dd‘) s2,
to_char(sysdate,‘yyyy‘) s3,
to_char(sysdate,‘yyyy-mm-dd hh12:mi:ss‘) s4,

to_char(sysdate, ‘hh24:mi:ss‘) s5,
to_char(sysdate,‘DAY‘) s6 from dual;
2) select sal,to_char(sal,‘＄99999‘) n1,to_char(sal,‘＄99,999‘) n2 from emp
2, to_date()[將字元類型轉換為日期類型] 
insert into emp(empno,hiredate) values(8000,to_date(‘2004-10-10‘,‘yyyy-mm-dd‘));
3, to_number() 轉換為數字類型 
select to_number(to_char(sysdate,‘hh12‘)) from dual; //以數字顯示的小時數
五）其他函數
user: 
返回登入的使用者名稱稱 
select user from dual; 
vsize: 
返回運算式所需的位元組數
select vsize(‘HELLO‘) from dual;
nvl(ex1,ex2): 　
ex1值為空白則返回ex2,否則返回該值本身ex1（常用） 
例：如果僱員沒有傭金，將顯示0，否則顯示傭金 
select comm,nvl(comm,0) from emp;
nullif(ex1,ex2): 
值相等返空，否則返回第一個值
例：如果工資和傭金相等，則顯示空，否則顯示工資
select nullif(sal,comm),sal,comm from emp;
coalesce: 　
返回列表中第一個非空運算式
select comm,sal,coalesce(comm,sal,sal*10) from emp;
　　　nvl2(ex1,ex2,ex3) :
如果ex1不為空白，顯示ex2,否則顯示ex3
如：查看有傭金的僱員姓名以及他們的傭金 
　　 　 select nvl2(comm,ename,‘) as HaveCommName,comm from emp;
六）分組函數
max min avg count sum
1，整個結果集是一個組
1) 求部門30 的最高工資，最低工資,平均工資，總人數，有工作的人數，工種數量及工資總和
select max(ename),max(sal), 
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp where deptno=30;
2, 帶group by 和 having 的分組
1)按部門分組求最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和
select deptno, max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp group by deptno;
2)部門30的最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和 
select deptno, max(ename),max(sal),
min(ename),min(sal),
avg(sal),
count(*) ,count(job),count(distinct(job)) ,
sum(sal) from emp group by deptno having deptno=30;
3, stddev 返回一組值的標準差
select deptno,stddev(sal) from emp group by deptno;
variance 返回一組值的方差差
select deptno,variance(sal) from emp group by deptno;
4, 帶有rollup和cube操作符的Group By
rollup 按分組的第一個列進行統計和最後的小計
cube 按分組的所有列的進行統計和最後的小計
select deptno,job ,sum(sal) from emp group by deptno,job;
select deptno,job ,sum(sal) from emp group by rollup(deptno,job); 
cube 產生組內所有列的統計和最後的小計
select deptno,job ,sum(sal) from emp group by cube(deptno,job);

七、暫存資料表
只在會話期間或在交易處理期間存在的表.
暫存資料表在插入資料時，動態分配空間 
create global temporary table temp_dept
(dno number,
dname varchar2(10))
on commit delete rows;
insert into temp_dept values(10,‘ABC‘);
commit;
select * from temp_dept; --無資料顯示,資料自動清除
on commit preserve rows:在會話期間表一直可以存在（保留資料）
on commit delete rows:事務結束清除資料（在事務結束時自動刪除表的資料）

 

 

 

 

 

 

 

 

 

 

========================== 串連操作 ================================ 

例如有A,B兩張表： 

A表            B表 
a  b         c  d 
1  0         4  7 
2  9         1  5 


select * from A,B where A.a = B.c 
等同於 
select * from A join B on A.a = B.c 
結果： 
a b c d 
1 0 1 5 

左串連： 
select * from A,B where A.a = B.c(+) 
等同於 
select * from A left join B on A.a = B.c 
即以A表查詢為主，附帶查詢出滿足A.a = B.c條件的B表中的結果 
結果： 
a b c d 
1 0 1 5 
2 9 


右串連： 
select * from A,B where A.a(+) = B.c 
等同於 
select * from A right join B on A.a = B.c 
此查詢為左串連，即以A表查詢為主，附帶查詢出滿足A.a = B.c條件的B表中的結果 
結果： 
c d a b 
1 5 1 0 
4 7 

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


===================== oracle 函數 =============================== 
DECODE: 
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值) 

A表 
a  b  c 
1  3  0 
3  2  9 
1  0  5 
3  7  7 
5  5  9 

select decode(A.a,1,4,3,8),A.b,A.c form A 
意思:如果A.a的值為1，則將A.a的值查詢為4，如果A.a的值為3，則將A.a的值查詢為8 
結果： 
a b c 
4 3 0 
8 2 9 
4 0 5 
8 7 7 
5 5 9 

select decode(A.a,1,4,8),A.b,A.c form A 
意思:如果A.a的值為1，則將A.a的值查詢為4，否則A.a的值查詢為8 
結果： 
a  b  c 
4  3  0 
8  2  9 
4  0  5 
8  7  7 
8  5  9 

select decode(A.a,1,4,3,8,7),A.b,A.c form A 
意思:如果A.a的值為1，則將A.a的值查詢為4，如果A.a的值為3，則將A.a的值查詢為8，否則查詢為7 
結果： 
a b c 
4 3 0 
8 2 9 
4 0 5 
8 7 7 
7 5 9 
=============================================================== 


==========================SEQUENCE===================================== 

SEQUENCE： CREATE   SEQUENCE   LOCATIONS_SEQ  
          INCREMENT   BY   1  
          START   WITH   1  
          MAXVALUE   9900  
          MINVALUE   1  

SELECT LOCATIONS_SEQ.currval FROM DUAL ; 
SELECT LOCATIONS_SEQ.nextval FROM DUAL ; 

============================SEQUENCE=================================== 



============================函數=================================== 
日期函數： 
add_months(d,n)  日期d加n個月 
last_day(d)  包含d的月份的最後一天的日期 
month_between(d,e) 日期d與e之間的月份數，e先於d 
new_time(d,a,b)  a時區的日期和時間d在b時區的日期和時間 
next_day(d,day)  比日期d晚，由day指定的周幾的日期 
sysdate  當前的系統日期和時間 
greatest(d1,d2,...dn) 給出的日期列表中最後的日期 
least(d1,k2,...dn) 給出的日期列表中最早的日期 
to_char(d [,fmt]) 日期d按fmt指定的格式轉變成字串 
to_date(st [,fmt]) 字串st按fmt指定的格式轉成日期值，若fmt忽略，st要用預設格式 
round(d [,fmt])  日期d按fmt指定格式舍入到最近的日期 
trunc(d [,fmt])  日期d按fmt指定格式截斷到最近的日期 

分組函數： 
avg([distinct/all] n) 列n的平均值 
count([all] *)  返回查詢範圍內的行數包括重複值和空值 
count([distinct/all] n) 非空值的行數 
max([distinct/all] n) 該列或運算式的最大值 
min([distinct/all] n) 該列或運算式的最小值 
stdev([distinct/all] n) 該列或運算式的標準差，忽略空值 
sum([distinct/all] n) 該列或運算式的總和 
variance([distinct/all] n) 該列或運算式的方差，忽略空值 

數值函數： 
abs(m)  m的絕對值 
mod(m,n) m被n除後的餘數 
power(m,n) m的n次方 
round(m[,n]) m四捨五入至小數點後n位的值（n預設為0） 
trunc(m[,n]) m截斷n位小數位的值（n預設為0） 

字元函數： 
initcap(st) 返回st將每個單詞的首字母大寫，所有其他字母小寫 
lower(st) 返回st將每個單詞的字母全部小寫 
upper(st) 返回st將每個單詞的字母全部大寫 
concat(st1,st2)  返回st為st2接st1的末尾（可用操作符"||"） 
lpad(st1,n[,st2]) 返回靠右對齊的st,st為在st1的左邊用st2填充直至長度為n,st2的預設為空白格 
rpad(st1,n[,st2]) 返回靠左對齊的st,st為在st1的右邊用st2填充直至長度為n,st2的預設為空白格 
ltrim(st[,set])  返回st,st為從左邊刪除set中字元直到第一個不是set中的字元。預設時，指的是空格 
rtrim(st[,set])  返回st,st為從右邊刪除set中字元直到第一個不是set中的字元。預設時，指的是空格 
replace(st,search_st[,replace_st]) 將每次在st中出現的search_st用replace_st替換，返回一個st。預設時，刪除search_st 
substr(st,m[,n]) n=返回st串的子串，從m位置開始，取n個字元長。預設時，一直返回到st末端 
length(st) 數值，返回st中的字元數 
instr(st1,st2[,m[,n]])  數值，返回st1從第m字元開始，st2第n次出現的位置，m及n的預設值為1 

oracle教程:PLSQL常用方法匯總