• 欢迎访问搞代码网站，推荐使用最新版火狐浏览器和Chrome浏览器访问本网站！
  • 如果您觉得本站非常有看点，那么赶紧使用Ctrl+D 收藏搞代码吧
> 数据库 > mysql > Oracle存储过程实例

Oracle存储过程实例

mysql 搞代码 4年前 (2022-01-09) 128次浏览 已收录 0个评论

createorreplaceprocedureGetRecords(name_outoutvarchar2,age_ininvarchar2)as begin selectNAMEintoname_outfromtestwhereAGE=age_in; end; createorreplaceprocedureinsertRecord(UserIDinvarchar2,UserNameinvarchar2,UserAgeinvarchar2)is begin insert

  1. create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as
  2. begin
  3. select NAME into name_out from test where AGE = age_in;
  4. end;
  6. create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is
  7. begin
  8. insert into test values (UserID, UserName, UserAge);
  9. end;

首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：

Java代码

  1. create sequence TEST_SEQ
  2. minvalue 100
  3. maxvalue 999
  4. start with 102
  5. increment by 1
  6. nocache;

语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。

定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：

–这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。

Java代码

  1. create or replace procedure InsertRecordWithSequence(UserID out number,UserName in varchar2,UserAge in number)
  2. is
  3. begin insert into test(id, name, age) –插入一条记录，PK值从Sequece获取
  4. values(test_seq.nextval, UserName, UserAge);
  5. /*返回PK值。注意Dual表的用法*/
  6. select test_seq.currval into UserID from dual;
  7. end InsertRecordWithSequence;

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

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

Java代码

  1. create or replace package TestPackage is
  2. type mycursor is ref cursor; — 定义游标变量
  3. procedure GetRecords(ret_cursor out mycursor); — 定义过程，用游标变量作为返回参数
  4. end TestPackage; 本文来源gao@dai!ma.com搞$代^码!网7
  5. 包体是这么定义的：
  6. create or replace package body TestPackage is
  7. /*过程体*/
  8. procedure GetRecords(ret_cursor out mycursor) as
  9. begin
  10. open ret_cursor for select * from test;
  11. end GetRecords;
  12. end TestPackage;

小结：

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

Java代码

  1. create or replace package TestPackage is
  2. type mycursor is ref cursor;
  3. procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);
  4. procedure SelectRecords(ret_cursor out mycursor);
  5. procedure DeleteRecords(id_in in number);
  6. procedure InsertRecords(name_in in varchar2, age_in in number);
  7. end TestPackage;

包体如下：

Java代码

  1. create or replace package body TestPackage is
  2. procedure UpdateRecords(id_in in number, newName in varchar2, newAge in number) as
  3. begin
  4. update test set age = newAge, name = newName where id = id_in;
  5. end UpdateRecords;
  7. procedure SelectRecords(ret_cursor out mycursor) as
  8. begin
  9. open ret_cursor for select * from test;
  10. end SelectRecords;
  12. procedure DeleteRecords(id_in in number) as
  13. begin
  14. delete from test where id = id_in;
  15. end DeleteRecords;
  17. procedure InsertRecords(name_in in varchar2, age_in in number) as
  18. begin
  19. insert into test values (test_seq.nextval, name_in, age_in);
  20. –test_seq是一个已建的Sequence对象，请参照前面的示例
  21. end InsertRecords;
  22. 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代码

  1. CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2 AS
  2. ————————————————————————
  3. — Oracle 包
  4. —国航支付平台VISA退款
  5. — 游标定义：
  6. —
  7. — 存储过程定义：
  8. — PY_WEBREFUND_VISA_PREPARE ： VISA退款准备
  9. — 最后修改人：dougq
  10. — 最后修改日期：2007.4.17
  11. ————————————————————————
  13. PROCEDURE PY_WEBREFUND_VISA_PREPARE (
  14. in_serialNoStr IN VARCHAR2, –用”|”隔开的一组网上退款申请流水号
  15. in_session_operatorid IN VARCHAR2, –业务操作员
  16. out_return_code OUT VARCHAR2, –存储过程返回码
  17. out_visaInfoStr OUT VARCHAR2
  18. );
  20. END PY_PCKG_REFUND2;
  21. /
  24. CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS
  26. PROCEDURE PY_WEBREFUND_VISA_PREPARE (
  27. in_serialNoStr IN VARCHAR2, –用”|”隔开的一组网上退款申请流水号
  28. in_session_operatorid IN VARCHAR2,–业务操作员
  29. out_return_code OUT VARCHAR2, –存储过程返回码
  30. out_visaInfoStr OUT VARCHAR2
  31. ) IS
  32. –变量声明
  33. v_serialno VARCHAR2(20);–网上退款申请流水号
  34. v_refserialno VARCHAR2(20);–支付交易流水号
  35. v_tobankOrderNo VARCHAR2(30);–上送银行的订单号
  36. v_orderDate VARCHAR2(8);–订单日期
  37. v_businessType VARCHAR2(10);–业务类型
  38. v_currType VARCHAR2(3);–订单类型（ET－电子机票）
  39. v_merno VARCHAR2(15);–商户号
  40. v_orderNo VARCHAR2(20);–商户订单号
  41. v_orderState VARCHAR2(2);
  42. v_refAmount NUMBER(15,2);–退款金额
  43. v_tranType VARCHAR(2);–交易类型
  44. v_bank VARCHAR2(10);–收单银行
  45. v_date VARCHAR2 (8);–交易日期
  46. v_time VARCHAR2 (6);–交易时间
  47. v_datetime VARCHAR2 (14);–获取的系统时间
  48. v_index_start NUMBER;
  49. v_index_end NUMBER;
  50. v_i NUMBER;
  51. BEGIN
  52. — 初始化参数
  53. out_visaInfoStr := ”;
  54. v_i := 1;
  55. v_index_start := 1;
  56. v_index_end := INSTR(in_serialNoStr,’|’,1,1);
  57. v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end-1);
  58. v_datetime := TO_CHAR (SYSDATE, ‘yyyymmddhh24miss’);
  59. v_date := SUBSTR (v_datetime, 1, 8);
  60. v_time := SUBSTR (v_datetime, 9, 14);
  62. –从退款请求表中查询定单信息（商户号、商户订单号、退款金额）
  63. WHILE v_index_end > 0 LOOP
  64. SELECT
  65. WEBR_MERNO,
  66. WEBR_ORDERNO,
  67. WEBR_AMOUNT,
  68. WEBR_SERIALNO,
  69. WEBR_REFUNDTYPE
  70. INTO
  71. v_merno,
  72. v_orderNo,
  73. v_refAmount,
  74. v_serialno,
  75. v_tranType
  76. FROM
  77. PY_WEB_REFUND
  78. WHERE
  79. WEBR_REFREQNO = v_refserialno;
  81. –将查询到的数据组成串
  82. out_visaInfoStr := out_visaInfoStr || v_merno || ‘~’ || v_orderNo || ‘~’ || v_refAmount + ‘|’;
  84. –为下次循环做数据准备
  85. v_i := v_i + 1;
  86. v_index_start := v_index_end + 1;
  87. v_index_end := INSTR(in_serialNoStr,’|’,1,v_i);
  88. IF v_index_end > 0 THEN
  89. v_refserialno := SUBSTR(in_serialNoStr, v_index_start, v_index_end – 1);
  90. END IF;
  92. –根据原支付流水号在流水表中查询该订单的信息，包括原上送银行或第三方的订单号：WTRN_TOBANKORDERNO
  93. SELECT
  94. WTRN_TOBANKORDERNO,
  95. WTRN_ORDERNO,
  96. WTRN_ORDERDATE,
  97. WTRN_BUSINESSTYPE,
  98. WTRN_ACCPBANK,
  99. WTRN_TRANCURRTYPE
  100. INTO
  101. v_tobankOrderNo,
  102. v_orderNo,
  103. v_orderDate,
  104. v_businessType,
  105. v_bank,
  106. v_currType
  107. FROM PY_WEBPAY_VIEW
  108. WHERE WTRN_SERIALNO = v_serialno;
  110. –记录流水表(退款)
  111. INSERT INTO PY_WEBPAY_TRAN(
  112. WTRN_SERIALNO,
  113. WTRN_TRANTYPE,
  114. WTRN_ORIGSERIALNO,
  115. WTRN_ORDERNO,
  116. WTRN_ORDERDATE,
  117. WTRN_BUSINESSTYPE,
  118. WTRN_TRANCURRTYPE,
  119. WTRN_TRANAMOUNT,
  120. WTRN_ACCPBANK,
  121. WTRN_TRANSTATE,
  122. WTRN_TRANTIME,
  123. WTRN_TRANDATE,
  124. WTRN_MERNO,
  125. WTRN_TOBANKORDERNO
  126. )VALUES(
  127. v_refserialno, –和申请表的流水号相同，作为参数传人
  128. v_tranType,
  129. v_serialno, –原交易流水号，查询退款申请表得到
  130. v_orderNo,
  131. v_orderDate,
  132. v_businessType,
  133. v_currType,
  134. v_refAmount,
  135. v_bank,
  136. ‘1’,
  137. v_time,
  138. v_date,
  139. v_merno,
  140. v_tobankOrderNo –上送银行的订单号，查询流水表得到
  141. );
  143. –更新网上退款申请表
  144. UPDATE PY_WEB_REFUND
  145. SET
  146. WEBR_IFDISPOSED = ‘1’,
  147. WEBR_DISPOSEDOPR = in_session_operatorid,
  148. WEBR_DISPOSEDDATE = v_datetime
  149. WHERE
  150. WEBR_REFREQNO = v_refserialno;
  152. –更新定单表
  153. IF v_tranType = ‘2’ THEN
  154. v_orderState := ‘7’;
  155. ELSE
  156. v_orderState := ’10’;
  157. END IF;
  159. UPDATE PY_ORDER
  160. SET
  161. ORD_ORDERSTATE = v_orderState
  162. WHERE
  163. ORD_ORDERNO = v_orderNo
  164. AND ORD_ORDERDATE = v_orderDate
  165. AND ORD_BUSINESSTYPE = v_businessType;
  166. END LOOP;
  168. — 异常处理
  169. EXCEPTION
  170. WHEN OTHERS THEN
  171. ROLLBACK;
  172. out_return_code := ‘14001’;
  173. RETURN;
  174. END;
  176. END PY_PCKG_REFUND2;
  177. /
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Oracle存储过程实例

喜欢 (0)赏
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址