【无标题】

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 , ‘(¹?\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 复合類型的變量

1. 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;
2. 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;
3. 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結構控制

1. IF條件控制語句
   IF condition1 THEN
   statements;
   [IF subCondition THEN
   …
   ELSE
   …
   END IF;]
   ELSEIF condition2 THEN
   statements;
   ELSE
   statements;
   END IF;
2. 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;
3. 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;	

4. 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

5. 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"這個方法來讀取“用指定分隔符的第幾個值”，非常方法實用，例如：

1. select REGEXP_SUBSTR(‘F7990001,張三,’, ‘[^,]+’, 1, 1) splitValue from dual
   –結果返回"F7990001"，即用分隔符",“來分隔的第1個值（如果字符串中沒有分隔符”,"，則返回字符串原本的內容）
2. 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,‘²+[0-9]$’);
　　這裡要注意的是：regexp_like函數不是在所有的oracle版本中都能使用的。regexp_like是oracle支援規則運算式的四個函數：regexp_like，regexp_replace，regexp_instr,regexp_substr中的 一個，有關這方面更加詳細資訊，請關注相關文檔。

注：還有一個種的表達式：判斷一個字符型欄位的值是否為數字類型的方法：這種允許前面包含正負符號，有小數點之類的。
REGEXP_LIKE(LIFNR, ‘(³?\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);

---

1. ± ↩︎

2. 0-9 ↩︎

3. ± ↩︎