存儲過程

概念：

完成特定功能的SQL語句集合，經過編譯存儲在數據庫中
編譯后sql語句，可以通過調用過程來實現功能，不需要重新寫sql語句

優點：

模塊化程序編程
減少網絡流通量
提高安全性
執行速度快

過程procedure

創建過程

create [ or replace ] procedure 過程名稱 [ ( 參數列表) ] { is | as }----聲明變量begin----執行代碼end [過程名稱];

例如：

create or replace procedure myprocasm number;beginm:=100;dbms_output.put_line(m);end;

調用存儲過程：

exec 存儲過程名稱 [ (參數) ]execute 存儲過程名稱 [ (參數) ]begin----存儲過程名稱 [ (參數) ]end

例如：

declarebeginmyproc;end;

刪除過程

drop procedure 過程名稱

獲取過程返回值

問題：給一個用戶，判斷用戶是否存在--聲明一個參數ret，是輸出參數out，是number類型create or replace procedure myproc2(pid in emp.eid%type,ret out number)asflag number;beginselect count(1) into flag from emp where eid=pid;if flag=1 thendbms_output.put_line(pid||'用戶存在');ret:=1; -- 如果用戶存在就把ret設為1elsedbms_output.put_line(pid||'用戶不存在');ret:=0; -- 如果用戶不存在就把ret設為0end if;end;

– 調用過程

declareinput varchar(50):=('&input');ret number; -- 聲明一個變量ret，類型是number，用來存儲過程的輸出值beginmyproc2(input,ret); -- 獲取到過程的輸出值存儲在ret中dbms_output.put_line(ret);end;

注意：創建過程中，無論參數是輸入參數還是輸出參數，有幾個參數，在調用過程時就要寫多少個參數

函數function

函數的主要特征是必須有一個返回值，通過return來指定函數的返回類型，在函數的任何地方可以通過return expression語句從函數返回，返回類型必須和聲明的返回類型一致。

創建函數

create [ or replace ] function 函數名稱 [ (參數列表) ] return 返回值類型{ is | as }----聲明變量begin----執行代碼end [函數名稱];

返回大值：

創建函數create or replace function func_max(num1 in number, num2 in number)return numberisbeginif num1>=num2 thenreturn num1;elsereturn num2;end if;end; 執行函數declarebegindbms_output.put_line(func_max(78,77));end;

調用函數

declare----創建變量存儲函數調用返回值begin----調用函數賦值給變量end;

刪除函數

drop function 函數名稱

函數和過程區別

至少返回一個變量的限制。而存儲過程可以返回多個，也可以不返回。而函數是可以嵌入在sql中使用的,可以在select中調用，而存儲過程不行。執行的本質都一樣。

函數限制比較多，比如不能用臨時表，只能用表變量．還有一些函數都不可用等等．而存儲過程的限制相對就比較少

一般來說，存儲過程實現的功能要復雜一點，而函數的實現的功能針對性比較強。

對于存儲過程來說可以返回參數，而函數只能返回值或者表對象。

存儲過程一般是作為一個獨立的部分來執行（EXEC執行），而函數可以作為查詢語句的一個部分來調用（SELECT調用），由于函數可以返回一個表對象，因此它可以在查詢語句中位于FROM關鍵字的后面。

當存儲過程和函數被執行的時候，SQL Manager會到procedure cache中去取相應的查詢語句，如果在procedure cache里沒有相應的查詢語句，SQL Manager就會對存儲過程和函數進行編譯。

包和包體

創建包

create [ or replace ] package 包名稱 is | as----定義公用常量、變量、過程、函數等（不能有具體實現）end [ 包名稱 ];

創建包體

create [ or replace ] package body 包名稱 is | as----定義公用常量、變量、過程、函數等----實現公用過程和函數end [ 包名稱 ];

調用包

declare----定義變量begin----包名.元素名稱(參數)end

刪除包或者包體

drop package [ body ] [ user. ] 包名

觸發器tigger

某個條件成立時，觸發器里面定義的語句會被自動執行

創建觸發器

create [or replace] tigger 觸發器名 { before | after } { insert | update | delete }on 表名 ---- 數據庫觸發器所在的表。[for each row] ---- 對表的每一行觸發器執行一次。如果沒有這一選項，則只對整個表執行一次。begin----pl/sql語句end

刪除觸發器

drop tigger 觸發器名稱

######觸發器功能???

允許/限制對表的修改
自動生成派生列，比如自增字段
強制數據一致性
提供審計和日志記錄
防止無效的事務處理
啟用復雜的業務邏輯

例子：

--創建觸發器，當星期四的時候不能修改emp表中的數據create or replace trigger mytriggerbefore insert or update or deleteon empbeginif to_char(systimestamp,'DY')='星期四' thenraise_application_error(-20001,'今天是星期四，不能修改'); -- -20000 到 -20999之間end if;end;

使用觸發器實現序號自增添加數據

------創建表create table tbuser(uuid number primary key,uname varchar2(40),upw varchar2(40));------創建序列create sequence myseq increment by 1start with 1001nomaxvaluenocyclecache 20;------創建觸發器create or replace trigger mytrigger2before inserton tbuserfor each rowdeclare uuuid number;beginselect myseq.nextval into uuuid from dual; -- 獲取序列號:new.uuid:=uuuid; -- :new 表示將要插入的那條記錄 -- :new.uuid 表示新插入記錄的uuidend;

寫一個日志表當對tbuser進行增加刪除修改的時候進行日志記錄

------創建表create table mylog(l_name varchar(40),l_type varchar(40),l_cdate date);------創建觸發器create or replace trigger mytrigger3after update or delete or inserton tbuserdeclarec_type mylog.l_type%type;beginif inserting thenc_type:='insert';dbms_output.put_line('插入了數據');elsif deleting thenc_type:='delete';dbms_output.put_line('刪除了數據');elsif updating thenc_type:='update';dbms_output.put_line('修改了數據');end if; insert into mylog values(user,c_type,sysdate);end;

創建觸發器，用來記錄刪除的數據

------創建表create table del_log(uuid number primary key,uname varchar2(40),upw varchar2(40));------創建觸發器create or replace trigger mytrigger4after delete on tbuserfor each rowdeclarebegininsert into del_log values(:old.uuid,:old.uname,:old.upw); -- :old 表示當前記錄 :new表示下一條記錄end;

:new 和 :old

:new --為一個引用最新的列值;

:old --為一個引用以前的列值; 這兩個變量只有在使用了關鍵字 "for each row"時才存在.且update語句兩個都有,而insert只有:new ,delect 只有:old;

create or replace trigger uptsalyafter update on empfor each rowbeginif :old.esalary > :new.esalary then ----注意 new 和 old ，new 表示修改后的數據， old 表示修改前的數據dbms_output.put_line('工資降低');elsif :old.esalary < :new.esalary then ----注意 new 和 old ，new 表示修改后的數據， old 表示修改前的數據dbms_output.put_line('工資增加');elsedbms_output.put_line('工資沒變');end if;dbms_output.put_line('更新前的工資'||:old.esalary);dbms_output.put_line('更新后的工資'||:new.esalary);end;

總結

以上是生活随笔為你收集整理的Oracle-存储过程（procedure、function、package、tigger）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。