前言~
? ? ??今天莫名的接到一個(gè)任務(wù)，需要使用oracle定時(shí)任務(wù)和oracle存儲(chǔ)過程來每日創(chuàng)建一個(gè)日志表，由于小編呢尚未接觸過存儲(chǔ)過程和定時(shí)任務(wù)，所以今天學(xué)習(xí)了一番，特定來總結(jié)一下。望能給予一些未接觸過存儲(chǔ)過程的小伙伴一些幫助。
? ? ? ?今入今天的正題，首先要了解一下oracle的存儲(chǔ)過程，都有哪些結(jié)構(gòu)，而plsql是一個(gè)輔助工具，是能幫助我們更輕松的實(shí)現(xiàn)存儲(chǔ)過程。
上述就是一個(gè)無參的存儲(chǔ)過程實(shí)例，一個(gè)存儲(chǔ)過程大體分為這么幾個(gè)部分：
1）、創(chuàng)建語句：create or replace procedure?存儲(chǔ)過程名稱 [authid current_user] ；
? ? ? ? ?“”[]“”中括號(hào)的內(nèi)容是可選的，其表示修改存儲(chǔ)過程，加入authid current_user時(shí)存儲(chǔ)過程可以使用role權(quán)限。
? ? ? ?如果沒有or replace語句，那只是新建一個(gè)存儲(chǔ)過程，如果系統(tǒng)中存在相同的存儲(chǔ)過程，則會(huì)報(bào)錯(cuò)，Create or replace procedure 如果系統(tǒng)中沒有此存儲(chǔ)過程就新建一個(gè)，如果系統(tǒng)中有此存儲(chǔ)過程則把原來刪除掉，重新創(chuàng)建一個(gè)存儲(chǔ)過程。
? ? ? ?存儲(chǔ)過程名定義：包括存儲(chǔ)過程名和參數(shù)列表、參數(shù)名和參數(shù)類型。參數(shù)列表可不寫，如例子所示。參數(shù)名不能重復(fù)，并且每個(gè)參數(shù)之間用分號(hào)“? ?；” 隔開， 參數(shù)傳遞方式:IN, OUT, IN OUT。如下面例子所示：
下面說明一下參數(shù)傳遞方式：
? ? ? ? in：表示輸入?yún)?shù)，調(diào)用存儲(chǔ)過程時(shí)從外面?zhèn)鬟M(jìn)來的，它的值不能修改。?
? ? ? ? out：表示輸出參數(shù)，當(dāng)一個(gè)參數(shù)被指定為OUT類型時(shí)，如果還未調(diào)用存儲(chǔ)過程之前對(duì)該參數(shù)進(jìn)行了賦值，那么在存儲(chǔ)過程中該參數(shù)的值仍然是null，但是如果在調(diào)用過程中對(duì)該參數(shù)進(jìn)行賦值，那么值不為null。
? ? ? ? in out：表示輸入輸出參數(shù)，它的值可以修改。
參數(shù)的數(shù)據(jù)類型只需要指明類型名即可，不需要指定寬度。參數(shù)的寬度由外部調(diào)用者決定。過程可以有參數(shù)，也可以沒有參數(shù)。
我們看到例子中存在一個(gè)“”as“”，它表示變量聲明塊，可以理解為plsql中的declare關(guān)鍵字，用于聲明變量。除了as外，還有is。變量聲明塊用于聲明該存儲(chǔ)過程需要用到的變量，它的作用域?yàn)樵摯鎯?chǔ)過程。另外這里聲明的變量必須指定寬度。遵循PL/SQL的變量聲明規(guī)范。
? ? ? ?其中，as和is的區(qū)別：在視圖(VIEW)中只能用AS不能用IS；
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?在游標(biāo)(CURSOR)中只能用IS不能用AS。
過程語句塊：從begin 關(guān)鍵字開始為過程的語句塊。存儲(chǔ)過程的具體邏輯在這里來實(shí)現(xiàn)。
異常處理塊：關(guān)鍵字為exception ，為處理語句產(chǎn)生的異常。該部分為可選
結(jié)束塊：由end關(guān)鍵字結(jié)果。
2)、下面講解一下參數(shù)列表中參數(shù)的默認(rèn)值
值得注意的是：默認(rèn)值僅僅支持IN傳輸類型的參數(shù)。OUT 和 IN OUT不能指定默認(rèn)值
上述情況是default關(guān)鍵字修飾的是最后一個(gè)參數(shù)，如果是修飾第一個(gè)參數(shù)呢？
如果我們想使用第一個(gè)參數(shù)的默認(rèn)值時(shí)，exec procdefault2('aa'); 這樣是會(huì)報(bào)錯(cuò)的。
那怎么變呢？可以指定參數(shù)的值。
SQL> exec procdefault2(p2 =>'aa');? ?這樣就OK了，指定aa傳給參數(shù)p2。
3）、繼續(xù)講解存儲(chǔ)過程內(nèi)部塊
? ? ? ?我們知道了存儲(chǔ)過程的結(jié)構(gòu)，語句塊由begin開始，以end結(jié)束。這些塊是可以嵌套。在語句塊中可以嵌套任何以下的塊：Declare … begin … exception … end; ?
4）、存儲(chǔ)過程中的循環(huán)
存儲(chǔ)過程的循環(huán)語句塊有：for...in...loop、while和loop循環(huán)。下面分別給予相關(guān)實(shí)例。
（1）、for...in...loop
實(shí)例一 循環(huán)遍歷游標(biāo)：
create or replace procedure proc_test
as
cursor c1
is
select * from? dat_trade;
begin
for x in c1
loop
dbms_output.put_line (x.id);
end loop;
end proc_test;
實(shí)例二?根據(jù)數(shù)值進(jìn)行循環(huán)：
create or replace procedure proc_test (v_num in NUMBER)
as
begin
for x in 1..100 loop
dbms_output.put_line (x);
end loop;
end proc_test;
實(shí)例三?在過程里指定輸入?yún)?shù) v_num. 在調(diào)用過程時(shí)指定循環(huán)次數(shù)：
create or replace procedure proc_test (v_num IN NUMBER)
as
begin
for x in 1 .. v_num
loop
dbms_output.put_line (x);
end loop;
end proc_test;
（2）、loop循環(huán)
LOOP
loop
delete from? orders
where senddate < to_char (add_months (sysdate, -3),'yyyy-mm-dd') and rownum < 1000;
exit when SQL%ROWCOUNT < 1;
commit;
end loop;
這里的 SQL%ROWCOUNT 是隱士游標(biāo)。 除了這個(gè)，還有其他幾
個(gè)：%found，%notfound， %isopen。
（3）while循環(huán)
create or replace procedure proc_test (v_num in number)
as
i number := 1;
begin
while i < v_num
loop
begin
i := i + 1;
dbms_output.put_line (i);
end;
end loop;
end proc_test;
5）、 存儲(chǔ)過程中的判斷
存儲(chǔ)過程的判斷語句塊有：if?條件語句、case ... when ... end case兩種
下面給出實(shí)例：
（1）、單if實(shí)例（if...then...end if; &&?if...then...else...end if;）
實(shí)例一：
create or replace procedure pro_test is
--邏輯判斷變量
? exit_table_data varchar2(40);? --判斷表數(shù)據(jù)是否存在
--sql語句執(zhí)行變量
? execu_sql varchar2(2000);
begin
? execu_sql := 'select count(*) from user';
? execute immediate execu_sql into exit_table_data;
? if exit_table_data=0 then
? execu_sql := 'insert into user values('大明')';
? execute immediate execu_sql;
? commit;
? end if;
end pro_test;
實(shí)例二：
create or replace procedure pro_test is
--邏輯判斷變量
? exit_table_data varchar2(40);? --判斷表數(shù)據(jù)是否存在
--sql語句執(zhí)行變量
? execu_sql varchar2(2000);
begin
? execu_sql := 'select count(*) from user';
? execute immediate execu_sql into exit_table_data;
? if exit_table_data=0 then
? execu_sql := 'insert into user values('大明')';
? execute immediate execu_sql;
? commit;
? else
? execu_sql:= 'update user set username='大華'';
? execute immediate execu_sql;
? commit;
? end if;
end pro_test;
（2）、多if實(shí)例（if...then...elseif...then...else...end if;）
create or replace procedure proc_test (v_num in number)
as
begin
if v_num < 10
then
dbms_output.put_line (v_num);
elseif v_num > 10 and v_num < 50
then
dbms_output.put_line (v_num - 10);
else
dbms_output.put_line (v_num - 50);
end if;
end proc_test;
（2）、case ... when ... end case
實(shí)例一：
create or replace procedure proc_test (v_num in number)
as
begin
case v_num
when 1 then
dbms_output.put_line (v_num);
when 2 then
dbms_output.put_line (v_num);
when 3 then
dbms_output.put_line (v_num);
else null;
end case;
end proc_test;
6）、給變量賦值
? ? ? ?我們?cè)趨?shù)列表定義輸出參數(shù)、輸入輸出參數(shù)yi，以及在參數(shù)名位置定義參數(shù)，可能都需要一個(gè)賦值操作，讓查詢sql的結(jié)果賦值或者定義輸入?yún)?shù)賦值等等，那么我們可以使用什么方法給這些參數(shù)賦值呢？
? ? ? ?下面列舉出一些常用的為變量賦值的方法：
? ? ? ?1、直接法
使用“? :=? ”?的符號(hào)為變量賦值，例如:?v_pare := "0"；
? ? ? ? 2、select into
假如變量為v_pare,那么為它賦值的語句為：select count(*) into v_pare from user；
? ? ? ? 3、execute immediate?變量名（查詢sql語句結(jié)果賦值給它的變量）into?變量名
例如：
?? v_sqlfalg?? := 'select count(*) from user_tables where table_name='''||v_tablename || '''';
?? execute immediate v_sqlfalg into v_flag;
?? 其中，v_tablename、v_sqlfalg、v_flag都是變量；
select into和execute immediate的區(qū)別：
? ? ? ?1、execute immediate?賦值的變量是通過select語句查詢出來的，而select?into是直接賦值給變量的。
7）、存儲(chǔ)過程跳出循環(huán)
oracle存儲(chǔ)過程可以使用3種方法跳出循環(huán)，分別是return、exit、continue；
它們的區(qū)別為：
? ? ? ? 1、return是直接跳出存儲(chǔ)過程；
? ? ? ? 2、如果存在多層循環(huán)，exit是直接跳出存儲(chǔ)過程的本次循環(huán)，而去執(zhí)行上一級(jí)循環(huán)的循環(huán)條件；
? ? ? ? 3、continue是本次循環(huán)后面的代碼部分不再執(zhí)行，轉(zhuǎn)而執(zhí)行本循環(huán)的下一次循環(huán)。
8）、Oracle存儲(chǔ)過程中是否需要寫commit的問題
是否需要在存儲(chǔ)過程中寫commit主要依據(jù)需求:
(1) 如果是不需要在存儲(chǔ)過程中進(jìn)行提交，而是由調(diào)用程序負(fù)責(zé)提交或者回滾，那么不需要在存儲(chǔ)過程中commit或者rollback。
(2) 如果不想由調(diào)用程序負(fù)責(zé)提交或者回滾，那么就應(yīng)該在存儲(chǔ)過程中進(jìn)行commit或rollback;?
另外,如果是純后臺(tái)數(shù)據(jù)庫開發(fā)，一定要寫.只是寫的時(shí)機(jī)同樣是分為兩種,一種是寫在過程里面;另一種是寫在調(diào)用存儲(chǔ)過程之后. 而之所以要寫commit的原因是,Oracle的默認(rèn)事務(wù)級(jí)別是READ COMMITED;默認(rèn)情況下，Oracle是不會(huì)自動(dòng)提交的,需要手動(dòng)提交才ok.
9）、使用plsql創(chuàng)建存儲(chǔ)過程步驟
1、登錄plsql后，在對(duì)象框中找到“Procedures”,點(diǎn)擊右鍵，找到新建，如圖所示：
2、進(jìn)入到新建界面，如圖所示：
3、最終就進(jìn)入到存儲(chǔ)過程結(jié)構(gòu)中，你要做的就是編寫存儲(chǔ)過程邏輯。
10）、使用plsql對(duì)存儲(chǔ)過程進(jìn)行調(diào)試
1、在“Procedures”下拉列表中找到已經(jīng)編寫好的存儲(chǔ)過程，點(diǎn)擊右鍵，找到“測(cè)試”，如圖所示：
2、PL\SQL會(huì)打開調(diào)試界面，圖中位置1的按鈕就是開始調(diào)試的按鈕，在調(diào)試之前要填寫輸入?yún)?shù)的值，位置2就是填寫參數(shù)的地方，如果有多個(gè)參數(shù)，會(huì)有多行參數(shù)框，按參數(shù)名填寫相應(yīng)的參數(shù)即可，如果沒有參數(shù)，可以不填。
3、填寫完參數(shù)，單擊開始調(diào)試按鈕后，調(diào)試的界面會(huì)發(fā)生一些變化。圖中位置1的變化，說明存過已經(jīng)處于執(zhí)行狀態(tài)，別人不能再編譯或者執(zhí)行。位置2的按鈕就是執(zhí)行按鈕，單擊這個(gè)按鈕存過會(huì)執(zhí)行完成或者遇到bug跳出，否則是不會(huì)停下來的，調(diào)試時(shí)不會(huì)用這個(gè)按鈕的。位置3的按鈕才是關(guān)鍵——單步執(zhí)行，就是讓代碼一行一行的執(zhí)行，位置4的按鈕是跳出單步執(zhí)行，等待下一個(gè)指令。
今天的課程就講解到這里，如果有不懂的地方，或者有建議，麻煩下方留言！
