1、set verify on/off 控制輸出行不顯示old和new
2、set define ‘&’定義變量字元
3、set echo off/on 禁止或者啟用sql腳本中的sql語句和指令
4、accept varname vartype format a../$... prompt ‘....’；
undefine varname
5、變量中被定義為&1,&2意味者第一個變量和第二個變量
6、ttitle和btitle 代表的頁眉和頁腳
7、break on 和 compute 用于為列添加小計
8、内聯視圖 select colnum from （select colum from tablename where condition）；
9、union:兩個表不重複的所有行，intersect：兩個表共有的行，minus：第一個表有而第二個表沒有的行。
10、translate（x，from_string，to_string）用作密碼檔案的形成。
11、decode（value，search_value，result，default_value）條件查詢，可以實作if的功能或者case的功能。
12、case
when condition then result1
when condition then result2
····
when condition then resultN
else default_result
end 注意case一般存在from前面
13、使用connect by和start with可以實作階層化查詢，可以通過lpad指令實作目錄樹的功能，lpad（x，num）它指用x字元在左邊填充num個。
14、1、rollup可以為每個分組傳回小計記錄，cube，可以傳回每一個列組合的小計記錄，同時在末尾加上總計記錄，他們都是group by的一種擴充。2、還有一個就是grouping函數，它可以接受一列，但是隻能愛使用rollup和cube的查詢中使用，當需要傳回空值的地方顯示某個值時，就很有用，當列為空傳回1，非空傳回0。3、是以我們可以通過decode和grouping來指定非空行顯示什麼，空行顯示什麼，還有一種類似的功能就是通過nvl和nvl2也可以實作decode和grouping相結合的功能。4、還可以通過grouping sets隻把小計記錄顯示出來，可以用來作為統計的資訊顯示。5、group_id()用來消除group by字句傳回的重複記錄，group_id()不接受任何參數。
15、可以從一個表向另外一個表複制，通過insert into table1（列的種類）select ··· from table2 where condition。
16、子表引用父表，子表中不能含有父表中沒有的列值，但是父表中可以含有子表中沒有的列值。
17、在建立表列時，可以指定default ''來定義預設值。
18、注意merge的活用
merge into table1
using table2 on （條件下）
when matched then
result1
when not matched then
result2； matched就是條件吻合的情況，not matched就是條件不吻合的情況
19、使用dbms包的閃回查詢，一種是時間戳，一種是scn
execute dbms_flashback.enable_at_time（sysdate － ？/ 1440）；
execute dbms_flashback.enable_at_system_change_number（scn号）；
20、系統授權可以傳遞使用with admin option，對象授權可以傳遞是使用with grant option
可以用sys權限建立synonym（同義詞）來讓public都通路。
21、修改列的注意事項：1、修改列的長度，條件是，改列的類型的長度可以修改，而且隻有表中還沒有任何行或者所有列都為空值時才可以減小列的長度。2、修改數字列的精度，同樣也而且隻有表中還沒有任何行或者所有列都為空值時才可以減小數字列的精度。3、修改列的資料類型，如果表中還沒有任何行或列為控制，就可以将列修改為任何一種資料類型（包括更短的資料類型），否則，就隻能将列的資料類型修改為一種相容的資料類型，但條件時沒有縮短列的長度。4、使用列的預設值，預設值隻适用于新插入表中的行。
22、禁用和啟動限制，alter table tablename disable/enable constraint constraint_name
23、如果需要删除表的所有行就需要使用truncate，而不是delete，應為truncate回重置表的存儲空間，以準備接受新行，執行truncate語句不需要在資料庫中使用任何undo空間，也不需要執行commit指令使删除操作永久化。
24、使用序列
create sequence name
start with start_num
increment by increment_num
maxvalue max_num
minvalue min_num
cycle|nocycle
cache cache_num|nocache
order|noorder
cycle用于指定是否循環的，cache則指定要保留在記憶體中的整數的個數，order則是確定按照請求次序生成整數。其中noorder為預設值,這裡注意，一個序列包含了兩個僞列，為currval和nextval，在檢索序列的目前值之前，必須通過檢索序列的下一個值對序列進行初始化，也就是說，必須先nextval，才能currval。可以用序列填充表的主鍵，修改序列的注意：不能修改序列的初值，序列的最小值不能大于目前值，序列的最大值不能小于目前值
25、一般都将表和索引存儲到不同的表空間
create index index_name on table_name（column_name，····） tablespace name；
如果某列的值幾乎都是唯一的，而且用where使用改列查詢所傳回的行都小于該表總行的10％，則改列就非常适合與建立索引。
如果要基于函數的索引，就必須将參數query_rewrite_enabled設定為true；通過alter system來修改。每個表建立，就會給主鍵自動增加一個索引。
26、注意視圖中不存儲資料，它隻是會通路基表中的行。
create or replace view force/noforce view_name as 查詢語句 [with (check option| read only) constraint constraint_name]；
在視圖上插入行就相當于在基表中插入，但是隻能對簡單視圖執行DML操作，複雜視圖不支援DML操作。而且在插入行時，還要滿足建立視圖時大的條件。用create or replace就可以用來徹底替換一個視圖，alter view可以用來修改視圖的限制。比如alter view name drop constraint ····
27、塊結構
declare
declaration_statements
begin
executable_statements
exception
exception_handing_statements
end；
28、dbms_output.put_line('')輸出字元用的。它必須設定set serveroutput on 才可以看到。
29、%type可以用來定義變量的類型比如說 column1_name table_name.column1_name%type,它的意思就是column1_name的類型和表table_name中的column1_name列的類型一緻
30、PL/SQL中使用的條件邏輯 簡單循環
if condition1 then loop
statement1 statements
elsif condition2 then end loop；可以在循環中設定exit when condition 跳出
statement2 while循環
else while condition loop
statement3 statements
endif； 可以嵌套if語句 end loop；
for循環
for loop_variable in [reverse] lower_number..upper_number loop
statements
end loop; 預設的是增加值，使用reverse就可以減少1
31、遊标，1、首先聲明一些變量結果儲存列值。一般都采用%type形式，用來和表中的列一樣的類型 declare v_column table.column%type 2、聲明遊标，一般都和select組合在一起，如cursor cursor_name is select_statement；3、打開遊标，直接open cursor_name 4、就可以從遊标中取得記錄了，fetch cursor_name into variable..,其中variable是declare中聲明的變量，而且一般是放在循環中一行一行的讀取，并設定exit when cursor_name%notfound時跳出。5、最後一步一定要注意，用完遊标要關閉open cursor_name。注意：一般将遊标和for循環合用，因為這樣可以增強在遊标中通路記錄的能力，而且使用for循環不用顯式地打開和關閉遊标，連聲明都一起省略了。
32、建立過程 create or replace procedure proceduce_name (parameter_name in | out |in out type,.....) is | as
begin
procedure_body
end;
這裡in是預設地，它指在程式運作地時候已經具有值，而且在程式體中這個值不會改變，而out定義參數隻是在過程體内部指派。in out是指參數在程式運作時可能已經具有值，但是在過程體中也可以修改的。如果發生錯誤，可以通過show error來顯示出來。
33、建立對象類型 create or replace type type_name as object（...），一個對象類型中可以引用已經建立好地對象類型比如說 column type_name 就可以指定column地屬性為type_name
在建立過程中，可以使用member function 函數 return 值；它和過程類似，唯一地差別式程式通常不傳回值。用類型建立表 create table table_name （column type_name）或者create table table_name of type_name；對象表還有一個特點就是可以為對象表之間地關系建立模型，而不是使用外鍵。使用ref例如 column ref type_name scope is table_name；這裡地scope is是将對象引用限制在特定表中地對象上。
可以用set describe depth num 來顯示資訊地深度，通過desc就可以看到類型包含類型地資訊了。SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
34、修改.sql腳本的執行路徑，dos下可以通過修改sqlpath的方法來實作，就是set sqlpaht＝路徑1；路徑2····，可以在sqlplus中編輯預設的腳本目錄，通過
sql>set editfile 目錄就可以了。
35、從sqlplus環境切換到作業系統指令提示符下，可以采用host指令或者“！”，host通用，！使用于linux和unix系統中但在windows下不被支援。 。
36、安裝幫助檔案可以通過啟動$ORACLE_HOME\sqlplus\admin\help\helpbld.sql和hlpbld.sql來呼用helpus.sql建立，也可以呼用helpdrop.sql來删除。
37、調用show all可以檢視所有的環境變量，show error用于顯示目前在建立函數、存儲過程、觸發器、包等對象的錯誤資訊。用show parameter parametername來顯示初始化參數的值。
show rel來顯示資料庫的版本、show sga顯示sga的大小，show user顯示目前使用者。
38. &與&&的差別。
&用來建立一個臨時變量，每當遇到這個臨時變量時，都會提示你輸入一個值。&&用來建立一個持久變量，就像用用define指令或帶new_vlaue字句的column指令建立的持久變量一樣。當用&&指令引用這個變量時，不會每次遇到該變量就提示使用者鍵入值，而隻是在第一次遇到時提示一次。
39、@與@@的差別是什麼。
@等于start指令，用來運作一個sql腳本檔案。
@指令調用目前目錄下的，或指定全路徑，或可以通過SQLPATH環境變量搜尋到的腳本檔案。
@@用在腳本檔案中，用來指定用@@執行的檔案與@@所在的檔案在同一目錄，而不用指定全路徑，也不從SQLPATH環境變量指定的路徑中尋找檔案，該指令一般用在嵌套腳本檔案中。
40、在查詢語句中‘＃’的活用，如：就是如果突然忘了表的結果，可以通過#desc 表名先來顯示表的名字
SQL> select deptno, empno, ename
2 from emp
3 where
4 #desc emp
Name Null? Type
----------------------------------------- -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
4 sal > 4000;
41、制作腳本的另類方法：
set echo off
set feedback off
set newpage none
set linesize 500
set verify off
set pagesize 0
set term off
set trims on
set heading off
set timing off
set numwidth 38
SPOOL c:\具體的檔案名
你要運作的sql語句例如（SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;）可以做一個删除目前使用者的所有表
SPOOL OFF。
這裡注意（1）目前session是否對修改的資料進行自動送出 SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]}
（2）在用start指令執行一個sql腳本時，是否顯示腳本中正在執行的SQL語句 SQL> SET ECHO {ON|OFF}
（3）是否顯示目前sql語句查詢或修改的行數 SQL> SET FEED[BACK] {6|n|ON|OFF} 預設隻有結果大于6行時才顯示結果的行數。如果set feedback 1 ，則不管查詢到多少行都傳回。當為off 時，一律不顯示查詢的行數。
（4）是否顯示列标題 SQL> SET HEA[DING] {ON|OFF} 當set heading off 時，在每頁的上面不顯示列标題，而是以空白行代替
（5）設定一行可以容納的字元數 SQL> SET LIN[ESIZE] {80|n} 如果一行的輸出内容大于設定的一行可容納的字元數，則折行顯示。
（6）設定頁與頁之間的分隔 SQL> SET NEWP[AGE] {1|n|NONE} 當set newpage 0 時，會在每頁的開頭有一個小的黑方框。當set newpage n 時，會在頁和頁之間隔着n個空行。當set newpage none 時，會在頁和頁之間沒有任何間隔。
（7）顯示時，用text值代替NULL值 SQL> SET NULL text
（8）設定一頁有多少行數 SQL> SET PAGES[IZE] {24|n} 如果設為0，則所有的輸出内容為一頁并且不顯示列标題
（9）是否顯示用DBMS_OUTPUT.PUT_LINE包進行輸出的資訊。SQL> SET SERVEROUT[PUT] {ON|OFF} 在編寫存儲過程時，我們有時會用dbms_output.put_line将必要的資訊輸出，以便對存儲過程進行調試，隻有将serveroutput變量設為on後，資訊才能顯示在螢幕上。
（10）當SQL語句的長度大于LINESIZE時，是否在顯示時截取SQL語句。 SQL> SET WRA[P] {ON|OFF} 當輸出的行的長度大于設定的行的長度時（用set linesize n指令設定），當set wrap on時，輸出行的多于的字元會另起一行顯示，否則，會将輸出行的多于字元切除，不予顯示。
（11）是否在螢幕上顯示輸出的内容，主要用與SPOOL結合使用。SQL> SET TERM[OUT] {ON|OFF} 在用spool指令将一個大表中的内容輸出到一個檔案中時，将内容輸出在螢幕上會耗費大量的時間，設定set termspool off後，則輸出的内容隻會儲存在輸出檔案中，不會顯示在螢幕上，極大的提高了spool的速度。
（12）将SPOOL輸出中每行後面多餘的空格去掉。SQL> SET TRIMS[OUT] {ON|OFF}
（13）顯示每個sql語句花費的執行時間 set TIMING {ON|OFF}
42、動态生成spool指令所需的檔案名
在我們上面的例子中，spool指令所需要的檔案名都是固定的。有時我們需要每天spool一次，并且每次spool的檔案名都不相同，如檔案名包含當天的日期，該如何實作呢？
column dat1 new_value filename;
select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual;
spool c:\&&filename.txt
select * from dept;
spool off;
43、常用的oracle中繼資料查詢
sequence: sql='select sequence_name from user_sequences order by sequence_name'
table: sql='select table_name from user_tables order by table_name'
view: sql='select view_name from user_views order by view_name'
PROCEDURE: sql='select object_name from user_objects where object_type=value'
FUNCTION: sql='select object_name from user_objects where object_type= value'
PACKAGE: sql='select object_name from user_objects where object_type= value'
TYPE: sql='select object_name from user_objects where object_type= value'
44、Oracle 表空間相關的系統表
dba_tablespaces, dba_data_files, dba_temp_files, dba_free_space, dba_segments
45、Show feedback 檢查feedback的行數
Show numwidth 修改數值的寬度
Show autocommit 自動送出工作的能力，即使沒有下達commit指令，有些操作（如quit、exit）以及資料定義語言（ddl）的指令也會使送出發生。
46、注意delete與truncate，delete可以送出或復原删除操作，而truncate自動删除表中的所有記錄。Truncate指令的操作結果使部能被復原或送出的，截除的記錄不能被恢複。也不能靠執行閃回請求來找回被截除的資料。
47、一下往一個表中插入多行，要借助select和union兩個語句
insert into test(id,name)
select * from
(select 101，‘swust’ from dual
union
select 102，‘student’from dual）；
48、修改系統時間顯示模式：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> alter session set nls_date_format='dd-mon-yyyy';
Session altered.
SQL> alter session set nls_date_language='SIMPLIFIED CHINESE';
SQL> select sysdate from dual;
SYSDATE
----------------
17-8月 -2007
SQL> alter session set nls_date_language='AMERICAN';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------
17-aug-2007
49、把一個表從一個表空間轉移到另一個表空間
首先,使用下面的指令移動:
alter table table_name move tablespace tablespace_name;
然後,如果有索引的話必須重建索引:
alter index index_name rebuild tablespace tablespace_name;
50、在oracle中，把一個表空間的所有索引換到另一個表空間呢？
比如：例子1、在linux下操作的。
1、建立一個select_index.sql，裡面寫入以下内容。
SELECT 'ALTER index ' ||index_NAME|| ' REBUILD TABLESPACE INDEX_ISTQZDEV;' FROM USER_INDEXES;
2、執行sqlplus username/password @select_index.sql > index.log
3、gawk '/ALTER index/ {print $0}' > alter_index.sql
4、在執行一下 sqlplus username/password @alter_index.sql .
5、表空間的所有索引都到另一個專門儲存index的表空間裡面去了
例子2、将swust使用者在SYSTEM表空間的全部索引移動到student表空間.
1、用以下的内容編輯成一個腳本:SELECTINDEX.SQL
connect sys/syspassword as sysdba;
set pagesize 20000
set heading OFF
spool /export/home/swust/rebuildindex.sql;
select 'ALTER INDEX swust.' ||index_name|| ' REBUILD TABLESPACE student;'
from all_indexes
where wner='SWUST'and tablespace_name='SYSTEM';
spool off
2、在SQLPLUS中執行上述腳本,可在/export/home/swust/生成rebuilindex.sql
3、在SQLPLUS中執行rebuildindex.sql, 可以将所有swust使用者在SYSTEM表空間中的索引移動到student表空間
51、删除表空間 drop tablespace tablespace_name[including contents [and datafiles]]
删除使用者 drop user user_name cascade
删除表的注意事項：在删除一個表中的全部資料時，須使用TRUNCATE TABLE 表名;因為用Drop TABLE，Delete * FROM 表名時，TABLESPACE表空間該表的占用空間并未釋放，反複幾次Drop，Delete操作後，該TABLESPACE上百兆的空間就被耗光了。
52、從一個已知表建立另一個表
create table table_name as select * from user.table_name where....
53、傳回表的幾條記錄
比如傳回前N條記錄，select * from table_name where rownum 要傳回第m－n條記錄 select * from table_name where rownum minus
select * from table_name where rownum （是以結果就為1－n－（1－m））為m－n條了。
注意：隻能用以上符号(,>=,=,Between...and，會提示this is stop key!的錯誤的。由于rownum是一個總是從1開始的僞列，Oracle 認為這種條件是不成立，是以也就查不到記錄。
還有一種方法比如： select * from （ select rownum r from table_name where rownum<=n
order by name ）where r>10；也可以傳回表的m－n條記錄
這是因為它先排序了再選擇的,也就是記憶體排序外層選，rownum是随着結構集生成的，一旦生成了就不會變化了，rownum是在查詢集合産生的過程中産生的僞列。
54、在oracle中建立一個編号會自動增加的字段，以利于查詢
建立序列，create sequence checkup_no_seq nocycle maxvalue 99999999 start with 1；
55、檢視對象的依賴關系：
檢視視圖：dba_dependencies 記錄了相關的依賴關系 查東西不知道要檢視哪個視圖時，可以在DBA_Objects裡看，select object_name from dba_objects where object_name like '%swust%'(假如檢視swust相關,注意一般都是大寫哦)，然後DESC一下就大體上知道了。
56、rpad（str1，n，str2），将str1用str2從右端開始補充字元（字元的個數為n－str1字元的個數，這裡n是總長度），通常用在建立，固定長度的記錄時補充空格，例如select rpad（xxw，5，‘1’）from dual；結果為xxw11。
initcap（char）将插入中每個單元的第一個字母改為大寫，select initcap(today is sunday) from dual; 結果為Today Is Sunday；
ceil（n）傳回大于或者等于n的最近整數，floor（n）傳回小于或者等于n的最近整數。round（n，m）将n四舍五入并保留小數點後的m位，power（m，n）傳回m的n次幂。
oracle中預設的紀年方式，在00－49前預設為20，在50－99前預設為19。在插入期間如果沒有指定時間，就會預設為午夜用00:00:00表示。
57、内部連接配接時要傳回在兩個表中特定列的值比對的記錄，與之不同的是，外部連接配接的傳回結果是從一個表中找出對應與另一個表，沒有比對值的記錄。自連接配接用于作單個表内部的關聯。表中的行被傳回來連接配接到同一個表。
union将傳回兩表中無任何重複的所有行，而union all可以允許傳回兩表中所有行，而intersect傳回的是兩個表中都有的行（但要注意使用它可能會産生嚴重的性能問題）。而minus傳回第一個表有而第2個表中沒有的行。
58、oracle資料庫如何增加表空間大小
有兩種方法，一種是為表空間增加資料檔案
alter tablespace users add datafile 'c:\oracle\ora81\oradata\sid\xxw02.dbf' size 100M;
另一種方法是增加表空間原有資料檔案尺寸：
alter database datafile 'c:\oracle\ora81\oradata\\sid\users.dbf' resize 1000M;
59、選擇在每一組b值相同的資料中對應的a最大的記錄的所有資訊(類似這樣的用法可以用于論壇每月排行榜,每月熱銷産品分析,按科目成績排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
也可以已經知道b列值如：select b，max(a) from tablename where b=值 group by b order by 2；
60、顯示文章、送出人和最後回複時間
select a.title,a.username,c.adddate from table a,(select max(adddate) adddate from table b where b.title=a.title) c
61、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重複行而派生出一個結果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
62、行列轉換執行個體
執行個體一、
表xxw有三個字段 no --序列 num --檢查項目 value --值
資料分别如下：
no num' value
------- -------- --------
11 1 0.50
11 2 0.21
11 3 0.25
12 1 0.24
12 2 0.30
12 3 0.22
實作功能 建立視圖時移動行值為列值
create or replace view change_view
as
select no 序号,
sum(decode(num,1, value)) 檢測項目1,
sum(decode(num,2, value)) 檢測項目2,
sum(decode(num,3, value)) 檢測項目3
from xxw group by no;
序号 檢測項目1 檢測項目2 檢測項目3
11 0.50 0.21 0.25
12 0.24 0.30 0.22
執行個體二、
表、視圖結構轉化
現有一個商品銷售表sale，表結構為：
month char(6) --月份
sell number(10,2) --月銷售金額
現有資料為：
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要轉化為以下結構的資料：
year char(4) --年份
------------ --------------------- -------------------
month1 number(10,2) --1月銷售金額
month2 number(10,2) --2月銷售金額
month3 number(10,2) --3月銷售金額
month4 number(10,2) --4月銷售金額
month5 number(10,2) --5月銷售金額
month6 number(10,2) --6月銷售金額
month7 number(10,2) --7月銷售金額
month8 number(10,2) --8月銷售金額
month9 number(10,2) --9月銷售金額
month10 number(10,2) --10月銷售金額
month11 number(10,2) --11月銷售金額
month12 number(10,2) --12月銷售金額
結構轉化的SQL語句為：
create or replace view
new_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);
體會：要用decode /group by/ order by/sign/sum來實作不同報表的生成
執行個體三、
CASE應用
no depart zhi sex
1 部門a 800 1 男
2 部門b 900 2 女
3 部門a 400 1 男
4 部門d 1400 2 女
5 部門e 1200 1 男
6 部門f 500 1 男
7 部門a 300 2 女
8 部門d 1000 1 男
9 部門d 1230 2 女
10 部門b 2000 2 女
11 部門c 2000 1 男
12 部門b 1200 1 男
SELECT depart as 部門,COUNT(no) as 人數,
SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 小于800元,
SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000)
WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
WHEN 800 THEN 1 ELSE 0 END)) as 從800至999,
SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
WHEN 1000 THEN 1 ELSE 0 END)) as 從1000元至1199元,
SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
+(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大于1200元
FroM xxw
GROUP BY depart；
部門 人數 男 女 小于800元 從800至999 從1000元至1199元 大于1200元
部門a 3 2 1 2 1 0 0
部門b 3 1 2 0 1 0 2
部門c 1 1 0 0 0 0 1
部門d 3 1 2 0 0 1 2
部門e 1 1 0 0 0 0 1
部門f 1 1 0 1 0 0 0
63、如果要檢視一個表中的rowid僞列和所有的列，用select rowid，* from tablename；就會提示錯誤，是以可以用别名來實作
如select rowid，a.* from tablename a；
64、提取目前日期的年月日，可以如下select extract（year/month/day from sysdate） from dual；
65、顯示員工工資排名
SELECT ENAME, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;
同樣顯示員工工資排名但是是按部門分類
SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;
但是它存在一個缺陷就是，如果部門中有工資相同的那麼sal_order不會相同隻能繼續排下區，如：1、2、3、4、····
但是使用rank()就可以實作同排名如：
SELECT DEPTNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP;
它就彌補了這個缺陷排名就可以如：1、2、2、4、5、·····
是以要找到每個部門工資最高的人(隻選一個)
SELECT DEPTNO, ENAME, SAL FROM
(SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER
(PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP)
WHERE SAL_ORDER < 2;
而要找到每個部門工資最高的人(包括并列第一)
SELECT DEPTNO, ENAME, SAL FROM
(SELECT DEPTNO, ENAME, SAL, RANK() OVER
(PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM SCOTT.EMP)
WHERE SAL_ORDER < 2;
66、建立分區表類型如下：
CREATE TABLE Sales_Details
(
Prod_Id VARCHAR2 (5),
Sale_Date DATE NOT NULL,
Cost NUMBER
)
PARTITION BY RANGE (Sale_Date)
(
PARTITION sd1 VALUES LESS THAN (TO_DATE('01/4月/2001', 'DD/MON/YYYY')),
PARTITION sd2 VALUES LESS THAN (TO_DATE('01/7月/2001', 'DD/MON/YYYY')),
PARTITION sd3 VALUES LESS THAN (TO_DATE('01/9月/2001', 'DD/MON/YYYY')),
PARTITION sd4 VALUES LESS THAN (TO_DATE('01/1月/2002', 'DD/MON/YYYY'))
);
CREATE TABLE EmpDetail
(
EmpID VARCHAR2 (5),
EmpName VARCHAR2 (15),
Department VARCHAR2 (10)
)
PARTITION BY HASH (EmpID)
(
PARTITION Eid1,
PARTITION Eid2,
PARTITION Eid3
);
CREATE TABLE DEPARTMENT
(
DepID varchar2 (5),
Dept_Name varchar2 (20)
)
PARTITION BY LIST (Dept_Name)
(
Partition D1 values ('會計部'),
Partition D2 values ('管理層'),
Partition D3 values ('人力資源部')
);
CREATE TABLE BranchDetails1
(
Branch_ID varchar2 (5) not null,
Branch_name varchar2(10) not null,
Address varchar2(20)
)
PARTITION BY RANGE (Branch_Id)
SUBPARTITION BY HASH (Branch_name)
SUBPARTITIONS 6
(
PARTITION S1 values LESS THAN ('B005'),
PARTITION S2 VALUES LESS THAN ('B010'),
PARTITION S3 VALUES LESS THAN ('B015'),
PARTITION S4 VALUES LESS THAN ('B020')
);
同時可以制定儲存的表空間。
67、重新編譯視圖alter view name_view compile;
68、檢視可更新的列 SELECT * FROM user_updatable_columns WHERE table_name；
69、手機号(service_id)
代理商(dealer_id)
繳費次數(pays_count)
繳費金額(payfee_sum)
最後繳費時間(last_pay)
手機号和代理商為主鍵,一個手機号可能多條記錄,查詢要求:
找出一個手機号繳費次數最多的記錄;
若兩個的代理商最大繳費次數一樣,取繳費金額多的記錄;
若繳費次數和續費金額一樣多,取繳費時間最大的記錄.
有什麼好的方法實作。
SELECT * FROM (SELECT a.*, rank() over(partition by service_id ORDER BY pays_count DESC, payfee_sum DESC, t_pay DESC) as rn FROM fl_wx_payfee_count) a WHERE rn = 1;
70、sys_context('context','attribute');用于傳回應用上下文的特定屬性值，其中context為應用上下文名，而attribute則用于制定屬性名。如
SQL> select sys_context('userenv','session_user') "資料庫使用者"
2 ,sys_context('userenv','os_user') "OS使用者"
3 from dual;
資料庫使用者 OS使用者
------------------- ----------------------------
SCOTT Administrator
記住兩個轉換函數一個scn_totimestamp(number)根據輸入的scn值傳回對應的大概日期時間，number用于指定scn值。
timestamp_scn(timestamp)根據輸入的timestamp傳回對應的scn值，其中timestamp用于指定日期時間。
greatest(expr1,expr2,.....)用于傳回表達式中值最大的一個。least(expr1,expr2,....)用于傳回表達式中值最小的一個。
nullif(expr1,expr2)用于比較表達式expr1和expr2，如果二者相等就傳回null，否則就傳回expr1。
71、sys_connect_by_path(column,char):隻适用于層次查詢，用于傳回從根到節點的列值路徑，例如：
SQL> select lpad(' ',2*(level-1))||sys_connect_by_path(ename,'/') "Path"
2 from emp start with ename='SCOTT'
3 connect by prior empno=mgr;
Path
--------------------------------------------------------------------------------
/SCOTT
/SCOTT/ADAMS
72、UID用于傳回目前會話所對應的使用者的ID号
SQL> select uid from dual;
UID
----------
57
73、同理user就是用于傳回目前會話所對應的資料庫使用者名。
SQL> select user from dual;
USER
------------------------------
SCOTT
74、USERENV(parameter) 用于傳回目前會話上下文的屬性資訊。其中parameter可以區isdba、language、terminal、client_info。例如：
SQL> select userenv('isdba'),userenv('language'), userenv('terminal') from dual;
USERENV('ISDBA') USERENV('LANGUAGE') USERENV('TERMINAL')
---------------- ------------------------------------- -----------------------
FALSE SIMPLIFIED CHINESE_CHINA.ZHS16GBK 67F6A7B4F468
75、vsize（expr）用于傳回oracle内部存儲expr的實際位元組數。如果expr為null那麼就傳回為null，但是該函數隻能在sql語句中使用，例如：
SQL> select ename,vsize(comm) from emp;
ENAME VSIZE(COMM)
---------- -----------
SMITH
ALLEN 2
WARD 2
76、percent_rank（expr1，expr2，···）within group（order by expr1，expr2，·）
用于傳回特定數值在統計級别中所占的比例。
SQL> select percent_rank(3000) within group(order by sal)
2 percent from emp;
PERCENT
----------
0.78571428
77、而percentile_cont（percent_expr）within group （order by expr），則正好相反，它是已經知道處于某個百分點，來确定數值。（連續分布模型确定）
SQL> select percentile_cont(0.6) within group(order by sal)
2 value from emp;
VALUE
----------
2476
還有percentile_disc（percent_expr）within group （order by expr），功能同上，但是它是（離散分布模型确定）
SQL> select percentile_disc(0.6) within group(order by sal)
2 value from emp;
VALUE
----------
2695
78、rank（expr1，expr2，··）within group（order by expr1，expr2，···）則用于傳回特定數值在統計數值後所占的等級。
SQL> select rank(800) within group(order by sal)
2 rank from emp;
RANK
----------
1
80、在pl/sql塊中嵌入select語句，格式注意
select select_list
into (variable_name1, variable_name2,·····)
from table where condition;
注意：在pl/sql直接使用select into 語句，該語句必須要傳回一條資料，并且隻能傳回一條資料，否則會觸發例外的。
81、在pl/sql中插入資料，如果使用values子句插入的
insert into table(···) values( ···);注意每次隻能插入一條資料，而且必須為表的主鍵列和not null 提供資料，較為麻煩，是以采用在pl/sql中使用子查詢插入資料。如：
insert into table select * from table where condition；
82、在pl/sql中更新資料，可以使用表達式更新列值。
update table set column＝value where condition；隻要注意滿足類型長度限制即可。
也可以使用子查詢來更新資料。例如：
update table set （column1，column2）＝（select ··· from table where condition） where column＝（select ··· from table where condition）；
83、在pl/sql中删除資料，可以使用變量來删除資料
delete from table where column＝變量，或者使用子查詢來删除資料，如：
delete from table where column ＝（select ···· from table where condition）；
84、在pl/sql中執行上述4中操作時候，oracle會相應配置設定上下文區（context area），這是我們就可以使用遊标指向上下文區的指針。
要注意sql遊标屬性，分為sql%found，sql%nofound，sql%rowcount，sql%isopen。
pl/sql在執行上述4個操作，oracle會隐含地打開遊标，并且執行完後，又會隐含地關閉遊标。sql%rowcount的功能是用于傳回sql語句所作用的總計行數。
85、自定義pl/sql記錄，文法如下：（定義某幾列）
type type_name is record （
filed1，filed2，···）；
指定變量 type_name；
或者使用%rowtype定義記錄變量，它可以基于表或者視圖定義記錄變量。使用它，記錄成員得名稱和類型與表或者視圖列得名稱和類型完全相同。（完全定義）
86、在select into語句中使用pl/sql，一種情況使用記錄變量如：
declare
type test is record
( ename emp.ename%type,
sal emp.sal%type,
deptno emp.deptno%type);
emp_record test;
begin
select ename,sal,deptno into emp_record from emp
where empno=&no;
dbms_output.put_line(emp_record.ename);
dbms_output.put_line(emp_record.deptno);
end;
但是要注意選擇清單中的列和表達式的順序、個數、類型必須要與記錄成員的順序、個數、類型完全比對才可以。（不能多不能少）
二種是使用記錄成員，這樣選擇清單後列和表達式的順序可以任意指定，但是記錄成員需要與之比對。例如
declare
type test is record
( ename emp.ename%type,
sal emp.sal%type,
deptno emp.deptno%type);
emp_record test;
begin
select ename,sal into emp_record.ename,emp_record.sal
from emp where empno=&no;
dbms_output.put_line(emp_record.ename);
dbms_output.put_line(emp_record.sal);
end;
87、在insert語句中也可以使用pl/sql記錄，
一種在values子句中使用記錄變量
declare
type test is record
( ename emp.ename%type,
sal emp.sal%type,
deptno emp.deptno%type);
emp_record test;
begin
select ename,sal into emp_record.ename,emp_record.sal
from emp where empno=&no;
dbms_output.put_line(emp_record.ename);
dbms_output.put_line(emp_record.sal);
end;
二種在values種使用記錄成員，列的順序可以任意指定，但記錄成員需要與之比對。
88、在update語句種使用pl/sql記錄
一種在set子句中使用記錄變量，列的順序、個數、類型必須要與之記錄成員的順序、個數、類型完全比對。
declare
dept_record dept%rowtype;
begin
dept_record.deptno :=30;
dept_record.dname := 'MANAGE';
dept_record.loc :='CHINA';
update dept set row=dept_record where deptno=30;
end;
另一種就是使用記錄成員，那樣列的順序可以是任意指定，但記錄成員需要與之比對。
89、索引表，又稱pl/sql表，這裡注意進階語言數組的元素個數是有限制的，并且下标不能為負值，但是索引标的元素個數是沒有限制的，而且下标可以為負值，但是索引表隻能作為pl/sql複合資料類型使用，而不能作為表列的資料類型使用。如：
type typename is table of element_type
[not null] index by key_type；
identifier typename；
90、嵌套表，進階語言的數組的元素下标從0或者1開始，而且元素個數有限值，但是嵌套表下标是從1開始，沒有元素個數限制，而且進階語言的數組元素值是順序的，而嵌套表元素的數組元素值可以是稀疏的。注意：索引表不能作為表列的資料類型使用，但是嵌套表可以定義如下：
type typename is table of element_type；
identifier typename；
當使用嵌套表時，必須首先使用其構造方法初始化嵌套表。
declare
type ename_type_name is table of emp.ename%type;
ename_record ename_type_name;
begin
ename_record :=ename_type_name('a','a','a'); 初始化很重要，要不報錯
select ename into ename_record(2) from emp
where empno=&no;
dbms_output.put_line(ename_record(2));
end;
在表列中使用嵌套表，例如：
SQL> create type phone_type is table of varchar(20);
2 /
Type created
SQL> create table employ (
2 id number(4),name varchar2(10),sal number(6,2),
3 phone phone_type
4 ) nested table phone store as phone_table;
Table created
往嵌套表中插入資料
begin
insert into employ values(1,'scott',800,
phone_type('123456','654321'));
end；
然後通過下面方式來檢視嵌套表：
declare
phone_table phone_type;
begin
select phone into phone_table
from employ where id=1;
for i in 1..phone_table.count loop
dbms_output.put_line('電話号碼'||phone_table(i));
end loop;
end;
更新嵌套表的資料，使用構造方法初始化該變量就可以了。
declare
phone_table phone_type:=phone_type('13758',
'45789','65846');
begin
update employ set phone=phone_table
where id=1;
end;
91、變長數組（varray），它可以作為表列的資料類型使用。與進階語言數組非常類似，元素下标從1開始，而且有限制。如：
type typename is varray(size_limit) of element_type [not null];
identifier typename;
使用varray元素時，必須要使用其構造方法初始化varray元素。但是不用全部初始化，用到那就初始化到那個地方
declare
type ename_table_type is varray(20) of emp.ename%type;
ename_record ename_table_type:=ename_table_type('a');
begin
select ename into ename_record(1) from emp 如果要用到2，就要初始化到2
where empno=&no;
dbms_output.put_line('雇員:'||ename_record(1));
end;
在表中使用varray同上類似如：
SQL> create type phone_type is varray(20) of varchar(20);
2 /
SQL> create table employ (
2 id number(4),name varchar2(10),sal number(6,2),
3 phone phone_type
4 );
92、可以将pl/sql記錄和pl/sql集合結合起來使用，進而能夠處理多行多列的資料例如：
declare
type ename_table_type is table of emp%rowtype
index by binary_integer;
ename_table ename_table_type;
begin
select * into ename_table(1) from emp
where empno=&no;
dbms_output.put_line(ename_table(1).ename);
end;
93、我們也可以通過以上來實作多級集合這樣來實作多元數組功能，就是可以在一個varray類型中再繼續嵌套一個varray類型。
declare
type first_varray_type is varray(10) of int;
type second_varray_type is varray(10) of first_varray_type;
num second_varray_type:=second_varray_type(
first_varray_type(1,2,3),
first_varray_type(4,5));
begin
dbms_output.put_line('顯示二維數組元素');
for i in 1..num.count loop 注意上限的表達形式
for j in 1..num(i).count loop 注意上限的表達形式
dbms_output.put_line('num('||i||','||j||')='||num(i)(j));注意這種在字元串中顯示i，j的方法。
end loop;
end loop;
end;
同理對于多級嵌套表和多級索引表如下：
declare
type first_varray_type is table of int;
type second_varray_type is table of first_varray_type;
num second_varray_type:=second_varray_type(
first_varray_type(1,2,3),
first_varray_type(4,5));
declare
type first_varray_type is table of int
index by binary_integer;
type second_varray_type is table of first_varray_type
index by binary_integer;
num second_varray_type;
94、集合方法，它是oracle提供用于操縱集合變量的内置函數或者過程，其中exists，count，limit，first，next，prior，next是函數，而extend，trim，delete則是過程。集合方法調用如下 collection_name.method_name[(parameters)]，注意集合方法隻能在pl/sql語句中使用，而不能在sql語句中調用，另外集合方法中extend和trim隻适用于嵌套表和varray。
exists用于确定集合元素是否存在。
count用于傳回目前集合變量中的元素總個數。
limit用于傳回集合元素的作大個數。
first和last用于傳回集合變量第一個元素的下标和最後一個元素的下标。
prior和next用于傳回目前集合元素的前一個元素的下标和後一個元素的下标。
extend用于擴充集合變量的尺寸，并為他們增加元素。有extend，extend(n),extend(n,i)形式，第一個extend為集合變量添加一個null元素，第二個extend(n)為集合變量添加n個null元素，而第三個extend(n,i)則為集合變量添加n個元素（其中元素值與第i個元素相同）。
trim用于從集合尾部删除元素，有trim和trim(n)兩種形式，trim用于從集合尾部删除一個元素，而trim(n)用于從集合尾部删除n個元素。
delete用于删除集合元素，同樣有delete，delete(n),delete(m,n)三種形式，其中delete用于删除所有元素，delete(n)用于删除集合變量的第n個元素，而delete(m,n)用于删除結合變量從m到n的所有元素。
95、集合指派，當使用嵌套表和varray時，通過執行insert，update，fetch，select，指派語句，使用者可以将一個集合的資料指派給另一個集合，在給嵌套表時還可以使用set，multiset union，multiset intersect，multiset except等集合操作符号，其中
set操作符用于取消嵌套表中的重複值。
multiset union用于取得兩個嵌套表的并集預設會包含重複值（帶distinct操作符可以取消重複結果）。如：集合1 multiset union distinct 集合2
multiset intersect用于取得兩個嵌套表的交集。
multiset except用于取得兩個嵌套表的差集。
将一個集合的資料指派給另一個集合，使用指派語句(:=)将源集合中的資料指派給目标集合時，會自動清除目标集合原有的資料，将源集合的資料指派給該目标集合。注意：當進行集合指派時，源集合和目标集合的資料類型必須完全一緻，如果集合元素資料類型一緻，但集合類型不一緻，那也不能進行指派。
declare
type first_varray_type is varray(5) of varchar2(10);
type second_varray_type is varray(5) of varchar2(10);
test1 first_varray_type;
test2 second_varray_type;
begin
test1 :=first_varray_type('scott','aa');
test2 :=test1;
end;
這就會提示錯誤。
給集合指派null值時，可以使用delete和trim，也可以将一個null集合變量指派給目标集合變量。
dbms_output.put_line列印出換行，而dbms_output.put不換行。
96、比較集合，可以使用cardinality,submultiset of,member of,is a set,is empty等來比較集合變量。其中
cardinality用于傳回嵌套表變量的元素個數。和count的功能幾乎相同
submultiset of 用于确定一個嵌套表是否為另一個嵌套表的子集。
如：if test1 submultiset of test2 then···檢測test1是否是test2的子集。
member of 用于檢測特定資料是否為嵌套表的元素。
如：if var member of test1 then····檢測變量var是不是test1的元素。
is a set用于檢測嵌套表是否包含重複的元素值。
如：if test is a set then····檢測test裡面包不包含重複值。如果為真則表明無重複值
is empty用于檢測嵌套表是否為null。
如：if test_table is empty then····。
比較兩個嵌套表是否相同，如if test_table1 比較操作符 test_table2 then····隻能用＝或者!＝,而且隻能用于嵌套表。
97、批量綁定，舉例，首先是不批量插入資料例如：
declare
type id_record_type is table of number(6)
index by binary_integer;
type name_record_type is table of varchar2(10)
index by binary_integer;
id_record id_record_type;
name_record name_record_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_record(i) :=i;
name_record(i) :='test'||to_char(i);
end loop;
start_time :=dbms_utility.get_time;
for i in 1..id_record.count loop
insert into test values (id_record(i),name_record(i));
end loop;
end_time :=dbms_utility.get_time;
dbms_output.put_line('總計時間'||to_char((end_time-start_time)/100)||'秒');
commit;
end;
相當于執行了5000次insert into 指令是以耗費時間較長，在來看批量輸入隻需要使用一條insert語句就可以輸入5000行資料，是以時間斷很多。如：
begin
for i in 1..5000 loop
id_record(i) :=i;
name_record(i) :='test'||to_char(i);
end loop;
start_time :=dbms_utility.get_time;
forall i in 1..id_record.count 很關鍵一條語句。
insert into test values (id_record(i),name_record(i));
end_time :=dbms_utility.get_time;
dbms_output.put_line('總計時間'||to_char((end_time-start_time)/100)||'秒');
commit;
end;
是以我們可以看出使用批量綁定的速度要遠遠優于不使用批量綁定的，批量綁定是使用bulk collect子句和forall語句完成的，其中：
98、forall語句隻适用于執行批量的dml操作，可以是連續的元素也可以是不連續的。
第一種形式 forall index in lower_bound..upper_bound sql_statement;
例如：forall i in 1..id_record.count 其中i必須是連續的。
insert into test values (id_record(i),name_record(i));
update test set name=name_record(i) where id=id_record(i);
delete from test where id_record(i);
第二種形式 forall index in indices of collection [between lower_bound and upper_bound] sql_statement;
例如：forall i in indices of id_record 這個i可以是不連續的。
delete from test where id=id_record(i);
第三種形式 forall index in values of index_collection sql_statement;該子句用于從其他結合變量中取得集合下标（index）值。
例如：forall i in values of index_point 其中index_point裡面記錄了i的值
insert into test values(id_record(i),name_record(i));
99、sql%bulk_rowcount，它是轉為forall語句提供，就是取得在執行批量操作的時候第i個元素所作用的行，如：dbms_oupput.put_line('第一個元素作用行數'||sql%bulk_rowcount(1))。
100、bull collect用于取得批量資料，該子句隻能用于select語句，fetch語句和dml傳回子句中。用法：....bulk collect into collection_name1,collection_name2···.....。
第一種在select into中使用，可以一次将select語句的多行結果檢索到集合變量中。
如：select * bulk collect into emp_table from emp where deptno＝&no; (很有用要注意····)
第二中在dml的傳回子句中使用，通過returning來實作。
如：delete from emp where deptno=&no returning ename bulk collect into ename_table;
101、pl/sql包含隐含遊标和顯示遊标兩種遊标類型，其中隐含遊标用于處理select into和dml語句，而顯示遊标則專門用于處理select語句傳回的多行資料。
顯示遊标
第一步：定義遊标 cursor cursor_name is select_statement;
第二步：打開遊标 open cursor_name;
第三步：提取資料 fetch cursor_name into varable1,varable2,····;
fetch cursor_name bulk collect into collect1,collect2,····;
第四步：關閉遊标 close cursor_name；
顯示遊标的屬性%isopen,%found,%notfound,%rowcount。其中%rowcount用于傳回目前行為止已經提取到的實際行數。
102、第一種使用fetch...into語句
declare
cursor emp_cursor is
select ename,sal from emp where deptno=10;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename||' '||v_sal);
end loop;
close emp_cursor;
end;
fetch...into每次隻能處理一行資料。要處理多行就要通過loop循環來實作。
第二種，使用fetch.....bulk collect into來提取資料。
declare
cursor emp_cursor is
select ename from emp where deptno=10;
type ename_table_type is table of varchar2(10);
ename_table ename_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into ename_table;
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor;
end;
這樣一次就可以提取出所有的資料。
第三種 使用fetch....bulk collect into...limit提取，預設情況下會提取結果集的所有資料。如果結果集含有大量資料，并且使用varray集合變量接收資料，那麼可能需要限制每次提取的行數。
如：fetch emp_cursor bulk collect into ename_table
limit rows；每次提取rows行資料。
第四種 就是使用遊标屬性來實作。
第五種 就是基于遊标的記錄變量，使用記錄變量存放遊标資料
declare
cursor emp_cursor is
select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_record.ename||' '||emp_record.sal);
end loop;
close emp_cursor;
end;
103、參數遊标就是指帶有參數的遊标，文法：
cursor cursor_name(parameter_name datatype) is select_statement;
但要注意，定義參數遊标時，遊标參數隻能指定資料類型，而不能指定長度。另外，定義參數遊标時，一定要在遊标子查詢的where子句中引用該參數，否則就失去定義參數遊标的意義。
declare
cursor emp_cursor(no number) is
select ename from emp where deptno=no; where子句中必須引用參數
v_ename emp.ename%type;
begin
open emp_cursor(10); 指定參數
loop
fetch emp_cursor into v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename);
end loop;
close emp_cursor;
end;
104、如果要通過遊标更新或者删除資料，必須有for update子句，文法如：
cursor cursor_name(parameter datatype) is select_statement for update [of column] [nowait]; 這裡的nowait子句用于指定不等待鎖。在提取了遊标資料之後，為了更新或者删除目前遊标行資料，必須在update或delete中引用where current of 子句例如：
update table_name set column=.... where current of cursor_name;
delete from table_name where current of cursor_name;例如
declare
cursor emp_cursor is
select ename,sal from emp
for update;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
if v_sal<200 then
update emp set sal=sal+2000 where current of emp_cursor;
end if;
dbms_output.put_line(v_ename||v_sal);
end loop;
close emp_cursor;
end;
使用of子句在特定表加共享鎖，如果遊标子查詢設計到多張表，那麼在預設情況下會在所有修改表上加行共享鎖。為了在特定的表上下，可以使用of子句，如下：
declare
cursor emp_cursor is
select ename,sal,dname,emp.deptno
from emp,dept
where emp.deptno=dept.deptno
for update of emp.deptno; 注意共享了deptno這一列
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
if emp_record.deptno=30 then
update emp set sal=sal+2000 where current of emp_cursor;
end if;
dbms_output.put_line(emp_record.ename||' '||emp_record.sal
||' '||emp_record.dname);
end loop;
close emp_cursor;
end;
如果在後面繼續指定nowait的話，即時目前會話已經加了鎖，但是也可以忽略，避免了等待。
105、遊标的for循環，文法：
for record_name in cursor_name loop
statement1;statement2;····
end loop;
在執行循環體内容之前，oracle會隐含地打開遊标，并且每循環一次提取一次資料，在提取了所有資料之後，會自動退出循環并隐含地關閉遊标。
一種情況：
declare
cursor emp_cursor is
select ename,sal from emp;
emp_record emp%rowtype;
begin
for emp_record in emp_cursor loop
dbms_output.put_line('第'||emp_cursor%rowcount||
原文位址：http://blog.oracle.com.cn/html/80/266780-26690.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26794549/viewspace-720242/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/26794549/viewspace-720242/
![ACS基本配置-權限等級管理[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)