oracle存储过程代码实例?编写Oracle存储过程的任务，我是程序员，脑袋里只有一些如何使用CALLABLE接口调用存
oracle存储过程代码实例
?编写Oracle存储过程的任务，我是程序员，脑袋里只有一些如何使用CALLABLE接口调用存储过程的经验，一时不知如何下手，我查阅了一些资料，通过实践发现编写ORACLE存储过程是非常不容易的工作，即使上路以后，调试和验证非常麻烦。简单地讲，Oracle存储过程就是存储在Oracle数据库中的一个程序。
一. 概述
Oracle存储过程开发的要点是：
? 使用Notepad文本编辑器，用Oracle PL/SQL编程语言写一个存储过程;
? 在Oracle数据库中创建一个存储过程;
? 在Oracle数据库中使用SQL*Plus工具运行Oracle存储过程;
? 在Oracle数据库中修改存储过程;
? 通过编译错误调试存储过程;
? 删除存储过程;
二.环境配置
包括以下内容：
? 一个文本编辑器Notepad;
? Oracle SQL*Plus工具，提交Oracle SQL和PL/SQL 语句到Oracle database。
? Oracle 10g express数据库，它是免费使用的版本;
需要的技巧：
? SQL基础知识,包括插入、修改、删除等
? 使用Oracle's SQL*Plus工具的基本技巧;
? 使用Oracle's PL/SQL 编程语言的基本技巧;
三.写一个存储过程
存储过程使用Oracle's PL/SQL 程序语言编写，让我们写一个什么工作都不做的Oracle存储过程，我们可以编译和运行它而不用担心会对数据库产生任何损害。
在Notepad, 写下：
CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
NULL;
END;
把文件存为skeleton.sql.
让我们一行行遍历这个Oracle存储过程:
1 CREATE OR REPLACE PROCEDURE skeleton
2 IS
3 BEGIN
4 NULL;
5 END;
行1:
CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;
行2:
IS关键词表明后面将跟随一个PL/SQL体。
行3:
BEGIN关键词表明PL/SQL体的开始。
行4:
NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;
行5:
END关键词表明PL/SQL体的结束
四.创建一个存储过程
SQL语句CREATE OR REPLACE PROCEDURE在Oracle数据库中创建、编译和保存一个Oracle存储过程。
从Window打开SQL*Plus并且从SQL*Plus登录到你的数据库;打开skeleton.sql文件.
在SQL 命令提示符下输入以下命令：
SQL @skeleton
SQL /
SQL*Plus装载skeleton.sql文件的内容到SQL*Plus缓冲区并且执行SQL*Plus语句;SQL*Plus 会通知你存储过程已经被成功地创建。
现在你的存储过程被创建，编译和保存在你的Oracle数据库，我们可以运行它。
五.运行一个存储过程
从SQL*Plus 命令行提示符运行你的存储过程使用EXECUTE命令，如下:
SQL EXECUTE skeleton;
SQL*Plus 输出一下信息确信Oracle存储过程成功执行: PL/SQL procedure successfully completed.
你也可以在一个无名PL/SQL块内运行你的存储过程，在SQL*Plus命令提示符下，它看起来像：
SQL BEGIN
2 SKELETON;
3 END;
4 /
现在我们已经运行了我们的Oracle存储过程，我们如何修改它呢?
六.修改一个存储过程
让我们写一个输出字符串“Hello World!”的Oracle存储过程，用Notepad打开你的skeleton.sql 文件，. 用DBMS_OUTPUT.PUT_LINE 过程调用去替换NULL语句，如下所示:
CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
保存到文件skeleton.sql.
从SQL*Plus命令行, 打开文件skeleton.sql .
SQL @skeleton
SQL
1 CREATE OR REPLACE PROCEDURE skeleton
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Hello World!');
5* END;
SQL /
SQL*Plus 通知你存储过程成功创建并输出提示信息：Procedure created.
SQL
用EXECUTE 命令运行你的存储过程:
SQL EXECUTE skeleton;
SQL*Plus显示Oracle存储过程运行成功：PL/SQL procedure successfully completed.
我们想要的输出字符串 "Hello World!"没有出来，在显示一个DBMS_OUTPUT.PUT_LINE 结果前需要运行一个SET命令，在SQL*Plus 命令行提示符,键入：
SQL SET SERVEROUTPUT ON
再次执行你的存储过程：
SQL EXECUTE skeleton;
现在结果输出了：Hello World!
PL/SQL procedure successfully completed.
七.调试一个存储过程
当调试一个Oracle存储过程时，遵循一样的步骤，修改SQL文件，创建存储过程，执行存储过程，根据编译器反馈的出错信息进行修改，这一步是非常繁琐的，需要依靠经验。
在实际的商用存储过程的开发调试过程中，由于涉及很多表、类型、光标、循环、条件等复杂的逻辑，和PL/SQL语句的灵活运用，编译时会产生很多错误提示信息，程序员在根据这些错误信息定位，进行修正，再编译最后得到正确的结构;
八.放弃一个存储过程
如果在数据库中你不在需要一个存储过程你可以删除它，SQL语句 DROP PROCEDURE 完成从数据库中删除一个存储过程，DROP PROCEDURE 在SQL中被归类为数据定义语言(DDL) 类操作，其他的例子有CREATE, ALTER, RENAME 和TRUNCATE。.
在SQL*Plus 命令提示符下，使用DROP PROCEDURE SQL 语句删除你的叫做skeleton的Oracle存储过程：
SQL DROP PROCEDURE skeleton;
SQL*Plus assures us the procedure has been removed:
Procedure dropped.
总结
本文详细讨论了如何使用Oracle工具开发商用Oracle存储过程的步骤。最后在Oracle存储的使用中可能是程序直接调用，也可能被触发器调用。
?
1、用来插入大量测试数据的存储过程
CREATE OR REPLACE PROCEDURE INSERTAMOUNTTEST
(
ST_NUM??????? IN???? NUMBER,
ED_NUM??????? IN???? NUMBER
)
IS
BEGIN
declare
?????? i?? number;
begin
FOR i IN ST_NUM..ED_NUM LOOP
INSERT INTO tb values(i,i,'3','3','3',100,'0');
END LOOP;
end;
END;
运行：
sql>execute INSERTAMOUNTTEST(1,45000)?? -- 一次插入45000条测试数据
2、从存储过程中返回值
create or replace procedure spaddflowdate
(
varAppTypeId?????????????? in varchar2,
varFlowId????????????????? in varchar2,
DateLength???????????????? in number,
ReturnValue??????????????? out number??? --返回值
)
is
begin
insert into td values(varAppTypeId,varFlowId,DateLength)
returning 1 into ReturnValue;?? --返回值
commit;
exception
when others then
rollback;
end;
存储过程的执行
sql>variable testvalue? number;
sql>execute spaddflowdate('v','v',2,:testvalue);
sql>print
就可以看到执行结果
?
3、用包实现存储过程返回游标：
create? or? replace? package? test_p?
as?
?
type? outList? is? ref? cursor;?
?
PROCEDURE? getinfor(taxpayerList? out? outList);?
?
end? test_p;?
/?
create? or? replace? package? body? test_p? as? PROCEDURE? getinfor(taxpayerList out? outList)? is? begin?
????? OPEN? taxpayerList??? FOR? select? *? from
????????????????????????td where tag='0';?
?
end? getinfor;?
?
end? test_p;?
/?
?
?
?
运行：
?
set? serverout? on;??? --将输出工具打开
?
variable? x? refcursor;?
?
execute test_p.getinfor(:x);
exec? test_p.getinfor(:x);
?
print? x;?
drop package test_p;
?
?
?
?
创建存储过程。
Java代码
?
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'; ??
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
