oracle存储过程

oracle存储过程

http://owftc.iteye.com/blog/90032

Java代码
create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as
begin
select NAME into name_out from test where AGE = age_in;
end;

create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is
begin
insert into test values (UserID, UserName, UserAge);
end;

create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as
begin
select NAME into name_out from test where AGE = age_in;
end;

create or replace procedure insertRecord(UserID in varchar2, UserName in varchar2,UserAge in varchar2) is
begin
insert into test values (UserID, UserName, UserAge);
end;

Java代码
-

-

首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：

Java代码
create sequence TEST_SEQ
minvalue 100
maxvalue 999
start with 102
increment by 1
nocache;

create sequence TEST_SEQ
minvalue 100
maxvalue 999
start with 102
increment by 1
nocache;

语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。

定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：
--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。
Java代码
create or replace procedure InsertRecordWithSequence(UserID out number,UserName in varchar2,UserAge in number)
is
begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取
values(test_seq.nextval, UserName, UserAge);
/*返回PK值。注意Dual表的用法*/
select test_seq.currval into UserID from dual;
end InsertRecordWithSequence;

create or replace procedure InsertRecordWithSequence(UserID out number,UserName in varchar2,UserAge in number)
is
begin insert into test(id, name, age) --插入一条记录，PK值从Sequece获取
values(test_seq.nextval, UserName, UserAge);
/*返回PK值。注意Dual表的用法*/
select test_seq.currval into UserID from dual;
end InsertRecordWithSequence;

为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。
关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：
Java代码
create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数
end TestPackage;
包体是这么定义的：
create or replace package body TestPackage is
/*过程体*/
procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end GetRecords;
end TestPackage;

create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数
end TestPackage;
包体是这么定义的：
create or replace package body TestPackage is
/*过程体*/
procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end GetRecords;
end TestPackage;


小结：
包是Oracle特有的概念，Sql Server中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。

Java代码
-

-

Java代码
create or replace package TestPackage is
type mycursor is ref cursor;
procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);
procedure SelectRecords(ret_cursor out mycursor);
procedure DeleteRecords(id_in in number);
procedure InsertRecords(name_in in varchar2, age_in in number);
end TestPackage;

create or replace package TestPackage is
type mycursor is ref cursor;
procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);
procedure SelectRecords(ret_cursor out mycursor);
procedure DeleteRecords(id_in in number);
procedure InsertRecords(name_in in varchar2, age_in in number);
end TestPackage;

包体如下：
Java代码
create or replace package body TestPackage is
procedure UpdateRecords(id_in in number, newName in varchar2, newAge in number) as
begin
update test set age = newAge, name = newName where id = id_in;
end UpdateRecords;

procedure SelectRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end SelectRecords;

procedure DeleteRecords(id_in in number) as
begin
delete from test where id = id_in;
end DeleteRecords;
procedure InsertRecords(name_in in varchar2, age_in in number) as
begin
insert into test values (test_seq.nextval, name_in, age_in);
--test_seq是一个已建的Sequence对象，请参照前面的示例
end InsertRecords;
end TestPackage;

create or replace package body TestPackage is
procedure UpdateRecords(id_in in number, newName in varchar2, newAge in number) as
begin
update test set age = newAge, name = newName where id = id_in;
end UpdateRecords;

procedure SelectRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end SelectRecords;

procedure DeleteRecords(id_in in number) as
begin
delete from test where id = id_in;
end DeleteRecords;
procedure InsertRecords(name_in in varchar2, age_in in number) as
begin
insert into test values (test_seq.nextval, name_in, age_in);
--test_seq是一个已建的Sequence对象，请参照前面的示例
end InsertRecords;
end TestPackage;


--------------------华丽的分割线-----------------------


http://oraclex.iteye.com/blog/814442

实现存储过程必须先在oracle建立相应的Procedures，如下所示：

Sql代码
--添加信息--
create or replace procedure insert_t_test(
p_id in number,
p_name in varchar2,
p_password in varchar2
) is
begin
insert into t_test(id,name,password) values(p_id,p_name,p_password);
end;
-------------------------
--删除信息--
create or replace procedure del_t_test(
p_id in number,
x_out_record out number) is
begin

delete t_test where id = p_id;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;
-----------------------------
--查询所有信息--
create or replace procedure all_t_test(
x_out_record out number,
x_out_cursor out sys_refcursor) is
begin
open x_out_cursor for
select * from t_test;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;

--添加信息--
create or replace procedure insert_t_test(
p_id in number,
p_name in varchar2,
p_password in varchar2
) is
begin
insert into t_test(id,name,password) values(p_id,p_name,p_password);
end;
-------------------------
--删除信息--
create or replace procedure del_t_test(
p_id in number,
x_out_record out number) is
begin

delete t_test where id = p_id;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;
-----------------------------
--查询所有信息--
create or replace procedure all_t_test(
x_out_record out number,
x_out_cursor out sys_refcursor) is
begin
open x_out_cursor for
select * from t_test;
x_out_record := 0;
exception
when others then
x_out_record := -1;
end;

其中的存储过程名字（就是加粗部分）必须要和java代码中的相对应
Java代码如下：
Java代码
package com.procedure.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;


public class ConnDB {

private String url="jdbc:oracle:thin:@localhost:1521:orcl";

private String driverClass="oracle.jdbc.driver.OracleDriver";

private String username="scott";

private String password="hello";


public Connection getConn(){
Connection conn=null;
try {
Class.forName(driverClass);
conn=DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

public static void main(String args[]){
ConnDB cd=new ConnDB();
Connection conn=cd.getConn();
/**
* 添加 有输入参数 无输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call insert_t_test(?,?,?)}");
call.setInt(1, 66);
call.setString(2, "小猫");
call.setString(3, "8989");
Boolean b=call.execute();
System.out.println("b="+b);
} catch (SQLException e) {
e.printStackTrace();
}*/

/**
* 删除 有输入参数 得到输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call del_t_test(?,?)}");
call.setInt(1, 66);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
Integer result=call.getInt(2);
System.out.println("执行结果为0正常，执行结果为-1不正常"+result);
} catch (SQLException e) {
e.printStackTrace();
}*/

/**
* 使用游标查询所有信息 无输入参数 有输出参数
*/
try {
CallableStatement call=conn.prepareCall("{call all_t_test(?,?)}");
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
Integer result=call.getInt(1);
ResultSet rs=(ResultSet) call.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

package com.procedure.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;


public class ConnDB {

private String url="jdbc:oracle:thin:@localhost:1521:orcl";

private String driverClass="oracle.jdbc.driver.OracleDriver";

private String username="scott";

private String password="hello";


public Connection getConn(){
Connection conn=null;
try {
Class.forName(driverClass);
conn=DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

public static void main(String args[]){
ConnDB cd=new ConnDB();
Connection conn=cd.getConn();
/**
* 添加 有输入参数 无输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call insert_t_test(?,?,?)}");
call.setInt(1, 66);
call.setString(2, "小猫");
call.setString(3, "8989");
Boolean b=call.execute();
System.out.println("b="+b);
} catch (SQLException e) {
e.printStackTrace();
}*/

/**
* 删除 有输入参数 得到输出参数
*/
/*try {
CallableStatement call=conn.prepareCall("{call del_t_test(?,?)}");
call.setInt(1, 66);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
Integer result=call.getInt(2);
System.out.println("执行结果为0正常，执行结果为-1不正常"+result);
} catch (SQLException e) {
e.printStackTrace();
}*/

/**
* 使用游标查询所有信息 无输入参数 有输出参数
*/
try {
CallableStatement call=conn.prepareCall("{call all_t_test(?,?)}");
call.registerOutParameter(1, Types.INTEGER);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
Integer result=call.getInt(1);
ResultSet rs=(ResultSet) call.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}


-----------------------------------华丽的分割线------------------------

http://www.cnblogs.com/liliu/archive/2011/06/22/2087546.html

Oracle 存储过程 简要记录存储过程语法与Java程序的调用方式

　　一 存储过程

　　　　首先，我们建立一个简单的表进行存储过程的测试

create table xuesheng(id integer, xing_ming varchar2(25), yu_wen number, shu_xue number);insert into xuesheng values(1,'zhangsan',80,90)insert into xuesheng values(2,'lisi',85,87)
1）无返回值的存储过程

create or replace procedure xs_proc_no isbegin insert into xuesheng values (3, 'wangwu', 90, 90); commit;end xs_proc_no;
2）有单个数据值返回的存储过程


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;
其中，以上两种与sql server基本类似，而对于返回数据集时，上述方法则不能满足我们的要求。在Oracle中，一般使用ref cursor来返回数据集。示例代码如下：

3）有返回值的存储过程(列表返回)

首先，建立我们自己的包。并定义包中的一个自定义ref cursor

create or replace package mypackage as type my_cursor is ref cursor;end mypackage;
在定义了ref cursor后，可以书写我们的程序代码

create or replace procedure xs_proc_list(shuxue in number, p_cursor out mypackage.my_cursor) isbegin open p_cursor for select * from xuesheng where shu_xue > shuxue;end xs_proc_list;
二、程序调用

在本节中，我们使用java语言调用存储过程。其中，关键是使用CallableStatement这个对象，代码如下：

String oracleDriverName = "oracle.jdbc.driver.OracleDriver";

// 以下使用的Test就是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");//此处为数据库用户名与密码

} 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.execute();
String teststring=proc.getString(2);
System.out.println(teststring);

} catch (Exception ex) {
ex.printStackTrace();
}
对于列表返回值的存储过程，在上述代码中做简单修改。如下


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.execute(); ResultSet rs=null; int total_number=proc.getInt(3); rs=(ResultSet)proc.getObject(4);
上述存储过程修改完毕。另外，一个复杂的工程项目中的例子：查询一段数据中间隔不超过十分钟且连续超过100条的数据。即上述代码所调用的getdcsj存储过程

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 for
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 for
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 for
select * from d_dcsj where zh = 'C';
open var_cursor_b for
select * from d_dcsj where zh = 'C';
else
open var_cursor_a for
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 for
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;
/