oracle 存储过程

select (case when nvl(max(catgroup_id),1)=1 then 1 else max(catgroup_id) end) from catgroup;
从查到分类表，如果分类表中的最大的catgroup是空的，哪么它等于1，否则查询最大值给分类表
[b]1>有两个现x_user_out ,x_user_in 现在要把1表中的数据迁移2表中，
如果1中表的cnname不为空，则flag 为1，如果cnname存在则更新表1
日志文件：x_brand_log[/b]
create table X_USER_IN
(
ID NUMBER,
CNNAME VARCHAR2(200),
OTHERNAME VARCHAR2(100),
FLAG NUMBER
)
create table X_USER_out
(
ID NUMBER,
CNNAME VARCHAR2(200),
OTHERNAME VARCHAR2(100),
FLAG NUMBER
)

create table X_BRAND_LOG
(
TABLENAME VARCHAR2(20),
ACTIONTYPE VARCHAR2(20),
RESULT VARCHAR2(20),
V_ERRORCODE VARCHAR2(256),
V_ERRORINFO VARCHAR2(256),
REMARK VARCHAR2(100)
)
[b]out 为返回值，in为入参[/b]
[b]建立存储过程[/b]
create or replace procedure x_test_user(UserID out number,
UserName in varchar2,
UserAge in varchar2) is
Type curType Is Ref Cursor;
v_id user_out.id%TYPE;
v_cnname user_out.cnname%TYPE;
v_othername user_out.othername%TYPE;
x_date curType;
v_sql varchar2(200);
v_flag number;
v_errorinfo varchar2(200);
v_errorcode varchar2(200);
v_Count number;
begin
v_sql := 'select id,cnname,othername from x_user_out';
open x_date For v_sql;
Loop
fetch x_date
Into v_id, v_cnname, v_othername;
Exit When x_date%Notfound;
if v_cnname is not null then
v_flag := 0;
else
v_flag := 1;
end if;
select count(1) into V_count from x_user_in where cnname = v_cnname;
if (V_count <= 0) then
insert into x_user_in
(id, cnname, othername, flag)
values
(test_seq.nextval, v_cnname, v_othername, v_flag);
v_errorinfo := substr(sqlerrm, 0, 100);
v_errorcode := substr(sqlcode, 0, 100);
insert into X_BRAND_LOG
(V_ERRORCODE, ACTIONTYPE, v_errorinfo, gmt_date)
values
(v_errorcode, 'success', v_errorcode, sysdate);
else
update x_user_in set othername = v_othername where cnname = v_cnname;
UserID:=1;
reutrn ;
end if;
End loop commit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
rollback;
v_errorinfo := substr(sqlerrm, 0, 100);
v_errorcode := substr(sqlcode, 0, 100);
insert into X_BRAND_LOG
(V_ERRORCODE, ACTIONTYPE, v_errorinfo, gmt_date)
values
(v_errorinfo, 'failed', v_errorcode, sysdate);
commit;
WHEN OTHERS THEN
rollback;
v_errorinfo := substr(sqlerrm, 0, 100);
v_errorcode := substr(sqlcode, 0, 100);
insert into X_BRAND_LOG
(V_ERRORCODE, ACTIONTYPE, v_errorinfo, gmt_date)
values
(v_errorinfo, 'failed', v_errorcode, sysdate);
commit;
end x_test_user;

[b]在PLSQL中执行[/b]
declare
a number;
begin
x_test_user(a,'张利华','jak');
end;


[b]3返回值问题[/b]
1.public int Analyze1(String examid, String courseid, String gradeid,
2. String KeMu) {
3. String procedure = "{call teaching.dbo.A1(?,?,?,?,?)}";
4. int result = 0;
5. CallableStatement cstmt;
6. try {
7. Session session = this.getSession();
8. Connection con = session.connection();
9. cstmt = con.prepareCall(procedure);
10. cstmt.setString(1, gradeid);
11. cstmt.setString(2, examid);
12. cstmt.setString(3, courseid);
13. cstmt.setString(4, KeMu);
14.
15. [b] cstmt.registerOutParameter(5,java.sql.Types.INTEGER);
16. cstmt.executeUpdate();
17. result = cstmt.getInt(5); [/b][color=red][/color]
18. //session.close();
19. } catch (SQLException e) {
20. e.printStackTrace();
21. }
22. return result;
23. }

[b]oracle 存储过程的基本语法 （转）[/b]

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
xxx;
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单步调试

[b]一个实例（转）[/b]
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./

DBMS_OUTPUT.put_line( "putline====== ");--换行 end; 打印功能