createorreplaceproceduregetrecords(name_outoutvarchar2,age_ininvarchar2)as begin selectnameintoname_outfromtestwhereage=age_in; end; createorreplaceprocedureinsertrecord(useridinvarchar2,usernameinvarchar2,userageinvarchar2)is begin insert
create or replace procedure getrecords(name_out out varchar2,age_in in varchar2) as begin select name into name_out from test where age = age_in; end; create or replace procedure insertrecord(userid in varchar2, username in varchar2,userage in varchar2) is begin insert into test values (userid, username, userage); end;
首先，在oracle中创建了一个名为test_seq的sequence对象，sql语句如下： java代码
create sequence test_seq minvalue 100 maxvalue 999 start with 102 increment by 1 nocache;
语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在pl/sql中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。
定义完了sequence，接下来就是创建一个存储过程insertrecordwithsequence：
--这次我修改了test表的定义，和前面的示例不同。其中，userid是pk。
java代码
create or replace procedure insertrecordwithsequence(userid out number,username in varchar2,userage in number) is begin insert into test(id, name, age) --插入一条记录，pk值从sequece获取 values(test_seq.nextval, username, userage); /*返回pk值。注意dual表的用法*/ select test_seq.currval into userid from dual; end insertrecordwithsequence;
为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和sql server中有着很大的不同！并且还要用到oracle中“包”（package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。
关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为testpackage的包，包头是这么定义的：
java代码
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特有的概念，sql server中找不到相匹配的东西。在我看来，包有点像vc++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则datareader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用datareader的nextresult()方法前进到下一个游标。
java代码
create or replace package testpackage is type mycursor is ref cursor; procedure updaterecords(id_in in number,newname in varchar2,newage in number); procedure selectrecords(ret_cursor out mycursor); procedure deleterecords(id_in in number); procedure insertrecords(name_in in varchar2, age_in in number); end testpackage;
包体如下：
java代码
create or replace package body testpackage is procedure updaterecords(id_in in number, newname in varchar2, 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 in number) as begin insert into test values (test_seq.nextval, name_in, age_in); --test_seq是一个已建的sequence对象，请参照前面的示例 end insertrecords; end testpackage;
testpackage.selectrecords
-------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle 存储过程的基本语法 1.基本结构
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存储过程一例 by 凌云志 发表于 2007-4-18 17:01:00
最近换了一个项目组，晕，要写oracle的存储过程，幸亏写过一些db2的存储过程，尚且有些经验，不过oralce的pl/sql不大一样，花费了一下午的时间写了一个出来，测试编译通过了，是为记，以备以后查阅。
java代码
create or replace package py_pckg_refund2 as ------------------------------------------------------------------------ -- 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_refund2 as procedure py_webrefund_visa_prepare ( in_serialnostr in varchar2, --用|隔开的一组网上退款申请流水号 in_session_operatorid in varchar2,--业务操作员 out_return_code out varchar2, --存储过程返回码 out_visainfostr out varchar2 ) is --变量声明 v_serialno varchar2(20);--网上退款申请流水号 v_refserialno varchar2(20);--支付交易流水号 v_tobankorderno varchar2(30);--上送银行的订单号 v_orderdate varchar2(8);--订单日期 v_businesstype varchar2(10);--业务类型 v_currtype varchar2(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 > 0 loop 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 > 0 then 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; /