SELECT
--------
SELECT 欄位 FROM 表格名稱
DISTINCT
--------
SELECT DISTINCT 欄位
FROM 表格名稱
WHERE
--------
SELECT 欄位
FROM 表格名稱
WHERE condition
AND / OR
--------
SELECT 欄位
FROM 表格名稱
WHERE 條件
{[ANDOR] 條件}
IN
--------
SELECT 欄位
FROM 表格名稱
WHERE 欄位 IN ('值1', '值2', ...)
BETWEEN
--------
SELECT 欄位
FROM 表格名稱
WHERE 欄位 BETWEEN '值1' AND '值2'
LIKE
--------
SELECT 欄位
FROM 表格名稱
WHERE 欄位 LIKE {模式}
ORDER BY
--------
SELECT 欄位
FROM 表格名稱
WHERE 條件
ORDER BY 欄位 [ASC, DESC]
COUNT
--------
SELECT COUNT(欄位)
FROM 表格名稱
GROUP BY
--------
SELECT 欄位1, SUM(欄位2)
FROM 表格名稱
GROUP BY 欄位1
HAVING
--------
SELECT 欄位1, SUM(欄位2)
FROM 表格名稱
GROUP BY 欄位1
HAVING (函數條件)
CREATE TABLE
--------
CREATE TABLE 表格名稱
(欄位1 資料型別, 欄位2 資料型別,...)
DROP TABLE
--------
DROP TABLE 表格名稱
TRUNCATE TABLE
--------
TRUNCATE TABLE 表格名稱
INSERT INTO
--------
INSERT INTO 表格名稱 (欄位1, 欄位2, ...)
VALUES (值1, 值2, ...)
UPDATE
--------
UPDATE 表格名稱
SET 欄位1 = [新值]
WHERE 條件
DELETE
--------
DELETE 表格名稱
WHERE [條件]
2007年10月16日 星期二
[PL/SQL] 善用動態SQL、PL/SQL
自Oracle 8i後，動態SQL有了第二個選擇：native dynamic SQL (NDS)。相對於DBMS_SQL，
他的優點除了較易撰寫，而且也能較快執行，Oracle ERP開的很多Package也都有他的影子。
動態 SQL
語法：
EXECUTE IMMEDIATE Sql_string
[INTO {define_list … record}]
[USING [IN OUT IN OUT] bind_list …];
Sql_string
此為字串運算式，可包括SQL語句或PL/SQL區塊。
define_list
此為變數，接收查詢回來的資料欄位值。
record
此可以是以使用者定義的TYPE或％ROWTYPE為基礎的紀錄，接收查詢回來的資料列。
bind_list
此為運算式，其值是傳給Sql_string。或為識別字，可當作輸出輸入變數使用。
INTO 子句
用於單列查詢上。指定接收查詢回來的資料欄位值。
USING 子句
此能讓你對SQL字串提共繫結引數，此用法通常與PL/SQL有關，預設值為IN。
測試一：
SQL> SELECT DEPTNO, DNAME FROM DEPT;
DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
BEGIN
EXECUTE IMMEDIATE 'UPDATE DEPT SET DNAME = ''TEST'' WHERE DNAME = ''ACCOUNTING''';
END;
/
PL/SQL 程序順利完成.
SQL> SELECT DEPTNO, DNAME FROM DEPT;
DEPTNO DNAME
---------- ----------------------------
10 TEST
20 RESEARCH
30 SALES
40 OPERATIONS
測試二：
SQL> DECLARE
2 V_NAME DEPT.DNAME%TYPE;
3 BEGIN
4 EXECUTE IMMEDIATE 'SELECT DNAME FROM DEPT WHERE DEPTNO= 10'
5 INTO V_NAME;
6 DBMS_OUTPUT.PUT_LINE(‘DNAME IS ’V_NAME);
7 END;
8 /
DNAME IS TEST
PL/SQL 程序順利完成.
測試三：
SQL> DECLARE
2 V_NAME DEPT.DNAME%TYPE;
3 BEGIN
4 EXECUTE IMMEDIATE 'SELECT DNAME FROM DEPT WHERE DEPTNO= :NO'
5 INTO V_NAME
6 USING IN 10;
7 DBMS_OUTPUT.PUT_LINE('DNAME IS 'V_NAME);
8 END;
9 /
DNAME IS TEST
PL/SQL 程序順利完成.
動態 PL/SQL
語法：
OPEN {cursor_variable :host_cursor_variable } FOR SQL_string
[USING bind_argument …];
cursor_variable
此為弱類型CURSOR變數。
:host_cursor_variable
此為CURSOR變數，主要宣告於像是Oracle Call Interface程式的PL/SQL主機環境中。
SQL_string
此主要為需動態執行的SELECT敘述。
USING 子句
此能讓你對SQL字串提共繫結引數，規則與IMMEDIATE相同。
測試：
CREATE OR REPLACE PROCEDURE SP_PMS_SCH_SCHSLIP(IN_STR_DT IN NUMBER,
IN_END_DT IN NUMBER) IS
-- 宣告 REF CURSOR TYPE
TYPE CURSOR_TYPE IS REF CURSOR;
-- 宣告 CUR_PM_SLIP 為 CURSOR_TYPE的CURSOR
CUR_PM_SLIP CURSOR_TYPE;
V_CUR VARCHAR2(3000);
V_SLIP_NO PM_SCHSLIP.SLIP_NO%TYPE;
V_FST_STR_DT PM_SCHSLIP.FST_STR_DT%TYPE;
V_STATUS PM_SCHSLIP.STATUS%TYPE;
V_EST_END_DT PM_SCHSLIP.EST_END_DT%TYPE;
V_SCH_SW PM_SCHSLIP.SCH_SW%TYPE;
V_SCH_ADJ PM_SCHSLIP.SCH_ADJ%TYPE;
V_PRIORITY PM_SCHSLIP.PRIORITY%TYPE;
BEGIN
-- SQL敘述
V_CUR :=
'SELECT A.SLIP_NO, DECODE(SIGN(NVL(A.EST_STR_DT,0)-:1),1,A.EST_STR_DT,:2)
STR_DT, A.STATUS,A.EST_END_DT,A.SCH_SW, A.SCH_ADJ, A.PRIORITY
FROM PM_SLIPM A
WHERE A.GENERATE_CODE = ''Y''
AND A.STATUS NOT IN (''F'',''C'',''H'')
AND NVL(A.EST_STR_DT,0) <= :3
AND NVL(A.SCH_SW,''2'') = ''1'''V_ORDERBY;
-- 開啟CURSOR
OPEN CUR_PM_SLIP
FOR V_CUR USING IN_STR_DT, IN_STR_DT, IN_END_DT; LOOP
-- 擷取資料
FETCH CUR_PM_SLIP INTO V_SLIP_NO, V_FST_STR_DT, V_STATUS,
V_EST_END_DT,V_SCH_SW, V_SCH_ADJ, V_PRIORITY;
EXIT WHEN CUR_PM_SLIP%NOTFOUND;
-- INSERT DATA ...
END LOOP;
COMMIT;
END;
/
其中:1、:2、:3對應IN_STR_DT, IN_STR_DT, IN_END_DT變數
他的優點除了較易撰寫，而且也能較快執行，Oracle ERP開的很多Package也都有他的影子。
動態 SQL
語法：
EXECUTE IMMEDIATE Sql_string
[INTO {define_list … record}]
[USING [IN OUT IN OUT] bind_list …];
Sql_string
此為字串運算式，可包括SQL語句或PL/SQL區塊。
define_list
此為變數，接收查詢回來的資料欄位值。
record
此可以是以使用者定義的TYPE或％ROWTYPE為基礎的紀錄，接收查詢回來的資料列。
bind_list
此為運算式，其值是傳給Sql_string。或為識別字，可當作輸出輸入變數使用。
INTO 子句
用於單列查詢上。指定接收查詢回來的資料欄位值。
USING 子句
此能讓你對SQL字串提共繫結引數，此用法通常與PL/SQL有關，預設值為IN。
測試一：
SQL> SELECT DEPTNO, DNAME FROM DEPT;
DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
BEGIN
EXECUTE IMMEDIATE 'UPDATE DEPT SET DNAME = ''TEST'' WHERE DNAME = ''ACCOUNTING''';
END;
/
PL/SQL 程序順利完成.
SQL> SELECT DEPTNO, DNAME FROM DEPT;
DEPTNO DNAME
---------- ----------------------------
10 TEST
20 RESEARCH
30 SALES
40 OPERATIONS
測試二：
SQL> DECLARE
2 V_NAME DEPT.DNAME%TYPE;
3 BEGIN
4 EXECUTE IMMEDIATE 'SELECT DNAME FROM DEPT WHERE DEPTNO= 10'
5 INTO V_NAME;
6 DBMS_OUTPUT.PUT_LINE(‘DNAME IS ’V_NAME);
7 END;
8 /
DNAME IS TEST
PL/SQL 程序順利完成.
測試三：
SQL> DECLARE
2 V_NAME DEPT.DNAME%TYPE;
3 BEGIN
4 EXECUTE IMMEDIATE 'SELECT DNAME FROM DEPT WHERE DEPTNO= :NO'
5 INTO V_NAME
6 USING IN 10;
7 DBMS_OUTPUT.PUT_LINE('DNAME IS 'V_NAME);
8 END;
9 /
DNAME IS TEST
PL/SQL 程序順利完成.
動態 PL/SQL
語法：
OPEN {cursor_variable :host_cursor_variable } FOR SQL_string
[USING bind_argument …];
cursor_variable
此為弱類型CURSOR變數。
:host_cursor_variable
此為CURSOR變數，主要宣告於像是Oracle Call Interface程式的PL/SQL主機環境中。
SQL_string
此主要為需動態執行的SELECT敘述。
USING 子句
此能讓你對SQL字串提共繫結引數，規則與IMMEDIATE相同。
測試：
CREATE OR REPLACE PROCEDURE SP_PMS_SCH_SCHSLIP(IN_STR_DT IN NUMBER,
IN_END_DT IN NUMBER) IS
-- 宣告 REF CURSOR TYPE
TYPE CURSOR_TYPE IS REF CURSOR;
-- 宣告 CUR_PM_SLIP 為 CURSOR_TYPE的CURSOR
CUR_PM_SLIP CURSOR_TYPE;
V_CUR VARCHAR2(3000);
V_SLIP_NO PM_SCHSLIP.SLIP_NO%TYPE;
V_FST_STR_DT PM_SCHSLIP.FST_STR_DT%TYPE;
V_STATUS PM_SCHSLIP.STATUS%TYPE;
V_EST_END_DT PM_SCHSLIP.EST_END_DT%TYPE;
V_SCH_SW PM_SCHSLIP.SCH_SW%TYPE;
V_SCH_ADJ PM_SCHSLIP.SCH_ADJ%TYPE;
V_PRIORITY PM_SCHSLIP.PRIORITY%TYPE;
BEGIN
-- SQL敘述
V_CUR :=
'SELECT A.SLIP_NO, DECODE(SIGN(NVL(A.EST_STR_DT,0)-:1),1,A.EST_STR_DT,:2)
STR_DT, A.STATUS,A.EST_END_DT,A.SCH_SW, A.SCH_ADJ, A.PRIORITY
FROM PM_SLIPM A
WHERE A.GENERATE_CODE = ''Y''
AND A.STATUS NOT IN (''F'',''C'',''H'')
AND NVL(A.EST_STR_DT,0) <= :3
AND NVL(A.SCH_SW,''2'') = ''1'''V_ORDERBY;
-- 開啟CURSOR
OPEN CUR_PM_SLIP
FOR V_CUR USING IN_STR_DT, IN_STR_DT, IN_END_DT; LOOP
-- 擷取資料
FETCH CUR_PM_SLIP INTO V_SLIP_NO, V_FST_STR_DT, V_STATUS,
V_EST_END_DT,V_SCH_SW, V_SCH_ADJ, V_PRIORITY;
EXIT WHEN CUR_PM_SLIP%NOTFOUND;
-- INSERT DATA ...
END LOOP;
COMMIT;
END;
/
其中:1、:2、:3對應IN_STR_DT, IN_STR_DT, IN_END_DT變數
[DB] 查詢被LOCK的TABLE資訊 (SQL Script)
當資料被lock時，下面這段SQL可以方便查出是那個Table lock，lock的mode為何，以及附帶一段kill session指令，方便刪除已當掉的session，不過要提醒的是，沒把握或不清楚時不要亂刪除 session。
0 - none - 無
[xsb Oracle Blog]有很好的解釋http://xsb.itpub.net/post/419/38475
[PL/SQL] PL/SQL函數說明
以前收集的PL/SQL函數說明，覺得還不錯，給大家參考參考
PL/SQL函數指令 | 說明 |
ABS(n) | 絕對值 |
ACOS(O) | 逆三角餘弦值 |
ADD_MONTHS(d，n) | 傳回d值加n個月 |
ASCII(n) | Ascii字元傳回十進位值 |
ATAN(n) | 逆三角正切值(徑，p/2 ~ -p/2) |
ATAN2(m，n) | 逆三角正切值(徑，p ~ -p) |
AVG(n) | 傳回平均值 |
BFILENAME(m，n) | 傳回外部檔案值，m=目錄字串， n=檔案字串 |
CEIL(n) | 無條件進位的整數 |
CHARTOROWID | 將字元型態轉為ROWID型態 |
CHR(n) | Chr十進位值，傳回ascii碼 |
CONCAT(m，n) | 不論文字或數字相加，都以字串相 加方式 |
CONVERT(m,n,z) | 將字串轉換另一國家字元設定值 |
COS(n) | 傳回n的餘弦值 |
COSH(n) | 傳回n的雙曲線餘弦值 |
COUNT(n) | 傳回總筆數 |
DECODE (C n，v1，r1，v2，r2d1)=v2 | 變數c字串或n數字，=v1則值=r1，則值=v2，或=dl |
DUMP(m，n，n1，n2…) | 傳回m的長度及內碼，n=進位數(預設值=10進位)，n1=m的開始位置，n2=取的位無數 |
EMPTY_[B C] LOB() | 將BLOB或CLOB初始化，就是傳回BLOB或CLOB的空指標 |
EXP(n) | 傳回自然對數e的n次方值 |
FLOOR(n) | 無條件捨去的整數 |
GREATEST(m) | m可為數字或文字，傳回最大值 |
GROUPING(n) | 傳回群集 |
HEXTORAW(m) | 將十六進位轉換為原始值 |
INITCAP(n) | 傳回字句一個字為大寫 |
INSTR(char1, char2 [m，[n]] ) | 求char1中從m位置起char2第n次出現的位置 |
INSTR(m，n) | 傳回n字串在m字串中的位置 |
INSTRB(m，n) | 以byte傳回n字串在m字串中的位置 |
LAST_DAY(d) | 傳回該月的最後一天的日期 |
LEAST(m1，m2，m3...mn) | m可為數字或文字，傳回最小值 |
LENGTH(n) | 傳回字串的長度 |
LENGTHB(n) | 以byte方式傳回字串的長度 |
LN(n) | 自然對數 |
LOG(m , n) | m進位的n對數 |
LOWER | 傳回每一字為小寫 |
LPAD(m , n , z) | 以n減1ength(m)的z字元數填入m的左邊 |
LTRIM(n) | 去除字串左邊的空格字元 |
MAX(n) | 傳回最大值 |
MIN(n) | 傳回最小值 |
MOD(m , n) | 求m/n整數的餘數 |
MONTHS_BETWEEN(m，n) | 傳回m - n的值，單位月(日期相減 單位為日) |
NEW_TIME(m , n , z) | n時區的日期、時間是m，傳回在 z時區的日期時間 |
NEXT_DAY(m , n) | 下一個星期n(1-7=sun-sat)是那一天 |
NLS_CHARSET_DECL_LEN(M , N) | 傳回所要的字體組對應當前資料庫 的字體組，m=字體數；n=字體組(如:'ZHT16BIG5') |
NLS_CHARSET_ID(m) | 傳回字體組的識別碼，m=字體組 |
NLS_CHARSET_NAME(n) | 傳回字體組，n=字體組的識別碼 |
NLS_INITCAP(m [,N]) | 傳回字句一個字為大寫(依照國家 語言不同) |
NLS_LOWER | 傳回每一字為小寫(依照國家語言 不同) |
NLS_UPPER | 傳回每一字為大寫(依照國家語言 不同) |
NLSSORT | 決定排列順序(依照國家語言不同) |
NVL(m，n) | m為NULL則傳回n值，否則傳回 m值 |
NVL2(m , n , z) | |
POWER(m , n) | 傳回m的n次方 |
RAWTOHEX(m) | 將原始值(例如A=65)進位轉換為 十六進位 |
REPLACE(m , n [, z] ) | M字串中去除n字串以z字串取代 |
ROUND(d) | 日期四捨五入以中午為中點 |
ROUND(m [, n]) | 算數m四捨五入，取到小數點第n 位[n default=0] |
ROWIDTOCHAR(m) | 將ROWID型態轉為字元型態 |
RPAD(m , n , z) | 以n減1ength(m)的z字元數填入m的右邊 |
RTRIM(n) | 去除字串右邊的空格字元 |
SIGN(n) | 信號的傳回，n<0則-1，n=0則0，n>0則 1 |
SIN(n) | 傳回n的正弦值 |
SINH(n) | 傳回n的雙曲線的正弦值 |
SOUNDEX(m) | 傳回以字串m的聲音(可做文字發音比對) |
SQRT(n) | 傳回n開平方根的值 |
STDDEV(n) | 傳回標準差 |
SUBSTR(m , n , z) | m字串中從頭n數位開始取，取z 個字元 |
SUBSTRB(m , n , z) | 以byte方式m字串中從頭n數位開始取，取z個字元 |
SUM(n) | 傳回加總 |
SYS_CONTEXT(m , n) | 傳回與某一個context名稱關聯的Package spec定義的變數的值，m=context的名稱，n=變數名稱 |
SYS_GUID() | 傳回全球獨一無二的識別碼 |
SYSDATE | 傳回系統日期 |
TAN(n) | 傳回n的正切值 |
TANH | 傳回n的雙曲線的正切值 |
TO_CHAR(d，t) | 將日期格武轉換為文字格式，d=日期，t=日期型態格武 |
TO_CHAR(n) | 將數字格式轉換為文字格式 |
TO_DATE(m , t) | 將文字格式轉換為日期格式，m=字串，t=日期型態格式 |
TO_LOB(m) | 將LONG的資料型態轉換成LOB的資料型態 |
TO_MULTI_BYTE(m) | 將CHAR的資料型態轉換成多位元的字體 |
TO_NUMBER(m) | 將字串轉成數字 |
TO_SINGLE_BYTE(m) | 將多位元的字體轉換成CHAR的字體 |
TRANSLATE(m using n) | 將Char轉咸nchar |
TRANSLATE(m , n [,z]) | 以n字串尋找m字串，以z字串取代 |
TRIM(n) | 去除字串左、右邊的空格字元 |
TRUNC(d [, n]) | 將d截斷到n[n default=NULL(日)]，n='month'截斷到月 |
TRUNC(m [, n]) | 將m截斷到n位[n default=0]，n=-1則截斷個位為0 |
UID(m) | 傳回目前的使用者的唯一識別碼 |
UPPER(n) | 傳回每一字為大寫 |
USER | 傳回使用者名稱 |
USERENV(m) | 傳回使用者環境參數的資訊 |
VARIANCE(n) | 傳回變異量 |
VSIZE(m) | 傳回字串的bytes數 |
訂閱：
文章 (Atom)