CREATE OR REPLACE PROCEDURE 存儲過程名字
(
??? 參數1 IN NUMBER,
??? 參數2 IN NUMBER
) IS
變量1 INTEGER :=0;
變量2 DATE;
BEGIN
END 存儲過程名字
2.SELECT INTO STATEMENT
? 將select查詢的結果存入到變量中，可以同時將多個列存儲多個變量中，必須有一條
? 記錄，否則拋出異常(如果沒有記錄拋出NO_DATA_FOUND)
? 例子：
? BEGIN
? SELECT col1,col2 into 變量1,變量2 FROM typestruct where xxx;
? EXCEPTION
? WHEN NO_DATA_FOUND THEN
???? ?xxxx;
? END;
? ...
3.IF 判斷
? IF V_TEST=1 THEN
??? BEGIN
?????? do something
??? END;
? END IF;
4.while 循環
? WHILE V_TEST=1 LOOP
? BEGIN
?XXXX
? END;
? END LOOP;
5.變量賦值
? V_TEST := 123;
6.用for in 使用cursor
? ...
? IS
? CURSOR cur IS SELECT * FROM xxx;
? BEGIN
?FOR cur_result in cur LOOP
??BEGIN
???V_SUM :=cur_result.列名1+cur_result.列名2
??END;
?END LOOP;
? END;
7.帶參數的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;
8.用pl/sql developer debug
? 連接數據庫后建立一個Test WINDOW
? 在窗口輸入調用SP的代碼,F9開始debug,CTRL+N單步調試
關于oracle存儲過程的若干問題備忘
select
?a.appname?
from
?appinfo?a;
--
正確
select
?a.appname?
from
?appinfo?
as
?a;
--
錯誤
??
select
?af.keynode?
into
?kn?
from
?APPFOUNDATION?af?
where
?af.appid
=
aid?
and
?af.foundationid
=
fid;
--
?有into，正確編譯
??
select
?af.keynode?
from
?APPFOUNDATION?af?
where
?af.appid
=
aid?
and
?af.foundationid
=
fid;
--
?沒有into，編譯報錯，提示：Compilation?
??Error:?PLS
-
00428
:?an?
INTO
?clause?
is
?expected?
in
?this?
SELECT
?statement
?
select
?keynode?
into
?kn?
from
?APPFOUNDATION?
where
?appid
=
aid?
and
?foundationid
=
fid;
--
?正確運行
select
?af.keynode?
into
?kn?
from
?APPFOUNDATION?af?
where
?
af.appid
=
appid
?
and
?
af.foundationid
=
foundationid
;
--
?運行階段報錯，提示
ORA
-
01422
:exact?
fetch
?
returns
?more?than?requested?
number
?
of
?rows
create
?
table
?A(
id?
varchar2
(
50
)?
primary
?
key
?
not
?
null
,
vcount?
number
(
8
)?
not
?
null
,
bid?
varchar2
(
50
)?
not
?
null
?
--
?外鍵?
);
select?sum(vcount)?into?fcount?from?A?
where
?bid
=
'
xxxxxx
'
;
if
?fcount?
is
?
null
?then
????fcount:
=
0
;
end?
if
;
????????
this
.pnumberManager.getHibernateTemplate().execute(
????????????????
new
?HibernateCallback()?
{
????????????????
????????????????????
public
?Object?doInHibernate(Session?session)
????????????????????????????
throws
?HibernateException,?SQLException?
{
????????????????????????????
????????????????????????CallableStatement?cs?
=
?session
????????????????????????????????.connection()
????????????????????????????????.prepareCall(
"
{call?modifyapppnumber_remain(?)}
"
);
????????????????????????cs.setString(
1
,?foundationid);
????????????????????????cs.execute();
????????????????????????
return
?
null
;
????????????????????}
????????????????}
);
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
