Query 3d information (Group 3, Group 6, Machine ball number)---how to use stored Procedures---__ storage

1, stored procedure Code: Pr_get_3dinfo------------------

Create or replace procedure Pr_get_3dinfo (
Iz3 in integer,--------Group Three for default 0
Iz6 in integer,--------Group Six
IJQ in integer,--------machine ball well
IZJQ in integer,-----recent period
Iyear in Varchar2,--------year
Ocursor out pk_public.zhcw_cursor-------------cursor set
)

is---------to define a local temporary variable directly at IS and begin
V_where VARCHAR2 (1000); --------Define a temporary variable (as a conditional variable string in a dynamic SQL string)
V_sql varchar2 (4000); --------Define a temporary variable (as a dynamic SQL string)


/*
Function:
Parameter: Default 0 not selected,
Iz3 1 indicates the selected
Iz6 1 indicates the selected
IJQ 1 1 Machine 1 ball, 2 1 Machine 2 ball, 3 2 machine 1 ball, 4 2 Machine 2 ball
Number of IZJQ----recent periods
Iyear--------Year
Author: Licheng
Time: 2010-7-17
*/
The business process phase between Begin-----------------------(begin) and (end stored procedure name)
---return;

V_where: = ' and t.cz_id=2 ';
if (ijq=1) then
V_where: = ' and M.j_num=1 and M.q_num=1 ' | | V_where;
elsif (ijq=2) Then
V_where: = ' and M.j_num=1 and m.q_num=2 ' | | V_where;
elsif (ijq=3) Then
V_where: = ' and m.j_num=2 and M.q_num=1 ' | | V_where;
elsif (ijq=4) Then
V_where: = ' and m.j_num=2 and m.q_num=2 ' | | V_where;
End If;

if (iyear >0) Then
V_where: = ' and substr (t.kj_date,1,4) = ' | | iyear| | | V_where;
End If;

if (iz3>0 and iz6=0) then
V_where: = ' and (substr (t.kj_z_num,1,1) =substr (t.kj_z_num,3,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,5,1))
or (substr (t.kj_z_num,1,1) =substr (t.kj_z_num,5,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,3,1))
or (substr (t.kj_z_num,5,1) =substr (t.kj_z_num,3,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,3,1))) ' | | V_where;
elsif (iz3=0 and iz6>0) then
V_where: = ' and (substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,3,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,5,1)) '|| V_where;
elsif (iz3>0 and iz6>0) then
V_where: = ' and (substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,3,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,5,1) )
or ((substr (t.kj_z_num,1,1) =substr (t.kj_z_num,3,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,5,1))
or (substr (t.kj_z_num,1,1) =substr (t.kj_z_num,5,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,3,1))
or (substr (t.kj_z_num,5,1) =substr (t.kj_z_num,3,1) and substr (t.kj_z_num,1,1)!=substr (t.kj_z_num,3,1))) ' | | V_where;


End If;

if (izjq=0) then
V_sql: = '
Select T.kj_issue,t.kj_z_num,t.kj_date,m.j_num,m.q_num,m.sj_num from Tab_kaijiang_info t
Left join Tab_cz_zj_info m on t.cz_id=m.cz_id and T.kj_issue=m.kj_issue
where 1=1 ' | | v_where| |
' ORDER by t.kj_issue Desc ';
Else
V_sql: = ' Select T.kj_issue,t.kj_z_num,t.kj_date,m.j_num,m.q_num,m.sj_num from
(Select b.* from (SELECT * to Tab_kaijiang_info R where r.cz_id=2 order by r.kj_issue Desc) b where rownum<= ' | | izjq| | ')
T left join Tab_cz_zj_info m on t.cz_id=m.cz_id and T.kj_issue=m.kj_issue
where 1=1 ' | | v_where| |
' ORDER by t.kj_issue Desc ';
End If;

Open ocursor for V_sql; -------Open a cursor connection
End Pr_get_3dinfo; -------Stored Procedure End identification

Second, create the returned cursor object type pk_public.zhcw_cursor:

Create or Replace package pk_public is

Type zhcw_cursor is REF CURSOR;

End Pk_public;

Where Pk_public is the package name and Zhcw_cursor is the cursor type.

Third, Java calls stored procedures.

/*
*
* Query 3d information, call the stored procedure
*
*/
Public List find_3d_infor () throws exception{

Create a Connection object
Connection conn = GetSession (). Connection ();
Define the list object that needs to be returned
List List = new ArrayList ();
Defines a DataSet object provided by Apache
Rowsetdynaclass result = null;

To define a result set object

ResultSet rs = null;

Defining Data connection Control objects
callablestatement proc = null;

proc = Conn.preparecall ("{Call Pr_get_3dinfo (?,?,?,?,?,?)}");
Set shaping and string-type data
Proc.setint (1, 0);
Proc.setint (2, 0);
Proc.setint (3, 0);
Proc.setint (4, 30);
Proc.setstring (5, "0");

Define the cursor parameters that need to be returned
Proc.registeroutparameter (6, Oracle.jdbc.OracleTypes.CURSOR);
Execution data
Proc.execute ();

Get the cursor data to return
rs = (ResultSet) proc.getobject (6);

To convert a result set to a desired dataset object
result = new Rowsetdynaclass (RS);
Converts a DeMerit collection object to a list object
List=result.getrows ();
Close result set
Rs.close ();

Turn off the controller
Proc.close ();

Close Connection Object

Conn.close ();

return list;
}

}