文檔說明：該文檔編輯環境為PLSQL DEVELOPER sql視窗，故文檔優點在于，可以直接拷貝整個該文檔（标題除外）到sql視窗即可直接運作，友善進行測試和修改。頁數較多，但以代碼為主，文字較簡潔。考慮文檔實用性未對文檔做過多的格式編排。
最佳觀看效果SQL視窗！
目錄
專題一：僞列（ROWID，ROWNUM）...2
專題二：索引（單值，多值）...5
專題三：分區表...9
專題四：大對象...18
專題五：集合（聯合數組、嵌套表、Varrays）...27
專題六：動态sql（主要是本地動态sql，帶參數）...35
題目一：基礎複習...40
題目二：基礎複習...44
實訓總結...47
專題一：僞列（ROWID，ROWNUM）
--2013-06-29
--僞列（ROWID，ROWNUM）-----------------------------------------------------------------------------------------------
--感性認識，輸出這兩個僞列，看其形式
selectrowid,rownumfrom scott.emp;
selectrowid,rownumfrom scott.empwhererownumbetween1and5;
select *from emp;
-----------------------------------------ROWNUM----------------------------------------
--rownum主要實作分頁顯示
--基本用法見下
--1.rownum 對于某特定值得查詢
--rownum=1可以作為限定條件，但是rownum=n(n>1)不行
--因為rownum都是從1開始，但是1以上的自然數在rownum做等于判斷是時認為都是false條件，
--是以無法查到rownum = n（n>1的自然數）。
--成功
select *from emp whererownum =1;
--不成功
select *from emp whererownum =3;
--成功，顯示前兩條資料。
select * from empwhererownum !=3;
--2.rownum 對于大于某值得查詢
--如果想找到從第二行記錄以後的記錄，當使用rownum>2是查不出記錄的，
--原因是由于rownum是一個總是從1開始的僞列，Oracle認為rownum> n(n>1的自然數)這種條件依舊不成立，是以查不到記錄
--falied 原因同上
select *from emp whererownum >3;
--解決方法：使用子查詢
--成功
select *from (selectrownumno, empno, enamefrom emp ) whereno >2;
--3.rownum 對于小于某值得查詢
--如果想找到第三條記錄以前的記錄，當使用rownum<3是能得到兩條記錄的。顯然rownum對于rownum<n（(n>1的自然數）
--的條件認為是成立的，是以可以找到記錄
--falied 原因同上
select *from emp whererownum <3;
--select * from emp where rownum != 3;
--解決方法：使用子查詢
select * from (selectrownumno, empno, ename from emp )whereno >2;
--rownum的編号，是按照插入資料庫的順序編号的
--下面這個例子可以看出
selectrownum, salfrom emp orderby empnodesc;
----------------------------------ROWNUM-------------------------------------------------
-------------------------------------ROWID--------------------------------------------------
--概念：
--rowid就是唯一标志記錄實體位置的一個id，rowid由file#+block#+row#組成，--占用6個bytes的空間，
--10 bit 的 file#，22bit 的 block#，16 bit 的 row#。
--分類：
--實體ROWID和邏輯ROWID。
--rowid的使用是記錄的詳細位址，用于快速定位資料
--格式：
--資料對象編号，檔案編号，塊編号，行編号（OOOOOO FFF RRR0)占10bytes空間
--1.快速删除重複的行
createtable temp_empasselectdistinct *from emp;
truncatetable emp;
insertinto empselect * from temp_emp;
--查找大量重複記錄
select empnofrom emp groupby empnohavingcount(*)>1;
select *from emp whererowidin(selectMin(rowid)from emp groupby empno);
--查找少量重複資料
select *from emp a whererowid<>(selectmax(rowid)from emp where empno= a.empno);
--删除重複記錄
delete empwhere empno in (select empnofrom emp groupby empnohavingcount(*)>1)
androwidnotin(selectmin(rowid)from emp groupby empnohavingcount(*)>1);
--根據ROWID來實作分頁
--解釋如下
--1.将表按empno降序排列
--2.檢索出前10條
--3.檢索出8到10這幾條的rowid
--4.檢索出rowid在上一步的結果中的資料并且按照cid降序排列
select *from emp whererowidin(select rid from (selectrownum rn,rid
from(selectrowid rid,deptnofrom emp orderby ciddesc)
whererownum<10)where rn>8)orderby deptnodesc;
---------------------------------------ROWID--------------------------------------------
專題二：索引（單值，多值）
--2013-06-30
--索引（單列、多列）
--使用到的查詢語句
--1.檢視一下sql執行計劃來檢視索引有沒有起作用
select *fromglobal_name;
--2.檢視索引個數和類别
select *from user_indexeswhere table_name='emp';
--3.檢視索引被索引的字段
select *from user_ind_columnswhere index_name=upper('&index_name');
--作用概述
--通過每個行的ROWID，索引Oracle提供了通路單行資料的能力。ROWID其實就是直接指向單獨行的線路圖。
--如果想檢查重複值或是其他對ROWID本身的引用，可以在任何表中使用和指定rowid列。
--關于索引的一些注意事項
--關于限制索引
--在SQL中使用一些限制條件導緻是以沒有被使用的情況。以下下面讨論一些常見的問題：
--1.使用不等于操作符（<>、!=）
--下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執行一次全表掃描。
selectid,namefrom testindexwhereid <>99999;
--解決方案，在采用基于規則的優化器而不是基于代價的優化器（更智能）時，将會使用索引。
--特别注意：通過把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。
selectid,namefrom testindex
wherecust_rating <9999or cust_rating >9999;
--2 使用IS NULL或IS NOT NULL帶來的問題
--使用IS NULL或IS NOT NULL同樣會限制索引的使用
--3 使用函數
--如果不使用基于函數的索引，那麼在SQL語句的WHERE子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。
--下面的查詢不會使用索引（隻要它不是基于函數的索引）
selectempno,ename,deptnofromemp wheretrunc(hiredate)='01-MAY-81';
--解決方案如下，這樣就可以通過索引進行查找。
select empno,ename,deptnofrom emp wherehiredate<(to_date('01-MAY-81')+0.9999);
--通過sql執行計劃，檢視索引是否作用的一些設定
altersessionset statistics_level='ALL';
select *fromtable(dbms_xplan.display);
setautotracetraceexplain;
select *from emp;
--建立表
createtable testindex(
idinteger,
namevarchar2(20)
);
--向表中插入資料100萬條，耗時36.7s
declare inumber;
begin
for i in1 ..1000000loop
insertinto testindexvalues (i,to_char(i));
endloop;
end;
select *from testindex;
selectcount(*)from testindex;
--建立索引
createindex idindexon testindex(id);--耗時2.969s
selectobject_name,object_typefrom user_objects;
--檢視索引資訊 建立成功
select *from user_indexeswhere table_name='testindex';
select *from user_ind_columnswhere index_name=upper('&index_name');
select *from testindex whereid =999999; --響應時間0.078s
dropindex idindex; --删除索引
select *from testindex whereid =999999; --響應時間0.094s
--重複執行以上查詢，可以看出索引節省時間
-------------------------sqlplus做----------------------------
建立表：
SQL> createtable dex (idint,sexchar(1),namechar(10));
Table created.
向表中插入10000條資料
SQL> begin
for iin1..10000
loop
insertinto dexvalues(i,'M','chongshi');
endloop;
commit;
end;
/
PL/SQLprocedure successfullycompleted.
檢視表記錄
select *from dex;
建立索引：
SQL> createindex dex_idx1on dex(id);
Index created.
檢視建立的表與索引
SQL> selectobject_name,object_typefrom user_objects;
OBJECT_NAME OBJECT_TYPE
---------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
SQL> createindex dex_index2on dex(sex,name);
Index created.
SQL> select object_name,object_typefrom user_objects;
OBJECT_NAME OBJECT_TYPE
---------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEX
-------------------------sqlplus做----------------------------
專題三：分區表
--2013-07-6
--分區表
--概念及作用
--Oracle提供了分區技術以支援VLDB(Very Large DataBase)。分區表通過對分區列的判斷，把分區列不同的記錄，
------放到不同的分區中。分區完全對應用透明。Oracle的分區表可以包括多個分區，每個分區都是一個獨立的段
------（SEGMENT），可以存放到不同的表空間中。查詢時可以通過查詢表來通路各個分區中的資料，也可以通過
------在查詢時直接指定分區的方法來進行查詢。
--何時分區
---When to Partition a Table什麼時候需要分區表，官網的2個建議如下：
------1）Tables greater than 2GB should always be considered forpartitioning.（即：表的大小超過2GB）
------2）Tables containing historical data, in which new data isadded into the newest partition.
------A typical example is a historical table where onlythe current month's data is updatable
------and the other 11 months are read only.（即：對于新舊資料分離時可采用。典型例子隻對月前資料更新，可采用分區表）
--優點：
------（1）由于将資料分散到各個分區中，減少了資料損壞的可能性；
------（2）可以對單獨的分區進行備份和恢複；
------（3）可以将分區映射到不同的實體磁盤上，來分散IO；
------（4）提高可管理性、可用性和性能。
--類型：
------（1）範圍分區（range）；
------（2）哈希分區（hash）；
------（3）清單分區（list）；
------（4）範圍－哈希複合分區（range-hash）；
------（5）範圍－清單複合分區（range-list）
--一.Range分區：
--Range分區是應用範圍比較廣的表分區方式，它是以列的值的範圍來做為分區的劃分條件，将記錄存放到列值所
------在的range分區中。
------如按照時間劃分，2010年1月的資料放到a分區，2月的資料放到b分區，在建立的時候，需要指定基于的列，
------以及分區的範圍值。在按時間分區時，如果某些記錄暫無法預測範圍，可以建立
------maxvalue分區，所有不在指定範圍内的記錄都會被存儲到maxvalue所在分區中。
--如：
createtable test1 (idnumber,timedate)partitionbyrange (time)
(
partition p1valueslessthan (to_date('2010-10-1','yyyy-mm-dd')),
partition p2valueslessthan (to_date('2010-11-1','yyyy-mm-dd')),
partition p3valueslessthan (to_date('2010-12-1','yyyy-mm-dd')),
partition p4valueslessthan (maxvalue)
)
selectsysdatefrom dual;
insertinto test1values(2, to_date('2010-5-30','yyyy-mm-dd'));
insertinto test1values(3, to_date('2012-5-30','yyyy-mm-dd'));
insertinto test1values(4, to_date('2013-5-30','yyyy-mm-dd'));
--以下來自sqlplus
SQL> select * from test1wheretime > to_date('2010-12-1','yyyy-mm-dd');
執行計劃：會直接查詢第四部分，進而加快了通路速度。其它操作與此相仿：insert update等
----------------------------------------------------------
Planhashvalue:3115573686
--------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 7 | 154 | 3 (0)|00:00:01 |
| |
| 1 | PARTITIONRANGESINGLE| | 7 | 154 | 3 (0)|00:00:01 |
4 | 4 |
|* 2 | TABLEACCESSFULL | TEST1 | 7 | 154 | 3 (0)|00:00:01 |
4 | 4 |
--以上來自sqlplus--------------------------------------------------------------
--二.Hash分區：
------對于那些無法有效劃分範圍的表，可以使用hash分區，這樣對于提高性能還是會有一定的幫助。hash分區會将
------表中的資料平均配置設定到你指定的幾個分區中，列所在分區是依據分區列的hash值自動配置設定，是以你并不能控制
------也不知道哪條記錄會被放到哪個分區中，hash分區也可以支援多個依賴列。
--如：--在這裡，如果需要可以為每個分區指定表空間。
createtable test2
(
transaction_id numberprimarykey,
item_id number(8)notnull
)
partitionbyhash(transaction_id)
(
partition part_01,
partition part_02,
partition part_03
);
select *from test2;
insertinto test2values(1,2);
insertinto test2values(2,7);
insertinto test2values(3,6);
insertinto test2values(4,999);
--三.List分區：
------List分區也需要指定列的值，其分區值必須明确指定，該分區列隻能有一個，不能像range或者hash分區那樣同時
------指定多個列做為分區依賴列，但它的單個分區對應值可以是多個。在分區時必須确定分區列可能存在的值，一旦
------插入的列值不在分區範圍内，則插入/更新就會失敗，是以通常建議使用list分區時，要建立一個default分區存
------儲那些不在指定範圍内的記錄，類似range分區中的maxvalue分區。
--在根據某字段，如城市代碼分區時，可以指定default，把非分區規則的資料，全部放到這個default分區。
--如：
createtable test3
(
id varchar2(15byte) notnull,
code varchar2(4byte)
)
partitionbylist (code)
(
partition t_list025values ('025'),
partition t_list372values ('372') ,
partition t_list510values ('510'),
partition p_othervalues (default)
)
select *from test3;
--四.組合分區：
------如果某表按照某列分區之後，仍然較大，或者是一些其它的需求，還可以通過分區内再建子分區的方式将分區再分區，
------即組合分區的方式。組合分區呢在10g中有兩種：range-hash，range-list。注意順序，根分區隻能是range分區，
------子分區可以是hash分區或list分區。
--如：range--hash
createtable test4
(
transaction_id numberprimarykey,
transaction_date date
)
partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)
subpartitions3--這裡用來指定分區從屬的表空間 store in(tablespace01,tablespace02,tablespace03)
(
partition part_01valueslessthan(to_date('2009-01-01','yyyy-mm-dd')),
partition part_02valueslessthan(to_date('2010-01-01','yyyy-mm-dd')),
partition part_03valueslessthan(maxvalue)
);
--如：range--hash
createtable emp_sub_template (
deptno number,
empname varchar(32),
grade number
)
partitionbyrange(deptno)subpartitionbyhash(empname)
subpartitiontemplate
(subpartition a1,--這裡用于指定表空間格式：tablespace ts2,
subpartition a2,
subpartition a3,
subpartition a4
)
(partition p1valueslessthan (1000),
partition p2valueslessthan (2000),
partition p3valueslessthan (maxvalue)
);
--如：range--list
createtablequarterly_regional_sales
(deptno number, item_novarchar2(20),
txn_date date, txn_amountnumber, state varchar2(2))
tablespace ts4
partitionbyrange (txn_date)
subpartitionbylist (state)
(partition q1_1999valueslessthan (to_date('1-apr-1999','dd-mon-yyyy'))
(subpartition q1_1999_northwestvalues ('or','wa'),
subpartition q1_1999_southwestvalues ('az','ut', 'nm'),
subpartition q1_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q1_1999_southeastvalues ('fl','ga'),
subpartition q1_1999_northcentralvalues ('sd','wi'),
subpartition q1_1999_southcentralvalues ('ok','tx')
),
partition q2_1999valueslessthan ( to_date('1-jul-1999','dd-mon-yyyy'))
(subpartition q2_1999_northwestvalues ('or','wa'),
subpartition q2_1999_southwestvalues ('az','ut', 'nm'),
subpartition q2_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q2_1999_southeastvalues ('fl','ga'),
subpartition q2_1999_northcentralvalues ('sd','wi'),
subpartition q2_1999_southcentralvalues ('ok','tx')
),
partition q3_1999valueslessthan (to_date('1-oct-1999','dd-mon-yyyy'))
(subpartition q3_1999_northwestvalues ('or','wa'),
subpartition q3_1999_southwestvalues ('az','ut', 'nm'),
subpartition q3_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q3_1999_southeastvalues ('fl','ga'),
subpartition q3_1999_northcentralvalues ('sd','wi'),
subpartition q3_1999_southcentralvalues ('ok','tx')
),
partition q4_1999valueslessthan ( to_date('1-jan-2000','dd-mon-yyyy'))
(subpartition q4_1999_northwestvalues ('or','wa'),
subpartition q4_1999_southwestvalues ('az','ut', 'nm'),
subpartition q4_1999_northeastvalues ('ny','vm', 'nj'),
subpartition q4_1999_southeastvalues ('fl','ga'),
subpartition q4_1999_northcentralvalues ('sd','wi'),
subpartition q4_1999_southcentralvalues ('ok','tx')
)
);
--四.分區表的索引
------分區索引分為本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理,而全局索引比較快。
------Local索引肯定是分區索引，Global索引可以選擇是否分區，如果分區，隻能是有字首的分區索引。
------分區索引分2類：有字首(prefix)的分區索引和無字首(nonprefix)的分區索引:
--4.1 Local 本地索引一定是分區索引
--對于local索引，當表的分區發生變化時，索引的維護由Oracle自動進行。
--注意事項：
--（1）局部索引一定是分區索引，分區鍵等同于表的分區鍵。
--（2）字首和非字首索引都可以支援索引分區消除，前提是查詢的條件中包含索引分區鍵。
--（3）局部索引隻支援分區内的唯一性，無法支援表上的唯一性，是以如果要用局部索引去給表做唯一性限制，則限制中必須要包括分區鍵列。
--（4）局部分區索引是對單個分區的，每個分區索引隻指向一個表分區；全局索引則不然，一個分區索引能指向n個表分區，同時，一個表分區，也可能指向n個索引分區，對分區表中的某個分區做truncate或者move，shrink等，可能會影響到n個全局索引分區，正因為這點，局部分區索引具有更高的可用性。
--（5）位圖索引必須是局部分區索引。
--（6）局部索引多應用于資料倉庫環境中。
--（7） B樹索引和位圖索引都可以分區，但是HASH索引不可以被分區。
--如：
createindex index_local_idon test1(id)local;
--和下面SQL效果相同，因為local索引就是分區索引：
createindex index_local_idon n test1(id)
local (
partition t_list556
partition p_other
)
createindex index_local_id2on test1(time)local;
selectindex_name,table_name,partitioning_type,locality,ALIGNMENT
from user_part_indexeswhere table_name='test1'
--4.2 Global索引
------對于global索引，可以選擇是否分區，而且索引的分區可以不與表分區相對應。全局分區索引隻能是B樹索引，
--到目前為止(10gR2)，oracle隻支援有字首的全局索引。
--注意事項：
--（1）全局索引可以分區，也可以是不分區索引，全局索引必須是字首索引，即全局索引的索引列必須是以索引分區鍵作為其前幾列。
--（2）全局索引可以依附于分區表；也可以依附于非分區表。
--（3）全局分區索引的索引條目可能指向若幹個分區，是以，對于全局分區索引，即使隻截斷一個分區中的資料，都需要rebulid若幹個分區甚至是整個索引。
--（4）全局索引多應用于oltp系統中。
--（5）全局分區索引隻按範圍或者散列分區，hash分區是10g以後才支援。
--（6） oracle9i以後對分區表做move或者truncate的時可以用update global indexes語句來同步更新全局分區索引，用消耗一定資源來換取高度的可用性。
--（7）表用a列作分區，索引用b做局部分區索引，若where條件中用b來查詢，那麼oracle會掃描所有的表和索引的分區，成本會比分區更高，此時可以考慮用b做全局分區索引。
--Oracle隻支援2中類型的全局分區索引：
--range partitioned和 Hash Partitioned.
--示例1全局索引，全局索引對所有分區類型都支援：
createindex ix_custaddr_global_idon custaddr(id)global;
--示例2：全局分區索引，隻支援Range分區和Hash 分區：
--（1）建立2個測試分區表：
createtable pdba (idnumber,timedate)partitionbyrange (time)
(
partition p1valueslessthan (to_date('2010-10-1','yyyy-mm-dd')),
partition p2valueslessthan (to_date('2010-11-1','yyyy-mm-dd')),
partition p3valueslessthan (to_date('2010-12-1','yyyy-mm-dd')),
partition p4valueslessthan (maxvalue)
);
createtable Thash
(
idnumberprimarykey,
item_id number(8)notnull
)
partitionbyhash(id)
(
partition part_01,
partition part_02,
partition part_03
);
--（2）建立分區索引
--示例2：全局分區索引
createindex i_id_globalon PDBA(id)global
partitionbyrange(id)
(partition p1valueslessthan (200),
partition p2valueslessthan (maxvalue)
);
--索引已建立。
createindex i_time_globalon PDBA(time)global
partitionbyrange(time)
(partition p1valueslessthan (TO_DATE('2010-12-1','YYYY-MM-DD')),
partition p2valueslessthan (maxvalue)
);
--索引已建立。
--有字首的分區索引
selectindex_name,table_name,partitioning_type,locality,ALIGNMENT
fromuser_part_indexeswheretable_name='PDBA';
index_name table_name
partition localialignment
------------------------------ ---------- --------------- ------------
i_id_global pdba range global prefixed
i_time_global pdba range global prefixed
專題四：大對象
--2013-07-06
--大對象
--概念及其分類
--在oracle中，有4個大對象（lobs）類型可用，分别是blob,clob,bfile,nclob。
-- 下面是對lob資料類型的簡單介紹。
-- l blob:二進制lob，為二進制資料，最長可達4GB，存貯在資料庫中。
-- 2 clob:字元lob,字元資料,最長可以達到4GB,存貯在資料庫中。
-- 3 bfile:二進制檔案;存貯在資料庫之外的隻讀型二進制資料，最大長度由作業系統限制。
-- 4 nclob：支援對位元組字元集合(nultibyte characterset)的一個clob列。
、
--oracle中可以用多種方法來檢索或操作lob資料。通常的處理方法是通過dbms_lob包。
--下以該方法示範
--一、在oracle開發環境中我們可以用dbms_lob包來處理！dbms_lob包功能強大，簡單應用。既可以用來讀取
--内部的lob對象，也可以用來處理bfile對象。但處理兩者之間，還有一點差别。處理内部lob對象（blob,
--clob）時，可以進行讀和寫，但處理外部lob對象bfile時，隻能進行讀操作，寫的操作可以用pl/sql處理。
--另外用sql也可以處理lob，但要注意sql僅可以處理整個lob，不能操作lob的資料片。
--對于pl/sql，下面介紹一種技巧，用動态的pl/sql語句處理clob對象來傳替表名！
--在dbms_lob包中内建了read(),append,write()，erase()，copy(),getlength()，substr()等函數，下面加以示範
--可以很友善地操作lob對象。
-- 動态PL/SQL,對CLOB字段操作可傳遞表名table_name，表的唯一标志字段名field_id，clob字段名field_name記錄号v_id，
-- 開始處理字元的位置v_pos，傳入的字元串變量v_clob
-- 修改CLOB的PL/SQL過程：updateclob
create or replace procedure updateclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number,
v_clob in varchar2)
is
lobloc clob;
c_clob varchar2(32767);
amt binary_integer;
pos binary_integer;
query_str varchar2(1000);
begin
pos:=v_pos*32766+1;
amt := length(v_clob);
c_clob:=v_clob;
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id for update ';
--initialize buffer with data to be inserted or updated
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
--from pos position, write 32766 varchar2 into lobloc
dbms_lob.write(lobloc, amt, pos, c_clob);
commit;
exception
when others then
rollback;
end;
--l 用法說明：
--在插入或修改以前，先把其它字段插入或修改，CLOB字段設定為空empty_clob()，
--然後調用以上的過程插入大于2048到32766個字元。
--如果需要插入大于32767個字元，編一個循環即可解決問題。
--查詢CLOB的PL/SQL函數：getclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
-- l 用法說明：
-- 用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual;
-- 可以從CLOB字段中取2000個字元到partstr中，
-- 編一個循環可以把partstr組合成dbms_lob.getlength(field_name)長度的目标字元串。
--二，大對象資料的錄入
--2.1，聲明LOB類型列
--建立所需表格
droptable tLob
CreateTable tLob (
noNumber(4),
nameVarChar2(10),
resumeCLob,
photo BLob,
recordBFile
)
Lob (resume,photo)StoreAs (
--Tablespace bigbig --指定存儲的表空間這裡使用預設表空間
Chunk6k --指定資料塊大小
DisableStorageInRow
);
INSERTINTO tlob
VALUES (2,
'BIG',
'CLOB大對象列',
EMPTY_BLOB (),
BFILENAME ('D:','Winter.jpg')
);
--2，操作大對象資料的過程
-- 操作會改變資料庫中原有資料，需要加上Updata鎖鎖上指定資料列，修改完後送出事務。
--①：DBMS_LOB.Write()：
--将指定數量的資料寫入LOB的過程。
-- DBMS_LOB.Write(被寫入LOB,寫入長度(指寫入LOB資料)，寫入起始位置(指被寫入LOB)，寫入LOB資料);
DECLARE
varC CLOB;
vWStr VARCHAR2 (1000);
vStrt NUMBER (4);
LN NUMBER (4);
BEGIN
vWStr := 'CLOB11';
LN := LENGTH (vWStr);
vStrt := 5;
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.Write (varC,
LN,
vStrt,
vWStr);
DBMS_OUTPUT.put_line ('改寫結果為: ' ||varC);
COMMIT;
END;
--②：DBMS_LOB.Append()：
--将指定的LOB資料追加到指定的LOB資料後的過程。
-- DBMS_LOB.Append(LOB資料，LOB資料)；
DECLARE
varC CLOB;
vAStr VARCHAR2 (1000);
BEGIN
vAStr := '，這是大對象列';
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.Append (varC, vAStr);
COMMIT;
DBMS_OUTPUT.put_line ('追加結果為: ' ||varC);
END;
--③：DBMS_LOB.Erase()：
--删除LOB資料中指定位置的部分資料的過程；
-- DBMS_LOB.Erase(LOB資料，指定删除長度,開始删除位置)；
DECLARE
varC CLOB;
LN NUMBER (4);
strt NUMBER (4);
BEGIN
LN := 1;
strt := 5;
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.ERASE (varC, LN, strt);
COMMIT;
DBMS_OUTPUT.put_line ('擦除結果為: ' ||varC);
END;
-- ④：DBMS_LOB.Trim()：
--截斷LOB資料中從第一位置開始指定長度的部分資料的過程；
-- DBMS_LOB.Trim(LOB資料，截斷長度)；
DECLARE
varC CLOB;
LN NUMBER (4);
BEGIN
LN := 4;
SELECT resume
INTO varC
FROM tlob
WHERE no =2
FORUPDATE ;
DBMS_LOB.TRIM (varC, LN);
COMMIT;
DBMS_OUTPUT.put_line ('截斷結果為: ' ||varC);
END;
-- ⑤：DBMS_LOB.Copy()：
--從指定位置開始将源LOB複制到目标LOB；
-- DBMS_LOB.Copy(源LOB，目标LOB，複制源LOB長度，複制到目标LOB開始位置，複制源LOB開始位置)
DECLARE
vDEST_LOB CLOB;
vSRC_LOB CLOB;
AMOUNT NUMBER;
DEST_OFFSET NUMBER;
SRC_OFFSET NUMBER;
BEGIN
SELECT resume
INTO vDEST_LOB
FROM tlob
WHERE no =2
FORUPDATE ;
SELECT resume
INTO vSRC_LOB
FROM tlob
WHERE no =2;
AMOUNT := DBMS_LOB.GetLength(vSRC_LOB);
DEST_OFFSET := DBMS_LOB.GetLength(vDEST_LOB) +1;
SRC_OFFSET := 1;
DBMS_LOB.COPY (vDEST_LOB,
vSRC_LOB,
AMOUNT,
DEST_OFFSET,
SRC_OFFSET);
DBMS_OUTPUT.put_line ('拷貝結果為: ' ||vDEST_LOB);
END;
--三，圖檔的存儲或二進制檔案的存儲
--操作步驟
-- 1，先插入普通資料，遇到大對象列使用empty_blob()構造空的指針。
droptable MyLob;
CreateTable MyLob
(
nonumber(8)primarykey,
fname varchar2(30),
myfile blob
)
Lob (myfile)StoreAs
(
Chunk15K
DisableStorageInRow
);
INSERTINTO MyLob
VALUES (1,'Winter.jpg', EMPTY_BLOB ());
-- 2，建立邏輯目錄MYDIR這裡隻能用大寫形式或者加寫在“”内
CreateorreplaceDirectory MYDIRAs'D:\Pic';
COMMIT;
-- 3，聲明一個BLOB類型變量，使用select into語句讓其指向到empty_blob()構造空的指針所指向的存儲空間
-- 格式：SELECT myfile INTO BLOB類型變量 FROM myLob WHERE no = 1 FOR UPDATE;
-- 4，聲明一個BFile類型變量，關聯邏輯目錄和實體目錄檔案，使用 BFileName()将其指向到待存儲的檔案。
-- 格式：BFile類型變量 :=BFileName('MYDIR','IMG_0210.JPG');
--5，使用DBMS_LOB.open()方法将BFile類型變量所指向的檔案打開
-- 格式：DBMS_LOB.Open(BFile類型變量);
--6，使用DBMS_LOB.loadfromfile()方法将BFile類型變量所指向的檔案讀入到BLOB類型變量所指向的存儲空間
-- 格式：DBMS_LOB.LoadfromFile(BLOB類型變量,BFile類型變量,DBMS_LOB.getlength(BFile類型變量));
--7，使用DBMS_LOB.close()方法将bfile的變量所指向的檔案關閉
-- 格式：DBMS_LOB.Close(BFile類型變量);
--8，送出事務
-- Commit;
-- 例子;
Declare
varB blob;
varF Bfile;
Begin
select myfileinto varB from myLob whereno =1forupdate;
varF := bfilename('MYDIR','Winter.jpg');
DBMS_LOB.open(varF);
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
--檢視檔案大小
Declare
varB blob;
Begin
select myfileinto varB from myLob whereno =1;
DBMS_OUTPUT.PUT_LINE('長度為: '||DBMS_LOB.getlength(varB));
End;
--如片圖檔圖檔圖檔
-- 例子：建立過程存儲圖檔或二進制檔案
CreateOrReplaceProcedure setBLOB(vFileNamevarchar2)
As
varF bfile;
varB blob;
vno number(8);
Begin
varF := bfilename('MYDIR',vFileName);
DBMS_LOB.open(varF);
selectmax(no)into vno from myLob;
if vnoisnullthen
vno := 1;
else
vno := vno + 1;
endif;
insertinto myLobvalues(vno,vFileName,empty_blob());
select myFileinto varB from myLob whereno = vnoforupdate;
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
select *from myLob
--執行過程指令視窗中個執行 sql視窗不支援該EXEC
EXEC setBLOB('WWinter.jpg');
--檢視檔案大小
Declare
varB blob;
Begin
select myfileinto varB from myLob whereno =6;
DBMS_output.put_line('長度為: '||DBMS_LOB.getlength(varB));
End;
--以下是java中查詢CLOB的例子
--未測試
-- //獲得資料庫連接配接
-- Connectioncon = ConnectionFactory.getConnection();
-- con.setAutoCommit(false);
-- Statement st= con.createStatement();
-- //不需要“for update”
-- ResultSet rs= st.executeQuery("select CLOBATTR from TESTCLOB where ID=1");
-- if(rs.next())
-- {
-- java.sql.Clob clob = rs.getClob("CLOBATTR");
-- ReaderinStream = clob.getCharacterStream();
-- char[] c= new char[(int) clob.length()];
-- inStream.read(c);
-- //data是讀出并需要傳回的資料，類型是String
-- data =new String(c);
-- inStream.close();
-- }
-- inStream.close();
-- con.commit();
-- con.close();
專題五：集合（聯合數組、嵌套表、Varrays）
--2013-07-06
--索引（單列、多列）
--ORACLE 的集合分為三類
--1.index_by 表即：
-- 聯合數組 :類似于 C 語言中的數組
-- 文法格式 :
-- TYPE tabletype
-- IS
-- TABLE OF TYPEINDEX BY BINARY_INTEGER
--說明 :
--tabletype,type 是要定義的 index_by表的類型 .
--例 :
droptable test;
createtable test(
idinteger,
namevarchar2(10)
);
createorreplaceTYPE test_name -- 類型名
IS
TABLEOF test.name%TYPE
INDEXBY BINARU_INTEGER;
--在聲明了類型和變量後 ,就可以用下列語句使用 index_by表中的單個元素 .
--v_name(index);
DECLARE
TYPE test_name -- 類型名
IS
TABLEOF test.name%TYPE
INDEXBYBINARY_INTEGER;
v_name test_name; --聲明變量
BEGIN
v_name(1):='qnma';
v_name(2):='cjh';
v_name(-2):='sss';
DBMS_OUTPUT.PUT_LINE('v_name(1)='||v_name(1));
DBMS_OUTPUT.PUT_LINE('v_name(2)='||v_name(2));
DBMS_OUTPUT.PUT_LINE('v_name(-2)='||v_name(-2));
END;
DECLARE
TYPE test
ISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER ;
v_test test;
BEGIN
FOR v_countIN1..5LOOP
v_test(v_count):= v_count*10;
ENDLOOP;
FOR v_countIN1..5LOOP
DBMS_OUTPUT.PUT_LINE(v_test(v_count));
ENDLOOP;
END;
--2. 嵌套表
--嵌套表與 index_by非常相似 , 唯一不同的就是沒有 INDEX BYBINARU_INTEGER
-- 如 :
DECLARE
TYPE test
IS
TABLEOFVARCHAR(20);
v_test test:=test('Tom','jack','ROSE'); --聲明時直接指派
BEGIN
--v_test(4):='dd'; --加入會報錯
FOR v_countIN1..3LOOP
DBMS_OUTPUT.PUT_LINE(v_test(v_count));
ENDLOOP;
END;
--2) 元素序列 :
--index_by 表中的元素是無序的 ,而嵌套表中的元素是有序的 . 嵌套表元素的 index 是
--從 1開始依次遞增的 .
--3. 可變數組
--注意
--與嵌套表一樣 ,可變數組也需要初始化 . 初始化需要注意的是 : 指派的數量必須保證不大于可變數組的最大上限 .
--以下是完整執行個體 :
DECLARE
TYPE dates
ISVARRAY(7)OFVARCHAR2(10);
TYPE months
ISVARRAY(12)OFVARCHAR2(10);
v_dates dates:=dates('Monday','Tuesday','Wednesday') ;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_dates(1));
DBMS_OUTPUT.PUT_LINE(v_dates(2));
DBMS_OUTPUT.PUT_LINE(v_dates(3));
END;
--以下介紹集合中常用的各種方法
--1.COUNT 屬性 :傳回集合中的數組元素個數
DECLARE
TYPEnameISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER;
TYPE pwdISTABLEOFVARCHAR2(20);
TYPE datesISVARRAY(7)OFVARCHAR2(20);--容量為 7
v_name name;
v_pwd pwd:=pwd('10000','12345','22','yes','no');
v_dates dates :=dates('Monday','Sunday');
BEGIN
v_name(1):='Tom';
v_name(-1):='Jack';
v_name(4):='Rose';
DBMS_OUTPUT.PUT_LINE('The index_by count is :'||v_name.count);
DBMS_OUTPUT.PUT_LINE('The nested count is :'||v_pwd.count);
DBMS_OUTPUT.PUT_LINE('The varray count is :'||v_dates.count);--傳回的是可變數組的實際元素個數
END;
--2.DELETE 方法
--通常使用 NEXT和 PRIOR 與 FIRST和 LAST 一起使用 ,用來處理循環 .
--例 :
DECLARE
TYPE pwdISTABLEOFVARCHAR2(20);
v_pwd pwd:= pwd('10000','12345','22','yes','no','ok','all','hello','right','left','football');
v_count integer;
BEGIN
v_count:=v_pwd.FIRST;
WHILEv_count<=v_pwd.LASTLOOP
DBMS_OUTPUT.PUT_LINE(v_pwd(v_count));
v_count:=v_pwd.NEXT(v_count);
ENDLOOP;
END;
專題六：動态sql（主要是本地動态sql，帶參數）
--2013-07-06
--動态sql（主要是本地動态sql，帶參數）
--Excute immediate 動态SQL語句 using綁定參數清單 returning into輸出參數清單;
--下面列舉一個執行個體：
--過程一：建立該表并輸入相應的資料
select *from emp1;
droptable emp1;
createorreplaceprocedure create_tableas
begin
executeimmediate
'create table emp1(id number,
name varchar2(10),
salary number )';--動态SQL為DDL語句
executeimmediate'insert into emp1 values (100,' ||'''jacky''' || ',5600)';
executeimmediate'insert into emp1 values (101,' ||'''rose''' || ',3000)';
executeimmediate'insert into emp1 values (102,' ||'''john''' || ',4500)';
commit;
endcreate_table;
--過程調用
call create_table();
select *from emp1;
--過程二：根據特定ID可以查詢到其姓名和薪水的資訊。
createorreplaceprocedure find_info(p_idnumber) as
v_name varchar2(10);
v_salary number;
begin
executeimmediate
'select name,salary from emp1 whereid=:1'
using p_id
returninginto v_name,v_salary;--動态SQL為查詢語句
--dbms_output.put_line(v_name ||'的收入為：'||to_char(v_salary))；
--exception
--when others then
--dbms_output.put_line('找不到相應資料')；
endfind_info;
call find_info(101);
--過程三：根據大于特定的薪水的查詢相應的員工資訊
createorreplaceprocedure find_emp1(p_salarynumber) as
r_emp1 emp1%rowtype;
type c_typeisrefcursor;
c1 c_type;
begin
open c1 for
'select * from emp1 where salary >:1'
usingp_salary;
loop
fetch c1into r_emp1;
exitwhen c1%notfound;
dbms_output.put_line('薪水大于'||to_char(p_salary)||'的員工為：');
dbms_output.put_line('ID為'to_char(r_emp1)||'其姓名為：'||r_emp1.name);
endloop;
close c1;
endcreate_table;
callfind_emp1(1000);
--3．動态SQL語句開發技巧
declare
type num_listisvarray(20)ofnumber;
v_id num_list :=num_list(100,101);
begin
--...
for iin v_id.first .. v_id.lastloop
-- ...
executeimmediate'update emp1
set =salary*1.2
where id=:1 '
using v_id(i);
endloop;
end;
--批處理
-- 對于上面的處理，當資料量大的時候就會顯得比較慢，那麼如果采用批聯編的話，則整個集合首先一次性的
--傳入到SQL引擎中進行處理，這樣比單獨處理效率要高的多，進行批聯編處理的代碼如下：
declare
type num_listisvarray(20)ofnumber;
v_id num_list :=num_list(100,101);
begin
--...
forall iin v_id.first .. v_id.lastloop
--...
executeimmediate'update emp1
set =salary*1.2
where id=:1 '
using v_id(i);
endloop;
end;
--create or replace procedure test(p_object in nocopysquare)
--...
--end;
--其中square為一個大的對象類型。這樣隻是傳遞一個位址，而不是傳遞整個對象了。顯然這樣的處理也是提高了效率。
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)