Oracle數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過程的示例詳解
1.1,Oracle存儲(chǔ)過程簡(jiǎn)介:
存儲(chǔ)過程是事先經(jīng)過編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句的集合,調(diào)用存儲(chǔ)過程可以簡(jiǎn)化應(yīng)用開發(fā)人員的很多工作,
減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸,對(duì)于提高數(shù)據(jù)處理的效率是有好處的。
優(yōu)點(diǎn):
- 允許模塊化程序設(shè)計(jì),就是說(shuō)只需要?jiǎng)?chuàng)建一次過程,以后在程序中就可以調(diào)用該過程任意次。
- 允許更快執(zhí)行,如果某操作需要執(zhí)行大量SQL語(yǔ)句或重復(fù)執(zhí)行,存儲(chǔ)過程比SQL語(yǔ)句執(zhí)行的要快。
- 減少網(wǎng)絡(luò)流量,例如一個(gè)需要數(shù)百行的SQL代碼的操作有一條執(zhí)行語(yǔ)句完成,不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼。
- 更好的安全機(jī)制,對(duì)于沒有權(quán)限執(zhí)行存儲(chǔ)過程的用戶,也可授權(quán)他們執(zhí)行存儲(chǔ)過程。
1.2,創(chuàng)建存儲(chǔ)過程的語(yǔ)法:
create [or replace] procedure 存儲(chǔ)過程名(param1 in type,param2 out type) as 變量1 類型(值范圍); 變量2 類型(值范圍); begin select count(*) into 變量1 from 表A where列名=param1; if (判斷條件) then select 列名 into 變量2 from 表A where列名=param1; dbms_output.Put_line('打印信息'); elsif (判斷條件) then dbms_output.Put_line('打印信息'); else raise 異常名(NO_DATA_FOUND); end if; exception when others then rollback; end;
參數(shù)的幾種類型:
in 是參數(shù)的默認(rèn)模式,這種模式就是在程序運(yùn)行的時(shí)候已經(jīng)具有值,在程序體中值不會(huì)改變。
out 模式定義的參數(shù)只能在過程體內(nèi)部賦值,表示該參數(shù)可以將某個(gè)值傳遞回調(diào)用他的過程
in out 表示高參數(shù)可以向該過程中傳遞值,也可以將某個(gè)值傳出去
1.3,示范一些存儲(chǔ)過程
[下面一些存儲(chǔ)過程的操作根據(jù)自己數(shù)據(jù)庫(kù)中的內(nèi)容進(jìn)行內(nèi)容顯示,只要顯示內(nèi)容就正確,報(bào)錯(cuò)除外- -,還有存儲(chǔ)過程盡量不要粘貼代碼,很容易報(bào)錯(cuò)]:
1.3.1,不帶參數(shù)的存儲(chǔ)過程:
CREATE OR REPLACE PROCEDURE MYDEMO02 AS name VARCHAR(10); age NUMBER(10); BEGIN name := 'xiaoming';--:=則是對(duì)屬性進(jìn)行賦值 age := 18; dbms_output.put_line ( 'name=' || name || ', age=' || age );--這條是輸出語(yǔ)句 END; --存儲(chǔ)過程調(diào)用(下面只是調(diào)用存儲(chǔ)過程語(yǔ)法) BEGIN MYDEMO02(); END;
1.3.2,帶參數(shù)的存儲(chǔ)過程:
CREATE OR REPLACE procedure MYDEMO03(name in varchar,age in int) AS BEGIN dbms_output.put_line('name='||name||', age='||age); END; --存儲(chǔ)過程調(diào)用 BEGIN MYDEMO03('姜煜',18); END;
1.3.3,出現(xiàn)異常的輸出存儲(chǔ)過程:
CREATE OR REPLACE PROCEDURE MYDEMO04 AS age INT; BEGIN age:=10/0; dbms_output.put_line(age); EXCEPTION when others then --處理異常 dbms_output.put_line('error'); END; --調(diào)用存儲(chǔ)過程 BEGIN MYDEMO04; END;
- Oracle常見的三大異常分類[沒有詳細(xì)陳述,有興趣的同學(xué)可以自行查下]
- 預(yù)定義異常:由PL/SQL定義的異常。由于它們已在standard包中預(yù)定義了,因此,這些預(yù)定義異??梢灾苯釉诔绦蛑惺褂茫槐卦俣x部分聲明。
- 非預(yù)定義異常:用于處理預(yù)定義異常所不能處理的Oracle錯(cuò)誤。
- 自定義異常:用戶自定義的異常,需要在定義部分聲明后才能在可執(zhí)行部分使用。用戶自定義異常對(duì)應(yīng)的錯(cuò)誤不一定是Oracle錯(cuò)誤,例如它可能是一個(gè)數(shù)據(jù)錯(cuò)誤。
1.3.4,獲取當(dāng)前時(shí)間和總?cè)藬?shù):
CREATE OR REPLACE PROCEDURE TEST_COUNT01 IS v_total int; v_date varchar(20); BEGIN select count(*) into v_total from EMP_TEST WHERE ENAME ='燕小六'; --into是賦值的關(guān)鍵字 select to_char(sysdate,'yyyy-mm-dd')into v_date FROM EMP_TEST WHERE ENAME ='郭芙蓉'; DBMS_OUTPUT.put_line('總?cè)藬?shù):'||v_total); DBMS_OUTPUT.put_line('當(dāng)前日期'||v_date); END; --調(diào)用存儲(chǔ)過程 BEGIN TEST_COUNT01(); END;
1.3.5,帶輸入?yún)?shù)和輸出參數(shù)的存儲(chǔ)過程:
CREATE OR REPLACE PROCEDURE TEST_COUNT04(v_id in int,v_name out varchar2) IS BEGIN SELECT ENAME into v_name FROM EMP_TEST WHERE EMPNO = v_id; dbms_output.put_line(v_name); EXCEPTION when no_data_found then dbms_output.put_line('no_data_found'); END; --調(diào)用存儲(chǔ)過程 DECLARE v_name varchar(200); BEGIN TEST_COUNT04('1002',v_name); END;
1.3.6,查詢存儲(chǔ)過程以及其他:
CREATE OR REPLACE PROCEDURE job_day04(de in varchar,name out varchar,App_Code out varchar,error_Msg out varchar) AS BEGIN SELECT ENAME into name FROM EMP_TEST WHERE ENAME=de; EXCEPTION WHEN others THEN error_Msg:='未找到數(shù)據(jù)'; END; --調(diào)用存儲(chǔ)過程 DECLARE de varchar(10); ab varchar(10); appcode varchar(20); ermg varchar(20); BEGIN de:= '張三豐'; JOB_DAY04(de,ab,appcode,ermg); dbms_output.put_line(ermg); END;
1.3.7,向數(shù)據(jù)庫(kù)中添加數(shù)據(jù)的存儲(chǔ)過程
CREATE OR REPLACE PROCEDURE job_day05(do1 in varchar,dn1 in varchar,eo1 in number,en1 in varchar,App_Code out varchar,error_Msg out varchar) AS BEGIN INSERT INTO STUDENT(NAME,CLASS)VALUES(do1,dn1); INSERT INTO COMPANY(EMPID,NAME,DEPARNAME)VALUES(eo1,en1,do1); COMMIT; EXCEPTION WHEN OTHERS THEN App_Code:=-1; error_Msg:='插入失敗'; END; --調(diào)用存儲(chǔ)過程 DECLARE do1 varchar(10); dn1 varchar(10); eo1 number(20); App_Code varchar(20); error_Msg varchar(20); BEGIN do1:= '張三豐'; dn1:='新橋'; eo1:=1001; JOB_DAY04(do1,dn1,App_Code,error_Msg); dbms_output.put_line(ermg); END;
這個(gè)比較麻煩,做的時(shí)候假如報(bào)錯(cuò)就別找了- -我找了好久也沒找到,,,
2.0,游標(biāo)的使用,看到的一段解釋很好的概念,如下:
- 游標(biāo)是SQL的一個(gè)內(nèi)存工作區(qū),由系統(tǒng)或用戶以變量的形式定義。游標(biāo)的作用就是用于臨時(shí)存儲(chǔ)從數(shù)據(jù)庫(kù)中提取的數(shù)據(jù)塊。在某些情況下,需要把數(shù)據(jù)從存放
- 在磁盤的表中調(diào)到計(jì)算機(jī)內(nèi)存中進(jìn)行處理,最后將處理結(jié)果顯示出來(lái)或最終寫回?cái)?shù)據(jù)庫(kù)。這樣數(shù)據(jù)處理的速度才會(huì)提高,否則頻繁的磁盤數(shù)據(jù)交換會(huì)降低效率。
- 游標(biāo)有兩種類型:顯式游標(biāo)和隱式游標(biāo)。在前述程序中用到的SELECT...INTO...查詢語(yǔ)句,一次只能從數(shù)據(jù)庫(kù)中提取一行數(shù)據(jù),對(duì)于這種形式的查詢和DML操作,
- 系統(tǒng)都會(huì)使用一個(gè)隱式游標(biāo)。但是如果要提取多行數(shù)據(jù),就要由程序員定義一個(gè)顯式游標(biāo),并通過與游標(biāo)有關(guān)的語(yǔ)句進(jìn)行處理。顯式游標(biāo)對(duì)應(yīng)一個(gè)返回結(jié)果為多
- 行多列的SELECT語(yǔ)句。
- 游標(biāo)一旦打開,數(shù)據(jù)就從數(shù)據(jù)庫(kù)中傳送到游標(biāo)變量中,然后應(yīng)用程序再?gòu)挠螛?biāo)變量中分解出需要的數(shù)據(jù),并進(jìn)行處理。
- 在我們進(jìn)行insert、update、delete和select value into variable 的操作中,使用的是隱式游標(biāo)
- 隱式游標(biāo)的屬性 返回值類型意義
- SQL%ROWCOUNT 整型 代表DML語(yǔ)句成功執(zhí)行的數(shù)據(jù)行數(shù)
- SQL%FOUND 布爾型 值為TRUE代表插入、刪除、更新或單行查詢操作成功
- SQL%NOTFOUND 布爾型 與SQL%FOUND屬性返回值相反
- SQL%ISOPEN 布爾型 DML執(zhí)行過程中為真,結(jié)束后為假
2.1,修改雇員薪資:
CREATE OR REPLACE PROCEDURE job_day06(epo in number) AS BEGIN UPDATE EMPS SET SAL=(SAL+100) WHERE empno = epo; IF SQL%FOUND --SQL%FOUND是隱式游標(biāo) 作用:判斷SQL語(yǔ)句是否成功執(zhí)行,當(dāng)有作用行時(shí)則成功執(zhí)行為true,否則為false。 6 THEN DBMS_OUTPUT.PUT_LINE('成功修改雇員工資!'); commit; else DBMS_OUTPUT.PUT_LINE('修改雇員工資失敗!'); END IF; END; --調(diào)用存儲(chǔ)過程 declare e_number number; begin e_number:=1001; job_day06(e_number); end;
2.2,查詢編號(hào)為1001信息
CREATE OR REPLACE PROCEDURE job_day07 IS BEGIN DECLARE cursor emp_sor is select name,sal FROM EMPS WHERE EMPNO = '1001'; --聲明游標(biāo) cname EMPS.NAME%type; --%type 作用: 聲明的變量ename與EMPS表的NAME列類型一樣 csal EMPS.SAL%type; BEGIN open emp_sor; --打開游標(biāo) loop -- 取游標(biāo)值給變量 FETCH emp_sor into cname,csal; dbms_output.put_line('name:'||cname); exit when emp_sor%notfound; end loop; close emp_sor; --關(guān)閉游標(biāo) end; end; --調(diào)用存儲(chǔ)過程 BEGIN job_day07(); END;
總結(jié):
存儲(chǔ)過程通俗的理解就是就是一個(gè)執(zhí)行過程,調(diào)用的時(shí)候給他所需要的需求就會(huì)對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作,相當(dāng)于我們自己手寫Sql,只不過有了存儲(chǔ)過程
只要調(diào)用一下傳給他參數(shù)他就會(huì)幫我們寫,比較方便,靈活的運(yùn)用存儲(chǔ)過程會(huì)讓我們開發(fā)很方便
到此這篇關(guān)于Oracle數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過程的示例詳解的文章就介紹到這了,更多相關(guān)Oracle數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- 解決PL/SQL修改Oracle存儲(chǔ)過程編譯就卡死的問題
- Oracle如何使用PL/SQL調(diào)試存儲(chǔ)過程
- PL/SQL中編寫Oracle數(shù)據(jù)庫(kù)分頁(yè)的存儲(chǔ)過程
- C#調(diào)用Oracle存儲(chǔ)過程方法介紹(附源碼)
- Oracle存儲(chǔ)過程案例詳解
- oracle調(diào)試存儲(chǔ)過程的過程詳解
- oracle中存儲(chǔ)函數(shù)與存儲(chǔ)過程的區(qū)別介紹
- 詳解Oracle調(diào)試存儲(chǔ)過程
- Oracle存儲(chǔ)過程和存儲(chǔ)函數(shù)創(chuàng)建方法(詳解)
- Oracle存儲(chǔ)過程及調(diào)用
- Oracle在PL/SQL中使用存儲(chǔ)過程
相關(guān)文章
一些Oracle數(shù)據(jù)庫(kù)中的查詢優(yōu)化建議綜合
這篇文章主要介紹了一些Oracle數(shù)據(jù)庫(kù)中的查詢優(yōu)化建議綜合,包括in和not in的使用等一些細(xì)節(jié)上的使用建議,需要的朋友可以參考下2016-01-01Oracle 23ai中重要新特性VECTOR數(shù)據(jù)類型的使用
Oracle 23ai 中的 VECTOR 數(shù)據(jù)類型是 Oracle 數(shù)據(jù)庫(kù)在 AI 領(lǐng)域的一個(gè)重要新特性,它允許用戶以向量的形式存儲(chǔ)數(shù)據(jù),并在這些向量的基礎(chǔ)上進(jìn)行高效的搜索和分析,下面就來(lái)介紹一下如何使用2024-08-08Oracle批量導(dǎo)入文本文件快速的方法(sqlldr實(shí)現(xiàn))
批量導(dǎo)入文本文件在Oracle數(shù)據(jù)庫(kù)操作中經(jīng)常遇見,今天給大家介紹一種通過sqlldr批處理實(shí)現(xiàn)的方法,有需要的朋友們可以參考借鑒,下面來(lái)一起看看。2016-09-09Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)
我們常常需要對(duì)數(shù)據(jù)進(jìn)行遷移,這篇文章主要介紹了Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn),小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來(lái)看看吧2018-05-05Oracle連接遠(yuǎn)程數(shù)據(jù)庫(kù)的四種方法
這篇文章主要為大家詳細(xì)介紹了Oracle連接遠(yuǎn)程數(shù)據(jù)庫(kù)的四種設(shè)置方法和注意事項(xiàng),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-05-05