oracle存储过程-----(转)
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) isbegininsert into test values (UserID, UserName, UserAge);end;-
首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：
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。
create or replace procedure InsertRecordWithSequence(UserID out number,UserName in varchar2,UserAgein 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的包，包头是这么定义的：
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()方法前进到下一个游标。
-
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;
包体如下：
create or replace package body TestPackage is procedure UpdateRecords(id_in in number, newName in varchar2, newAgein 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不大一样，花费了一下午的时间写了一个出来，测试编译通过了，是为记，以备以后查阅。
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 INVARCHAR2, --用"|"隔开的一组网上退款申请流水号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 ASPROCEDURE PY_WEBREFUND_VISA_PREPARE (in_serialNoStr INVARCHAR2, --用"|"隔开的一组网上退款申请流水号in_session_operatorid IN VARCHAR2,--业务操作员out_return_code OUT VARCHAR2, --存储过程返回码out_visaInfoStr OUT VARCHAR2 ) IS--变量声明v_serialnoVARCHAR2(20);--网上退款申请流水号v_refserialno VARCHAR2(20);--支付交易流水号v_tobankOrderNo VARCHAR2(30);--上送银行的订单号v_orderDateVARCHAR2(8);--订单日期v_businessType VARCHAR2(10);--业务类型v_currTypeVARCHAR2(3);--订单类型（ET－电子机票）v_merno VARCHAR2(15);--商户号v_orderNoVARCHAR2(20);--商户订单号v_orderState VARCHAR2(2);v_refAmount NUMBER(15,2);--退款金额 v_tranTypeVARCHAR(2);--交易类型v_bank VARCHAR2(10);--收单银行v_date VARCHAR2 (8);--交易日期 v_time VARCHAR2 (6);--交易时间 v_datetimeVARCHAR2 (14);--获取的系统时间v_index_start NUMBER;v_index_endNUMBER;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;/
页:
[1]