oracle存儲過程的基本語法及注意事項

　　基本結構

　　CREATE OR REPLACE PROCEDURE 存儲過程名字

　　(

　　參數 IN NUMBER

　　參數 IN NUMBER

　　) IS

　　變量 INTEGER :=;

　　變量 DATE;

　　BEGIN

　　END 存儲過程名字

　　SELECT INTO STATEMENT

　　將select查詢的結果存入到變量中可以同時將多個列存儲多個變量中必須有一條

　　記錄否則拋出異常(如果沒有記錄拋出NO_DATA_FOUND)

　　例子

　　BEGIN

　　SELECT colcol into 變量變量 FROM typestruct where xxx;

　　EXCEPTION

　　WHEN NO_DATA_FOUND THEN

　　xxxx;

　　END;

　　

　　IF 判斷

　　IF V_TEST= THEN

　　BEGIN

　　do something

　　END;

　　END IF;

　　while 循環

　　WHILE V_TEST= LOOP

　　BEGIN

　　XXXX

　　END;

　　END LOOP;

　　變量賦值

　　V_TEST := ;

　　用for in 使用cursor

　　

　　IS

　　CURSOR cur IS SELECT * FROM xxx;

　　BEGIN

　　FOR cur_result in cur LOOP

　　BEGIN

　　V_SUM :=cur_result列名+cur_result列名

　　END;

　　END LOOP;

　　END;

　　帶參數的cursor

　　CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

　　OPEN C_USER(變量值);

　　LOOP

　　FETCH C_USER INTO V_NAME;

　　EXIT FETCH C_USER%NOTFOUND;

　　do something

　　END LOOP;

　　CLOSE C_USER;

　　用pl/sql developer debug

　　連接數據庫後建立一個Test WINDOW

　　在窗口輸入調用SP的代碼F開始debugCTRL+N單步調試

　　關於oracle存儲過程的若干問題備忘

　　在oracle中數據表別名不能加as如

　　select aappname from appinfo a; 正確

　　select aappname from appinfo as a; 錯誤

　　也許是怕和oracle中的存儲過程中的關鍵字as沖突的問題吧

　　在存儲過程中select某一字段時後面必須緊跟into如果select整個記錄利用游標的話就另當別論了

　　select afkeynode into kn from APPFOUNDATION af where afappid=aid and affoundationid=fid; 有into正確編譯

　　select afkeynode from APPFOUNDATION af where afappid=aid and affoundationid=fid; 沒有into編譯報錯提示Compilation

　　Error: PLS: an INTO clause is expected in this SELECT statement

　　在利用selectinto語法時必須先確保數據庫中有該條記錄否則會報出no data found異常

　　可以在該語法之前先利用select count(*) from 查看數據庫中是否存在該記錄如果存在再利用selectinto

　　在存儲過程中別名不能和字段名稱相同否則雖然編譯可以通過但在運行階段會報錯

　　select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid; 正確運行

　　select afkeynode into kn from APPFOUNDATION af where afappid=appid and affoundationid=foundationid; 運行階段報錯提示

　　ORA:exact fetch returns more than requested number of rows

　　在存儲過程中關於出現null的問題

　　假設有一個表A定義如下

　　create table A(

　　id varchar() primary key not null

　　vcount number() not null

　　bid varchar() not null 外鍵

　　);

　　如果在存儲過程中使用如下語句

　　select sum(vcount) into fcount from A where bid=xxxxxx;

　　如果A表中不存在bid=xxxxxx的記錄則fcount=null(即使fcount定義時設置了默認值如fcount number():=依然無效fcount還是會變成null)這樣以後使用fcount時就可能有問題所以在這裡最好先判斷一下

　　if fcount is null then

　　fcount:=;

　　end if;

　　這樣就一切ok了

　　Hibernate調用oracle存儲過程

　　thispnumberManagergetHibernateTemplate()execute(

　　new HibernateCallback() {

　　public Object doInHibernate(Session session)

　　throws HibernateException SQLException {

　　CallableStatement cs = session

　　nnection()

　　prepareCall({call modifyapppnumber_remain(?)});

　　cssetString( foundationid);

　　csexecute();

　　return null;

　　}

　　});

From:http://tw.wingwit.com/Article/program/Oracle/201311/16725.html