1、第1章 Oracle安裝配置1Oracle服務端安裝好之后，在命令提示符下，利用echo %Path%命令查看此時的系統變量Path。在Windows系統中，單擊【開始】|【運行】，并鍵入“cmd”，如下圖所示：單擊【確定】按鈕，將進入Windows命令提示符，如下圖所示：鍵入echo %path%，并按下回車鍵，將顯示此時變量path的值，如下圖所示：2利用java version命令，查看此時java環境的版本，以確認是否為Oracle安裝時自帶的Java文件。 在Windows的【Command】窗口中執行java version命令，將看到本機Java環境的版本，如下圖所示：3Orac

2、le數據庫服務器安裝之后，在硬盤上搜尋名為oradata的文件夾。其中包含了所有數據庫的物理文件，查看已有數據庫的子文件夾及文件。一個數據庫的典型文件包括：后綴為CTL的控制文件；后綴為LOG的重做日志；后綴為DBF的數據文件。第2章 Oracle常用工具1有時無法連接數據庫，是由于多次安裝了服務端/客戶端，而導致客戶端軟件尋找TNS配置文件時，混淆了當前有效的路徑。此時，可以利用將DNS描述直接作為參數傳遞給客戶端軟件的方式來登錄數據庫，從而不再使用TNS配置文件。嘗試利用數據庫ORCL的TNS描述直接登錄數據庫。利用sqlplus+TNS配置登錄數據庫的命令如下所示：sqlplus sys

3、/abc123(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl) as sysdba登錄成功的界面如下所示：2EZConnect是EasyConnect的簡稱。利用EZConnect可以在客戶端以IP+SID的方式登錄數據。利用NetManager配置EZConnect的連接方式，并利用客戶端軟件+EZConnect的方式登錄數據庫ORCL。首先保證Oracle數據庫服務器支持EZConnect連接方式，如下

4、圖所示：然后，在【Command】窗口中，利用sqlplus登錄數據庫：登錄成功的界面如下所示：當然，也可以利用PL/SQL Developer進行登錄：3客戶端連接Oracle數據庫連接時，默認端口為1521。創建一個新的監聽程序，其端口為1522。然后將ORCL注冊于該監聽程序。1）在Net Manager中創建名為LISTENER_1522。為【監聽位置】填寫主機IP和端口號。注意其端口號為15222）選擇監聽程序的【數據庫服務】，并為其輸入ORCL。3）利用【文件】|【保持網絡配置】，保存監聽程序LISTENER_1522的信息。4）在Windows Command窗口中，啟動監聽程序

5、LISTENER_1522。C:>lsnrctl start listener_1522LSNRCTL for 32-bit Windows: Version .2.0 - Production on 31-7月 -2011 19:37:17Copyright (c) 1991, 2004, Oracle. All rights reserved.Starting tnslsnr: please wait.TNSLSNR for 32-bit Windows: Version .2.0 - ProductionSystem parameter file is D:oracleNETWOR

6、KADMINlistener.oraLog messages written to d:oraclenetworkloglistener_1522.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.5)(PORT=1522)Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.5)(PORT=1522)STATUS of the LISTENER-Alias listener_1522Version TNSLSNR for 3

7、2-bit Windows: Version .2.0 - ProductionStart Date 31-7月 -2011 19:37:17Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File D:oracleNETWORKADMINlistener.oraListener Log File d:oraclenetworkloglistener_1522.logListening Endpoints Summary.

8、(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.5)(PORT=1522)Services Summary.Service "ORCL" has 1 instance(s). Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service.The command completed successfully這樣，即使沒有其他監聽程序的存在，或者1521端口沖突。Oracle也會自動通過監聽程序LISTENER_1522來連接數據

9、庫ORCL。第3章 SQL Plus和PL/SQL1利用sqlplus登錄數據庫，并查看數據庫版本。利用SQL Plus登錄數據庫：C:>sqlplus / as sysdbaSQL*Plus: Release .2.0 - Production on 星期日 7月 10 18:43:50 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release .2.0 - ProductionWith the Partiti

10、oning, OLAP and Data Mining optionsSQL>在控制臺上打印出的信息，可以清楚看到Oracle數據庫的版本為.2.0。2在對數據庫進行重要操作時，首先應該確認數據庫身份，以免在其他數據庫上進行操作。嘗試利用SQL Plus顯示數據庫實例名稱。1）利用SQL Plus登錄數據庫：C:>sqlplus / as sysdbaSQL*Plus: Release .2.0 - Production on 星期日 7月 10 18:43:50 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.C

11、onnected to:Oracle Database 10g Enterprise Edition Release .2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>2）鍵入show parameter instance_name來查看實例名稱SQL> show parameter instance_nameNAME TYPE VALUE- - -instance_name string orcl3試著利用SQL Plus來創建一個新表test (id number, name varc

12、har2(20)。可以通過如下步驟利用SQL Plus來創建一個新表：（1）利用SQL Plus登錄數據庫ORCL。在Windows的【開始】|【運行】的【打開】文本框中輸入sqlplus scott/abc123orcl來登錄數據庫ORCL。（2）在SQL Plus命令行下輸入如下命令來創建新表test：create table test (id number, name varchar2(20);（3）在SQL Plus會出現表創建成功的提示，如圖所示。此時，證明表創建成功。第4章 Oracle數據庫1在數據庫中創建一個表lob_source(id number, description

13、clob)。將表lob_test的數據導入另外一個數據表lob_dest(id number, description clob)。1）創建表lob_sourceSQL> create table lob_source(id number, description clob);Table created.2)向表lob_source中插入測試數據SQL> insert into lob_source values(1, 'a clob text from source');1 row created.3)創建測試表lob_destSQL> create tab

14、le lob_dest(id number, description clob);Table created.4)向測試表lob_dest中插入測試數據，但是不包含clob類型的description列SQL> insert into lob_dest(id) values(1);1 row created.5）利用表lob_source中的description信息，更新表lob_dest中的description信息。SQL> update lob_dest set description = (select description from lob_source source

15、 where source.id = lob_dest.id);1 row updated.SQL> select * from lob_dest; ID DESCRIPTION- -1 a clob text from source該實例實際說明了針對lob類型的數據的操作方式。由于lob類型的數據的特殊性，因此在實現數據庫遷移時，如果遇到棘手的lob類型處理，可以考慮利用本例所演示的方法。2利用exp/imp方式，將數據庫orcl中users表的內容，遷移到數據庫test中。1)在數據庫orcl中，創建測試表usersSQL> create table users(user_i

16、d number, user_name varchar(20);Table created.SQL> insert into users values(1, 'allen');1 row created.SQL> insert into users values(2, 'mike');1 row created.SQL> commit;Commit complete. 2）導出表users到d:user.bakC:>exp system/abc123/192.168.16.5/orcl tables=(users) file='d

17、:/users.bak'Export: Release .2.0 - Production on 星期三 7月 13 00:06:22 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in ZHS16GBK character set and

18、 AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path . . exporting table USERS 2 rows exportedExport terminated successfully without warnings.3）將d:user.bak的內容導入數據庫testC:>imp system/abc123/192.168.16.5/test tables=(users) file='d:/users.bak'Import: Release .

19、2.0 - Production on 星期三 7月 13 00:10:09 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V via conventional pathimport done in ZHS16G

20、BK character set and AL16UTF16 NCHAR character set. importing SYSTEM's objects into SYSTEM. . importing table "USERS" 2 rows importedImport terminated successfully without warnings.3如果數據庫處于歸檔模式，那么，隨著時間的累積，歸檔日志將會占用很大空間。一旦達到默認大小20G，那么將導致數據庫掛起，在告警日志中一般會有如下提示：ORA-00257: archiver error. Con

21、nect internal only, until freed。利用修改參數db_recovery_file_dest_size的方式，快速解決數據庫無法歸檔的問題。1）查看默認空間大小SQL> show parameter db_recovery_file_dest_sizeNAME TYPE VALUE- - -db_recovery_file_dest_size big integer 2G2）修改其大小SQL> alter system set db_recovery_file_dest_size=3G scope=both;System altered.這一用法，適合于快

22、速處理現場由于歸檔日志過大導致的數據庫掛起。第5章 Oracle數據表對象1創建一個表空間testsize，其數據文件大小為2M，并設置自動增長尺寸為1M。在表空間中建立一個數據表，并向其中插入大量數據，觀察表空間文件的變化。1）創建一個大小為2M，自動增長尺寸為1M的表空間SQL> create tablespace testsize datafile 'e:databaseoracletestsize_data.dbf' size 2M 2 autoextend on next 1M 3 / Tablespace created2）創建一個數據表test_tables

23、pace_size(test_data varchar2(100)SQL> create table test_tablespace_size(test_data varchar2(100) tablespace testsize; Table created3）利用如下SQL語句向表test_tablespace_size中插入數據SQL> begin 2 for i in 1.100000 loop 3 insert into test_tablespace_size values('0123456789'); 4 end loop; 5 commit; 6 e

24、nd; 7 / PL/SQL procedure successfully completed4）此時，表空間文件testsize_data.dbf將增長為3M。2刪除表空間testsize，同時刪除其物理文件。刪除表空間應該使用drop tablespace命令，同時刪除物理文件，應使用including contents and datafiles。SQL> drop tablespace testsize including contents and datafiles;Tablespace dropped.3在數據庫中創建一個表test_bak，并向其中插入10條記錄。利用exp

25、/imp命令來實現該數據表的備份/恢復。1）在數據庫中創建表test_bak(id number)。SQL> create table test_bak(id number); Table created2）向其中插入10條數據。SQL> begin 2 for i in 1.10 loop 3 insert into test_bak values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completedSQL> commit; Commit complete3）利用exp命令備份該表C:>e

26、xp system/abc123/192.168.16.5/orcl tables=(test_bak) file='d:/test_bak.bak'Export: Release .2.0 - Production on 星期六 7月 16 14:51:54 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .2.0 - ProductionWith the Partitioning, OL

27、AP and Data Mining optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path . . exporting table TEST_BAK 10 rows exportedExport terminated successfully without warnings.4）在數據庫中刪除表test_bak。SQL> drop table test_bak; Table d

28、ropped5）將表test_bak重新導入數據庫C:>imp system/abc123/192.168.16.5/orcl tables=(test_bak) file='d:/test_bak.bak'Import: Release .2.0 - Production on 星期六 7月 16 14:54:24 2011Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .2.0 - Pro

29、ductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SYSTEM's objects into SYSTEM. . importing table "TEST_BAK" 10 rows importedImport terminated

30、 successfully without warnings.6）重新查詢表test_bakSQL> select * from test_bak; ID- 1 2 3 4 5 6 7 8 9 10 10 rows selected第6章 約束1查看表customers的主鍵狀況，如果有，則重建其主鍵，如果沒有，選擇其中一列創建主鍵。1）利用如下SQL語句查看表customers的主鍵狀況：SQL> select table_name, constraint_name, constraint_type, status from user_constraints 2 where ta

31、ble_name = 'CUSTOMERS' and constraint_type='P' TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS- - - -CUSTOMERS SYS_C005015 P ENABLED2）此時，在已有主鍵的情況下，首先刪除主鍵SYS_C005015。SQL> alter table customers drop primary key; alter table customers drop primary key ORA-02273: this unique/primary

32、 key is referenced by some foreign keys3）表customers中的主鍵與其他表的外鍵關聯，可以利用cascade選項來刪除關聯約束。SQL> alter table customers drop primary key cascade; Table altered4）重新創建基于列customer_id的主鍵。SQL> alter table customers add primary key(customer_id); Table altered2在數據庫中，創建表country(country_id, country_name)、city

33、(city_id, country_id，city_name)，并建立city.country_id到country.country_id的外鍵關聯。1）創建表country和citySQL> create table country(country_id number, country_name varchar2(50); Table created SQL> create table city(city_id number, city_name varchar2(50), country_id number); Table created2）在表country的country_

34、id列上創建主鍵約束SQL> alter table country add primary key(country_id); Table altered3）在表city上創建country_id到表country(country_id)的外鍵關聯SQL> alter table city add foreign key (country_id) references country(country_id); Table altered3驗證所建外鍵關聯的作用。1）嘗試向表city中添加城市信息。SQL> insert into city (city_id, city_nam

35、e, country_id) values (1, '北京', 1); insert into city (city_id, city_name, country_id) values (1, '北京', 1) ORA-02291: integrity constraint (SYSTEM.SYS_C005086) violated - parent key not found由于表country中并不存在country_id為1的值，因此，將導致添加失敗。2）向表country中添加country_id為1的信息。SQL> insert into cou

36、ntry values(1, '中國'); 1 row inserted3）再次為表citry添加城市信息。SQL> insert into city (city_id, city_name, country_id) values (1, '北京', 1); 1 row inserted第7章 視圖1在數據庫中不存在表animals(animal_id, animal_name, animal_type)的情況下，強制創建視圖vw_animal_cat(animal_id, animal_name)。該視圖中，僅含有animal_type=cat的貓科動物

37、的信息。SQL> create or replace force view vw_animal_cat(animal_id, animal_name) 2 as 3 select animal_id, animal_name, animal_type from animals where ainmal_type='cat' 4 / Warning: View created with compilation errors2創建一個物化視圖mv_user_objects(object_type, objectCount)，其數據來源于user_objects(owner,

38、count(object_name)，也就是對每種object類型統計其object的數目。1）因為物化視圖中，不能使用子查詢。而關系視圖又被當做子查詢看待。因此，首先需要獲得user_objects的拷貝，創建一個新表tmp_user_objects。SQL> create table tmp_user_objects as select * from user_objects; Table created2）利用新表tmp_user_objects來創建物化視圖SQL> create materialized view mv_user_objects 2 as 3 select

39、 object_type, count(object_name) object_count from tmp_user_objects 4 group by object_type 5 / Materialized view createdSQL> select * from mv_user_objects; OBJECT_TYPE OBJECT_COUNT- -FUNCTION 7INDEX 189INDEX PARTITION 31LOB 24PACKAGE 2PACKAGE BODY 2PROCEDURE 9QUEUE 4SEQUENCE 25SYNONYM 8TABLE 197T

40、ABLE PARTITION 27TRIGGER 16TYPE 4VIEW 16 15 rows selected3分別啟用/禁用物化視圖mv_user_objects，來查看select object_type, count(object_name) object_count from tmp_user_objects的執行效率。1）對于SQL語句，select owner, count(object_name) from dba_objects group by owner未啟用查詢重寫功能時，其執行計劃如下所示：2）利用enable query rewrite選項，啟用物化視圖mv_us

41、er_objects的查詢重寫功能alter materialized view mv_user_objects enable query rewrite3）重新執行相同的SQL語句，查看此時的執行計劃第8章 函數與存儲過程1創建一個函數is_date，并傳入一個字符串函數。如果該字符串可以轉換為“YYYY-MM-DD hh24:mi:ss”形式的日期，那么返回為真，否則返回為假。1）首先利用create or replace function命令創建is_date函數SQL> create or replace function is_date (param varchar2) ret

42、urn varchar2 is 2 d date; 3 begin 4 d:=to_date (nvl (param, ' '), 'yyyy-mm-dd hh24:mi:ss'); 5 return 'TRUE' 6 7 exception 8 when others then 9 return 'FALSE' 10 end; 11 / Function createdto_date (nvl (param, ' '), 'yyyy-mm-dd hh24:mi:ss')用于將字符串參數param轉

43、換為日期時間型，如果轉換成功，則返回“TRUE”；exception則用于處理異常情況，如果發生異常，函數將返回 “TRUE”。2）可以利用如下語句測試is_date()函數。SQL> select is_date('2010') as is_date from dual; IS_DATE-FALSESQL> select is_date('abc') as is_date from dual; IS_DATE-FALSESQL> select is_date('20100512') is_date from dual; IS_

44、DATE-TRUE2創建一個存儲過程find_student，并傳入參數學生姓名(studentName)，打印表students中所有同名的學生信息。如果未找到同名學生，那么打印“無名為xxx的學生”。1）利用如下SQL語句創建存儲過程find_studentSQL> create or replace procedure find_student(studentName varchar2) 2 as 3 4 begin 5 declare student_count number; 6 begin 7 select count(*) into student_count from s

45、tudents where student_name=studentName; 8 if student_count>0 then 9 dbms_output.put_line('共找到' | student_count | '個名為' | studentName | '的學生！'); 10 else 11 dbms_output.put_line('未找到名為' | studentName | '的學生！'); 12 end if; 13 end; 14 end; 15 / Procedure create

46、d2）嘗試查找名為“張三”的學生SQL> exec find_student('張三'); 共找到3個名為張三的學生！ PL/SQL procedure successfully completed3）嘗試查找名為“李四”的學生SQL> exec find_student('李四'); 未找到名為李四的學生！ PL/SQL procedure successfully completed3利用PL/SQL Developer的Debug功能調試存儲過程find_student。1）在PL/SQL Developer的Procedures下，找到存儲過

47、程find_student。2）在右鍵菜單中選擇【Test】3）在參數欄內填入要傳入的參數，并單擊Debug按鈕或者按下F9。4）此時，調試步驟按鈕欄將變為可用。從左至右依次為：Run（繼續執行，直至程序結束，或者下一個斷點）Step into（進入存儲過程/函數內部）Step Over（執行當前語句，在下一條語句處停止）Step out（跳出當前存儲過程/函數）Run to next exception（執行直至下次拋出異常）4）利用這5個按鈕，即可進行存儲過程的調試。第9章 游標1聲明一個游標cu_sutdnet，并向該游標傳遞參數studentName，來獲得所有與參數同名的學生信息。對

48、該游標依次執行打開、獲取、關閉的步驟來依次打印獲得的學生信息。1）聲明帶有參數的游標時，應將參數列表置于小括號內declare cursor cu_student_id_name(studentName)2）聲明變量臨時存取學生姓名和學號 student_id students.student_id%type; student_name students.student_name%type; 3）打開游標時，傳入參數studentNameopen cu_student_id_name('張三'); 4）獲取游標數據fetch cu_student_id_name into st

49、udent_id, student_name; 5）循環打印學生信息while cu_student_id_name %found loop dbms_output.put_line(student_id | ':' | student_name); fetch cu_student_id_name into student_id, student_name; end loop;6）關閉游標close cu_student_id_name; 7）執行結果如下SQL> declare cursor cu_sutdnet(studentName in varchar2) is

50、 2 select student_id, student_name 3 from students where student_name=studentName; 4 5 student_id students.student_id%type; 6 student_name students.student_name%type; 7 begin 8 open cu_sutdnet('張三'); 9 fetch cu_sutdnet into student_id, student_name; 10 11 while cu_sutdnet%found loop 12 dbms_

51、output.put_line(student_id | ':' | student_name); 13 fetch cu_sutdnet into student_id, student_name; 14 end loop; 15 16 close cu_sutdnet; 17 end; 18 / 17:張三18:張三21:張三 PL/SQL procedure successfully completed2聲明一個名為studentname的變量，然后利用cursor for游標來實現習題1的功能。通過修改studentname的值，來模擬傳入參數功能。SQL> begin 2 declare 3 studentname varchar2(20); 4 begin 5 studentname := '張三' 6 for student in (select * from students where student_name = studentname) loop 7 dbms_output.put_line(student.student_id | ':' | 8 student.student_n