PL/SQL :PL/SQL


建立 PL/SQL 匿名區塊 ? :Begin Executable Section End； 建立 PL/SQL 匿名區塊 ? [Declare Declare Section] [Exception Exception handling Section]


顯示訊息 ? :顯示訊息 ? 在新一列顯示資料 : DBMS_OUTPUT.PUT_LINE(…);。 在同一列顯示資料 : DBMS_OUTPUT.PUT(…);。 在SQL PLUS中顯示之前，輸入 : Set ServerOutput On


設定變數值 ? :設定變數值 ? 利用 := ，即 Variable := …; 如 : Test_tmp := 2;


註解表示 ? :註解表示 ? 單行註解 : -- Statement; 多行註解 : /* Statement */


條件式設定? :條件式設定? IF Condition Then Executable Section elsIF Condition2 Then Executable Section … else Executable Section End IF;


迴圈設定? :迴圈設定? For迴圈 For Variable In Start..End Loop Executable Section End Loop; While迴圈 While Condition Loop Executable Section End Loop;


相同資料型態宣告 ? :相同資料型態宣告 ? 與 Field Data_type 同型態 : Variable Table.Column%Type; 與 Record Data_type 同型態 : Variable Table%RowType;


陣列宣告 ? :陣列宣告 ? 定義 : Type Type_Name Is Table of Data_Type Index by Binary_Integer; 宣告 Variable Type_Name; 使用 : Variable(n)


在Select時，Lock查詢資料 ? :在Select時，Lock查詢資料 ? Select … For Update [of Column1，Column2…] 在Select最後面加上 For Update敘述Lock查詢資料，直到Commit or Rollback結束Lock。 使用敘述 : 不輸入，Lock全部的查詢欄位。 P.S. 若要在Select後即自動解除Lock，可在For Update 之後加上No Wait。


例外處理 ? :例外處理 ? Exception When exception1 Then Executable Section When exception2 Then Executable Section … End ;


自定例外處理 (方式一) ? :自定例外處理 (方式一) ? 宣告 Variable Exception; 程式呼叫 Raise Variable; Exception When Variable Then


自定例外處理 (方式二) ? :自定例外處理 (方式二) ? 宣告 Variable Exception; PRAGMA Exception_init( Variable, error_num ); 程式呼叫 Raise_Application_Error( error_num, Variable ); Exception When Variable Then


建立 Stored Procedure ? :Begin Executable Section End； 建立 Stored Procedure ? [Declare Section] [Exception Exception handling Section] Create or Replace Procedure Procedure_Name [ (arg1，arg2，…) ] As


建立 Stored Procedure注意事項 ? :建立 Stored Procedure注意事項 ? Argument mode有三 : In(default), Out, In Out。 Argument 的任何資料型態不能指定長度。 宣告區變數的資料型態為char or varchar ，則須指定長度。 宣告區的變數為Not Null時，須指定預設值，如 : AA varchar(20) not null := ‘test’;


呼叫 Stored Procedure ? :呼叫 Stored Procedure ? 有兩種呼叫方式 : 1.位置表示法 : Procedure( value1，value2，…); 2.命名表示法 : Procedure( arg1 => value1，arg2 => value2，…); 其中 位置表示法的argument與Procedure的argument順序要一樣。 命名表示法的argument與Procedure的argument，位置可不同，但名稱要一樣。


建立 Stored Function ? :Begin Executable Section End； 建立 Stored Function ? [Declare Section] [Exception Exception handling Section] Create or Replace Function Function_Name [ (arg1，arg2，…) ] Return Data_Type As


Function與Procedure的差異點 ? :Function與Procedure的差異點 ? Function須指定Return Value。 Function的Argument mode只有用 In(default)。 Function只能使用位置表示法。


建立 Stored Package 規格? :End Package_Name； 建立 Stored Package 規格? [Procedure Procedure_Name(…);] Create or Replace Package Package_Name As [Function Function_Name(…) Return Data_Type;] [Variable Data_Type;]


建立 Stored Package 主體? :End Package_Name； 建立 Stored Package 主體? Procedure Procedure_Name(…) as Begin End; Create or Replace Package Body Package_Name As Function Function_Name(…) Return Data_Type as Begin End;


建立 超負載 Procedure 規格? :End Package_Name； 建立 超負載 Procedure 規格? { [Procedure Procedure_Name(arg1…);] [Procedure Procedure_Name(arg1，arg2…);] } Create or Replace Package Package_Name As { [Function Function_Name(arg1…) Return Data_Type;] [Function Function_Name(arg1，arg2…) Return Data_Type;] } [Variable Data_Type;] 超負載 : Procedure or Function 擁有相同名稱，卻有不同的參數或資料型態。


建立 Trigger ? :Begin Executable Section End； 建立 Trigger ? [Declare Section] [Exception Exception handling Section] Create or Replace Trigger Trigger_Name {Before | After} Trigger_Event On Table_Name [For Each Row] [When (Condition) ]


Trigger_Event ? :Trigger_Event ? Insert : 在新增時觸發。 Delete : 在刪除時觸發。 Update [of Column] : 在修改時觸發。 若有指定, 則在修改該欄位時才觸發


Trigger ~ For Each Row ? :Trigger ~ For Each Row ? 敘述觸發 : 在Trigger中無加入For Each Row 每一筆資料皆觸發一次，但不能使用 :old 與 :new 。 列觸發 : 在Trigger中加入For Each Row 每一筆資料皆觸發一次，也只有此觸發才能使用 :old 與 :new。 (建議用此) 下一頁


Trigger ~ Old, New ? :Trigger ~ Old, New ? Insert Update Delete Old New V V V V Old : 異動前的資料。 New : 異動後的資料。 在When子句中引用不加“ : ”，Old.dept 在Body子句中引用須加“ : ”，:Old.dept


Trigger ~ When條件性觸發 ? :Trigger ~ When條件性觸發 ? 只適用於列觸發，故在for each row之後加上 When ( Condition )，如 : When ( old.qty > 30 )


Trigger ~ Enable and Disable ? :Trigger ~ Enable and Disable ? Single Trigger : Alter Trigger Trigger {Enable | Disable}; All Trigger of Table : Alter Table Table_name {Enable | Disable} All Triggers;


游標(Cursor) ? :游標(Cursor) ? 內隱游標(Implicit Cursor) : 只能單一紀錄查詢。 外顯游標(Explicit Cursor) : 可以多筆查詢。


內隱游標(Implicit Cursor) ? :內隱游標(Implicit Cursor) ? 使用 Select敘述但未定義游標，Select敘述將被當成內隱游標。 使用內隱游標須注意 : 查詢資料必須只傳回一筆，無資料或兩筆以上皆不可。 無資料 : 觸發NO_DATA_FOUND例外處理。 兩筆以上 : 觸發TOO_MANY_ROWS例外處理。


外顯游標(Explicit Cursor) ? :外顯游標(Explicit Cursor) ? 利用外顯游標查詢多筆資料，其宣告如下 : Cursor Cursor_Name [ (arg1，arg2，…) ] As Select …; 使用方式 : Open Cursor: 執行 Cursor的 Select查詢。 Fetch Cursor into Variable… : 將 Cursor擷取的資料放入變數中。 Close Cursor : 釋放與 Cursor相關的記憶體。


游標監控 ? :游標監控 ? 使用方式如右 : SQL%Property Property 如下 : Found : 若擷取資料成功，則傳回True值。 NotFound : 與Found相反。 IsOpen : 若 Cursor已開啟，則傳回True值。 RowCount : 目前擷取資料的筆數。


游標參數的使用 ? :游標參數的使用 ? 宣告 : Cursor Cursor_Name(arg1，arg2…) As Select …; 與無參數的差別使用 : Open Cursor(arg1，arg2，…) ;


游標變數的宣告 ? :游標變數的宣告 ? 定義強型態游標型態 : 單一型態任何查詢 Type Cursor_Type Is Ref Cursor Return Data_Type; 定義弱型態游標型態 : 任何型態任何查詢 Type Cursor_Type Is Ref Cursor; 使用步驟 : 1.定義游標型態。 2.宣告游標變數。


游標變數的使用 ? :游標變數的使用 ? 開啟方式 1 : 靜態 SQL Open Cursor for Select …; 開啟方式 2 : 動態 SQL Open Cursor for 'Select …'; 擷取資料 : 如以往使用方式。


游標變數的限制 ? :游標變數的限制 ? 1.游標變數無法使用游標參數。 2.游標變數無法使用 for update 查詢。 3.游標變數無法使用 for … loop … end loop;。


使用For擷取Cursor資料 ? :使用For擷取Cursor資料 ? For Variable in Cursor Loop Executable Section End Loop ; 使用敘述 : 其中不用宣告，直接使用即可。


執行 Dynamic SQL - Select ? :執行 Dynamic SQL - Select ? 敘述 : Cursor_id := DBMS_SQL.OPEN_CURSOR; -- 開啟Cursor --剖析 DBMS_SQL.PARSE( Cursor_id, Sql_str, DBMS_SQL.V7 ); -- 定義Column n of Cursor 的型態為 的資料型態, 若是char型態則設定長度 DBMS_SQL.DEFINE_COLUMN( Cursor_id, n, Column [, char_len] ) -- 執行Dynamic sql Count := DBMS_SQL.EXECUTE( Cursor_id ); Loop -- 擷取 Exit When DBMS_SQL.FETCH_ROWS( Cursor_id ) = 0; -- 將 Column n of Cursor 的資料設定給Variable DBMS_SQL.COLUMN_VALUE( Cursor_id, n, Variable ); End Loop; DBMS_SQL.CLOSE_CURSOR( Cursor_id ); -- 釋放Cursor 點選此處看範例 放置 SQL Statement 宣告三個變數: Cursor_id number; Count number; Sql_str varchar2(100);


執行 Dynamic SQL - DML ? :執行 Dynamic SQL - DML ? 敘述 : Cursor_id := DBMS_SQL.OPEN_CURSOR; -- 開啟Cursor Sql_str := ‘Insert into Table Values( :var1, :var2,…)’; --剖析 DBMS_SQL.PARSE( Cursor_id, Sql_str, DBMS_SQL.V7 ); -- 給定SQL字串中輸入變數的設定值 (輸入變數前面有冒號者, 而 value可為變數 or 數字) DBMS_SQL.BIND_VARIABLE( Cursor_id, ‘:var1’, value ); -- 執行Dynamic sql Count := DBMS_SQL.EXECUTE( Cursor_id ); -- 釋放Cursor DBMS_SQL.CLOSE_CURSOR( Cursor_id ); 點選此處看範例. 放置 SQL Statement 宣告三個變數: Cursor_id number; Count number; Sql_str varchar2(100);


執行 Dynamic SQL – Begin…End ? :執行 Dynamic SQL – Begin…End ? 敘述 : Cursor_id := DBMS_SQL.OPEN_CURSOR; -- 開啟Cursor Sql_str := ‘Begin…:var1 … :var2… End;’; -- 分號一定要有 --剖析 DBMS_SQL.PARSE( Cursor_id, Sql_str, DBMS_SQL.V7 ); -- 給定SQL字串中輸入變數的設定值 (如:var1為輸入變數) DBMS_SQL.BIND_VARIABLE( Cursor_id, ‘:var1’, value ); -- 執行Dynamic sql Count := DBMS_SQL.EXECUTE( Cursor_id ); -- 若有輸出值時, 則將輸出變數值設定接收變數 (如:var2為輸出變數) DBMS_SQL.VARIABLE_VALUE( Cursor_id, ‘:var2’, 接收variable ); -- 釋放Cursor DBMS_SQL.CLOSE_CURSOR( Cursor_id ); 放置 SQL Statement 宣告三個變數: Cursor_id number; Count number; Sql_str varchar2(100);


設定預交排程 ? :設定預交排程 ? 宣告 : Job_num Number; 敘述 : DBMS_JOB.SUBMIT ( Job_num, -- Job_Number '內容;', -- 預定執行的程序 SYSDATE + n, -- 第一次執行時間, 一分鐘為1/1440 'SYSDATE + m', -- 下一次執行的時間, ‘NULL’表示只執行一次 FALSE ); 請設定 INITSID.ORA檔案之JOB_QUEUE_PROCESSES > 0 才能執行


刪除預交排程 ? :刪除預交排程 ? 敘述 : DBMS_JOB.REMOVE ( Job_num );


Q & A :Q & A mail: tomkuo139@yahoo.com.tw msn: tomkuo1234567890@hotmail.com blog: http://tomkuo139.blogspot.com/