當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle 常用命令举例

發(fā)布時間：2024/7/19 编程问答 23 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle 常用命令举例小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

Oracle 常用命令舉例
? 基本語法
? % type用法
? %rowtype用法
? TYPE用法
? 游標(biāo)的使用
? for 循環(huán)
? loop循環(huán)
? while循環(huán)
? if / else 的用法
? case 的用法
? 錯誤定義
? error的設(shè)定
? exception用法
? 存儲過程及函數(shù)
? procedure 的建立和調(diào)用
? function的建立和調(diào)用
? 參數(shù)的調(diào)用（in 模式為按址調(diào)用，out / in out模式為按值調(diào)用。NOCOPY 強行轉(zhuǎn)換成按址調(diào)用）。
? 軟件包及封裝
? 軟件包（PACKAGE）的建立和調(diào)用
? 軟件包的全局結(jié)構(gòu)
? 封裝函數(shù)的純度
? 查看源代碼及建立用戶、用戶的權(quán)限
? 源代碼的查看
? 建立用戶及登陸
? 授予權(quán)限和權(quán)限收回
? 依賴
? 直接依賴
? 查看依賴
? 包之間調(diào)用
? 觸發(fā)器
? 建立簡單的觸發(fā)器
? 觸發(fā)器分類
? 稍復(fù)雜的觸發(fā)器
? 條件謂詞
? 觸發(fā)器中不可使用Commit
? 系統(tǒng)觸發(fā)器舉例（LOGON）
? instead of 觸發(fā)器
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
1. % type用法，提取% type所在字段的類型
declare
myid dept.id % type;
myname dept.name % type;
begin
select id,name into myid,myname from dept;
dbms_output.put_line(myid);
dbms_output.put_line(myname);
end;
/
2. %rowtype用法，提取%rowtype所在的字段的類型
declare
type type_dept is table of dept % rowtype
index by binary_integer;
tb type_dept;
begin
tb(1).id:='001';
tb(2).id:='001';
dbms_output.put_line(tb.COUNT);
end;
/
3. TYPE用法，相當(dāng)于結(jié)構(gòu)體
declare
lv_order_date DAte:=sysdate;
lv_last_txt varchar2(5) default '001';
lv_last varchar2(10) not null:='us';
TYPE type_test is record(
myid dept.id % type,
myname dept.name % type);
rec type_test;
begin
lv_order_date:=sysdate;
dbms_output.put_line(lv_last);
select id,name into rec from dept;
dbms_output.put_line(rec.myid);
dbms_output.put_line(rec.myname);
end;
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
/
4. 游標(biāo)的使用
declare
g_id char(10):='002';
find_not char(1):='N';
cursor cur is
select * from dept;
TYPE type_dept is record(
cur指向表
myid dept.id % type,
myname dept.name % type,
myaddr dept.addr % type);
rect type_dept;
begin
open cur;
loop
fetch cur into rect;
exit when cur% NOTFOUND;
提取cur指向的記錄到rect結(jié)構(gòu)中
if rect.myid=g_id then
find_not:='Y';
dbms_output.put_line('Find it!!');
dbms_output.put_line('DEPT ID:' || rect.myid);
dbms_output.put_line('NAME:' || rect.myname);
dbms_output.put_line('ADDR:' || rect.myaddr);
end if;
end loop;
close cur;
if find_not='N' then
dbms_output.put_line('no record');
end if;
end;
/
5. for 循環(huán)
begin
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
/
6. loop循環(huán)
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
declare
v number:=1;
begin
loop
dbms_output.put_line(v);
exit when v>5;
v:=v+1;
end loop;
end;
/
7. while循環(huán)
declare
v number:=1;
begin
while v<5 loop
dbms_output.put_line(v);
v:=v+1;
end loop;
end;
/
8. error的設(shè)定
declare
v1 number:=90;
begin
if v1=10 then dbms_output.put_line('v1 is 10');
elsif v1=20 then dbms_output.put_line('v2 is 20');
else goto err;
dbms_output.put_line('normal end');
<<err>>
dbms_output.put_line('error found');
end if;
end;
/
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
9. exception用法
declare
ex Exception;
begin
Update dept set name='Edison'
where id='100';
if SQL%NOTFOUND Then
Raise ex;
end if;
Exception
When ex then
dbms_output.put_line('update failed.');
end;
/
declare
type rc_dept is record (
myid dept.id%type,
myname dept.name%type,
myaddr dept.addr%type
);
tb rc_dept;
begin
select id,name,addr into tb from dept where id=:gb_id;
dbms_output.put_line('id:' || tb.myid);
dbms_output.put_line('name:' || tb.myname);
dbms_output.put_line('addr:' || tb.myaddr);
exception
when NO_DATA_FOUND then
dbms_output.put_line('no record is found');
when TOO_MANY_ROWS then
dbms_output.put_line('too many rows are selected');
when OTHERS then
dbms_output.put_line('undefine error');
dbms_output.put_line('error coede: ' || SQLCODE);
dbms_output.put_line('error message:' || SQLERRM);
end;
/
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
declare
type rc_dept is record (
myid dept.id%type,
myname dept.name%type,
myaddr dept.addr%type
);
tb rc_dept;
begin
begin
select id,name,addr into tb from dept where id=:gb_id;
內(nèi)層錯誤捕捉其始點，在此之前發(fā)生的錯誤由外層進(jìn)行捕捉。
dbms_output.put_line('id:'|| tb.myid);
dbms_output.put_line('name:' || tb.myname);
dbms_output.put_line('addr:'|| tb.myaddr);
exception
when NO_DATA_FOUND then
dbms_output.put_line('no record is found, occur in inner.');
end;
exception
when TOO_MANY_ROWS then
dbms_output.put_line('too many rows are selected, occur in outer.');
內(nèi)層的錯誤捕捉到后，外層的錯誤就不捕捉了。否則由外層捕獲錯誤。
when OTHERS then
dbms_output.put_line('undefine error');
dbms_output.put_line('error coede: ' || SQLCODE);
dbms_output.put_line('error message:' || SQLERRM);
end;
/
10. if / else 的用法
declare
v1 number:=90;
begin
if v1=10 then dbms_output.put_line('v1 is 10');
elsif v1=20 then dbms_output.put_line('v2 is 20');
else dbms_output.put_line('v2 is others');
end if;
end;
/
11. case 的用法
declare
v number:=10;
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
begin
case :v
when 10 then dbms_output.put_line('v is 10');
when 20 then dbms_output.put_line('v is 20');
else dbms_output.put_line('v is not 10 and 20');
end case;
end;
/
12. procedure 的建立和調(diào)用
create or replace procedure test_sp
(test in number, outtest out number)
is
begin
參數(shù)的聲明不要對它的大小進(jìn)行定義。IN表示傳入的參數(shù)，不能修改，OUT表示傳出的參數(shù)。
if test>10 then
printsomthing ('test is over 10!!');
else
begin
outtest:=test;
printsomthing (outtest);
end;
過程調(diào)用過程的參數(shù)調(diào)用格式注意，不加“：”
end if;
end;
/
create or replace procedure printsomthing
(print in number)
is
begin
dbms_output.put_line(print);
end;
/
create or replace procedure printsomthing
(print in char)
is
begin
dbms_output.put_line(print);
end;
/
exec test_sp(:test,:outtest); 外部執(zhí)行的時候注意參數(shù)調(diào)用方式要加“：”
存儲過程可以重載，符合C++的重載規(guī)則。
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
13. function的建立和調(diào)用
create or replace function test(t in number) return number
is
Function的建立，需要返回值，但不需要說明大小。
begin
if t>10 then
dbms_output.put_line(t);
elsif t<10 then
dbms_output.put_line(t);
end if;
return t;
end;
/
注意：調(diào)用的方法，不能以procedure那樣獨立進(jìn)行調(diào)用。函數(shù)是表達(dá)式的一部分（有返回值）。
exec test(1); 錯誤
exec :tt:=test(2); 正確
Tips:建議使用return模式，而不是使用out模式。
procedure 中也能用return，這里的return只表示當(dāng)前procedure的中斷。
參數(shù)如同procedure一樣，不能修改in的參數(shù)
create or replace function test(t in number) return number
is
begin
if t>10 then
t:=t+10;
dbms_output.put_line(t);
elsif t<10 then
dbms_output.put_line(t);
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3 PLS-00363: 表達(dá)式 'T' 不能用作賦值目標(biāo)
5/3 PL/SQL: Statement ignored
end if;
return t;
end;
/
多路return
create or replace function test(t in number) return number
is
begin
if t<10 then
return 1;
elsif t>=10 then
return 2;
end if;
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
end;
/
function中調(diào)用procedure
create or replace function test(t in number) return number
is
begin
printnumber(t);
return t;
end;
/
create or replace procedure printnumber
(print in number)
is
begin
dbms_output.put_line(print);
end;
/
14. 參數(shù)的調(diào)用（in 模式為按址調(diào)用，out / in out模式為按值調(diào)用。NOCOPY 強行轉(zhuǎn)換成按址調(diào)用。
create or replace procedure test_nocopy_sp(p_in in number, p_out in out nocopy number)
is
begin
p_out:=5;
if p_in=1 then
raise no_data_found;
強行拋出一個異常，以顯示參數(shù)的結(jié)果。
end if;
end;
/
create or replace procedure run_nocopy_sp
is
lv_test_num number;
begin
lv_test_num:=1;
test_nocopy_sp(1,lv_test_num);
exception
when others then
因為test_nocopy_sp這個過程的第二個參數(shù)是nocopy的，也就是傳址的，所以修改了lv_test_num，為5。
如果test_nocopy_sp這個過程的第二個參數(shù)不是nocopy，那么就是傳值，lv_test_num不被修改，仍然為1。
dbms_output.put_line('error happened'|| lv_test_num);
end;
/
error happened 5
error happened 1
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
15. 軟件包（package）的建立（包含了函數(shù)的重載）
軟件包聲明
create or replace package test_package
is
procedure test_sp
(test in number, outtest out number);
只聲明過程、函數(shù)的原型。
procedure printsomthing
(print in number);
函數(shù)printsomething的重載
procedure printsomthing
(print in char);
function test
(t in number) return number;
end;
/
軟件包體的建立
create or replace package body test_package
is
procedure test_sp
無begin
(test in number, outtest out number)
is
begin
if test>10 then
printsomthing ('test is over 10!!');
else
begin
outtest:=test;
printsomthing (test);
end;
end if;
end test_sp;
end 的注意
procedure printsomthing
(print in number)
is
begin
dbms_output.put_line(print);
end printsomthing;
procedure printsomthing
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
(print in char)
is
begin
dbms_output.put_line(print);
end printsomthing;
function test(t in number) return number
is
begin
if t>10 then
dbms_output.put_line(t);
elsif t<10 then
dbms_output.put_line(t);
end if;
return t;
end test;
end;
/
具體寫體的時候和以前一樣寫，只不過不用寫create or replace，在end最后還要緊跟過程或函數(shù)名。
執(zhí)行結(jié)果
SQL> var test1 number;
SQL> exec test_package.test_sp(20,:test1);
test is over 10!!
PL/SQL 過程已成功完成。
SQL> exec test_package.test_sp(1,:test1);
1
PL/SQL 過程已成功完成。
SQL> exec :test1:=test_package.test(20);
20
調(diào)用方法就是在前面加個包名，其余注意點和過程或函數(shù)相同。
調(diào)用方法就是在前面加個包名，其余注意點和過程或函數(shù)相同。
PL/SQL 過程已成功完成。
SQL> print test1;
TEST1
----------
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
20
16. 軟件包全局結(jié)構(gòu)
create or replace package test_global
is
global_v number(3):=0;
procedure setValue(p1 in number);
包的全局變量
end;
/
create or replace package body test_global
is
procedure setValue(p1 in number)
is
begin
global_v:=p1;
dbms_output.put_line(global_v);
修改全局變量并輸出
end setValue;
end;
/
建立2個會話：
exec test_global.setValue(20);
exec test_global.setValue(10);
2個會話分別維護自己的全局變量。互不影響。
17. 封裝函數(shù)的純度
create or replace package test_global
is
global_v number(3):=0;
function setValue(p1 in number) return number;
pragma restrict_references(setValue,WNPS);
end;
/
create or replace package body test_global
指定純度。
WNDS Writes No Database State
函數(shù)不休改任何數(shù)據(jù)庫表
RNDS Reads No Database State
函數(shù)不讀取任何表
WNPS Writes No Package State
函數(shù)不修改任何封裝變量
RNPS Reads No Package State
函數(shù)不讀取任何封裝變量
is
function setValue(p1 in number) return number
is
begin
global_v:=p1;
違反了WNPS的約束。
PACKAGE BODY TEST_GLOBAL 出現(xiàn)錯誤:
LINE/COL ERROR
PLS-00452: 子程序 'SETVALUE' 違反了它的相關(guān)注記
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
dbms_output.put_line(global_v);
return global_v;
end setValue;
end;
/
18. 源代碼的查看
SQL> desc user_source
名稱是否為空? 類型
----------------------------------------- -------- -------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
存放源代碼的字段
SQL> select text from user_source where name='TEST';
注意：name一定要大寫。
TEXT
--------------------------------------------------------------
function test(t in number) return number
is
begin
if t>10 then
dbms_output.put_line(t);
elsif t<10 then
dbms_output.put_line(t);
end if;
return t;
end;
已選擇10行。
SQL> select rownum,text from user_source where name='TEST';
顯示行號
19. 建立用戶及登陸
SQL> create user MascotZhuang IDENTIFIED BY MascotZhuang;
用戶已創(chuàng)建
密碼
用戶名
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
SQL> grant create session to MascotZhuang; 賦予能夠連上數(shù)據(jù)庫的權(quán)力
授權(quán)成功。
如果沒有這一句會發(fā)生以下錯誤：
ERROR：
ORA-01045: user MASCOTZHUANG lacks CREATE SESSION privilege; logon denied
C:\>sqlplus "MascotZhuang/MascotZhuang"
連接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 – Production
注意：
如果以 MascotZhuang/MascotZhuang as sysdba
登陸，相當(dāng)于/ as sysdba登陸。
通過show user可以看到user還是sys，而不是MascotZhuang
SQL>
20. 授權(quán)和收回權(quán)限
授予全部權(quán)限
SQL> grant all on test_package to MascotZhuang;
授權(quán)成功。
授予特定權(quán)限
SQL> grant execute on test_package to MascotZhuang;
授權(quán)成功。
收回權(quán)限
SQL> revoke all on test_package from MascotZhuang;
撤銷成功。
創(chuàng)建的用戶使用包
SQL> var test1 number;
SQL> set serveroutput on
SQL> exec :test1:=sys.test_package.test(20);
20
PL/SQL 過程已成功完成。
SQL>
21. 直接依賴性
create or replace procedure test_dependency
(p_print char)
is
begin
printsomething(p_print);
end;
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
/
當(dāng)創(chuàng)建這個procedure的時候，會發(fā)現(xiàn)一下錯誤。
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3 PLS-00201: 必須說明標(biāo)識符 'PRINTSOMETHING'
5/3 PL/SQL: Statement ignored
因為這個時候，printsomething這個procedure還沒有創(chuàng)建。換句話說，test_dependency依賴于printsomething。所以在printsomething創(chuàng)建之前，test_dependency是無效的。
SQL> select status from user_objects where object_name='TEST_DEPENDENCY';
STATUS
-------
INVALID
因此，必須創(chuàng)建printsomething這個過程。
create or replace procedure printsomething
(p_print char)
is
begin
dbms_output.put_line(p_print);
end;
/
這時候，test_denpendency的status還是invalid。需要進(jìn)行重現(xiàn)編譯，才能使得status為valid。
SQL> alter procedure test_dependency compile;
SQL> select status from user_objects where object_name='TEST_DEPENDENCY';
STATUS
-------
VALID
22. 查看依賴性
SQL> select referenced_name,referenced_type from user_dependencies where name='TEST_DEPENDENCY';
REFERENCED_NAME REFERENCED_T
------------------------------ ------------
STANDARD PACKAGE
SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE
PRINTSOMETHING PROCEDURE
依賴系統(tǒng)的PACKAGE
TEST_DEPENDENCY所依賴的procedure
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
SQL> select referenced_name,referenced_type from user_dependencies where name='PRINTSOMETHING';
REFERENCED_NAME REFERENCED_T
------------------------------ ------------
STANDARD PACKAGE
SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE
依賴系統(tǒng)的PACKAGE
DBMS_OUTPUT PACKAGE
23. 包之間的調(diào)用
create or replace package test_global
is
global_v number(3):=0;
都有一個包的全局變量
procedure setValue(p1 in number);
procedure print(p1 in number);
end;
/
create or replace package body test_global
is
procedure setValue(p1 in number)
is
begin
global_v:=p1;
dbms_output.put_line('this is test_global,global_v is ' || global_v);
test1_global.setValue(3);
end setValue;
procedure print(p1 in number)
調(diào)用test1_global這個包中的setValue這個過程。只需加上這個包名即可。
is
begin
dbms_output.put_line('test_global, global_v is ' || global_v);
end print;
end;
/
create or replace package test1_global
is
global_v number(3):=0;
都有一個包的全局變量
procedure setValue(p1 in number);
procedure print(p1 in number);
end;
/
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
create or replace package body test1_global
is
procedure setValue(p1 in number)
is
begin
global_v:=p1;
dbms_output.put_line('this is test1_global,global_v is ' || global_v);
end setValue;
procedure print(p1 in number)
is
begin
dbms_output.put_line('test1_global, global_v is ' || global_v);
end print;
end;
/
setValue過程對全局變量賦值。但是各自的包調(diào)用各自的過程，修改各自的全局變量。
SQL> exec test_global.setValue(200);
this is test_global,global_v is 200
this is test1_global,global_v is 3
PL/SQL 過程已成功完成。
SQL> exec test_global.print(10);
test_global, global_v is 200
PL/SQL 過程已成功完成。
SQL> exec test1_global.print(10);
各自的過程修改的自己的全局變量。
各自的過程修改的自己的全局變量。
test1_global, global_v is 3
PL/SQL 過程已成功完成。
24. 建立觸發(fā)器（僅說明觸發(fā)器的工作原理）
建立一個簡單的表：
create table table_test_trigger(id char(10));
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
建立簡單的觸發(fā)器：
create or replace trigger myTrigger
after insert on table_test_trigger
觸發(fā)器觸發(fā)的時機，有記錄插入到table_test_trigger后就觸發(fā)。
begin
dbms_output.put_line('something is inserted!!');
觸發(fā)器體，觸發(fā)器處理的東西
end;
/
SQL> insert into table_test_trigger Values('01');
插入一條記錄來觸發(fā)觸發(fā)器。
something is inserted!!
觸發(fā)器被觸發(fā)。
已創(chuàng)建 1 行。
25. 觸發(fā)器的分類
分為：行級觸發(fā)器和語句級觸發(fā)器
行級觸發(fā)器：對于DML語句影響的每一行都觸發(fā)觸發(fā)器代碼。只適合于UPDATE和DELETE事件。
語句級觸發(fā)器：對該事件觸發(fā)一次觸發(fā)器。INSERT事件
26. 稍復(fù)雜的觸發(fā)器
第一個觸發(fā)器：
create table table_test_trigger(id char(10),name char(10));
insert into table_test_trigger values('01','a');
insert into table_test_trigger values('02','b');
insert into table_test_trigger values('03','c');
insert into table_test_trigger values('04','d');
insert into table_test_trigger values('05','e');
create or replace trigger myTrigger
after update of name on table_test_trigger
begin
dbms_output.put_line('something is inserted!!');
end;
/
SQL> update table_test_trigger set name='zz' where id='01';
something is inserted!!
這里比24 建立觸發(fā)器中多了 of name ，表示只有當(dāng)UPDATE NAME這個字段后，才觸發(fā)觸發(fā)器。
觸發(fā)了觸發(fā)器。
已更新 1 行
SQL> update table_test_trigger set id='99' where name='b';
已更新 1 行。
沒有觸發(fā)觸發(fā)器。因為沒對name進(jìn)行update
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
第二個觸發(fā)器：
create or replace trigger myTrigger
after update of name on table_test_trigger
for each row
when(OLD.name='a')
begin
dbms_output.put_line('somthing is inserted!');
For each row表示每行都要觸發(fā)，但要滿足for each row下面when的條件。OLD.name表示未更新前的name指，相對的有個NEW.name表示更新后的值。
end;
/
SQL> update table_test_trigger set name='zb';
somthing is inserted!
只觸發(fā)了一次，因為只有一條記錄滿足條件。
已更新5行。
第三個觸發(fā)器：
create or replace trigger myTrigger
after update of name on table_test_trigger
for each row
begin
dbms_output.put_line('somthing is inserted!');
end;
/
SQL> update table_test_trigger set name='zb';
somthing is inserted!
無條件，對于每行都觸發(fā)，這里的每行是指外部update語句影響到的每行。
這里是無條件的UPDATE，因此，表中有幾條記錄，就應(yīng)該觸發(fā)幾次。
somthing is inserted!
somthing is inserted!
somthing is inserted!
somthing is inserted!
已更新5行。
27. 條件謂詞
create or replace trigger myTrigger
after update of name on table_test_trigger
for each row
begin
if UPDATING THEN
dbms_output.put_line('UPDATING!');
end if;
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
if DELETING THEN
dbms_output.put_line('DELETING!');
end if;
if INSERTING THEN
dbms_output.put_line('INSERTING!');
end if;
end;
/
SQL> update table_test_trigger set name='zb';
觸發(fā)器觸發(fā)
UPDATING!
UPDATING!
UPDATING!
UPDATING!
UPDATING!
已更新5行。
create or replace trigger myTrigger
after update of name on table_test_trigger
for each row
begin
if UPDATING('name') THEN
當(dāng)name字段update的時候才觸發(fā)觸發(fā)器。
dbms_output.put_line('UPDATING!');
end if;
end;
/
SQL> update table_test_trigger set name='zb';
觸發(fā)觸發(fā)器
UPDATING!
UPDATING!
UPDATING!
UPDATING!
UPDATING!
已更新5行。
SQL> update table_test_trigger set id='99';
未觸發(fā)觸發(fā)器
已更新5行。
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
28. Trigger 中不能使用 Commit
create table test(id char(10));
create table test1(id char(10),logdate date);
create or replace trigger myTrigger
after insert on test
begin
insert into test1 values('001',sysdate);
沒有commit的觸發(fā)器
end;
/
SQL> insert into test values('001');
已創(chuàng)建 1 行。
SQL> select * from test1;
ID LOGDATE
---------- ----------
001 14-10月-04
SQL> rollback;
回滾后發(fā)現(xiàn)，兩個表的操作均被撤銷
回退已完成。
SQL> select * from test1;
未選定行
SQL> select * from test;
未選定行
create or replace trigger myTrigger
after insert on test
begin
insert into test1 values('001',sysdate);
commit;
添加了commit
end;
/
SQL> insert into test values('009');
insert into test values('009')
*
ERROR 位于第 1 行:
ORA-04092: COMMIT 不能在觸發(fā)器中
ORA-06512: 在"SCOTT.MYTRIGGER", line 3
ORA-04088: 觸發(fā)器 'SCOTT.MYTRIGGER' 執(zhí)行過程中出錯
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
29. 系統(tǒng)觸發(fā)器舉例
create table test1(id char(10),logdate date);
create or replace trigger logon_trg
after logon on schema
Logon是系統(tǒng)觸發(fā)器。系統(tǒng)觸發(fā)器詳見書本。
begin
insert into test1 values(user,sysdate);
end;
/
再用scott/tiger重新在新連接中登陸，會發(fā)現(xiàn)觸發(fā)了系統(tǒng)觸發(fā)器。注：用sys/sas as dba登陸無效。
SQL> select * from test1;
ID LOGDATE
---------- ----------
SCOTT 14-10月-04
30. instead of 觸發(fā)器
create table test1(id char(10),name char(10));
insert into test1 values('01','ab');
create or replace view test_view
建立視圖
as select name from test1;
create or replace trigger test_trigger
instead of update on test_view
用觸發(fā)器來代替UPDATE，這就是為什么叫instead of觸發(fā)器。
for each row
begin
update test1 set name='zz';
end;
/
SQL> update test_view set name='aa';
已更新 1 行。
SQL> select * from test1;
ID NAME
---------- ----------
01 zz
Oracle9i 開發(fā)指南：PL/SQL 程序設(shè)計
清華大學(xué)出版社 ISBN 7-302-08002-x
Ben 整理 2004 年秋
MSN: mascotzhuang@hotmail.com
31. 創(chuàng)建主鍵
CREATE TABLE test
( id char(10) CONSTRAINT id_pk PRIMARY KEY
主鍵設(shè)置
, name varchar2(20)
);
CREATE TABLE test
( id char(10) CONSTRAINT id_pk NOT NULL
設(shè)置不為空的設(shè)置
, name varchar2(20)
);
insert into test values('b03011117','zb');
32. 創(chuàng)建外鍵
CREATE TABLE test
( id char(10) CONSTRAINT id_pk PRIMARY KEY
, name varchar2(20)
);
CREATE TABLE test1
( studentID varchar2(20) CONSTRAINT studentID_pk PRIMARY KEY
, id CONSTRAINT fk_id REFERENCES test(id)
設(shè)置外鍵，也就是說，這里面的id的值受到test表中id值的限制。
);
insert into test values('b03011117','zb');
insert into test1 values('b03011117','zb');
ERROR 位于第 1 行:
違反了約束條件，出錯。
ORA-02291: 違反完整約束條件 (SCOTT.FK_ID) - 未找到父項關(guān)鍵字

轉(zhuǎn)載于:https://www.cnblogs.com/runningzz/p/7085481.html

總結(jié)

以上是生活随笔為你收集整理的Oracle 常用命令举例的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。