Oracle打怪升級之路二【視圖、序列、游標、索引、存儲過程、觸發器】

前言

在之前《Oracle打怪升級之路一》中我們主要介紹了Oracle的基礎和Oracle常用查詢及函數，這篇文章作為補充，主要介紹Oracle的對象，視圖、序列、同義詞、索引等，以及PL/SQL編程的相關知識

視圖

什么是視圖

視圖是一種數據庫對象，是從一個或者多個數據表或視圖中導出的虛表，視圖所對應的數據并不真正地存儲在視圖中，而是存儲在所引用的數據表中，視圖的結構和數據是對數據表進行查詢的結果。根據創建視圖時給定的條件，視圖可以是一個數據表的一部分，也可以是多個基表的聯合，它存儲了要執行檢索的查詢語句的定義，以便在引用該視圖時使用。

使用視圖的優點：

簡化數據操作：視圖可以簡化用戶處理數據的方式。
著重于特定數據：不必要的數據或敏感數據可以不出現在視圖中。
視圖提供了一個簡單而有效的安全機制，可以定制不同用戶對數據的訪問權限。
提供向后兼容性：視圖使用戶能夠在表的架構更改時為表創建向后兼容接口。

創建或修改視圖語法

語法

CREATE [OR REPLACE] [FORCE] VIEW view_name 
AS subquery 
[WITH CHECK OPTION ]
[WITH READ ONLY]

選項解釋

OR REPLACE ：若所創建的試圖已經存在，ORACLE 自動重建該視圖；
FORCE ：不管基表是否存在 ORACLE 都會自動創建該視圖；
subquery ：一條完整的 SELECT 語句，可以在該語句中定義別名；
WITH CHECK OPTION ：插入或修改的數據行必須滿足視圖定義的約束；
WITH READ ONLY ：該視圖上不能進行任何 DML 操作。

刪除視圖的語法

DROP VIEW view_name;

案例

創建簡單視圖

需求：創建視圖：業主類型為 1 的業主信息

語句

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1

利用該視圖進行查詢

select * from view_owners1 where addressid=1;

就像使用表一樣去使用視圖就可以了。

對于簡單視圖，我們不僅可以用查詢，還可以增刪改記錄。

我們下面寫一條更新的語句，試一下：

update view_owners1 set name='王剛' where id=2;

我們再次查詢表數據發現表的數據也跟著更改了。由此我們得出結論：視圖其實是一個虛擬的表，它的數據其實來自于表。如果更改了視圖的數據，表的數據也自然會變化，更改了表的數據，視圖也自然會變化。一個視圖所存儲的并不是數據，而是一條 SQL語句。

帶檢查的約束視圖

需求：根據地址表（T_ADDRESS）創建視圖 VIEW_ADDRESS2 ,內容為區域 ID為 2 的記錄。

語句

create or replace view view_address2 as
select * from T_ADDRESS where areaid=2
with check option

執行下列更新語句：

update view_address2 set areaid=1 where id=4

系統提示如下錯誤信息：

只讀視圖的創建與使用

如果我們創建一個視圖，并不希望用戶能對視圖進行修改，那我們就需要創建視圖時指定 WITH READ ONLY 選項，這樣創建的視圖就是一個只讀視圖。

需求：將上邊的視圖修改為只讀視圖

查詢語句

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
with read only

修改后，再次執行 update 語句，會出現如下錯誤提示

創建帶錯誤的視圖

我們創建一個視圖，如果視圖的 SQL 語句所設計的表并不存在，如下

create or replace view view_TEMP as
select * from T_TEMP

T_TEMP 表并不存在，此時系統會給出錯誤提示

有的時候，我們創建視圖時的表可能并不存在，但是以后可能會存在，我們如果

此時需要創建這樣的視圖，需要添加 FORCE 選項，SQL 語句如下：

create or replace FORCE view view_TEMP as
select * from T_TEMP

此時視圖創建成功。

復雜視圖的創建與使用

所謂復雜視圖，就是視圖的 SQL 語句中，有聚合函數或多表關聯查詢。

我們看下面的例子：

多表關聯查詢的例子

需求：創建視圖，查詢顯示業主編號，業主名稱，業主類型名稱

查詢語句

create or replace view view_owners as
select o.id 業主編號,o.name 業主名稱,ot.name 業主類型
from T_OWNERS o,T_OWNERTYPE ot 
where o.ownertypeid=ot.id;

使用該視圖進行查詢

select * from view_owners;

那這個視圖能不能去修改數據呢？

我們試一下下面的語句：

update view_owners set 業主名稱='范小冰' where 業主編號=1;

可以修改成功。

我們再試一下下面的語句：

update view_owners set 業主類型='普通居民' where 業主編號=1;

這次我們會發現，系統彈出錯誤提示：

這個是什么意思？是說我們所需改的列不屬于鍵保留表的列。

什么叫鍵保留表呢？

鍵保留表是理解連接視圖修改限制的一個基本概念。該表的主鍵列全部顯示在視圖中,并且它們的值在視圖中都是唯一且非空的。也就是說，表的鍵值在一個連接視圖中也是鍵值，那么就稱這個表為鍵保留表。

在我們這個例子中，視圖中存在兩個表，業主表（T_OWNERS）和業主類型表（T_OWNERTYPE）, 其中 T_OWNERS 表就是鍵保留表，因為 T_OWNERS 的主鍵也是作為視圖的主鍵。鍵保留表的字段是可以更新的，而非鍵保留表是不能更新的。

分組聚合統計查詢視圖

需求：創建視圖，按年月統計水費金額，效果如下

create view view_accountsum as
select year,month,sum(money) moneysum 
from T_ACCOUNT 
group by year,month
order by year,month;

此例用到聚合函數，沒有鍵保留表，所以無法執行 update

物化視圖

什么是物化視圖

視圖是一個虛擬表（也可以認為是一條語句），基于它創建時指定的查詢語句返回的結果集。每次訪問它都會導致這個查詢語句被執行一次。為了避免每次訪問都執行這個查詢，可以將這個查詢結果集存儲到一個物化視圖（也叫實體化視圖）。

物化視圖與普通的視圖相比的區別是物化視圖是建立的副本，它類似于一張表，需要占用存儲空間。而對一個物化視圖查詢的執行效率與查詢一個表是一樣的。

創建物化視圖語法

CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE] [
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

語法解釋

BUILD IMMEDIATE 是在創建物化視圖的時候就生成數據
BUILD DEFERRED 則在創建時不生成數據，以后根據需要再生成數據。默認為 BUILD IMMEDIATE。
刷新（REFRESH）：指當基表發生了 DML 操作后，物化視圖何時采用哪種方式和基表進行同步。

REFRESH 后跟著指定的刷新方法有三種：FAST、COMPLETE、FORCE。FAST刷新采用增量刷新，只刷新自上次刷新以后進行的修改。COMPLETE 刷新對整個物化視圖進行完全的刷新。如果選擇 FORCE 方式，則 Oracle 在刷新時會去判斷是否可以進行快速刷新，如果可以則采用 FAST 方式，否則采用 COMPLETE的方式。FORCE 是默認的方式。

刷新的模式有兩種：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要手動刷新物化視圖（默認）。ON COMMIT 指在基表發生 COMMIT 操作時自動刷新。

案例

創建手動刷新的物化視圖

需求：查詢地址 ID,地址名稱和所屬區域名稱, 結果如下：

create materialized view mv_address 
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id

執行上邊的語句后查詢

select * from mv_address;

這時，我們向地址表（T_ADDRESS）中插入一條新記錄，

insert into t_address values(8,'宏福苑小區',1,1);

再次執行上邊的語句進行查詢，會發現新插入的語句并沒有出現在物化視圖中。

我們需要通過下面的語句（PL/SQL），手動刷新物化視圖：

begin
DBMS_MVIEW.refresh('MV_ADDRESS','C');
end;

或者通過下面的命令手動刷新物化視圖：

EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');
注意：此語句需要在命令窗口中執行。

執行此命令后再次查詢物化視圖，就可以查詢到最新的數據了。

DBMS_MVIEW.refresh 實際上是系統內置的存儲過程

創建自動刷新的物化視圖

語句如下

create materialized view mv_address2
refresh 
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id

創建此物化視圖后，當 T_ADDRESS 表發生變化時，MV_ADDRESS2 自動跟著改變。

創建時不生成數據的物化視圖

create materialized view mv_address3
build deferred 
refresh
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

創建后執行下列語句查詢物化視圖

select * from mv_address3

執行下列語句生成數據

begin
DBMS_MVIEW.refresh('MV_ADDRESS3','C');
end;

由于我們創建時指定的 on commit ,所以在修改數據后能立刻看到最新數據，無須再次執行 refresh

創建增量刷新的物化視圖

如果創建增量刷新的物化視圖，必須首先創建物化視圖日志

create materialized view log on t_address with rowid；
create materialized view log on t_area with rowid

創建的物化視圖日志名稱為 MLOG$_表名稱

創建物化視圖

create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name 
adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;

注意：創建增量刷新的物化視圖，必須：

創建物化視圖中涉及表的物化視圖日志。
在查詢語句中，必須包含所有表的 rowid ( 以 rowid 方式建立物化視圖日志 )

當我們向地址表插入數據后，物化視圖日志的內容：

SNAPTIME$$：用于表示刷新時間。

DMLTYPE$$：用于表示 DML 操作類型，I 表示 INSERT，D 表示 DELETE，U表示 UPDATE。

OLD_NEW$$：用于表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U 表示 UPDATE 操作。

CHANGE_VECTOR$$：表示修改矢量，用來表示被修改的是哪個或哪幾個字段。此列是 RAW 類型，其實 Oracle 采用的方式就是用每個 BIT 位去映射一個列。插入操作顯示為：FE, 刪除顯示為：OO 更新操作則根據更新字段的位置而顯示不同的值。

當我們手動刷新物化視圖后，物化視圖日志被清空，物化視圖更新。

begin
DBMS_MVIEW.refresh('MV_ADDRESS4','C');
end;

序列

什么是序列

序列是 ORACLE 提供的用于產生一系列唯一數字的數據庫對象。

創建與使用簡單序列

創建序列語法

-- 通過序列的偽列來訪問序列的值
create sequence 序列名稱;

語法解釋

NEXTVAL 返回序列的下一個值
CURRVAL 返回序列的當前值

注意：我們在剛建立序列后，無法提取當前值，只有先提取下一個值時才能再次提取當前值。

提取下一個值

select 序列名稱.nextval from dual

提取當前值

select 序列名稱.currval from dual

創建復雜序列

CREATE SEQUENCE sequence //創建序列名稱
[INCREMENT BY n] //遞增的序列值是 n 如果 n 是正數就遞增,如果是負數就遞減 默
認是 1
[START WITH n] //開始的值,遞增默認是 minvalue 遞減是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循環/不循環
[{CACHE n | NOCACHE}];//分配并存入到內存中

案例

創建有最大值的非循環序列

創建序列語句

create sequence seq_test1 
increment by 10
start with 10
maxvalue 300
minvalue 20

以上的錯誤，是由于我們的開始值小于最小值。開始值不能小于最小值，修改以上語句：

create sequence seq_test1 
increment by 10
start with 10
maxvalue 300
minvalue 5

我們執行下列語句提取序列值，當序列值為 300（最大值）的時候再次提取值，系統會報異常信息。

創建有最大值的循環序列

create sequence seq_test2
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle ;

當序列當前值為 300（最大值），再次提取序列的值

select seq_test2.nextval from dual

循環的序列，第一次循環是從開始值開始循環，而第二次循環是從最小值開始循環。創建的是一個循環的序列，所以必須指定最大值，

創建帶緩存的序列

create sequence seq_test3
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle
cache 50;

我們執行上邊語句的意思是每次取出 50 個緩存值，但是執行會提示錯誤

上邊錯誤提示的意思是：緩存設置的數必須小于每次循環的數。

我們緩存設定的值是 50，而最大值是 300，那么為什么還會提示這樣的信息呢？其實我們的 cache 雖然是 50，但是我們每次增長值是 10。這樣 50 次緩存提取出的數是 500 （50*10）

我們更改為下列的語句：

create sequence seq_test4
increment by 10
start with 10
maxvalue 500
minvalue 10
cycle
cache 50;

下列語句依然會提示上邊的錯誤，這是因為還存在一個 minvalue ，minvalue 和maxvalue 之間是 490 個數，也就是一次循環可以提取 490，但是我們的緩存是

500。

我們再次修改語句：

create sequence seq_test5
increment by 10
start with 10
maxvalue 500
minvalue 9
cycle
cache 50;

把最小值減 1，或把最大值加 1，都可以通過。

修改和刪除序列

修改序列

使用 ALTER SEQUENCE 語句修改序列，不能更改序列的 START WITH 參數

ALTER SEQUENCE 序列名稱 MAXVALUE 5000 CYCLE;

刪除序列

DROP SEQUENCE 序列名稱;

同義詞

什么是同義詞

同義詞實質上是指定方案對象的一個別名。通過屏蔽對象的名稱和所有者以及對分布式數據庫的遠程對象提供位置透明性，同義詞可以提供一定程度的安全性。同時，同義詞的易用性較好，降低了數據庫用戶的 SQL 語句復雜度。同義詞允許基對象重命名或者移動，這時，只需對同義詞進行重定義，基于同義詞的應用程序可以繼續運行而無需修改。

你可以創建公共同義詞和私有同義詞。其中，公共同義詞屬于 PUBLIC 特殊用戶組，數據庫的所有用戶都能訪問；而私有同義詞包含在特定用戶的方案中，只允許特定用戶或者有基對象訪問權限的用戶進行訪問。

同義詞本身不涉及安全，當你賦予一個同義詞對象權限時，你實質上是在給同義詞的基對象賦予權限，同義詞只是基對象的一個別名。

創建與使用同義詞

創建同義詞語法

create [public] SYNONYM synooym for object;

其中 synonym 表示要創建的同義詞的名稱，object 表示表，視圖，序列等我們要創建同義詞的對象的名稱。

創建私有同義詞

需求：為表 T_OWNERS 創建( 私有 )同義詞名稱為 OWNERS

語句

create synonym OWNERS for T_OWNERS;

使用同義詞

select * from OWNERS ;

創建公有同義詞

需求：為表 T_OWNERS 創建( 公有 )同義詞名稱為 OWNERS2：

create public synonym OWNERS2 for T_OWNERS;

索引

什么是索引

索引是用于加速數據存取的數據對象。合理的使用索引可以大大降低 i/o 次數,從而提高數據訪問性能。

索引是需要占據存儲空間的，也可以理解為是一種特殊的數據。形式類似于下圖的一棵“樹”，而樹的節點存儲的就是每條記錄的物理地址，也就是我們提到的偽列（ROWID）

普通索引

創建語法

create index 索引名稱 on 表名(列名);

需求：基于業主表的 name 字段來建立索引，創建語句

create index index_owners_name on T_OWNERS(name)

索引性能測試

創建一個兩個字段的表

create table T_INDEXTEST (
ID NUMBER,
NAME VARCHAR2(30));

編寫 PL/SQL 插入 100 萬條記錄

BEGIN
FOR i in 1..1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop;
commit;
END;

創建完數據后，根據 name 列創建索引

CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name)

執行下面兩句 SQL 執行

SELECT * from T_INDEXTEST where ID=765432;
SELECT * from T_INDEXTEST where NAME='AA765432';

我們會發現根據 name 查詢所用的時間會比根據 id 查詢所用的時間要短

唯一索引

如果我們需要在某個表某個列創建索引，而這列的值是不會重復的。這是我們可以創建唯一索引

創建語法

create unique index 索引名稱 on 表名(列名);

需求：在業主表的水表編號一列創建唯一索引

create unique index index_owners_watermeter on T_OWNERS(watermeter);

復合索引

我們經常要對某幾列進行查詢，比如，我們經常要根據學歷和性別對學員進行搜索，如果我們對這兩列建立兩個索引，因為要查兩棵樹，查詢性能不一定高。那如何建立索引呢？我們可以建立復合索引，也就是基于兩個以上的列建立一個索引。

創建語法

create index 索引名稱 on 表名(列名,列名.....);

根據地址和門牌號對學員表創建索引，語句如下

create index owners_index_ah
on T_OWNERS(addressid,housenumber);

反向鍵索引

應用場景：當某個字段的值為連續增長的值，如果構建標準索引，會形成歪脖子樹。這樣會增加查詢的層數，性能會下降。建立反向鍵索引，可以使索引的值變得不規則，從而使索引樹能夠均勻分布。

創建語法

create index 索引名稱 on 表名(列名) reverse;

位圖索引

使用場景：位圖索引適合創建在低基數列上。位圖索引不直接存儲 ROWID，而是存儲字節位到 ROWID 的映射

優點：減少響應時間，節省空間占用

創建語法

create bitmap index 索引名稱 on 表名(列名);

需求：我們在 T_owners 表的 ownertypeid 列上建立位圖索引，創建語句

create bitmap index index_owners_typeid on T_OWNERS(ownertypeid)

PL/SQL

什么是PL/SQL

PL/SQL（Procedure Language/SQL）是 Oracle 對 sql 語言的過程化擴展，指在 SQL 命令語言中增加了過程處理語句（如分支、循環等），使 SQL 語言具有過程處理能力。把 SQL 語言的數據操縱能力與過程語言的數據處理能力結合起來，使得 PLSQL 面向過程但比過程語言簡單、高效、靈活和實用。

基本語法結構

[declare
--聲明變量
]
begin
--代碼邏輯
[exception
--異常處理
]
end;

變量

變量聲明

變量名 類型（長度）；

變量賦值

變量名:=變量值

需求：聲明變量水費單價、水費字數、噸數、金額。對水費單價、字數、進行賦值。噸數根據水費字數換算，規則為水費字數除以1000，并且四舍五入，保留兩位小數。計算金額，金額=單價*噸數。輸出單價、數量和金額。

--變量的用法--
declare
v_price number(10,2);--水費單價
v_usenum number; --水費字數
v_usenum2 number(10,2);--噸數
v_money number(10,2);--金額
begin
v_price:=2.45;--水費單價
v_usenum:=8012;--字數
--字數換算為噸數
v_usenum2:= round( v_usenum/1000,2);
--計算金額
v_money:=round(v_price*v_usenum2,2);
dbms_output.put_line('單價：'||v_price||'噸 數:'||v_usenum2||'金額：'||v_money); 
end;

Select into 方式賦值

select 列名 into 變量名 from 表名 where 條件

注意：結果必須是一條記錄，有多條記錄和沒有記錄都會報錯

declare
v_price number(10,2);--單價
v_usenum number;--水費字數
v_num0 number;--上月字數
v_num1 number;--本月字數
v_usenum2 number(10,2);--使用噸數
v_money number(10,2);--水費金額
begin
--對單價進行賦值
v_price:=3.45;
--變量賦值
select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
T_ACCOUNT 
where year='2012' and month='01' and owneruuid=1;
v_usenum2:= round(v_usenum/1000,2);
v_money:=v_price*v_usenum2;
DBMS_OUTPUT.put_line('單價：'||v_price||'噸數：'
||v_usenum2||'金額：'||v_money||'上月字數：'||v_num0||'本月
字數'||v_num1);
end;

屬性類型

%TYPE 引用型

作用：引用某表某列的字段類型

declare
v_price number(10,2);--單價
v_usenum T_ACCOUNT.USENUM%TYPE;--水費字數
v_num0 T_ACCOUNT.NUM0%TYPE;--上月字數
v_num1 T_ACCOUNT.NUM1%TYPE;--本月字數
v_usenum2 number(10,2);--使用噸數
v_money number(10,2);--水費金額
begin
--對單價進行賦值
v_price:=3.45;
--v_usenum:=8090;
select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
T_ACCOUNT 
where year='2012' and month='01' and owneruuid=1;
--使用噸數
v_usenum2:= round(v_usenum/1000,2);
--計算金額
v_money:=v_price*v_usenum2;
DBMS_OUTPUT.put_line('單價：'||v_price||'噸數：'
||v_usenum2||'金額：'||v_money||'上月字數：'||v_num0||'本月
字數'||v_num1);
end;

%ROWTYPE 記錄型

作用: 標識某個表的行記錄類型

--變量的用法--
declare
v_price number(10,2);--單價
v_account T_ACCOUNT%ROWTYPE;--記錄型
v_usenum2 number(10,2);--使用噸數
v_money number(10,2);--水費金額
begin
--對單價進行賦值
v_price:=3.45;
--賦值
select * into v_account from T_ACCOUNT 
where year='2012' and month='01' and owneruuid=1;
--使用噸數
v_usenum2:= round(v_account.usenum/1000,2);
--計算金額
v_money:=v_price*v_usenum2;
DBMS_OUTPUT.put_line('單價：'||v_price||'噸數：'
||v_usenum2||'金額：'||v_money||'上月字數：
'||v_account.num0||'本月字數'||v_account.num1);
end;

異常

在運行程序時出現的錯誤叫做異常發生異常后，語句將停止執行，控制權轉移到 PL/SQL 塊的異常處理部分。

異常有兩種類型：

預定義異常 - 當 PL/SQL 程序違反 Oracle 規則或超越系統限制時隱式引發
用戶定義異常 - 用戶可以在 PL/SQL 塊的聲明部分定義異常，自定義的異常通過 RAISE 語句顯式引發

預定義異常類型

Oracle 預定義異常 21 個

命名的系統異常	產生原因
ACCESS_INTO_NULL	未定義對象
CASE_NOT_FOUND	CASE 中若未包含相應的 WHEN ，并且沒有設置 ELSE 時
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游標已經打開
DUP_VAL_ON_INDEX	唯一索引對應的列上有重復的值
INVALID_CURSOR	在不合法的游標上進行操作
INVALID_NUMBER	內嵌的 SQL 語句不能將字符轉換為數字
NO_DATA_FOUND	使用 select into 未返回行
TOO_MANY_ROWS	執行 select into 時，結果集超過一行
ZERO_DIVIDE	除數為 0
SUBSCRIPT_BEYOND_COUNT	元素下標超過嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 時，將下標指定為負數
VALUE_ERROR	賦值時，變量長度不足以容納實際數據
LOGIN_DENIED	PL/SQL 應用程序連接到 oracle 數據庫時，提供了不正確的用戶名或密碼
NOT_LOGGED_ON	PL/SQL 應用程序在沒有連接 oralce 數據庫的情況下訪問數據
PROGRAM_ERROR	PL/SQL 內部問題，可能需要重裝數據字典＆ pl./SQL 系統包
ROWTYPE_MISMATCH	宿主游標變量與 PL/SQL 游標變量的返回類型不兼容
SELF_IS_NULL	使用對象類型時，在 null 對象上調用對象方法
STORAGE_ERROR	運行 PL/SQL 時，超出內存空間
SYS_INVALID_ID	無效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待資源時超時

語法結構

exception
  when 異常類型 then
  異常處理邏輯

根據上例中的代碼，添加異常處理部分

--變量的用法--
declare
    v_price   number(10, 2);--水費單價
    v_usenum  T_ACCOUNT.USENUM%type; --水費字數
    v_usenum2 number(10, 3);--噸數
    v_money   number(10, 2);--金額
begin
    v_price := 2.45;--水費單價
    select usenum
    into v_usenum
    from T_ACCOUNT
    where owneruuid = 1
      and year = '2012'
      and month = '01';
--字數換算為噸數
    v_usenum2 := round(v_usenum / 1000, 3);
--計算金額
    v_money := round(v_price * v_usenum2, 2);
    dbms_output.put_line('單價：' || v_price || '噸 數:' || v_usenum2 || '金額：' || v_money);
exception
    when NO_DATA_FOUND then
        dbms_output.put_line('未找到數據，請核實');
    when TOO_MANY_ROWS then
        dbms_output.put_line('查詢條件有誤，返回多條信息，請核實');
end;

條件判斷

基本語法1

if 條件 then
業務邏輯
end if;

基本語法2

if 條件 then
業務邏輯
else
業務邏輯
end if;

基本語法3

if 條件 then
業務邏輯
elsif 條件 then
業務邏輯
else
業務邏輯
end if;

需求：設置三個等級的水費 5 噸以下 2.45 元/噸 5 噸到 10 噸部分 3.45 元/噸，超過 10 噸部分 4.45 ，根據使用水費的量來計算階梯水費。

declare
    v_price1  number(10, 2);--不足 5 噸的單價
    v_price2  number(10, 2);--超過 5 噸不足 10 噸單價
    v_price3  number(10, 2);--超過 10 噸單價
    v_account T_ACCOUNT%ROWTYPE;--記錄型
    v_usenum2 number(10, 2);--使用噸數
    v_money   number(10, 2);--水費金額
begin
    -- 對單價進行賦值
    v_price1 := 2.45;
    v_price2 := 3.45;
    v_price3 := 4.45;
    -- 賦值
    select *
    into v_account
    from T_ACCOUNT
    where year = '2012'
      and month = '01'
      and owneruuid = 1;
		-- 使用噸數
    v_usenum2 := round(v_account.usenum / 1000, 2);
		-- 計算金額(階梯水費)
    if v_usenum2 <= 5 then--第一個階梯
        v_money := v_price1 * v_usenum2;
    elsif v_usenum2 > 5 and v_usenum2 <= 10 then --第二個階梯
        v_money := v_price1 * 5 + v_price2 * (v_usenum2 - 5);
    else --第三個階梯
        v_money := v_price1 * 5 + v_price2 * 5 +
                   v_price3 * (v_usenum2 - 10);
    end if;
    DBMS_OUTPUT.put_line('噸數：'
        || v_usenum2 || '金額：' || v_money || '上月字數： ' || v_account.num0 || '本月字數' || v_account.num1);
exception
    when NO_DATA_FOUND then
        DBMS_OUTPUT.put_line('沒有找到數據');
    when TOO_MANY_ROWS then
        DBMS_OUTPUT.put_line('返回的數據有多行');
end;

循環

無條件循環

語法結構

loop
	--循環語句
end loop;

范例：輸出從1開始的100個數

declare
    v_num number := 1;
begin
    loop
        dbms_output.put_line(v_num);
        v_num := v_num + 1;
        exit when v_num > 100;
    end loop;
end ;

條件循環

語法結構

while 條件
loop
end loop;

范例：輸出從1開始的100個數

declare
    v_num number := 1;
begin
    while v_num <= 100
        loop
            dbms_output.put_line(v_num);
            v_num := v_num + 1;
        end loop;
end ;

for循環

基礎語法

for 變量 in 起始值..終止值
loop
end loop;

范例：輸出從1開始的100個數

begin
    for v_num in 1..100
        loop
            dbms_output.put_line(v_num);
        end loop;
end;

游標

什么是游標

游標是系統為用戶開設的一個數據緩沖區,存放 SQL 語句的執行結果。我們可以把游標理解為 PL/SQL 中的結果集。

語法結構及示例

創建語法

cursor 游標名稱 is SQL 語句;

使用游標語法

open 游標名稱
    loop
        fetch 游標名稱 into 變量
        exit when 游標名稱%notfound
    end loop;
close 游標名稱

需求：打印業主類型為 1 的價格表

代碼

declare
    v_pricetable T_PRICETABLE%rowtype;--價格行對象
    cursor cur_pricetable is select *
                             from T_PRICETABLE
                             where ownertypeid = 1;--定義游標
begin
    open cur_pricetable;--打開游標
    loop
        fetch cur_pricetable into v_pricetable;--提取游標到變量
        exit when cur_pricetable%notfound;--當游標到最后一行下面退
        出循環 dbms_output.put_line( '價格:'
||v_pricetable.price ||'噸位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
    end loop;
    close cur_pricetable;--關閉游標
end ;

創建帶參數的游標

我們的查詢語句的條件值有可能是在運行時才能決定的，比如性業主類型，可能是運行時才可以決定，那如何實現呢？

我們接下來學習帶參數的游標，修改上述案例

declare
    v_pricetable T_PRICETABLE%rowtype;--價格行對象
    cursor cur_pricetable(v_ownertypeid number) is select *
                                                   from T_PRICETABLE
                                                   where ownertypeid = v_ownertypeid;--定義游
    標
begin
    北京市昌平區建材城西路金燕龍辦公樓一層 電話：400-618-9090
    open cur_pricetable(2);--打開游標
    loop
        fetch cur_pricetable into v_pricetable;--提取游標到變量
        exit when cur_pricetable%notfound;--當游標到最后一行下面退
        出循環 dbms_output.put_line('價格:'||v_pricetable.price ||'噸
位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
    end loop;
    close cur_pricetable;--關閉游標
end ;

for循環提取游標值

我們每次提取游標，需要打開游標關閉游標循環游標提取游標控制循環的退出等等，好麻煩！有沒有更簡單的寫法呢？

有！用 for 循環一切都那么簡單，上例的代碼可以改造為下列形式

declare
    cursor cur_pricetable(v_ownertypeid number) is 
    select * from T_PRICETABLEwhere ownertypeid = v_ownertypeid;--定義游標
begin
    for v_pricetable in cur_pricetable(3)
        loop
            dbms_output.put_line('價格:' || v_pricetable.price || 
                                 '噸位：' || v_pricetable.minnum || '-' || v_pricetable.maxnum);
        end loop;
end ;

存儲函數

什么是存儲函數

存儲函數又稱為自定義函數。可以接收一個或多個參數，返回一個結果。在函數中我們可以使用 P/SQL 進行邏輯的處理。

存儲函數語法結構

創建或修改存儲過程的語法如下

CREATE
[ OR REPLACE ] FUNCTION 函數名稱
（參數名稱 參數類型, 參數名稱 參數類型, ...
）
RETURN 結果變量數據類型
IS
變量聲明部分;
BEGIN
    邏輯部分;
    RETURN 結果變量;
    [
EXCEPTION
    異常處理部分]
END;

案例

需求：創建存儲函數，根據地址 ID 查詢地址名稱。

創建語句

create function fn_getaddress(v_id number) 
return varchar2
is
v_name varchar2(30);
begin
select name into v_name from t_address where id=v_id;
return v_name;
end;

測試此函數

select fn_getaddress(3) from dual

需求：查詢業主 ID，業主名稱，業主地址，業主地址使用剛才我們創建的函數來實現。

select id 編號,name 業主名稱,fn_getaddress(addressid) 地址
from t_owners

存儲過程

什么是存儲過程

存儲過程是被命名的 PL/SQL 塊，存儲于數據庫中，是數據庫對象的一種。應用程序可以調用存儲過程，執行相應的邏輯。存儲過程與存儲函數都可以封裝一定的業務邏輯并返回結果，存在區別如下：

存儲函數中有返回值，且必須返回；而存儲過程沒有返回值，可以通過傳出參數返回多個值。
存儲函數可以在 select 語句中直接使用，而存儲過程不能。過程多數是被應用程序所調用。
存儲函數一般都是封裝一個查詢結果，而存儲過程一般都封裝一段事務代碼。

存儲過程語法結構

創建或修改存儲過程的語法如下:

CREATE
[ OR REPLACE ] PROCEDURE 存儲過程名稱
（參數名 類型, 參數名 類型, 參數名 類型
）
IS|AS
變量聲明部分;
BEGIN
    邏輯部分
        [EXCEPTION
        異常處理部分]
END;

參數只指定類型，不指定長度

過程參數的三種模式：

IN 傳入參數（默認）
OUT 傳出參數，主要用于返回程序運行結果
IN OUT 傳入傳出參數

案例

創建不帶傳出參數的存儲過程：添加業主信息

--增加業主信息序列
create sequence seq_owners start with 11;
--增加業主信息存儲過程
create or replace procedure pro_owners_add(
    v_name varchar2,
    v_addressid number,
    v_housenumber varchar2,
    v_watermeter varchar2,
    v_type number
)
    is
begin
    insert into T_OWNERS
    values (seq_owners.nextval, v_name, v_addressid, v_housenumb
        er, v_watermeter, sysdate, v_type);
    commit;
end;

PL/SQL 中調用存儲過程

call pro_owners_add('趙偉',1,'999-3','132-7',1);

創建帶傳出參數的存儲過程

需求：添加業主信息，傳出參數為新增業主的 ID

--增加業主信息存儲過程
create or replace procedure pro_owners_add(
    v_name varchar2,
    v_addressid number,
    v_housenumber varchar2,
    v_watermeter varchar2,
    v_type number,
    v_id out number
)
    is
begin
    select seq_owners.nextval into v_id from dual;
    insert into T_OWNERS
    values (v_id, v_name, v_addressid, v_housenumber, v_watermete
        r, sysdate, v_type);
    commit;
end;

PL/SQL 調用該存儲過程

declare
    v_id number;--定義傳出參數的變量
begin
    pro_owners_add('王旺旺', 1, '922-3', '133-7', 1, v_id);
    DBMS_OUTPUT.put_line('增加成功,ID:' || v_id);
end;

觸發器

什么是觸發器

數據庫觸發器是一個與表相關聯的、存儲的 PL/SQL 程序。每當一個特定的數據操作語句(Insert,update,delete)在指定的表上發出時，Oracle 自動地執行觸發器中定義的語句序列。

觸發器的作用

數據確認
實施復雜的安全性檢查
做審計，跟蹤表上所做的數據操作等
數據的備份和同步

觸發器的分類

前置觸發器（BEFORE）
后置觸發器（AFTER）

創建觸發器的語法

語法

CREATE [or REPLACE] TRIGGER 觸發器名
	BEFORE | AFTER
	[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
	ON 表名
	[	FOR EACH ROW ][WHEN(條件) ]
declare
	……
begin
	PLSQL 塊
End ；

FOR EACH ROW 作用是標注此觸發器是行級觸發器語句級觸發器

在觸發器中觸發語句與偽記錄變量的值

觸發語句	:old :new	:new
Insert	所有字段都是空(null)	將要插入的數據
Update	更新以前該行的值	更新后的值
delete	刪除以前該行的值	所有字段都是空(null)

案例

前置觸發器

需求：當用戶輸入本月累計表數后，自動計算出本月使用數。

語句：

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
:new.usenum:=:new.num1-:new.num0;
end;

后置觸發器

需求：當用戶修改了業主信息表的數據時記錄修改前與修改后的值

--創建業主名稱修改日志表:用于記錄業主更改前后的名稱
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
--創建后置觸發器，自動記錄業主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
insert into t_owners_log 
values(sysdate,:old.id,:old.name,:new.name);
end;

測試

--更新數據
update t_owners set name='楊小花' where id=3;
commit;
--查詢日志表
select * from t_owners_log;

posted @ 2022-01-06 15:44 DOONDO 閱讀(266) 評論(0) 收藏舉報

刷新頁面返回頂部

DOONDO

Oracle打怪升級之路二【視圖、序列、游標、索引、存儲過程、觸發器】

視圖

什么是視圖

創建或修改視圖語法

刪除視圖的語法

案例

創建簡單視圖

帶檢查的約束視圖

只讀視圖的創建與使用

創建帶錯誤的視圖

復雜視圖的創建與使用

分組聚合統計查詢視圖

物化視圖

什么是物化視圖

創建物化視圖語法

案例

創建手動刷新的物化視圖

創建自動刷新的物化視圖

創建時不生成數據的物化視圖

創建增量刷新的物化視圖

序列

什么是序列

創建與使用簡單序列

創建復雜序列

創建有最大值的非循環序列

創建有最大值的循環序列

創建帶緩存的序列

修改和刪除序列

修改序列

刪除序列

同義詞

什么是同義詞

創建與使用同義詞

創建同義詞語法

創建私有同義詞

創建公有同義詞

索引

什么是索引

普通索引

唯一索引

復合索引

反向鍵索引

位圖索引

PL/SQL

什么是PL/SQL

基本語法結構

變量

變量聲明

變量賦值

Select into 方式 賦值

屬性類型

%TYPE 引用型

%ROWTYPE 記錄型

異常

預定義異常類型

語法結構

條件判斷

循環

無條件循環

條件循環

for循環

游標

什么是游標

語法結構及示例

創建語法

使用游標語法

創建帶參數的游標

for循環提取游標值

存儲函數

什么是存儲函數

存儲函數語法結構

案例

存儲過程

什么是存儲過程

存儲過程語法結構

案例

觸發器

什么是觸發器

觸發器的作用

Select into 方式賦值