存储过程开发

String call=\"{call proc_select(?,?)};\" //调用语句

CallableStatement proc=con.preparecall(call); //调用存储过程

proc.setString(1,\"12345678\"); //给输入参数传值

proc.registerOutParameter(2,Type.varchar); //声明输出参数是什么类型的

proc.execute(); //执行 String address=proc.getString(2); //获得输出参数

 

Java code

 CallableStatement proc = connection.prepareCall(\"{ ? = call snuffed_it_when(?) }\");    proc.registerOutParameter(1, Types.INTEGER);
    proc.setString(2, poetName);

在plsql中创建一个存储过程：（file-新建-存储过程）
create or replace procedure a_time
is
begin
dbms_session.set_nls('nls_date_format','''yyyy-mm-dd''');
dbms_output.put_line(sysdate);
end;


执行一个存储过程：（命令窗口）
exec a_time;

具有参数的存储过程：（以测试数据t_user为例）
 create or replace procedure b_insert (i t_user.id%type,n t_user.name%type,a t_user.gender%type,b t_user.age%type,c t_user.address%type)
 is
 begin
 insert into t_user values(i,n,a,b,c);
 commit;
 end;




带有输出参数的存储过程
 create or replace procedure c_update(old varchar2,new t_user.id%type,t_user_test out t_user.name%type)
 is
 begin
 select name into t_user_test from t_user where id=old;
 update t_user set id=new where id=old;
 commit;
 end;

 执行：
 declare
 nn t_user.name%type;
 begin
 c_update(&old,&new,nn);
 dbms_output.put_line('被修改id的员工姓名：'||nn);
 end;
 

带有输入输出参数的存储过程：
create or replace procedure in_out
 (n1 in out number,n2 in out number) is
 v1 number;
 v2 number;
 begin
 v1:=trunc(n1/n2);
 v2:=mod(n1,n2);
 n1:=v1;
 n2:=v2;
 end;

执行：
declare
 a1 number:=&n1;
 a2 number:=&n2;
 begin
 in_out(a1,a2);
 dbms_output.put_line('除法的商'||a1||',除法的余数:'||a2);
 end;


在plsql中如果要看到控制台的语句需要在命令窗口中先执行：
 Set   Serveroutput   On 
然后执行相应的调用和执行
create   or   replace   trigger   Temp_bur 
    before   insert   on   temp     
    for   each   row 
    begin 
          dbms_output.put_line( 'bur   old   N: '||:old.n|| '   M: '||:old.M); 
          dbms_output.put_line( 'BUR   new   N: '||:new.n|| '   M: '||:new.M);     
    end   Temp_bur; 

 java中调用存储过程：

package callProcedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

// 调用存储过程
public class CallProcedure {

public static Connection con = null;
// 调用存储过程,函数特有的封装类
public static CallableStatement callableStatement = null;
public static ResultSet rs = null;

public static void main(String args[]) throws SQLException {

   System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>调用存储过程<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
   // 调用无返回值的存储过程
   callNoReturnValueProcedure();
   // 调用带参数的存储过程
   callParamProcedure();
   // 调用只有一个返回值的存储过程
   callOnlyOneReturnValueProcedure();
   // 调用有一组返回值的存储过程
   callReturnValuesProcedure();
   System.out.println(">>>>>>>>>>>>>>>>>><<>>>>>>>>>>>调用函数<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
   // 调用有一个返回值的函数
   callReturnValueFunction();
}

////////////////////////////*******调用无返回值的存储过程*******/////////////////////////////
public static void callNoReturnValueProcedure() throws SQLException {
   try {
    con = getConnection();
    callableStatement = con.prepareCall("{call noReturnValueProcedure}");
    callableStatement.execute();
   } catch (SQLException sqlException) {
    sqlException.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   } finally {
    close();
   }
   System.out.println("callNoReturnValueProcedure()无返回值存储过程执行成功！");
}
/*
上面callNoReturnValueProcedure()调用的存储过程如下：
   create or replace procedure noReturnValueProcedure is -- 如果存储过程没有参数则不带括号,如果写成testProcedure()则报错
   begin
    insert into cht.remarke_info
    values
      ('001',
       '001',
       to_char(sysdate, 'yyyymmdd'),
       to_char(sysdate, 'yyyymm'),
       'cht',
       '发展的还可以。',
       sysdate,
       100);
   end noReturnValueProcedure;
*/ 

//////////////////////////////*******调用带参数的存储过程*******//////////////////////////////
public static void callParamProcedure() throws SQLException {
   try {
    con = getConnection();
//    // 写法一
//    callableStatement = con.prepareCall("{call paramProcedure('bonc-cht', 2001)}");
   
    // 写法二
    callableStatement = con.prepareCall("{call paramProcedure(?, ?)}");
    callableStatement.setString(1, "bonc-cht");
    callableStatement.setInt(2, 3001);
   
    callableStatement.execute();
   } catch (SQLException sqlException) {
    sqlException.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   } finally {
    close();
   }
   System.out.println("callParamProcedure()带参数存储过程执行成功！");
}
/*
上面callParamProcedure()调用的存储过程如下：
   create or replace procedure paramProcedure(userID varchar2, ramark_info_id number) is
    user_id varchar2(200); -- 定义字符串类型的内部变量要声明长度，否则报错(如：user_id varchar2报错)，但是number类型的不用,如：v_id_no number;
   begin
    user_id := userID; -- 赋值操作,写成 userid = userID 也可以,一般都写成：:=
    insert into cht.remarke_info
    values
      ('002',
       '002',
       to_char(sysdate, 'yyyymmdd'),
       to_char(sysdate, 'yyyymm'),
       user_id, -- 内部变量
       '发展的一般般。',
       sysdate,
       ramark_info_id); -- 所传参数变量
   end paramProcedure;
*/

/////////////////////////*******调用只有一个返回值的存储过程*******//////////////////////////
public static void callOnlyOneReturnValueProcedure() throws SQLException {
   try {
    con = getConnection();
    callableStatement = con.prepareCall("{call cht.onlyOneReturnValueProcedure(?, ?)}");
    callableStatement.setString(1, "fdasfdsa");
    // 设置返回值的类型2表示第二个参数
    callableStatement.registerOutParameter(2, Types.INTEGER);
    callableStatement.execute();
    // 获取返回值（一个）
    // 注意：callableStatement.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，
    // 如果out是在第一个位置，那就是callableStatement.getString(1)，
    // 如果是第三个位置，就是callableStatement.getString(3)。
    // 当然也可以同时有多个返回值，那就是再多加几个out参数了。
    int label_inst_id = callableStatement.getInt(2); // 注意：getInt(2)别写成了getInt("2")
    System.out.println("callOnlyOneReturnValueProcedure()只有一个返回值的存储过程的返回值(最大值)：" + label_inst_id);
   } catch (SQLException sqlException) {
    sqlException.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   } finally {
    close();
   }
}
/*
上面callOnlyOneReturnValueProcedure()调用的存储过程如下：
   说明：存储过程只能通过output参数来返回结果，只要在存储过程已经给这个输出参数赋值了,它就能返回回去
     这种返回方式可以返回多个值，通过return返回值只是应用在函数function中
    create or replace procedure onlyOneReturnValueProcedure(user_ID in varchar2, max_label_inst_id out number) is
    begin
    select max(t.remarke_info_id)
       into max_label_inst_id　　---赋了值，能自动将该值返回
       from cht.remarke_info t
      where t.user_id = user_ID;
    end onlyOneReturnValueProcedure;
*/

///////////////////////////////*******调用有一组返回值的存储过程*******////////////////////////////
public static void callReturnValuesProcedure() throws SQLException {
   try {
    con = getConnection();
    callableStatement = con.prepareCall("{call getRemarke_info(?, ?)}");
    callableStatement.setString(1, "cht");
    // 预设输出结果的类型
    callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
    callableStatement.execute();
    // 将结果转化成结果集
    rs = (ResultSet)callableStatement.getObject(2);
    int i = 0;
    while (rs.next()) {
     System.out.println(rs.getString(1) + "\t" + 
       rs.getString(2) + "\t" + 
       rs.getString(3) + "\t" + 
       rs.getString(4) + "\t" + 
       rs.getString(5) + "\t" + 
       rs.getString(6) + "\t" + 
       rs.getString(7) + "\t" + 
       rs.getString(8));
     i ++;
    }
    System.out.println("调用有一组返回值的存储过程callReturnValuesProcedure()执行成功，有" + i + "条记录！");
   } catch (SQLException sqlException) {
    sqlException.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   } finally {
    close();
   }
}
/*
上面callReturnValuesProcedure()调用的包和存储过程(过程要返回一个结果集,要用到包和游标)如下：
   包：
    create or replace package myPackage as    ---as和is意思一样
     type cur is ref cursor; ---声明游标
     procedure getRemarke_info(userId in varchar2, outputValue out cur);   ---过程,函数等放在可以放在包里面,打包
    end myPackage;
   存储过程：
    create or replace procedure getRemarke_info(userId in varchar2, outputValue out myPackage.cur) is    ---as和is意思一样
    begin
     open outputValue for ---打开游标,存放结果
       select * from cht.remarke_info t where t.user_id = userId;
    end getRemarke_info;
*/

////////////////////////////*******调用有一个返回值的函数*******/////////////////////////////
public static void callReturnValueFunction() throws SQLException {
   try {
    con = getConnection();
    callableStatement = con.prepareCall("{? = call returnValuFunction(?)}"); // 如果函数无输入参数，则不要带括号如：returnValuFunction，带了反而报错，如:returnValuFunction()
    callableStatement.registerOutParameter(1, Types.VARCHAR);
    callableStatement.setString(2, "cht");
    callableStatement.execute();
   
    // 获取函数返回值getString(1)表示第一个问号(返回值)
    String output = callableStatement.getString(1);
    System.out.println("callReturnValueFunction()有一个返回值的函数执行成功！返回结果：" + output);
   } catch (SQLException sqlException) {
    sqlException.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   } finally {
    close();
   }
}
/*
上面callReturnValueFunction()调用的函数如下：
   create or replace function returnValuFunction(param varchar2) return varchar2 is ---return varchar2(varchar, number...)；如果函数无输入参数，则不要带括号如：returnValuFunction，带了反而报错，如:returnValuFunction()
    return_value varchar2(20);   --- 内部字符串变量必须声明长度
   begin
    select count(*)
      into return_value
    from 
      cht.remarke_info t
    where t.user_id = param;
    return return_value;
   end returnValuFunction;
*/


////////////////////////////////*******数据库操作的相关方法*******//////////////////////////////
// 建立数据库连接
public static Connection getConnection() {

   // 本工程所引的jar包
   String driver = "oracle.jdbc.driver.OracleDriver";
   // 注意thin:别少了:
   String url = "jdbc:oracle:thin:@133.64.140.45:1521:orcl";
   String userName = "cht";
   String password = "cht";

   Connection con = null;
   try {
    Class.forName(driver);
    con = DriverManager.getConnection(url, userName, password);
   } catch (SQLException sqlException) {
    sqlException.printStackTrace();
   } catch (Exception e) {
    e.printStackTrace();
   }
   return con;
}

// 关闭数据库操作
public static void close() {
   try {
    if (rs != null) {
     rs.close();
    }
    if (callableStatement != null) {
     callableStatement.close();
    }
    if (con != null) {
     con.close();
    }
   } catch (SQLException e) {
    e.printStackTrace();
   } catch (Exception e1) {
    e1.printStackTrace();
   }
}
}

/*
本文件中所用表的表结构如下：

CREATE TABLE "CHT"."REMARKE_INFO" 
   ( "LABEL_INST_ID" VARCHAR2(200), 
"DATE_TYPE" VARCHAR2(5), 
"DAY_ID" VARCHAR2(8), 
"MONTH_ID" VARCHAR2(6), 
"USER_ID" VARCHAR2(20), 
"REMARKE_CONTENT" VARCHAR2(500), 
"REMARKE_DATE" DATE, 
"REMARKE_INFO_ID" NUMBER
   )

*/