Oracle數(shù)據(jù)庫創(chuàng)建存儲過程的示例詳解
1.1,Oracle存儲過程簡介:
存儲過程是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段SQL語句的集合,調(diào)用存儲過程可以簡化應用開發(fā)人員的很多工作,
減少數(shù)據(jù)在數(shù)據(jù)庫和應用服務器之間的傳輸,對于提高數(shù)據(jù)處理的效率是有好處的。
優(yōu)點:
- 允許模塊化程序設計,就是說只需要創(chuàng)建一次過程,以后在程序中就可以調(diào)用該過程任意次。
- 允許更快執(zhí)行,如果某操作需要執(zhí)行大量SQL語句或重復執(zhí)行,存儲過程比SQL語句執(zhí)行的要快。
- 減少網(wǎng)絡流量,例如一個需要數(shù)百行的SQL代碼的操作有一條執(zhí)行語句完成,不需要在網(wǎng)絡中發(fā)送數(shù)百行代碼。
- 更好的安全機制,對于沒有權(quán)限執(zhí)行存儲過程的用戶,也可授權(quán)他們執(zhí)行存儲過程。
1.2,創(chuàng)建存儲過程的語法:
create [or replace] procedure 存儲過程名(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ù)的默認模式,這種模式就是在程序運行的時候已經(jīng)具有值,在程序體中值不會改變。
out 模式定義的參數(shù)只能在過程體內(nèi)部賦值,表示該參數(shù)可以將某個值傳遞回調(diào)用他的過程
in out 表示高參數(shù)可以向該過程中傳遞值,也可以將某個值傳出去
1.3,示范一些存儲過程
[下面一些存儲過程的操作根據(jù)自己數(shù)據(jù)庫中的內(nèi)容進行內(nèi)容顯示,只要顯示內(nèi)容就正確,報錯除外- -,還有存儲過程盡量不要粘貼代碼,很容易報錯]:
1.3.1,不帶參數(shù)的存儲過程:
CREATE OR REPLACE PROCEDURE MYDEMO02 AS name VARCHAR(10); age NUMBER(10); BEGIN name := 'xiaoming';--:=則是對屬性進行賦值 age := 18; dbms_output.put_line ( 'name=' || name || ', age=' || age );--這條是輸出語句 END; --存儲過程調(diào)用(下面只是調(diào)用存儲過程語法) BEGIN MYDEMO02(); END;
1.3.2,帶參數(shù)的存儲過程:
CREATE OR REPLACE procedure MYDEMO03(name in varchar,age in int) AS BEGIN dbms_output.put_line('name='||name||', age='||age); END; --存儲過程調(diào)用 BEGIN MYDEMO03('姜煜',18); END;
1.3.3,出現(xiàn)異常的輸出存儲過程:
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)用存儲過程 BEGIN MYDEMO04; END;
- Oracle常見的三大異常分類[沒有詳細陳述,有興趣的同學可以自行查下]
- 預定義異常:由PL/SQL定義的異常。由于它們已在standard包中預定義了,因此,這些預定義異常可以直接在程序中使用,而不必再定義部分聲明。
- 非預定義異常:用于處理預定義異常所不能處理的Oracle錯誤。
- 自定義異常:用戶自定義的異常,需要在定義部分聲明后才能在可執(zhí)行部分使用。用戶自定義異常對應的錯誤不一定是Oracle錯誤,例如它可能是一個數(shù)據(jù)錯誤。
1.3.4,獲取當前時間和總?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('當前日期'||v_date); END; --調(diào)用存儲過程 BEGIN TEST_COUNT01(); END;
1.3.5,帶輸入?yún)?shù)和輸出參數(shù)的存儲過程:
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)用存儲過程 DECLARE v_name varchar(200); BEGIN TEST_COUNT04('1002',v_name); END;
1.3.6,查詢存儲過程以及其他:
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)用存儲過程 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ù)庫中添加數(shù)據(jù)的存儲過程
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)用存儲過程 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;
這個比較麻煩,做的時候假如報錯就別找了- -我找了好久也沒找到,,,
2.0,游標的使用,看到的一段解釋很好的概念,如下:
- 游標是SQL的一個內(nèi)存工作區(qū),由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。在某些情況下,需要把數(shù)據(jù)從存放
- 在磁盤的表中調(diào)到計算機內(nèi)存中進行處理,最后將處理結(jié)果顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理的速度才會提高,否則頻繁的磁盤數(shù)據(jù)交換會降低效率。
- 游標有兩種類型:顯式游標和隱式游標。在前述程序中用到的SELECT...INTO...查詢語句,一次只能從數(shù)據(jù)庫中提取一行數(shù)據(jù),對于這種形式的查詢和DML操作,
- 系統(tǒng)都會使用一個隱式游標。但是如果要提取多行數(shù)據(jù),就要由程序員定義一個顯式游標,并通過與游標有關(guān)的語句進行處理。顯式游標對應一個返回結(jié)果為多
- 行多列的SELECT語句。
- 游標一旦打開,數(shù)據(jù)就從數(shù)據(jù)庫中傳送到游標變量中,然后應用程序再從游標變量中分解出需要的數(shù)據(jù),并進行處理。
- 在我們進行insert、update、delete和select value into variable 的操作中,使用的是隱式游標
- 隱式游標的屬性 返回值類型意義
- SQL%ROWCOUNT 整型 代表DML語句成功執(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是隱式游標 作用:判斷SQL語句是否成功執(zhí)行,當有作用行時則成功執(zhí)行為true,否則為false。 6 THEN DBMS_OUTPUT.PUT_LINE('成功修改雇員工資!'); commit; else DBMS_OUTPUT.PUT_LINE('修改雇員工資失??!'); END IF; END; --調(diào)用存儲過程 declare e_number number; begin e_number:=1001; job_day06(e_number); end;
2.2,查詢編號為1001信息
CREATE OR REPLACE PROCEDURE job_day07 IS BEGIN DECLARE cursor emp_sor is select name,sal FROM EMPS WHERE EMPNO = '1001'; --聲明游標 cname EMPS.NAME%type; --%type 作用: 聲明的變量ename與EMPS表的NAME列類型一樣 csal EMPS.SAL%type; BEGIN open emp_sor; --打開游標 loop -- 取游標值給變量 FETCH emp_sor into cname,csal; dbms_output.put_line('name:'||cname); exit when emp_sor%notfound; end loop; close emp_sor; --關(guān)閉游標 end; end; --調(diào)用存儲過程 BEGIN job_day07(); END;
總結(jié):
存儲過程通俗的理解就是就是一個執(zhí)行過程,調(diào)用的時候給他所需要的需求就會對數(shù)據(jù)庫進行操作,相當于我們自己手寫Sql,只不過有了存儲過程
只要調(diào)用一下傳給他參數(shù)他就會幫我們寫,比較方便,靈活的運用存儲過程會讓我們開發(fā)很方便
到此這篇關(guān)于Oracle數(shù)據(jù)庫創(chuàng)建存儲過程的示例詳解的文章就介紹到這了,更多相關(guān)Oracle數(shù)據(jù)庫創(chuàng)建存儲過程內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
一些Oracle數(shù)據(jù)庫中的查詢優(yōu)化建議綜合
這篇文章主要介紹了一些Oracle數(shù)據(jù)庫中的查詢優(yōu)化建議綜合,包括in和not in的使用等一些細節(jié)上的使用建議,需要的朋友可以參考下2016-01-01Oracle 23ai中重要新特性VECTOR數(shù)據(jù)類型的使用
Oracle 23ai 中的 VECTOR 數(shù)據(jù)類型是 Oracle 數(shù)據(jù)庫在 AI 領域的一個重要新特性,它允許用戶以向量的形式存儲數(shù)據(jù),并在這些向量的基礎上進行高效的搜索和分析,下面就來介紹一下如何使用2024-08-08Oracle批量導入文本文件快速的方法(sqlldr實現(xiàn))
批量導入文本文件在Oracle數(shù)據(jù)庫操作中經(jīng)常遇見,今天給大家介紹一種通過sqlldr批處理實現(xiàn)的方法,有需要的朋友們可以參考借鑒,下面來一起看看。2016-09-09Oracle 10g DG 數(shù)據(jù)文件遷移的實現(xiàn)
我們常常需要對數(shù)據(jù)進行遷移,這篇文章主要介紹了Oracle 10g DG 數(shù)據(jù)文件遷移的實現(xiàn),小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2018-05-05