Posted By

jesusdomin on 09/23/11


Tagged

PLSQL store procedures CallableStatement


Versions (?)

Call store procedures from Java


 / Published in: Java
 

Classe JAVA amb exemples de com realitzar les crides a diferents tipus de procediments emmagatzemats: - Crida al procediment sense valors de retorn - Crida funcio que retorna dos valors - Funcio que retorna un cursor

  1. package cat.eix.exemples.exemple;
  2.  
  3. import java.sql.*;
  4. import oracle.jdbc.OracleTypes;
  5.  
  6. public class CallProc {
  7.  
  8. public static final String BDD = "jdbc:oracle:thin:@192.168.10.17:1521:ORA92";
  9. public static final String USR = "ICT";
  10. public static final String PWD = "ICT78";
  11.  
  12. public CallProc() {
  13. }
  14.  
  15. public static void main(String args []) throws SQLException {
  16. Connection conn = null;
  17. CallableStatement cstm = null;
  18. ResultSet rset = null;
  19.  
  20. try {
  21. // Carrega driver JDBC
  22. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  23.  
  24. // Connexio a BDD
  25. conn = DriverManager.getConnection (BDD, USR, PWD);
  26.  
  27. //
  28. // Crida al procediment sense valors de retorn
  29. //
  30. System.out.println( "Crida al procés sense valors de retorn:" );
  31. cstm = conn.prepareCall("begin call_proc.proc1( ?, ?, ? ); end;");
  32. cstm.setString(1, "Cadena"); //Exemple varchar2
  33. cstm.setLong(2, 1000000); //Exemple number
  34. cstm.setString(3, "21/11/1977"); //Exemple date
  35.  
  36. cstm.execute();
  37. System.out.println( "Procés executat." );
  38. System.out.println( "" );
  39. cstm.close();
  40.  
  41. //
  42. //Crida funcio que retorna dos valors
  43. //
  44. System.out.println( "Crida funcio que retorna dos valors:" );
  45. cstm = conn.prepareCall("begin ? := call_proc.func1( ?, ?, ?); end;");
  46. cstm.registerOutParameter(1, Types.VARCHAR); //Retorn
  47. cstm.registerOutParameter(4, Types.VARCHAR); //Paràmtre OUT
  48. cstm.setString(2, "Cadena"); //VARCHAR2
  49. cstm.setLong(3, 1000000); //NUMBER
  50.  
  51. cstm.execute();
  52. System.out.println( "Procés executat." );
  53.  
  54. String vVC2_1 = cstm.getString(1);
  55. String vVC2_4 = cstm.getString(4);
  56. System.out.println( "VALOR RETORN: " + vVC2_1 + " PARAMETE OUT: " + vVC2_4 );
  57. System.out.println( "" );
  58. cstm.close();
  59.  
  60. //
  61. //Funcio que retorna un cursor
  62. //
  63. System.out.println( "Funcio que retorna un cursor" );
  64. cstm = conn.prepareCall( "begin ? := call_proc.func2( ? ); end;" );
  65. cstm.registerOutParameter(1, OracleTypes.CURSOR);
  66. cstm.setLong(2, 10);
  67. cstm.execute();
  68.  
  69. // Recuperar resultset
  70. rset = (ResultSet) cstm.getObject(1);
  71. System.out.println( "Cursor recuperat: " );
  72.  
  73. while( rset.next() ) {
  74. System.out.println( " " + rset.getString("object_id") + " - " + rset.getString("object_name") );
  75. }
  76.  
  77. rset.close();
  78. cstm.close();
  79.  
  80. } catch (SQLException e) {
  81. e.printStackTrace();
  82. } finally {
  83. System.out.println("Closing....");
  84. if (rset != null) { rset.close(); rset = null; }
  85. if (cstm != null) { cstm.close(); cstm = null; }
  86. if (conn != null) { conn.close(); conn = null; }
  87. }
  88. }
  89. }

Report this snippet  

You need to login to post a comment.