Oracle筆記
修改表名:
alter table epo_bulletin rename to bulletin
增加字段:alter table tablename add (column datatype [default value][null/not null],….);
修改字段:alter table tablename modify (column datatype [default value][null/not null],….);
刪除字段:alter table tablename drop (column);
增加,修改,刪除多列的話,用逗號隔開
另:比較正規的寫法是:
– Add columns
alter table TABLE_NAME add FIELD_NAME datatype [default value];
如:alter table person add delflag char(1) default ‘0’;
modify columns:
alter table TABLE_NAME rename column FIELD_NAME to NEW_FIELD_NAME;
刪除一個字段:
alter table test1 drop COLUMN fieldName;
如:alter table person drop column delflag;
修改字段的長度:
alter table TABLE_NAME modify FIELD_NAME datatype
例如:alter table prak_document modify PRTDLINE VARCHAR2(2000 BYTE)
將View授於另一個賬號的select權限,例如:
grant select on vw_epr_release to eaidev
注:有時將Table授權於另一賬號,另一賬號在View中訪問此Table時依然會報錯,故可以改用如下這種語句
Grant SELECT on xy_email_t to hrorg with grant option
SQL的執行順序:From ->Where ->Group By ->Order By
執行From字句是從右往左進行執行,因此必須選擇記錄條件最少的表放在右邊;
對於Where字句其執行順序是從後向前執行,因此可以過濾最大數量記錄的條件必須寫在Where子句的末尾,而對於多表之間的連接,則寫在之前。
日期函數:
to_date(‘2014/02/25’, ‘yyyy/MM/dd’)
to_char(create_time, ‘yyyy/mm/dd’)
將日期時間為"2014/4/15 下午 01:37:15"轉成24小時制的"2014/04/15 13:34:15"
TO_CHAR(A.CREATE_TIME,‘YYYY/MM/DD HH24:MI:SS’)
–注:如果這個時間值的欄位是varchar2而不是date,若想將字符"2014/4/15 下午 01:37:15"轉成日期類型,可以用如下方法:
select to_date(‘2014/4/15 下午 01:37:15’,‘yyyy/mm/dd am hh:mi:ss’) from dual
計算兩個日期之間的分鍾數:
Select ROUND(TO_NUMBER(a.send_time - a.create_time) * 24 * 60) 時間差 from table1 a
將數字轉成千位符格式:
(CASE WHEN A.PMT_AMOUNT=0 THEN ‘0’ ELSE TRIM(TO_CHAR(A.PMT_AMOUNT,‘9,999,999,999,990.99’)) END) AS PMT_AMOUNT_SHOW
to_char(tmpDate,‘D’):取該日期(tmpDate)為一周內的第幾天,從周日開始,周日為1
數字轉字符串:to_char
例如:to_char(1210.73),也可以設定轉換的格式,例如:to_char(1210.73, ‘$9,999.00’)就得到’$1,210.73’
字符串轉數字:to_number
例如:to_number(‘1210.73’)
Varchar2轉Clob類弄:to_clob
例如:update table1 set field_clob=to_clob(field_char)
判斷一個字符型欄位的值是否為數字類型的方法:
REGEXP_LIKE(LIFNR , ‘(1?\d{0,}.?\d{0,}$)’)
set PO_NO= case PO_NO ‘4700001340’ (case when INSTR(PO_NO,‘IDL’)>0 then PO_NO else PO_NO+‘,’+‘IDL’ end)
1.查詢表中的一個字段,返回了多行,就把這麼多行的數據都拼成一個字符串。
例如:
select WMSYS.WM_CONCAT(a.name) from user
結果把三行的字段返回字串:“aa,bb,cc”
如果相用分號隔開,可以這樣:select replace(WMSYS.WM_CONCAT(a.name),‘,’,‘;’) from user a
結果就為"aa;bb;cc"
注意:如果欄位name定義的類型是VARCHAR2,那可以直接WMSYS.WM_CONCAT(a.name)這樣用,
但如果定義的類型是NVARCHAR2,就應該這樣:WMSYS.WM_CONCAT(TO_CHAR(a.name))
–還要注意,最好在WMSYS.WM_CONCAT外面加一層to_char(),因為在oracle高一點的版本中會報類型錯誤(會返回clob類型字段)
例如:select to_char(WMSYS.WM_CONCAT(a.name)) from user
//2017/10/30,在用這個函數處理字段內容是中文的時候會出現亂碼,解決這個問題的要點是在欄位前面增加一個to_char()涵數,如:
select to_char(WMSYS.WM_CONCAT(to_char(signercname))) from contract_signrecord
//另外,Oracle中的回車換行符是"chr(13)||chr(10)“(順序不要顛倒),我們默認的行轉列分隔符為”,",如果需要替換為回車換行符,可以用如下:
select replace(to_char(WMSYS.WM_CONCAT(to_char(signercname))),‘,’,chr(13)||chr(10)) from contract_signrecord
//2017/10/31,測試發現用WMSYS.WM_CONCAT()合並行的結果是沒有排序的,若需要增加排序,可參考如下案例的方法:
select A.TYPE,max(KEY)
from
(
select TYPE,
to_char(WMSYS.WM_CONCAT(CITY) OVER(PARTITION BY TYPE ORDER BY SOFT,ID)) KEY, --用type分組,用soft,id排序
row_number() over(PARTITION BY TYPE ORDER BY SOFT) rs
from
(
SELECT 2 AS ID , ‘瀋陽市’ AS CITY, ‘1’ TYPE,2 SOFT FROM DUAL
UNION ALL
SELECT 3 AS ID , ‘大連市’, ‘1’ TYPE, 1 SOFT FROM DUAL
UNION ALL
SELECT 5 AS ID , ‘長春市’, ‘2’ TYPE , 3 SOFT FROM DUAL
UNION ALL
SELECT 6 AS ID , ‘延邊市’, ‘2’ TYPE , 1 SOFT FROM DUAL
order by SOFT
)
)A
group by A.TYPE
//2018/03/05,列轉行的另一個函數"listagg()",有時間了解一下與上面這個函數"WMSYS.WM_CONCAT()"的區別,
基本用法:LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX),例如nation,listagg(city,‘,’) within GROUP (order by city)
還有一個高級用法,就是over(partition by XXX),也就是說,在你不實用Group By語句時候,也可以使用Listagg()函數;
update PAYMENT_DOCUMENT
set PMT_AMOUNT = (select (case when sum(PARK_AMOUNT) is null then 0 else sum(PARK_AMOUNT) end) PARK_AMOUNT from pmt_po_item WHERE IS_SEL=‘Y’ AND REF_NO=‘488E1891-88C3-4C6A-8544-DDDF846BF505A’),
PARK_BANK = (select PO_CURRENCY from pmt_po_item WHERE IS_SEL=‘Y’ AND REF_NO=‘488E1891-88C3-4C6A-8544-DDDF846BF505A’ AND ROWNUM=1),
PO_NO = (select WMSYS.WM_CONCAT(distinct PO_NO) from pmt_po_item WHERE IS_SEL=‘Y’ AND REF_NO=‘488E1891-88C3-4C6A-8544-DDDF846BF505A’)
where REF_NO=‘488E1891-88C3-4C6A-8544-DDDF846BF505’
2.oracle中的dual表介紹:
dual是oracle中的虛擬表,可以用來做很多事情,例如常用來查詢序列號:
select sysdate from dual; //系統時間
select your_sequence.nextval from dual; //獲得序列your_sequence的下一個值
select your_sequence.nextval from dual; //獲得序列your_sequence的當前值
創建序列的方法:
CREATE SEQUENCE emp_sequence --序列名
INCREMENT BY 1 – 每次加幾個
START WITH 1 – 從1開始計數
NOMAXVALUE – 不設置最大值
NOCYCLE – 一直累加,不循環
CACHE 10;
規範約束鍵的命名定義:
主鍵:“pk_” + 字段名稱
外鍵:“fk_” + 字段名稱
索引鍵:“idx_” + 字段名稱
唯一約束鍵:“unq_” + 字段名稱
檢查約束鍵:“chk_” + 字段名稱
3.oracle中創建約束鍵:
主鍵約束與唯一約束的區別:主鍵約束在一個表中只能有一個,而唯一約束可以有多個
3.1可以在創建表時就創建unique唯一約束,例如:
create table unique_test
(id number,
fname varchar2(20),
lname varchar2(20),
address varchar2(100),
email varchar2(40),
constraint name_unique unique(fname,lname))
在這裡創建了一個名為"name_unique"的唯一約束鍵,由fname和lname兩個字段組合。
3.2我們也可以在表創建完成後手動的通過修改表的方式來增加約束,例如:
alter table unique_test
add constraint unq_email unique(email);
3.3創建主鍵約束: PRIMARY KEY
ALTER TABLE table_name
ADD CONSTRAINTS contrains_name PRIMARY KEY (column_name);
例如:ALTER TABLE categoryinfo ADD CONSTRAINTS pk_category PRIMARY KEY(categoryid);
刪除主鍵約束:ALTER TABLE categoryinfo DROP CONSTRAINTS pk_category;
3.4創建外鍵約束: FOREIGN KEY
例如:ALTER TABLE productinfo
ADD CONSTRAINT fk_product FOREIGN KEY(category)
REFERENCES categoryinfo(categoryid)
ON DELETE CASCADE;
3.5創建檢查約束: CHECK
例如,設置“性別”欄只能輸入男或女
ALTER TABLE custominfo
ADD CONSTRAINT chk_gender CHECK(Gender=‘男’ or Gender=‘女’); --設置年齡為18~30,條件:age>=18 and age<=30
3.6他建非空約束: NOT NULL
可以在定義表字段時聲明,如:LoginName varchar2(10) NOT NULL,
也可以修改表字段的定義,如:ALTER TABLE managerinfo MODIFY LoginName NOT NULL;
相反的,如果要取消NOT NULL的設置,重複上句,只要把"NOT NULL"改為"NULL"就可以了。
4.decode函數的使用:
語法:decode(條件,值1,返回值1,值2,返回值2,…值n,返回值n[,缺省值])
Decode函數與一系列嵌套的 IF-THEN-ELSE語句相似
結合Lpad函數,如何使主鍵的值自動加1並在前面補0
select LPAD(decode(count(記錄號碼),0,1,max(to_number(記錄號碼)+1)),14,‘0’) 記錄號碼 from tetdmis
比如我要查詢某班男生和女生的數量分別是多少?現在可以這樣寫:
select decode(性別,男,1,0),decode(性別,女,1,0) from 表
再比如,表table_subject,有subject_name列,要求按照:語、數、外的順序進行排序。這時,就可以非常輕鬆的使用Decode完成要求了
select * from table_subject order by decode(subject_name, ‘語文’, 1, ‘數學’, 2, ‘外語’,3)
5.sign函數
作用:sign()函數根據某個值是0、正數還是負數,分別返回0、1、-1,即可用來比較兩個數之間的大小
如sign(int1 - int2),如果int1>int2結果為1,如果int1=int2結果為0,如果int1<int2結果為-1
6.substrb函數:取字串中指定位置長度的子串
如:substrb(month,5,2)
7.nvl()函數使用說明:
NVL(expr1,expr2)
如果expr1是NULL,則返回expr2,否則返回expr1.返回值與expr1類型相同,除非expr1是字串類,在這種情況下將返回VARCHAR2類型.這個函數用於確保查詢記錄集中不包含NULL值.
類似的還有
NVL(expr1,expr2,expr3)
如果expr1是NULL,則返回expr2,否則返回expr3.返回值與expr2類型相同,除非expr2是字元類型,在這種情況下返回VARCHAR2類型
8.將字符串前面的0自動去掉的方法:
LTRIM(LIFNR,‘0’) as LIFNR,
例如0078954005結果為78954005,0S564500結果為S564500
9.保留小數位數
round和trunc
round(m,n):可以四舍五入
trunc(m,n):直接丟棄,不四舍五入
trunc日期處理的常用方法:
(1)trunc(sysdate,‘year’)等於trunc(sysdate,‘yyyy’),截取到年(結果是返回本年的第一天,例如2016/1/1)
(2)trunc(sysdate,‘q’),截取到季度(本季度的第一天,如2016/10/1)
(3)trunc(sysdate,‘month’)等於trunc(sysdate,‘mm’),截取到月(本月的第一天,如2016/5/1)
(4)trunc(sysdate,‘ww’),截取每周的第一天(按年度1月1日的第一天為每周第一天),例如2016/5/20
注:w–按月份1日的第一天作為每周第一天
(5)trunc(sysdate,‘day’)等於trunc(sysdate,‘d’),截取到周(本周第一天,即上周日,如2016/5/22)
(6)trunc(sysdate,‘iw’),本周第2天(本周一,如2016/5/23)
(7)to_char(trunc(sysdate,‘dd’),‘yyyy/mm/dd hh24:mi:ss’);截取到日(當日的零點零分零秒,如2016/05/23 00:00:00)
(8)trunc(sysdate,‘hh24’),截取到小時(當前小時,零分零秒,如2016/5/23 15:00:00)
(9)trunc(sysdate,‘mi’),截取到分(當前分,零秒,如2016/5/23 15:35:00)
(10)trunc(sysdate,‘dd’),返回當前年月日,如2015/5/23,其功能等價於:TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’)
trunc數字的處理:TRUNC(number,num_digits),num_digits用於指定取整精度的數字,默認值為0,例如:
(1)trunc(123.458)=123
(2)trunc(123.458,0)=123
(3)trunc(123.458,1)=123.4
(4)trunc(123.458,-1)=120
(5)trunc(123.458,-4)=0
(6)trunc(123.458,4)=123.458
(7)trunc(123,1)=123
(8)trunc(123,-1)=120
10.在Oracle中測試存儲過程的例子:
DECLARE
SERVIERNO VARCHAR2(200);
BEGIN
sp_gen_serialNo(‘ECR’,‘1411’, SERVIERNO);
dbms_output.put_line(servierno);
END;
測試函數:
declare
b varchar2(100);
begin
dbms_output.put_line(func1(‘A’,b));
dbms_output.put_line(b);
end;
11.計算兩個日期年份之間的差:
方式1:to_number(to_char(sysdate,‘yyyy’))-to_number(to_char(Birthday,‘yyyy’))
方式2:extract(year from sysdate)-extract(year from Birthday)
另外,計算兩個日期之間的年份,保留一位小數:
round(months_between(sysdate,InltdDate)/12,1)
12.常用函數instr()和substr()函數
instr():在一個字串中查找指定的字元,返回被查找到的指定的字元的位置。
語法:
instr(sourceString,destString,start,appearPosition)
instr(‘源字串’ , ‘目標字串’ ,‘開始位置’,‘第幾次出現’)
其中sourceString代表源字串;
destString代表要從源字串中查找的子串;
start代表查找的開始位置,這個參數可選的,默認為1;
appearPosition代表想從源字元中查找出第幾次出現的destString,這個參數也是可選的, 默認為1
如果start的值為負數,則代表從右往左進行查找,但是位置資料仍然從左向右計算。
返回值為:查找到的字串的位置。
SUBSTR():是從給定的字元運算式或備註欄位中返回一個子字串。
語法格式:SUBSTR(cExpression,nStartPosition [,nCharactersReturned])
其中,cExpression指定要從其中返回字串的字元運算式或備註欄位;
nStartPosition用於指定返回的字串在字元運算式或備註欄位中的位置,
nCharactersReturned用於指定返回的字元數目,缺省時返回字元運算式的值結束前的全部字元。
例如:substr(‘abcdefghijlkm’,-2)將返回’km’
13.1 常用數據類型
1.字符型
varchar2 0~4000 可變長度的字符串
nvarchar2 0~2000 用來存儲Unicode字符集的變長字符型數據
char 0~2000 用於描述定長的字符型數據
nchar 0~1000 用來存儲Unicode字符集的定長字符型數據
long 0~2G 用來存儲變長的字符串
區別:
VARCHAR2最多存放4000位元組的資料,最多可以可以存入4000個字母,或最多存入2000個漢字(資料庫字元集編碼是GBK時,varchar2最多能存放2000個漢字,資料庫字元集編碼是UTF-8時,那就最多只能存放1333個漢字)
NVARCHAR2(size),size最大值為2000,單位是字元,而且不管是漢字還是字母,每個字元的長度都是2個位元組。所以nvarchar2類型的資料最多能存放2000個漢字,也最多只能存放2000個字母。並且NVARCHAR2不受資料庫字元集的影響。
2.數字型
number (p,s) p最大精度是38位(十進制) P代表的是精度,s代表的是保留的小數位數;可以用來存儲定長的整數和小數
float 存儲126位數據(二進制)
3.日期類型
date 用來存儲日期和時間,範圍在公元前4712年1月1日到公元9999年12月31日
timestamp 用來存儲日期和時間,顯示時間更精確,date類型的時間精確到秒,而timestamp可以精確到小數秒,并且可以顯示當前是上午或下午
4.其他數據類型
blob 最多可以存放4GB 存儲二進制數據
clob 最多可以存放4GB 存儲字符串數據
bfile 大小與操作系統有關,用來把非結構化的二進制數據存儲在數據庫以外的操作系統文件中
boolean 可選值:true,false,null
5.使用"%TYPE"方式定義變量類型
作用:使定義的變量數據類型與引用表中字段的類型同步
例如:declare v_productid productinfo.productid%TYPE;
v_productname varchar2(20);
v_desp CONSTANT v_productname%TYPE := ‘測試’;
v_long LONG := ‘Long類型測試’;
v_date DATE := SYSDATE;
13.2 复合類型的變量
- PL/SQL"記錄類型"
1.1 方法一
TYPE type_name IS RECORD
(
field_name datatype
[,…]
);
例如:
DECLARE
TYPE product_rec IS RECODE
(
v_productid productinfo.productid%TYPE,
v_productname varchar2(20),
v_productprice number(8,2)
);
v_product product_rec; --記錄類型變量
BEGIN
SELECT productid,productname,productprice INTO v_product FROM productinfo WHERE productid=‘024004001’;
DBMS_OUTPUT.PUT_LINE(v_product.v_productid);
DBMS_OUTPUT.PUT_LINE(v_product.v_productname);
DBMS_OUTPUT.PUT_LINE(v_product.v_productprice);
END;
1.2 方法二
利用%ROWTYPE聲明記錄類型數據
例如:
DECLARE
v_product productinfo%ROWTYPE;
BEGIN
SELECT * INTO v_product FROM productinfo WHERE productid=‘024001052’;
DBMS_OUTPUT.PUT_LINE(v_product.productid);
DBMS_OUTPUT.PUT_LINE(v_product.productname);
DBMS_OUTPUT.PUT_LINE(v_product.productprice);
END; - PL/SQL索引表類型(關聯數組)
該類型和數組相似,利用鍵值查找對應的值
例如:
DECLARE
TYPE prodt_tab_thd IS TABLE OF NUMBER(8)
INDEX BY VARCHAR2(20);
v_prt_chr prodt_tab_thd;
BEGIN
v_prt_chr(‘test’) := 10;
v_prt_chr(‘0’) := 102;
v_prt_chr(‘1’) := 3;
DBMS_OUTPUT.PUT_LINE('v_prt_chr(1) = ’ || v_prt_chr(‘1’)); --結果為3
DBMS_OUTPUT.PUT_LINE('v_prt_chr(0) = ’ || v_prt_chr.first); --結果為test
DBMS_OUTPUT.PUT_LINE('v_prt_chr(2) = ’ || v_prt_chr(v_prt_chr.first)); --結果為10
END; - VARRAY變長數組
該類型的元素個數是需要限制的,它是一個存儲有序元素的集合。集合的下標從1開始,比較適合較少的數據使用。
例如:
DECLARE
TYPE varr IS VARRAY(100) OF VARCHAR2(20);
v_product varr := varr(‘1’,‘2’);
i number;
BEGIN
v_product(1) := ‘THIS IS A’;
v_product(2) := ‘THIS’;
v_product.extend(); --與v_product.extend(1)一樣;
v_product(v_product.last()):= ‘test’;
–FOR i IN 1…v_product.count() LOOP
FOR i IN v_product.FIRST()…v_product.LAST() LOOP
DBMS_OUTPUT.PUT_LINE(‘v_product(’||i||‘)=’||v_product(i));
END LOOP;
END;
數組的方法介紹:LIMIT()–最大容量,COUNT()–正在使用的條目數,FIRST()–第一個下標(總是1),LAST()–返回正在使用的最大下標(與COUNT一樣),
PRIOR(n)和n-1是一樣的,NEXT(n)和n+1是一樣的,但是PRIOR(1)和NEXT(V.COUNT())則返回NULL;
EXTEND(k)–可以在VARRAY的最後追加k個新的條目(新增的條目的值默認為NULL),如果k沒有被指定,則只增加一個條目(對VARRAY的擴展前必須要對其進行初始化);
TRIM(K)–在VARRAY的尾部刪除最後k個條目(如果k沒有被指定時,刪除最後一個條目),TRIM(V.COUNT())表示刪除數組中的所有條目(與V.DELETE()的作用一樣)
14.增、刪、改、查
1.INSERT,常用的方式:
1.1 INSERT INTO table_name(column1,column2,…) VALUES(data1,data2,…);
1.2 INSERT INTO table_name1(column1,column2,…)
select column_name1,column_name2,… FROM table_name2;
1.3 CREATE TABLE table_name AS SELECT column_name1,column_name2,…FROM source_table;
–1.1和1.2都是表已存在的情況,1.3是創建了一個新表
2.UPDATE
2.1 UPDATE table_name SET column_name1=data1,column_name2=data2 [WHERE condition] ;
2.2 UPDATE table_name t1 SET (t1.column_name1,t1.column_name2) =
(SELECT t2.data1,t2.data2 FROM table_name t2 [WHERE t1.empno=t2.empno and t1.station not in(98,99)]);
//2019/11/25,更新備註:
如上2.2的SQL,如果t1表中存在條件不符的記錄,那這些記錄的更新字段(column_name1,column_name2)將會被更新成null值;
所以,如果我們是要只更新條件相符的這些記錄,那我們需要在主表(t1)增加where條件,如下:
UPDATE table_name t1 SET (t1.column_name1,t1.column_name2) = (SELECT t2.data1,t2.data2 FROM table_name t2 WHERE t1.empno=t2.empno and t1.station not in(98,99))
WHERE EXISTS(SELECT 1 FROM table_name t2 WHERE t1.empno=t2.empno and t1.station not in(98,99))
–AND t1.ref_no=‘abcde’ --其他的過濾條件
3.DELETE (注意與TRUNCATE的區別:TRUNCATE速度快,沒有where條件,自增序號會重置)
3.1 DELETE FROM table_name [WHERE condition];
4.SELECT
4.1 SELECT column_name1,column_name2,…FROM table_name [WHERE condition];
4.2 SELECT t.*,t.rowid FROM table_name [WHERE condition]; --可直接在查詢工具做DML(insert/update/delete)動作
15.排序時對NULL值的處理
NULLS FIRST | NULLS LAST
默認情況下排序時把它看成最大值,也就是說,當排序的記錄中出現NULL值時,默認情況下,升序排列時它在最後,降序排列時它在首位;
例如:select productname, quantity from productinfo order by quantity NULLS FIRST;–或者quantity NULLS LAST
再比如,表table_subject,有subject_name列,要求按照:語、數、外的順序進行排序。這時,就可以非常輕鬆的使用Decode完成要求了
select * from table_subject order by decode(subject_name, ‘語文’, 1, ‘數學’, 2, ‘外語’,3)
16.LIKE的通配符:
_: 可以替代一個字符;
%: 可以替代多個字符;
BETWEEN … AND …
2017/10/16注:
通常來講,like的功能可以用instr代替,即可以用這兩種方法來進行模糊查詢,例如:
instr(title,‘手冊’)>0 相當於 title like ‘%手冊%’
instr(title,‘手冊’)=1 相當於 title like ‘手冊%’
instr(title,‘手冊’)=0 相當於 title not like ‘%手冊%’
一般來講,資料量不大,如果沒有對表的字段"title"加索引,兩者效率差不多,區別不大;但如果加了索引,使用instr的方法效率提高了不少;
另外,在大資料量表(如1千多萬筆)查詢時,用instr的方法也是比like的方法明顯效率好很多;
17.連接查詢:
17.1 內連接
from t1 INNER JOIN t2 ON t1.xxx=t2.xxx
(INNER可以省略,等於 from t1,t2 where t1.xxx=t2.xxx)
17.2 自連接
獲取表ProductInfo中數量相等的不同產品,例如:
select p.productname,p.productprice,pr.productname,pr.productprice,pr.quantity
from ProductInfo p, ProductInfo pr
where p.productid != pr.productid --避免查詢結果的記錄中出現兩個產品ID一樣的數據,因為自身和自身的數量比永遠是相等的
and p.quantity = pr.quantity --表示數量相等的數據
and p.rowid < pr.rowid; --表示去除重複的記錄,只取Rowid比較小的那一條。
17.3 外連接
17.3.1 左外連接 (左邊的表為主表)
FROM t1 LEFT JOIN t2 ON t1.xxx=t2.xxx
(等價於: from t1,t2 where t1.xxx=t2.xxx(+))
注意:在使用Left Join時,如果是過濾從表的獨立條件(例如,等於什麼條件),則一定要放在ON的後面,不能放WHERE後面;
例如一張申請單上有三種不同類型(PR,PO,IR)的附件,只需要關聯類型為PR的附件記錄,寫法如下:
正確:SELECT i.UNID,a.ATTACHMENT_ID FROM DOC_FILE_ITEM i LEFT JOIN DOC_ATTACHMENT a
ON (i.UNID=a.REF_NO AND a.FILE_TYPE=‘PR’) WHERE i.REF_NO=‘7DEE2E16-EEC6-49AD-AA1C-2AC3560E3C39’
錯誤:SELECT i.UNID,a.ATTACHMENT_ID FROM DOC_FILE_ITEM i LEFT JOIN DOC_ATTACHMENT a
ON (i.UNID=a.REF_NO) WHERE i.REF_NO=‘7DEE2E16-EEC6-49AD-AA1C-2AC3560E3C39’ AND a.FILE_TYPE=‘PR’
針對查詢結果,有一些記錄的從表欄位(a.ATTACHMENT_ID)是空的,表示此表單沒有類型為PR的附件記錄;
如果此時只想出現從表欄位(a.ATTACHMENT_ID)是空的那些記錄,即表示對當前查詢結果的過濾條件,
則從表的這個條件一定要放在WHERE後面,不能放在ON後面,寫法如下:
正確:SELECT i.UNID,a.ATTACHMENT_ID FROM DOC_FILE_ITEM i LEFT JOIN DOC_ATTACHMENT a
ON (i.UNID=a.REF_NO AND a.FILE_TYPE='PR') WHERE i.REF_NO='7DEE2E16-EEC6-49AD-AA1C-2AC3560E3C39' AND a.ATTACHMENT_ID IS NULL
錯誤:SELECT i.UNID,a.ATTACHMENT_ID FROM DOC_FILE_ITEM i LEFT JOIN DOC_ATTACHMENT a
ON (i.UNID=a.REF_NO AND a.FILE_TYPE='PR' AND a.ATTACHMENT_ID IS NULL) WHERE i.REF_NO='7DEE2E16-EEC6-49AD-AA1C-2AC3560E3C39'
另一種實現此結果的寫法是用Not Exists(即表示沒有此類別附件的表單記錄)
SELECT i.UNID,i.APPENDIX_NAME FROM DOC_APPENDIX_ITEM i
WHERE NOT EXISTS(SELECT 1 FROM DOC_ATTACHMENT a WHERE i.UNID=a.REF_NO AND a.FILE_TYPE='FILE')
AND i.REF_NO='7DEE2E16-EEC6-49AD-AA1C-2AC3560E3C39'
17.3.2 右外連接 (右邊的表為主表,與左外連接相反)
FROM t1 RIGHT JOIN t2 ON t1.xxx=t2.xxx
(等價於: from t1,t2 where t1.xxx(+)=t2.xxx)
17.3.3 全外連接:除去返回內連接匹配的數據外,也將返回兩個表中不匹配的數據
FROM t1 FULL JOIN t2 ON t1.xxx=t2.xxx
使用(+)可以簡單記一下,該操作符總是放在非主表的一方;
需要注意的地方有:
1.需要使用WHERE子句,不能與OUTER JOIN一起使用;
2.該操作符不能用於全外連接;
3.如果外連接有多個條件,那麼每一個條件都需要使用該操作符;
總的來講,還是建議大家使用OUTER JOIN關鍵字。
18.常用函數分類:
18.1 數值類型函數:主要包括ABS(絕對值), MOD(求余), SIGN(正負號), CEIL(求最小整數),FLOOR(求最大整數),
POWER(冪函數,如power(5,2)結果為25), SORT(求平方根,如sort(100)結果為10), ROUND(四捨五入), TRUNC(直接截取)等
18.2 字符類型函數:主要包括CHR, ASCII, LENGTH(字符長度,如NVL(length(‘’),0))/LENGTHB(字節長度), SUBSTR(字符單位)/SUBSTRB(字節單位),
CONCAT(與"||"相似), UPPER, LOWER, INITCAP(轉換每個單詞的首字母為大寫)/NLS_INITCAP(可帶排序功能的轉換),
RPAD(向右填充)/LPAD(向左填充), TRIM(可以刪除指定的字符,默認為空)/RTRIM(與RPAD相反)/LTRIM(如ltrim(tname,‘0’):移除左邊開始所有連續的0),
REPLACE, INSTR(返回字符串中的位置(字符計算))/INSTRB等
18.3 日期類型函數:主要包括SYSDATE, SYSTIMESTAMP(如顯示毫秒TO_CHAR(SYSTIMESTAMP,‘YYYY/MM/DD HH24:MI:SS.FF3’)),
ADD_MONTHS(增加月份), LAST_DAY(返回指定日期對應月份的最後一天(是日期而不是天數)), NEXT_DAY(與星期參數有關),
EXTRACT(提取指定日期的特定部分,如EXTRACT(YEAR FROM SYSDATE)), MONTHS_BETWEEN(兩個月之間的月份數,可能為小數),
ROUND(日期四捨五入,可帶參數), TRUNC(date[,fmt])等
18.4 轉換函數:主要包括ASCIISTR(字符串轉ASCII類型字符串), CAST(數據類型轉換,一般用於數字與字符之間或字符與日期之間的轉換,如CAST(122 AS VARCHAR2(8)),CAST(SYSDATE AS VARCHAR2(12))),
CONVERT(字符串在字符集間轉換,如CONVERT(‘測試’,‘US7ASCII’,‘ZHS16GBK’)), TO_CHAR(如to_char(sysdate,‘YYYY/MM/DD HH24:MI:SS’)), TO_DATE(如to_date(‘2014/12/05’,‘yyyy/mm/dd’)), TO_NUMBER(‘2456.304’,‘9999.999’),
TO_SINGLE_BYTE(全角轉半角函數), ROWIDTOCHAR(rowid轉為字符串)等
18.5 NULL函數:主要包括LNNVL(condition)–得到除了condition要求條件之外的數據,包括NULL的條件,如WHERE LNNVL(QUANTITY>=70)將得到數據小於70的產品,包括數量為NULL的數據,
NVL(替換NULL值,如NVL(QUANTITY,0)), NVL2(exp1,exp2,exp3)–當exp1為NULL時,函數返回expr3的值,當expr1不為空時,則返回expr2的值。
18.6 集合函數:主要包括AVG, COUNT, MAX, MIN, SUM等
18.7 其它函數:如DECODE–如,DECODE(SIGN(QUANTITY-100),1,‘充足’,-1,‘不足’,0,‘剛好’)
19.輸出結果或語句的命令
DBMS_OUTPUT.PUT_LINE(string)
例如:
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘沒有對應的記錄’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘返回多條記錄’);
20.PL/SQL基本規則:
1.標識符不區分大小寫,所有的名稱在存儲時都被修改成大寫;
2.標識符中只允許字母、數字、下劃線,并且以字母開頭;
3.標識符最多30個字符;
4.不能使用保留字。如與保留字同名必須使用雙引號括起來;
5.語句使用分號結束,而且在語句塊的結束標誌END後面同樣需要使用分號;
6.語句的關鍵詞、標識符、字段的名稱以及表的名稱都需要用空格來分隔;
7.字符類型和日期類型需要使用單引號括起。
建議規範:
1.每行只寫一條語句;
2.全部保留字,內置函數,程序包,過程名稱,以及用戶自定義的數據類型都用大寫;
3.變量的名稱都要使用有意義的名稱命名;
4.命名應以""連接(長度不能超過30個字符),盡量不用大小寫混合的方式;
5.變量前最好加上前綴(例如:v_empno,即前綴為"v");
6.每個變量都應加上註釋;
7.在重要的程序段處都應該加上註釋;
8.建議3個半角空格替代TAB鍵進行縮進;
9.逗號後面以及操作符的前後都應加空格;
21.兩種註釋方法
1.單行註釋:使用"–“兩個短橫線;
2.多行註釋:使用”/* … */"
22.PL/SQL結構控制
- IF條件控制語句
IF condition1 THEN
statements;
[IF subCondition THEN
…
ELSE
…
END IF;]
ELSEIF condition2 THEN
statements;
ELSE
statements;
END IF; - CASE條件控制語句
2.1 簡單CASE條件控制
CASE v_category
WHEN ‘1’ || ‘001’ THEN
DBMS_OUTPUT.putline(‘電腦’);
WHEN ‘2’ THEN
DBMS_OUTPUT.putline(‘冰霜’);
[…]
[ELSE
…]
END CASE;
2.2 搜索式的CASE語句
CASE
WHEN boolean_expression THEN statement;
[WHEN v_productprice > 10 AND v_productprice <=50 THEN statement;]…
[ELSE statement;]
END CASE; - LOOP循環控制語句
3.1 基本的LOOP
[<<label_name>>]
LOOP
statement…
END LOOP [label_name];
例如:
DECLARE v_num NUMBER(8) := 1;
BEGIN
<<basic_loop>>
LOOP
–可以利用EXIT、GOTO等中斷LOOP循環(異常也能使LOOP語句中斷)
DBMS_OUTPUT.putline(‘當前v_num變量的值是:’ || v_num);
v_num := v_num + 1;
IF v_num > 5 THEN
–EXIT默認是終止退出當前的循環,但如果使用標簽,可以終止並退出指定的LOOP循環。
EXIT basic_loop;
END IF;
–EXIT…WHEN語句等同上面的IF…EXIT
–EXIT basic_loop WHEN v_num > 5; --建議使用此方式
END LOOP;
END;
3.2 WHILE...LOOP語句
[<<label_name>>]
WHILE boolean_expression
LOOP
statement;
END LOOP [label_name];
如果要確保LOOP循環體至少執行一次,可以將boolean_expression先置為true,然後在LOOP中增加IF...boolean_expression為false的跳出條件。
3.3 FOR...LOOP
[<<label_name>>]
FOR index_name IN
[REVERSE] --循環方式,表示倒序(upper_bound -> lower_bound),默認為升序(lower_bound -> upper_bound)
lower_bound .. upper_bound --下標 .. 上標
LOOP
statement..
END LOOP [label_name];
例如:
DECLARE v_num NUMBER(8) := 0;
BEGIN
DBMS_OUTPUT.put('1~20之間整數和');
<<for_loop>>
--inx循環計算器,該變量得到當前的循環次數,但不能為其賦值。
FOR inx IN 1..20 LOOP
v_num := v_num + inx;
END LOOP;
DBMS_OUTPUT.put_line(v_num);
END;
3.4 執行動態SQL語句
命令:EXECUTE IMMEDIATE,利用該命令可以執行動態SQL語句,可以執行DDL和DML語句,但不建議在存儲過程中使用。
例如DECLARE pc_createStr VARCHAR2(200);
BEGIN
pc_createStr := 'CREATE TABLE TAB(
operid VARCHAR2(10) PRIMARY KEY,
operName VARCHAR2(30),
operDate DATE
)';
EXECUTE IMMEDIATE pc_createStr;
END;
-
PL/SQL中的異常
處理異常的語法
EXCEPTION
WHEN exception1 [OR exception2…] THEN
statement [statement]…
[WHEN exception3 [OR exception4…] THEN
statement [statement]…]
[WHEN OTHERS THEN
statement [statement]…]
常用的預定義異常:CASE_NOT_FOUND, NO_DATA_FOUND, TOO_MANY_ROWS, VALUE_ERROR, ZERO_DIVIDE, CURSOR_ALREADY_OPEN -
PL/SQL函數編寫
語法:CREATE [OR REPLACE] FUNCTION func_name [(param_declare [, param2])]
RETURN datatype
{ IS | AS}
[declare_section]
BEGIN
statement…
END [name];
說明:參數有IN、OUT、IN OUT三種類型,至少有一個RETURN語句。
例1:根據當前的產品數量對產品價格進行打折計算,如果產品數量低于50就打七五折,如果產品數量等於或高於50則打九折。
CREATE FUNCTION f_pric
(v_pric IN NUMBER, v_qty IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF v_qty < 50 THEN
RETURN (v_price * 0.75);
ELSE
RETURN (v_price * 0.9);
END IF;
END;
調用:SELECT productid,productname,productprice,f_pric(productprice,quantity) FROM productinfo;
例2:使用IN OUT類型的參數,函數有兩個參數分別是產品類型編碼和價格,求出該產品類型下比指定價格高的產品的平均價格,並返回該範圍內最少的產品數量。
CREATE FUNCTION f_avg_pric(v_ctgry IN VARCHAR2, v_pric IN OUT NUMBER) RETURN NUMBER IS
v_qty NUMBER;
BEGIN
IF v_qty IS NULL THEN
v_pric := 0;
END IF;
SELECT AVG(productprice),MIN(quqntity) INTO v_pric,v_qty FROM productinfo
WHERE category = v_ctgry AND productprice > v_pric;
RETURN v_qty;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('沒有對應的數據!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('對應數據過多,請確認!');
END;
調用:v_qty := f_avg_pric(v_ctgry, v_price); --函數會對v_price重新賦值
刪除函數的語法:DROP FUNCTION [schema.]func_name; --schema:函數所屬的模式名稱。
24.connect by的應用例子:
tmpTable = string.Format(@"select deptcode from hrorg.vhrorgs_tonotes_gisall " +
" where (expirationdate IS NULL OR expirationdate > sysdate) " +
" start with superdeptcode=‘{0}’ connect by prior deptcode=superdeptcode " +
" union all " +
" select ‘{0}’ as deptcode from dual “, deptCode);
subSql = “select id from rms_applicant_forhr where status<>‘草稿’ and DEPTCODE in(” + tmpTable + “)”;
sql += " AND A.AID IN(” + subSql + “)”;
25.Oracle中的DDL、DQL、DML及DCL含義:
(1).DDL:資料定義語言
create table 創建表
alter table 修改表
drop table 刪除表
truncate table 刪除表中所有行
create index 創建索引
drop index 刪除索引
當執行DDL語句時,在每一條語句前後,oracle都將提交當前的事務。如果使用者使用insert命令將記錄插入到資料庫後,
執行了一條DDL語句(如create table),此時來自insert命令的資料將被提交到資料庫。當DDL語句執行完成時,DDL語句會被自動提交,不能回滾。
(2).DQL:資料查詢語言
SELECT
FROM
WHERE
GROUP BY /ORDER BY
(3).DML:資料操縱語言
insert 將記錄插入到資料庫
update 修改資料庫的記錄
delete 刪除資料庫的記錄
當執行DML命令如果沒有提交,將不會被其他會話看到。除非在DML命令之後執行了DDL命令或DCL命令,或用戶退出會話,或終止實例,
此時系統會自動發出commit命令,使未提交的DML命令提交。
(4).DCL:資料控制語言
GRANT[權限]
DENY[權限]
26.一個用分隔符處理的數組例子:
declare
var_phone varchar2(100);
idex int;
nextIdex int;
iphone varchar2(100);
begin
var_phone := ‘10000,10001,10002,10003,10004’;
dbms_output.put_line(var_phone);
idex := 0;
nextIdex := 1;
while nextIdex > 0 loop
nextIdex := instr(var_phone, ‘,’, idex+1);
dbms_output.put_line(idex || ’ — ’ || nextIdex);
if nextIdex > 0 then
iphone := substr(var_phone, idex+1, nextIdex-idex-1);
else
iphone := substr(var_phone, idex+1);
end if;
idex := nextIdex;
dbms_output.put_line(iphone);
end loop;
end;
還有一個特別的,將字符串轉換成行,
語句:SELECT REGEXP_SUBSTR(MatirType, ‘[^,]+’, 1, rownum) from dual CONNECT BY ROWNUM <= (LENGTH(MatirType) - LENGTH(REPLACE(MatirType, ‘,’, ‘’)) + 1);
"MatirType"是參數變量,例如當MatirType:=‘A1,A2,A3’;時,出來的結果就會是三行:
A1
A2
A3
或者直接執行SQL:SELECT REGEXP_SUBSTR(‘A1,A2,A3’, ‘[^,]+’, 1, rownum)aa from dual CONNECT BY ROWNUM <= (LENGTH(‘A1,A2,A3’) - LENGTH(REPLACE(‘A1,A2,A3’, ‘,’, ‘’)) + 1);
這個功能常被用來Where條件中的in的用法,例如:
where t.buyerempno in (select n.buyerempno from vpa_setuserauthor m left join vpa_buyermaprelation n on m.bu=n.bu where m.empno=inempno) and t.ym=byYearMonth
and t.matkl in (select k.materialgroup from vpa_straighttimbermaprelation k where k.materialtype in (SELECT REGEXP_SUBSTR (MatirType, ‘[^,]+’, 1,rownum) from dual CONNECT BY ROWNUM <= (LENGTH(MatirType) - LENGTH(REPLACE(MatirType, ‘,’, ‘’)) + 1))) and t.bu=byBu and t.site=bySite
2018/03/25增加備註,也可以用"REGEXP_SUBSTR"這個方法來讀取“用指定分隔符的第幾個值”,非常方法實用,例如:
- select REGEXP_SUBSTR(‘F7990001,張三,’, ‘[^,]+’, 1, 1) splitValue from dual
–結果返回"F7990001",即用分隔符",“來分隔的第1個值(如果字符串中沒有分隔符”,",則返回字符串原本的內容) - select REGEXP_SUBSTR(‘F7990001,張三,’, ‘[^,]+’, 1, 2) splitValue from dual
–結果返回"張三",即用分隔符",“來分隔的第2個值(如果字符串中沒有分隔符”,",則返回空)
27.游標(cursor)循環嵌套使用:
一般情況下,一開始會先定義最外層的游標,然後開始Begin,for循環第一層游標,如果這時需要再定義新的游標時,需要如下做:
(1).使用declare關鍵字
(2).定義游標變量(跟最外層的定義一樣)CURSOR cur_vai IS Select語句
(3).後面一定要有一對Begin…End;
(4).Begin對裡面至少要有一句執行語句(If…End if;要求也一樣)
例子:可參考EOL數據庫中的"USP_GEN_SAP_OUTPUT_QTY"存儲過程(lhmix:WF_EOLDEV/EOLDEV)
28.判斷一個欄位的值是否為數字的方法:
方法一,使用trim+translate函數:
代碼如下:
select * from table where trim(translate(column,‘0123456789’,’ ‘)) is NULL;
這裡要注意的是:translate函數的第三個參數是一個空格,不是’‘, 因為translate的第三個參數如果為空的話,那麼永遠返回’‘,這樣的就不能達到過濾純數位的目的。
這樣把所有的數字都轉化為空格,如果全部是由數字構成,那麼一旦trim後自然是空,實現了上述目標。當然如果想排除空項的話,可以這樣寫:
代碼如下:
select * from table where trim(translate(nvl(column,‘x’),‘0123456789’,’ ')) is NULL;–x 表示任何’0-9’以外的字元。
方法二,使用regexp_like函數:
代碼如下:
select * from table where regexp_like(column,‘2+[0-9]$’);
這裡要注意的是:regexp_like函數不是在所有的oracle版本中都能使用的。regexp_like是oracle支援規則運算式的四個函數:regexp_like,regexp_replace,regexp_instr,regexp_substr中的 一個,有關這方面更加詳細資訊,請關注相關文檔。
注:還有一個種的表達式:判斷一個字符型欄位的值是否為數字類型的方法:這種允許前面包含正負符號,有小數點之類的。
REGEXP_LIKE(LIFNR, ‘(3?\d{0,}.?\d{0,}$)’)
(特別備註,判斷工號第一位是否為字母開頭的方法:regexp_like(substr(empno,0,1),‘[[:alpha:]]’))
方法三,利用to_number
CREATE OR REPLACE FUNCTION isnumeric(str IN VARCHAR2)
RETURN NUMBER
IS
v_str FLOAT;
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN 0;
END;
RETURN 1;
END IF;
END isnumeric;
29.Oracle中nvarchar2與varchar2的相互轉換
(1).將nvarchar2轉換為varchar2;
declare
v_username varchar2(12);
v_nm_login nvarchar2(12);
begin
select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_nm_login))
into v_username
from dual;
end;
(2).將varchar2轉換為nvarchar2;
declare
v_username varchar2(12) := ‘pavan408’;
v_nm_login nvarchar2(12);
begin
select utl_raw.cast_to_nvarchar2(utl_raw.cast_to_raw(v_username))
into v_nm_login
from dual;
end;
補充:以上轉換如果含有漢字有可能轉換失敗,都成了"??",可以用Translate函數來轉換,例如:
Translate(‘你好’ USING NCHAR_CS)–此處的“你好”是varchar類型
Translate(yourWords USING CHAR_CS)–此處的yourWords是nvarchar類型
30.關於數字千分位顯示的特殊需求,即整數部分顯示千分位,小數位有多少位就顯示多少位,不固定小數位數;
例如2233.4567=>2,233.4567,當2233.45=>2,233.45,而不是2,233.4500
方法一:第一,整數位單獨截取出來顯示千分位; 例如:to_char(trunc(22355.135),‘fm9,999,999,990’) => 22,355
第二,如果有帶小數位,剛增加拼接小數位; 例如:to_char(22355.135-trunc(22355.135)) => .135
如:select to_char(trunc(22355.135),‘fm9,999,999,990’)||(case when trunc(22355.135)=22355.135 then ‘’ else to_char(22355.135-trunc(22355.135)) end) from dual;
方法二:第一,先將數字的小數位顯示很多位,如10位; 例如:to_char(22355.135,‘fm9,999,999,990.0000000000’) => 22,355.1350000000
第二,再將右邊多餘的字符去除(帶小數時右邊是’0’,整數時右邊是’.‘) 例如:rtrim(rtrim(to_char(22355.135,‘fm9,999,999,990.0000000000’),‘0’),’.‘) => 22,355.135
如:select rtrim(rtrim(to_char(22355.135,‘fm9,999,999,990.0000000000’),‘0’),’.') from dual
31.Update或Insert的欄位如果定義的是Clob類型,如果內容小於4000字節時,像普通的寫法就可以;
但如果內容大於4000字節時就要特殊一點,參考如下案例:
sql = “DECLARE V_LANG CLOB := '” + Str + “‘; " +
"BEGIN " +
" insert into vms_tosap_log (formid,status,msg,createdate,data,action) values(’” + key + “‘,’” + status + “‘,’” + Rmsg + “‘,sysdate,V_LANG,’” + Aciton + "'); " +
"End; ";
DAL.DAL dal = new DAL.DAL();
dal.ExecuteNonQuery(Properties.Settings.Default.DBWF, sql);