Oracle Stored Procedure 1 (returns the value of a single record)

Recently, I used java to verify the metadata of an excel form. The data size is very large and the verification rules are complicated. If you simply use the program for verification, the efficiency will be very low, considering that the oracle stored procedure can be compiled, the data will be put into the cache after each compilation, and the next retrieval will be very fast. Oracle

Recently, I used java to verify the metadata of an excel form. The data size is very large and the verification rules are complicated. If you simply use the program for verification, the efficiency will be very low, considering that the oracle stored procedure can be compiled, the data will be put into the cache after each compilation, and the next retrieval will be very fast. Oracle

Recently, we are using java to verify the information of cells in an excel table. The data size is very large and the verification rules are complicated. If we simply use the program for verification, the efficiency will be very low, considering that the oracle stored procedure can be compiled, the data will be put into the cache after each compilation, and the next retrieval will be very fast. I haven't touched any oracle stored procedures before, so it took me a few days to study them. Due to time constraints, there is no good arrangement. Now let's share some basic implementation methods.

The following describes how to use java to call a stored procedure to return the out value, because the stored procedure has three parameters: in table input, out table output, and in out, which can be input and output. What does that mean? People who just came into contact may not understand it much. in fact, just like the parameters in the java method, in can pass the parameters to the stored procedure (in fact, we can regard the stored procedure as a method, it is only special, he can compile), while out is equivalent to the return parameter of the java method, but the stored procedure can define multiple out, which is equivalent to returning multiple parameters, do you understand?

It should be noted that a single record or multiple records can be returned for an out statement, and their implementation methods are different. If a single record is returned, it can be directly assigned a value as a variable, the following is an example:

Stored Procedure Code:

Create or replace procedure pro_return_result (a in varchar2, B out varchar2) as begin if a is not null then B: = 'hard to say '; end if; END pro_return_result;

Java Call Code: (only paste the core part, and connect to other databases)

Code

CallableStatement cs  = null;        cs  = con.prepareCall("{call pro_return_result(?,?)}");        cs.setString(1, "1");        cs.registerOutParameter(2, Types.VARCHAR);        cs.execute();        String result = cs.getString(2);        System.out.println(result);

See the example above. Because the second parameter of the stored procedure is the out input and a single record is returned, this method is used to register the output out.

Cs. registerOutParameter (2, Types. VARCHAR); output result: If you want to output multiple records, write several more out records in the stored procedure, and then write several more cs records. registerOutParameter (parameter location, Types. VARCHAR); OK to get a single record. If you want to assign a value to the write variable in the stored procedure, you can use this method when returning a single record: select Field 1 of a table, field 2 ...... Into variable 1, variable 2 from table where Table field = a value, for example:
Code
CREATE OR REPLACE PROCEDURE validate_module_name(  a in varchar2,b out varchar2) AS c varchar2(255);BEGIN  select module_one into c  from temp_test_case where num= a;      if c is not null then      b := '1';    end if;END validate_module_name;

The calling method is the same as described above. as is a custom variable, begin to End is a PL/SQL statement, and a null value in PL/SQL is expressed as is null, non-empty: is not null, which is different from java programs. PL/SQL has time to explain

Note: There is another kind of return condition in the stored procedure. Multiple records are returned, that is, the result set instead of a single record. You cannot use the method above. You must use the cursor to output the result set, select Field 1 and Field 2 in a table at the same time ......
Into variable 1, variable 2 from table where Table field = A value cannot be used because the select into statement only returns a single record. Stored procedures cannot be called in java.
Cs. registerOutParameter (parameter location, Types. VARCHAR). The next java call to the returned result set in the stored procedure will be explained in detail ......