1饭入、存儲(chǔ)過程簡單實(shí)例

CREATE OR REPLACE PROCEDURE  存儲(chǔ)過程名稱 （參數(shù)in,參數(shù)out）
AS
-- 變量聲明未檩，每個(gè)聲明用分號結(jié)束。可以在聲明的同時(shí)初始化 
name varchar2(50);
age number(8) default 0;

--開始邏輯運(yùn)算
BEGIN

--業(yè)務(wù)邏輯

END

2.游標(biāo)實(shí)現(xiàn)方式

顯式游標(biāo)實(shí)現(xiàn)方式（可多值）

cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender;   -- 聲明游標(biāo)甜无，select語句可以包括單引號等。
  
begin  
    open cursorVar;    -- 打開游標(biāo)  
    loop  
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
         exit when cursorVar%notfound;                             --當(dāng)沒有記錄時(shí)退出循環(huán)  
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
    end loop;  
     
    close cursorVar;   -- 關(guān)閉游標(biāo)  
   
    游標(biāo)的屬性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;   
    --%FOUND:已檢索到記錄時(shí)，返回true   
    --%NOTFOUNRD：檢索不到記錄時(shí)碗旅，返回true   
    --%ISOPEN:游標(biāo)已打開時(shí)返回true   
    --%ROWCOUNT:代表檢索的記錄數(shù)，從1開始   
end;

隱式游標(biāo)（可以實(shí)現(xiàn)查詢多值）

for currow in (  
   select t.col1, t.col2  
   from tableName t  
   where ...  
) loop  
    if currow.col1 = 0 then  
       return;    -- 中止sp镜悉，返回  
   end if;  
end loop;

帶有參數(shù)的游標(biāo)（可實(shí)現(xiàn)查詢多值）

declare  
isok integer;  
v_event_id number(10);  
v_isagain number(2);  
v_rate number(2);  
  
v_sender char(11) := '13800138000';  
  
cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- 聲明游標(biāo)  
  
begin  
    open cursorVar(v_sender);    -- 打開游標(biāo)祟辟，在括號里傳參。  
    loop  
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
         exit when cursorVar%notfound;                             --當(dāng)沒有記錄時(shí)退出循環(huán)  
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
    end loop;  
     
    close cursorVar;   -- 關(guān)閉游標(biāo)  
end;

3.異常處理

EXCEPTION 
   WHEN OTHERS THEN 
      vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
 
   ROLLBACK; 

   --把當(dāng)前錯(cuò)誤記錄進(jìn)日志表侣肄。 
   INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
   VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); 
   COMMIT; 
   RETURN;

4.select into statement

將select 查詢結(jié)果存入變量旧困；可同時(shí)將多個(gè)查詢列儲(chǔ)存入多個(gè)變量之中，但查詢必須至少包含一條記錄稼锅，否者拋出NO_DATA_FOUND異常吼具。

BEGIN
SELECT col1,col2 into 變量1,變量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;

5.用for in 來使用游標(biāo)cursor

   procedure copy(a_id int) is
    cursor t_cursor is    --定義名為t_cursor的游標(biāo)
      select * from table f where f.id = a_id;
   v_row table%rowtype;

begin
　　for v_row in t_cursor loop    
--循環(huán)之前定義的t_cursor游標(biāo)將其中多行查詢結(jié)果一行行遍歷。
　　　　-- 處理數(shù)據(jù)
　　end loop;
end copy

關(guān)于oracle存儲(chǔ)過程的若干問題備忘矩距。

1.存儲(chǔ)過程中as分隔存儲(chǔ)過程名（+參數(shù)名）和變量名拗盒。

因而數(shù)據(jù)庫表別名定義時(shí)不可加as

2.在select字段時(shí)后面必須緊跟into +變量名，否者報(bào)錯(cuò)Compilation锥债；除非使用游標(biāo)select整個(gè)字段列陡蝇。

3.select ~into在使用時(shí)須確保select字段存在，否者報(bào)錯(cuò)no datafound哮肚。因此可提前使用select count(*) from xxx查看存在與否登夫。

4.存儲(chǔ)過程中的別名不可與字段名重復(fù)，否則雖可編譯但運(yùn)行報(bào)錯(cuò)允趟。

5.在存儲(chǔ)過程中定義的變量ss接收了一個(gè)結(jié)果為null的查詢恼策。那么即便之前ss定義了一個(gè)默認(rèn)值，ss的值也依舊會(huì)被覆蓋為null拼窥。

createtable table1(
name varchar2(10) primary key not null,
age number(10) not null,
sex varchar2(2) not null,--外鍵
);
--存儲(chǔ)過程中有如下語句：
ss number(10) := 0;--定義ss默認(rèn)值為數(shù)字0.
...
select sum(age) into ss from table1 where name = 'tiny';
--此時(shí)表table1中不存在name為tiny的值戏蔑；則此時(shí)的ss 依舊為null蹋凝；

解決方式為之后判斷ss的值若為null再次賦值即可。

if ss is null then
ss := 0;
end if;

6.Hibernate調(diào)用oracle存儲(chǔ)過程：

 this.pnumberManager.getHibernateTemplate().execute(
 new HibernateCallback() {
 public ObjectdoInHibernate(Session session)
 throws HibernateException,SQLException {
 CallableStatementcs = session
 .connection()
 .prepareCall("{callmodifyapppnumber_remain(?)}");
 cs.setString(1,foundationid);
 cs.execute();
 return null;
 }
 });

7.1oracle 存儲(chǔ)過程中使用Sequence：

create or replace procedure GetRecords(name_out outvarchar2,age_in in varchar2) as
begin
select NAME into name_out from test where AGE =age_in;
end;
create or replace procedure insertRecord(UserID invarchar2, UserName in varchar2,UserAge in varchar2) is
begin
insert into test values (UserID, UserName,UserAge);
end;

7.2創(chuàng)建一個(gè)存儲(chǔ)過程用來記錄序列（insert Record With Sequence）

create or replace procedureInsertRecordWithSequence(UserID out number,UserName invarchar2,UserAge in number)
is
begin insert into test(id, name, age)--插入一條記錄总棵，PK值從Sequece獲取
values(test_seq.nextval, UserName, UserAge);
select test_seq.currval into UserID fromdual;
end InsertRecordWithSequence;
--此處test表的定義鳍寂，和前面的示例不同。其中情龄，UserID是PK迄汛。

7.3存儲(chǔ)過程中包的定義：

為了讓存儲(chǔ)過程返回結(jié)果集，必須定義一個(gè)游標(biāo)變量作為輸出參數(shù)骤视。這和SqlServer中有著很大的不同鞍爱！并且還要用到Oracle中“包”（Package）的概念，似乎有點(diǎn)繁瑣专酗，但熟悉后也會(huì)覺得很方便睹逃。

關(guān)于“包”的概念，有很多內(nèi)容可以參考祷肯，在此就不贅述了沉填。首先，我創(chuàng)建了一個(gè)名為TestPackage的包佑笋，包頭是這么定義的：

create or replace package TestPackage is
type mycursor is ref cursor; -- 定義游標(biāo)變量
procedure GetRecords(ret_cursor out mycursor); --定義過程翼闹，用游標(biāo)變量作為返回參數(shù)
end TestPackage;
包體是這么定義的：
create or replace package body TestPackage is
procedure GetRecords(ret_cursor out mycursor)as
begin
open ret_cursor for select * from test;
end GetRecords;
end TestPackage;

小結(jié)：包是Oracle特有的概念，SqlServer中找不到相匹配的東西蒋纬。在我看來猎荠，包有點(diǎn)像VC++的類，包頭就是.h文件蜀备，包體就是.cpp文件关摇。包頭只負(fù)責(zé)定義，包體則負(fù)責(zé)具體實(shí)現(xiàn)琼掠。如果包返回多個(gè)游標(biāo)拒垃，則DataReader會(huì)按照您向參數(shù)集合中添加它們的順序來訪問這些游標(biāo)，而不是按照它們在過程中出現(xiàn)的順序來訪問瓷蛙〉课停可使用DataReader的NextResult()方法前進(jìn)到下一個(gè)游標(biāo)。

create or replace package TestPackage is
type mycursor is ref cursor;
procedure UpdateRecords(id_in in number,newName invarchar2,newAge in number);
procedure SelectRecords(ret_cursor outmycursor);
procedure DeleteRecords(id_in in number);
procedure InsertRecords(name_in in varchar2, age_in innumber);
end TestPackage;

包體如下：

create or replace package body TestPackage is
procedure UpdateRecords(id_in in number, newName invarchar2, newAge in number) as
begin
update test set age = newAge, name = newName where id =id_in;
end UpdateRecords;
procedure SelectRecords(ret_cursor out mycursor)as
begin
open ret_cursor for select * from test;
end SelectRecords;
procedure DeleteRecords(id_in in number) as
begin
delete from test where id = id_in;
end DeleteRecords;
procedure InsertRecords(name_in in varchar2, age_in innumber) as
begin
insert into test values (test_seq.nextval, name_in,age_in);
--test_seq是一個(gè)已建的Sequence對象艰猬，請參照前面的示例
end InsertRecords;
end TestPackage;
TestPackage.SelectRecords

8.1項(xiàng)目級存儲(chǔ)過程實(shí)例：

CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2AS
------------------------------------------------------------------------
-- Oracle 包
---國航支付平臺VISA退款
-- 游標(biāo)定義：
--
-- 存儲(chǔ)過程定義：
-- PY_WEBREFUND_VISA_PREPARE ：VISA退款準(zhǔn)備
-- 最后修改人：dougq
-- 最后修改日期：2007.4.17
------------------------------------------------------------------------
PROCEDURE PY_WEBREFUND_VISA_PREPARE(
in_serialNoStr IN VARCHAR2,--用"|"隔開的一組網(wǎng)上退款申請流水號
in_session_operatorid IN VARCHAR2,--業(yè)務(wù)操作員
out_return_code OUT VARCHAR2,--存儲(chǔ)過程返回碼
out_visaInfoStr OUT VARCHAR2
);
END PY_PCKG_REFUND2;
/
CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2AS
PROCEDURE PY_WEBREFUND_VISA_PREPARE(
in_serialNoStr IN VARCHAR2,--用"|"隔開的一組網(wǎng)上退款申請流水號
in_session_operatorid INVARCHAR2,--業(yè)務(wù)操作員
out_return_code OUT VARCHAR2,--存儲(chǔ)過程返回碼
out_visaInfoStr OUT VARCHAR2
) IS
--變量聲明
v_serialno VARCHAR2(20);--網(wǎng)上退款申請流水號
v_refserialno VARCHAR2(20);--支付交易流水號
v_tobankOrderNoVARCHAR2(30);--上送銀行的訂單號
v_orderDate VARCHAR2(8);--訂單日期
v_businessType VARCHAR2(10);--業(yè)務(wù)類型
v_currTypeVARCHAR2(3);--訂單類型（ET－電子機(jī)票）
v_merno VARCHAR2(15);--商戶號
v_orderNo VARCHAR2(20);--商戶訂單號
v_orderState VARCHAR2(2);
v_refAmount NUMBER(15,2);--退款金額
v_tranType VARCHAR(2);--交易類型
v_bank VARCHAR2(10);--收單銀行
v_date VARCHAR2 (8);--交易日期
v_time VARCHAR2 (6);--交易時(shí)間
v_datetime VARCHAR2 (14);--獲取的系統(tǒng)時(shí)間
v_index_start NUMBER;
v_index_end NUMBER;
v_i NUMBER;
BEGIN
-- 初始化參數(shù)
out_visaInfoStr := '';
v_i := 1;
v_index_start := 1;
v_index_end :=INSTR(in_serialNoStr,'|',1,1);
v_refserialno := SUBSTR(in_serialNoStr,v_index_start, v_index_end-1);
v_datetime := TO_CHAR (SYSDATE,'yyyymmddhh24miss');
v_date := SUBSTR (v_datetime, 1, 8);
v_time := SUBSTR (v_datetime, 9,14);
--從退款請求表中查詢定單信息（商戶號横堡、商戶訂單號、退款金額）
WHILE v_index_end > 0LOOP
SELECT
WEBR_MERNO,
WEBR_ORDERNO,
WEBR_AMOUNT,
WEBR_SERIALNO,
WEBR_REFUNDTYPE
INTO
v_merno,
v_orderNo,
v_refAmount,
v_serialno,
v_tranType
FROM
PY_WEB_REFUND
WHERE
WEBR_REFREQNO = v_refserialno;
--將查詢到的數(shù)據(jù)組成串
out_visaInfoStr := out_visaInfoStr || v_merno ||'~' || v_orderNo || '~' || v_refAmount + '|';
--為下次循環(huán)做數(shù)據(jù)準(zhǔn)備
v_i := v_i + 1;
v_index_start := v_index_end + 1;
v_index_end :=INSTR(in_serialNoStr,'|',1,v_i);
IF v_index_end > 0THEN
v_refserialno := SUBSTR(in_serialNoStr,v_index_start, v_index_end - 1);
END IF;
--根據(jù)原支付流水號在流水表中查詢該訂單的信息冠桃，包括原上送銀行或第三方的訂單號：WTRN_TOBANKORDERNO
SELECT
WTRN_TOBANKORDERNO,
WTRN_ORDERNO,
WTRN_ORDERDATE,
WTRN_BUSINESSTYPE,
WTRN_ACCPBANK,
WTRN_TRANCURRTYPE
INTO
v_tobankOrderNo,
v_orderNo,
v_orderDate,
v_businessType,
v_bank,
v_currType
FROM PY_WEBPAY_VIEW
WHERE WTRN_SERIALNO = v_serialno;
--記錄流水表(退款)
INSERT INTO PY_WEBPAY_TRAN(
WTRN_SERIALNO,
WTRN_TRANTYPE,
WTRN_ORIGSERIALNO,
WTRN_ORDERNO,
WTRN_ORDERDATE,
WTRN_BUSINESSTYPE,
WTRN_TRANCURRTYPE,
WTRN_TRANAMOUNT,
WTRN_ACCPBANK,
WTRN_TRANSTATE,
WTRN_TRANTIME,
WTRN_TRANDATE,
WTRN_MERNO,
WTRN_TOBANKORDERNO
)VALUES(
v_refserialno, --和申請表的流水號相同命贴，作為參數(shù)傳人
v_tranType,
v_serialno, --原交易流水號，查詢退款申請表得到
v_orderNo,
v_orderDate,
v_businessType,
v_currType,
v_refAmount,
v_bank,
'1',
v_time,
v_date,
v_merno,
v_tobankOrderNo --上送銀行的訂單號，查詢流水表得到
);
--更新網(wǎng)上退款申請表
UPDATE PY_WEB_REFUND
SET
WEBR_IFDISPOSED = '1',
WEBR_DISPOSEDOPR =in_session_operatorid,
WEBR_DISPOSEDDATE = v_datetime
WHERE
WEBR_REFREQNO = v_refserialno;
--更新定單表
IF v_tranType = '2' THEN
v_orderState := '7';
ELSE
v_orderState := '10';
END IF;
UPDATE PY_ORDER
SET
ORD_ORDERSTATE = v_orderState
WHERE
ORD_ORDERNO = v_orderNo
AND ORD_ORDERDATE = v_orderDate
AND ORD_BUSINESSTYPE =v_businessType;
END LOOP;
-- 異常處理
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
out_return_code := '14001';
RETURN;
END;
END PY_PCKG_REFUND2;
/  

8.2帶游標(biāo)的存儲(chǔ)過程詳例：

--1胸蛛、簡單的存儲(chǔ)過程
create or replaceprocedure procedure_test
(p_id in varchar,p_statusout varchar) --p_id為輸入?yún)?shù),p_status為輸出參數(shù)
as
t_name varchar2(20);
t_count number:=0;
begin
select votetitle,vatesum intot_name,t_count from votemaster whereid=p_id; --注意：此處沒有：來賦值
if t_count <=0 then
p_status:= t_name||':差';
elsif t_count >0 and t_count<3 then
p_status:= t_name||':良好';
else
p_status:= t_name||':優(yōu)秀';
end if;
end;
--執(zhí)行
declare
out_param varchar2(50);
begin
procedure_test('1',out_param);
dbms_output.put_line(out_param);
end;

--2污茵、帶游標(biāo)的存儲(chǔ)過程
create or replaceprocedure procedure_cursor_test
(p_id in varchar2,p_status outvarchar2)
as
vote votemaster%rowtype;--聲明一個(gè)對象(votemaster)類型的對象
cursor my_cur is select* from votemaster; --聲明一個(gè)游標(biāo)并填充數(shù)據(jù)
begin
open my_cur; --打開游標(biāo)
loop
fetch my_cur into vote ;--循環(huán)游標(biāo)，并放入對象
exit when my_cur%notfound;--如果沒有數(shù)據(jù)葬项，則直接exit
if vote.id=p_id then
p_status :=vote.votetitle||'：'||vote.vatesum;
--如果想終止循環(huán)泞当，可以直接exit;
end if;
end loop;
close my_cur; --關(guān)閉游標(biāo)
end;
--執(zhí)行
declare
out_param varchar2(50);
begin
procedure_cursor_test('1',out_param);
dbms_output.put_line(out_param);
end;  

9.1存儲(chǔ)過程function的使用詳例：

--包定義
create orreplace package t_package
is
--定義過程
procedure append_proc(t varchar2,aout varchar2);
--過程的重載
procedure append_proc(t number,aout varchar2);
--定義函數(shù)
function append_fun(t varchar2)return varchar2;
end;

--包主題
create orreplace package body t_package
is
v_t varchar2(30);
--私有成員函數(shù)
function private_fun(t varchar2)return varchar2 is
begin
v_t := t||'hello';
return v_t;
end;
--實(shí)現(xiàn)過程
procedure append_proc(t varchar2,aout varchar2) is
begin
a := t||'hello';
end;
--過程的重載
procedure append_proc(t number,aout varchar2) is
begin
a := t||'hello';
end;
--實(shí)現(xiàn)函數(shù)
function append_fun(tvarchar2)
return varchar2 is
begin
v_t := t||'hello';
return v_t;
end;
end;