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

Recently, I used Java to verify the information of cells in an Excel table. The data volume is large and the verification rules are complex.ProgramThe 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, in out can be Input and OutputWhat does it mean? People who have just been in touch may not understand it. In factIn is like a parameter in a Java method.Similarly, parameters can be uploaded to the stored procedure (in fact, the stored procedure can be considered as a method, but it is special and he can be compiled ).Out is equivalent to the return parameter of the Java method., JustStored Procedures can define multiple out, Which is equivalent to returning multiple parameters. Do you understand?

Note that,An out statement can return one or more records.Their implementation methods are different,Returns a single record.Assign a value directly as a variable. The following is an example:

Stored ProcedureCode:

  Create     Or     Replace     Procedure  Pro_return_result
(
A  In     Varchar2  , B out  Varchar2  
) As   
  Begin  
  If  A  Is     Not     Null     Then  
B:  =     '  Not easy 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.exe cute ();
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: Hard to say
If you want to output multiple records, write a few more out records in the stored procedure, and then write several more records.CS. registeroutparameter (parameter location  , Types. varchar) ; OK. A single record is returned.

If the stored procedure needs to assign values to the write variable, you can use this method when returning a single record:Select a table's Field 1, 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 used to place custom variables, and begin to end is a PL/SQL statement,NULL values in PL/SQL are represented by is null. non-null values are: Is not null, which is different from Java programs,PL/SQL has time for further explanation
 
 
Note: A stored procedure also returns multiple records, which are result sets rather than single records. You cannot use the method above. You must use the cursor to output the result set.Select a table's Field 1, Field 2 ......
Into variable 1, variable 2 from table where Table field = A ValueThis method 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 Java call to the returned result set of the stored procedure will be explained in the next step ......