1、存储过程简单实例

CREATE OR REPLACE PROCEDURE  存储过程名称 （参数in,参数out）
AS
-- 变量声明，每个声明用分号结束。可以在声明的同时初始化 
name varchar2(50);
age number(8) default 0;

--开始逻辑运算
BEGIN

--业务逻辑

END

2.游标实现方式

显式游标实现方式（可多值）

cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender;   -- 声明游标，select语句可以包括单引号等。
  
begin  
    open cursorVar;    -- 打开游标  
    loop  
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
         exit when cursorVar%notfound;                             --当没有记录时退出循环  
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
    end loop;  
     
    close cursorVar;   -- 关闭游标  
   
    游标的属性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;   
    --%FOUND:已检索到记录时，返回true   
    --%NOTFOUNRD：检索不到记录时，返回true   
    --%ISOPEN:游标已打开时返回true   
    --%ROWCOUNT:代表检索的记录数，从1开始   
end;

隐式游标（可以实现查询多值）

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

带有参数的游标（可实现查询多值）

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; -- 声明游标  
  
begin  
    open cursorVar(v_sender);    -- 打开游标，在括号里传参。  
    loop  
         fetch cursorVar into v_event_id, v_isagain, v_rate;       -- 取值  
         exit when cursorVar%notfound;                             --当没有记录时退出循环  
         dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);  
    end loop;  
     
    close cursorVar;   -- 关闭游标  
end;

3.异常处理

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

   --把当前错误记录进日志表。 
   INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
   VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE); 
   COMMIT; 
   RETURN;

4.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;

5.用for in 来使用游标cursor

   procedure copy(a_id int) is
    cursor t_cursor is    --定义名为t_cursor的游标
      select * from table f where f.id = a_id;
   v_row table%rowtype;

begin
　　for v_row in t_cursor loop    
--循环之前定义的t_cursor游标将其中多行查询结果一行行遍历。
　　　　-- 处理数据
　　end loop;
end copy

关于oracle存储过程的若干问题备忘。

1.存储过程中as分隔存储过程名（+参数名）和变量名。

因而数据库表别名定义时不可加as

2.在select字段时后面必须紧跟into +变量名，否者报错Compilation；除非使用游标select整个字段列。

3.select ~into在使用时须确保select字段存在，否者报错no datafound。因此可提前使用select count(*) from xxx查看存在与否。

4.存储过程中的别名不可与字段名重复，否则虽可编译但运行报错。

5.在存储过程中定义的变量ss接收了一个结果为null的查询。那么即便之前ss定义了一个默认值，ss的值也依旧会被覆盖为null。

createtable table1(
name varchar2(10) primary key not null,
age number(10) not null,
sex varchar2(2) not null,--外键
);
--存储过程中有如下语句：
ss number(10) := 0;--定义ss默认值为数字0.
...
select sum(age) into ss from table1 where name = 'tiny';
--此时表table1中不存在name为tiny的值；则此时的ss 依旧为null；

解决方式为之后判断ss的值若为null再次赋值即可。

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

6.Hibernate调用oracle存储过程：

 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 存储过程中使用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创建一个存储过程用来记录序列（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存储过程中包的定义：

为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和SqlServer中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。

关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：

create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); --定义过程，用游标变量作为返回参数
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;

小结：包是Oracle特有的概念，SqlServer中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。

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是一个已建的Sequence对象，请参照前面的示例
end InsertRecords;
end TestPackage;
TestPackage.SelectRecords

8.1项目级存储过程实例：

CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2AS
------------------------------------------------------------------------
-- Oracle 包
---国航支付平台VISA退款
-- 游标定义：
--
-- 存储过程定义：
-- PY_WEBREFUND_VISA_PREPARE ：VISA退款准备
-- 最后修改人：dougq
-- 最后修改日期：2007.4.17
------------------------------------------------------------------------
PROCEDURE PY_WEBREFUND_VISA_PREPARE(
in_serialNoStr IN VARCHAR2,--用"|"隔开的一组网上退款申请流水号
in_session_operatorid IN VARCHAR2,--业务操作员
out_return_code OUT VARCHAR2,--存储过程返回码
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,--用"|"隔开的一组网上退款申请流水号
in_session_operatorid INVARCHAR2,--业务操作员
out_return_code OUT VARCHAR2,--存储过程返回码
out_visaInfoStr OUT VARCHAR2
) IS
--变量声明
v_serialno VARCHAR2(20);--网上退款申请流水号
v_refserialno VARCHAR2(20);--支付交易流水号
v_tobankOrderNoVARCHAR2(30);--上送银行的订单号
v_orderDate VARCHAR2(8);--订单日期
v_businessType VARCHAR2(10);--业务类型
v_currTypeVARCHAR2(3);--订单类型（ET－电子机票）
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);--交易时间
v_datetime VARCHAR2 (14);--获取的系统时间
v_index_start NUMBER;
v_index_end NUMBER;
v_i NUMBER;
BEGIN
-- 初始化参数
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;
--将查询到的数据组成串
out_visaInfoStr := out_visaInfoStr || v_merno ||'~' || v_orderNo || '~' || v_refAmount + '|';
--为下次循环做数据准备
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;
--根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：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, --和申请表的流水号相同，作为参数传人
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 --上送银行的订单号，查询流水表得到
);
--更新网上退款申请表
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带游标的存储过程详例：

--1、简单的存储过程
create or replaceprocedure procedure_test
(p_id in varchar,p_statusout varchar) --p_id为输入参数,p_status为输出参数
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||':优秀';
end if;
end;
--执行
declare
out_param varchar2(50);
begin
procedure_test('1',out_param);
dbms_output.put_line(out_param);
end;

--2、带游标的存储过程
create or replaceprocedure procedure_cursor_test
(p_id in varchar2,p_status outvarchar2)
as
vote votemaster%rowtype;--声明一个对象(votemaster)类型的对象
cursor my_cur is select* from votemaster; --声明一个游标并填充数据
begin
open my_cur; --打开游标
loop
fetch my_cur into vote ;--循环游标，并放入对象
exit when my_cur%notfound;--如果没有数据，则直接exit
if vote.id=p_id then
p_status :=vote.votetitle||'：'||vote.vatesum;
--如果想终止循环，可以直接exit;
end if;
end loop;
close my_cur; --关闭游标
end;
--执行
declare
out_param varchar2(50);
begin
procedure_cursor_test('1',out_param);
dbms_output.put_line(out_param);
end;  

9.1存储过程function的使用详例：

--包定义
create orreplace package t_package
is
--定义过程
procedure append_proc(t varchar2,aout varchar2);
--过程的重载
procedure append_proc(t number,aout varchar2);
--定义函数
function append_fun(t varchar2)return varchar2;
end;

--包主题
create orreplace package body t_package
is
v_t varchar2(30);
--私有成员函数
function private_fun(t varchar2)return varchar2 is
begin
v_t := t||'hello';
return v_t;
end;
--实现过程
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;
--实现函数
function append_fun(tvarchar2)
return varchar2 is
begin
v_t := t||'hello';
return v_t;
end;
end;