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，如：

select a.appname from appinfo a; -- 正确
select a.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，正确编译
select af.keynode from APPFOUNDATION af where af.appid = aid and af.foundationid = fid; -- 没有into，编译报错，提示：Compilation
Error:PLS - 00428 :an INTO clause 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; -- 正确运行
select af.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，定义如下：

create table 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时就可能有问题，所以在这里最好先判断一下：

if fcount 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代码

 

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代码

 

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 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代码

 

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代码

 

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代码

 

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代码

 

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;