oracle存储过程基础语法+提升+例子总结

1.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 WHERETYPEID=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
连接数据库后建立一个TestWINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

2.关于oracle存储过程的若干问题备忘

1.在oracle中,数据表别名不能加as,如:
selecta.appname from appinfo a;-- 正确
selecta.appname from appinfo as a;-- 错误
也许,是怕和oracle中的存储过程中的关键字as冲突的问题吧
2.在存储过程中,select某一字段时,后面必须紧跟into,如果select整个记录,利用游标的话就另当别论了。
select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 有into,正确编译
selectaf.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into,编译报错,提示:Compilation
Error:PLS-00428:an INTOclause is expected in this SELECT statement

3.在利用select...into...语法时,必须先确保数据库中有该条记录,否则会报出"no datafound"异常。
可以在该语法之前,先利用select count(*) from查看数据库中是否存在该记录,如果存在,再利用select...into...
4.在存储过程中,别名不能和字段名称相同,否则虽然编译可以通过,但在运行阶段会报错
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行
selectaf.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-- 运行阶段报错,提示
ORA-01422:exact fetch returns more than requested number of rows
5.在存储过程中,关于出现null的问题
假设有一个表A,定义如下:
createtable A(
id
varchar2(50) primary key not null,
vcount
number(8) not null,
bid
varchar2(50) not null -- 外键
);
如果在存储过程中,使用如下语句:
selectsum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid="xxxxxx"的记录,则fcount=null(即使fcount定义时设置了默认值,如:fcountnumber(8):=0依然无效,fcount还是会变成null),这样以后使用fcount时就可能有问题,所以在这里最好先判断一下:
iffcount is null then
fcount:
=0;
end
if;
这样就一切ok了。
6.Hibernate调用oracle存储过程
 this.pnumberManager.getHibernateTemplate().execute(
 new HibernateCallback() {
 public ObjectdoInHibernate(Session session)
 throws HibernateException,SQLException {
 CallableStatementcs = session
 .connection()
 .prepareCall("{callmodifyapppnumber_remain(?)}");
 cs.setString(1,foundationid);
 cs.execute();
 return null;
 }
 });

3.存储过程中Sequence的使用

Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

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

 

 

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

Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

  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代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

  1. create or replace procedureInsertRecordWithSequence(UserID out number,UserName invarchar2,UserAge in number)
  2. is
  3. begin insert into test(id, name, age)--插入一条记录,PK值从Sequece获取
  4. values(test_seq.nextval, UserName, UserAge);
  5. select test_seq.currval into UserID fromdual;
  6. end InsertRecordWithSequence;

4.存储过程中包的定义

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

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

Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

  1. create or replace package TestPackage is
  2. type mycursor is ref cursor; -- 定义游标变量
  3. procedure GetRecords(ret_cursor out mycursor); --定义过程,用游标变量作为返回参数
  4. end TestPackage;
  5. 包体是这么定义的:
  6. create or replace package body TestPackage is
  7. procedure GetRecords(ret_cursor out mycursor)as
  8. begin
  9. open ret_cursor for select * from test;
  10. end GetRecords;
  11. end TestPackage;

小结:

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

 

 

Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

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

包体如下:

Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

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

TestPackage.SelectRecords

-------------------------------------------------------------------------------------------------------------------------------------------------------------

5.存储过程详细例子

 

最近换了一个项目组,晕,要写oracle的存储过程,幸亏写过一些db2的存储过程,尚且有些经验,不过oralce的pl/sql不大一样,花费了一下午的时间写了一个出来,测试编译通过了,是为记,以备以后查阅。

Java代码 Oracle存储过程实例 - qindingsky - 宁静以致远

 

  1. CREATE OR REPLACE PACKAGE PY_PCKG_REFUND2AS
  2. ------------------------------------------------------------------------
  3. -- Oracle 包
  4. ---国航支付平台VISA退款
  5. -- 游标定义:
  6. --
  7. -- 存储过程定义:
  8. -- PY_WEBREFUND_VISA_PREPARE :VISA退款准备
  9. -- 最后修改人:dougq
  10. -- 最后修改日期:2007.4.17
  11. ------------------------------------------------------------------------
  12. PROCEDURE PY_WEBREFUND_VISA_PREPARE(
  13. in_serialNoStr IN VARCHAR2,--用"|"隔开的一组网上退款申请流水号
  14. in_session_operatorid IN VARCHAR2,--业务操作员
  15. out_return_code OUT VARCHAR2,--存储过程返回码
  16. out_visaInfoStr OUT VARCHAR2
  17. );
  18. END PY_PCKG_REFUND2;
  19. /
  20. CREATE OR REPLACE PACKAGE BODY PY_PCKG_REFUND2AS
  21. PROCEDURE PY_WEBREFUND_VISA_PREPARE(
  22. in_serialNoStr IN VARCHAR2,--用"|"隔开的一组网上退款申请流水号
  23. in_session_operatorid INVARCHAR2,--业务操作员
  24. out_return_code OUT VARCHAR2,--存储过程返回码
  25. out_visaInfoStr OUT VARCHAR2
  26. ) IS
  27. --变量声明
  28. v_serialno VARCHAR2(20);--网上退款申请流水号
  29. v_refserialno VARCHAR2(20);--支付交易流水号
  30. v_tobankOrderNoVARCHAR2(30);--上送银行的订单号
  31. v_orderDate VARCHAR2(8);--订单日期
  32. v_businessType VARCHAR2(10);--业务类型
  33. v_currTypeVARCHAR2(3);--订单类型(ET-电子机票)
  34. v_merno VARCHAR2(15);--商户号
  35. v_orderNo VARCHAR2(20);--商户订单号
  36. v_orderState VARCHAR2(2);
  37. v_refAmount NUMBER(15,2);--退款金额
  38. v_tranType VARCHAR(2);--交易类型
  39. v_bank VARCHAR2(10);--收单银行
  40. v_date VARCHAR2 (8);--交易日期
  41. v_time VARCHAR2 (6);--交易时间
  42. v_datetime VARCHAR2 (14);--获取的系统时间
  43. v_index_start NUMBER;
  44. v_index_end NUMBER;
  45. v_i NUMBER;
  46. BEGIN
  47. -- 初始化参数
  48. out_visaInfoStr := '';
  49. v_i := 1;
  50. v_index_start := 1;
  51. v_index_end :=INSTR(in_serialNoStr,'|',1,1);
  52. v_refserialno := SUBSTR(in_serialNoStr,v_index_start, v_index_end-1);
  53. v_datetime := TO_CHAR (SYSDATE,'yyyymmddhh24miss');
  54. v_date := SUBSTR (v_datetime, 1, 8);
  55. v_time := SUBSTR (v_datetime, 9,14);
  56. --从退款请求表中查询定单信息(商户号、商户订单号、退款金额)
  57. WHILE v_index_end > 0LOOP
  58. SELECT
  59. WEBR_MERNO,
  60. WEBR_ORDERNO,
  61. WEBR_AMOUNT,
  62. WEBR_SERIALNO,
  63. WEBR_REFUNDTYPE
  64. INTO
  65. v_merno,
  66. v_orderNo,
  67. v_refAmount,
  68. v_serialno,
  69. v_tranType
  70. FROM
  71. PY_WEB_REFUND
  72. WHERE
  73. WEBR_REFREQNO = v_refserialno;
  74. --将查询到的数据组成串
  75. out_visaInfoStr := out_visaInfoStr || v_merno ||'~' || v_orderNo || '~' || v_refAmount + '|';
  76. --为下次循环做数据准备
  77. v_i := v_i + 1;
  78. v_index_start := v_index_end + 1;
  79. v_index_end :=INSTR(in_serialNoStr,'|',1,v_i);
  80. IF v_index_end > 0THEN
  81. v_refserialno := SUBSTR(in_serialNoStr,v_index_start, v_index_end - 1);
  82. END IF;
  83. --根据原支付流水号在流水表中查询该订单的信息,包括原上送银行或第三方的订单号:WTRN_TOBANKORDERNO
  84. SELECT
  85. WTRN_TOBANKORDERNO,
  86. WTRN_ORDERNO,
  87. WTRN_ORDERDATE,
  88. WTRN_BUSINESSTYPE,
  89. WTRN_ACCPBANK,
  90. WTRN_TRANCURRTYPE
  91. INTO
  92. v_tobankOrderNo,
  93. v_orderNo,
  94. v_orderDate,
  95. v_businessType,
  96. v_bank,
  97. v_currType
  98. FROM PY_WEBPAY_VIEW
  99. WHERE WTRN_SERIALNO = v_serialno;
  100. --记录流水表(退款)
  101. INSERT INTO PY_WEBPAY_TRAN(
  102. WTRN_SERIALNO,
  103. WTRN_TRANTYPE,
  104. WTRN_ORIGSERIALNO,
  105. WTRN_ORDERNO,
  106. WTRN_ORDERDATE,
  107. WTRN_BUSINESSTYPE,
  108. WTRN_TRANCURRTYPE,
  109. WTRN_TRANAMOUNT,
  110. WTRN_ACCPBANK,
  111. WTRN_TRANSTATE,
  112. WTRN_TRANTIME,
  113. WTRN_TRANDATE,
  114. WTRN_MERNO,
  115. WTRN_TOBANKORDERNO
  116. )VALUES(
  117. v_refserialno, --和申请表的流水号相同,作为参数传人
  118. v_tranType,
  119. v_serialno, --原交易流水号,查询退款申请表得到
  120. v_orderNo,
  121. v_orderDate,
  122. v_businessType,
  123. v_currType,
  124. v_refAmount,
  125. v_bank,
  126. '1',
  127. v_time,
  128. v_date,
  129. v_merno,
  130. v_tobankOrderNo --上送银行的订单号,查询流水表得到
  131. );
  132. --更新网上退款申请表
  133. UPDATE PY_WEB_REFUND
  134. SET
  135. WEBR_IFDISPOSED = '1',
  136. WEBR_DISPOSEDOPR =in_session_operatorid,
  137. WEBR_DISPOSEDDATE = v_datetime
  138. WHERE
  139. WEBR_REFREQNO = v_refserialno;
  140. --更新定单表
  141. IF v_tranType = '2' THEN
  142. v_orderState := '7';
  143. ELSE
  144. v_orderState := '10';
  145. END IF;
  146. UPDATE PY_ORDER
  147. SET
  148. ORD_ORDERSTATE = v_orderState
  149. WHERE
  150. ORD_ORDERNO = v_orderNo
  151. AND ORD_ORDERDATE = v_orderDate
  152. AND ORD_BUSINESSTYPE =v_businessType;
  153. END LOOP;
  154. -- 异常处理
  155. EXCEPTION
  156. WHEN OTHERS THEN
  157. ROLLBACK;
  158. out_return_code := '14001';
  159. RETURN;
  160. END;
  161. END PY_PCKG_REFUND2;
  162. /  
6.带游标cursor的存储过程详细例子
Sql代码 复制代码 收藏代码
  1. --1、简单的存储过程
  2. create or replaceprocedure procedure_test
  3. (p_id in varchar,p_statusout varchar) --p_id为输入参数,p_status为输出参数
  4. as
  5. t_name varchar2(20);
  6. t_count number:=0;
  7. begin
  8. select votetitle,vatesum intot_name,t_count from votemaster whereid=p_id; --注意:此处没有:来赋值
  9. if t_count <=0 then
  10. p_status:= t_name||':差';
  11. elsif t_count >0 and t_count<3 then
  12. p_status:= t_name||':良好';
  13. else
  14. p_status:= t_name||':优秀';
  15. end if;
  16. end;
  17. --执行
  18. declare
  19. out_param varchar2(50);
  20. begin
  21. procedure_test('1',out_param);
  22. dbms_output.put_line(out_param);
  23. end;
  24. --2、带游标的存储过程
  25. create or replaceprocedure procedure_cursor_test
  26. (p_id in varchar2,p_status outvarchar2)
  27. as
  28. vote votemaster%rowtype;--声明一个对象(votemaster)类型的对象
  29. cursor my_cur is select* from votemaster; --声明一个游标并填充数据
  30. begin
  31. open my_cur; --打开游标
  32. loop
  33. fetch my_cur into vote ;--循环游标,并放入对象
  34. exit when my_cur%notfound;--如果没有数据,则直接exit
  35. if vote.id=p_id then
  36. p_status :=vote.votetitle||':'||vote.vatesum;
  37. --如果想终止循环,可以直接exit;
  38. end if;
  39. end loop;
  40. close my_cur; --关闭游标
  41. end;
  42. --执行
  43. declare
  44. out_param varchar2(50);
  45. begin
  46. procedure_cursor_test('1',out_param);
  47. dbms_output.put_line(out_param);
  48. end;  
7.存储过程中function使用详细例子
Sql代码 复制代码 收藏代码
  1. --包定义
  2. create orreplace package t_package
  3. is
  4. --定义过程
  5. procedure append_proc(t varchar2,aout varchar2);
  6. --过程的重载
  7. procedure append_proc(t number,aout varchar2);
  8. --定义函数
  9. function append_fun(t varchar2)return varchar2;
  10. end;
Sql代码 复制代码 收藏代码
  1. --包主题
  2. create orreplace package body t_package
  3. is
  4. v_t varchar2(30);
  5. --私有成员函数
  6. function private_fun(t varchar2)return varchar2 is
  7. begin
  8. v_t := t||'hello';
  9. return v_t;
  10. end;
  11. --实现过程
  12. procedure append_proc(t varchar2,aout varchar2) is
  13. begin
  14. a := t||'hello';
  15. end;
  16. --过程的重载
  17. procedure append_proc(t number,aout varchar2) is
  18. begin
  19. a := t||'hello';
  20. end;
  21. --实现函数
  22. function append_fun(tvarchar2)
  23. return varchar2 is
  24. begin
  25. v_t := t||'hello';
  26. return v_t;
  27. end;
  28. end;  
阅读更多
换一批

没有更多推荐了,返回首页