Oracle存储过程的调用(返回参数)

CREATE OR REPLACE PROCEDURE test_in_out_exception (v_empno VARCHAR2,v_guess_sal NUMBER,v_true_sal OUT NUMBER)
AS
   v_exits   VARCHAR2 (20);
   v_sal     emp.sal%TYPE;
   v_raise EXCEPTION;
BEGIN
   SELECT   COUNT (1)
     INTO   v_exits
     FROM   emp t
    WHERE   t.empno = v_empno;

   IF (v_exits > 0)
   THEN
      SELECT   t.sal
        INTO   v_sal
        FROM   emp t
       WHERE   t.empno = v_empno;
       v_true_sal :=  v_sal;
      IF (v_sal > v_guess_sal)
      THEN
         DBMS_OUTPUT.Put_line ('猜大了');
      ELSIF (v_sal < v_guess_sal)
      THEN
         DBMS_OUTPUT.Put_line ('猜小了');
      ELSIF (v_sal = v_guess_sal)
      THEN
         DBMS_OUTPUT.Put_line ('猜中了');
      END IF;
   ELSE
      RAISE v_raise;
   END IF;
EXCEPTION
   WHEN v_raise
   THEN
      RAISE_APPLICATION_ERROR (-20010, 'v_empno not exists!');
   WHEN NO_DATA_FOUND
   THEN
      RAISE_APPLICATION_ERROR (-20011, 'ERROR：不存在!');
   WHEN OTHERS
   THEN
      ROLLBACK;
END;

Java 代码

        try {
            // 创建存储过程的对象
            CallableStatement c = conn.prepareCall("{call test_in_out_exception(?,?,?)}");

            // 给存储过程的第一个参数设置值
            c.setString(1, "7369");// 7369结果是 800    736900结果是 报错
            c.setInt(2, 24);
            // 注册存储过程的第二个参数
            c.registerOutParameter(3, java.sql.Types.INTEGER);
            // 执行存储过程
            c.execute();
            // 得到存储过程的输出参数值
            System.out.println(c.getInt(3));
        } catch (SQLException e) {
            System.out.println("SQLState : " + e.getSQLState());
            System.out.println("ErrorCode : " + e.getErrorCode());
            System.out.println("Message : " + e.getMessage());
            System.out.println("LocalizedMessage : " + e.getLocalizedMessage());
            //e.printStackTrace();
        } finally {
            conn.close();
        }

 表是数据oracle 的 scott  用户 默认的表

转载于:https://www.cnblogs.com/opaljc/p/3367906.html