Oracle存储过程1

CREATE OR REPLACE 
PROCEDURE "Func_Users" (id "T_Users".USER_ID%TYPE)--默认参数类型是in，等价于id  IN "T_Users".USER_ID%TYPE
AS
var_id "T_Users".USER_ID%TYPE;--声明变量，确定类型
BEGIN
                  var_id:=id;--为变量赋值
	  DBMS_OUTPUT.PUT_LINE(var_id);--输出变量
END;

 

变量声明及赋值

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

 

   

CREATE OR REPLACE 
PROCEDURE "Func_Users" (USER_ID "T_Users".USER_ID%TYPE,NAME_ID OUT NVARCHAR2)
AS
VAR_ID "T_Users".USER_ID%TYPE;
BEGIN
	VAR_ID:=USER_ID;--为声明的变量赋值
	NAME_ID:=VAR_ID;--为返回参数赋值
END;

 

 

返回参数 OUT

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

 

        String sql="{Call FUNC_USERS(?,?)}";
        CallableStatement cst;
        try
        {
            Connection conn=HibernateSessionFactory.getSession().connection();
            cst = conn.prepareCall(sql);
            cst.setInt(1,123);
            cst.registerOutParameter(2,java.sql.Types.VARCHAR);
            cst.executeQuery();
            String strResult=cst.getString(2);
            System.out.println(strResult);
            HibernateSessionFactory.getSession().connection().close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

 

通过java调用存储过程取得OUT参数

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

 

        String sql="{Call FUNC_USERS(:USER_ID,:NAME_ID)}";
        CallableStatement cst;
        try
        {
            Connection conn=HibernateSessionFactory.getSession().connection();
            cst = conn.prepareCall(sql);
            cst.setInt("USER_ID", 123);
            cst.registerOutParameter("NAME_ID",java.sql.Types.VARCHAR);
            cst.executeQuery();
            String strResult=cst.getString("NAME_ID");
            System.out.println(strResult);
            HibernateSessionFactory.getSession().connection().close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

 

当然为了防止对错数字，也可以使用参数命名的方式设置参数

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