怎么调用Oracle存储过程
2022-11-21
本文介绍如何调用Oracle存储过程。内容很详细。有兴趣的朋友可以参考一下。希望对大家有所帮助。
Oracle的存储过程语法如下：
create procedure 存储过程名称（随便取） is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end;
(2) 带参数的存储过程语法：
create procedure 存储过程名称（随便取） (变量1 数据类型,变量2 数据类型,...,变量n 数据类型) is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end;
(3) 带输入输出参数的存储过程语法：
create procedure 存储过程名称（随便取） (变量1 in(或out) 数据类型,变量2 in(或out) 数据类型,...,变量n in(或out) 数据类型) is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量 begin 执行部分 end;
注意：使用上述语法创建存储过程时存储过程怎么执行，可能会遇到数据库中存在同名存储过程，因此Oracle会弹框报错，说该名称已被现有对象使用. 有两种解决方案：
方法一：更改存储过程名称
方法二：在第一个create procedure之间添加或替换关键字，例如：create or replace procedure存储过程名。但是不推荐这种方法，因为这种方法会把之前的同名存储过程替换成你现在写的
存储过程案例一：无参存储过程
create replace procedure procedure_1
is
begin
dbms_output.put_line('procedure_1.......');
end;
存储过程案例2：带参数的存储过程
create procedure procedure_2(v_i number,v_j number)
is
v_m number(5);
begin
dbms_output.put_line('procedure_2.......');
v_m := v_i + v_j;
dbms_output.put_line(v_i||' + '||v_j||' = '||v_m);
end;
存储过程案例三：带输入输出参数的存储过程
存储过程的参数分为输入参数和输出参数。
输入参数：输入参数一般在变量名和数据类型之间加上，表示该参数为输入参数
输出参数：输出参数一般在变量名和数据类型之间加上out，表示该变量是输出参数
in和out不写，默认为入参
create procedure procedure_3(v_i in number,v_j in number ,v_m out number)
is
begin
dbms_output.put_line('procedure_3.......');
v_m:=v_i - v_j;
dbms_output.put_line(v_i||' - '||v_j||' = '||v_m);
end;
在 PL/SQL 块中调用存储过程
下面以调用上面三个存储过程为例
declare v_param1 number(5):=2; v_param2 number(5):=8;v_result number(5); begin --调用上面案例一的存储过程 procedure_1(); --调用上面案例二的存储过程 procedure_2(v_param1,v_param2); --调用上面案例三的存储过程 procedure_3(v_param1,v_param2,v_result); dbms_output.put_line(v_result); end; /*执行结果：*/ procedure_1....... procedure_2....... 2 + 8 = 10 procedure_3....... 2 - 8 = -6 10
java调用存储过程
案例一：Java调用存储过程没有返回值
需求：写一个数据库emp表，插入一条编号为6666，名字为张三，位置为MANAGER的记录
/*存储过程*/
create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )
is
begin
insert into emp (empno,ename,job) values (v_empno,v_ename,v_job);
end;
//java调用存储过程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
//java调用存储过程
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger");
cs=conn.prepareCall("{call procedure_4(?,?,?)}");
//给输入参数赋值
cs.setInt(1, 6666);
cs.setString(2, "张三");
cs.setString(3, "MANAGER");
cs.execute();//执行
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
//执行后就会向数据库的emp表中插入一条编号为6666，姓名为张三，职位为MANAGER的记录
案例二：Java调用存储过程返回单列单行
需求：写一个存储过程，根据员工编号查找员工姓名，用java调用存储过程
/*存储过程*/
create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)
is
begin
select ename into v_ename from emp where empno=v_empno;
end;
//java调用存储过程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_5(?,?)}");
cs.setInt(1, 6666);//给输入参数赋值
/*指定输出参数的数据类型
语法：oracle.jdbc.OracleTypes.输出参数的数据类型
此例输出参数的数据类型是varchar，所以是oracle.jdbc.OracleTypes.VARCHAR*/
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();//执行
//获取输出参数的值，位置要和输出参数对应？的位置对应起来，该例输出参数对应第2个问号，而且输出参数的数据类型为字符型，所以是cs.getString(2)
String a=cs.getString(2);
System.out.println("员工姓名："+a);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*执行结果,控制台打印：*/
结果：员工姓名：张三
案例三：Java调用存储过程返回单行多列
需求：写一个存储过程，根据员工编号查找员工姓名、职位、工资，并用java调用存储过程
/*存储过程*/
create procedure procedure_6(v_empno in emp.empno%type,v_ename out emp.ename%type,v_job out emp.job%type,v_sal out emp.sal%type)
is
begin
select ename,job,sal into v_ename,v_job,v_sal from emp where empno=v_empno;
end;
//java调用存储过程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_6(?,?,?,?)}");
cs.setInt(1, 7788);
//指定输出参数的数据类型，注意：顺序要对应起来
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE);
cs.execute();//执行
//获取返回值
String ename=cs.getString(2);//获取姓名
String job=cs.getString(3);//获取职位
double sal=cs.getDouble(4);//获取薪水
System.out.println("员工编号为7788的姓名为："+ename+" 职位是："+job+" 薪水是："+sal);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*执行结果，控制台打印：*/
员工编号为7788的姓名为：SCOTT 职位是：ANALYST 薪水是：3000.0
案例四：Java调用存储过程返回多行多列（返回列表）
需求：写一个存储过程，根据部门编号查找该部门的所有员工信息，并用java调用存储过程
/*定义游标*/
create package my_package as
type emp_cursor is ref cursor;
end my_package;
/*存储过程*/
create procedure procedure_7(v_deptno in emp.deptno%type,emp_cursor out my_package.emp_cursor)
is
begin
open emp_cursor for select * from emp where deptno=v_deptno;
end;
//java调用存储过程
public static void main(String[] args) {
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger");
cs=conn.prepareCall("{call procedure_7(?,?)}");
cs.setInt(1, 20);//给输入参数赋值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );//指定输出参数的数据类型
cs.execute();
rs=(ResultSet) cs.getObject(2);//获取输出参数的值
while(rs.next()){
//顺序为数据库中字段前后顺序，例如数据库emp表中第5列为hiredate，数据类型为Date，所以获取第5列值时就应该用rs.getDate(5)
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getDate(5));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
closeResource(conn,cs,rs);//关闭资源
}
}
/*以下是20号部门所有员工的信息存储过程怎么执行，为了方便，我们这里只打印编号、姓名和入职时间
运行结果，控制台打印：*/
7369 SMITH 1980-12-17 7566 JONES 1981-04-02 7788 SCOTT 1987-04-19 7876 ADAMS 1987-05-23 7902 FORD 1981-12-03
这是上面java调用存储过程代码中关闭资源方法的代码
public static void closeResource(Connection conn,CallableStatement cs,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
最后，对于一个应用程序，分页的存储过程
分页存储过程：
/*定义游标*/
create package page_package as
type page_cursor is ref cursor;
end page_package;
/*存储过程*/
create procedure pro_paging (
v_page_size in number,--每页显示多少条
v_page_count out number,--总页数
v_current_page in number,--当前页
v_total_count out number,--记录总条数
emp_cursor out page_package.page_cursor--返回查询结果集的游标
)
is
v_begin number(5):=v_page_size*(v_current_page-1)+1;--查询起始位置
v_end number(5):=v_page_size*v_current_page;--查询结束位置
v_sql varchar2(1000):='select empno,ename from
(select a.empno,a.ename,rownum rn from
(select empno,ename from emp) a
where rownum<='|| v_end ||') b
where b.rn>='||v_begin;
/*不能像下面这么写，不然调用该存储过程时会报类型不一致的错，因为最里面查的只有empno,ename，因此外面也要和里面保持一致
v_sql varchar2(1000):=\'select * from
(select a.*,rownum rn from
(select empno,ename from emp) a
where rownum<=\'|| v_end ||\') b
where b.rn>='||v_begin;*/
v_ename varchar2(10);
v_empno number(4);
begin
open emp_cursor for v_sql;
loop
fetch emp_cursor into v_empno,v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_ename);
end loop;
v_sql:='select count(empno) from emp';
execute immediate v_sql into v_total_count;
if(mod(v_total_count,v_page_size)=0) then
v_page_count:=v_total_count/v_page_size;
else
v_page_count:=trunc(v_total_count/v_page_size)+1;
end if;
dbms_output.put_line('共 '||v_total_count||' 条记录');
dbms_output.put_line('共 '||v_page_count||' 页');
dbms_output.put_line('当前页： '||v_current_page);
dbms_output.put_line('每页显示 '||v_page_size||' 条');
end;
Java调用和上面java调用存储过程的例子是一样的。这里为了方便，直接在pl/sql中调用
/*调用分页存储过程*/
declare
v_page_count number(5);
v_cursor page_package.page_cursor;
v_total_count number(5);
begin
dbms_output.put_line('第一页数据。。。。。。。。。');
pro_paging(5,--每页显示5条
v_page_count,--总页数
1,--当前页
v_total_count,--记录总条数
v_cursor--游标
);
dbms_output.put_line('--------------------------');
dbms_output.put_line('第二页数据。。。。。。。。。');
--显示第二页数据
pro_paging(5,--每页显示5条
v_page_count,--总页数
2,--当前页
v_total_count,--记录总条数
v_cursor--游标
);
end;
/*运行结果：*/
第一页数据。。。。。。。。。
6666 张三
20 empSu2
19 empSave2
7369 SMITH
7499 ALLEN
共 17 条记录
共 4 页
当前页： 1
每页显示 5 条
--------------------------
第二页数据。。。。。。。。。
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
共 17 条记录
共 4 页
当前页： 2
每页显示 5 条
我将在这里分享如何调用Oracle存储过程。希望以上内容能对大家有所帮助，多了解。如果觉得文章不错，可以分享出去，让更多人看到。
