oracle 存储过程

select (case when nvl(max(catgroup_id),1)=1 then 1 else max(catgroup_id) end)   from catgroup;
从查到分类表，如果分类表中的最大的catgroup是空的，哪么它等于1，否则查询最大值给分类表
1>有两个现x_user_out ,x_user_in 现在要把1表中的数据迁移2表中，
如果1中表的cnname不为空，则flag 为1，如果cnname存在则更新表1
日志文件：x_brand_log
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)
)
out 为返回值，in为入参
建立存储过程
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;

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


3返回值问题
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.            cstmt.registerOutParameter(5,java.sql.Types.INTEGER); 
16.            cstmt.executeUpdate(); 
17.            result = cstmt.getInt(5);  [color=red][/color]
18.            //session.close(); 
19.        } catch (SQLException e) { 
20.            e.printStackTrace(); 
21.        } 
22.        return result; 
23.    } 

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

一个实例（转）
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,;  
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; 打印功能