PL/SQL學習筆記-2

Oracle9i PL/SQL程式設計 的學習筆記

第4章到第5章

[@more@]

第四章 PL/SQL中的SQL
4.1 SQL語句
類別 SQL範例
資料操縱語言(DML) SELECT,INSERT,UPDATE,DELETE,EXPLAIN PLAN
資料定義語言(DDL) DROP,CREATE,ALTER,GRANT,REVOKE
事務控制 COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION
會話控制 ALTER SESSION,SET ROLE
系統控制 ALTER SYSTEM

在PL/SQL中可以直接使用的SQL語句只有DML(除了EXPLAIN PLAN)和事務控制語句。特別的是，使用DDL語句是非法的。
PL/SQL採用了早期繫結，就是在編譯的時候繫結變數。
這種設計導致了DDL語句被禁止使用了。因為DDL語句將修改資料庫物件，許可權必須重新進行驗證，對於許可權的驗證需要識別符號是繫結過的，而這已經在編譯時就完成。

用動態SQL可以執行所有的SQL。PL/SQL中的動態SQL有兩種：本地動態SQL和DBMS_SQL包。

4.2 在PL/SQL中使用DML
SELECT...INTO [PL/SQL_record|variable] FROM ... WHERE ...
INSERT INITO ... ...
UPDATE ... [CURRENT OF cursor]
DELETE FROM ... [CURRENT OF cursor]

在PL/SQL中如果變數名字和欄位一樣，例如 department=Department 。PL/SQL的匹配規則是先檢視是否與表中的列相匹配，然後再檢查他們是不是PL/SQL塊中的變數。由於PL/SQL不區分大小寫，所以PL/SQL會把剛才的語句認為是恆等的。
這在SQL語句中是很危險的。
在字串比較的時候，如果一邊是char，一邊是varchar時要注意比較的填充空格問題。
-------------------------------------------------------------------------------------------------------------
SQL> desc test
Name Type Nullable Default Comments
---- -------- -------- ------- --------
ID INTEGER Y
NAME CHAR(20) Y

SQL> select * from test;

ID NAME
--------------------------------------- --------------------
1 a
2 b
3 c

SQL> declare
2 v_name varchar(20);
3 begin
4 v_name:='a';
5 delete from test where name=v_name;
6 end;
7 /

PL/SQL procedure successfully completed

SQL> select * from test;

ID NAME
--------------------------------------- --------------------
1 a
2 b
3 c

SQL> declare
2 v_name test.name%TYPE;
3 begin
4 v_name:='a';
5 delete from test where name=v_name;
6 end;
7 /

PL/SQL procedure successfully completed

SQL> select * from test;

ID NAME
--------------------------------------- --------------------
2 b
3 c

SQL>
--------------------------------------------------------------------------------------------------------------

批繫結：
for v_nums in 1..10000 loop
insert into test values(v_nums,'a');
end loop;
可以改寫成
forall i in 1..10000
insert into ....
將SQL語句打包一次性提交給資料庫，可以減少PL/SQL引擎和SQL引擎之間的上下文切換。

不過有個奇怪的情況，就是forall後面的變數，無法在SQL語句中直接使用。
--------------------------------------------------------------------------------------------------------------
SQL> declare
2 v_num int;
3 begin
4 for v_nums in 1..10000 loop
5 insert into test values(v_nums,'a');
6 end loop；
7 end;
8 /

PL/SQL procedure successfully completed

Executed in 0.766 seconds

SQL> declare
2 v_num int;
3 begin
4 forall v_nums in 1..10000 loop
5 insert into test values(v_nums,'a');
6 end loop;
7 end;
8 /

ORA-06550: 第 5 行, 第 29 列:
PLS-00430: 該上下文中不允許使用 FORALL 迴圈變數 V_NUMS
ORA-06550: 第 5 行, 第 5 列:
PLS-00435: 沒有 BULK In-BIND 的 DML 語句在 FORALL 內不能使用

SQL> declare
2 v_num int;
3 type t_numbers is table of int index by binary_integer;
4 v_nums t_numbers;
5 begin
6 for v_count in 1..10000 loop
7 v_nums(v_count):=v_count;
8 end loop;
9 forall v_count in 1..10000
10 insert into test values(v_nums(v_count),'a');
11 end;
12 /
PL/SQL procedure successfully completed

Executed in 0.047 seconds
--------------------------------------------------------------------------------------------------------------

RETURNING子句
RETURNING expr INTO variable
例如：
INSERT INTO test values(...) RETURNING rowid INTO v_rowid;
UPDATE test Set id=... RETURNING xx INTO v_xx;
DELETE test where name='...' RETURNING id INTO v_id;
用RETURNING來獲取插入的記錄的自動增長的序列號或者rowid，是個不錯的方法。

資料庫連結
CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING sqlnet_string
當資料庫連結用作某個事務的一部分的時，該事務就被稱為
同義詞
CREATE SYNONYM synonym_name FOR reference

4.3 偽列
偽列是僅能從SQL語句中呼叫的其他功能。
CURRVAL和NEXTVAL 這2個偽列和序列一起使用。序列是一種Oracle物件，用來產生唯一的數字。

LEVEL 僅僅用在對錶執行層次樹遍歷的SELECT語句中，使用START WITH 和 CONNECT BY 子句。

ROWID 這個不用我說了吧。

ROWNUM 在查詢中返回當前的行號。

4.4 GRANT、REVOKE和許可權
有兩種型別的許可權-物件許可權和系統許可權。
對於物件許可權：GRANT privilege ON object TO grantee [WITH GRANT OPTION];
對於系統許可權：GRANT privilege TO grantee [WITH GRANT OPTION];
對於物件許可權：REVOKE privilege ON object FROM grantee [CASCADE CONSTRANINTS] [FORCE];
對於系統許可權：REVOKE privilege FROM grantee;

CASCADE CONSTRAINTS
This clause is relevant only if you revoke the REFERENCES privilege or ALL [PRIVILEGES].
It drops any referential integrity constraints that the revokee has defined using the REFERENCES privilege (which might have been granted either explicitly or implicitly through a grant of ALL [PRIVILEGES]).

FORCE
Specify FORCE to revoke the EXECUTE object privilege on user-defined type objects with table or type dependencies. You must use FORCE to revoke the EXECUTE object privilege on user-defined type objects with table dependencies.
If you specify FORCE, then all privileges will be revoked, but all dependent objects are marked INVALID, data in dependent tables becomes inaccessible, and all dependent function-based indexes are marked UNUSABLE. (Regranting the necessary type privilege will revalidate the table.)

對於 CASCADE CONSTRAINTS 和 FORCE 看的有點似懂非懂。英文在上面，以後研究吧。

4.5 事務控制
COMMIT [WORK]
ROLLBACK [WORk]
可選擇的WORK關鍵字可以用來提高閱讀性，沒有實際的意義。
儲存點： SAVEPOINT name;
ROLLBACK [WORK] TO SAVEPOINT name;
注意：
* 從儲存點之後的所有工作都被撤銷，但是儲存點依然有效。
* 從該儲存點之後的SQL所需要的鎖和資源都被釋放了。
* 該事務並沒有結束，因為SQL語句依然掛起。

自治事務：
執行自治事務的唯一方法是在PL/SQL語句塊內部執行。在宣告部分使用編譯指示。
CREATE OR REPLACE PROCEDURE Autonomous AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ...;
COMMIT;
END Autonomous;
自治塊的型別 不是所有的語句塊都可以被標記為自治，只有下面的是合法的：
* 頂層自治塊。
* 本地的、單個的和打包的子程式。
* 物件型別的方法。
* 資料庫觸發器。

自治事務開始於自治塊的第一條SQL語句，結束於COMMIT或者ROLLBACK語句，如果沒有COMMIT或者ROLLBACK，則自治塊會報ORA-6519的錯誤。
--------------------------------------------------------------------------------------------------------------
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 BEGIN
3 INSERT INTO test VALUES(1,'a');
4 END Autonomous;
5 /

Procedure created

Executed in 0.047 seconds

SQL> exec Autonomous;

PL/SQL procedure successfully completed

Executed in 0.015 seconds

SQL>
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 BEGIN
3 INSERT INTO test VALUES(1,'a');
4 COMMIT;
5 END Autonomous;
6 /

Procedure created

Executed in 0.016 seconds

SQL> exec Autonomous;

PL/SQL procedure successfully completed

Executed in 0 seconds

SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO test VALUES(1,'a');
5 COMMIT;
6 END Autonomous;
7 /

Procedure created

Executed in 0.109 seconds
SQL> exec Autonomous;

PL/SQL procedure successfully completed

Executed in 0 seconds

SQL>
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO test VALUES(1,'a');
5 END Autonomous;
6 /

Procedure created

Executed in 0.093 seconds

SQL> exec Autonomous;

begin Autonomous; end;

ORA-06519: 檢測到活動的自治事務處理，已經回退
ORA-06512: 在"IPNET.AUTONOMOUS", line 4
ORA-06512: 在line 1
--------------------------------------------------------------------------------------------------------------

第5章 內建SQL函式

5.1 概述
SQL提供了許多預定義函式，你可以從SQL語句中呼叫他們。

5.2 返回字元值的字元函式
CHAR(x[USING NCHAR_CS]) 返回資料庫字符集中與x值相等的字元。
CONCAT(string1,string2) 返回連線string1和string2的字串，等同用 ||
INITCAP(string) 返回首字母大寫，其他改為小寫
LOWER(string) 以小寫返回string
LPAD(string1,x[,string2]) 左填充
LTRIM(string1,string2) 從string1左邊開始，刪除出現在string2中的字元，直到碰到第一個在string2種找不到的字元。
REPLACE(string,search_str[,replace_str]) 替換字元
RPAD(string1,x[,string2]) 同LPAD，只不過是從右邊開始
RTIRM(string1,string2) 同LTRIM，只不過是從右邊開始
SOUNDEX(string) 返回string的語音表示形式。
SUBSTR(string,a[,b]) 返回string的一部分，a和b是以字元為單位。
SUBSTRB(string,a[,b]) 返回string的一部分，a和b是以位元組為單位。
SUBSTRC(string,a[,b]) 返回string的一部分，a和b是以Unicode完全字元為單位。
SUBSTR2(string,a[,b]) 返回string的一部分，a和b是以UCS2程式碼點為單位。
SUBSTR4(string,a[,b]) 返回string的一部分，a和b是以UCS4程式碼點為單位。
TRANSLATE(string.from_str,to_str) 返回將所有出現的from_str中每個字元替代為to_str中相應字元後的string。
TRIM([{{LEADING|TRAILING|BOTH} [trim_char|trim_char]} FROM] string) 返回刪除了在開頭、結尾或開頭結尾都出現的trim_char之後的string。
UPPER(string) 以大寫形式返回string。

仔細看下列的例子，注意replace 和 translate 的區別。
--------------------------------------------------------------------------------------
SQL> select replace('abccba','abc','123') from dual;

REPLACE('ABCCBA','ABC','123')
-----------------------------
123cba

SQL> select translate('abccba','abc','123') from dual;

TRANSLATE('ABCCBA','ABC','123'
------------------------------
123321

--------------------------------------------------------------------------------------


5.3 返回數字值得字元函式
ASCII(string) 返回string首字母的十進位制表示形式。
INSTR(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以字元為單位.
INSTRB(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以位元組為單位.
INSTRC(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以Unicode完全字元為單位.
INSTR2(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以UCS2程式碼點為單位.
INSTR4(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以UCS4程式碼點為單位.
LENGTH(string1) 返回以字元為單位的長度.
LENGTHB(string1) 返回以位元組為單位的長度.
LENGTHC(string1) 返回以Unicode完全字元為單位的長度.
LENGTH2(string1) 返回以UCS2程式碼點為單位的長度.
LENGTH4(string1) 返回以UCS4程式碼點為單位的長度.

5.4 NLS函式
從來沒用過。略

5.5 數字函式

ACOS(x) x的反餘旋值。
ASIN(x) x的反正旋值
ATAN(x) x的反正切值
ATAN2(x,y) x和y的反正切值
BITAND(x,y) 返回x和y的與結果
COS(x) x的餘旋
COSH(x) x的雙曲餘旋
EXP(x) x的次幕
LN(x) x的自然對數
LOG(x,y) 以x為底的y的對數
SIN(x) x的正旋
SINH(x) x的雙曲正旋
SQRT(x) x的平方根
TAN(x) x的正切
TANH(x) x的雙曲正切
SIGN(x) 如果x<0 返回-1 如果x=0 返回 0 如果x>0 返回1
POWER(x,y) x的y次幕
ABS(x) x的絕對值
CEIL(x) 返回大於或等於x的最大整數
FLOOT(x) 等於或者小於x的最大整數
MOD(x,y) x除以y的餘數
ROUND(x[,y]) 四捨五入到小數點右邊y位的x值
TRUNC(x[,y]) 返回擷取到y為小數的x值
WIDTH_BUCKET(x,min,max,num_buckets)
範圍min到max被分為num_buckets節，每節有相同的大小。返回x所在的那一節。如果x小於min,將返回0。如果x大於或等於max,則返回 num_buckets+1。


5.6 時間和日期函式
ADD_MONTH(d,x) 加上x月後的日期d的值。
CURRENT_DATE 以date型別返回會話時區當前的日期
CURRENT_TIMESTAMP[(precision)] 以TIMESTAMP WITH TIMEZONE 型別返回會話時區當前的日期。
DBTIMEZONE 返回資料庫的時區
EXTRACE({YEAR,MONTH,DAY,HOUR,DATE欄位僅在oracle8i中使用，其餘用於MINUTE,SECOND,TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_REGION,TIMEZONE_ABBR} FROM datetime_or_interval)
返回從datetime_or_interval中選擇的日期。
LAST_DAY(d) 返回d所在月份的最後一天
LOCALTIMESTAMP[(precision)] 以TIMESTAMP型別返回會話時區的當前日期。
MONTHS_BETWEEN(date1,date2) 返回date1和date2之間月的數目。
NEW_TIME(d,zone1,zone2) 當時區zone1中的日期和時間是d時，返回時區zone2中的日期和時間。
NEXT_DAY(d,string) 返回在日期d之後由string限定的第一天。
ROUND(d[,format]) 將日期d按照由format指定的格式進行四捨五入
SESSIONTIMEZONE 返回當前會話的時區。
SYS_EXTRACT_UTC(datetime) 從提供的datetime中以UTC返回時間。
SYSDATE 以Date型別返回當前日期和時間。
SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE型別返回當前日期和時間。
TRUNC(d[,format]) 返回截尾到由format指定的單位的日期上。
TZ_OFF(timezone) 以字元字串返回提供的timezone和UTC之間的偏移量。

--------------------------------------------------------------------------------------
SQL> select extract(year from sysdate),NUMTODSINTERVAL(99,'MINUTE'),extract(minute from NUMTODSINTERVAL(99,'MINUTE')) from dual;

EXTRACT(YEARFROMSYSDATE) NUMTODSINTERVAL(99,'MINUTE') EXTRACT(MINUTEFROMNUMTODSINTER
------------------------ --------------------------------------- ------------------------------
2007 +000000000 01:39:00.000000000 39

SQL> select sysdate,to_char(sysdate,'fmDay'),next_day(sysdate,'星期四') from dual;

SYSDATE TO_CHAR(SYSDATE,'FMDAY') NEXT_DAY(SYSDATE,'星期四')
----------- ------------------------ --------------------------
2007-6-13 2 星期三 2007-6-14 22:14:33

--------------------------------------------------------------------------------------

5.7 轉換函式
ASCIISTR(string) 返回只包含有效的SQL字元和斜線的字串。
BIN_TO_NUM(num[,num]...) 將一個位向量轉換為相當的數字。
CHARTOROWID(string) 將包含外部格式rowid的char或者varchar轉換成內部格式
COMPOSE(string) 以相同字符集中完全規格化Unicode形式返回string。
DECOMPOSE(string) 返回一個Unicode字串，它是string的規範化分解。
FROM_TZ(timestamp,ktimezone) 返回一個TIMESTAMP WITH TIMEZONE型別值
HEXTORAW(string) 將包含十六進位制的CHAR轉換為一個RAW數值。
NUMTODSINTERVAL(x,unit) 將x轉換為interval day to second值。unit是 day hour monute second
NUMTOYMINTERVAL(x,unit) 將x轉換為interval year to month 值。unit是 year month
REFTOHEX(refvalue) 返回一個REF refvalue的十六進位制表示。
RAWTOHEX(rawvalue) 將RAW數值轉換為一個包含十六進位制的CHAR值。
RAWTONHEX(rawvalue) 將RAW數值轉換為一個包含十六進位制的NCHAR值。
ROWIDTOCHAR(ROWID) 將一個ROWID數值轉換為VARCHAR2資料型別。
ROWIDTONCHAR(ROWID) 將一個ROWID數值轉換為NVARCHAR2資料型別。
TO_CHAR
TO_CLOB(string) 將string轉化成CLOB
TO_DATE
TO_DSINTERVAL(string[,nlsparams]) 將string 轉換成 interval day to second型別
TO_LOB(long_column) 將long_column轉化成CLOB
TO_MULTI_BYTE(string) 將所有的單位元組替換成等價的雙位元組
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE(string) 將所有的雙位元組替換成等價的單位元組
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL(string) 將string 轉換成 interval year to month 型別

---------------------------------------------------------------------------------
SQL> select BIN_TO_NUM(1,1,0,0,1) from dual;

BIN_TO_NUM(1,1,0,0,1)
---------------------
25
---------------------------------------------------------------------------------

5.8 聚合和分析函式
AVG([distinct|all]col) 求平均值
CORR(x1,x2) 略
COUNT(*|[distinct|all]col) 返回查詢中的數目
GROUP_ID() 返回一個唯一數字用於在GROUP BY字句中辨別組
GROUPING
GROUPING_ID
MAX([distinct|all]col)
MIN([distinct|all]col)
RANK
SUM([distinct|all]col)
下面的都不懂：
COVAR_POP(x1,x2) 返回表示式x1和x2組成的集合的人口協方差
COVAR_SAMP(x1,x2) 返回表示式x1和x2組成的集合的相同協方差
CUME_DIST 返回一組值中一個值的累積分佈
DENSE_RANK 返回有序分組的行中以行的一
PERCENTILE_CONT
PERCENTILE_DISC
PERVENT_RANK
REGR
STDDEV
STDDEV_POP(col)
STDDEV_SAMP(col)
VAR_POP(x)
VAR_SAMP(x)
VARIANCE([distinct|all]col)
注意count(*)和count(col)的區別
---------------------------------------------------------------------------------
SQL> select * from test2;

ID
---------------------------------------
0
1
2
3
4
5
6
7
8

10 rows selected

SQL> select count(*) from test2;

COUNT(*)
----------
10

SQL> select count(id) from test2;

COUNT(ID)
----------
9

SQL>
---------------------------------------------------------------------------------

5.9 其他函式
BFILENAME(directory,file_name) 返回作業系統中與物理檔案file_name相關的BFILE位置指示符。
COALESCE(expr,...[exprn]) 返回引數列表中第一個非NULL的expr。
DECODE(base_expr,comare1,value1,...)
DUMP
EMPTY_BLOB/EMPTY_CLOB 返回一個空的LOB指示符
EXISTSNODE(XMLType_instace,Xpath_string)
EXTRACT(XMLType_instace,Xpath_string)
GREATEST(expr1[,expr2]) 返回其引數中最大的表示式
LEAST(expr1[,expr2]) 返回其引數中最小的表示式
NULLIF(a,b) 如果a等於b，返回NULL否則返回b
NVL(expr1,expr2) 如果expr1是null 返回expr2 否則返回expr1
NVL2(expr1,expr2,expr3) 如果expr1是null 返回expr2 否則返回expr3
SYS_CONNECT_BY_PATH()
SYS_CONTEXT
SYS_DBURIGEN
SYS_GUID 以16位RAW型別返回一個全域性唯一的識別符號
SYS_TYPEID(object_type) 返回指定物件的型別ID
SYS_XMLAGG
SYS_XMLGEN
TREAT
UID 當前資料庫使用者的整數表識
USER 當前資料庫使用者名稱
USERENV
VSIZE(x) 返回x內部表示的位元組數