Oracle實訓進階編程

標籤：

進階資料庫編程

1、PL/SQL 簡介：
PL/SQL 代表 Procedural Language/SQL（Procedural Language：過程語言）
它是對 SQL 的擴充

嚴密的安全性
對客戶機和伺服器之間的應用程式邏輯進行分隔，用戶端只執行預存程序

2、PL/SQL 引擎
處理 PL/SQL 塊並將其分離為 SQL 陳述式及過程語句：
a.將過程語句發送到過程語句執行器以進行處理
b.將 SQL 陳述式發送到 SQL 陳述式執行器以進行處理

3.PL/SQL塊的結構

a.PL/SQL 的各個組成部分:
聲明部分
可執行部分
異常處理部分

b.PL/SQL 塊的結構:
DECLARE
declarations
BEGIN
executable statements
EXCEPTION
handlers
END;

其中，declarations 是聲明，executable statements 是可執行語句，handlers 是處理常式

 

4.輸出：DBMS_OUTPUT.PUT_LINE();

5.如何定義變數:
格式：declare 變數名1 資料類型1：=初始值1;
declare mynum varchar2(20):=‘abc‘;

6.對變數賦值有兩種,分別見下面的例子
a、:=進行賦值
b、 select into 變數名進行賦值
第一種賦值:
Declare
n number;str varchar2(20);
begin
n:=100*3;
str:=‘&abc‘;--彈出對話方塊，擷取輸入的賦值給變數str
dbms_output.put_line(n||‘,’||str);--其中||是連接字串的意思，並列印到介面
end;

第二種賦值:
select tname into tnames from temp where tid=2;
--列印到介面
dbms_output.put_line(‘類型名稱:‘|| tnames);

7、條件控制語句：
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF

IF <條件> THEN
語句;
END IF;

IF <條件> THEN
語句;
ELSE
語句;
END IF;

IF <條件1> THEN
語句;
ELSIF <條件2> THEN
語句;
ELSIF <條件3> THEN
語句;
ELSE
語句;
END IF;

8.迴圈控制語句：
使用loop,無條件迴圈,從1累加到100，採用exit配合if退出
declare
i number:=1; --定義迴圈次數變數i
total number:=0; --定義累加結果變數total
begin
loop
total:=total+i;
i:=i+1;
if i>100 then
exit;
end if;
end loop;
dbms_output.put_line(‘最終結果：‘||total);
end;

9、異常
當在 PL/SQL 程式中出現錯誤時，將引發異常
在出現錯誤時，正常執行將停止，控制權轉移到異常處理部分
declare
i number:=0; n number:=0;
begin
i:=100/n;--這裡會報錯，除數為0
dbms_output.put_line (‘結果：‘||i);
Exception
--當不知道是什麼錯誤的時候就寫others
when others then
--sqlerrm這個會列印錯誤資訊
dbms_output.put_line(‘異常：‘||sqlerrm);
end;

10、預存程序的定義和使用
實際開發中通常把複雜的業務封裝在過程中。
建立過程的文法：

CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration> 聲明變數declare關鍵字
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;

註：通過處理我們一般都想得到一個或者多個結果，如何通過預存程序帶出一個或多個結果呢？
過程參數的三種模式：
IN
用於接受調用程式的值
預設的參數模式
OUT
用於向調用程式傳回值
IN OUT
用於接受調用程式的值，並向調用程式返回更新的值
執行預存程序可以使用EXEC命令或者在其它過程中調用。

11、儲存函數的定義和使用
我們已經很多ORACLE內建的函數，那麼能不能自己定義函數呢？
函數是可以傳回值的命名的 PL/SQL 子程式。
建立函數的文法：
CREATE [OR REPLACE] FUNCTION
function name [(param1,param2)]
RETURN datatype IS|AS -- 傳回值類型
[local declarations]
BEGIN
Executable Statements;
RETURN result; -- 記得要返回結果
EXCEPTION
Exception handlers;
END;
類比java 語言中的方法定義
刪除函數 drop function fun_name;

12.觸發器的定義和使用
觸發器是當特定事件出現時自動執行的預存程序
特定事件可以是執行更新的DML語句和DDL語句
觸發器不能被顯式調用
觸發器的功能：
自動產生資料
自訂複雜的安全許可權
提供審計和日誌記錄
啟用複雜的商務邏輯

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;

注意：
:new 和 :old 是oracle觸發器裡的行處理時的新值和舊值的行記錄。

是固定用法。

可以
UPDATE E1 SET DEPTNO = ‘ABC’ || :NEW.DEPTNO WHERE DEPTNO =:OLD.DEPTNO;

也可以在觸發器中定義一個變數 v_aaa，然後賦值：

v_aaa := :NEW.DEPTNO;

Oracle實訓進階編程