Oracle Stored Procedure

% TYPE attribute
In PL/SQL, you can declare variables and constants as built-in or user-defined data types to reference a column name and inherit its data types and sizes. This dynamic value assignment method is very useful. For example, the data TYPE and size of the columns referenced by variables have changed. If % TYPE is used, you do not need to modify the code, otherwise, you must modify the code.

Example:
V_empno SCOTT. EMP. EMPNO % TYPE;
V_salary EMP. SALARY % TYPE;
Not only can the column name use % TYPE, but also variables, cursors, records, or declared constants can use % TYPE. This is useful for defining variables of the same data type.
 
 
The Oracle stored procedure consists of three parts: Process Declaration, execution process, and stored procedure exception.
Oracle stored procedures can contain parameter stored procedures and parameter stored procedures.
I. No parameter program process syntax
 
1 create or replace procedure NoParPro
2 ......;
3 begin
4 ......;
5 exception // The stored procedure is abnormal.
6 ......;
7 end;
8

Ii. Example of stored procedure with Parameters
<! -- <Br> Code highlighting produced by Actipro CodeHighlighter (freeware) <br> http://www.CodeHighlighter.com/<br> --> 1 create or replace procedure queryempname (sfindno emp. empno % type)
2 sName emp. ename % type;
3 sjob emp. job % type;
4 begin
5 ....
7 exception
....
14 end;
15

Iii. Parameter stored procedures and value assignment methods
<! -- <Br> Code highlighting produced by Actipro CodeHighlighter (freeware) <br> http://www.CodeHighlighter.com/<br> --> 1 create or replace procedure runbyparmeters (isal in emp. sal % type,
Sname out varchar, sjob in out varchar)
2 as icount number;
3 begin
4 select count (*) into icount from emp where sal> isal and job = sjob;
5 if icount = 1 then
6 ....
9 else
10 ....
12 end if;
13 exception
14 when too_many_rows then
15 DBMS_OUTPUT.PUT_LINE ('Return value more than 1 line ');
16 when others then
17 DBMS_OUTPUT.PUT_LINE ('An error occurred during RUNBYPARMETERS! ');
18 end;
19

4. Calling stored procedures in Oracle
Method 1
<! -- <Br> Code highlighting produced by Actipro CodeHighlighter (freeware) <br> http://www.CodeHighlighter.com/<br> --> 1 declare
2 realsal emp. sal % type;
3 realname varchar (40 );
4 realjob varchar (40 );
5 begin // The start of the stored procedure call
6. realsal: = 1100;
7 realname: = '';
8 realjob: = 'cler ';
9 runbyparmeters (realsal, realname, realjob); -- must be in order
10 DBMS_OUTPUT.PUT_LINE (REALNAME | ''| REALJOB );
11 END; // END of the process call
12

Method 2
<! -- <Br> Code highlighting produced by Actipro CodeHighlighter (freeware) <br> http://www.CodeHighlighter.com/<br> --> 1 declare
2 realsal emp. sal % type;
3 realname varchar (40 );
4 realjob varchar (40 );
5 begin // process call start
6. realsal: = 1100;
7 realname: = '';
8 realjob: = 'cler ';
9 runbyparmeters (sname => realname, isal => realsal, sjob => realjob); -- the variable sequence of the specified value is variable.
10 DBMS_OUTPUT.PUT_LINE (REALNAME | ''| REALJOB );
11 END; // END of the process call
12
 
 
The following [Reproduced from] http://www.cnblogs.com/liliu/archive/2011/06/22/2087546.html
Briefly record Stored Procedure syntax and Java Program Calling Method
1. Stored Procedure
First, we create a simple table to test the stored procedure.

Create table
Xuesheng (id integer, xing_ming varchar2 (25), yu_wen number, shu_xue number );

Insert into xuesheng values (1, 'hangsan', 80, 90)
Insert into xuesheng values (2, 'lisi)
1) stored procedure without return values

Create or replace procedure xs_proc_no is
Begin
Insert into xuesheng values (3, 'wangw', 90, 90 );
Commit;
End xs_proc_no;
2) stored procedure with a single data value returned

Create or replace procedure xs_proc (temp_name in varchar2,
Temp_num out number) is
Num_1 number;
Num_2 number;
Begin
Select yu_wen, shu_xue
Into num_1, num_2
From xuesheng
Where xing_ming = temp_name;
-- Dbms_output.put_line (num_1 + num_2 );
Temp_num: = num_1 + num_2;
End;
The above two methods are similar to those of SQL server, while the above methods cannot meet our requirements for the returned data sets. In Oracle, ref cursor is generally used to return data sets. The sample code is as follows:
3) stored procedures with returned values (list return)
First, create our own package. And define a custom ref cursor in the package.

Create or replace package mypackage
Type my_cursor is ref cursor;
End mypackage;
After defining the ref cursor, you can write our program code.

Create or replace procedure xs_proc_list (shuxue in number,
P_cursor out mypackage. my_cursor) is
Begin
Open p_cursor
Select * from xuesheng where shu_xue> shuxue;
End xs_proc_list;
2. program call
In this section, we use java to call stored procedures. The key is to use the CallableStatement object. The Code is as follows:
View sourceprint?
String oracleDriverName = "oracle. jdbc. driver. OracleDriver ";
 
// The Test used below is the tablespace in Oracle
String oracleUrlToConnect = "jdbc: oracle: thin: @ 127.0.0.1: 1521: orcl ";
Connection myConnection = null;
Try {
Class. forName (oracleDriverName );
} Catch (ClassNotFoundException ex ){
Ex. printStackTrace ();
}
Try {
MyConnection = DriverManager. getConnection (oracleUrlToConnect,
"Xxxx", "xxxx"); // The database username and password.
 
} Catch (Exception ex ){
Ex. printStackTrace ();
}
Try {

CallableStatement proc = null;
Proc = myConnection. prepareCall ("{call xs_proc (?,?)} ");
Proc. setString (1, "zhangsan ");
Proc. registerOutParameter (2, Types. NUMERIC );
Proc.exe cute ();
String teststring = proc. getString (2 );
System. out. println (teststring );
 
} Catch (Exception ex ){
Ex. printStackTrace ();
}
For the stored procedure of list return values, make a simple modification in the above Code. As follows:

CallableStatement proc = null;
Proc = myConnection. prepareCall ("{call getdcsj (?,?,?,?,?)} ");
Proc. setString (1, strDate );
Proc. setString (2, jzbh );
Proc. registerOutParameter (3, Types. NUMERIC );
Proc. registerOutParameter (4, OracleTypes. CURSOR );
Proc. registerOutParameter (5, OracleTypes. CURSOR );
Proc.exe cute ();
ResultSet rs = null;
Int total_number = proc. getInt (3 );
Rs = (ResultSet) proc. getObject (4 );
The preceding stored procedure has been modified. In addition, an example of a complex project: query a piece of data with an interval of no more than 10 minutes and more than 100 consecutive records. That is, the getdcsj stored procedure called by the above Code
View sourceprint?
Create or replace procedure getDcsj (var_flag in varchar2,
Var_jzbh in varchar2,
Number_total out number,
Var_cursor_a out mypackage. my_cursor,
Var_cursor_ B out mypackage. my_cursor) is
Total number;
Cursor cur is
Select sj, flag
From d_dcsj
Where jzbh = var_jzbh
Order by sj desc
For update;
Last_time date;
Begin
For cur1 in cur loop
If last_time is null or cur1.sj> = last_time-10/60/24 then
Update d_dcsj set flag = var_flag where current of cur;
Last_time: = cur1.sj;
Else
Select count (*) into total from d_dcsj where flag = var_flag;
Dbms_output.put_line (total );
If total <100 then
Update d_dcsj set flag = null where flag = var_flag;
Last_time: = null;
Update d_dcsj set flag = var_flag where current of cur;
Else
Open var_cursor_a
Select *
From d_dcsj
Where flag = var_flag
And jzbh = var_jzbh
And zh = 'A'
Order by sj desc;
Number_total: = total;
Open var_cursor_ B
Select *
From d_dcsj
Where flag = var_flag
And jzbh = var_jzbh
And zh = 'B'
Order by sj desc;
Number_total: = total;
Exit;
End if;
End if;
End loop;
Select count (*) into total from d_dcsj where flag = var_flag;
Dbms_output.put_line (total );
If total <100 then
Open var_cursor_a
Select * from d_dcsj where zh = 'C ';
Open var_cursor_ B
Select * from d_dcsj where zh = 'C ';
Else
Open var_cursor_a
Select *
From d_dcsj
Where flag = var_flag
And jzbh = var_jzbh
And zh = 'A'
Order by sj desc;
Number_total: = total;
Open var_cursor_ B
Select *
From d_dcsj
Where flag = var_flag
And jzbh = var_jzbh
And zh = 'B'
Order by sj desc;
Number_total: = total;
End if;
Commit;
End;
/

Author: "fiercepanda"