oracle存储过程 39 转意,Oracle存储过程实例 [转]

最新推荐文章于 2021-06-07 11:19:22 发布

转载最新推荐文章于 2021-06-07 11:19:22 发布 · 192 阅读

文章标签：

本文详细介绍了如何在Oracle中创建和使用存储过程，包括如何定义Sequence、插入记录、使用游标以及创建包。文中通过示例展示了如何创建带有输出参数的存储过程，如何返回游标结果集，并解释了Oracle特有的包概念及其用法。此外，还展示了如何在PL/SQL中进行条件判断、循环控制以及异常处理。

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)

begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取

values(test_seq.nextval, UserName, UserAge);

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 判断

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

...

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;

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;