ORACLE資料庫開發經驗總結

ORACLE資料庫作為大型資料庫管理系統，近年來一直佔有世界上高階資料庫的最大份額，其強大而完善的資料庫管理功能，以及ORACLE公司推陳出新的不斷努力，一直成為IT業界矚目的焦點。嶺澳核電站的資料庫平臺採用了ORACLE7.3作為後端平臺，前端選擇了ORACLE公司的DEVELOPER 2000 及DESIGNER 2000作為開發工具，採用了目前流行的CLIENT/SERVER模式。本人在ORACLE系統的開發中，就ORACLE的整套開發工具提出一些自己的體會，供同行參考。

---- 一. ORACLE SQL PLUS 使用技巧:

---- ①查詢重複記錄:

SELECT DRAWING,DSNO FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5
_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND 
EM5_PIPE_PREFAB.DSNO=D.DSNO);

---- 執行上述SQL語句後就可以顯示所有DRAWING和DSNO相同且重複的記錄。

---- 刪除重複記錄:

DELETE FROM EM5_PIPE_PREFAB 
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5
_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND 
EM5_PIPE_PREFAB.DSNO=D.DSNO);

---- 執行上述SQL語句後就可以刪除所有DRAWING和DSNO相同且重複的記錄。

---- ② 快速編譯所有檢視

---- 當在把資料庫倒入到新的伺服器上後(資料庫重建)，需要將檢視重新編譯一遍，因為該表空間檢視到其它表空間的表的連線會出現問題，可以利用PL/SQL的語言特性，快速編譯。

 SQL >SPOOL ON.SQL
 SQL >SELECT ‘ALTER VIEW ‘||TNAME||’
 COMPILE;’ FROM TAB;
 SQL >SPOOL OFF
然後執行ON.SQL即可。
 SQL >@ON.SQL
當然，授權和建立同義詞也可以快速進行，如：
 SQL >SELECT ‘GRANT SELECT ON  ’
||TNAME||’  TO USERNAME;’ FROM TAB;
 SQL >SELECT ‘CREATE SYNONYM  
‘||TNAME||’  FOR USERNAME.’||TNAME||’;’ FROM TAB;

③ 用外聯接提高表連線的查詢速度
  在作表連線(常用於檢視)時，常使用以下方法來查詢資料:
SELECT  PAY_NO, PROJECT_NAME
FROM   A
WHERE  A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE >=120000);

---- 但是若表A有10000條記錄，表B有10000條記錄，則要用掉30分鐘才能查完，主要因為NOT IN要進行一條一條的比較，共需要10000*10000次比較後，才能得到結果。該用外聯接後，可以縮短到1分左右的時間:

SELECT  PAY_NO,PROJECT_NAME 
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL 
AND B.VALUE >=12000;

---- ④ 怎樣讀寫文字型作業系統檔案

---- 在PL/SQL 3.3以上的版本中，UTL_FILE包允許使用者通過PL/SQL讀寫作業系統檔案。如下：

DECALRE
   FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
   FILE_HANDLE:=UTL_FILE.FOPEN(
‘C:\’,’TEST.TXT’,’A’);
   UTL_FILE.PUT_LINE(FILE_HANDLE,’
HELLO,IT’S A TEST TXT FILE’);
   UTL_FILE.FCLOSE(FILE_HANDLE);
END;

---- 相關UTL_FILE資料庫包詳細資訊可以參見相關資料。

---- ⑤ 怎樣在資料庫觸發器中使用列的新值與舊值

---- 在資料庫觸發器中幾乎總是要使用觸發器基表的列值，如果某條語句需要某列修改前的值，使用:OLD就可以了，使用某列修改後的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。

---- 二.ORACLE DEVELOPER 2000使用技巧：

---- ① 改變FORM(FMX模組)執行時的Runform4.5的題頭:

---- DEVELOPER2000中FMX預設題頭為：Developer/2000 Forms Runtime for Windows 95 / NT 你可以改為自己定義的標題,

---- 1. 在Form級觸發器中新增觸發WHEN-NEW-FORM-INSTANCE

---- 2. 在此觸發器中寫如下程式碼：

set_window_property(FORMS_MDI_WINDOW,TITLE,'POINT
 SYSTEM 歡迎使用');

---- ② 如何隱藏選單中的window選項:

---- 在建立自己的選單時，最後選項總有window項，下面介紹如何去掉它,

---- 1. 建立一個Menu

---- 2. 在Menu中建立一個Item,命名為WINDOW。

---- 3. 設定該Item屬性如下:

----

Menu Item Type:Magic 
Command Type:Null 
Magic Item:Window 
Lable:為空

---- ③ 怎樣建立動態下拉選單List

---- Developer 2000 中的列表是通過設定相關屬性而完成資料項的列表設定的，但那只是靜態的，有時你想讓某項成為動態的列表，隨輸入資料的改變而改變，就需要動手去編個小程式。下面詳細介紹怎樣去做:

---- 有塊EBOP_CABLE_ACCOUNT，下有SPECIFICATION資料項，當一進入該模組時，就將SPECIFICATION項在資料庫中儲存的值動態顯示出來，先在Form4.5中建立一個PRCEDURE，命名為DYN_LIST:

PROCEDURE DYN_LIST IS
	CURSOR C1 IS 
SELECT DISTINCT(SPECIFICATION) 
FROM EBOP_CABLE_ACCOUNT;
	CNT    NUMBER;
     i      NUMBER; 
	TNAME  EBOP_CABLE_
ACCOUNT.SPECIFICATION%TYPE;
BEGIN
	CLEAR_LIST('EBOP_CABLE_
ACCOUNT.SPECIFICATION');
	SELECT COUNT(DISTINCT
(SPECIFICATION)) INTO CNT FROM EBOP
_CABLE_ACCOUNT;
        open C1;
	FOR i IN 1..CNT LOOP
        FETCH C1 INTO TNAME;
        EXIT WHEN C1%NOTFOUND 
OR C1%NOTFOUND IS NULL;
             ADD_LIST_ELEMENT
('EBOP_CABLE_ACCOUNT
.SPECIFICATION',i,TNAME,TNAME);
	END LOOP;
        DELETE_LIST_ELEMENT
('EBOP_CABLE_ACCOUNT.SPECIFICATION',CNT+1);
	CLOSE C1;
END;
然後在FORM的WHEN-NEW
-FORM-INSTANCE觸發子中加入一行：
DYN_LIST;

---- 這樣一進入該FMX，就會動態重新整理該列表。除此之外，SPECIFICATION資料項改為列表項。

---- ④ 當顯示多條記錄且資料項特別多時，如何組織錄入及顯示介面:

---- 如上圖所示，PRN程式碼及裝置程式碼在畫布1(CONTENT型)上，其它資料項在畫布2(STACK型)上，所有資料項為一個表的列或一個塊的資料項。在拉動水平滾動條時或用TAB或敲Enter鍵時，將看到全部資料項。這種排布方法適用於資料項特別多又想顯示多條記錄時用。主要製作順序為：先建立兩個畫布，畫布1(CONTENT型)，畫布2(STACK型)，然後建立塊，選畫布時用畫布1，這樣所有項都顯示在畫布1上，然後選中除PRN程式碼及裝置程式碼之外的所有資料項，選TOOLS選單下的PROPERTIES選項，將這些資料項的CANVAS屬性選為畫布2(STACK型)，然後調整整體位置就可以了。

---- ⑤ 如何在FORM的受限觸發子中提交儲存資料

---- 在FORM中很多觸發子是不能用COMMIT WORK語句的，當你在該觸發子中使用了UPDATE，DELETE等操作並想立即存檔時，就需要COMMIT WORK語句了。首先在伺服器端建立DB_SQL_COMMIT這個過程(採用ORACLE7.3資料庫)，

PROCEDURE DB_SQL_COMMIT IS
     source_cursor integer;
     ignore        integer;
V7 NUMBER :=2;
BEGIN
source_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(source_cursor,'COMMIT WORK',V7);
ignore:=dbms_sql.execute(source_cursor);
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END;

---- 然後在FORM中該觸發子中呼叫過程DB_SQL_COMMIT;就可以了，當然你可以根據自己需要將該過程加入引數，這樣通過引數可以得到執行DML語句的許可權。

---- ⑥ 如何在FORM中實現某資料項自動按記錄序號加一操作

---- 設塊名為VO,要操作的資料項為VO_ID,在該塊中建立塊級觸發子WHEN-CREATE- RECORD，加入如下程式碼：

:VO_ID:=:System.Trigger_Record;

---- 這樣每當生成新記錄時VO_ID就會自動加一了。

---- ⑦ 如何在一個FORM中呼叫另一個FORM，或在一個塊中呼叫另一個塊時顯示特定的記錄有時使用者會要求在呼叫另一個FORM時，只顯示相關的記錄，舉例如下，在一個FORM的塊中有一個按鈕，在按鈕觸發子中加入如下程式碼：

DECLARE
       PM 
 PARAMLIST;
BEGIN
	PM:=GET_PARAMETER_LIST('PM');
	IF NOT ID_NULL(PM) THEN
           DESTROY_PARAMETER_LIST('PM');
     END IF;
     PM:=CREATE_PARAMETER_LIST('PM');
......................
   ADD_PARAMETER(PM,'THE_WHERE',
TEXT_PARAMETER,'EM_NAME=''EM4'' 
AND EM_PROJECT_NAME=''支架預製''');
   OPEN_FORM('PAYMENT',ACTIVATE,SESSION,PM);
END；

---- 其中EM_NAME，EM_PROJECT_NAME為本FORM某塊的資料項，PAYMENT為要呼叫的FORM模組。這樣通過傳遞引數列表就可以得到想要的結果。在FORM. PAYMENT.FMB中，建立一引數THE_WHERE，CHAR型，長1000，然後在PAYMENT.FMB中建立FORM級觸發子WHEN-NEW-FORM-INSTANCE，在該觸發子中加入以下語句：

IF :PARAMETER.THE_WHERE IS NOT NULL THEN
SET_BLOCK_PROPERTY('PAYMENT',
DEFAULT_WHERE,:PARAMETER.THE_WHERE);
END IF;

---- 其中PAYMENT為要顯示的塊，這樣通過引數傳遞就得到想要的某些特定條件的資料了。

---- ⑧ 在FORM中當有主從塊時，連續輸入記錄如何避免被不斷的提示儲存：

---- 每輸入一條主記錄和若干條該主記錄的從記錄後，此時再導航到主塊輸下一條記錄，FORM就會提示你是否要儲存記錄，而你並不希望FORM提示，讓它自動儲存，此時你可以到Program Units中找到過程PROCEDURE Clear_All_Master_Details，然後在這個過程中找到語句

Clear_Block(ASK_COMMIT); 

---- 將其改為Clear_Block(DO_COMMIT);就可以了。

---- ⑨ 在Report開始時選擇排序項:

---- 在報表開始的Parameter Form中選擇報表按哪個資料項排序，

---- 1. 先在USER PARAMETER 中建立SORT引數，為字元型，長20。

---- 2. 初始值選’責任方’,然後將這四個值輸入到DATA SELECTION中，形成列表。

---- 3. 然後處理QUERY中的SQL語句:

select CHARGER,FCO_NO,EM_NAME,FCO
_NO,DESCRIPTION, FCR_POINT 
from FCR_MAIN 
ORDER BY DECODE(:SORT,'責任方',CHARGER,'FCO號',
FCO_NO,'FCR號',FCR_NO,'FCR號',EM_NAME)

---- ⑩ 在Developer 2000中如何讀寫作業系統檔案

---- 在用Developer 2000的開發工具開發應用程式時，經常碰到需要讀寫外部檔案的問題，可以用ORACLE 帶的包TEXT_IO來完成這項需求。例如:

   DECLARE
	IN_FILE		TEXT_IO.FILE_TYPE;
	OUT_FILE		TEXT_IO.FILE_TYPE;
	LINE_BUFER	VARCHAR2(80);  
   /*若不用IN_FILE,可以將各欄位聯接在一起賦值給此變數*/
   BEGIN
	IN_FILE:=TEXT_IO.FOPEN
(‘C:\TEMP\TEST1.TXT’,’r’);
	OUT_FILE:=TEXT_IO.FOPEN
(‘C:\TEMP\TEST2.TXT’,’w+’);
     LOOP
        TEXT_IO.GET_LINE(IN_FILE,LINE_BUFER);
        TEXT_IO.PUT(LINE_BUFER);
        TEXT_IO.NEW_LINE;
        TEXT_IO.PUT_LINE(OUT_FILE,LINE_BUFER);
     END LOOP;
     EXCEPTION
         WHEN no_data_found THEN
TEXT_IO.PUT_LINE(‘CLOSING THE FILE ,PLEASE WAITING....’);
TEXT_IO.FCLOSE(IN_FILE);
TEXT_IO.FCLOSE(OUT_FILE);
   END;

---- 三.資料庫管理

---- ① 在刪除一個表中的全部資料時，須使用TRUNCATE TABLE 表名;因為用DROP TABLE，DELETE * FROM 表名時，TABLESPACE表空間該表的佔用空間並未釋放，反覆幾次DROP，DELETE操作後，該TABLESPACE上百兆的空間就被耗光了。

---- ② 資料庫檔案的移動方法

---- 當想將資料庫檔案移動到另外一個目錄下時，可以用ALTER DATABASE命令來移動(比ALTER TABLESPACE適用性強)：

---- 1. 使用SERVER MANAGER關閉例項.

SVRMGR > connect internal;
SVRMGR > shutdown;
SVRMGR >exit;

---- 2. 使用作業系統命令來移動資料庫檔案位置(假設這裡作業系統為SOLARIS 2.6). 在UNIX中用 mv命令可以把檔案移動到新的位置，

#mv /ora13/orarun/document.dbf /ora12/orarun

---- 3. 裝載資料庫並用alter database命令來改變資料庫中的檔名.

SVRMGR > connect internal;
SVRMGR > startup mount RUN73;
SVRMGR > alter database rename file
       > ‘/ ora13/orarun/document.dbf’
       > ‘/ ora12/orarun/document.dbf’;

---- 4. 啟動例項.

SVRMGR > alter database open;

---- (huangfutong@china.com)

---- ORACLE資料庫開發經驗總結

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10742223/viewspace-230933/，如需轉載，請註明出處，否則將追究法律責任。