oracle 存储过程的基本语法 及注意事项.doc

oracle 存储过程的基本语法1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字( 参数1 IN NUMBER, 参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字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 BEGINXXXX END; END LOOP;5.变量赋值 V_TEST := 123;6.用for in 使用cursor . IS CURSOR cur IS SELECT * FROM xxx; BEGINFOR cur_result in cur LOOPBEGINV_SUM :=cur_result.列名1+cur_result.列名2END;END LOOP; END;7.带参数的cursor CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID; OPEN C_USER(变量值); LOOPFETCH 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存储过程的若干问题备忘1.在oracle中，数据表别名不能加as，如：selecta.appnamefromappinfoa;- 正确selecta.appnamefromappinfoasa;- 错误也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;-有into，正确编译selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;-没有into，编译报错，提示：CompilationError:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement3.在利用o.语法时，必须先确保数据库中有该条记录，否则会报出no data found异常。 可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用o.4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;-正确运行selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;-运行阶段报错，提示ORA-01422:exactfetchreturnsmorethanrequestednumberofrows5.在存储过程中，关于出现null的问题假设有一个表A，定义如下：createtableA(idvarchar2(50)primarykeynotnull,vcountnumber(8)notnull,bidvarchar2(50)notnull-外键);如果在存储过程中，使用如下语句：selectsum(vcount)intofcountfromAwherebid=xxxxxx;如果A表中不存在bid=xxxxxx的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：iffcountisnullthenfcount:=0;endif;这样就一切ok了。6.Hibernate调用oracle存储过程this.pnumberManager.getHibernateTemplate().execute(newHibernateCallback().publicObjectdoInHibernate(Sessionsession)throwsHibernateException,SQLException.CallableStatementcs=session.connection().prepareCall(callmodifyapppnumber_remain(?);cs.setString(1,foundationid);cs.execute();returnnull;);Oracle存储过程中循环遍历数组的实际应用 时间 ： 2010-07-06 作者：网络 编辑：huyang629 点击： 7 评论 -综合 资源 电子书 社区 如果你是Oracle存储过程方面的新手，对Oracle存储过程的相关实际应用不是很了解的话，以下的文章会给你提供更全面的知识。下面就是文章的具体介绍，望你浏览完以下的内容会有所收获。循环遍历数组 create or replace procedure test(varArray in myPackage.TestArray) as (输入参数varArray 是自定义的数组类型，定义方式见标题6)i number; begin i := 1; Oracle存储过程数组是起始位置是从1开始的，与java、C、C+等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历for i in 1.varArray.count LOOP dbms_output.putline('The No.'| i | 'record in varArray is:'|varArray(i); end LOOP; end test; While 循环 while 条件语句 LOOPbegin end; end LOOP; E.g create or replace procedure test(i in number) as begin while i isal and job=sjob; 5 if icount=1 then 6 . 9 else 10 . 12 end if; 13 exception 14 when too_many_rows then 15 DBMS_OUTPUT.PUT_LINE(返回值多于1行); 16 when others then 17 DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错！); 18 end; 19 过程调用 方式一 1 declare 2 realsal emp.sal%type; 3 realname varchar(40); 4 realjob varchar(40); 5 begin 6 realsal:=1100; 7 realname:=; 8 realjob:=CLERK; 9 runbyparmeters(realsal,realname,realjob); 必须按顺序 10 DBMS_OUTPUT.PUT_LINE(REALNAME| |REALJOB); 11 END; 12 方式二 1 declare 2 realsal emp.sal%type; 3 realname varchar(40); 4 realjob varchar(40); 5 begin 6 realsal:=1100; 7 realname:=; 8 realjob:=CLERK; 9 runbyparmeters(sname=realname,isal=realsal,sjob=realjob); 指定值对应变量顺序可变 10 DBMS_OUTPUT.PUT_LINE(REALNAME| |REALJOB); 11 END; 本文来自CSDN博客，转载请标明出处：/Icandoeverything/archive/2009/03/19/4005524.aspxoracle存储过程一例 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. - 12. 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. ); 19. 20. END PY_PCKG_REFUND2; 21. / 22. 23. 24. CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2 AS 25. 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); 61. 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; 80. 81. -将查询到的数据组成串 82. out_visaInfoStr := out_visaInfoStr | v_merno | | v_orderNo | | v_refAmount + |; 83. 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; 91. 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; 109. 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. ); 142. 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; 151. 152. -更新定单表 153. IF v_tranType = 2 THEN 154. v_orderState := 7; 155. ELSE 156. v_orderState := 10; 157. END IF; 158. 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; 167. 168. - 异常处理 169. EXCEPTION 170. WHEN OTHERS THEN 171. ROLLBACK; 172. out_return_code := 14001; 173. RETURN; 174. END; 175. 176. END PY_PCKG_REFUND2; 177. / Oracle存储过程可以有无参数存储过程和带参数存储过程。一、无参程序过程语法1createorreplaceprocedureNoParPro2as;3begin4;5exception /存储过程异常6;7end;8 二、带参存储过程实例1createorreplaceprocedurequeryempname(sfindnoemp.empno%type)as2sNameemp.ename